109 lines
3.3 KiB
MySQL
109 lines
3.3 KiB
MySQL
@@header
|
|
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display archivelog gap
|
|
* Parameter : 1 - Thread Number (Default '%')
|
|
* 2 - From Sequence (Default '%')
|
|
* 3 - To Sequence (Default '%')
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 04-Apr-10 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
DEFINE thread="&&1"
|
|
DEFINE from_sequence="&2"
|
|
DEFINE to_sequence="&3"
|
|
|
|
set term off
|
|
COLUMN _thread NEW_VALUE thread NOPRINT
|
|
COLUMN _from_sequence NEW_VALUE from_sequence NOPRINT
|
|
COLUMN _to_sequence NEW_VALUE to_sequence NOPRINT
|
|
|
|
SELECT DECODE('&&thread','','%','&&thread') "_thread"
|
|
, DECODE('&&from_sequence','','%','&&from_sequence') "_from_sequence"
|
|
, DECODE('&&to_sequence','','%','&&to_sequence') "_to_sequence"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
/************************************
|
|
* M A I N S C R I P T
|
|
************************************/
|
|
|
|
COLUMN sequence# HEADING "SEQUENCE#" FORMAT 99999999
|
|
|
|
/*
|
|
* Not using WITH clause (aka CTE aka sub-query refactorying
|
|
* , as it does not work on standby database in MOUNT mode and not in OPEN mode.
|
|
*/
|
|
|
|
select minmax.thread#
|
|
, i.sequence# + minmax.min_sequence# - 1 sequence#
|
|
--, l.thread#
|
|
--, l.sequence#
|
|
, l.applied
|
|
, l.deleted
|
|
, l.status
|
|
, l.archived
|
|
, NVL(TO_CHAR(l.first_time,'DD-MON-YY HH24:MI:SS'),' Missing ') first_time
|
|
, TO_CHAR(l.next_time,'DD-MON-YY HH24:MI:SS') next_time
|
|
, l.fal
|
|
, l.creator
|
|
, l.registrar
|
|
, TO_CHAR(l.completion_time,'DD-MON-YY HH24:MI:SS') completion_time
|
|
from (select rownum sequence#
|
|
from dual
|
|
connect by level <= 1500
|
|
) i
|
|
join (select dest_id
|
|
, thread#
|
|
, max(DECODE(applied, 'YES',sequence#,0)) - 3 min_sequence#
|
|
, max(sequence#) max_sequence#
|
|
--, min(first_time)
|
|
--, max(first_time)
|
|
From v$archived_log
|
|
where dest_id = 1
|
|
AND thread# like '&&thread'
|
|
and first_time > trunc(sysdate - 1)
|
|
group by dest_id, thread#
|
|
order by thread#
|
|
) minmax on 1 = 1
|
|
LEFT OUTER JOIN v$archived_log l on l.thread# = minmax.thread# and l.sequence# = i.sequence# + minmax.min_sequence# - 1
|
|
WHERE 1=1
|
|
--AND (l.thread# IS NULL OR l.sequence# IS NULL)
|
|
and i.sequence# + minmax.min_sequence# - 1 <= minmax.max_sequence#
|
|
AND minmax.thread# like '&&thread'
|
|
AND i.sequence# + minmax.min_sequence# - 1
|
|
BETWEEN DECODE('&&from_sequence','%',minmax.min_sequence#,'&&from_sequence')
|
|
AND DECODE('&&to_sequence','%',minmax.max_sequence#,'&&to_sequence')
|
|
order by minmax.thread#
|
|
, i.sequence#
|
|
--order by l.first_change#
|
|
;
|
|
|
|
|
|
SELECT l.thread#
|
|
, TO_CHAR(sysdate,'DD-MON-YY HH24:MI:SS') current_time
|
|
, TO_CHAR(max(l.next_time) ,'DD-MON-YY HH24:MI:SS') max_next_time
|
|
FROM v$archived_log l
|
|
GROUP BY l.thread#
|
|
ORDER BY l.thread#
|
|
;
|
|
|
|
|
|
@@footer |