DHARAMDBA: RMAN USEFUL COMMANDS

Here I am introducing some very useful RMAN commands related to backup .

1. If you want to take backup of archivelogs and dont want backup set to be more than 50mb in size then.. It will create backup sets of size 50mb each. Whenever backupset size will go beyond 50mb then a new backupset will be created. Hence each backupset will have size 50mb only.

RMAN> backup maxsetsize 50m archivelog all;

Starting backup at 08-JAN-12
current log archived
using channel ORA_DISK_2
channel ORA_DISK_2: starting archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=1 sequence=8 recid=1 stamp=771287373
input archive log thread=1 sequence=9 recid=2 stamp=771327757
input archive log thread=1 sequence=10 recid=3 stamp=771358788
input archive log thread=1 sequence=11 recid=4 stamp=771384567
input archive log thread=1 sequence=12 recid=5 stamp=771418375
input archive log thread=1 sequence=13 recid=6 stamp=771425997
input archive log thread=1 sequence=14 recid=7 stamp=771439835
input archive log thread=1 sequence=15 recid=8 stamp=771500404
input archive log thread=1 sequence=16 recid=9 stamp=771537279
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\3EN08JID_1_1.BKP tag=TAG20120108T135524 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:09
channel ORA_DISK_2: starting archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=1 sequence=27 recid=10 stamp=771890208
input archive log thread=1 sequence=28 recid=11 stamp=771890208
input archive log thread=1 sequence=29 recid=12 stamp=771890314
input archive log thread=1 sequence=30 recid=13 stamp=771928314
input archive log thread=1 sequence=31 recid=14 stamp=771967956
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\3FN08JIM_1_1.BKP tag=TAG20120108T135524 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:09
channel ORA_DISK_2: starting archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=1 sequence=32 recid=15 stamp=772019025
input archive log thread=1 sequence=33 recid=16 stamp=772027123
input archive log thread=1 sequence=34 recid=17 stamp=772031598
input archive log thread=1 sequence=35 recid=18 stamp=772033405
input archive log thread=1 sequence=36 recid=19 stamp=772034021
input archive log thread=1 sequence=37 recid=20 stamp=772034088
input archive log thread=1 sequence=38 recid=21 stamp=772034104
input archive log thread=1 sequence=39 recid=22 stamp=772034112
input archive log thread=1 sequence=40 recid=23 stamp=772034124
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\3GN08JJ0_1_1.BKP tag=TAG20120108T135524 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:05
Finished backup at 08-JAN-12

Starting Control File and SPFILE Autobackup at 08-JAN-12
piece handle=H:\RMAN\C-2590818756-20120108-10.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-12

2. If you want to skip some inaccessible ,offline or read only files from full database backup then here is the command.It will ignore inaccessible ,offline or read only files.

SQL> select file_name,file_id,online_status from dba_data_files;

FILE_NAME                         FILE_ID ONLINE_
------------------------------ ---------- -------
H:\RMAN\DATA_D-FINANCE_I-25908          4 ONLINE
18756_TS-USERS_FNO-4_1IMVMG63.
BKP

H:\FINANCE\SYSAUX01.DBF                 3 ONLINE
H:\FINANCE\UNDOTBS01.DBF                2 RECOVER
H:\FINANCE\SYSTEM01.DBF                 1 SYSTEM

RMAN> backup database

2> skip inaccessible

3> skip offline

4> skip readonly;

Starting backup at 08-JAN-12
using channel ORA_DISK_2
skipping offline file 2
RMAN-06060: WARNING: skipping datafile compromises tablespace UNDOTBS1 recoverability
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00001 name=H:\FINANCE\SYSTEM01.DBF
input datafile fno=00003 name=H:\FINANCE\SYSAUX01.DBF
input datafile fno=00004 name=H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4_1IMVMG63.BKP
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\3IN08K0L_1_1.BKP tag=TAG20120108T140301 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:56
Finished backup at 08-JAN-12

Starting Control File and SPFILE Autobackup at 08-JAN-12
piece handle=H:\RMAN\C-2590818756-20120108-11.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-12

