Oracle

Oracle RMAN Configuration and Monitoring Scripts-14

Advertisements

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

All

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

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/disk1/%U’, ‘/disk2/%U’;

 

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

Scenario

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

Scenario

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

Unregister

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.

Backupset

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.
DELETE BACKUP; DELETE EXPIRED BACKUP; DELETE OBSOLETE;

 

 

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;

 

Scenario

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}

 

Rman>

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;

}

Execute:

 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

 

Scenario

How to check the tape backup is working or not.

 

Rman>

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*

http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmcncpt.htm#BABIHBBE

https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmquick.htm#BRADV89356

 

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 *

7 + 3 =