109 lines
3.5 KiB
SQL
109 lines
3.5 KiB
SQL
@@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
|