129 lines
6.3 KiB
SQL
129 lines
6.3 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display breakdown of archivelogs fetched using dataguard FAL calls
|
|
* Parameters : 1 - Number of days (Default 7)
|
|
* 2 - Destination Id (Default %)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------------------
|
|
* 27-Jan-14 Vishal Gupta Added following to output and changed output layout
|
|
* - Count of archivelogs
|
|
* - Primary total count and sizes
|
|
* 22-Nov-13 Vishal Gupta Added destination wise totals
|
|
* 08-Nov-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE DAYS
|
|
UNDEFINE DEST_ID
|
|
|
|
DEFINE DAYS="&&1"
|
|
DEFINE DEST_ID="&&2"
|
|
|
|
COLUMN _DAYS NEW_VALUE DAYS NOPRINT
|
|
COLUMN _DEST_ID NEW_VALUE DEST_ID NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&DAYS','','7','&&DAYS') "_DAYS"
|
|
, DECODE('&&DEST_ID','','%','&&DEST_ID') "_DEST_ID"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
PROMPT *****************************************************************
|
|
PROMPT * DataGuard FAL Breakdown (RUN ON PRIMARY INSTANCE)
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Days = '&&DAYS'
|
|
PROMPT * - Destination Id = '&&DEST_ID'
|
|
PROMPT *****************************************************************
|
|
|
|
COLUMN dest_id HEADING "DEST_ID" FORMAT 9999999999
|
|
COLUMN name HEADING "DataGuard|DestName" FORMAT a25
|
|
COLUMN destination HEADING "DataGuard|DestName" FORMAT a25
|
|
COLUMN next_time HEADING "ArchLog|Creation|Date" FORMAT a9
|
|
COLUMN total_prim_size_GB HEADING "Total|Primary|(GB)" FORMAT 999,999
|
|
COLUMN total_dest_size_GB HEADING "Total|Dest|(GB)" FORMAT 999,999
|
|
COLUMN NOFAL_size_GB HEADING "NOFAL|(Pushed)|(GB)" FORMAT 999,999
|
|
COLUMN FAL_size_GB HEADING "FAL|(Pulled)|(GB)" FORMAT 999,999
|
|
COLUMN NOFAL_percent HEADING "NOFAL|(Pushed)|(%)" FORMAT 999.99
|
|
COLUMN FAL_percent HEADING "FAL|(Pulled)|(%)" FORMAT 999.99
|
|
COLUMN total_prim_count HEADING "Total|Primary|(#)" FORMAT 99,999
|
|
COLUMN total_dest_count HEADING "Total|Dest|(#)" FORMAT 99,999
|
|
COLUMN NOFAL_count HEADING "FAL|(Pushed)|(#)" FORMAT 99,999
|
|
COLUMN FAL_count HEADING "FAL|(Pulled)|(#)" FORMAT 99,999
|
|
COLUMN separator HEADING "!|!|!" FORMAT a1
|
|
|
|
BREAK ON DEST_ID SKIP 1 DUPLICATES ON REPORT
|
|
--BREAK ON REPORT
|
|
|
|
COMPUTE SUM LABEL 'Total' OF total_prim_size_GB FORMAT 99,999,999 ON DEST_ID
|
|
COMPUTE SUM LABEL 'Total' OF total_dest_size_GB FORMAT 99,999,999 ON DEST_ID
|
|
COMPUTE SUM LABEL 'Total' OF NOFAL_size_GB FORMAT 99,999,999 ON DEST_ID
|
|
COMPUTE SUM LABEL 'Total' OF FAL_size_GB FORMAT 99,999,999 ON DEST_ID
|
|
COMPUTE SUM LABEL 'Total' OF total_prim_count FORMAT 99,999,999 ON DEST_ID
|
|
COMPUTE SUM LABEL 'Total' OF total_dest_count FORMAT 99,999,999 ON DEST_ID
|
|
COMPUTE SUM LABEL 'Total' OF NOFAL_count FORMAT 99,999,999 ON DEST_ID
|
|
COMPUTE SUM LABEL 'Total' OF FAL_count FORMAT 99,999,999 ON DEST_ID
|
|
|
|
COMPUTE SUM LABEL 'Grand Total' OF total_prim_size_GB FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Grand Total' OF total_dest_size_GB FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Grand Total' OF NOFAL_size_GB FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Grand Total' OF FAL_size_GB FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Grand Total' OF total_prim_count FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Grand Total' OF total_dest_count FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Grand Total' OF NOFAL_count FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Grand Total' OF FAL_count FORMAT 99,999,999 ON REPORT
|
|
|
|
SELECT l.dest_id
|
|
--, lower(l.name) name
|
|
, d.destination
|
|
, TO_CHAR(TRUNC(l.next_time),'DD-MON-YY') next_time
|
|
, '|' separator
|
|
, p.total_count total_prim_count
|
|
, count(1) total_dest_count
|
|
, SUM(DECODE(l.fal,'NO',1,0)) NOFAL_count
|
|
, SUM(DECODE(l.fal,'YES',1,0)) FAL_count
|
|
, '|' separator
|
|
, ROUND(p.total_size/power(1024,3),2) total_prim_size_GB
|
|
, ROUND(SUM(l.BLOCKS * l.block_size) /power(1024,3),2) total_dest_size_GB
|
|
, ROUND(SUM(DECODE(l.fal,'NO',(l.BLOCKS * l.block_size ),0)) /power(1024,3),2) NOFAL_size_GB
|
|
, ROUND(SUM(DECODE(l.fal,'YES',(l.BLOCKS * l.block_size ),0)) /power(1024,3),2) FAL_size_GB
|
|
, '|' separator
|
|
, ROUND(SUM(DECODE(l.fal,'NO',(l.BLOCKS),0))/SUM(l.BLOCKS)*100,2) NOFAL_percent
|
|
, ROUND(SUM(DECODE(l.fal,'YES',(l.BLOCKS),0))/SUM(l.BLOCKS)*100,2) FAL_percent
|
|
FROM v$archived_log l
|
|
JOIN v$archive_dest d ON d.dest_id = l.dest_id
|
|
JOIN (SELECT TO_CHAR(TRUNC(l2.next_time),'DD-MON-YY') next_time
|
|
, SUM(l2.BLOCKS * l2.block_size) total_size
|
|
, count(1) total_count
|
|
FROM v$archived_log l2
|
|
WHERE l2.dest_id = (select dest_id from v$archived_log l3 where l3.standby_dest <> 'YES' and rownum =1)
|
|
AND l2.next_time > sysdate - &&days
|
|
GROUP BY TO_CHAR(TRUNC(l2.next_time),'DD-MON-YY')
|
|
) p ON p.next_time = TO_CHAR(TRUNC(l.next_time),'DD-MON-YY')
|
|
WHERE 1=1
|
|
AND l.standby_dest = 'YES'
|
|
AND l.next_time > sysdate - &&days
|
|
AND l.dest_id like '&&DEST_ID'
|
|
GROUP BY l.dest_id
|
|
--, lower(l.name)
|
|
, d.destination
|
|
, TRUNC(l.next_time)
|
|
, p.total_size
|
|
, p.total_count
|
|
ORDER BY l.dest_id
|
|
, TRUNC(l.next_time) ASC
|
|
;
|
|
|
|
@@footer
|
|
|
|
|