@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display Archive Destination Configuration
*  Parameters : NONE
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  21-Apr-15  Vishal Gupta  Conditional multiple Oracle version output
*  09-May-14  Vishal Gupta  Output layout changes
*  19-Mar-14  Vishal Gupta  Created
*  
*/

PROMPT *****************************************************************
PROMPT *  Archive Destinations
PROMPT *****************************************************************

COLUMN dest_id             HEADING "Dest|Id"                     FORMAT 99
COLUMN dest_name           HEADING "Dest Name"                   FORMAT a20
COLUMN db_unique_name      HEADING "UniqueName"                  FORMAT a15
COLUMN destination         HEADING "Destination"                 FORMAT a25
COLUMN name_space          HEADING "Name|Space"                  FORMAT a8
COLUMN compression         HEADING "Compress"                    FORMAT a8
COLUMN archiver            HEADING "Arch|iver"                   FORMAT a4
COLUMN transmit_mode       HEADING "Transmit|Mode"                
COLUMN affirm              HEADING "AFFIRM"                      FORMAT a6

COLUMN reopen_secs         HEADING "Reopen|(sec)"                FORMAT 999999
COLUMN delay_mins          HEADING "Delay|(min)"                 FORMAT 99999
COLUMN max_connections     HEADING "Max|Conns"                   FORMAT 99999
COLUMN net_timeout         HEADING "Net|Time|Out|(sec)"          FORMAT 9999
COLUMN alternate           HEADING "Alertnate"                   FORMAT a10
COLUMN dependency          HEADING "Dependency"                  FORMAT a10

COLUMN register            HEADING "Regi|ster"                   FORMAT a4
COLUMN log_sequence        HEADING "LogSeq"                      FORMAT 9999999

COLUMN async_blocks        HEADING "ASYNC|Blocks"                FORMAT 999999
COLUMN valid_now           HEADING "Valid|Now"                   FORMAT a7
COLUMN verify              HEADING "Verify"                      FORMAT a6

COLUMN applied_scn         HEADING "Applied SCN"                 FORMAT 99999999999999
COLUMN applied_time        HEADING "Applied Time"                FORMAT a18

COLUMN fail_date           HEADING "FailDate"                    FORMAT a15
COLUMN fail_sequence       HEADING "FailSeq"                     FORMAT 9999999
COLUMN failure_count       HEADING "Fail|Count"                  FORMAT 99999
COLUMN max_failure         HEADING "Max|Fail"                    FORMAT 99999
COLUMN error               HEADING "Error"                       FORMAT a30

SELECT ad.dest_id
                             , ad.dest_name
&&_IF_ORA_10gR1_OR_HIGHER    , ad.db_unique_name
                             , ad.destination
                             , ad.status
                             , ad.schedule
                             , ad.target
&&_IF_ORA_10gR1_OR_HIGHER    , ad.valid_type
&&_IF_ORA_10gR1_OR_HIGHER    , ad.valid_role
                             , ad.binding
                             , ad.name_space
&&_IF_ORA_11gR1_OR_HIGHER    , ad.compression
                             , ad.archiver
                             , ad.transmit_mode
                             , ad.affirm
  FROM v$archive_dest ad
WHERE status <> 'INACTIVE'
;

SELECT ad.dest_id
                             , ad.delay_mins
&&_IF_ORA_10gR2_OR_HIGHER    , ad.max_connections
                             , ad.max_failure
                             , ad.reopen_secs
                             , ad.net_timeout
                             , ad.process
                             , ad.register
                             , ad.log_sequence
                             , ad.alternate
                             , ad.dependency
                             , ad.async_blocks
                             , ad.type
&&_IF_ORA_10gR1_OR_HIGHER    , ad.valid_now
&&_IF_ORA_10gR1_OR_HIGHER    , ad.verify
&&_IF_ORA_11202_OR_HIGHER    , ad.applied_scn
--&&_IF_ORA_11202_OR_HIGHER    , TO_CHAR(SCN_TO_TIMESTAMP(ad.applied_scn),'DD-MON-YY HH24:MI') applied_time
&&_IF_ORA_11202_OR_HIGHER    , TO_CHAR(ad.fail_date,'DD-MON-YY HH24:MI') fail_date
                             , ad.fail_sequence
                             , ad.failure_count
                             , ad.error
  FROM v$archive_dest ad
WHERE status <> 'INACTIVE'
;



@@footer