RMAN restore spfile from autobackup and compatible weirdness

Prompted by a recent interview I wanted to perform a little test with RMAN, and incrementally updated backups. I created a 11.2.0.2.5 database on my Linux (OEL 5.5) test system and refreshed my understanding of this most useful rman feature (which is for another post).

So after I was happy with the working of the incrementally updated image copies I decided to see if I could restore my database with those. Time to use “drop database”, which removes spfile, data files, temp files and the control files. Tabula rasa!

But it didn’t matter I thought: I have controlfile/spfile autobackups and fully recovered image copies plus all the archived logs in the FRA. What could possibly go wrong? Well it took me 30 minutes to get the database back.

RMAN duplicate and read-only tablespaces

This one is to be filed under the “little things I didn’t really know until now” section: RMAN duplicate. Be honest to yourself: would you have known the answer to this question: what happens to tablespaces which are read-only on the source during an RMAN duplication (not for standby)?

Some background

I have started my career as a DBA on Oracle 8.1.7.0, and at the time not everyone fully embraced RMAN. OK, RMAN made it really hard at the time to fall in love with it. So when we “cloned” our production database to development, the following steps were followed:

1. For each tablespace in the database, put it in hot backup mode and copy the files across to the destination location, then end the hot backup mode.
2. Copy any archived logs generated in during the hot backup mode to DEV.
3. Create a backup controlfile to trace.
4. Edit the create controlfile statement (Set #2-resetlogs) and extract the “create controlfile” statement, adding the new database name in the “set” clause.
5. Start DEV in nomount mode.
6. Run create controlfile statement.
7. Recover the database until cancel using the backup controlfile, applying all archived logs.
8. Open the database with the resetlogs clause.

That worked ok, unless you had a data file offline or read only.

Recreate the standby controlfile when using ASM

This is in reply to some work I was supposed to carry out over the weekend on a DR database. It could have been really simple, but it turned out it wasn’t. As part of a major migration I moved a 2TB database from OCFS to ASM, which worked really nicely with a Data Guard switchover operation. Trouble now was that the standby databases still references online redo log files on the file system rather than ASM. I wanted to get rid of these annoying messages by updating the control file which was already rectified on the primary.

Under normal (non ASM) circumstances that doesn’t cause any trouble, but since ASM has the naming convention “+DISGROUPNAME/db_unique_name” the standby controlfile isn’t that easy to replace. Well actually it is but I needed some more careful thinking.

The steps are quite simple:

• Create a standby controlfile on the primary: alter database create standby controlfile as ‘/tmp/primary.ctl’;
• Ship this file to the standby database server
• Create a backup of the existing standby controlfile just in case (rman: backup current controlfile format ‘/tmp/backup.ctl’)
• Cancel managed recovery: alter database recover managed standby database cancel;
• Shut the standby database down: shutdown immediate (if you’re a good citizen you also defer log shipping from the primary to avoid any errors poluting the alert.log and potentially triggering an alarm)
• Startup nomount
• RMAN: restore controlfile from ‘/tmp/primary.ctl’

A remark at this place: the controlfiles parameter usually uses OMF syntax when in ASM. Prior to 10.2.0.4 you couldn’t restore the controlfile if the spfile parameter was an OMF name, but it seems you can do this now. Users of other versions please reset the control_files parameter in the spfile and bounce the instance.

ORA-02290: check constraint (RMAN.AL_C_STATUS) violated

I ran into this problem today when I was working on a backup problem. The scenario is as follows:

• Oracle 10.2.0.4 EE
• RHEL 5.2 PAE kernel, i.e. 32bit
• Backup taken on the standby database
• Using a recovery catalog

All rman operations on the standby database with catalog database connection failed with the following error stack:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 10/30/2009 10:01:57
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of partial resync command on default channel at 10/30/2009 10:01:57
ORA-02290: check constraint (RMAN.AL_C_STATUS) violated

Ooops-so no backups, need to fix this quickly now. BTW, the same problem didn’t exist when running the same operation on the primary, but I don’t have space for the (disk) backup over there.

Archivelog retention policy changes in RMAN 11g

In the past, that means at least until 10.2.x it used to be a bit clumsy to set the archivelog deletion policy to “applied on standby” on both primary and standby database. It involved setting an underscore parameter, as per Metalink Note 728053.1 “Configure RMAN to purge archivelogs after applied on standby”. For reference, the parameter was _log_deletion_policy, and you’d have to bounce the database for it to take effect.

SQL> alter system set "_log_deletion_policy"='ALL' scope=spfile;

Only then could you avoid the error message.Now with 11g that’s a lot easier.