To register manual backups

Suppose we have taken a backup of a particular datafile outside of RMAN through O/s commands. Whatever backup you take outside of RMAN, RMAN will not know about it and it will not consider this backup exist until we register the backup in RMAN catalog.

For example suppose we have taken a backup of users01.dbf datafile on /u02/oracle/d2/users01.dbf' directory and now we want to register this backup in RMAN repository

To register give the following command

RMAN> catalog datafilecopy '/u02/oracle/d2/users01.dbf';

 

Once you issue the above command then RMAN will enter the information about this backup in the catalog and will recover from it in future recovery operation if needed.

Crosscheck Command in RMAN

Crosscheck is a very useful command in RMAN which automatically checks all the backups whose information is store in the RMAN repository and whether they are available on the disk or not.

If a corresponding backup is not available on the disk then Crosscheck command marks the backup as Expired otherwise it marks it as Available

To check status of all backupsets we can give the following command

RMAN> crosscheck backup;

To check status of all image copies we can give the following command

RMAN> crosscheck copy;

To know the status of Expired and Available backupsets give the following command

RMAN> list expired backup;

To know the status unavailable image copy copy give the following command

RMAN> list expired copy;

To delete the expired backup sets we can give the following command

RMAN> delete expired backup

To delete the expired image copies we can give the following command

RMAN> delete expired copy;

RMAN delete obsolete = ORA-19606: Cannot copy or restore to snapshot control file, RMAN-06214: Datafile Copy, etc…

PROBLEM

RMAN> delete obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     18660437 15-JUL-10          /oracle/product/11.2.0.2/dbs/snapcf_P10AC.f
Backup Set           18667103 19-JUL-10
  Backup Piece       18667105 19-JUL-10          /oraback/P10AC/autobackup/2010_07_19/o1_mf_s_724801673_64b73c6t_.bkp

Do you really want to delete the above objects (enter YES or NO)? YES
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_DISK_1 channel at 07/22/2010 14:44:43
ORA-19606: Cannot copy or restore to snapshot control file

Another common problem that can be solved the same way (if the easier “crosscheck controlfile copy” doesn’t work):

RMAN> delete noprompt obsolete;
using target database control file instead of recovery catalog
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4 device type=DISK
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     2      03-JUN-13          /oracle/product/11.2.0.3/dbs/snapcf_P10VQA.f
RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy   /oracle/product/11.2.0.3/dbs/snapcf_P10VQA.f

SOLUTION

Not sure why this happens, but the solution is to configure a different snapshot controlfile name so that RMAN can use that, thus allowing you to remove the “old” one. Then configure back to what you had:

configure snapshot controlfile name to '/tmp/it.f';
crosscheck controlfilecopy '/oracle/product/11.2.0.4/dbs/snapcf_P30ATLIS.f';
delete noprompt expired controlfilecopy '/oracle/product/11.2.0.4/dbs/snapcf_P30ATLIS.f';
delete noprompt obsolete;
configure snapshot controlfile name to '/p-atlis/oracle/product/11.2.0.4/dbs/snapcf_P30ATLIS.f';
show all;

MISMATCHED DATAFILE COPY

This is another similar issue but with a different solution.

RMAN> delete noprompt obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 3
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy 4 11-NOV-11 /tmp/control00.ctl
RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy /tmp/control00.ctl

REPORT THIS AD

and the solution is:

RMAN> crosscheck copy;
RMAN> delete noprompt expired copy;

RMAN-20005: target database name is ambiguous

RMAN Operation Fails With RMAN-20005: target data base name is ambiguous (Doc ID 224352.1)

RMAN-03002: failure of resync from db_unique_name command 

RMAN catalog. In case you are not connected to the RMAN catalog you end up with the following error:

1 2 3 4 5 6 7 8

RMAN> SHOW ARCHIVELOG DELETION POLICY FOR DB_UNIQUE_NAME 'DBIT121_SITE2'; using target database control file instead of recovery catalog RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of show command at 02/21/2017 13:58:53 RMAN-05037: FOR DB_UNIQUE_NAME option cannot be used in nocatalog mode

