20 Jun RMAN: Report Details of Backups
So the other I was asked for a report regarding the backups in RMAN, so this query below helped me to give the needed details of the RMAN backups that we have.
So I connected to the RMAN catalog and executed the query below, goes to say that I was only looking for Full ,Incremental backups or Backupsets, one other thing you will see below, if you have control file autobackup on in your RMAN settings, you will find that your backups, even though they are backupsets to SBT, you will see them as an INPUT_TYPE of CONTROLFILE for any OUTPUT_DEVICE_TYPE of SBT_TAPE in the RC_RMAN_BACKUP_JOB_DETAILS, that is why you see me doing a CASE and filtering as well by CONTROLFILE
SELECT 'Development',
DB_NAME,
CASE
WHEN OUTPUT_DEVICE_TYPE = 'SBT_TAPE'
THEN 'DB FULL'
ELSE INPUT_TYPE
END INPUT_TYPE,
TO_CHAR(END_TIME,'DAY') END_TIME_DAY,
TO_CHAR(END_TIME,'mm/dd/yy') END_TIME,
TIME_TAKEN_DISPLAY,
ROUND(SUM(OUTPUT_BYTES/1024/1024/1024),2) SUM_BACKUP_PIECES_IN_GB,
OUTPUT_DEVICE_TYPE
FROM RC_RMAN_BACKUP_JOB_DETAILS
WHERE STATUS ='COMPLETED'
AND INPUT_TYPE in ('BACKUPSET','DB FULL','DB INCR','CONTROLFILE')
AND OUTPUT_DEVICE_TYPE IS NOT NULL
AND END_TIME >SYSDATE-90
GROUP BY DB_NAME,
INPUT_TYPE,
STATUS,
TO_CHAR(END_TIME,'DAY') ,
TO_CHAR(END_TIME,'mm/dd/yy') ,
TIME_TAKEN_DISPLAY,
OUTPUT_DEVICE_TYPE
ORDER BY DB_NAME,
END_TIME_DAY,
END_TIME;
'DEVELOPMENT' DB_NAME INPUT_TYPE END_TIME_DAY END_TIME TIME_TAKEN SUM_BACKUP_PIECES_IN_GB OUTPUT_DEVICE_TYPE
------------- -------- ------------- ------------------------------------ -------- ---------- ----------------------- ------------------
Development DBATEST DB FULL 04/04/12 WEDNESDAY 00:11:05 0.28 SBT_TAPE
Development DBATEST DB FULL 04/04/12 WEDNESDAY 00:36:32 10.11 SBT_TAPE
Development DBATEST DB FULL 04/05/12 THURSDAY 00:14:18 0.2 SBT_TAPE
Development DBATEST DB FULL 04/06/12 FRIDAY 00:17:32 0.33 SBT_TAPE
Development DBATEST DB FULL 04/07/12 SATURDAY 00:24:16 0.21 SBT_TAPE
Development DBATEST DB FULL 04/08/12 SUNDAY 00:07:11 3.37 DISK
Development DBATEST DB FULL 04/09/12 MONDAY 00:03:22 0.04 SBT_TAPE
Development DBATEST DB FULL 04/09/12 MONDAY 00:13:42 0.07 SBT_TAPE
Development DBATEST DB FULL 04/09/12 MONDAY 00:26:26 3.57 SBT_TAPE
Development DBATEST DB FULL 04/09/12 MONDAY 00:48:29 0.49 SBT_TAPE
Development DBATEST DB FULL 04/10/12 TUESDAY 00:14:05 0.4 SBT_TAPE
Development DBATEST DB FULL 04/11/12 WEDNESDAY 00:46:14 0.2 SBT_TAPE
Once I got the results of this query , I put this into a Pivot table in excel (very helpful link for those beginning in Pivot Tables) and voila , instant dynamic reporting for the last 90 days, really helpful to get information on which days is a DB backup running and how much space is it occupying.
Sorry, the comment form is closed at this time.