Oracle RMAN Configuration and Monitoring Scripts-14


RMAN Configuration and Monitoring Scripts


RMAN – SHOW ALL is the code to know the RMAN configurations.


RMAN backup configuration and schedule

Find out the backup configuration info


rman> show all;

List used block bakup

rman> list backup;

rman> list backup of datafile 4;

rman> list backup of tablespace tools;

List image copy backup

rman> list copy of database;

rman> list copy of datafile 4;

rman> list copy of tablespace tools;


Based on the application requirements this needs to be configured.

By default backup will be stored in disk. We can also change this to tape.

Keyword: Disk –Disk Tape — SBT/ SBT_TAPE

rman> configure default device type to disk or sbt/sbt_tape;

How to change the default RMAN location

$ mkdir –p /backup/rman/testdb

rman> configure channel device type disk format ‘/backup/rman/testdb/ full_%d_%u.bkp’;

Location – backup piece

D – Dbname

U – Unique identifier 8- digit number

rman> configure controlfile autobackup format for device type disk to ‘/backup/rman/testdb/ control_%s.bkp’;

S- String

How to Change the control file auto backup for all RMAN backups

rman> configure controlfile autobackup on / off;

In case of auto –OFF, When will the control file backed up.

Full DB backup

Control file backup

System tablespace backup

Parallelism to speed up RMAN

Note: By default, only one channel will be used for backup. Max channel is 1600. Increasing channel will improve RMAN backup performance.

rman> configure device type disk parallelism 3;

Mirror backup



rman> configure datafile backup copies for device type disk to 2; — Default 1

rman> configure archivelog backup copies for device type disk to 2; — Default 1

Image copy configuration

It will backup all data blocks. By default RMAN backup only backup used block

rman> configure device type disk to backup type  to copy / backupset;

Compressed backup configuration

rman> configure device type disk to backup type compressed backupset;


Exclude & Include tablespace from full backup

rman> configure exclude for tablespace tools;

sql>  select * from v$tablesapce; — Check the tablespace exclusion — INC column will be no

rman> configure exclude for tablespace clear;

You can also, backup the database even with excluded tablespace.

rman> backup database noexclude;

rman> configure retention policy to recovery window of 30 days;

7 days – will be default. RMAN will expire the backup once the retention is completed, But it will not delete the backup physically, only mark as expiried.

rman> configure retention policy to redundancy 3;

Per day we can take 3 backup, the 4th backup will expire the 1st backup..

Either recovery window or redundancy can be used.


10g feature:  Optimization ON for Archivelogs

rman> configure backup optimization on;


It’s only for archive log. It will avoid the repeated backup of archive logs.

Optimization ON only backup the missing logs from the backup of all pervious.

Ex: We have 1 to 50 archive logs. Day 1 1 to 50 backed up day2 it will not backup again 1 to 50, only a new logs.


Recover expired backup from RMAN catalog option


We have 7 days retention, application team needs a two months old data needs to be restored.

The backup is available, but RMAN does not know, since it has 7 days retention and got expired it.

For these cases, we need to notify the RAMN from catalog database. Catalog is important one, which should be enabled for all servers.

From 10g onwards,

rman> catalog backuppiece  ‘\backup\rman\testdb\full_testdb_214732.bkp’;


Register & Unregister the manual HOT backup


Manually, We took hot backup, how to recover by using RMAN.

Solution: We need to register. It’s from 10g onwards.

For one datafile

rman> catalog start with ‘\backup\hotbkp\testdb\tools.dbf’;

For more datafile

rman> catalog start with ‘\backup\hotbkp\testdb\’;

rman> catalog archivelog with ‘\backup\hotbkp\testdb\arc1.arch’;  — Archive log


But, unregister we cannot do for all files. Only one by one.

rman> change bakuppiece  ‘\backup\hotbkp\testdb\ full_testdb_214732.bkp’ uncatalog;

rman> change archivelog all uncatalog;

rman> change datafile 1,2 uncatalog;

rman> change tablespace tools uncatalog;



Delete the backup and image copy

It will delete the file physically.


rman> show all;

rman> delete backup;

rman> delete backup of datafile 4;

rman> delete backup of tablespace tools;


Image copy

rman> delete copy of database;

rman> delete copy of datafile 4;

rman> delete copy of tablespace tools;


Delete backup Delete expired Delete obsolete
Removes physically Removes from RMAN repository Removes physically all backups, copies & archive logs based on the retention.



Command Use Catalog Needed
List command It’ll show all type of backup details, expire & Incarnations no
Report command which files need a backup, which backups are no longer needed, which files are in the schema-.dbf & TBS no
Show command Current RMAN configuration settings no
PRINT SCRIPT command It’ll show the  stored sripts Yes
Recovery catalog fixed views It’s a view same as list,report  & show Yes



Validate the backup & image copy expired or not

crosscheck command compares the RMAN  catalog entries with the actual OS files and reports to locate “expired” or “obsolete” RMAN catalog entries.


rman> crosscheck backup;

rman> crosscheck backup of datafile 4;

rman> crosscheck backup of tablespace tools;

Status: Available – good Expired — gone

rman> crosscheck copy of database;

rman> crosscheck copy of datafile 4;

rman> crosscheck copy of tablespace tools;



Day one DB has –1 to 50 Archive log

— Backup (1 to 50)

— Deleted (51 to 55)

— Backup archive log, it will fail since, no archive logs (51 to 55)

Since, we deleted manually RMAN does not knew it.

rman> crosscheck archivelog all;

RMAN will mark as expired/deleted. In case if you deleted archive log manually, Full backup needs to be taken.

Display expired backup

rman> report obsolete;

Delete the expired backup manually and from physical location.

rman> delete obsolete;

Type: yes


RMAN Run block

It’s a batch of command. We can execute more command in single run block. We need to allocate the  channel for this.

Key words: run {allocate channel c1 device type disk; commands release channel c1}



run {

allocate channel c1 device type disk;

backup database;

crosscheck archivelog all;

backup archivelog all;

release channel c1;


rman checksyntax – Will check the syntax.

RMAN> @rman_bkp.rman


How to create a run block as a script


Rman>  create script full_db_testdb


allocate channel c1 device type disk;

backup database;

crosscheck archivelog all;

backup archivelog all;

release channel c1;



 Rman>  run

Execute script full_db_testdb



Recovery Script:

Rman>  create script Df4_recovery


allocate channel c1 device type disk;

sql ‘ alter database datafile 4 offline drop’;

restore datafile 4;

Recover datafile 4;

sql ‘ alter database datafile 4 online’;

Release channel c1;



Note: All the scripts are stored in catalog DB


How to check the created script and definition

Login to catalog DB

Sql> select * from v$rc_script_line; — Script name

Sql> select * from v$rc_stored_script; – Definition of script


11 g feature:

We can validate the existing DB block corruption in online.

Rman> validate database;

Rman> validate datafile 4;

Rman> validate tablespace tools;

It will display the total & corrupted blocks. In case of any corruption we can get an advice from RMAN.

Rman> advise failure;

Rman> Repair failure preview; — It will display the script of oflline drop, restore & recover

Rman> Repair failure; — It will execute the recovery script



How to check the tape backup is working or not.



run {

allocate channel c1 device type sbt/sbt_tape;

release channel c1;


Error: You can see media manager layer. Third party library is not installed correctly.

Mostly the location of tool: ORACLE_HOME/bin/sbt*


I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

Your email address will not be published. Required fields are marked *

85 + = 90