55 lines
1.3 KiB
MySQL
55 lines
1.3 KiB
MySQL
@@header
|
|
|
|
set term off
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display missing archivelog backups for a database.
|
|
* Arguments : 1 - Database name
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 01-Aug-08 Vishal Gupta First Draft
|
|
*/
|
|
set term on
|
|
|
|
DEFINE database="&&1"
|
|
|
|
|
|
WITH max_min_arch as
|
|
( SELECT db_name
|
|
, thread#
|
|
, MAX(sequence#) max_sequence#
|
|
, MIN(sequence#) min_sequence#
|
|
FROM RC_BACKUP_ARCHIVELOG_DETAILS
|
|
WHERE db_name = '&database'
|
|
GROUP BY db_name
|
|
, thread#
|
|
)
|
|
, archivelogs as
|
|
(SELECT db_name
|
|
,thread#
|
|
,min_sequence# + ROWNUM - 1 sequence#
|
|
FROM dual
|
|
, max_min_arch
|
|
CONNECT BY LEVEL <= max_sequence# - min_sequence# + 1
|
|
)
|
|
select db_name
|
|
,thread#
|
|
,sequence#
|
|
from archivelogs a
|
|
WHERE NOT EXISTS ( SELECT 1
|
|
FROM RC_BACKUP_ARCHIVELOG_DETAILS r
|
|
WHERE r.db_name = a.db_name
|
|
AND r.thread# = a.thread#
|
|
AND r.sequence# = a.sequence#
|
|
)
|
|
ORDER BY db_name
|
|
,thread#
|
|
,sequence#
|
|
;
|
|
|
|
@@footer
|