@@header

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

PROMPT *****************************************************************
PROMPT *  D A T A G U A R D    L A G
PROMPT *****************************************************************

COLUMN name                HEADING "Name"                        FORMAT a23
COLUMN value               HEADING "Value"                       FORMAT a20
COLUMN unit                HEADING "Unit"                        FORMAT a30
COLUMN time_computed       HEADING "TimeComputed"                FORMAT a20
COLUMN datum_time          HEADING "datum_time"                  FORMAT a20


SELECT ds.name
     , ds.value
     , ds.unit
     , ds.time_computed
     , ds.datum_time
  FROM v$dataguard_stats ds
;

/*
COLUMN dest_id             HEADING "Dest|Id"                     FORMAT 9999
COLUMN destination         HEADING "Destination"                 FORMAT a20
COLUMN dest_status         HEADING "Dest|Status"                 FORMAT a8
COLUMN type                HEADING "Type"                        FORMAT a10
COLUMN database_mode       HEADING "Database Mode"               FORMAT a15
COLUMN recovery_mode       HEADING "Recovery Mode"               FORMAT a23
COLUMN protection_mode     HEADING "Protection Mode"             FORMAT a20
COLUMN gap_status          HEADING "GAP|Status"                  FORMAT a6
COLUMN applied             HEADING "A|p|p|l|i|e|d"               FORMAT a1   TRUNCATE
COLUMN archived            HEADING "A|r|c|h|e|v|e|d"             FORMAT a1   TRUNCATE
COLUMN deleted             HEADING "D|e|l|e|t|e|d"               FORMAT a1   TRUNCATE
COLUMN archlog_status      HEADING "S|t|a|t|u|s"                 FORMAT a1   TRUNCATE
COLUMN max_sequence#       HEADING "Max Sequence#"               FORMAT 999999
COLUMN max_next_time       HEADING "Max Next Time"               FORMAT a18
COLUMN max_first_time      HEADING "Max First Time"              FORMAT a18

--BREAK ON applied ON archived ON deleted on status SKIP 1 

SELECT l.dest_id
     , s.destination
     , s.status   dest_status
     , s.type
     , s.database_mode
     , s.recovery_mode
     , s.protection_mode
     , s.gap_status
     , l.applied
     , l.archived
     , l.deleted
     , l.status    archlog_status
     --, l.thread#
     --, max(l.sequence#) max_sequence#
     , TO_CHAR(max(l.next_time),'DD-MON-YY hh24:MI:SS')  max_next_time
     --, TO_CHAR(max(l.first_time),'DD-MON-YY hh24:MI:SS') max_first_time
     , ROUND((SUM(l.block_size * l.blocks)/1024/1024/1024),2)  redo_size_GB
 FROM v$archived_log l
    , v$archive_dest_status s
WHERE l.dest_id = s.dest_id     
 GROUP BY l.dest_id
        , s.destination
        , s.status
        , s.type
        , s.database_mode
        , s.recovery_mode
        , s.protection_mode
        , s.gap_status
        , l.applied
        , l.archived
        , l.deleted
        , l.status
        --, l.thread#
 ORDER BY l.applied desc
        , l.dest_id
 ;
*/
 
/* 
SELECT s.dest_id
     , s.destination
     , s.status
     , s.type
     , s.database_mode
     , s.recovery_mode
     , s.protection_mode
     , s.gap_status
     , s.SRL
  FROM v$archive_dest_status s
 WHERE NOT (type = 'LOCAL' AND database_mode = 'UNKNOWN');
*/
 

@@footer