Uncataloging a backupset from the rman repository


Ever seen this error message?

RMAN-06122: CHANGE .. UNCATALOG not supported for BACKUPSET

Seems you’re trying the get clean of backup sets that still exists on disk or tape but should no longer be regarded as a part of this database. What for? Well, this is quite a common requirement for rman-cloned or -duplicated databases where the rman repository (controlfile copy) originates from a seed database. Think of a standby database creation for example.

Since uncataloging backupsets is not allowed, one may test drive a change backupset ... delete;. Uurgh, this will also delete the files physically, which is clearly not the intention.

Another escape may be change backupset ... unavailable;, however, this attempt may result in ORA-19813: cannot have unavailable file ... in DB_RECOVERY_FILE_DEST. Great!

Ok, iff you resort to the good old crosscheck ...;, having removed the files in question temporarily, the rman repository entries will not truly be removed but just marked as expired. You may of course add a delete noprompt expired backupset; afterwards, to be done finally. but, do not forget to copy back the files in question … a lot of files … really data … still an affordable effort to copy forth and back?

What else? Thinking just twice about the sense of RMAN-06122, one may easily conclude that a catalog operation will actually be applicable to 1:1-file-associated rman-backup objects only. You know that from cataloging archive logs from a remote backup copy! Such a way, a backupset can’t be managed as expected above, can it? Yes in fact it is possible. You actually have to uncatalog every piece of the target backupset to get rid of the backupset itself eventually. Not very smart? No!

Ok, for the sake of an example i cleaned up all the rman repository. Therefore, a list backup; produces no output.

RMAN> list backup;

Since i have a test database that will produce backups of around 500 mb, i set the backuppiece size to 200 mb, which will result in a backupset of three pieces (the fourth is the controlfile autobackup).

RMAN> configure channel device type disk maxpiecesize = 200M;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 200 M;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1

RMAN> backup database;
Starting backup at 02-NOV-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=133 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=E:\ORADATA\DGS\SYSTEM01.DBF
input datafile fno=00003 name=E:\ORADATA\DGS\SYSAUX01.DBF
input datafile fno=00002 name=E:\ORADATA\DGS\UNDOTBS01.DBF
input datafile fno=00004 name=E:\ORADATA\DGS\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 02-NOV-10
channel ORA_DISK_1: finished piece 1 at 02-NOV-10
piece handle=E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NNNDF_TAG20101102T083023_6DZHJHSS_.BKP tag=TAG20101102T083023 comment=NONE
channel ORA_DISK_1: starting piece 2 at 02-NOV-10
channel ORA_DISK_1: finished piece 2 at 02-NOV-10
piece handle=E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NNNDF_TAG20101102T083023_6DZHJYY5_.BKP tag=TAG20101102T083023 comment=NONE
channel ORA_DISK_1: starting piece 3 at 02-NOV-10
channel ORA_DISK_1: finished piece 3 at 02-NOV-10
piece handle=E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NNNDF_TAG20101102T083023_6DZHK5Z4_.BKP tag=TAG20101102T083023 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:29
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 02-NOV-10
channel ORA_DISK_1: finished piece 1 at 02-NOV-10
piece handle=E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NCSNF_TAG20101102T083023_6DZHKGGK_.BKP tag=TAG20101102T083023 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 02-NOV-10

