Oracle RMAN command cheat sheet
Quick reference of RMAN commands: show, backup, catalog, report, list, crosscheck, delete, change and validate.
This article is a quick reference of the most common Oracle RMAN (Recovery Manager) commands, grouped by purpose: SHOW, BACKUP, CATALOG, REPORT, LIST, CROSSCHECK, DELETE, CHANGE and VALIDATE.
SHOW
| Purpose | Command |
|---|---|
| Show all parameters | show all; |
| Archivelog deletion policy | show archivelog deletion policy; |
| Number of archivelog backup copies | show archivelog backup copies; |
| Auxiliary database information | show auxname; |
| Whether backup optimization is on or off | show backup optimization; |
| How the normal and auxiliary channels are configured | show [auxiliary] channel; |
| Channel characteristics | show channel for device type [disk | <media device>]; |
| Whether control file autobackup is on or off | show controlfile autobackup; |
| Format of the control file autobackup | show controlfile autobackup format; |
| Number of datafile backup copies kept | show datafile backup copies; |
| Default device type (disk or tape) | show default device type; |
| Retention policy (what RMAN marks as obsolete) | show retention policy; |
| Encryption algorithm in use | show encryption algorithm; |
| Encryption for the database and each tablespace | show encryption for [database | tablespace]; |
| Tablespaces excluded from backups | show exclude; |
| Maximum backup set size (unlimited by default) | show maxsetsize; |
| Snapshot control file name | show snapshot controlfile name; |
| Compression algorithm in force (ZLIB by default) | show compression algorithm; |
BACKUP
RMAN> backup current controlfile; -- manual backup of the current control file
RMAN> backup tablespace users include current controlfile; -- control file included in a tablespace backup
RMAN> backup spfile; -- server parameter file
RMAN> backup database; -- complete database
RMAN> backup database plus archivelogs; -- database plus archived logs
RMAN> backup archivelog all; -- all archived logs
RMAN> backup datafile 5 tag dbfile_5_bkp; -- one datafile, with a tag to find it easily
Restart a backup that failed midway through a nightly run:
RMAN> backup not backed up since time 'sysdate-1' database plus archivelog;
Force RMAN to back up files even if they are identical to a previous backup (the force option overrides backup optimization):
RMAN> backup database force;
CATALOG
Add user-managed copies or unregistered backup pieces to the RMAN repository.
RMAN> catalog datafilecopy '/u01/oracle/users.bkp';
RMAN> catalog datafilecopy '/u01/oracle/users.bkp' level 0; -- catalog as an incremental level 0 backup
RMAN> catalog backuppiece 'ertt2lu4_1_1';
RMAN> catalog start with '/backups/source_bkp' noprompt;
RMAN> catalog recovery area;
catalog start with catalogs all valid backup sets, datafile copies and archived redo logs whose name starts with the given pattern. It is useful, for example, after copying production backups to a target server for database cloning. catalog recovery area catalogs all files in the flash recovery area.
REPORT
RMAN> report need backup; -- backups required to comply with the retention policy
RMAN> report schema; -- all datafiles of the database
RMAN> report schema at time 'sysdate-1'; -- same, at a past point in time
RMAN> crosscheck backup;
RMAN> report obsolete; -- obsolete backups
Always run crosscheck before report obsolete so that the status of the backups in the repository matches what exists on disk and tape.
LIST
RMAN> list backup summary;
RMAN> list backup;
RMAN> list backup by file;
RMAN> list backupset; -- backup sets and proxy copies, not image copies
RMAN> list copy; -- datafile, archived log and control file copies
RMAN> list backupset tag 'full_database_backup';
RMAN> list backup of database;
RMAN> list incarnation;
RMAN> list restore point;
RMAN> list restore point all;
RMAN> list script names; -- recovery catalog scripts
RMAN> list expired backup;
RMAN> list expired archivelog all;
RMAN> list recoverable backup;
An open resetlogs operation creates a new database incarnation. Check the incarnations with list incarnation before performing recovery on such a database.
Archived logs
RMAN> list archivelog all; -- archived logs known to the control file
RMAN> backup archivelog all;
RMAN> backup archivelog all not backed up 1 times;
RMAN> backup archivelog all delete input; -- back up, then delete from disk
RMAN> list archivelog all backed up 1 times to DISK;
RMAN> list backup of controlfile;
RMAN> list backup of archivelog all;
RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
RMAN> delete archivelog all backed up 1 times to DISK;
RMAN> crosscheck archivelog sequence 24;
RMAN> crosscheck backup of archivelog sequence 24;
CROSSCHECK
crosscheck verifies that the backups recorded in the repository still exist. It covers backup sets, proxy copies and image copies.
RMAN> crosscheck backupset;
RMAN> crosscheck copy of database;
RMAN> crosscheck backupset 10, 12;
RMAN> crosscheck backuppiece tag = 'monthly_backup';
RMAN> crosscheck controlfilecopy '/backups/control01.ctl';
RMAN> crosscheck backup of datafile "/u01/oracle/system01.dbf" completed after 'sysdate-7';
RMAN> crosscheck backup of archivelog all spfile;
RMAN> crosscheck backup; -- all backups on disk and tape
DELETE
RMAN> delete backup; -- archived log backups and RMAN backups
RMAN> delete copy; -- all image copies
RMAN> delete backuppiece 9;
RMAN> delete copy of controlfile like '/backups/%';
RMAN> delete backup tag='double_bkp_prod';
RMAN> delete backup of tablespace sysaux device type sbt;
RMAN> delete archivelog all;
RMAN> delete archivelog all backed up 2 times to sbt;
RMAN> delete archivelog until sequence = 1234;
RMAN> backup device type sbt archivelog all delete all input;
RMAN> delete script full_disk_db;
RMAN> delete global script full_disk_db;
- RMAN asks for confirmation before deleting;
delete nopromptsuppresses the prompt. The physical file is removed from the backup media as well. - Run
crosscheck backup;(orcrosscheck copy;) first so that the repository and the physical media are synchronized. delete force: deletes the specified files whether or not they exist on media, and removes their records from the repository.delete expired: deletes only the files marked as expired bycrosscheck.delete obsolete: deletes datafile backups, copies, archived logs and log backups recorded as obsolete. It relies only on the retention policy in force.- If a local and a global script share the same name,
delete scriptdrops the local one. Use theglobalkeyword to drop the global one.
CHANGE
RMAN> change backupset 6 unavailable;
RMAN> change backupset 6 available;
RMAN> change backup tag 'initial_db_bkup' keep forever;
RMAN> change backup tag 'initial_db_bkup' nokeep;
RMAN> change backupset 12 keep until time 'sysdate+60';
unavailable: use it when a backup or copy is not physically accessible (for example an NFS-mounted disk that is temporarily unreachable) but you do not want to delete it. RMAN does not use an unavailable file in restore or recover operations. Switch it back withavailableonce the media is accessible again.keep foreverturns a regular backup into an archival backup: RMAN ignores the retention time for it.nokeepputs it back under the configured retention policy.keep until time 'sysdate+60'retains an archival backup for 60 days; after that it becomes obsolete anddelete obsoletecan remove it.
VALIDATE
Check for physical corruption without performing a backup:
RMAN> backup validate database archivelog all;
Check for logical corruption only:
RMAN> backup validate check logical database archivelog all;
The validate command is more granular than backup ... validate: it works on individual datafiles, backup sets and even data blocks. It always skips blocks that were never used.
RMAN> validate backupset 5;
RMAN> validate database;
RMAN> validate recovery area;
RMAN> validate recovery files;
RMAN> validate spfile;
RMAN> validate tablespace <tablespace_name>;
RMAN> validate controlfilecopy <filename>;
RMAN> validate backupset <primary_key>;