[Oracle] RMAN query to get backup status
Hi! we’ll see how to query rman catalog both in control file and in rman catalog database to get backup status. We’ll also see query to get status of backups in summary format, very useful to implement an RMAN backup report.
ENVIRONMENT
- Oracle Linux 7 [OS]
- Oracle RDBMS 12.2.0.1 [DATABASE]
- Oracle RDBMS 12.2.0.1 [RMAN CATALOG]
1 – Control file query
- Connect to your Oracle database server (sqlplus sys/password@DB_SERVICE)
- Run below query:
- View RMAN backup status in the last 7 days for all input types with start, stop and elapsed time:
--QUERY
col STATUS format a9
col ELA format 999.99
col START_TIME for a15
col end_time for a15
select SESSION_KEY,
INPUT_TYPE,
STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') START_TIME,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 ELA
from V$RMAN_BACKUP_JOB_DETAILS
where START_TIME > sysdate-7
order by session_key;
--OUTPUT
SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME ELA
----------- ------------- --------- --------------- --------------- -------
33828 CONTROLFILE COMPLETED 04/19/20 18:00 04/19/20 18:00 .00
33839 DB INCR COMPLETED 04/20/20 17:00 04/20/20 17:12 .20
33841 DB INCR COMPLETED 04/20/20 17:12 04/20/20 17:13 .02
33850 ARCHIVELOG COMPLETED 04/20/20 17:14 04/20/20 18:23 1.15
33858 CONTROLFILE COMPLETED 04/20/20 18:24 04/20/20 18:25 .00
33869 DB INCR COMPLETED 04/21/20 17:00 04/21/20 17:13 .21
33871 DB INCR COMPLETED 04/21/20 17:13 04/21/20 17:14 .02
33880 ARCHIVELOG COMPLETED 04/21/20 17:14 04/21/20 20:02 2.80
33888 CONTROLFILE COMPLETED 04/21/20 20:31 04/21/20 20:33 .05
33899 DB INCR COMPLETED 04/22/20 17:00 04/22/20 17:18 .31
33901 DB INCR COMPLETED 04/22/20 17:19 04/22/20 17:20 .02
33910 ARCHIVELOG COMPLETED 04/22/20 17:21 04/22/20 19:39 2.30
33918 CONTROLFILE COMPLETED 04/22/20 19:40 04/22/20 19:41 .00
33929 DB INCR COMPLETED 04/23/20 17:00 04/23/20 17:16 .26
33931 DB INCR COMPLETED 04/23/20 17:16 04/23/20 17:17 .02
33940 ARCHIVELOG COMPLETED 04/23/20 17:18 04/23/20 19:37 2.32
33948 CONTROLFILE COMPLETED 04/23/20 19:38 04/23/20 19:39 .00
33971 DB INCR COMPLETED 04/24/20 17:00 04/24/20 17:11 .18
33973 DB INCR COMPLETED 04/24/20 17:11 04/24/20 17:12 .02
33982 ARCHIVELOG FAILED 04/24/20 17:13 04/24/20 18:06 .90
33984 DB INCR COMPLETED 04/25/20 17:00 04/25/20 17:07 .11
33986 DB INCR COMPLETED 04/25/20 17:07 04/25/20 17:07 .01
33995 ARCHIVELOG COMPLETED 04/25/20 17:08 04/25/20 18:52 1.74
34003 CONTROLFILE COMPLETED 04/25/20 18:53 04/25/20 18:53 .00
34014 DB INCR COMPLETED 04/26/20 17:00 04/26/20 17:03 .06
34016 DB INCR COMPLETED 04/26/20 17:03 04/26/20 17:04 .01
34025 ARCHIVELOG RUNNING 04/26/20 17:05 04/26/20 17:35 .51- View estimated remaining percentage of completion for current running RMAN backup
--QUERY
SET lines 100
COL SESS for a20
col OPNAME for a30
SELECT ''''||SID||','||SERIAL#||',@'||INST_ID||'''' SESS,
decode(OPNAME,
'RMAN: aggregate input', 'TOTAL PROGRESS',
'CURRENT: '||substr(OPNAME, 7, length(OPNAME))) OPNAME,
CONTEXT,
SOFAR,
TOTALWORK,
ROUND(SOFAR/TOTALWORK*100, 2) "%_COMPLETE"
FROM GV$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK;
--OUTPUT
SESS OPNAME CONTEXT SOFAR TOTALWORK %_COMPLETE
-------------------- ------------------------------ ---------- ---------- ---------- ----------
'532,33285,@1' TOTAL PROGRESS 3 302359015 1244764536 24.29
'281,18465,@1' CURRENT: archived log backup 1 1279558 2303536 55.55
'678,33453,@1' CURRENT: archived log backup 1 1179659 3161233 37.322 – DB catalog query
- Connect to your RMAN catalog database (sqlplus RMAN_SCHEMA/password@RMAN_SERVICE)
- Run below query:
- View RMAN backup status in the last 7 days for all input types with completion time, TAG and KEEP options
--OUTPUT
set lines 150
col BACKUP_TYPE for a12
select BS.BS_KEY,
BS.RECID,
BS.STAMP,
DECODE(BS.BACKUP_TYPE,
'D', 'FULL/L0',
'I', 'L1',
'L', 'ARCHIVELOG',
'N/A') BACKUP_TYPE,
BS.CONTROLFILE_INCLUDED,
to_char(BS.COMPLETION_TIME, 'YYYY-MM-DD HH24:MI') COMPLETION_TIME,
BP.TAG,
BS.KEEP,
BS.KEEP_UNTIL,
BS.KEEP_OPTIONS
from (rc_backup_set BS inner join rc_backup_piece BP on BS.SET_STAMP=BP.SET_STAMP and BS.SET_COUNT=BP.SET_COUNT)
where BS.COMPLETION_TIME > sysdate-7
--and BP.TAG like '%TAG%'
order by BS.BS_KEY;
--OUTPUT
BS_KEY RECID STAMP BACKUP_TYPE CONTROL COMPLETION_TIME TAG KEE KEEP_UNTI KEEP_OPTION
---------- ---------- ---------- ------------ ------- ---------------- -------------------------------- --- --------- -----------
2008480 100877 1038694668 FULL/L0 BACKUP 2020-04-25 22:17 LASTCONTROLFILE NO
2008480 100877 1038694668 FULL/L0 BACKUP 2020-04-25 22:17 LASTCONTROLFILE NO
2008496 100878 1038694671 FULL/L0 NONE 2020-04-25 22:17 LASTSPFILE NO
2008496 100878 1038694671 FULL/L0 NONE 2020-04-25 22:17 LASTSPFILE NO
2008682 100881 1038700816 ARCHIVELOG NONE 2020-04-26 00:00 TAG20200426T000015 NO
2008683 100882 1038700816 ARCHIVELOG NONE 2020-04-26 00:00 TAG20200426T000015 NO
2008720 100883 1038715215 ARCHIVELOG NONE 2020-04-26 04:00 TAG20200426T040014 NO
2008721 100884 1038715216 ARCHIVELOG NONE 2020-04-26 04:00 TAG20200426T040014 NO
2008791 100885 1038729682 ARCHIVELOG NONE 2020-04-26 08:01 TAG20200426T080027 NO
2008792 100886 1038729727 ARCHIVELOG NONE 2020-04-26 08:02 TAG20200426T080027 NO
2008871 100887 1038744085 ARCHIVELOG NONE 2020-04-26 12:01 TAG20200426T120026 NO
2008872 100888 1038744122 ARCHIVELOG NONE 2020-04-26 12:02 TAG20200426T120026 NO
2008951 100889 1038758475 ARCHIVELOG NONE 2020-04-26 16:01 TAG20200426T160017 NO
2008952 100890 1038758491 ARCHIVELOG NONE 2020-04-26 16:01 TAG20200426T160017 NO
2008953 100891 1038758498 ARCHIVELOG NONE 2020-04-26 16:01 TAG20200426T160017 NO- View RMAN backup summary status for all types (COPY/BACKUPSET – FULL/L0/L1/ARCH) in hours
--QUERY
set lines 200
WITH
BACKUP_BACKUPSET AS /*BACKUP AS BACKUPSET RECORDS*/
(select d.DBID, d.name, input_type, incremental_level, max(END_TIME) last, ceil((sysdate - max(END_TIME))*24) hours
from
RC_DATABASE d,
RC_RMAN_BACKUP_JOB_DETAILS j,
RC_BACKUP_SET_DETAILS s
where d.dbid = (select dbid from RC_DATABASE)
and j.DB_KEY = d.db_key
and j.end_time > sysdate-30
and j.status like 'COMPLETED%'
and s.db_key (+)= j.db_key
and s.session_key (+)= j.session_key
group by
d.DBID,
d.name,
input_type,
incremental_level),
BACKUP_COPY AS /*BACKUP AS COPY RECORDS*/
(select d.DBID, d.name, 'DB FULL' input_type, null incremental_level, max(END_TIME) last, ceil((sysdate - max(END_TIME))*24) hours
from
RC_DATABASE d,
RC_BACKUP_COPY_DETAILS j,
RC_RMAN_STATUS s
where d.dbid = (select dbid from RC_DATABASE)
and j.DB_KEY = d.db_key
and j.RSR_KEY = s.RSR_KEY
and j.COMPLETION_TIME > sysdate-30
and s.status like 'COMPLETED%'
group by
d.DBID,
d.name)
select /*+rule*/
dbid,
name,
max(decode(input_type,'DB FULL',hours,-1)) last_full,
max(decode(input_type,'DB INCR',decode(incremental_level, 0,hours,null),-1)) last_lev0,
max(decode(input_type,'DB INCR',decode(incremental_level, 1,hours,null),-1)) last_lev1,
max(decode(input_type,'DB INCR',decode(incremental_level,null,hours,null),-1)) last_bck,
max(decode(input_type,'ARCHIVELOG',hours,-1)) last_arch
from (select * from BACKUP_BACKUPSET
union all
select * from BACKUP_COPY)
group by dbid, name;
--OUTPUT
DBID NAME LAST_FULL LAST_LEV0 LAST_LEV1 LAST_BCK LAST_ARCH
---------- ---------- ---------- ---------- ---------- ---------- ----------
1446405546 DB_NAME -1 159 23 23 1The last query is very usefull for a report summary. You can schedule a script that, for all schemas in recovery catalog, executes the query to extract a summary report of all backups. The output columns have the following meaning:
- DBID: Oracle unique database ID
- NAME: Oracle database name
- LAST_FULL: time in hours since the last full backup as backupset or copy (if -1 there aren’t backup in last 30 days)
- LAST_LEV0: time in hours since the last L0 backup (if -1 there aren’t backup in last 30 days)
- LAST_LEV1: time in hours since the last L1 incremental/differential backup (if -1 there aren’t backup in last 30 days)
- LAST_BCK: time in hours since the last database backup, the minimum between FULL/L0/L1 (if -1 there aren’t backup in last 30days)
- LAST_ARCH: time in hours since the last archivelog backup (if -1 there aren’t backup in last 30days)
With this output you can check if your backup policy are respected for all databases.