RMAN Basic Commands

For more exciting blogs on Oracle, please click the LINK

1. Crosscheck and delete expired archive logs

RMAN>list expired archivelog all;

RMAN>crosscheck archivelog all;

RMAN>delete noprompt expired archivelog all;

2. Delete archive logs keeping just a days archive logs

run{  
allocate channel C1 type disk;  
delete noprompt archivelog until time 'SYSDATE-1';   
release channel C1;  
}

3. Take incremental backup using SCN

run{  
allocate channel C1 type disk; 
BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 1093014094 DATABASE  FORMAT '/RMAN/backup_standby/incr_standby_%U';
release channel C1;  
}

4. Register an archive log file

alter database register logfile '/tmp/1_196022_887534485.dbf';

OR 

CATALOG ARCHIVELOG '/tmp/1_196021_887534485.dbf';

5. Restore archive logs for a particular sequence

run{
allocate channel ch01 type 'SBT_TAPE'
   parms 'ENV=(NB_ORA_POLICY=,NB_ORA_CLIENT=)';
set archivelog destination to '/tmp/';
restore archivelog from logseq=196263 until logseq=196267 thread=1;
}

6. Check Serial and SID of RMAN process

select b.sid, b.serial#, a.spid, b.client_info
from v$process a, v$session b
where a.addr=b.paddr and client_info like 'rman%';

7. Check RMAN Job status :

select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%'; 

OR

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

OR 

set lines 1200 pages 1200
col username for a10
col target for a10
col %done for a10
col opname for a30

select b.username,a.sid,b.opname,b.target,round(b.SOFAR*100 / b.TOTALWORK,0) || '%' as "%DONE",
  b.TIME_REMAINING/60,to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') START_TIME
  from V$SESSION_LONGOPS b,V$SESSION a where a.sid=b.sid and TIME_REMAINING <> 0 order by b.SOFAR/b.TOTALWORK;

8. Check RMAN backup job details

set linesize 500 pagesize 2000
col Hours format 9999.99
col STATUS format a10
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,
to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,
elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS	
order by session_key;

9. RMAN Disk backup

run{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate channel ch3 type disk;
backup incremental level 0
format '/RMAN/RMAN_BACKUP/df_incr_0_%d_t%t_s%s_p%p'
filesperset=100
(database);
release channel ch1;
release channel ch2;
release channel ch3;
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate channel ch3 type disk;
crosscheck archivelog all;
backup
format '/RMAN/RMAN_BACKUP/al_inc1_%d_t%t_s%s_p%p'
filesperset=100
(archivelog all delete input);
BACKUP CURRENT CONTROLFILE format '/RMAN/RMAN_BACKUP/ct_%d_t%t_s%s_p%p';
release channel ch1;
release channel ch2;
release channel ch3;
}

10. To check the space limit, space available and percent full for RMAN

SELECT NAME, TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT, TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE, '999,999,999,999') AS SPACE_AVAILABLE, ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS PERCENT_FULL FROM V$RECOVERY_FILE_DEST;

11. Check RMAN Job status and percentage completion

col dbsize_mbytes      for 99,999,990.00 justify right head "DBSIZE_MB"
col input_mbytes       for 99,999,990.00 justify right head "READ_MB"
col output_mbytes      for 99,999,990.00 justify right head "WRITTEN_MB"
col output_device_type for a10           justify left head "DEVICE"
col complete           for 990.00        justify right head "COMPLETE %" 
col compression        for 990.00        justify right head "COMPRESS|% ORIG"
col est_complete       for a20           head "ESTIMATED COMPLETION"
col recid              for 9999999       head "ID"

select recid
     , output_device_type
     , dbsize_mbytes
     , input_bytes/1024/1024 input_mbytes
     , output_bytes/1024/1024 output_mbytes
     , (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 dbsize_mbytes from v$datafile) 
 where status='RUNNING'
   and output_device_type is not null

12. Recover corrupted blocks using RMAN

a. Start SQL*Plus and connect to the target database.

b. Query V$DATABASE_BLOCK_CORRUPTION to determine whether corrupt blocks exist. For example, execute the following statement:

SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
c. Start RMAN and connect to the target database.

d. Recover all blocks marked corrupt in V$DATABASE_BLOCK_CORRUPTION.

The following command repairs all physically corrupted blocks recorded in the view:

RMAN> RECOVER CORRUPTION LIST;

After the blocks are recovered, the database removes them from V$DATABASE_BLOCK_CORRUPTION.

Submit a Comment Cancel reply