103 lines
4.5 KiB
SQL
103 lines
4.5 KiB
SQL
@@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
|