100 lines
4.3 KiB
SQL
100 lines
4.3 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display RMAN Backup job Details
|
|
* Parameters : 1 - Input Type (e.g DB FULL, ARCHIVELOG, DB INCR etc. Use % as wildcard character)
|
|
* 2 - Status (e.g COMPLETED, RUNNING, FAILED. Use % as wildcard character)
|
|
* 3 - Device Type (e.g DISK , SBT_TAPE . Use % as wildcard character)
|
|
* 4 - StartTime (In YYYY-MM-DD HH24:MI:SS format. Use % as wildcard character)
|
|
* 5 - EndTime (In YYYY-MM-DD HH24:MI:SS format. Use % as wildcard character)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 08-OCT-14 Vishal Gupta Added input parmaeters to allow output filtering
|
|
* 17-Oct-13 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE INPUT_TYPE
|
|
UNDEFINE STATUS
|
|
UNDEFINE OUTPUT_DEVICE_TYPE
|
|
UNDEFINE START_TIME
|
|
UNDEFINE END_TIME
|
|
|
|
DEFINE INPUT_TYPE="&&1"
|
|
DEFINE STATUS="&&2"
|
|
DEFINE OUTPUT_DEVICE_TYPE="&&3"
|
|
DEFINE START_TIME="&&4"
|
|
DEFINE END_TIME="&&5"
|
|
|
|
set term off
|
|
COLUMN _INPUT_TYPE NEW_VALUE INPUT_TYPE NOPRINT
|
|
COLUMN _STATUS NEW_VALUE STATUS NOPRINT
|
|
COLUMN _OUTPUT_DEVICE_TYPE NEW_VALUE OUTPUT_DEVICE_TYPE NOPRINT
|
|
COLUMN _START_TIME NEW_VALUE START_TIME NOPRINT
|
|
COLUMN _END_TIME NEW_VALUE END_TIME NOPRINT
|
|
|
|
SELECT UPPER(DECODE('&&INPUT_TYPE' ,'','%','&&INPUT_TYPE')) "_INPUT_TYPE"
|
|
, UPPER(DECODE('&&STATUS' ,'','%','&&STATUS')) "_STATUS"
|
|
, UPPER(DECODE('&&OUTPUT_DEVICE_TYPE','','%','&&OUTPUT_DEVICE_TYPE')) "_OUTPUT_DEVICE_TYPE"
|
|
, UPPER(DECODE('&&START_TIME','',TO_CHAR(sysdate - 30, 'YYYY-MM-DD HH24:MI:SS'),'&&START_TIME')) "_START_TIME"
|
|
, UPPER(DECODE('&&END_TIME','',TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS'),'&&END_TIME')) "_END_TIME"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT ***********************************************************************
|
|
PROMPT * RMAN Backup Job Details
|
|
PROMPT *
|
|
PROMPT * Input Parmaeters
|
|
PROMPT * Input Type = '&&INPUT_TYPE'
|
|
PROMPT * Status = '&&STATUS'
|
|
PROMPT * DeviceType = '&&OUTPUT_DEVICE_TYPE'
|
|
PROMPT * StartTime = '&&START_TIME' (YYYY-MM-DD HH24:MI:SS format)
|
|
PROMPT * EndTime = '&&END_TIME' (YYYY-MM-DD HH24:MI:SS format)
|
|
PROMPT ***********************************************************************
|
|
|
|
COLUMN input_type HEADING "Input|Type" FORMAT a18
|
|
COLUMN status HEADING "Status" FORMAT a23
|
|
COLUMN output_device_type HEADING "Output|Device|Type" FORMAT a10
|
|
COLUMN start_time HEADING "StartTime" FORMAT a20
|
|
COLUMN end_time HEADING "EndTime" FORMAT a20
|
|
COLUMN time_taken_display HEADING "Duration" FORMAT a10
|
|
COLUMN input_bytes_display HEADING "Input|Bytes" FORMAT a10
|
|
COLUMN output_bytes_display HEADING "Output|Bytes" FORMAT a10
|
|
COLUMN compression_ratio HEADING "Compression|Ratio" FORMAT 999.99
|
|
COLUMN input_bytes_per_sec_display HEADING "InputRate|PerSec" FORMAT a10 JUSTIFY RIGHT
|
|
COLUMN output_bytes_per_sec_display HEADING "OutputRate|PerSec" FORMAT a10 JUSTIFY RIGHT
|
|
|
|
|
|
SELECT r.input_type
|
|
, r.status
|
|
, r.output_device_type
|
|
, TO_CHAR(r.start_time,'DD-MON-YY HH24:MI:SS') start_time
|
|
, DECODE(r.status,'RUNNING','',TO_CHAR(r.end_time,'DD-MON-YY HH24:MI:SS')) end_time
|
|
, r.time_taken_display
|
|
, r.input_bytes_display
|
|
, r.output_bytes_display
|
|
, r.compression_ratio
|
|
, r.input_bytes_per_sec_display
|
|
, r.output_bytes_per_sec_display
|
|
FROM v$rman_backup_job_details r
|
|
WHERE 1=1
|
|
AND NVL(r.input_type,'x') LIKE '&&INPUT_TYPE'
|
|
AND NVL(r.status,'x') LIKE '&&STATUS'
|
|
AND NVL(r.output_device_type,'x') LIKE '&&OUTPUT_DEVICE_TYPE'
|
|
AND (r.start_time is NULL OR r.start_time >= TO_DATE('&&START_TIME','YYYY-MM-DD HH24:MI:SS') )
|
|
AND (r.end_time is NULL OR r.end_time <= TO_DATE('&&END_TIME' ,'YYYY-MM-DD HH24:MI:SS') )
|
|
ORDER BY r.start_time
|
|
;
|
|
|
|
|
|
|
|
@@footer |