Thursday 14 November 2013

The ORA-00001 while resyncing the catalog

Lately I switched over a database to the standby. All went well until I faced the RMAN configuration. Usually there are problems with archived logs, which were backed up on the previous primary node and I usually deal with that calling CROSSCHECK ARCHIVELOG ALL;
This time however I have got the following:


RMAN> resync catalog;

starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 10/24/2013 22:57:29
ORA-00001: unique constraint (RMAN.TS_U3) violated

I registered an SR on the MOS, and they stated I probably hit the BUG:12597985 - RMAN RESYNC CATALOG GOT ORA-1 and may install some patch or use workaround in the form:

unregister database;
register database;

I went on with the provided workaround and hit the following:

RMAN> unregister database;

database name is "YYYYYY" and DBID is 688900000

Do you really want to unregister the database (enter YES or NO)? YES
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: 
ORA-02292: integrity constraint (RMAN.TSATT_F2) violated - child record found

Now I was informed this time it is a Bug 9799518 RMAN-6004 / ORA-2292 trying to unregister a database from an RMAN catalog and again I can install a patch. I would like to avoid the patch installation, so I did something else. Below the solution:

RMAN> unregister database;

database name is "YYYYYY" and DBID is 688900000

Do you really want to unregister the database (enter YES or NO)? YES
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: 
ORA-02292: integrity constraint (RMAN.TSATT_F2) violated - child record found

# here from sqlplus I disabled RMAN.TSATT_F2
# select table_name from dba_constraints where constraint_name='TSATT_F2';
# alter table TSATT modify constraint TSATT_F2 disable;

RMAN> unregister database;

database name is "YYYYYY" and DBID is 688900000

Do you really want to unregister the database (enter YES or NO)? YES
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: 
ORA-02292: integrity constraint (RMAN.TSATT_F3) violated - child record found

# here from sqlplus I disabled RMAN.TSATT_F3
# alter table rman.TSATT modify constraint TSATT_F3 disable;

RMAN> unregister database;

database name is "YYYYYY" and DBID is 688900000

Do you really want to unregister the database (enter YES or NO)? YES
database unregistered from the recovery catalog

# here I found the orphaned rows in the TSATT table and delete them - according to the expectations 
# these were the rows making both the constraints unable to be enabled
# after the deletion I enabled the constraints disabled previously
# delete from RMAN.TSATT T where not exists (select 1 from RMAN.CKP C where C.CKP_KEY=T.START_CKP_KEY) ;
# delete from RMAN.TSATT T where not exists (select 1 from RMAN.CKP C where C.C3626503034KP_KEY=T.END_CKP_KEY) ;
# most possibly both queries above would touch the same rows

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete