Remigium: The ORA-00001 while resyncing the catalog

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

1 comment:

Koper said...: During DR test after come back switchover when tried to do backup I had got the following:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 10/13/2018 11:50:58
ORA-00001: unique constraint (RMANDB.TS_P1) violated

Because this was production database I wanted to avoid unregistering database. I realized that problem was with TEMP tablespaces. So what i have done:

1. Sqlplus into the catalog database as the rman user and find out the DBINC_KEY.
RMANDB > select dbid,name,resetlogs_time,dbinc_key from rc_database_incarnation where dbid=505738321;

2. Find tablespace mismatch

RMANDB >select TS#,TS_NAME, CREATE_TIME, TEMPORARY, DROP_TIME from ts where DBINC_KEY=14541;

Some of my TEMP tablespaces were marked in repository as dropped but in db those were not.

3. So I deleted those rows.
RMANDB >delete from ts where DBINC_KEY=14541 and ts#=43;

4. And Successful resync catalog.

Inspired by http://ravigaur.blogspot.com/2009/09/ora-00001-unique-constraint-rmantfu2.html; 16 October 2018 at 09:11:00 CEST