7. Using RMAN Commands
7.1 Using RMAN report Command
7.1.1 Report Need backup command
Create new tablespace : SQL> @cr_ts
RMAN> report need backup;
starting full resync of recovery catalog
full resync complete
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of files with less than 2 redundant backups
File #bkps Name
—- —– —————————————————–
1 1 +BIG_DATA/db1dup/datafile/system.260.807722583
2 1 +BIG_DATA/db1dup/datafile/sysaux.261.807722585
3 1 +BIG_DATA/db1dup/datafile/undotbs1.259.807722585
4 0 +BIG_DATA/db1dup/datafile/hh_test.277.808077061
#bkps = 0 for File 4 shows there is no backup for tablespace hh_test
RMAN> report need backup days 1;
Report of files whose recovery needs more than 1 days of archived logs
File Days Name
—- —– —————————————————–
–> All files are just backuped up
7.1.2 Report obsolete backup pieces
RMAN> report obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
——————– —— —————— ——————–
Backup Set 124 18-FEB-13 15:26:01
Backup Piece 125 18-FEB-13 15:26:01 +FRA/db1dup/autobackup/2013_02_18/s_807722759.369.807722761
Backup Set 137 18-FEB-13 16:44:23
Backup Piece 142 18-FEB-13 16:44:23 +FRA/db1dup/backupset/2013_02_18/annnf0_tag20130218t164416_0.367.807727463
7.1.3 Report tablespaces and related Datafiles
RMAN> report schema;
Report of database schema for database with db_unique_name DB1DUP
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 325 SYSTEM YES +BIG_DATA/db1dup/datafile/system.260.807722583
2 325 SYSAUX NO +BIG_DATA/db1dup/datafile/sysaux.261.807722585
3 200 UNDOTBS1 YES +BIG_DATA/db1dup/datafile/undotbs1.259.807722585
4 125 HH_TEST NO +BIG_DATA/db1dup/datafile/hh_test.277.808077061
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 20 TEMPTS1 20 +BIG_DATA/db1dup/tempfile/tempts1.270.807187371
7.1.4 Report an unreoverable tablespace
RMAN> report unrecoverable;
SQL> create table hh_emp nologging tablespace hh_test as select * from emp;
RMAN> report unrecoverable;
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
—- ———————– ———————————–
4 full +BIG_DATA/db1dup/datafile/hh_test.277.808077061
RMAN> backup tablespace hh_test;
RMAN> report unrecoverable;
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
—- ———————– ———————————–
7.2 Using the RMAN list Command
7.2.1 List Expired Backups
RMAN> list backup of expired database backups
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
——- — — – ———– —————— ——- ——- ———- —
3241 B F X DISK 22-FEB-13 18:23:41 1 1 NO TAG20130222T182323
% asmcmd rm +FRA/db1dup/backupset/2013_02_22/nnndf0_tag20130222t182323_0.405.808079011
RMAN> crosscheck backup of database;
RMAN> list expired backup of database summary;
RMAN> list expired backup of database summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
——- — — – ———– —————— ——- ——- ———- —
3241 B F X DISK 22-FEB-13 18:23:41 1 1 NO TAG20130222T182323
RMAN> delete expired backup;
using channel ORA_DISK_1
using channel ORA_DISK_2
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
——- ——- — — ———– ———– ———-
3244 3241 1 1 EXPIRED DISK +FRA/db1dup/backupset/2013_02_22/nnndf0_tag20130222t182323_0.405.808079011
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=+FRA/db1dup/backupset/2013_02_22/nnndf0_tag20130222t182323_0.405.808079011 RECID=118 STAMP=808079009
Deleted 1 EXPIRED objects
7.2.2 List incarnations
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
65 78 DB1DUP 1660019163 PARENT 688397 12-FEB-13 12:48:35
65 79 DB1DUP 1660019163 PARENT 736809 13-FEB-13 11:18:06
65 66 DB1DUP 1660019163 PARENT 1032840 18-FEB-13 15:25:22
65 769 DB1DUP 1660019163 ORPHAN 1088934 19-FEB-13 15:49:43
65 982 DB1DUP 1660019163 PARENT 1088934 19-FEB-13 17:34:28
65 1373 DB1DUP 1660019163 PARENT 1124692 20-FEB-13 09:13:25
65 1485 DB1DUP 1660019163 PARENT 1125805 20-FEB-13 10:36:44
65 2050 DB1DUP 1660019163 CURRENT 1200856 21-FEB-13 12:22:07
2175 2186 DB2DUP 3523769914 PARENT 1 04-FEB-13 18:25:34
2175 2176 DB2DUP 3523769914 PARENT 1128535 22-FEB-13 12:59:45
2175 2396 DB2DUP 3523769914 PARENT 1156912 22-FEB-13 16:12:05
2175 2740 DB2DUP 3523769914 CURRENT 1157590 22-FEB-13 16:29:20
7.2.3 List restore points
RMAN> list restore point all;
SCN RSP Time Type Time Name
—————- —————— ———- —————— —-
–> Flashback not yet enabled in database
7.2.4 List all script names
RMAN> list all script names;
List of Stored Scripts in Recovery Catalog
No scripts in recovery catalog
7.2.5 Other list commands
RMAN> list backup of database summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
——- — — – ———– —————— ——- ——- ———- —
2946 B F A DISK 22-FEB-13 17:44:07 1 1 NO TAG20130222T174355
2947 B F A DISK 22-FEB-13 17:44:09 1 1 NO TAG20130222T174355
2948 B F A DISK 22-FEB-13 17:44:17 1 1 NO TAG20130222T174355
3063 B F A DISK 22-FEB-13 18:18:07 1 1 NO TAG20130222T181759
3240 B F A DISK 22-FEB-13 18:23:39 1 1 NO TAG20130222T182323
3241 B F X DISK 22-FEB-13 18:23:41 1 1 NO TAG20130222T182323
RMAN> list backup of archivelog all summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
——- — — – ———– —————— ——- ——- ———- —
2963 B A A DISK 22-FEB-13 17:44:31 1 1 NO TAG20130222T174426
3206 B A A DISK 22-FEB-13 18:23:21 1 1 NO TAG20130222T182315
3207 B A A DISK 22-FEB-13 18:23:22 1 1 NO TAG20130222T182315
7.3 Monitoring and Tuning RMAN operations
SQL> select sid, serial#, opname, time_remaining from v$session_longops
where sid in ( select sid from v$session where program like ‘%rman%’ )
and time_remaining > 0;
SID SERIAL# OPNAME TIME_REMAINING
———- ———- —————————————- ————–
250 271 RMAN: aggregate input 873607
19 23 RMAN: full datafile backup 3
10 51 RMAN: full datafile backup 4
SQL> select sid, serial#, event, seconds_in_wait from v$session
where sid in ( select sid from v$session where program like ‘%rman%’ );
SID SERIAL# EVENT SECONDS_IN_WAIT
———- ———- —————————————- —————
2 103 SQL*Net message from client 4
10 51 RMAN backup & recovery I/O 0
19 23 RMAN backup & recovery I/O 0
250 271 SQL*Net message from client 7
SQL> select sid, event, wait_time from v$session_wait_history
where sid in ( select sid from v$session where program like ‘%rman%’ )
and wait_time > 0;
SID EVENT WAIT_TIME
———- —————————————- ———-
2 SQL*Net message from client 200
2 SQL*Net message from client 100
10 RMAN backup & recovery I/O 1
10 RMAN backup & recovery I/O 1
10 RMAN backup & recovery I/O 2
10 RMAN backup & recovery I/O 2
10 RMAN backup & recovery I/O 1
10 RMAN backup & recovery I/O 1
19 RMAN backup & recovery I/O 1
19 control file parallel write 3
19 control file parallel write 4
7.4 RMAN operations
7.4.1 Catalog a datase
7.4.2 Syncronize Control File and Recovery Catalog
RMAN> resync catalog;
7.4.3 Syncronize RMAN catalog and Control File records
RMAN> crosscheck backup of database
7.4.4 Delete expired backup
RMAN> delete expired backup;
7.4.5 Crosscheck and delete Sample
RMAN> list backup of database;
List of Backup Sets
===================
Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————
3717 Full 238.20M DISK 00:00:11 23-FEB-13 12:08:40
BP Key: 3720 Status: AVAILABLE Compressed: NO Tag: TAG20130223T120823
Piece Name: +FRA/db1dup/backupset/2013_02_23/nnndf0_tag20130223t120823_0.406.808142909
List of Datafiles in backup set 3717
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- —————— —-
2 Full 1329966 23-FEB-13 12:08:29 +BIG_DATA/db1dup/datafile/sysaux.261.807722585
3 Full 1329966 23-FEB-13 12:08:29 +BIG_DATA/db1dup/datafile/undotbs1.259.807722585
% asmcmd rm +FRA/db1dup/backupset/2013_02_23/nnndf0_tag20130223t120823_0.406.808142909
RMAN> crosscheck backup of database;
RMAN> list expired backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
——- — — – ———– —————— ——- ——- ———- —
3717 B F X DISK 23-FEB-13 12:08:40 1 1 NO TAG20130223T120823
RMAN> delete expired backup;
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
——- ——- — — ———– ———– ———-
3720 3717 1 1 EXPIRED DISK +FRA/db1dup/backupset/2013_02_23/nnndf0_tag20130223t120823_0.406.808142909
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=+FRA/db1dup/backupset/2013_02_23/nnndf0_tag20130223t120823_0.406.808142909 RECID=129 STAMP=808142909
Deleted 1 EXPIRED objects
7.4.6 Restore and Catalog a Backuppiece
RMAN> backup tablespace hh_test;
% asmcmd ls +FRA/db1dup/backupset/2013_02_24/
nnndf0_TAG20130224T114749_0.398.808228075
RMAN> list backup tag TAG20130224T114749;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————
6100 Full 1.10M DISK 00:00:02 24-FEB-13 11:47:56
BP Key: 6103 Status: AVAILABLE Compressed: NO Tag: TAG20130224T114749
Piece Name: +FRA/db1dup/backupset/2013_02_24/nnndf0_tag20130224t114749_0.398.808228075
List of Datafiles in backup set 6100
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- —————— —-
4 Full 1382507 24-FEB-13 11:47:54 +BIG_DATA/db1dup/datafile/hh_test.277.80807706
% asmcmd cp +FRA/db1dup/backupset/2013_02_24/nnndf0_TAG20130224T114749_0.398.808228075 /zpool_tmp/DB_BACKUP
% asmcmd rm ‘+FRA/db1dup/backupset/2013_02_24/*’;
RMAN> crosscheck backup of database;
RMAN> list expired backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
——- — — – ———– —————— ——- ——- ———- —
6100 B F X DISK 24-FEB-13 11:47:56 1 1 NO TAG20130224T114749
RMAN> delete expired backup
–> Restore and re-catalog backuppiece
% asmcmd mkdir ‘+FRA/db1dup/backupset/2013_02_24’;
% asmcmd cp ‘/zpool_tmp/DB_BACKUP/nnndf0_TAG20130224T114749_0.398.808228075’
+FRA/db1dup/backupset/2013_02_24/nnndf0_TAG20130224T114749_0
copying /zpool_tmp/DB_BACKUP/nnndf0_TAG20130224T114749_0.398.808228075
-> +FRA/db1dup/backupset/2013_02_24/nnndf0_TAG20130224T114749_0
% asmcmd ls +FRA/db1dup/backupset/2013_02_24/
nnndf0_TAG20130224T114749_0
RMAN> catalog backuppiece ‘+FRA/db1dup/backupset/2013_02_24/nnndf0_TAG20130224T114749_0’;
cataloged backup piece
backup piece handle=+FRA/db1dup/backupset/2013_02_24/nnndf0_tag20130224t114749_0 RECID=150 STAMP=808234337
RMAN> list backup tag TAG20130224T114749;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————
6504 Full 1.10M DISK 00:00:00 24-FEB-13 11:47:54
BP Key: 6508 Status: AVAILABLE Compressed: NO Tag: TAG20130224T114749
Piece Name: +FRA/db1dup/backupset/2013_02_24/nnndf0_tag20130224t114749_0
List of Datafiles in backup set 6504
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- —————— —-
4 Full 1382507 24-FEB-13 11:47:54 +BIG_DATA/db1dup/datafile/hh_test.277.808077061
7.4.7 Tuning RMAN Operations
7.4.7.1 Invalidate backup if not finished for pre-define time ( here we use 1 hour )
RMAN> backup duration 1:00 tablespace hh_test;
Starting backup at 24-FEB-13 13:46:13
Finished backup at 24-FEB-13 13:46:22
7.4.7.2 Minimize load by stretching backup operations ( here we used 1 Minute )
RMAN> backup duration 0:01 MINIMIZE LOAD tablespace hh_test;
Starting backup at 24-FEB-13 13:48:25
Finished backup at 24-FEB-13 13:49:25
7.5 Lessons learned in this section
- To avoid ORA-15056 during copying files from Filesytem to ASM your OMF-File name should not contain the file number/incarnation
Error:
supsunhh3% cp /zpool_tmp/DB_BACKUP/nnndf0_tag20130224t105240_0.402.808224767 +FRA/db1dup/backupset/2013_02_24/*
+FRA/db1dup/backupset/2013_02_24/: No such file or directory
supsunhh3% asmcmd cp /zpool_tmp/DB_BACKUP/nnndf0_tag20130224t105240_0.402.808224767 +FRA/db1dup/backupset/2013_02_24/
copying /zpool_tmp/DB_BACKUP/nnndf0_tag20130224t105240_0.402.808224767
-> +FRA/db1dup/backupset/2013_02_24/nnndf0_tag20130224t105240_0.402.808224767
ASMCMD-8016: copy source->’/zpool_tmp/DB_BACKUP/nnndf0_tag20130224t105240_0.402.808224767′
and target->’+FRA/db1dup/backupset/2013_02_24/nnndf0_tag20130224t105240_0.402.808224767′ failed
ORA-15056: additional error message
ORA-15046: ASM file name ‘+FRA/db1dup/backupset/2013_02_24/nnndf0_tag20130224t105240_0.402.808224767′
is not in single-file creation form
ORA-06512: at “SYS.X$DBMS_DISKGROUP”, line 413
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)Working sample :
supsunhh3% asmcmd cp /zpool_tmp/DB_BACKUP/nnndf0_tag20130224t105240_0.402.808224767
+FRA/db1dup/backupset/2013_02_24/nnndf0_tag20130224t105240_0
copying /zpool_tmp/DB_BACKUP/nnndf0_tag20130224t105240_0.402.808224767 ->
+FRA/db1dup/backupset/2013_02_24/nnndf0_tag20130224t105240_0
- #bkps Usage
#bkps 0 mean we have 1 backup : redundancy backup 0
#bkps 1 mean we have 1 backup : redundancy backup 1
Report of files with less than 2 redundant backups
File #bkps Name
—- —– —————————————————–
1 1 +BIG_DATA/db1dup/datafile/system.260.807722583
4 0 +BIG_DATA/db1dup/datafile/hh_test.277.808077061
- An expired backuppiece is as backup piece which was deleted from disk
Use following command to deal with :
RMAN> crosscheck backup of database;
RMAN> list expired backup summary;
- An obsolete backup becomes obsolete due to your retention policy
Use following command to deal with :
RMAN> report obsolete;
RMAN> delete noprompt obsolete;
- A backup using duration 2:00 partial minimize load will
– fail after 2 hours but already backed up datafiles will we usable
– RMAN will non-backed up datafile with higher priority during next run
– RMAN will not return any error despite duration expires