Rman Commands in oracle
Rman Commands:-
All the commands were tested on Oracle database version 11gR2 (11.2.0.4)
CONNECT command
Establish a connection between RMAN and a target, auxiliary, or recovery catalog database.
RMAN> CONNECT TARGET;
RMAN> CONNECT TARGET /
RMAN> CONNECT TARGET sys@tgt;
RMAN> CONNECT TARGET sys/pwd@tgt;
RMAN> CONNECT CATALOG rman@catdb;
RMAN> CONNECT CATALOG rman/pwd@catdb;
RMAN> CONNECT AUXILIARY /
RMAN> CONNECT AUXILIARY rman@auxdb;
RMAN> CONNECT AUXILIARY rman/pwd@auxdb;
SHOW COMMAND:-
1) Shows all parameters.
RMAN> show all;
2) Shows the archivelog deletion policy.
RMAN> show archivelog deletion policy;
3) Shows the number of archivelog backup copies
RMAN> show archivelog backup copies;
4) Shows the auxiliary database information.
RMAN> show auxname;
5) Shows whether optimization is on or off.
RMAN> show backup optimization;
6) Shows how the normal channel and auxiliary channel are configured.
RMAN> show [auxiliary] channel;
7) Shows the characteristics of the channel
RMAN> show channel for device type [disk | <media device>;
8) Shows whether control file autobackup is on or off.
RMAN> show controlfile autobackup;
9) Shows the format of the autobackup control file
RMAN> show controlfile autobackup format;
10) Shows the number of datafile backup copies being kept.
RMAN> show datafile backup copies;
11) Shows the default type (disk or tape)
RMAN> show default device type;
12) Shows policy for datafile and control file backups and copies that RMAN marks as obsolete.
RMAN> show retention policy;
13) Shows the encryption algorithm currently in use.
RMAN> show encryption algorithm;
14) Shows the encryption for the database and every tablespace.
RMAN> show encryption for [database | tablespace];
15) Shows the tablespaces excluded from the backup.
RMAN> show exclude;
16) Shows the maximum size for backup sets. The default is unlimited.
RMAN> show maxsetsize;
17) Shows the policy for datafile and control file backups and copies that RMAN marks as obsolete.
RMAN> show retention policy;
18) Shows the snapshot control filename.
RMAN> show snapshot controlfile name;
19) Shows the compression algorithm in force. The default is the ZLIB algorithm.
RMAN> show compression algorithm;
CREATE CATALOG:-
Create Oracle schema for the recovery catalog.
RMAN> CREATE CATALOG;
RMAN> CREATE CATALOG TABLESPACE rmants;
RMAN> CREATE VIRTUAL CATALOG;
BACKUP COMMAND:-
1) To perform a manual backup of the current control file
RMAN> backup current controlfile;
2) To back up the control file as part of a tablespace backup operation
RMAN> backup tablespace users include current controlfile;
3) To back up the server parameter file
RMAN> backup spfile;
4) To restart an RMAN backup that failed midway through a nightly backup.
RMAN> backup not backed up since time ‘sysdate-1′ database plus archivelog;
5) To force RMAN to back up a file regardless of whether it’s identical to a previously backed up file by specifying the force option
RMAN> backup database force;
By using the force option, you make RMAN back up all the specified files, even if the backup optimization feature is turned on.
6) To backup complete database
RMAN> backup database;
7) To backup database plus archivelogs
RMAN> backup database plus archivelogs;
8) To backup all archive logs
RMAN> backup archivelog all;
9) To backup specific data file
RMAN> backup datafile 5 tag dbfile_5_bkp;
A tag was also added to easily locate this datafile’s backup.
Incremantal backup:-
RMAN> BACKUP INCREMENTAL LEVEL=0 DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL=1 DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL=0 DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE SKIP INACCESSIBLE DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr' DATABASE;
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'oltp' DATABASE;
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 351986 DATABASE FORMAT '/tmp/incr_standby_%U';
RMAN> BACKUP INCREMENTAL FROM SCN 629184 DATAFILE 5 FORMAT '/tmp/ForStandby_%U' TAG 'FORSTANDBY';
RMAN> BACKUP INCREMENTAL LEVEL = --- tablespace/datafile
CATALOG COMMAND:-
1) Add user-managed copies of datafile to RMAN repository
RMAN> catalog datafilecopy ‘/u01/oracle/users.bkp’;
RMAN> catalog datafilecopy ‘/u01/oracle/users.bkp’ level 0; (To catalog as incremental level 0 backup)
2) Add uncataloged backup piece to RMAN repository
RMAN> catalog backuppiece ‘ertt2lu4_1_1′;
3) To catalog multiple files ( say you copied production backup to target database for database cloning)
RMAN> catalog start with ‘/backups/source_bkp’ noprompt;
The start with clause specifies that RMAN catalog all valid backup sets, datafile copies, and archived redo logs starting with the string pattern you pass.
4) To catalog all files in the flash recovery area
RMAN> catalog recovery area;
RESYNC CATALOG command:-
Perform a full resynchronization, which creates a snapshot control file and then copies any new or changed information from that snapshot control file to the recovery catalog.
RMAN> RESYNC CATALOG;
RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME prod_db;
RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME ALL;
REPORT COMMAND:-
1) To find out which backups you need to make in order to conform to the retention policy you put in place
RMAN> report need backup;
The output of the report need backup command tells you that you must back up which all database files to comply with your retention policy.
2) To get a report about all the datafiles in a database
RMAN> report schema;
RMAN> report schema at time ‘sysdate-1′; (from a past point in time)
3) To reports on any obsolete backups
RMAN> crosscheck backup;
RMAN> report obsolete;
Always run the crosscheck command first in order to update the status of the backups in the RMAN repository to that on disk and tape.
LIST COMMAND:-
1) To review RMAN backups of datafiles, archived redo logs, and control files.
RMAN> list backup;
2) List the backups by just the backup files
RMAN> list backup by file;
3) Lists only backup sets and proxy copies but not image copies
RMAN> list backupset;
4) Lists only datafile, archived redo log, and control file copies
RMAN> list copy;
5) Lists backups by tag:
RMAN> list backupset tag ‘full_database_backup’;
6) To list the backups of all datafiles and archivelogs of the target database:
RMAN> list backup of database;
7) Lists all incarnations of a database
RMAN> list incarnation;
When you perform an open resetlogs operation, it results in the creation of a new incarnation of the database. When performing recovery operations on such a database, you might want to check the database incarnation
8) Lists all restore points in the target database
RMAN> list restore point;
9) Lists the names of all recovery catalog scripts
RMAN> list script names;
10) Which of the backups of the target database have an expired status in the repository.
RMAN> list expired backup;
11) Which of the archived redo log backups have the expired status
RMAN> list expired archivelog all;
12) To restrict the list of backups and copies whose status is listed as available
RMAN> list recoverable backup;
13) To view all the restore points in the database
RMAN> list restore point all;
CROSSCHECK COMMAND:-
1) Cross-checking just backup sets
RMAN> crosscheck backupset;
2) Cross-checking a copy of a database
RMAN> crosscheck copy of database;
3) Cross-checking specific backupsets
RMAN> crosscheck backupset 10, 12;
4) Cross-checking using a backup tag
RMAN> crosscheck backuppiece tag = ‘monthly_backup’;
5) Cross-checking a control file copy;
RMAN> crosscheck controlfilecopy ‘/backups/control01.ctl’;
6) Cross-checking backups completed after a specific time
RMAN> crosscheck backup of datafile “/u01/oracle//system01.dbf” completed after ‘sysdate-7′;
7) Cross-checking of all archivelogs and the spfile
RMAN> crosscheck backup of archivelog all spfile;
8) Cross-checking all backups on disk and tape
RMAN> crosscheck backup;
The crosscheck command checks whether the backups still exist. The command checks backup sets, proxy copies, and image copies.
DELETE COMMAND:-
1) To remove both archived redo logs and RMAN backups
RMAN> delete backup;
RMAN always prompts you for confirmation before going ahead and deleting the backup files. You can issue the delete noprompt command to suppress the confirmation prompt. This will also remove the physical file from the backup media
To make sure the repository and the physical media are synchronized, run “RMAN> crosscheck backup;” before running above command
2) To remove all image copies
RMAN> delete copy;
To make sure the repository and the physical media are synchronized, run “RMAN> crosscheck copy;” before running above command
3) To delete specfic backuppiece
RMAN> delete backuppiece 9;
4) To delete copy of controlfile under /backups
RMAN> delete copy of controlfile like ‘/backups/%’;
5) To delete backups with specific tag
RMAN> delete backup tag=’double_bkp_prod’;
6) To delete bakups of specific tablespace
RMAN> delete backup of tablespace sysaux device type sbt;
******************
You can also use force, expired, obsolete keyword with delete commad:
delete force ..: Deletes the specified files whether they actually exist on media or not and removes their records from the RMAN repository as well
delete expired ..: Deletes only those files marked as expired as per crosscheck command.
delete obsolete ..: Deletes datafile backups and copies and the archived redo logs and log backups that are recorded as obsolete in the RMAN repository
The delete obsolete command relies only on the backup retention policy in force.
******************
7) To delete all archived redo logs
RMAN> delete archivelog all;
8) To delete already backed up archived redo logs
RMAN> delete archivelog all backed up 2 times to sbt;
9) To delete specific archived redo logs
RMAN> delete archivelog until sequence = 1234;
10) Delete archive logs after taking backup
RMAN> backup device type sbt archivelog all delete all input;
11) Delete stored script
RMAN> delete script full_disk_db;
If you have two scripts—one local and one global—in the same name, then the delete script command drops the local one, not the global one. If you want to drop the global script, you must use the keyword global in the command, as shown here:
RMAN> delete global script full_disk_db;
CHANGE COMMAND:-
1) Change the status of a backup set to unavailable
RMAN> change backupset 6 unavailable;
You usually do it when you don’t want to delete the backup/copy but you also don’t want to delete that backup/copy (probably it is not available physically on disk)
Once you mark a backup file unavailable, RMAN won’t use that file in a restore or recover operation.
2) Change the status of a backup set to available again
RMAN> change backupset 6 available;
For example, say you performed a backup using an NFS-mounted disk and that disk subsequently becomes inaccessible for some reasons, just issue the change command to set the status of the backup as unavailable. Later, once the disk becomes accessible again, you can change its status back to available.
3) To modify a regular consistent database backup into an archival backup:
RMAN> change backup tag ‘initial_db_bkup’ keep forever;
When you make an archival backup with the keep … forever option, RMAN disregards the backup retention time for these backups.
4) To change the archival backup to a normal database backup
RMAN> change backup tag ‘inital_db_backup’ nokeep;
When you run the change … nokeep command, the backup set with the tag inital_db_backup, which was previously designated as a long term archival backup, will once again come under the purview of your configured retention policy.
5) To modify the time period for which you want to retain the archival backups
RMAN> change backupset 12 keep until time ‘sysdate+60′;
After the 60 days are up, the backup will become obsolete and is eligible for deletion by the delete obsolete command.
VALIDATE COMMAND:-
1) To check all the datafiles and the archived redo logs for physical corruption without actually performing the backup
RMAN> backup validate database archivelog all;
2) To check for logical corruption without actually performing the backup
RMAN> backup validate check logical database archivelog all;
The check logical clause means that RMAN will check for logical corruption only.
3) To validate a single backup set
RMAN> validate backupset 5;
4) To validate all datafiles at once
RMAN> validate database;
Note that the validate command can check at a much more granular level than the backup … validate command. You can use the validate command with individual datafiles, backup sets, and even data blocks.
The validate command always skips all the data blocks that were never used, in each of the datafile it validates.
5) To validate recovery area
RMAN> validate recovery area;
6) To validate all the recovery related files
RMAN> validate recovery files;
7) To validate the spfile
RMAN> validate spfile;
8) To validate specific tablespace
RMAN> validate tablespace <tablespace_name>;
9) To validate specific control file copy
RMAN> validate controlfilecopy <filename>;
10) To validate specific backupset
RMAN> validate backupset <primary_key>;
SET COMMANDS:-
RMAN> SET ECHO ON;
RMAN> SET ECHO OFF;
RMAN> SET DATABASE prod;
RMAN> SET DBID=4240978820;
RMAN> SET DBID 591329635;
RMAN> SET COMMAND ID TO 'rman';
RMAN> SET MAXCORRUPT FOR DATABASE TO 2;
RMAN> SET MAXCORRUPT FOR DATAFILE 13 TO 200;
RMAN> SET BACKUP COPIES = 2;
RMAN> SET NEWNAME FOR DATABASE TO '/oradata1/%b';
RMAN> SET NEWNAME FOR TABLESPACE users TO '/oradata2/%U';
RMAN> SET NEWNAME FOR DATAFILE 1 to ‘/oradata/system01.dbf’;
RMAN> SET NEWNAME FOR DATAFILE '/disk7/tbs11.f' TO '/disk9/tbs11.f';
RMAN> SET NEWNAME FOR TEMPFILE 1 TO '/newdisk/dbs/temp1.f';
RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE sbt TO 'cf_%F';
RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf_%F.bak';
RMAN> SET UNTIL TIME ’04-23-2010:23:50:04’;
RMAN> SET ARCHIVELOG DESTINATION TO '/oracle/temp_restore';
RMAN> SET COMPRESSION ALGORITHM 'LOW';
RMAN> SET COMPRESSION ALGORITHM 'LOW' OPTIMIZE FOR LOAD FALSE;
RMAN> SET COMPRESSION ALGORITHM 'MEDIUM';
RMAN> SET COMPRESSION ALGORITHM 'HIGH';
ALTER DATABASE COMMANDS:-
Mount or open a database.
RMAN> ALTER DATABASE MOUNT;
RMAN> ALTER DATABASE OPEN;
RMAN> ALTER DATABASE OPEN RESETLOGS;
RESTORE COMMANDS:-
RMAN> RESTORE DATABASE;
RMAN> RESTORE DATABASE VALIDATE;
RMAN> RESTORE DATABASE PREVIEW;
RMAN> RESTORE DATABASE PREVIEW SUMMARY;
RMAN> RESTORE DATABASE SKIP TABLESPACE temp, history;
RMAN> RESTORE DATABASE UNTIL SCN 154876;
RMAN> RESTORE TABLESPACE users;
RMAN> RESTORE TABLESPACE dwh1, dwh2;
RMAN> RESTORE TABLESPACE tbs1 PREVIEW;
RMAN> RESTORE TABLESPACE users VALIDATE;
RMAN> RESTORE DATAFILE 45;
RMAN> RESTORE DATAFILE 23 PREVIEW;
RMAN> RESTORE DATAFILE 12 VALIDATE;
RMAN> RESTORE CONTROLFILE;
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN> RESTORE CONTROLFILE FROM TAG 'monday_cf_backup';
RMAN> RESTORE CONTROLFILE FROM '/u01/control01.ctl';
RMAN> RESTORE CONTROLFILE VALIDATE;
RMAN> RESTORE CONTROLFILE TO '/tmp/autobkp.dbf' FROM AUTOBACKUP MAXSEQ 20 MAXDAYS 150;
RMAN> RESTORE SPFILE;
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
RMAN> RESTORE ARCHIVELOG ALL VALIDATE;
RMAN> RESTORE ARCHIVELOG ALL PREVIEW;
RMAN> RESTORE ARCHIVELOG ALL PREVIEW RECALL;
RMAN> RESTORE ARCHIVELOG ALL DEVICE TYPE sbt PREVIEW;
RMAN> RESTORE ARCHIVELOG LOW LOGSEQ 78311 HIGH LOGSEQ 78340 THREAD 1 ALL;
RECOVER COMMANDS:-
RMAN> RECOVER DATABASE;
RMAN> RECOVER DATABASE NOREDO;
RMAN> RECOVER DATABASE SKIP TABLESPACE temp;
RMAN> RECOVER DATABASE SKIP FOREVER TABLESPACE exam;
RMAN> RECOVER DATABASE UNTIL SCN 154876;
RMAN> RECOVER TABLESPACE users;
RMAN> RECOVER TABLESPACE dwh DELETE ARCHIVELOG MAXSIZE 2M;
RMAN> RECOVER DATAFILE 33;
RMAN> RECOVER DATAFILE 3 BLOCK 116 DATAFILE 4 BLOCK 10;
RMAN> RECOVER DATAFILE 2 BLOCK 204 DATAFILE 9 BLOCK 109 FROM TAG=sundaynight;
RMAN> RECOVER DATAFILECOPY '/disk1/img.df' UNTIL TIME 'SYSDATE-7';
RMAN> RECOVER COPY OF DATABASE WITH TAG 'incr';
RMAN> RECOVER COPY OF DATABASE WITH TAG 'upd' UNTIL TIME 'SYSDATE - 7';
RMAN> RECOVER CORRUPTION LIST;
ADVICE COMMANDS(11gR1):-
Display repair options.
RMAN> ADVISE FAILURE;
RMAN> ADVISE FAILURE 555, 242;
RMAN> ADVISE FAILURE ALL;
RMAN> ADVISE FAILURE CRITICAL;
RMAN> ADVISE FAILURE HIGH;
RMAN> ADVISE FAILURE LOW;
RMAN> ADVISE FAILURE HIGH EXCLUDE FAILURE 625;
REPAIR COMMANDS (From Oracle 11g R1)
Repair one or more failures recorded in the automated diagnostic repository.
RMAN> REPAIR FAILURE;
RMAN> REPAIR FAILURE PREVIEW;
RMAN> REPAIR FAILURE NOPROMPT;
RMAN> REPAIR FAILURE USING ADVISE OPTION integer;
RUN COMMANDS:-
Execute a sequence of one or more RMAN commands, which are one or more statements executed within the braces of RUN.
RMAN>
run {
ALLOCATE CHANNEL c1 TYPE DISK FORMAT '/orabak/%U';
BACKUP TABLESPACE users;
}
RMAN> run {
ALLOCATE CHANNEL c1 TYPE DISK FORMAT '&1/%U';
BACKUP TABLESPACE &2;
}
RMAN> run
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK FORMAT '/fs1/%U';
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK FORMAT '/fs2/%U';
BACKUP(TABLESPACE system,fin,mark FILESPERSET 20) (DATAFILE 2,3,6);
}
HOST COMMANDS:-
Invoke an operating system command-line subshell from within RMAN or run a specific operating system command.
RMAN> HOST;
RMAN> HOST 'ls -lt /disk2/*';
RMAN> HOST '/bin/mv $ORACLE_HOME/dbs/*.arc /disk2/archlog/';
EXIT AND QUIT COMMANDS
Exit the RMAN console.
RMAN> exit;
RMAN> quit;
All the commands were tested on Oracle database version 11gR2 (11.2.0.4)
CONNECT command
Establish a connection between RMAN and a target, auxiliary, or recovery catalog database.
RMAN> CONNECT TARGET;
RMAN> CONNECT TARGET /
RMAN> CONNECT TARGET sys@tgt;
RMAN> CONNECT TARGET sys/pwd@tgt;
RMAN> CONNECT CATALOG rman@catdb;
RMAN> CONNECT CATALOG rman/pwd@catdb;
RMAN> CONNECT AUXILIARY /
RMAN> CONNECT AUXILIARY rman@auxdb;
RMAN> CONNECT AUXILIARY rman/pwd@auxdb;
SHOW COMMAND:-
1) Shows all parameters.
RMAN> show all;
2) Shows the archivelog deletion policy.
RMAN> show archivelog deletion policy;
3) Shows the number of archivelog backup copies
RMAN> show archivelog backup copies;
4) Shows the auxiliary database information.
RMAN> show auxname;
5) Shows whether optimization is on or off.
RMAN> show backup optimization;
6) Shows how the normal channel and auxiliary channel are configured.
RMAN> show [auxiliary] channel;
7) Shows the characteristics of the channel
RMAN> show channel for device type [disk | <media device>;
8) Shows whether control file autobackup is on or off.
RMAN> show controlfile autobackup;
9) Shows the format of the autobackup control file
RMAN> show controlfile autobackup format;
10) Shows the number of datafile backup copies being kept.
RMAN> show datafile backup copies;
11) Shows the default type (disk or tape)
RMAN> show default device type;
12) Shows policy for datafile and control file backups and copies that RMAN marks as obsolete.
RMAN> show retention policy;
13) Shows the encryption algorithm currently in use.
RMAN> show encryption algorithm;
14) Shows the encryption for the database and every tablespace.
RMAN> show encryption for [database | tablespace];
15) Shows the tablespaces excluded from the backup.
RMAN> show exclude;
16) Shows the maximum size for backup sets. The default is unlimited.
RMAN> show maxsetsize;
17) Shows the policy for datafile and control file backups and copies that RMAN marks as obsolete.
RMAN> show retention policy;
18) Shows the snapshot control filename.
RMAN> show snapshot controlfile name;
19) Shows the compression algorithm in force. The default is the ZLIB algorithm.
RMAN> show compression algorithm;
CREATE CATALOG:-
Create Oracle schema for the recovery catalog.
RMAN> CREATE CATALOG;
RMAN> CREATE CATALOG TABLESPACE rmants;
RMAN> CREATE VIRTUAL CATALOG;
BACKUP COMMAND:-
1) To perform a manual backup of the current control file
RMAN> backup current controlfile;
2) To back up the control file as part of a tablespace backup operation
RMAN> backup tablespace users include current controlfile;
3) To back up the server parameter file
RMAN> backup spfile;
4) To restart an RMAN backup that failed midway through a nightly backup.
RMAN> backup not backed up since time ‘sysdate-1′ database plus archivelog;
5) To force RMAN to back up a file regardless of whether it’s identical to a previously backed up file by specifying the force option
RMAN> backup database force;
By using the force option, you make RMAN back up all the specified files, even if the backup optimization feature is turned on.
6) To backup complete database
RMAN> backup database;
7) To backup database plus archivelogs
RMAN> backup database plus archivelogs;
8) To backup all archive logs
RMAN> backup archivelog all;
9) To backup specific data file
RMAN> backup datafile 5 tag dbfile_5_bkp;
A tag was also added to easily locate this datafile’s backup.
Incremantal backup:-
RMAN> BACKUP INCREMENTAL LEVEL=0 DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL=1 DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL=0 DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE SKIP INACCESSIBLE DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr' DATABASE;
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'oltp' DATABASE;
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 351986 DATABASE FORMAT '/tmp/incr_standby_%U';
RMAN> BACKUP INCREMENTAL FROM SCN 629184 DATAFILE 5 FORMAT '/tmp/ForStandby_%U' TAG 'FORSTANDBY';
RMAN> BACKUP INCREMENTAL LEVEL = --- tablespace/datafile
RMAN> BACKUP BLOCKS ALL CHECK LOGICAL VALIDATE DATAFILE 1398
CATALOG COMMAND:-
1) Add user-managed copies of datafile to RMAN repository
RMAN> catalog datafilecopy ‘/u01/oracle/users.bkp’;
RMAN> catalog datafilecopy ‘/u01/oracle/users.bkp’ level 0; (To catalog as incremental level 0 backup)
2) Add uncataloged backup piece to RMAN repository
RMAN> catalog backuppiece ‘ertt2lu4_1_1′;
3) To catalog multiple files ( say you copied production backup to target database for database cloning)
RMAN> catalog start with ‘/backups/source_bkp’ noprompt;
The start with clause specifies that RMAN catalog all valid backup sets, datafile copies, and archived redo logs starting with the string pattern you pass.
4) To catalog all files in the flash recovery area
RMAN> catalog recovery area;
RESYNC CATALOG command:-
Perform a full resynchronization, which creates a snapshot control file and then copies any new or changed information from that snapshot control file to the recovery catalog.
RMAN> RESYNC CATALOG;
RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME prod_db;
RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME ALL;
REPORT COMMAND:-
1) To find out which backups you need to make in order to conform to the retention policy you put in place
RMAN> report need backup;
The output of the report need backup command tells you that you must back up which all database files to comply with your retention policy.
2) To get a report about all the datafiles in a database
RMAN> report schema;
RMAN> report schema at time ‘sysdate-1′; (from a past point in time)
3) To reports on any obsolete backups
RMAN> crosscheck backup;
RMAN> report obsolete;
Always run the crosscheck command first in order to update the status of the backups in the RMAN repository to that on disk and tape.
LIST COMMAND:-
1) To review RMAN backups of datafiles, archived redo logs, and control files.
RMAN> list backup;
2) List the backups by just the backup files
RMAN> list backup by file;
3) Lists only backup sets and proxy copies but not image copies
RMAN> list backupset;
4) Lists only datafile, archived redo log, and control file copies
RMAN> list copy;
5) Lists backups by tag:
RMAN> list backupset tag ‘full_database_backup’;
6) To list the backups of all datafiles and archivelogs of the target database:
RMAN> list backup of database;
7) Lists all incarnations of a database
RMAN> list incarnation;
When you perform an open resetlogs operation, it results in the creation of a new incarnation of the database. When performing recovery operations on such a database, you might want to check the database incarnation
8) Lists all restore points in the target database
RMAN> list restore point;
9) Lists the names of all recovery catalog scripts
RMAN> list script names;
10) Which of the backups of the target database have an expired status in the repository.
RMAN> list expired backup;
11) Which of the archived redo log backups have the expired status
RMAN> list expired archivelog all;
12) To restrict the list of backups and copies whose status is listed as available
RMAN> list recoverable backup;
13) To view all the restore points in the database
RMAN> list restore point all;
CROSSCHECK COMMAND:-
1) Cross-checking just backup sets
RMAN> crosscheck backupset;
2) Cross-checking a copy of a database
RMAN> crosscheck copy of database;
3) Cross-checking specific backupsets
RMAN> crosscheck backupset 10, 12;
4) Cross-checking using a backup tag
RMAN> crosscheck backuppiece tag = ‘monthly_backup’;
5) Cross-checking a control file copy;
RMAN> crosscheck controlfilecopy ‘/backups/control01.ctl’;
6) Cross-checking backups completed after a specific time
RMAN> crosscheck backup of datafile “/u01/oracle//system01.dbf” completed after ‘sysdate-7′;
7) Cross-checking of all archivelogs and the spfile
RMAN> crosscheck backup of archivelog all spfile;
8) Cross-checking all backups on disk and tape
RMAN> crosscheck backup;
The crosscheck command checks whether the backups still exist. The command checks backup sets, proxy copies, and image copies.
DELETE COMMAND:-
1) To remove both archived redo logs and RMAN backups
RMAN> delete backup;
RMAN always prompts you for confirmation before going ahead and deleting the backup files. You can issue the delete noprompt command to suppress the confirmation prompt. This will also remove the physical file from the backup media
To make sure the repository and the physical media are synchronized, run “RMAN> crosscheck backup;” before running above command
2) To remove all image copies
RMAN> delete copy;
To make sure the repository and the physical media are synchronized, run “RMAN> crosscheck copy;” before running above command
3) To delete specfic backuppiece
RMAN> delete backuppiece 9;
4) To delete copy of controlfile under /backups
RMAN> delete copy of controlfile like ‘/backups/%’;
5) To delete backups with specific tag
RMAN> delete backup tag=’double_bkp_prod’;
6) To delete bakups of specific tablespace
RMAN> delete backup of tablespace sysaux device type sbt;
******************
You can also use force, expired, obsolete keyword with delete commad:
delete force ..: Deletes the specified files whether they actually exist on media or not and removes their records from the RMAN repository as well
delete expired ..: Deletes only those files marked as expired as per crosscheck command.
delete obsolete ..: Deletes datafile backups and copies and the archived redo logs and log backups that are recorded as obsolete in the RMAN repository
The delete obsolete command relies only on the backup retention policy in force.
******************
7) To delete all archived redo logs
RMAN> delete archivelog all;
8) To delete already backed up archived redo logs
RMAN> delete archivelog all backed up 2 times to sbt;
9) To delete specific archived redo logs
RMAN> delete archivelog until sequence = 1234;
10) Delete archive logs after taking backup
RMAN> backup device type sbt archivelog all delete all input;
11) Delete stored script
RMAN> delete script full_disk_db;
If you have two scripts—one local and one global—in the same name, then the delete script command drops the local one, not the global one. If you want to drop the global script, you must use the keyword global in the command, as shown here:
RMAN> delete global script full_disk_db;
CHANGE COMMAND:-
1) Change the status of a backup set to unavailable
RMAN> change backupset 6 unavailable;
You usually do it when you don’t want to delete the backup/copy but you also don’t want to delete that backup/copy (probably it is not available physically on disk)
Once you mark a backup file unavailable, RMAN won’t use that file in a restore or recover operation.
2) Change the status of a backup set to available again
RMAN> change backupset 6 available;
For example, say you performed a backup using an NFS-mounted disk and that disk subsequently becomes inaccessible for some reasons, just issue the change command to set the status of the backup as unavailable. Later, once the disk becomes accessible again, you can change its status back to available.
3) To modify a regular consistent database backup into an archival backup:
RMAN> change backup tag ‘initial_db_bkup’ keep forever;
When you make an archival backup with the keep … forever option, RMAN disregards the backup retention time for these backups.
4) To change the archival backup to a normal database backup
RMAN> change backup tag ‘inital_db_backup’ nokeep;
When you run the change … nokeep command, the backup set with the tag inital_db_backup, which was previously designated as a long term archival backup, will once again come under the purview of your configured retention policy.
5) To modify the time period for which you want to retain the archival backups
RMAN> change backupset 12 keep until time ‘sysdate+60′;
After the 60 days are up, the backup will become obsolete and is eligible for deletion by the delete obsolete command.
VALIDATE COMMAND:-
1) To check all the datafiles and the archived redo logs for physical corruption without actually performing the backup
RMAN> backup validate database archivelog all;
2) To check for logical corruption without actually performing the backup
RMAN> backup validate check logical database archivelog all;
The check logical clause means that RMAN will check for logical corruption only.
3) To validate a single backup set
RMAN> validate backupset 5;
4) To validate all datafiles at once
RMAN> validate database;
Note that the validate command can check at a much more granular level than the backup … validate command. You can use the validate command with individual datafiles, backup sets, and even data blocks.
The validate command always skips all the data blocks that were never used, in each of the datafile it validates.
5) To validate recovery area
RMAN> validate recovery area;
6) To validate all the recovery related files
RMAN> validate recovery files;
7) To validate the spfile
RMAN> validate spfile;
8) To validate specific tablespace
RMAN> validate tablespace <tablespace_name>;
9) To validate specific control file copy
RMAN> validate controlfilecopy <filename>;
10) To validate specific backupset
RMAN> validate backupset <primary_key>;
SET COMMANDS:-
RMAN> SET ECHO ON;
RMAN> SET ECHO OFF;
RMAN> SET DATABASE prod;
RMAN> SET DBID=4240978820;
RMAN> SET DBID 591329635;
RMAN> SET COMMAND ID TO 'rman';
RMAN> SET MAXCORRUPT FOR DATABASE TO 2;
RMAN> SET MAXCORRUPT FOR DATAFILE 13 TO 200;
RMAN> SET BACKUP COPIES = 2;
RMAN> SET NEWNAME FOR DATABASE TO '/oradata1/%b';
RMAN> SET NEWNAME FOR TABLESPACE users TO '/oradata2/%U';
RMAN> SET NEWNAME FOR DATAFILE 1 to ‘/oradata/system01.dbf’;
RMAN> SET NEWNAME FOR DATAFILE '/disk7/tbs11.f' TO '/disk9/tbs11.f';
RMAN> SET NEWNAME FOR TEMPFILE 1 TO '/newdisk/dbs/temp1.f';
RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE sbt TO 'cf_%F';
RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf_%F.bak';
RMAN> SET UNTIL TIME ’04-23-2010:23:50:04’;
RMAN> SET ARCHIVELOG DESTINATION TO '/oracle/temp_restore';
RMAN> SET COMPRESSION ALGORITHM 'LOW';
RMAN> SET COMPRESSION ALGORITHM 'LOW' OPTIMIZE FOR LOAD FALSE;
RMAN> SET COMPRESSION ALGORITHM 'MEDIUM';
RMAN> SET COMPRESSION ALGORITHM 'HIGH';
ALTER DATABASE COMMANDS:-
Mount or open a database.
RMAN> ALTER DATABASE MOUNT;
RMAN> ALTER DATABASE OPEN;
RMAN> ALTER DATABASE OPEN RESETLOGS;
RESTORE COMMANDS:-
RMAN> RESTORE DATABASE;
RMAN> RESTORE DATABASE VALIDATE;
RMAN> RESTORE DATABASE PREVIEW;
RMAN> RESTORE DATABASE PREVIEW SUMMARY;
RMAN> RESTORE DATABASE SKIP TABLESPACE temp, history;
RMAN> RESTORE DATABASE UNTIL SCN 154876;
RMAN> RESTORE TABLESPACE users;
RMAN> RESTORE TABLESPACE dwh1, dwh2;
RMAN> RESTORE TABLESPACE tbs1 PREVIEW;
RMAN> RESTORE TABLESPACE users VALIDATE;
RMAN> RESTORE DATAFILE 45;
RMAN> RESTORE DATAFILE 23 PREVIEW;
RMAN> RESTORE DATAFILE 12 VALIDATE;
RMAN> RESTORE CONTROLFILE;
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN> RESTORE CONTROLFILE FROM TAG 'monday_cf_backup';
RMAN> RESTORE CONTROLFILE FROM '/u01/control01.ctl';
RMAN> RESTORE CONTROLFILE VALIDATE;
RMAN> RESTORE CONTROLFILE TO '/tmp/autobkp.dbf' FROM AUTOBACKUP MAXSEQ 20 MAXDAYS 150;
RMAN> RESTORE SPFILE;
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
RMAN> RESTORE ARCHIVELOG ALL VALIDATE;
RMAN> RESTORE ARCHIVELOG ALL PREVIEW;
RMAN> RESTORE ARCHIVELOG ALL PREVIEW RECALL;
RMAN> RESTORE ARCHIVELOG ALL DEVICE TYPE sbt PREVIEW;
RMAN> RESTORE ARCHIVELOG LOW LOGSEQ 78311 HIGH LOGSEQ 78340 THREAD 1 ALL;
RECOVER COMMANDS:-
RMAN> RECOVER DATABASE;
RMAN> RECOVER DATABASE NOREDO;
RMAN> RECOVER DATABASE SKIP TABLESPACE temp;
RMAN> RECOVER DATABASE SKIP FOREVER TABLESPACE exam;
RMAN> RECOVER DATABASE UNTIL SCN 154876;
RMAN> RECOVER TABLESPACE users;
RMAN> RECOVER TABLESPACE dwh DELETE ARCHIVELOG MAXSIZE 2M;
RMAN> RECOVER DATAFILE 33;
RMAN> RECOVER DATAFILE 3 BLOCK 116 DATAFILE 4 BLOCK 10;
RMAN> RECOVER DATAFILE 2 BLOCK 204 DATAFILE 9 BLOCK 109 FROM TAG=sundaynight;
RMAN> RECOVER DATAFILECOPY '/disk1/img.df' UNTIL TIME 'SYSDATE-7';
RMAN> RECOVER COPY OF DATABASE WITH TAG 'incr';
RMAN> RECOVER COPY OF DATABASE WITH TAG 'upd' UNTIL TIME 'SYSDATE - 7';
RMAN> RECOVER CORRUPTION LIST;
ADVICE COMMANDS(11gR1):-
Display repair options.
RMAN> ADVISE FAILURE;
RMAN> ADVISE FAILURE 555, 242;
RMAN> ADVISE FAILURE ALL;
RMAN> ADVISE FAILURE CRITICAL;
RMAN> ADVISE FAILURE HIGH;
RMAN> ADVISE FAILURE LOW;
RMAN> ADVISE FAILURE HIGH EXCLUDE FAILURE 625;
REPAIR COMMANDS (From Oracle 11g R1)
Repair one or more failures recorded in the automated diagnostic repository.
RMAN> REPAIR FAILURE;
RMAN> REPAIR FAILURE PREVIEW;
RMAN> REPAIR FAILURE NOPROMPT;
RMAN> REPAIR FAILURE USING ADVISE OPTION integer;
RUN COMMANDS:-
Execute a sequence of one or more RMAN commands, which are one or more statements executed within the braces of RUN.
RMAN>
run {
ALLOCATE CHANNEL c1 TYPE DISK FORMAT '/orabak/%U';
BACKUP TABLESPACE users;
}
RMAN> run {
ALLOCATE CHANNEL c1 TYPE DISK FORMAT '&1/%U';
BACKUP TABLESPACE &2;
}
RMAN> run
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK FORMAT '/fs1/%U';
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK FORMAT '/fs2/%U';
BACKUP(TABLESPACE system,fin,mark FILESPERSET 20) (DATAFILE 2,3,6);
}
HOST COMMANDS:-
Invoke an operating system command-line subshell from within RMAN or run a specific operating system command.
RMAN> HOST;
RMAN> HOST 'ls -lt /disk2/*';
RMAN> HOST '/bin/mv $ORACLE_HOME/dbs/*.arc /disk2/archlog/';
EXIT AND QUIT COMMANDS
Exit the RMAN console.
RMAN> exit;
RMAN> quit;
Comments
Post a Comment