RMAN Practices - PART 4- Back to Basics
In this article we will be covering below topics
RMAN REPORT
Use the REPORT command to perform detailed analyses of the RMAN repository. RMAN writes the report to standard output or the message log file. The REPORT command provides various reports of RMAN backups. Use the REPORT command to answer important questions, such as: Which files need a backup? Which files have had unrecoverable operations performed on them? Which backups are obsolete and can be deleted? What was the physical schema of the target database or a database in the Data Guard environment at some previous time? Which files have not been backed up recently? => REPORT SCHEMA Lists the names of all data files (permanent and temporary) and tablespaces for the target database at the specified point in time. RMAN> report schema; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name TNT List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 920 SYSTEM YES +DATA/TNT/DATAFILE/system.282.1115902991 2 100 RO_TS NO +DATA/TNT/DATAFILE/ro_ts.294.1117385373 3 620 SYSAUX NO +DATA/TNT/DATAFILE/sysaux.283.1115903017 4 335 UNDOTBS1 YES +DATA/TNT/DATAFILE/undotbs1.284.1115903031 5 50 UNDOTBS2 YES +DATA/TNT/DATAFILE/undotbs2.290.1115904057 7 16 USERS NO +DATA/TNT/DATAFILE/users.285.1115903033 8 100 TS_NOLOG NO +DATA/TNT/DATAFILE/ts_nolog.295.1117461389 List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 35 TEMP 32767 +DATA/TNT/TEMPFILE/temp.289.1115903147 => REPORT NEEDBACKUP. Lists all data files in the specified operation that require a new backup. The report assumes that you restore the most recent backup. If you do not specify any option, then RMAN uses the current retention policy configuration. If the retention policy is disabled (CONFIGURE RETENTION POLICY TO NONE), then RMAN generates an error. RMAN> list backup of datafile 8; specification does not match any backup in the repository RMAN> report need backup datafile 8; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 Report of files with less than 1 redundant backups File #bkps Name ---- ----- ----------------------------------------------------- 8 0 +DATA/TNT/DATAFILE/ts_nolog.295.1117461389 RMAN> BACKUP DATABASE FORMAT '/ora_home/bkp/db_bkp_%d%u%s'; Starting backup at 07-OCT-22 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=156 instance=TNT1 device type=DISK allocated channel: ORA_DISK_2 .... .... Starting Control File and SPFILE Autobackup at 07-OCT-22 piece handle=+FRA/TNT/AUTOBACKUP/2022_10_07/s_1117462627.756.1117462629 comment=NONE Finished Control File and SPFILE Autobackup at 07-OCT-22 RMAN> report need backup datafile 8; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 Report of files with less than 1 redundant backups File #bkps Name ---- ----- ----------------------------------------------------- --need backup REDUNDANCY --Specifies the minimum number of backups or copies that must exist for a data file to be considered not in need of a backup. In other words, a data file needs a backup if there are fewer thaninteger
backups or copies of this file. For example,REDUNDANCY 2
means that if there are fewer than two copies or backups of a data file, then it needs a new backup. RMAN> report need backup REDUNDANCY 2 DATAFILE 8; Report of files with less than 2 redundant backups File #bkps Name ---- ----- ----------------------------------------------------- 8 1 +DATA/TNT/DATAFILE/ts_nolog.295.1117461389 RMAN> LIST BACKUP OF DATAFILE 8; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 230 Full 4.78M DISK 00:00:03 07-OCT-22 BP Key: 285 Status: AVAILABLE Compressed: NO Tag: TAG20221007T141657 Piece Name: /ora_home/bkp/db_bkp_TNT8r19m82q283 List of Datafiles in backup set 230 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 8 Full 2647318 07-OCT-22 NO +DATA/TNT/DATAFILE/ts_nolog.295.1117461389 RMAN> report need backup REDUNDANCY 1 DATAFILE 8; Report of files with less than 1 redundant backups File #bkps Name ---- ----- ----------------------------------------------------- RMAN> => REPORT OBSOLETE Lists full backups, data file copies, and archived redo log files recorded in the RMAN repository that can be deleted because they are no longer needed. RMAN> report obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 no obsolete backups found RMAN> backup database; Starting backup at 07-OCT-22 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=156 instance=TNT1 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=36 instance=TNT1 device type=DISK allocated channel: ORA_DISK_3 ... ... Finished backup at 07-OCT-22 Starting Control File and SPFILE Autobackup at 07-OCT-22 piece handle=+FRA/TNT/AUTOBACKUP/2022_10_07/s_1117463254.750.1117463255 comment=NONE Finished Control File and SPFILE Autobackup at 07-OCT-22 RMAN> report obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 Report of obsolete backups and copies Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Backup Set 231 07-OCT-22 Backup Piece 286 07-OCT-22 /ora_home/bkp/db_bkp_TNT8p19m82q281 Backup Set 232 07-OCT-22 Backup Piece 287 07-OCT-22 /ora_home/bkp/db_bkp_TNT8q19m82q282 Backup Set 230 07-OCT-22 Backup Piece 285 07-OCT-22 /ora_home/bkp/db_bkp_TNT8r19m82q283 Backup Set 233 07-OCT-22 Backup Piece 288 07-OCT-22 +FRA/TNT/AUTOBACKUP/2022_10_07/s_1117462627.756.1117462629 RMAN> report obsolete redundancy 1; Report of obsolete backups and copies Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Backup Set 231 07-OCT-22 Backup Piece 286 07-OCT-22 /ora_home/bkp/db_bkp_TNT8p19m82q281 Backup Set 232 07-OCT-22 Backup Piece 287 07-OCT-22 /ora_home/bkp/db_bkp_TNT8q19m82q282 Backup Set 230 07-OCT-22 Backup Piece 285 07-OCT-22 /ora_home/bkp/db_bkp_TNT8r19m82q283 Backup Set 233 07-OCT-22 Backup Piece 288 07-OCT-22 +FRA/TNT/AUTOBACKUP/2022_10_07/s_1117462627.756.1117462629 RMAN> report obsolete redundancy 2; no obsolete backups found RMAN> => REPORT UNRECOVERABLE Lists all unrecoverable data files. A data file is considered unrecoverable if an unrecoverable operation has been performed against an object residing in the data file since the last backup of the data file. In an unrecoverable operation, redo is not generated. Examples are direct load of table data and updates with the NOLOGGING option. When a data file has been changed by an unrecoverable operation, such as a direct load insert, normal media recovery cannot be used to recover the file, because an unrecoverable operation does not generate redo. You must perform either a full or incremental backup of affected data files after such operations, to ensure that data blocks affected by the unrecoverable operation can be recovered using RMAN. Note: The nonexistence of any backup of a data file is not sufficient reason to consider it unrecoverable. Such data files can be recovered with the CREATE DATAFILE command, if redo logs starting from when the file was created still exist. RMAN> REPORT UNRECOVERABLE; Report of files that need backup due to unrecoverable operations File Type of Backup Required Name ---- ----------------------- ----------------------------------- RMAN> [oracle@racsetn1 bkp]$ sqlplus "/as sysdba" SQL> create table testing nologging tablespace ts_nolog as select * from dba_objects; Table created. SQL> EXIT [oracle@racsetn1 bkp]$ rman target / connected to target database: TNT (DBID=1427195521) RMAN> REPORT UNRECOVERABLE; using target database control file instead of recovery catalog Report of files that need backup due to unrecoverable operations File Type of Backup Required Name ---- ----------------------- ----------------------------------- 8 full or incremental +DATA/TNT/DATAFILE/ts_nolog.295.1117461389 RMAN>
RMAN LIST
The LIST command uses the information in the RMAN repository to provide lists of backups and other objects relating to backup and recovery. The primary purpose of the LIST command is to list backup and copies. For example, you can list: Backups and proxy copies of a database, tablespace, data file, archived redo log, or control file Backups that have expired Backups restricted by time, path name, device type, tag, or recoverability Archived redo log files and disk copies => LIST BACKUP RMAN> list backup summary; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 230 B F A DISK 07-OCT-22 1 1 NO TAG20221007T141657 231 B F A DISK 07-OCT-22 1 1 NO TAG20221007T141657 232 B F A DISK 07-OCT-22 1 1 NO TAG20221007T141657 233 B F A DISK 07-OCT-22 1 1 NO TAG20221007T141707 234 B F A DISK 07-OCT-22 1 1 NO TAG20221007T142652 235 B F A DISK 07-OCT-22 1 1 NO TAG20221007T142652 236 B F A DISK 07-OCT-22 1 1 NO TAG20221007T142652 237 B F A DISK 07-OCT-22 1 1 NO TAG20221007T142733 RMAN> => LIST DB_UNIQUE_NAME RMAN> list db_unique_name of database; List of Databases DB Key DB Name DB ID Database Role Db_unique_name ------- ------- ----------------- --------------- ------------------ 2 TNT 1427195521 PRIMARY TNT RMAN> => LIST EXPIRED Displays backup sets, proxy copies, and image copies marked in the repository as EXPIRED, which means they were not found. To ensure that LIST EXPIRED shows up-to-date output, use a CROSSCHECK command periodically. When you use a CROSSCHECK command, RMAN searches disk and tape for backups and copies recorded in the repository. If it does not find them, then it updates their repository records to status EXPIRED. RMAN> backup datafile 8 format '/ora_home/bkp/file_8_%d%u%s'; Starting backup at 07-OCT-22 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00008 name=+DATA/TNT/DATAFILE/ts_nolog.295.1117461389 channel ORA_DISK_1: starting piece 1 at 07-OCT-22 channel ORA_DISK_1: finished piece 1 at 07-OCT-22 piece handle=/ora_home/bkp/file_8_TNT9319mb1g291 tag=TAG20221007T150728 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 07-OCT-22 RMAN> exit Recovery Manager complete. [oracle@racsetn1 bkp]$ cd /ora_home/bkp/ [oracle@racsetn1 bkp]$ ls -lrt total 1346092 drwxr-x---. 3 oracle asmadmin 4096 Oct 7 13:21 TNT -rw-r-----. 1 oracle asmadmin 5021696 Oct 7 14:17 db_bkp_TNT8r19m82q283 -rw-r-----. 1 oracle asmadmin 840032256 Oct 7 14:17 db_bkp_TNT8p19m82q281 -rw-r-----. 1 oracle asmadmin 507125760 Oct 7 14:17 db_bkp_TNT8q19m82q282 -rw-r-----. 1 oracle asmadmin 24846336 Oct 7 15:07 file_8_TNT9319mb1g291 [oracle@racsetn1 bkp]$ rm -rf file_8_TNT9319mb1g291 [oracle@racsetn1 bkp]$ [oracle@racsetn1 bkp]$ ls -lrt total 1321800 drwxr-x---. 3 oracle asmadmin 4096 Oct 7 13:21 TNT -rw-r-----. 1 oracle asmadmin 5021696 Oct 7 14:17 db_bkp_TNT8r19m82q283 -rw-r-----. 1 oracle asmadmin 840032256 Oct 7 14:17 db_bkp_TNT8p19m82q281 -rw-r-----. 1 oracle asmadmin 507125760 Oct 7 14:17 db_bkp_TNT8q19m82q282 [oracle@racsetn1 bkp]$ rman target / Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: TNT (DBID=1427195521) RMAN> crosscheck backup of datafile 8; using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=24 instance=TNT1 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=36 instance=TNT1 device type=DISK allocated channel: ORA_DISK_3 ... ... RMAN> list expired backup of datafile 8; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 240 Full 23.69M DISK 00:00:01 07-OCT-22 BP Key: 295 Status: EXPIRED Compressed: NO Tag: TAG20221007T150728 Piece Name: /ora_home/bkp/file_8_TNT9319mb1g291 List of Datafiles in backup set 240 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 8 Full 2652077 07-OCT-22 NO +DATA/TNT/DATAFILE/ts_nolog.295.1117461389 RMAN> => LIST INCARNATION RMAN> list incarnation; Displays information about the incarnations of a database. List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 TNT 1427195521 PARENT 1 17-APR-19 2 2 TNT 1427195521 CURRENT 1920977 20-SEP-22 RMAN> RMAN> list backup by file; List of Datafile Backups ======================== File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Compressed Tag ---- ------- - -- - ---------- --------- ------- ------- ---------- --- 1 235 B F A 2648115 07-OCT-22 1 1 NO TAG20221007T142652 231 B F A 2647313 07-OCT-22 1 1 NO TAG20221007T141657 2 234 B F A 2648120 07-OCT-22 1 1 NO TAG20221007T142652 230 B F A 2647318 07-OCT-22 1 1 NO TAG20221007T141657 3 236 B F A 2648117 07-OCT-22 1 1 NO TAG20221007T142652 232 B F A 2647315 07-OCT-22 1 1 NO TAG20221007T141657 4 234 B F A 2648120 07-OCT-22 1 1 NO TAG20221007T142652 230 B F A 2647318 07-OCT-22 1 1 NO TAG20221007T141657 5 236 B F A 2648117 07-OCT-22 1 1 NO TAG20221007T142652 232 B F A 2647315 07-OCT-22 1 1 NO TAG20221007T141657 7 236 B F A 2648117 07-OCT-22 1 1 NO TAG20221007T142652 232 B F A 2647315 07-OCT-22 1 1 NO TAG20221007T141657 8 240 B F X 2652077 07-OCT-22 1 1 NO TAG20221007T150728 238 B F A 2652011 07-OCT-22 1 1 NO TAG20221007T150649 234 B F A 2648120 07-OCT-22 1 1 NO TAG20221007T142652 230 B F A 2647318 07-OCT-22 1 1 NO TAG20221007T141657 List of Control File Backups ============================ CF Ckp SCN Ckp Time BS Key S #Pieces #Copies Compressed Tag ---------- --------- ------- - ------- ------- ---------- --- 2652086 07-OCT-22 241 A 1 1 NO TAG20221007T150730 2652022 07-OCT-22 239 A 1 1 NO TAG20221007T150653 2648164 07-OCT-22 237 A 1 1 NO TAG20221007T142733 2647343 07-OCT-22 233 A 1 1 NO TAG20221007T141707 List of SPFILE Backups ====================== Modification Time BS Key S #Pieces #Copies Compressed Tag ----------------- ------- - ------- ------- ---------- --- 07-OCT-22 241 A 1 1 NO TAG20221007T150730 07-OCT-22 239 A 1 1 NO TAG20221007T150653 07-OCT-22 237 A 1 1 NO TAG20221007T142733 07-OCT-22 233 A 1 1 NO TAG20221007T141707 RMAN>
RMAN CROSSCHECK
Updating the RMAN Repository Several situations can cause a discrepancy between the repository and the files that it records, including tape or disk failures and user-managed copies or deletions of RMAN-related files. This section explains how to ensure that the RMAN repository accurately reflects the reality of the RMAN-related files stored on disk and tape. Crosschecking the RMAN Repository To ensure that data about backups in the recovery catalog or control file is synchronized with corresponding data on disk or in the media management catalog, perform a crosscheck. The CROSSCHECK command operates only on files that are currently recorded in the RMAN repository. About RMAN Crosschecks Crosschecks update outdated RMAN repository information about backups whose repository records do not match their physical status. For example, if a user removes archived logs from disk with an operating system command, the repository still indicates that the logs are on disk, when in fact they are not. You can issue the DELETE EXPIRED command to delete all expired backups. RMAN removes the record for the expired file from the repository. If for some reason the file still exists on the media, then RMAN issues warnings and lists the mismatched objects that cannot be deleted. In this case you can run the following commands to crosscheck both disk and SBT: RMAN> CROSSCHECK BACKUP OF DATABASE; allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=282 instance=TNT1 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=290 instance=TNT1 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=412 instance=TNT1 device type=DISK crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/ora_home/bkp/db_bkp_TNT8p19m82q281 RECID=286 STAMP=1117462619 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/ora_home/bkp/db_bkp_TNT8r19m82q283 RECID=285 STAMP=1117462620 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/ora_home/bkp/db_bkp_TNT8q19m82q282 RECID=287 STAMP=1117462619 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/TNT/BACKUPSET/2022_10_07/nnndf0_tag20221007t142652_0.774.1117463219 RECID=289 STAMP=1117463218 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/TNT/BACKUPSET/2022_10_07/nnndf0_tag20221007t142652_0.773.1117463213 RECID=290 STAMP=1117463213 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/TNT/BACKUPSET/2022_10_07/nnndf0_tag20221007t142652_0.752.1117463215 RECID=291 STAMP=1117463214 Crosschecked 2 objects crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/ora_home/bkp/file_8_TNT9319mb1g291 RECID=295 STAMP=1117465649 Crosschecked 3 objects crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/TNT/BACKUPSET/2022_10_07/nnndf0_tag20221007t150649_0.741.1117465611 RECID=293 STAMP=1117465610 Crosschecked 3 objects RMAN> EXIT Recovery Manager complete. [oracle@racsetn1 ~]$ ls -lrt /ora_home/bkp/file_8_TNT9319mb1g291 ls: cannot access /ora_home/bkp/file_8_TNT9319mb1g291: No such file or directory [oracle@racsetn1 ~]$ Several commands for CROSSCHECK as below. CROSSCHECK BACKUP; # checks backup sets, proxy copies, and image copies CROSSCHECK COPY OF DATABASE; CROSSCHECK BACKUPSET 1338, 1339, 1340; CROSSCHECK BACKUPPIECE TAG 'nightly_backup'; CROSSCHECK BACKUP OF ARCHIVELOG ALL SPFILE; CROSSCHECK BACKUP OF DATAFILE "?/oradata/trgt/system01.dbf" COMPLETED AFTER 'SYSDATE-14'; CROSSCHECK BACKUPSET DEVICE TYPE disk BACKED UP 3 TIMES TO sbt; CROSSCHECK BACKUP OF DATABASE BACKED UP 2 TIMES TO sbt; CROSSCHECK CONTROLFILECOPY '/tmp/control01.ctl'; CROSSCHECK DATAFILECOPY 113, 114, 115; CROSSCHECK PROXY 789;
RMAN CHANGE ... AVAILABLE/UNAVAILABLE
Changing the Repository Status of Backups and Copies RMAN provides multiple methods of changing the repository status of backups and copies. Perform any of the following tasks to change the repository status of backups and copies: Making backups available or unavailable You can change the status of a backup if it becomes temporarily available or unavailable. For example, if a mounted disk undergoes maintenance, then you can update the records for backups on the disk to status UNAVAILABLE. Including or exempting backups from the retention policy Archival backups can be created by using the KEEP clause to exempt backups from the configured retention policy. You can also change the status of an archival backup and subsequently include it in the configured retention policy. Updating a Backup to Status AVAILABLE or UNAVAILABLE Run the CHANGE...UNAVAILABLE command when a backup cannot be found or has migrated offsite. RMAN does not use files with status UNAVAILABLE in RESTORE or RECOVER commands. If the file is later found or returns to the main site, then you can update its status again by issuing CHANGE...AVAILABLE . The files in the fast recovery area cannot be marked as UNAVAILABLE. To update the status of a file in the repository to UNAVAILABLE or AVAILABLE: Issue a LIST command to determine the availability status of RMAN backups. For example, issue the following command to list all backups: LIST BACKUP; Run CHANGE with the UNAVAILABLE or AVAILABLE keyword to update its status in the RMAN repository. The following examples illustrate forms of the CHANGE command: CHANGE DATAFILECOPY '/tmp/control01.ctl' UNAVAILABLE; CHANGE COPY OF ARCHIVELOG SEQUENCE BETWEEN 1000 AND 1012 UNAVAILABLE; CHANGE BACKUPSET 12 UNAVAILABLE; CHANGE BACKUP OF SPFILE TAG "TAG20120208T154556" UNAVAILABLE; CHANGE DATAFILECOPY '/tmp/system01.dbf' AVAILABLE; CHANGE BACKUPSET 12 AVAILABLE; CHANGE BACKUP OF SPFILE TAG "TAG20120208T154556" AVAILABLE; Example. RMAN> REPORT SCHEMA; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name TNT List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 920 SYSTEM YES +DATA/TNT/DATAFILE/system.282.1115902991 2 100 RO_TS NO +DATA/TNT/DATAFILE/ro_ts.294.1117385373 3 630 SYSAUX NO +DATA/TNT/DATAFILE/sysaux.283.1115903017 4 335 UNDOTBS1 YES +DATA/TNT/DATAFILE/undotbs1.284.1115903031 5 50 UNDOTBS2 YES +DATA/TNT/DATAFILE/undotbs2.290.1115904057 7 16 USERS NO +DATA/TNT/DATAFILE/users.285.1115903033 8 100 TS_NOLOG NO +DATA/TNT/DATAFILE/ts_nolog.295.1117461389 List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 35 TEMP 32767 +DATA/TNT/TEMPFILE/temp.289.1115903147 RMAN> BACKUP AS COPY DATAFILE 8 format '/ora_home/bkp/file_%d%u%s'; Starting backup at 07-OCT-22 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=412 instance=TNT1 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=282 instance=TNT1 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=268 instance=TNT1 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00008 name=+DATA/TNT/DATAFILE/ts_nolog.295.1117461389 output file name=/ora_home/bkp/file_TNT9519mj83293 tag=TAG20221007T172730 RECID=10 STAMP=1117474051 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 07-OCT-22 RMAN> list copy of datafile 8; List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time Sparse ------- ---- - --------------- ---------- --------------- ------ 10 8 A 07-OCT-22 2767813 07-OCT-22 NO Name: /ora_home/bkp/file_TNT9519mj83293 Tag: TAG20221007T172730 RMAN> change datafilecopy '/ora_home/bkp/file_TNT9519mj83293' unavailable; changed datafile copy unavailable datafile copy file name=/ora_home/bkp/file_TNT9519mj83293 RECID=10 STAMP=1117474051 Changed 1 objects to UNAVAILABLE status RMAN> list copy of datafile 8; List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time Sparse ------- ---- - --------------- ---------- --------------- ------ 10 8 U 07-OCT-22 2767813 07-OCT-22 NO Name: /ora_home/bkp/file_TNT9519mj83293 Tag: TAG20221007T172730 RMAN> change datafilecopy '/ora_home/bkp/file_TNT9519mj83293' available; released channel: ORA_DISK_1 released channel: ORA_DISK_2 released channel: ORA_DISK_3 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=412 instance=TNT1 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=282 instance=TNT1 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=268 instance=TNT1 device type=DISK changed datafile copy available datafile copy file name=/ora_home/bkp/file_TNT9519mj83293 RECID=10 STAMP=1117474051 Changed 1 objects to AVAILABLE status RMAN> list copy of datafile 8; List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time Sparse ------- ---- - --------------- ---------- --------------- ------ 10 8 A 07-OCT-22 2767813 07-OCT-22 NO Name: /ora_home/bkp/file_TNT9519mj83293 Tag: TAG20221007T172730
RMAN CHANGE KEEP/NOKEEP
Changing the Status of an Archival Backup You can designate backups as exempt from the retention policy. This technique is useful for archiving backups to comply with business requirements. An archival backup is still a fully valid backup, however, and can be restored just as any other RMAN backup. Note: The KEEP FOREVER clause requires the use of a recovery catalog, because the control file cannot contain an infinitely large set of RMAN repository data. You can use the CHANGE command to alter the KEEP status of an existing backup. For example, you may decide that you no longer want to keep a long-term backup. The same options available for BACKUP...KEEP are available with CHANGE...KEEP . You cannot set KEEP attributes for backup sets or files stored in the fast recovery area. To alter the KEEP status of an archival backup: Issue a LIST command to list the backups. For example, issue the following command to list all backups: LIST BACKUP; Issue a CHANGE...KEEP command to define a different retention period for this backup, or a CHANGE...NOKEEP command to let the retention policy apply to this file. This example allows a backup set to be subject to the backup retention policy: CHANGE BACKUPSET 231 NOKEEP; This example makes a data file copy exempt from the retention policy for 180 days: CHANGE DATAFILECOPY '/tmp/system01.dbf' KEEP UNTIL TIME 'SYSDATE+180';
RMAN CATALOG & UNCATALOG
Adding Backup Records to the RMAN Repository
You can use the CATALOG command to make RMAN aware of the existence of archived logs not recorded in the repository or copies of database files that are created through means other than RMAN.
About Cataloging Operations
The target database control file keeps records of all archived redo logs generated by the target database and all RMAN backups.
The purpose of the CATALOG command is to add metadata to the repository when it does not have a record of files for RMAN to manage.
Run the RMAN CATALOG command when:
You use an operating system utility to make copies of data files, archived logs, or backup pieces. In this case, the repository has no record of them.
You perform recovery with a backup control file and you change the archiving destination or format during recovery.
In this situation, the repository does not have information about archived logs needed for recovery, and you must catalog these logs.
You want to catalog data file copy as a level 0 backup, thus enabling you to perform an incremental backup later by using the data file copy as the base of an incremental backup strategy.
--
Cataloging User-Managed Data File Copies
Use the CATALOG command to propagate information about user-managed copies to the RMAN repository.
To create and catalog a user-managed copy of a data file:
Make a data file copy with an operating system utility. ALTER TABLESPACE BEGIN/END BACKUP is necessary if the database is open and the data files are online while the backup is in progress.
This example backs up an online data file, using the SQL*Plus HOST command to issue an operating system command.
SQL> ALTER TABLESPACE users BEGIN BACKUP;
SQL> HOST CP $ORACLE_HOME/oradata/trgt/users01.dbf /tmp/users01.dbf;
SQL> ALTER TABLESPACE users END BACKUP;
Start RMAN and connect to a target database and recovery catalog (if used).
Run the CATALOG command.
For example, enter the following command to catalog a user-managed data file copy:
CATALOG DATAFILECOPY '/tmp/users01.dbf';
EXAMPLE
RMAN> list copy of datafile 8;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time Sparse
------- ---- - --------------- ---------- --------------- ------
10 8 A 07-OCT-22 2767813 07-OCT-22 NO
Name: /ora_home/bkp/file_TNT9519mj83293
Tag: TAG20221007T172730
RMAN> EXIT
Recovery Manager complete.
[oracle@racsetn1 ~]$ cd /ora_home/bkp
[oracle@racsetn1 bkp]$ ls -lrt
total 1424312
drwxr-x---. 3 oracle asmadmin 4096 Oct 7 13:21 TNT
-rw-r-----. 1 oracle asmadmin 5021696 Oct 7 14:17 db_bkp_TNT8r19m82q283
-rw-r-----. 1 oracle asmadmin 840032256 Oct 7 14:17 db_bkp_TNT8p19m82q281
-rw-r-----. 1 oracle asmadmin 507125760 Oct 7 14:17 db_bkp_TNT8q19m82q282
-rw-r-----. 1 oracle asmadmin 104865792 Oct 7 17:27 file_TNT9519mj83293
[oracle@racsetn1 bkp]$
[oracle@racsetn1 bkp]$ mv file_TNT9519mj83293 TNT/
RMAN> crosscheck copy of datafile 8;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=412 instance=TNT1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=268 instance=TNT1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=290 instance=TNT1 device type=DISK
validation failed for datafile copy
datafile copy file name=/ora_home/bkp/file_TNT9519mj83293 RECID=10 STAMP=1117474051
Crosschecked 1 objects
RMAN> delete expired copy of datafile 8;
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=412 instance=TNT1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=268 instance=TNT1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=290 instance=TNT1 device type=DISK
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time Sparse
------- ---- - --------------- ---------- --------------- ------
10 8 X 07-OCT-22 2767813 07-OCT-22 NO
Name: /ora_home/bkp/file_TNT9519mj83293
Tag: TAG20221007T172730
Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy file name=/ora_home/bkp/file_TNT9519mj83293 RECID=10 STAMP=1117474051
Deleted 1 EXPIRED objects
RMAN> list copy of datafile 8;
specification does not match any datafile copy in the repository
RMAN> exit
Recovery Manager complete.
[oracle@racsetn1 bkp]$ cd TNT/
[oracle@racsetn1 TNT]$ ls -lrt
total 103884
drwxr-x---. 3 oracle asmadmin 4096 Oct 6 15:11 backupset
-rw-r-----. 1 oracle asmadmin 1179648 Oct 7 13:09 dbfile2_TNT7q19m456250
-rw-r-----. 1 oracle asmadmin 73728 Oct 7 13:12 dbfile2_TNT7s19m4a5252
-rw-r-----. 1 oracle asmadmin 73728 Oct 7 13:16 dbfile2_TNT7u19m4hp254
-rw-r-----. 1 oracle asmadmin 57344 Oct 7 13:20 dbfile2_TNT8419m4p4260
-rw-r-----. 1 oracle asmadmin 104865792 Oct 7 17:27 file_TNT9519mj83293
[oracle@racsetn1 TNT]$ mv file_TNT9519mj83293 ../
[oracle@racsetn1 TNT]$ cd ..
[oracle@racsetn1 bkp]$ ls -lrt
total 1424312
-rw-r-----. 1 oracle asmadmin 5021696 Oct 7 14:17 db_bkp_TNT8r19m82q283
-rw-r-----. 1 oracle asmadmin 840032256 Oct 7 14:17 db_bkp_TNT8p19m82q281
-rw-r-----. 1 oracle asmadmin 507125760 Oct 7 14:17 db_bkp_TNT8q19m82q282
-rw-r-----. 1 oracle asmadmin 104865792 Oct 7 17:27 file_TNT9519mj83293
drwxr-x---. 3 oracle asmadmin 4096 Oct 7 17:40 TNT
[oracle@racsetn1 bkp]$
[oracle@racsetn1 bkp]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Oct 7 17:41:06 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: TNT (DBID=1427195521)
RMAN> crosscheck copy of datafile 8;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=412 instance=TNT1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=290 instance=TNT1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=282 instance=TNT1 device type=DISK
specification does not match any datafile copy in the repository
RMAN> catalog datafilecopy '/ora_home/bkp/file_TNT9519mj83293';
cataloged datafile copy
datafile copy file name=/ora_home/bkp/file_TNT9519mj83293 RECID=11 STAMP=1117474935
RMAN> crosscheck copy of datafile 8;
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=412 instance=TNT1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=290 instance=TNT1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=282 instance=TNT1 device type=DISK
validation succeeded for datafile copy
datafile copy file name=/ora_home/bkp/file_TNT9519mj83293 RECID=11 STAMP=1117474935
Crosschecked 1 objects
RMAN> LIST COPY OF DATAFILE 8;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time Sparse
------- ---- - --------------- ---------- --------------- ------
11 8 A 07-OCT-22 2767813 07-OCT-22 NO
Name: /ora_home/bkp/file_TNT9519mj83293
Tag: TAG20221007T172730
RMAN>
Cataloging Backup Pieces
You can catalog backup pieces on disk. This technique is useful if you use an operating system utility to copy backup pieces from one location to another on the same host, or from one host to another.
You can even catalog a backup piece from a prior incarnation of the database. RMAN can determine whether that backup piece can be used during a subsequent restore and recovery operation.
To catalog a backup piece:
Start RMAN and connect to a target database and recovery catalog (if used).
Catalog the file names of the backup pieces.
For example, enter the following command:
CATALOG BACKUPPIECE '/disk2/09dtq55d_1_2', '/disk2/0bdtqdou_1_1';
Cataloging All Files in a Disk Location
The CATALOG START WITH command enables you to search through all files in an ASM disk group, Oracle Managed Files location, or traditional file system directory and investigate those that are not recorded in the RMAN repository.
The CATALOG RECOVERY AREA command enables you to catalog all files in the recovery area.
Typically, you do not need to run this command manually because RMAN automatically runs it as needed, for example, when you restore or create a control file.
You can run this command when files are copied into the fast recovery area by operating system utilities.
For example, enter the following commands:
CATALOG START WITH '+disk'; # catalog all files from an ASM disk group
CATALOG START WITH '/fs1/datafiles/'; # catalog all files in directory
You can use the CATALOG RECOVERY AREA command to catalog all files in the recovery area.
During this operation, any files in the recovery area not listed in the RMAN repository are added. For example:
CATALOG RECOVERY AREA;
Run a LIST command to verify that the files were cataloged.
Removing Records from the RMAN Repository
You can remove records for files from the RMAN repository. About Uncataloging Operations in the RMAN Repository Run the CHANGE...UNCATALOG command to perform the following actions on RMAN repository records: Update a backup record in the control file repository to status DELETED Delete a specific backup record from the recovery catalog (if you use one) RMAN does not change the specified physical files: it only alters the repository records for these files. You can use this command when you have deleted a backup through a means other than RMAN. For example, if you delete archived redo logs with an operating system utility, then remove the record for this log from the repository by issuing a CHANGE ARCHIVELOG ... UNCATALOG command. Removing Records for Files Deleted with Operating System Utilities You can use the CHANGE ... UNCATALOG command to update the RMAN repository for the absent files. To remove the catalog record for a backup or archived redo log: Run a CHANGE ... UNCATALOG command for the backups that you deleted from the operating system with operating system commands. This example deletes repository references to disk copies of the control file and data file 1: CHANGE CONTROLFILECOPY '/tmp/control01.ctl' UNCATALOG; CHANGE DATAFILECOPY '/tmp/system01.dbf' UNCATALOG; CHANGE BACKUPSET '/disk1/oradata/backups/db1_full.bkp' UNCATALOG; Optionally, view the relevant recovery catalog view, for example, RC_DATAFILE_COPY or RC_CONTROLFILE_COPY, to confirm that a given record was removed. This query confirms that the record of copy 4833 was removed: SELECT CDF_KEY, STATUS FROM RC_DATAFILE_COPY WHERE CDF_KEY = 4833; CDF_KEY STATUS ---------- ------ 0 rows selected. RMAN> RMAN> CHANGE DATAFILECOPY '/ora_home/bkp/file_TNT9519mj83293' UNCATALOG; using target database control file instead of recovery catalog uncataloged datafile copy datafile copy file name=/ora_home/bkp/file_TNT9519mj83293 RECID=11 STAMP=1117474935 Uncataloged 1 objects RMAN> LIST COPY OF DATAFILE 8; specification does not match any datafile copy in the repository RMAN>
RMAN DELETE
Deleting RMAN Backups and Archived Redo Logs You can use the RMAN DELETE command to delete archived redo logs and RMAN backups. For backups on disk, deleting backups physically removes the backup file from disk. For backups on SBT devices, the RMAN DELETE command instructs the media manager to delete the backup pieces or proxy copies on tape. In either case, RMAN updates the RMAN repository to reflect the deletion. Overview of Deleting RMAN Backups Every RMAN backup produces a corresponding record in the RMAN repository. This record is stored in the control file. When you use an RMAN command to delete a backup or archived redo log file, RMAN does the following: Removes the physical file from the operating system (if the file is still present) Updates the file records in the control file to status DELETED Removes the file records from the recovery catalog tables (if RMAN is connected to a recovery catalog) Because of the way that control file data is stored, RMAN cannot remove the record from the control file, only update it to DELETED status. Because the recovery catalog tables are ordinary database tables, however, RMAN deletes rows from them in the same way that rows are deleted from any table. About RMAN Deletion Commands You can delete backups and recovery catalog records for backups. The following table describes the RMAN commands that can delete backups. DELETE To delete backups, update the control file records to status DELETED, and remove their records from the recovery catalog (if a recovery catalog is used). You can specify that DELETE removes backups that are EXPIRED or OBSOLETE. If you run DELETE EXPIRED on a backup that exists, then RMAN issues a warning and does not delete the backup. If you use the DELETE command with the optional FORCE keyword, then RMAN deletes the specified backups, but ignores any I/O errors, including those that occur when a backup is missing from disk or tape. [oracle@racsetn1 bkp]$ [oracle@racsetn1 bkp]$ ls -lrt /ora_home/bkp/file_TNT9519mj83293 -rw-r-----. 1 oracle asmadmin 104865792 Oct 7 17:27 /ora_home/bkp/file_TNT9519mj83293 [oracle@racsetn1 bkp]$ rman target / connected to target database: TNT (DBID=1427195521) RMAN> DELETE COPY OF DATAFILE 8; allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=379 instance=TNT1 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=34 instance=TNT1 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=160 instance=TNT1 device type=DISK List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time Sparse ------- ---- - --------------- ---------- --------------- ------ 12 8 A 07-OCT-22 2767813 07-OCT-22 NO Name: /ora_home/bkp/file_TNT9519mj83293 Tag: TAG20221007T172730 Do you really want to delete the above objects (enter YES or NO)? YES deleted datafile copy datafile copy file name=/ora_home/bkp/file_TNT9519mj83293 RECID=12 STAMP=1117477905 Deleted 1 objects RMAN> exit Recovery Manager complete. [oracle@racsetn1 bkp]$ [oracle@racsetn1 bkp]$ ls -lrt /ora_home/bkp/file_TNT9519mj83293 ls: cannot access /ora_home/bkp/file_TNT9519mj83293: No such file or directory [oracle@racsetn1 bkp]$ BACKUP...DELETE [ALL] INPUT To back up archived logs, data file copies, or backup sets, then delete the input files from the operating system after the successful completion of the backup. RMAN also deletes and updates repository records for the deleted input files. If you specify DELETE INPUT (without ALL), then RMAN deletes only the specific files that it backs up. If you specify ALL INPUT, then RMAN deletes all copies of the files recorded in the RMAN repository. RMAN> RMAN> backup datafile 8 delete input; Starting backup at 07-OCT-22 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=379 instance=TNT1 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=34 instance=TNT1 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=154 instance=TNT1 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup command at 10/07/2022 18:33:03 RMAN-06030: the DELETE [ALL] INPUT option may not be used with a datafile backup set RMAN> CHANGE...UNCATALOG To delete recovery catalog records for specified backups and change their control file records to status DELETED. The CHANGE...UNCATALOG command only changes the RMAN repository record of backups, and does not actually delete backups. The RMAN repository record for an object can sometimes fail to reflect the physical status of the object. For example, you back up an archived redo log to disk and then use an operating system utility to delete it. If you run DELETE without first running CROSSCHECK, then the repository erroneously lists the log as AVAILABLE. If you run RMAN interactively, then RMAN asks for confirmation before deleting any files. You can suppress these confirmations by using the NOPROMPT keyword with any form of the BACKUP command: DELETE NOPROMPT ARCHIVELOG ALL; == About Deletion of Archived Redo Logs You can delete eligible logs from any location, inside or outside the recovery area, with BACKUP ... DELETE INPUT or DELETE ARCHIVELOG. Both of these commands obey the archive redo log deletion policy when the policy is any setting other than NONE. You can override the archived redo log deletion policy settings by using the FORCE option in the DELETE command. Deleting All Backups and Copies Use the RMAN DELETE command to delete backups and image copies. In some circumstances, you may need to delete all backup sets, proxy copies, and image copies associated with a database. For example, you no longer need a database and want to remove all related files from the system. An image copy is a file generated with BACKUP AS COPY command, a log archived by the database, or a file cataloged with the CATALOG command. To delete all backups and copies: DELETE BACKUP; DELETE COPY; Deleting Specified Backups and Copies You can use both the DELETE and BACKUP ... DELETE commands to delete specific backups and copies. The BACKUP ... DELETE command backs up the files first, typically to tape, and then deletes the input files afterward. The DELETE command supports a wide range of options to identify objects to delete. When deleting archived redo logs, RMAN uses the configured settings to determine whether a log can be deleted. DELETE BACKUPPIECE 101; Deleting backups by file name on disk: DELETE CONTROLFILECOPY '/tmp/control01.ctl'; Deleting archived redo logs: DELETE NOPROMPT ARCHIVELOG UNTIL SEQUENCE 300; Deleting backups based on tags: DELETE BACKUP TAG 'before_upgrade'; Deleting backups based on the objects backed up and the media or disk location where the backup is stored: DELETE BACKUP OF TABLESPACE users DEVICE TYPE sbt; # delete only from tape DELETE COPY OF CONTROLFILE LIKE '/tmp/%'; Deleting archived redo logs from disk based on whether they are backed up on tape: DELETE ARCHIVELOG ALL BACKED UP 3 TIMES TO sbt; Deleting backup sets that were backed up twice to tape: DELETE BACKUPSET DEVICE TYPE disk BACKED UP 2 TIMES TO sbt; Deleting backups of the target database that were backed up once to tape: DELETE BACKUP OF DATABASE DEVICE TYPE disk BACKED UP 1 TIMES TO sbt; Deleting Specified Files with BACKUP ... DELETE You can use BACKUP ... DELETE to back up archived redo logs, data file copies, or backup sets and then delete the input files after successfully backing them up. Specifying the DELETE INPUT option is equivalent to issuing the DELETE command for the input files. RMAN uses the configured settings to determine whether an archived redo log can be deleted.The ALL option in the DELETE ALL INPUT clause applies only to archived redo logs. If you run BACKUP ... DELETE ALL INPUT, then the command deletes all copies of corresponding archived redo logs or data file copies that match the selection criteria in the BACKUP command. Deleting Expired RMAN Backups and Copies If you run CROSSCHECK, and if RMAN cannot locate the files, then it updates their records in the RMAN repository to EXPIRED status. You can then use the DELETE EXPIRED command to remove records of expired backups and copies from the RMAN repository. To delete expired repository records: CROSSCHECK BACKUP; Delete the expired backups. For example, issue: DELETE EXPIRED BACKUP; Deleting Obsolete RMAN Backups Based on Retention Policies The RMAN DELETE command supports an OBSOLETE option, which deletes backups that are no longer needed to satisfy specified recoverability requirements. You can delete files that are obsolete according to the configured default retention policy, or another retention policy that you specify as an option to the DELETE OBSOLETE command. As with other forms of the DELETE command, the files deleted are removed from backup media, deleted from the recovery catalog, and marked as DELETED in the control file. If you specify the DELETE OBSOLETE command with no arguments, then RMAN deletes all obsolete backups defined by the configured retention policy. For example: DELETE OBSOLETE; DELETE OBSOLETE Behavior When KEEP UNTIL TIME Expires If the KEEP UNTIL TIME period has not expired for an archival backup, RMAN does not consider the backup as obsolete. As soon as the KEEP UNTIL period expires, however, the backup is immediately considered to be obsolete, regardless of any configured backup retention policy. Thus, DELETE OBSOLETE deletes any backup created with BACKUP ... KEEP UNTIL TIME if the KEEP time has expired.
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444