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)?
I have started my career as a DBA on Oracle 18.104.22.168, 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:
- 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.
- Copy any archived logs generated in during the hot backup mode to DEV.
- Create a backup controlfile to trace.
- Edit the create controlfile statement (Set #2-resetlogs) and extract the “create controlfile” statement, adding the new database name in the “set” clause.
- Start DEV in nomount mode.
- Run create controlfile statement.
- Recover the database until cancel using the backup controlfile, applying all archived logs.
- Open the database with the resetlogs clause.
That worked ok, unless you had a data file offline or read only.
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-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
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. Continue reading
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.