Search this Blog

Friday, October 7, 2011

Unregister RMAN database directly from catalog DB

What DBA never moved databases to new hardware ??
And what DBA never forgot to unregister the database first from the RMAN catalog ??

If you didn't unregister the database after a database move, then at those crucial moments, months or years later, you might end up facing a problem when trying to e.g. restore that database from backup.

Imagine you're in the middle of disaster, the production database server crashed, management is breathing down your neck, and you need to restore this database as soon as possible.
You prepare a new server, install Oracle on it and immediately start the restore, but then disaster strikes again:


RMAN> run {
2> allocate channel ch0 device type 'sbt';
3> restore controlfile;
4> }


allocated channel: ch0
channel ch0: sid=147 instance=hkdrt1 devtype=SBT_TAPE
channel ch0: Veritas NetBackup for Oracle - Release 6.5 (2009050106)

Starting restore at 2009-DEC-03 11:57:20

released channel: ch0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/03/2009 11:57:20
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous


The restore fails, because RMAN does not know which of the databases known in the recovery catalog with the given SID needs to be restored.
You should have unregistered the old copy of the database years back, when you moved the database to that "new" server which now is totally dead.
You should have -maybe- tried a restore back then to make sure it worked !!

But hey, even with the management in your back everything now goes smooth.
You have this blog guide to follow:


First of all:
Following this blog, you are directly administrating the RMAN catalog. Make sure you only follow the below procedure, do not manually delete records from any of the tables, or change any of the data, as you corrupt the catalog in doing so, rendering it totally useless 


How to continue?

We are going to use a database package that is installed in this catalog database. The package is called dbms_rcvcat and it has a procedure unregisterdatabase that cleans out all information about a database by providing a database key (db_key) and a database id (db_id).
The statement to do this looks like:

SQL> exec dbms_rcvcat.unregisterdatabase(db_key,db_id);

Now we only need to find these db_key and db_id values.
First connect to the database which contains the RMAN catalog using whatever SQLplus program you like.
Find in the catalog the list of databases know to the catalog with this SID:

SQL> select db_key, db_name, 
               reset_time, dbinc_status
               from dbinc where db_name = 'YOURSID';

The result is a list of registered databases with this SID name.


DB_KEY DB_NAME RESET_TIME DBINC_STATUS
411792
YOURSID 7-11-2002 14:29:22 PARENT
411792
YOURSID 24-10-2005 13:40:18 CURRENT
7455112
YOURSID 10-10-2008 21:41:20 CURRENT


In this example a couple of records are shown for the same SID. If correctly configured a database has only one CURRENT record in the catalog. A CURRENT record represents a database that is - according to the RMAN catalog - currently an active representation of a running database.
Since however a database can have only one CURRENT incarnation the one with the most recent RESET_TIME is the actual CURRENT database registration.
A PARENT incarnation is a database incarnation from which the current incarnation branched following an OPEN RESETLOGS operation. It is therefore not always the case that a PARENT incarnation exists.

In the above example the database registration with DB_KEY=7455112 seems to be the actual current database, as this one has the most recent reset_time.
This means we are going to unregister the database with the db_key equal to 411792;

Now we only need to find the DB_ID that belongs to this database:

SQL> select db_key, db_id 
               from db where db_key= 411792;

The result will look like:


DB_KEY DB_ID
411792
412102591


Now that we know the DB_KEY and the DB_ID, we can use the above mentioned procedure to unregister this database from the catalog:

SQL> exec dbms_rcvcat.unregisterdatabase(411792,412102591);

Don't worry, this statement can run for minutes ( up to 10-15 minutes on our system )!!

Afterwards re-run the select from the DBINC table again, and you'll see that the database registration has been removed from the catalog.
If a PARENT record existed for this DB_KEY, that record has also been deleted.


Conclusion:

The above procedure enables you to successfully clean out ( unregister ) databases from the RMAN catalog that do no longer exist.
This procedure also is a very risky procedure. Especially if one does now know what he is looking at, and expecially if the management team is breathing down your neck.
If you're in a disaster situation, where an immediate restore of a production environment is wanted, it is not the good time to do these kind of exercises.
If and when you enter by mistake the DB_KEY and DB_ID of the latest CURRENT incarnation ( the database you need to restore ) and you accidently press [Enter], then immediately start thinking about another job !!
Any restore of that particular database by means of RMAN then is no longer possible!!