RMAN COMMANDS

Useful RMAN commands

followed very good article by

http://rd-oracledba.blogspot.com

This article about various RMAN commands which are useful in our daily job.

RMAN will take database backup in two methods

 1.backup set and 2.image copy.

By default method is backup set.

Diff between backup set and image copy:

==============================

A backup set consists of one or more backup pieces, which are physical files written in a format that only RMAN can access.You can also take compressed backups using backup set.

Image copy is a bit-for-bit copy of a database file created on disk. Image copies are identical to copies created with operating system commands like cp on Linux or COPY on Windows, but are recorded in the RMAN repository and so are usable by RMAN.

Monitor RMAN Backups:

==================

set pages 2000 lines 200

COL STATUS FORMAT a9

col START_TIME for a20

col END_TIME for a20

COL hrs FORMAT 999.99

select INPUT_TYPE,

STATUS,

TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,

TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,

ELAPSED_SECONDS/3600 hrs,

INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,

OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,

OUTPUT_DEVICE_TYPE

FROM V$RMAN_BACKUP_JOB_DETAILS

order by SESSION_KEY;

To check the progress of your current RMAN backup use this script:

 select recid , output_device_type, dbsize_mbytes, input_bytes/1024/1024/1024 input_gbytes
, output_bytes/1024/1024/1024 output_gbytes , (output_bytes/input_bytes*100) compression
, (mbytes_processed/dbsize_mbytes*100) complete
 , to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete
from v$rman_status rs , (select sum(bytes)/1024/1024/1024 dbsize_mbytes from v$datafile) 
where status='RUNNING' and output_device_type is not null

BACKUP SET examples :

===================

* To take full backup using tag:

RMAN> BACKUP AS BACKUPSET TAG 'FULL_DB' DATABASE PLUS ARCHIVELOG;

* To take compressed backups:

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

* To creates a level 1 differential incremental backup:

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

* To creates a level 1 cumulative incremental backup:

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

* To take archivelog backup:

rman> backup archivelog all;

rman> backup filesperset 10 archivelog all;

rman> backup archivelog all delete all input;  

rman> backup archivelog all not backed up 1 times;

rman> backup archivelog all not backed up 1 times tag 'arch_tape_backup';

rman> backup  as compressed backupset filesperset 1 archivelog until time 'sysdate - 2/24';

* To list backups:

rman> list backup summary;

rman> list backup;

rman> list backup of database:

rman> list backuppiece 'db_rameshxsx_vgdffj_01';

rman> list backupset 13202;

rman> list backup of datafile 10;

rman> list backupset of datafile 10;

* To list and delete archive files:

rman> list archivelog all;

rman> list backup of archivelog all;

rman> list archivelog from sequence 1145 until sequence 1250;

rman> list backup of archivelog until time 'sysdate-2';

rman> list backup of archivelog from sequence 78895 until sequence 92566;

rman> delete archivelog all;

rman> delete archivelog until time 'sysdate -2';

rman> delete noprompt archivelog until time 'sysdate -3/24';

rman> delete archivelog all completed before 'sysdate-5';

rman> delete noprompt archivelog all  backed up 1 times to device type sbt;

rman> delete noprompt archivelog until time 'sysdate-2' backed up 1 times to device type disk;

rman> delete noprompt archivelog until time 'sysdate - 2/24' backed up 1 times to device type disk;

Note: All delete all input means RMAN will delete archive logs from archive destinations once they are backed up.

Image copy examples:

===================

* To take full image backup with archivelogs

RMAN> BACKUP AS COPY TAG 'FULL_DB' DATABASE PLUS ARCHIVELOG; // with archive logs

RMAN> BACKUP AS COPY INCREMENTAL LEVEL 0 TAG  'FULL_DB'  DATABASE;

To take full image backup to specific location:

rman> backup as copy database tag 'full_db' format '/u02/backup/%b';

rman> backup as copy database tag 'full_db' format '/u01/backup/%f_%b';

(or)

allocate channel ch1 device type disk format '/u02/backup/%b';

backup as copy database tag 'full_db';

release channel ch1;

* To take incremental for image backup:

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'FULL_DB' DATABASE;

* Rollforward full image backup with incremental:

RMAN> RECOVER COPY OF DATABASE WITH TAG 'FULL_DB';

or 

RMAN> RECOVER COPY OF DATABASE WITH TAG 'FULL_DB' UNTIL TIME 'SYSDATE-1';

*  Back Up Controlfile  as copy:

RMAN>BACKUP AS COPY CURRENT CONTROLFILE;

* Backup recovery area to tape:

RMAN> BACKUP RECOVERY AREA TAG 'FULL_DB';

* To take archivelog copy backup

RMAN> BACKUP AS COPY ARCHIVELOG;