After connecting to the catalog, you can use this feature, e.g. to show the archive deletion policy.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

$ rman target sys/welcome1 catalog /@rcat Recovery Manager: Release 12.1.0.2.0 - Production on Tue Feb 21 14:25:10 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved. connected to target database: DBIT121 (DBID=644484523) connected to recovery catalog database RMAN> SHOW ARCHIVELOG DELETION POLICY FOR DB_UNIQUE_NAME 'DBIT121_SITE1'; RMAN configuration parameters for database with db_unique_name DBIT121_SITE1 are: CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY BACKED UP 1 TIMES TO DISK; RMAN> SHOW ARCHIVELOG DELETION POLICY FOR DB_UNIQUE_NAME 'DBIT121_SITE2'; RMAN configuration parameters for database with db_unique_name DBIT121_SITE2 are: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

There are quite a lot options which can be combined with the DB_UNIQUE_NAME feature like the following.

1 2 3

LIST ARCHIVELOG ALL FOR DB_UNIQUE_NAME 'DBIT121_SITE2'; REPORT SCHEMA FOR DB_UNIQUE_NAME 'DBIT121_SITE2'; SHOW ALL FOR DB_UNIQUE_NAME 'DBIT121_SITE2';

But getting back to my issue. I was running a resync catalog from my Standby database and ended up with the following error:

1 2 3 4 5 6 7 8

RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME 'DBIT121_SITE1'; resyncing from database with DB_UNIQUE_NAME DBIT121_SITE1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of resync from db_unique_name command on default channel at 02/21/2017 13:08:42 RMAN-20005: target database name is ambiguous

RMAN says that the target database name is ambiguous. But what does this mean. Let’s take a look a the RMAN error with the oerr utility. The oerr utility can not only be used with “ORA” error codes like “oerr ora 01555”, but also with “RMAN” error codes.

1 2 3 4 5

$ oerr rman 20005 20005, 1, "target database name is ambiguous" // *Cause: two or more databases in the recovery catalog match this name // *Action: //

Ok. This error is much more precise. Looks like that RMAN found more the one database called DBIT121 in the catalog, and so RMAN does not know, on which DBID to perform the requested command. Ok. So let’s connect to the RMAN catalog and check if this is really the case.

1 2 3 4 5 6 7 8 9

SQL> SELECT DB.DB_KEY,DB.DB_ID, DB.CURR_DBINC_KEY, DBINC.DB_NAME         FROM DB, DBINC        WHERE DB.CURR_DBINC_KEY = DBINC.DBINC_KEY          AND DBINC.DB_NAME   = 'DBIT121' ;  2    3    4     DB_KEY      DB_ID CURR_DBINC_KEY DB_NAME ---------- ---------- -------------- --------          1  642589239              2 DBIT121     546780  644484523         546781 DBIT121

Indeed. I do have two different DBID’s pointing to the same DB_NAME. Kinda confusing for RMAN. But which one is the one that have been backed up. We could query the RC_BACKUP_SET and RC_BACKUP_PIECE views to find that out.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