Verified, one backupset (#17) with three pieces for the data and one backupset (#18) for the controlfile autobackup.

RMAN> list backup;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17      Full    519.95M    DISK        00:00:26     02-NOV-10
  List of Datafiles in backup set 17
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 623598     02-NOV-10 E:\ORADATA\DGS\SYSTEM01.DBF
  2       Full 623598     02-NOV-10 E:\ORADATA\DGS\UNDOTBS01.DBF
  3       Full 623598     02-NOV-10 E:\ORADATA\DGS\SYSAUX01.DBF
  4       Full 623598     02-NOV-10 E:\ORADATA\DGS\USERS01.DBF
  Backup Set Copy #1 of backup set 17
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:26     02-NOV-10       NO         TAG20101102T083023
    List of Backup Pieces for backup set 17 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    20      1   AVAILABLE   E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NNNDF_TAG20101102T083023_6DZHJHSS_.BKP
    21      2   AVAILABLE   E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NNNDF_TAG20101102T083023_6DZHJYY5_.BKP
    22      3   AVAILABLE   E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NNNDF_TAG20101102T083023_6DZHK5Z4_.BKP
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18      Full    7.08M      DISK        00:00:01     02-NOV-10
        BP Key: 23   Status: AVAILABLE  Compressed: NO  Tag: TAG20101102T083023
        Piece Name: E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NCSNF_TAG20101102T083023_6DZHKGGK_.BKP
  Standby Control File Included: Ckp SCN: 623598       Ckp time: 02-NOV-10
  SPFILE Included: Modification time: 01-NOV-10

Ok, let’s uncatalog pieces #20 and #23.

RMAN> CHANGE BACKUPpiece 20 uncatalog;
uncataloged backuppiece
backup piece handle=E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NNNDF_TAG20101102T083023_6DZHJHSS_.BKP recid=20
stamp=733998623
Uncataloged 1 objects

RMAN> CHANGE BACKUPpiece 23 uncatalog;
uncataloged backuppiece
backup piece handle=E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NCSNF_TAG20101102T083023_6DZHKGGK_.BKP recid=23
stamp=733998654
Uncataloged 1 objects

Another list backup; shows only the two remaining pieces for backupset #17 and no longer any backupset #18. Rman removed the backupset record from the repository with the delete of the last enclosed piece.

RMAN> list backup;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17      Full    319.96M    DISK        00:00:26     02-NOV-10
  List of Datafiles in backup set 17
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 623598     02-NOV-10 E:\ORADATA\DGS\SYSTEM01.DBF
  2       Full 623598     02-NOV-10 E:\ORADATA\DGS\UNDOTBS01.DBF
  3       Full 623598     02-NOV-10 E:\ORADATA\DGS\SYSAUX01.DBF
  4       Full 623598     02-NOV-10 E:\ORADATA\DGS\USERS01.DBF
  Backup Set Copy #1 of backup set 17
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:26     02-NOV-10       NO         TAG20101102T083023
    List of Backup Pieces for backup set 17 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
            1   DELETED
    21      2   AVAILABLE   E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NNNDF_TAG20101102T083023_6DZHJYY5_.BKP
    22      3   AVAILABLE   E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NNNDF_TAG20101102T083023_6DZHK5Z4_.BKP

Aahhm, yes, the same is true for archivelog backups as follows.

RMAN> CHANGE backup of ARCHIVELOG ALL UNCATALOG;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of uncatalog command at 11/02/2010 09:16:11
RMAN-06122: CHANGE .. UNCATALOG not supported for BACKUPSET

And, however, the reverse of the procedure above is also possible like so (since all the files still exist on disk or tape), or more flexible catalog start with noprompt;.

RMAN> catalog db_recovery_file_dest noprompt;
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NCSNF_TAG20101102T083023_6DZHKGGK_.BKP
File Name: E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NNNDF_TAG20101102T083023_6DZHJHSS_.BKP
File Name: E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NNNDF_TAG20101102T083023_6DZHJYY5_.BKP
File Name: E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NNNDF_TAG20101102T083023_6DZHK5Z4_.BKP
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NCSNF_TAG20101102T083023_6DZHKGGK_.BKP
File Name: E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NNNDF_TAG20101102T083023_6DZHJHSS_.BKP
File Name: E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NNNDF_TAG20101102T083023_6DZHJYY5_.BKP
File Name: E:\ORADATA\DGS\FLASH\DGS\BACKUPSET\2010_11_02\O1_MF_NNNDF_TAG20101102T083023_6DZHK5Z4_.BKP

That’s it. Have fun!

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: