91 lines
3.0 KiB
SQL
91 lines
3.0 KiB
SQL
@@header
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display "Real" transport lag on DataGuard standby database
|
|
* Parameter : 1 - Number of hours (Default '48')
|
|
* : 2 - LagMinutes (Default 1 minute)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 14-Jan-15 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE HOURS
|
|
UNDEFINE LagMinutes
|
|
DEFINE HOURS="&&1"
|
|
DEFINE LagMinutes="&&2"
|
|
|
|
set term off
|
|
COLUMN _HOURS NEW_VALUE HOURS NOPRINT
|
|
COLUMN _LagMinutes NEW_VALUE LagMinutes NOPRINT
|
|
|
|
SELECT DECODE('&&HOURS','','48','&&HOURS') "_HOURS"
|
|
, DECODE('&&LagMinutes','','1','&&LagMinutes') "_LagMinutes"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
/************************************
|
|
* M A I N S C R I P T
|
|
************************************/
|
|
|
|
PROMPT *************************************************
|
|
PROMPT * DataGuard - Real Transport Lag
|
|
PROMPT *
|
|
PROMPT * Input Parameter:
|
|
PROMPT * Number Of Hours : &&HOURS
|
|
PROMPT * Lag Minutes : &&LagMinutes
|
|
PROMPT *************************************************
|
|
|
|
COLUMN thread# HEADING "T#" FORMAT 999
|
|
COLUMN sequence# HEADING "SEQ#" FORMAT 99999999
|
|
COLUMN applied HEADING "APPLIED" FORMAT a3
|
|
COLUMN FIRST_TIME HEADING "FIRST_TIME" FORMAT a20
|
|
COLUMN NEXT_TIME HEADING "NEXT_TIME" FORMAT a20
|
|
COLUMN COMPLETION_TIME HEADING "COMPLETION_TIME" FORMAT a20
|
|
COLUMN size_mb HEADING "SIZE_MB" FORMAT 99,999
|
|
COLUMN delay_in_shipping HEADING "Delay in Shipping" FORMAT a20
|
|
|
|
/*
|
|
* Not using WITH clause (aka CTE aka sub-query refactor'ing
|
|
* , as it does not work on standby database in MOUNT mode and only works in OPEN mode.
|
|
*/
|
|
|
|
select l.thread#
|
|
, l.sequence#
|
|
, l.applied
|
|
, l.deleted
|
|
, l.status
|
|
, l.archived
|
|
, TO_CHAR(l.first_time,'DD-MON-YY HH24:MI:SS') first_time
|
|
, TO_CHAR(l.next_time,'DD-MON-YY HH24:MI:SS') next_time
|
|
, ROUND((l.blocks * l.block_size)/power(1024,2)) size_mb
|
|
, l.fal
|
|
, l.creator
|
|
, l.registrar
|
|
, TO_CHAR(l.completion_time,'DD-MON-YY HH24:MI:SS') completion_time
|
|
, LPAD(FLOOR(l.completion_time-l.next_time) ,2) || 'd '
|
|
|| LPAD(FLOOR(MOD((l.completion_time-l.next_time) ,1) * 24 ),2) || 'h '
|
|
|| LPAD(FLOOR(MOD((l.completion_time-l.next_time)*24 ,1) * 60 ),2) || 'm '
|
|
|| LPAD(FLOOR(MOD((l.completion_time-l.next_time)*24*60,1) * 60 ),2) || 's' delay_in_shipping
|
|
from v$archived_log l
|
|
WHERE 1=1
|
|
AND l.dest_id = 1
|
|
AND l.first_time > sysdate - (&&HOURS/24)
|
|
AND (l.completion_time-l.next_time) * 24 * 60 > &&LagMinutes
|
|
order by l.first_time
|
|
;
|
|
|
|
|
|
|
|
|
|
@@footer |