3. If you want to start a RMAN backup job which should complete in specified time then you can use following command. Here i am specifying 1 minute for database backup job to complete. If job is not complete in 1 minute then it will be aborted.

RMAN> backup duration 00:01 database;

Starting backup at 08-JAN-12
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=H:\FINANCE\SYSAUX01.DBF
input datafile fno=00002 name=H:\FINANCE\UNDOTBS01.DBF
channel ORA_DISK_1: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00001 name=H:\FINANCE\SYSTEM01.DBF
input datafile fno=00004 name=H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4
_1IMVMG63.BKP
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_1: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN\2IN0887G_1_1.BKP tag=TAG20120108T104152 comment=NONE
channel ORA_DISK_1: starting piece 2 at 08-JAN-12
channel ORA_DISK_1: finished piece 2 at 08-JAN-12
piece handle=H:\RMAN\2IN0887G_2_1.BKP tag=TAG20120108T104152 comment=NONE
channel ORA_DISK_1: starting piece 3 at 08-JAN-12
channel ORA_DISK_1: finished piece 3 at 08-JAN-12
piece handle=H:\RMAN\2IN0887G_3_1.BKP tag=TAG20120108T104152 comment=NONE
channel ORA_DISK_1: starting piece 4 at 08-JAN-12
channel ORA_DISK_1: finished piece 4 at 08-JAN-12
piece handle=H:\RMAN\2IN0887G_4_1.BKP tag=TAG20120108T104152 comment=NONE
channel ORA_DISK_1: starting piece 5 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\2JN0887G_1_1.BKP tag=TAG20120108T104152 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:57
channel ORA_DISK_1: finished piece 5 at 08-JAN-12
piece handle=H:\RMAN\2IN0887G_5_1.BKP tag=TAG20120108T104152 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:58
Finished backup at 08-JAN-12

Starting Control File and SPFILE Autobackup at 08-JAN-12
piece handle=H:\RMAN\C-2590818756-20120108-06.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-12

4. In above command the oracle is putting performance impact on database in order to complete the backup job in given time. In following command Oracle is trying to complete the job in given time without affecting the database performance . Since Performance of database is prime here hence 1 min time is note enough to complete the job and aborted.

RMAN> backup duration 00:01 minimize time database;

Starting backup at 08-JAN-12
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=H:\FINANCE\SYSTEM01.DBF
input datafile fno=00004 name=H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4
_1IMVMG63.BKP
channel ORA_DISK_1: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00003 name=H:\FINANCE\SYSAUX01.DBF
input datafile fno=00002 name=H:\FINANCE\UNDOTBS01.DBF
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_1: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN\2LN0890E_1_1.BKP tag=TAG20120108T105510 comment=NONE
channel ORA_DISK_1: starting piece 2 at 08-JAN-12
channel ORA_DISK_1: finished piece 2 at 08-JAN-12
piece handle=H:\RMAN\2LN0890E_2_1.BKP tag=TAG20120108T105510 comment=NONE
channel ORA_DISK_1: starting piece 3 at 08-JAN-12
channel ORA_DISK_1: finished piece 3 at 08-JAN-12
piece handle=H:\RMAN\2LN0890E_3_1.BKP tag=TAG20120108T105510 comment=NONE
channel ORA_DISK_1: starting piece 4 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\2MN0890E_1_1.BKP tag=TAG20120108T105510 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:39
channel ORA_DISK_1: finished piece 4 at 08-JAN-12
piece handle=H:\RMAN\2LN0890E_4_1.BKP tag=TAG20120108T105510 comment=NONE
channel ORA_DISK_1: starting piece 5 at 08-JAN-12
channel ORA_DISK_1: finished piece 5 at 08-JAN-12
piece handle=H:\RMAN\2LN0890E_5_1.BKP tag=TAG20120108T105510 comment=NONE
channel ORA_DISK_1: starting piece 6 at 08-JAN-12
channel ORA_DISK_1: finished piece 6 at 08-JAN-12
piece handle=H:\RMAN\2LN0890E_6_1.BKP tag=TAG20120108T105510 comment=NONE
channel ORA_DISK_1: starting piece 7 at 08-JAN-12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/08/2012 10:56:
11
ORA-19591: backup aborted because job time exceeded duration time