* To delete archivelogs copy backup:

RMAN> DELETE COPY OF ARCHIVELOG;

* To list copy backups:

RMAN> LIST COPY OF BACKUP;

RMAN> LIST COPY OF ARCHIVELOG;

RMAN> LIST COPY OF ARCHIVELOG FROM SEQUENCE 10;

RMAN> List Copy of archivelog from sequence 10 until sequence 20;

RMAN> LIST COPY;

* Delete copy backups:

RMAN> delete copy of database tag='REFRESH_DB_8th_DEC';

RMAN> delete copy TAG='REFRESH_DB_8th_DEC';

Crosscheck and Expired:

=====================

Crosschecks update RMAN repository information about backups whose repository records do not match their physical status.

For example, if a user removes archived logs from disk with an os commands (ex: rm) , the repository still indicates that the logs are on disk, when in fact logs are not there .So by using crosscheck command we are checking physical existance of files and if files are not there then rman will mark these missing/deleted files as expired.

rman> crosscheck backup;

rman> crosscheck backup of database;

rman> crosscheck backupset;

rman> crosscheck copy;

rman> crosscheck archivelog all ;

rman> list expired;

rman> list expired archivelog all;

rman> list expired backup;

rman> list expired backup of archivelog all;

rman> delete expired backup;

rman> delete expired archivelog all;

rman> delete force noprompt expired copy tag='REFRESH_DB_8th_DEC';

Obsolete backups:

================

Backups will become obsolete based on retention policy.For example if retention policy is "recovery window 7 days" then rman will mark backups as an obsolete

after 7 days.

RMAN> REPORT OBSOLETE;

RMAN> DELETE OBSOLETE;

RMAN> DELETE FORCE OBSOLETE;

validate :

==========

You can use validate cmd to check database files and archived redo log files are physically and logically  corrupted.

RMAN> BACKUP VALIDATE;

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

RMAN> VALIDATE BACKUPSET 3;

RMAN> VALIDATE DATAFILE 5;

RMAN> VALIDATE DATAFILECOPY ALL;

Catalog files :

==============

Add information about file copies and user-managed backups to the RMAN repository.

CATALOG START WITH '/u02/backup/';

CATALOG START WITH '+FRA_DG';

CATALOG BACKUPPIECE '/u02/backup/annnf0_full1_0.276.895785483';

CATALOG DATAFILECOPY '/u02/backup/users01.dbf' LEVEL 0;

CATALOG ARCHIVELOG '/u02/backup/thread_1_seq_8.321.895785769', '/u02/backup/thread_2_seq_2.326.895781619';

Change :

======

Change command will update the availability status of backups and copies recorded in the RMAN repository.For example if you don't want any particular backup then we can mark them as unavailable.

CHANGE BACKUPSET 4 UNAVAILABLE;

CHANGE ARCHIVELOG ALL UNCATALOG; // for suppose if we moved archive files from one location to another location...then we can uncatlog those files by using change cmd.

Configuration settings :

=================

you can change rman configuration settings and below is one of the example to change the retention policy.

rman> show all;

rman> configure retention policy to redundancy 3;

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

rman> configure retention policy clear;   // to set to default 

Backup controlfile to trace :

======================

RMAN> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

RMAN> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/U02/BACKUP/CONTROLFILE.CTL';

Control File and Server Parameter File Autobackups:

=======================================

If CONFIGURE CONTROLFILE AUTOBACKUP is ON, then RMAN automatically backs up the control file and the current sp file at the end of a successful BACKUP command.

RMAN> show CONTROLFILE AUTOBACKUP;

RMAN configuration parameters for database with db_unique_name RAMESH1 are:

CONFIGURE CONTROLFILE AUTOBACKUP ON; # default

To run the rman script:

=======================

rman target / @rman_backup.cmd log=rman_backup.log

rman_backup.cmd

---------------

run

{ 

allocate channel ch1 device type disk FORMAT '/u02/backup/%b';

BACKUP AS COPY DATABASE TAG 'FULL_DB';

release channel ch1;

}

Trace a rman session:

===============

rman target / debug trace rman_ramesh1_crosscheck.trc log rman_ramesh1_crosscheck.log

RUN

{

ALLOCATE CHANNEL C1 DEVICE TYPE DISK;

ALLOCATE CHANNEL C2 DEVICE TYPE SBT;

ALLOCATE CHANNEL C3 DEVICE TYPE DISK;

ALLOCATE CHANNEL C4 DEVICE TYPE SBT;

CROSSCHECK COPY;

}

control_file_record_keep_time parameter value change:

========================================

select name, ISSYS_MODIFIABLE from v$parameter where name='control_file_record_keep_time';

alter system set control_file_record_keep_time=10 scope=both sid='*';