@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display DataGuard Lag
*  Parameters : NONE
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  30-Jul-12  Vishal Gupta  Created
*  
*/

PROMPT *********************************************
PROMPT *    R E C O V E R Y     P R O G R E S S    *
PROMPT *********************************************

COLUMN inst_id             HEADING "I#"                            FORMAT 99
COLUMN start_time          HEADING "Start Time"                    FORMAT a18
COLUMN type                HEADING "Type"                          FORMAT a20
COLUMN item                HEADING "Item"                          FORMAT a25
COLUMN units               HEADING "Units"                         FORMAT a10
COLUMN sofar               HEADING "SoFar"                         FORMAT a45
COLUMN total               HEADING "Total"                         FORMAT 999,999,999,999
COLUMN timestamp           HEADING "Start Time"                    FORMAT a18
COLUMN comments            HEADING "Comments"                      FORMAT a18

SELECT inst_id
     --, TO_CHAR(r.start_time,'DD-MON-YY HH24:MI:SS') start_time
     --, r.type
     , item
     , CASE item
            WHEN 'Last Applied Redo' THEN TO_CHAR(timestamp,'DD-MON-YY HH24:MI:SS') || ' , ' || comments 
                                          || chr(10) || 'ApplyLag - ' ||  REPLACE(REPLACE(TO_CHAR( CAST(sysdate as TIMESTAMP) - timestamp)
                                                                                          ,'+0000000','+')
                                                                                 ,'.000000','')
            WHEN 'Active Time'       THEN FLOOR(sofar/3600) || 'h ' || FLOOR(MOD(sofar,3600)/60) || 'm ' ||  MOD(sofar,60) || 's' 
            WHEN 'Elapsed Time'      THEN FLOOR(sofar/3600) || 'h ' || FLOOR(MOD(sofar,3600)/60) || 'm ' ||  MOD(sofar,60) || 's'
                                          || chr(10) || 'StartTime: ' || TO_CHAR(start_time,'DD-MON-YY HH24:MI:SS') 
            ELSE TRIM(TO_CHAR(sofar,'999,999,999,999')) || ' ' || units 
       END  SoFar
     --, r.total
  FROM ( SELECT r.inst_id
              --, NVL(r.item,d.item) item
              , r.item
              , NVL(r.timestamp,d.timestamp) timestamp
              --, r.timestamp  timestamp
              , r.start_time
              , r.comments
              , r.sofar
              , r.units
         FROM (SELECT 'Last Applied Redo' item
                    , controlfile_time timestamp
                FROM v$database ) d
              LEFT OUTER JOIN gv$recovery_progress r ON 1=1
              LEFT OUTER JOIN (SELECT MAX(start_time) start_time from gv$recovery_progress) r2 ON r.start_time =  r2.start_time
       ) 
 ORDER BY DECODE(item
                 ,'Last Applied Redo',1
                 ,'Standby Apply Lag',2
                 ,'Active Apply Rate',3
                 ,'Average Apply Rate',4
                 ,'Maximum Apply Rate',5
                 ,'Redo Applied',6
                 ,'Log Files',7
                 ,'Apply Time per Log',8
                 ,'Checkpoint Time per Log',9
                 ,'Active Time',21
                 ,'Elapsed Time',22
                 ,999
                 )
;

@@footer