Oracle DBA – A lifelong learning experience

Cataloging RMAN backups

Posted by John Hallas on April 15, 2010

We had a situation where we had backed up several databases and then would restore them as part of performance testing work. The catalog entries in the controlfile were lost either because they had become obsolete or we had restored to another point prior to the backup being taken.

 The backups were on still on disk and therefore they could be re-introduced to the controlfile. 

RMAN> catalog start with '/app/oracle/backups/KEEP_UNTIl_30APRIL2010';

searching for all files that match the pattern /app/oracle/backups/KEEP_UNTIl_30APRIL2010

List of Files Unknown to the Database
=====================================
File Name: /app/oracle/backups/KEEP_UNTIl_30APRIL2010/backup_db_MOMPRD1A_S_3441_P_1_T_714667083
File Name: /app/oracle/backups/KEEP_UNTIl_30APRIL2010/backup_db_MOMPRD1A_S_3442_P_1_T_714667108

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /app/oracle/backups/KEEP_UNTIl_30APRIL2010/backup_db_MOMPRD1A_S_3441_P_1_T_714667083
File Name: /app/oracle/backups/KEEP_UNTIl_30APRIL2010/backup_db_MOMPRD1A_S_3442_P_1_T_714667108

RMAN> list backup of database summary device type disk;
List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
3916    B  F  A DISK        2010-03-26:14:38:03 1       1       NO         BACKFULCOLD_MOMPRD_26032010

RMAN> change backupset 3916 keep until time "to_date('01-MAY-2010','DD-MON-YYYY')" nologs;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2183 instance=MOMPRD1A1 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=1076 instance=MOMPRD1A1 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protector A.06.00/PHSS_37147/PHSS_37148/DPSOL_00306/DPLNX_
keep attributes for the backup are changed
backup will be obsolete on date 2010-05-01:00:00:00
archived logs will not be kept or backed up
backup set key=3916 RECID=3916 STAMP=716380486

RMAN> list backup of database device type disk;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3916    Full    3.65G      DISK        00:00:00     2010-03-26:14:38:03
        BP Key: 3916   Status: AVAILABLE  Compressed: NO  Tag: BACKFULCOLD_MOMPRD_26032010
        Piece Name: /app/oracle/backups/KEEP_UNTIl_30APRIL2010/backup_db_MOMPRD1A_S_3441_P_1_T_714667083
        Keep: NOLOGS             Until: 2010-05-01:00:00:00
  List of Datafiles in backup set 3916
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 116434441  2010-03-26:14:27:48 +DATA/momprd1b/datafile/system.308.710077037
  2       Full 116434441  2010-03-26:14:27:48 +DATA/momprd1b/datafile/sysaux.309.710077341
  3       Full 116434441  2010-03-26:14:27:48 +DATA/momprd1b/datafile/undotbs1.260.710077985
  4       Full 116434441  2010-03-26:14:27:48 +DATA/momprd1b/datafile/undotbs2.311.710077647
  5       Full 116434441  2010-03-26:14:27:48 +DATA/momprd1b/datafile/users.261.710077655
  6       Full 116434441  2010-03-26:14:27:48 +DATA/momprd1b/datafile/retek_index.264.710076463
  7       Full 116434441  2010-03-26:14:27:48 +DATA/momprd1b/datafile/retek_data.319.710076751
  8       Full 116434441  2010-03-26:14:27:48 +DATA/momprd1b/datafile/lob_data.259.710077993
  9       Full 116434441  2010-03-26:14:27:48 +DATA/momprd1b/datafile/momstg.320.710077037
  10      Full 116434441  2010-03-26:14:27:48 +DATA/momprd1b/datafile/momclns.263.710076461
  11      Full 116434441  2010-03-26:14:27:48 +DATA/momprd1b/datafile/momtrns.318.710076749
  12      Full 116434441  2010-03-26:14:27:48 +DATA/momprd1b/datafile/owbsys.310.710077343

In another situation we had 2 databases both backed up to the same directory on disk and we needed to catalog individual datafile. Very much the same method but we just provide the exact path of each backup piece.

[/code]

/app/oracle/KEEP_UNTIl_30APRIL2010 $ll
total 7375936
-rw-r—–   1 oracle     dba        1493114880 Mar 26 14:41 backup_db_APXPRD1A_S_308_P_1_T_714667306
-rw-r—–   1 oracle     dba        47251456 Mar 26 14:42 backup_db_APXPRD1A_S_309_P_1_T_714667322
-rw-r—–   1 oracle     dba        2028044288 Mar 26 14:37 backup_db_SOAPRD1A_S_26837_P_1_T_714667044
-rw-r—–   1 oracle     dba        208044032 Mar 26 14:37 backup_db_SOAPRD1A_S_26838_P_1_T_714667060

Setting ORACLE_SID = APXPRD1A1
RMAN> catalog start with ‘/app/oracle/KEEP_UNTIl_30APRIL2010/backup_db_APXPRD1A_S_308_P_1_T_714667306’;

searching for all files that match the pattern /app/oracle/KEEP_UNTIl_30APRIL2010/backup_db_APXPRD1A_S_308_P_1_T_714667306

List of Files Unknown to the Database
=====================================
File Name: /app/oracle/KEEP_UNTIl_30APRIL2010/backup_db_APXPRD1A_S_308_P_1_T_714667306

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /app/oracle/KEEP_UNTIl_30APRIL2010/backup_db_APXPRD1A_S_308_P_1_T_714667306
RMAN-08591: WARNING: invalid archived log deletion policy

RMAN> catalog start with ‘/app/oracle/KEEP_UNTIl_30APRIL2010/backup_db_APXPRD1A_S_309_P_1_T_714667322’;

searching for all files that match the pattern /app/oracle/KEEP_UNTIl_30APRIL2010/backup_db_APXPRD1A_S_309_P_1_T_714667322

List of Files Unknown to the Database
=====================================
File Name: /app/oracle/KEEP_UNTIl_30APRIL2010/backup_db_APXPRD1A_S_309_P_1_T_714667322

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /app/oracle/KEEP_UNTIl_30APRIL2010/backup_db_APXPRD1A_S_309_P_1_T_714667322
RMAN-08591: WARNING: invalid archived log deletion policy

RMAN> list backup of database summary device type disk;
List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
——- — — – ———– ——————- ——- ——- ———- —
685     B  F  A DISK        2010-03-26:14:41:46 1       1       NO         BACKFULCOLD_APXPRD_26032010

Then set the keep until time as above

Advertisements

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

 
%d bloggers like this: