78 lines
3.4 KiB
SQL
78 lines
3.4 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 * R E C O V E R Y P R O G R E S S *
|
|
PROMPT *********************************************
|
|
|
|
COLUMN inst_id HEADING "I#" FORMAT 99
|
|
COLUMN start_time HEADING "Start Time" FORMAT a18
|
|
COLUMN type HEADING "Type" FORMAT a20
|
|
COLUMN item HEADING "Item" FORMAT a25
|
|
COLUMN units HEADING "Units" FORMAT a10
|
|
COLUMN sofar HEADING "SoFar" FORMAT a45
|
|
COLUMN total HEADING "Total" FORMAT 999,999,999,999
|
|
COLUMN timestamp HEADING "Start Time" FORMAT a18
|
|
COLUMN comments HEADING "Comments" FORMAT a18
|
|
|
|
SELECT inst_id
|
|
--, TO_CHAR(r.start_time,'DD-MON-YY HH24:MI:SS') start_time
|
|
--, r.type
|
|
, item
|
|
, CASE item
|
|
WHEN 'Last Applied Redo' THEN TO_CHAR(timestamp,'DD-MON-YY HH24:MI:SS') || ' , ' || comments
|
|
|| chr(10) || 'ApplyLag - ' || REPLACE(REPLACE(TO_CHAR( CAST(sysdate as TIMESTAMP) - timestamp)
|
|
,'+0000000','+')
|
|
,'.000000','')
|
|
WHEN 'Active Time' THEN FLOOR(sofar/3600) || 'h ' || FLOOR(MOD(sofar,3600)/60) || 'm ' || MOD(sofar,60) || 's'
|
|
WHEN 'Elapsed Time' THEN FLOOR(sofar/3600) || 'h ' || FLOOR(MOD(sofar,3600)/60) || 'm ' || MOD(sofar,60) || 's'
|
|
|| chr(10) || 'StartTime: ' || TO_CHAR(start_time,'DD-MON-YY HH24:MI:SS')
|
|
ELSE TRIM(TO_CHAR(sofar,'999,999,999,999')) || ' ' || units
|
|
END SoFar
|
|
--, r.total
|
|
FROM ( SELECT r.inst_id
|
|
--, NVL(r.item,d.item) item
|
|
, r.item
|
|
, NVL(r.timestamp,d.timestamp) timestamp
|
|
--, r.timestamp timestamp
|
|
, r.start_time
|
|
, r.comments
|
|
, r.sofar
|
|
, r.units
|
|
FROM (SELECT 'Last Applied Redo' item
|
|
, controlfile_time timestamp
|
|
FROM v$database ) d
|
|
LEFT OUTER JOIN gv$recovery_progress r ON 1=1
|
|
LEFT OUTER JOIN (SELECT MAX(start_time) start_time from gv$recovery_progress) r2 ON r.start_time = r2.start_time
|
|
)
|
|
ORDER BY DECODE(item
|
|
,'Last Applied Redo',1
|
|
,'Standby Apply Lag',2
|
|
,'Active Apply Rate',3
|
|
,'Average Apply Rate',4
|
|
,'Maximum Apply Rate',5
|
|
,'Redo Applied',6
|
|
,'Log Files',7
|
|
,'Apply Time per Log',8
|
|
,'Checkpoint Time per Log',9
|
|
,'Active Time',21
|
|
,'Elapsed Time',22
|
|
,999
|
|
)
|
|
;
|
|
|
|
@@footer
|