@@header

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

PROMPT *****************************************************************
PROMPT *  DataGuard Destionation Status
PROMPT *****************************************************************

COLUMN dest_id             HEADING "Dest|Id"                     FORMAT 9999
COLUMN destination         HEADING "Destination"                 FORMAT a20
COLUMN status              HEADING "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 a10
COLUMN applied             HEADING "Applied"                     FORMAT a9
COLUMN archived            HEADING "Archived"                    FORMAT a7
COLUMN deleted             HEADING "Deleted"                     FORMAT a7
COLUMN max_sequence#       HEADING "Max Sequence#"               FORMAT 999999
COLUMN max_next_time       HEADING "Max Next Time"               FORMAT a18
COLUMN min_next_time       HEADING "Min 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.applied
     , l.dest_id
     , s.destination
     , s.status
     , s.type
     , s.database_mode
     , s.recovery_mode
     , s.protection_mode
     , s.gap_status
     , l.archived
     , l.deleted
     , l.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(min(l.next_time),'DD-MON-YY hh24:MI:SS')  min_next_time
     , ROUND((SUM(l.block_size * l.blocks)/1024/1024/1024),2)     redo_size_GB
     --, TO_CHAR(max(l.first_time),'DD-MON-YY hh24:MI:SS') max_first_time
  FROM v$archived_log l
     , v$archive_dest_status s
WHERE l.dest_id = s.dest_id    
  AND NOT (s.type = 'LOCAL' and s.database_mode = 'UNKNOWN')
 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
 ;

@@footer