79 lines
2.8 KiB
SQL
79 lines
2.8 KiB
SQL
@@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
|