4. If we increase the time then Backup job is successful.

RMAN> backup duration 00:02 minimize time database;

Starting backup at 08-JAN-12
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=H:\FINANCE\SYSTEM01.DBF
input datafile fno=00004 name=H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4
_1IMVMG63.BKP
channel ORA_DISK_1: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00003 name=H:\FINANCE\SYSAUX01.DBF
input datafile fno=00002 name=H:\FINANCE\UNDOTBS01.DBF
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_1: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN\2NN08937_1_1.BKP tag=TAG20120108T105639 comment=NONE
channel ORA_DISK_1: starting piece 2 at 08-JAN-12
channel ORA_DISK_1: finished piece 2 at 08-JAN-12
piece handle=H:\RMAN\2NN08937_2_1.BKP tag=TAG20120108T105639 comment=NONE
channel ORA_DISK_1: starting piece 3 at 08-JAN-12
channel ORA_DISK_1: finished piece 3 at 08-JAN-12
piece handle=H:\RMAN\2NN08937_3_1.BKP tag=TAG20120108T105639 comment=NONE
channel ORA_DISK_1: starting piece 4 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\2ON08938_1_1.BKP tag=TAG20120108T105639 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:38
channel ORA_DISK_1: finished piece 4 at 08-JAN-12
piece handle=H:\RMAN\2NN08937_4_1.BKP tag=TAG20120108T105639 comment=NONE
channel ORA_DISK_1: starting piece 5 at 08-JAN-12
channel ORA_DISK_1: finished piece 5 at 08-JAN-12
piece handle=H:\RMAN\2NN08937_5_1.BKP tag=TAG20120108T105639 comment=NONE
channel ORA_DISK_1: starting piece 6 at 08-JAN-12
channel ORA_DISK_1: finished piece 6 at 08-JAN-12
piece handle=H:\RMAN\2NN08937_6_1.BKP tag=TAG20120108T105639 comment=NONE
channel ORA_DISK_1: starting piece 7 at 08-JAN-12
channel ORA_DISK_1: finished piece 7 at 08-JAN-12
piece handle=H:\RMAN\2NN08937_7_1.BKP tag=TAG20120108T105639 comment=NONE
channel ORA_DISK_1: starting piece 8 at 08-JAN-12
channel ORA_DISK_1: finished piece 8 at 08-JAN-12
piece handle=H:\RMAN\2NN08937_8_1.BKP tag=TAG20120108T105639 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:11
Finished backup at 08-JAN-12

Starting Control File and SPFILE Autobackup at 08-JAN-12
piece handle=H:\RMAN\C-2590818756-20120108-07.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-12

5. In following command Oracle will try to adjust database time taken to complete backup job according to rate of taking backup. If backup is going to take less time than specified then Oracle will automatically slowdown the backup speed in order to maximize performance.

RMAN> backup duration 00:02 minimize time minimize load database;

Starting backup at 08-JAN-12
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=H:\FINANCE\SYSAUX01.DBF
input datafile fno=00002 name=H:\FINANCE\UNDOTBS01.DBF
channel ORA_DISK_1: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00001 name=H:\FINANCE\SYSTEM01.DBF
input datafile fno=00004 name=H:\RMAN\DATA_D-FINANCE_I-2590818756_TS-USERS_FNO-4
_1IMVMG63.BKP
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_1: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN\2QN08965_1_1.BKP tag=TAG20120108T105813 comment=NONE
channel ORA_DISK_1: starting piece 2 at 08-JAN-12
channel ORA_DISK_1: finished piece 2 at 08-JAN-12
piece handle=H:\RMAN\2QN08965_2_1.BKP tag=TAG20120108T105813 comment=NONE
channel ORA_DISK_1: starting piece 3 at 08-JAN-12
channel ORA_DISK_1: finished piece 3 at 08-JAN-12
piece handle=H:\RMAN\2QN08965_3_1.BKP tag=TAG20120108T105813 comment=NONE
channel ORA_DISK_1: starting piece 4 at 08-JAN-12
channel ORA_DISK_1: finished piece 4 at 08-JAN-12
piece handle=H:\RMAN\2QN08965_4_1.BKP tag=TAG20120108T105813 comment=NONE
channel ORA_DISK_1: starting piece 5 at 08-JAN-12
channel ORA_DISK_1: finished piece 5 at 08-JAN-12
piece handle=H:\RMAN\2QN08965_5_1.BKP tag=TAG20120108T105813 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:01
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\2RN08965_1_1.BKP tag=TAG20120108T105813 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:02
Finished backup at 08-JAN-12