SQL> SELECT RBS.DB_KEY          , RD.NAME          , RBS.DB_ID   2    3    4           , RBS.BS_KEY          , RBS.RECID          , RBS.STAMP          , RBS.BACKUP_TYPE          , RBS.START_TIME, STATUS   5    6    7    8    9        FROM RC_BACKUP_SET RBS, RC_DATABASE RD      WHERE RBS.DB_KEY=RD.DB_KEY        AND RBS.DB_ID=RD.DBID        AND RD.NAME='DBIT121' ;  10   11   12 ... ...     DB_KEY NAME          DB_ID     BS_KEY      RECID      STAMP B START_TIM S ---------- -------- ---------- ---------- ---------- ---------- - --------- -     546780 DBIT121   644484523     555608       3070  936496831 I 21-FEB-17 A     546780 DBIT121   644484523     555609       3071  936496832 I 21-FEB-17 A     546780 DBIT121   644484523     555610       3072  936496836 D 21-FEB-17 A     546780 DBIT121   644484523     555611       3073  936496860 D 21-FEB-17 A     546780 DBIT121   644484523     555612       3074  936496875 D 21-FEB-17 A     546780 DBIT121   644484523     555613       3075  936496884 D 21-FEB-17 A     546780 DBIT121   644484523     555614       3076  936496890 D 21-FEB-17 A     546780 DBIT121   644484523     555615       3077  936496895 L 21-FEB-17 A     546780 DBIT121   644484523     555616       3078  936496897 L 21-FEB-17 A     546780 DBIT121   644484523     555617       3079  936496897 L 21-FEB-17 A     546780 DBIT121   644484523     555618       3080  936496898 D 21-FEB-17 A     DB_KEY NAME          DB_ID     BS_KEY      RECID      STAMP B START_TIM S ---------- -------- ---------- ---------- ---------- ---------- - --------- -     546780 DBIT121   644484523     555619       3081  936496900 D 21-FEB-17 A     546780 DBIT121   644484523     555620       3082  936498788 D 21-FEB-17 A     546780 DBIT121   644484523     555621       3083  936502389 D 21-FEB-17 A     546780 DBIT121   644484523     555622       3084  936505991 D 21-FEB-17 A     546780 DBIT121   644484523     555623       3085  936509589 D 21-FEB-17 A     546780 DBIT121   644484523     555624       3086  936513189 D 21-FEB-17 A     546780 DBIT121   644484523     555625       3087  936516788 D 21-FEB-17 A     546780 DBIT121   644484523     555626       3088  936520387 D 21-FEB-17 A     546780 DBIT121   644484523     555627       3089  936523988 D 21-FEB-17 A     546780 DBIT121   644484523     555628       3090  936527608 D 21-FEB-17 A     546780 DBIT121   644484523     555629       3091  936531188 D 21-FEB-17 A ... ...

After checking the output, I see that DBID 644484523 is the correct one, and DBID 642589239 is the one I want to get rid of.

To do so, we can shutdown the Standby database and start it up with nomount. The reason for that, is that you can’t issue the SET DBID command against a database which is mounted or open.

1 2 3 4 5 6 7 8

RMAN> SET DBID=642589239; executing command: SET DBID RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of set command at 02/21/2017 13:15:26 RMAN-06188: cannot use command when connected to a mounted target database

Ok. Let’s go the nomount and execute the “unregister database;” command after the correct DBID is set.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

$ rman target sys/welcome1 catalog /@rcat Recovery Manager: Release 12.1.0.2.0 - Production on Tue Feb 21 14:25:10 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved. connected to target database: DBIT121 (not mounted) connected to recovery catalog database RMAN> SET DBID=642589239; executing command: SET DBID database name is "DBIT121" and DBID is 642589239 RMAN> unregister database; database name is "DBIT121" and DBID is 642589239 Do you really want to unregister the database (enter YES or NO)? YES database unregistered from the recovery catalog RMAN>

Let’s check the RMAN catalog again.

1 2 3 4 5 6 7 8

SQL> SELECT DB.DB_KEY, DB.DB_ID, DB.CURR_DBINC_KEY, DBINC.DB_NAME         FROM DB, DBINC        WHERE DB.CURR_DBINC_KEY = DBINC.DBINC_KEY          AND DBINC.DB_NAME   = 'DBIT121' ;  2    3    4     DB_KEY      DB_ID CURR_DBINC_KEY DB_NAME ---------- ---------- -------------- --------     556718  644484523         556719 DBIT121

Cool. Looks much better.  Now my resync catalog from SITE1 issued from SITE2 works again.

1 2 3 4 5 6 7 8 9 10 11 12 13

RMAN> LIST DB_UNIQUE_NAME OF DATABASE; List of Databases DB Key  DB Name  DB ID            Database Role    Db_unique_name ------- ------- ----------------- ---------------  ------------------ 556718  DBIT121  644484523        PRIMARY          DBIT121_SITE1 556718  DBIT121  644484523        STANDBY          DBIT121_SITE2 RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME 'DBIT121_SITE1'; resyncing from database with DB_UNIQUE_NAME DBIT121_SITE1 starting full resync of recovery catalog full resync complete