четверг, 25 февраля 2016 г.

Monitoring the recovery progress of Standard/Standby database using v$recovery_process


Here it is script to track recovery progress of your standard/standby database:
set linesize 255
set pagesize 60
col type format a30
col sofar_formatted format a20
col total_formatted format a15
col comments format a30

select to_char(start_time, 'dd-mon-rr hh24:mi:ss') start_time, type, item, units,
  sofar,
  case units
      when 'KB/sec' then round(sofar/1024,2) || ' MB/Sec'
      when 'Megabytes' then round(sofar/1024,2) || ' G'
      when 'Seconds' then to_char(cast(numtodsinterval(sofar, 'SECOND') as interval day(2) to second(2)), 'DD HH24:MI:SS')
      else to_char(sofar)
  end sofar_formatted,
  total,
    case units
      when 'KB/sec' then round(total/1024,2) || ' MB/Sec'
      when 'Megabytes' then round(total/1024,2) || ' G'
      when 'Seconds' then to_char(cast(numtodsinterval(total, 'SECOND') as interval day(2) to second(2)), 'DD HH24:MI:SS')
      else to_char(total)
  end total_formatted,
  comments
from v$recovery_progress;

Output:

set linesize 255
START_TIME                  TYPE                           ITEM                             UNITS                                 SOFAR SOFAR_FORMATTED           TOTAL TOTAL_FORMATTED COMMENTS
--------------------------- ------------------------------ -------------------------------- -------------------------------- ---------- -------------------- ---------- --------------- ------------------------------
11-feb-16 22:50:00          Media Recovery                 Log Files                        Files                                     8 8                             8 8
11-feb-16 22:50:00          Media Recovery                 Active Apply Rate                KB/sec                                 3316 3.24 MB/Sec                3316 3.24 MB/Sec
11-feb-16 22:50:00          Media Recovery                 Average Apply Rate               KB/sec                                  762 .74 MB/Sec                  762 .74 MB/Sec
11-feb-16 22:50:00          Media Recovery                 Maximum Apply Rate               KB/sec                                 9617 9.39 MB/Sec                9617 9.39 MB/Sec
11-feb-16 22:50:00          Media Recovery                 Redo Applied                     Megabytes                              1144 1.12 G                     1144 1.12 G
11-feb-16 22:50:00          Media Recovery                 Last Applied Redo                SCN+Time                                  0 0                             0 0               SCN: 6296684740127
11-feb-16 22:50:00          Media Recovery                 Active Time                      Seconds                                 214 +00 00:03:34.00             214 +00 00:03:34.00
11-feb-16 22:50:00          Media Recovery                 Apply Time per Log               Seconds                                  22 +00 00:00:22.00              22 +00 00:00:22.00
11-feb-16 22:50:00          Media Recovery                 Checkpoint Time per Log          Seconds                                   5 +00 00:00:05.00               5 +00 00:00:05.00
11-feb-16 22:50:00          Media Recovery                 Elapsed Time                     Seconds                                1537 +00 00:25:37.00            1537 +00 00:25:37.00

References