SQL QUERY TO MONITOR RMAN RESTORE IN ORACLE

SQL QUERY TO MONITOR RMAN RESTORE IN ORACLE

Its often a requirement for DBA to get the size for a database for determining various plans or activity prechecks to conclude the fastness,capacity,criticality feature of performing a critical change.

I was researching a way to find out the size of database when the database is in mount stage. As we cannot access any dba_* views during mount stage like dba_data_files …etc, we will not be able to get accurate or even approximate size of datafiles using any dynamic v$ views like v$datafile,v$datafile_header etc.. during a database restore. If we forget to note the approximate database size before restore, then it would be tedious to predict or guess the remaining time taken or percentage of time to complete the database restore.

Use this query to monitor rman restore progress approximately

set feedback off
set lines 200
set pages 1000
col INPUT_BYTES/1024/1024 format 9999999
col OUTPUT_BYTES/1024/1024 format 9999999
col OBJECT_TYPE format a10
set serveroutput off
variable s_num number;
BEGIN
  select sum((datafile_blocks)*8/1024) into :s_num from v$BACKUP_DATAFILE;
  dbms_output.put_line(:s_num);
END;
/
set feedback on
select INPUT_BYTES/1024/1024 as inp_byte,
OUTPUT_BYTES/1024/1024 as out_byte,
OBJECT_TYPE,
100*(MBYTES_PROCESSED/:s_num) as pctcomplete 
from v$rman_status 
where status like '%RUNNING%';

Another simple query

set lines 200 pages 1000
col INPUT_BYTES for 99999999999
col OUTPUT_BYTES for 999999999999
select INPUT_BYTES/1024/1024 as inp_byte,
OUTPUT_BYTES/1024/1024 as out_byte,
OBJECT_TYPE,
100*(MBYTES_PROCESSED/
(
select sum((datafile_blocks)*8/1024) 
from 
v$BACKUP_DATAFILE
)) as pctcomplete 
from 
v$rman_status 
where status like '%RUNNING%';

Sample output:

SQL> select INPUT_BYTES/1024/1024 as inp_byte,OUTPUT_BYTES/1024/1024 as out_byte,OBJECT_TYPE,100*(MBYTES_PROCESSED/(select sum((datafile_blocks)*8/1024) from v$BACKUP_DATAFILE)) as pctcomplete from v$rman_status where status like '%RUNNING%';

  INP_BYTE   OUT_BYTE OBJECT_TYPE   PCTCOMPLETE
---------- ---------- ------------- -----------
         0          0                         0
 9.9921875 327.703125 DB FULL         4.2905715