RMAN 10G Registration Problems

OK, let's start this problem prevention blog by providing you with an overview of the problem.

As you know we have begun converting many of our test databases to Oracle's latest and greatest release, Oracle10G.  You also know that I have begun testing 10G RMAN to make sure everything works.  The problem occurs when you convert an Oracle 9I database to 10G and then attempt to register the newly upgraded database with the 10G RMAN repository. 

During the registration, you may get the following error:

RMAN> register database ;

database registered in recovery catalog

starting full resync of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03008: error while performing automatic resync of recovery catalog

ORA-01400: cannot insert NULL into ("RMAN"."BSF"."MODIFICATION_TIME")

If you do get the error, which I am assuming everyone will, you are in for a somewhat rude surprise.   You are hitting BUG 3657899 - Registering Migrated Database in RMAN Fails with ORA-01400.   The bug definition states that the problem occurs because the MODIFICATION_TIME is NULL in V$BACKUP_SPFILE after the migration.  Actually, the registration works; it's the catalog resync that occurs automatically after the registration that fails.

Oracle's fix is to have the customer issue the 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE' statement and use the output to recreate the target database's control files.   The database can then be successfully registered and the catalog resynchronized.  

Needless to say, the 'CREATE CONTROLFILE' statement is NOT an operation to be taken lightly.  As far as I am concerned, it is one of the most dangerous statements a DBA can execute.  During my career as an Oracle instructor, I have personally taught hundreds of students how to recreate controlfiles in Oracle's Backup and Recovery Class and I still only execute this statement as a last resort.  I will always try and "weasel" my way out of doing it if I can.

We now need to recreate the controlfiles for several of our test databases.   Lucky for us we caught the problem before we began migrating our production databases from Oracle9i to Oracle10G.   I created a TAR with Oracle that pretty much stated that recreating a database's controlfiles should only be done as a last resort in a recovery situation and asked if there were any other workarounds available.    I received a quick response from Oracle telling us that the problem was fixed in releases 10.1.0.4 and 10.2.   The problem is that neither of those versions have been released yet.   At that time, we felt that we should place our 10G production migration on hold until 10.1.0.4 became available.

Arun Bhat, Giant Eagle's RMAN expert, decided that instead of us waiting for Oracle versions 10.1.0.4 or 10.2 to be released for AIX,  we needed to do some further testing on our own.   One of Arun's suggestions was to preregister the 9I database with the 10G repository, upgrade the 9I database to 10G and then execute the RESYNC CATALOG command to resynchronize the newly upgraded database in 10G RMAN.  Jeff Kondas agreed and decided that he would run the tests.   Arun is usually correct and he once again was in this case.   The database resynchronize was successful.

So to summarize the problem:

Problem Description:
Registering a target database in RMAN that has been upgraded from 9i to 10G will fail with an ORA-01400.  The actual registration itself works but the automatic resynchronization that occurs after the registration fails with the ORA-01400.  Since you can only back up a 10G database with a 10G RMAN repository, you will be unable to use RMAN to back up your newly upgraded database. 

Problem Fix:
Issue the 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE' statement which sends a file containing the CREATE CONTROLFILE syntax to the database's user dump destination directory.   Edit the output file and execute it to recreate all of the database's control files.   Register the database with 10G RMAN.

Problem Corrected:
The problem is supposed to be corrected in Oracle versions 10.1.0.4 or 10.2. 

Problem Prevention:
Make SURE YOU REGISTER THE 9I DATABASE WITH 10G RMAN BEFORE YOU UPGRADE THE DATABASE TO 10G.  If you don't, you'll be forced to drop and recreate the target database's control files.  Not an enviable position to be in...