Starting Control File and SPFILE Autobackup at 08-JAN-12
piece handle=H:\RMAN\C-2590818756-20120108-08.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-12

6. Following command will take backup of archivelogs generated in between two scn numbers.

RMAN> backup archivelog from scn 901427 until scn 1296996;

Starting backup at 08-JAN-12
using channel ORA_DISK_2
channel ORA_DISK_2: starting archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=1 sequence=15 recid=8 stamp=771500404
input archive log thread=1 sequence=16 recid=9 stamp=771537279
input archive log thread=1 sequence=27 recid=10 stamp=771890208
input archive log thread=1 sequence=28 recid=11 stamp=771890208
input archive log thread=1 sequence=29 recid=12 stamp=771890314
input archive log thread=1 sequence=30 recid=13 stamp=771928314
input archive log thread=1 sequence=31 recid=14 stamp=771967956
input archive log thread=1 sequence=32 recid=15 stamp=772019025
input archive log thread=1 sequence=33 recid=16 stamp=772027123
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\36N08IF2_1_1.BKP tag=TAG20120108T133633 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:09
Finished backup at 08-JAN-12

Starting Control File and SPFILE Autobackup at 08-JAN-12
piece handle=H:\RMAN\C-2590818756-20120108-0C.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-12

7. Following command will take backup of archivelogs generated between 1st january 2012 and current date.

RMAN> backup archivelog from time '01-jan-2012' until time 'sysdate';

Starting backup at 08-JAN-12
using channel ORA_DISK_2
channel ORA_DISK_2: starting archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=1 sequence=11 recid=4 stamp=771384567
input archive log thread=1 sequence=12 recid=5 stamp=771418375
input archive log thread=1 sequence=13 recid=6 stamp=771425997
input archive log thread=1 sequence=14 recid=7 stamp=771439835
input archive log thread=1 sequence=15 recid=8 stamp=771500404
input archive log thread=1 sequence=16 recid=9 stamp=771537279
input archive log thread=1 sequence=27 recid=10 stamp=771890208
input archive log thread=1 sequence=28 recid=11 stamp=771890208
input archive log thread=1 sequence=29 recid=12 stamp=771890314
input archive log thread=1 sequence=30 recid=13 stamp=771928314
input archive log thread=1 sequence=31 recid=14 stamp=771967956
input archive log thread=1 sequence=32 recid=15 stamp=772019025
input archive log thread=1 sequence=33 recid=16 stamp=772027123
input archive log thread=1 sequence=34 recid=17 stamp=772031598
channel ORA_DISK_2: starting piece 1 at 08-JAN-12
channel ORA_DISK_2: finished piece 1 at 08-JAN-12
piece handle=H:\RMAN2\38N08IO1_1_1.BKP tag=TAG20120108T134120 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:01
Finished backup at 08-JAN-12

Starting Control File and SPFILE Autobackup at 08-JAN-12
piece handle=H:\RMAN\C-2590818756-20120108-0D.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-12

8. Following command will take backup of archivelogs generated after 05 jan 2012 to current date.

RMAN> backup archivelog from time '05-jan-2012';

DHARAMDBA

About Me

Sunday 19 February 2012

RMAN USEFUL COMMANDS

3 comments:

Visitors Location

Usefull links For DBA

STATS