Basic RMAN Commands - Handy

Note:  Proofread any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any damage.

Commands	Description
LIST	LIST BACKUP; #List all your backup sets. LIST BACKUPSET; #Lists only backup sets and proxy copies. LIST COPY; #Lists of Image copies and Archive Logs. LIST EXPIRED BACKUP; #Backups did not found after crosscheck. That is backup is manually moved or deleted from OS. LIST EXPIRED BACKUPSET; LIST RECOVERABLE BACKUPSET; LIST BACKUP BY FILE; #List backup by Datafile, controlfile, spfile. LIST BACKUP SUMMARY; #Lists backup sets, proxy copies, and disk copies. LIST BACKUP OF DATABASE; LIST BACKUP LIKE '/tmp/%'; list backup of datafile 1; LIST ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE sbt; LIST COPY OF DATAFILE 2 COMPLETED BETWEEN '17-MAR-2008' AND '22-MAR-2008'; are also available. LIST INCARNATION; LIST INCARNATION OF DATABASE; to see the incarnations of your database. ADVICE FAILURE;
REPORT	REPORT NEED BACKUP; # Determine which database files need backup under a specific retention policy. # REPORT UNRECOVERABLE; #Report which database files require backup because they have been affected by some NOLOGGING operation. REPORT SCHEMA; #Lists and displays information about the database files. REPORT OBSOLETE; #REPORT OBSOLETE displays the backups that are obsolete according to the current retention policy REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE SKIP TABLESPACE data01; REPORT SCHEMA AT TIME 'SYSDATE-1';
DELETE	DELETE BACKUPSET ALL; - Delete all Backup setDELETE COPY ALL; -Delete all image copies DELETE OBSOLETE; -Delete obsolete Backups BACKUP ARCHIVELOG ALL DELETE ALL INPUT; DELETE ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE SBT_TAPE;
CROSSCHECK	CROSSCHECK BACKUP; CROSSCHECK ARCHIVELOG ALL; DELETE EXPIRED BACKUP; DELETE EXPIRED ARCHIVELOG ALL
Expired and Obsolete	Expired and Obselete difference - DELETE removes the physical files from the backup media, deletes the record of the backup from the recovery catalog (if RMAN is connected to a recovery catalog), and updates the records of these backups in the control file to status DELETED. in the obsolete case, what helps to clarify the difference between OBSOLETE and EXPIRED is the retention policy. If a backup item is no longer needed for recovery because it is older than the retention policy then it is obsolete. What does DELETE OBSOLETE do? The RMAN DELETE command supports an OBSOLETE option, which deletes backups that are obsolete, that is, no longer needed to satisfy specified recoverability requirements. You can delete files 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. The next clarification concerns the EXPIRED status. How does an object become expired? When the CROSSCHECK command is used to determine whether backups recorded in the repository still exist on disk or tape, if RMAN cannot locate the backups, 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 from the RMAN repository. If the expired files still exist, then the DELETE EXPIRED command terminates with an error.
Delete Backup from Disk	ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK; CROSSCHECK BACKUPSET; CROSSCHECK COPY; CROSSCHECK ARCHIVELOG ALL; DELETE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK; DELETE NOPROMPT EXPIRED ARCHIVELOG ALL DEVICE TYPE DISK
Delete Backup from TAPE Drive	ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE SBT; CROSSCHECK BACKUP; CROSSCHECK COPY; CROSSCHECK ARCHIVELOG ALL; DELETE NOPROMPT EXPIRED BACKUP DEVICE TYPE SBT; DELETE NOPROMPT EXPIRED ARCHIVELOG ALL DEVICE TYPE SBT;
COMPRESSED BACKUP	BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG DELETE INPUT;
RECOVER SYSTEM DATAFILE	System Datafile Recovery - 1. startup mount; 2. restore datafile 1; 3. recover datafile 1; 4. alter database open;
RECOVER NORMAL DATAFILE	1. restore datafile 4; 2. recover datafile 4; 3. sql 'alter tablespace users online' ;
RECOVER CONTROL FILE	1. SET DBID $v_dbid; 2. STARTUP NOMOUNT; 3. RESTORE CONTROLFILE FROM AUTOBACKUP; 4. ALTER DATABASE MOUNT; 5. RECOVER DATABASE; 6. ALTER DATABASE OPEN RESETLOGS;
COMPLETE RECOVERY	1. SET DBID $v_dbid;  #It is part of the name of autobackup file. So if autobackup file name is 'c-669001291-20070129-03' then DBID is 669001291 2. STARTUP NOMOUNT; 3. RESTORE SPFILE FROM AUTOBACKUP ; 4. STARTUP FORCE NOMOUNT; 5. RESTORE CONTROLFILE FROM AUTOBACKUP; (LIST BACKUP SUMMARY; and LIST BACKUP SET nnnnnn;) 6. ALTER DATABASE MOUNT; run { set until sequence $v_seq thread 1; restore database; recover database; } ALTER DATABASE OPEN RESETLOGS;
ENABLE BLOCK TRACKING	CTWR background process is writing to Change block tracking File at OS level. ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+fradg'; select FILENAME, STATUS, BYTES from v$block_change_tracking;
PRINT GLOBAL SCRIPTS	RMAN> print global script level0_sapr01_backup; printing stored global script: level0_sapr01_backup {set command id to 'rman_sapr01_incrlevel0'; delete noprompt backup completed before 'sysdate-6'; delete noprompt archivelog all completed before 'sysdate-3'; crosscheck backup; crosscheck archivelog all; backup as compressed backupset incremental level 0 TAG = SAPR01_LEVEL0 database plus archivelog; report obsolete; delete noprompt backup completed before 'sysdate-6'; report need backup database; resync catalog;}
WINDOWS TO SCHEDULE BACKUP	rman_level0_test01_dailybackup.cmd which contains - c:\oracle\product\10.2.0\db_1\bin\RMAN TARGET sys/mytest01@test01 CATALOG rc/test01oracle@rc CMDFILE=c:\oracle_scripts\test01\rman_test01_backup\rman_level0_test01_backup.rcv LOG=c:\oracle_scripts\test01\rman_test01_backup\rman_level0_test01_backup.log append rman_level0_test01_backup.rcv contains - RUN {EXECUTE GLOBAL SCRIPT level0_test01_backup;}
Check RMAN Backup Job details status	SELECT TO_CHAR (start_time, 'DD-MM-YYYY HH24:MI:SS') start_time, input_type, status, ROUND (elapsed_seconds/3600, 1) time_hr, input_bytes/1024/1024/1024 IN_GB, output_bytes/1024/1024/1024 OUT_GB, output_device_type FROM v$rman_backup_job_details where start_time > SYSDATE - 3 ORDER BY start_time DESC;