Hello Friend's,
Below are the scripts will help you to check the status of Rman restore and estimate how much more time will be needed to get it completed.
Hope this helps in your day to day DBA life .
Sctipt 1 : To check the overall progress of restore
TTITLE LEFT '% Completed. Aggregate is the overall progress:'SET LINE 132SELECT opname, round(sofar/totalwork*100) "% Complete" FROM gv$session_longops WHERE opname LIKE 'RMAN%' AND totalwork != 0 AND sofar <> totalwork ORDER BY 1;
Sample Output :
% Completed. Aggregate is the overall progress:
OPNAME % Complete
--------------------------------------------------------------- ----------
RMAN: aggregate input 85
RMAN: full datafile restore 80
Script 2 : To check the status of channel used in restore
TTITLE LEFT 'Channels waiting:'
COL client_info FORMAT A15 TRUNC
COL event FORMAT A20 TRUNC
COL state FORMAT A7
COL wait FORMAT 999.90 HEAD "Min waiting"
SELECT s.sid, p.spid, s.client_info, status, event, state, seconds_in_wait/60 wait
FROM gv$process p, gv$session s
WHERE p.addr = s.paddr
AND client_info LIKE 'rman%';
Sample Output :
Channels waiting
SID SPID CLIENT_INFO STATUS EVENT STATE Min waiting
--- ---- --------------- -------- -------------------- ------- -----------
143 1658 rman channel=C1 ACTIVE Backup: sbtrestore WAITING 85.57
142 1655 rman channel=C2 ACTIVE Backup: sbtread2 WAITING 4.15
141 1652 rman channel=C3 INACTIVE SQL*Net message from WAITING 75.72
124 1548 rman channel=C4 INACTIVE SQL*Net message from WAITING 41.42
Script 3 : To check the files that are currently used to write data
TTITLE LEFT 'Files currently being written to:'
COL filename FORMAT a50
SELECT filename, bytes/1024/1024/1024 GB, io_count
FROM v$backup_async_io
WHERE status='IN PROGRESS'
/
Sample output :
FILENAME BYTES IO_COUNT
---------------------------------------------------- ---------- ----------
/oracle/EMDPRD/oradata/SYSTEM.dbf 4251641154 65685
/oracle/EMDPRD/oradata/User01.dbf 4251641164 65682
Script 4 : To check the restoration speed , (Its estimated one)
TTITLE OFF
SET HEAD OFF
SELECT 'Throughput: '||
ROUND(SUM(v.value/1024/1024/1024),1) || ' Gig so far @ ' ||
ROUND(SUM(v.value /1024/1024)/NVL((SELECT MIN(elapsed_seconds)
FROM v$session_longops
WHERE opname LIKE 'RMAN: aggregate input'
AND sofar != TOTALWORK
AND elapsed_seconds IS NOT NULL
),SUM(v.value /1024/1024)),2) || ' Meg/sec'
FROM gv$sesstat v, v$statname n, gv$session s
WHERE v.statistic# = n.statistic#
AND n.name = 'physical write total bytes'
AND v.sid = s.sid
AND v.inst_id = s.inst_id
AND s.program LIKE 'rman@%'
GROUP BY n.name
/
SET HEAD ON
Sample Output :
Throughput: 204.3 Gig so far @ 435.87 Meg/sec
ConversionConversion EmoticonEmoticon