News

  1. Home
  2. All posts
All posts, Backup, SQL by Team Ajara

Query to display FULL and INCR backup details of DB

Query to display/report FULL and INCR backup details of database.

In this article, we will demonstrate about the queries that provide FULL and INCR backup details of a oracle database.
Directly we dont have any single oracle view to display the FULL and INCR backup details, hence we need to join below views to get the output as show below .
There are two places to get the backup status .
1) From controlfile , default Oracle views.
2) From catalog views .

  • v$database
  • v$instance
  • v$backup_set_details
  • v$rman_backup_job_details

1) From default views

a) Query to display FULL and INCR backup details of database by using v$views

Version: 12c/19c

Query/script name:backup_report.sql –> Same query for standalone DB or RAC DB.

Purpose: To display the Full and Incrimental backup of a database.

Parameters to be passed: No

correct usage: @$HOME/db_backup_status_FULL_INCR_.sql

Technology: SQL query in Oracle

sub scripts: No

 

FileName: db_backup_status_FULL_INCR_.sql

Content:

connect to target database .

DB Name: TEST
Instance Name: TEST1

SQL> !cat db_backup_status_FULL_INCR_.sql
set lines 199
col host_name form a15
col instance_name form a12
col name form a8
select
i.host_name,
i.instance_name,
d.name,
a.start_time,
a.end_time,
round(a.ELAPSED_SECONDS/3600,2) “ELAPSED(H)”,
round(a.INPUT_BYTES/1024/1024/1024,2) “INPUT(GB)”,
round(a.OUTPUT_BYTES/1024/1024/1024,2)”OUTPUT(GB)”,
b.TYPE,
CASE a.status
WHEN ‘COMPLETED’ THEN ‘COMPLETED’
WHEN ‘COMPLETED WITH WARNINGS’ THEN ‘COMPLETED WITH WARNINGS’
WHEN ‘FAILED’ THEN ‘FAILED’
WHEN ‘RUNNING’ THEN ‘RUNNING’
END STATUS
from v$rman_backup_job_details a,
(select session_key,session_recid,
decode(incremental_level,’0′,’DBFULL’,’1′,’DBINCR’) “TYPE”,
to_char(min(start_time),’dd-mon-rr hh24:mi’) “Start”,
to_char(max(completion_time),’dd-mon-rr hh24:mi’) “End”,
status
from v$backup_set_details
where incremental_level in (‘0′,’1′)
group by session_key,session_recid,backup_type,incremental_level,to_char(start_time,’dd-mon-rrrr’),status) b,v$instance i,v$database d
where a.session_key=b.session_key
and start_time>=sysdate-14
–and a.status=’COMPLETED’
group by a.session_key,a.session_recid,start_time,a.end_time,
round(a.ELAPSED_SECONDS/3600,2),round(a.INPUT_BYTES/1024/1024/1024,2),round(a.OUTPUT_BYTES/1024/1024/1024,2),b.type,a.status,
i.host_name,i.instance_name,d.name
order by a.start_time
/

SQL>

Oracle views :

v$backup_set_details
v$rman_backup_job_details
v$database
v$instance

 

Sample output:

set lines 199
col host_name form a15
col instance_name form a12
col name form a8
select
i.host_name,
i.instance_name,
d.name,
a.start_time,
a.end_time,
round(a.ELAPSED_SECONDS/3600,2) “ELAPSED(H)”,
round(a.INPUT_BYTES/1024/1024/1024,2) “INPUT(GB)”,
round(a.OUTPUT_BYTES/1024/1024/1024,2)”OUTPUT(GB)”,
b.TYPE,
CASE a.status
WHEN ‘COMPLETED’ THEN ‘COMPLETED’
WHEN ‘COMPLETED WITH WARNINGS’ THEN ‘COMPLETED WITH WARNINGS’
WHEN ‘FAILED’ THEN ‘FAILED’
WHEN ‘RUNNING’ THEN ‘RUNNING’
END STATUS
from v$rman_backup_job_details a,
(select session_key,session_recid,
decode(incremental_level,’0′,’DBFULL’,’1′,’DBINCR’) “TYPE”,
to_char(min(start_time),’dd-mon-rr hh24:mi’) “Start”,
to_char(max(completion_time),’dd-mon-rr hh24:mi’) “End”,
status
from v$backup_set_details
where incremental_level in (‘0′,’1′)
group by session_key,session_recid,backup_type,incremental_level,to_char(start_time,’dd-mon-rrrr’),status) b,v$instance i,v$database d
where a.session_key=b.session_key
and start_time>=sysdate-14
–and a.status=’COMPLETED’
group by a.session_key,a.session_recid,start_time,a.end_time,
round(a.ELAPSED_SECONDS/3600,2),round(a.INPUT_BYTES/1024/1024/1024,2),round(a.OUTPUT_BYTES/1024/1024/1024,2),b.type,a.status,
i.host_name,i.instance_name,d.name
order by a.start_time
/

SQL>

HOST_NAME INSTANCE_NAM NAME START_TIM END_TIME ELAPSED(H) INPUT(GB) OUTPUT(GB) TYPE STATUS
————— ———— ——– ——— ——— ———- ———- ———- —— ———
RAC_NODE1 TEST1 TEST 05-APR-23 05-APR-23 1.28 480.48 4.94 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 06-APR-23 06-APR-23 1.26 479.96 .78 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 07-APR-23 07-APR-23 1.25 479.84 .57 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 09-APR-23 09-APR-23 1.26 479.99 .95 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 10-APR-23 10-APR-23 1.25 479.9 .66 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 11-APR-23 11-APR-23 1.15 479.78 .52 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 12-APR-23 12-APR-23 1.27 479.98 .86 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 13-APR-23 13-APR-23 1.27 480.38 1.5 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 14-APR-23 14-APR-23 1.28 480.36 4.63 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 16-APR-23 16-APR-23 1.38 480.84 2.05 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 17-APR-23 17-APR-23 1.34 479.9 .64 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 18-APR-23 18-APR-23 1.34 479.88 .72 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 19-APR-23 19-APR-23 1.34 479.86 .62 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 20-APR-23 20-APR-23 1.34 479.89 .63 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 21-APR-23 21-APR-23 1.39 479.85 .67 DBINCR COMPLETED

SQL>

b) Query to display FULL backup details of database by using v$views

set lines 199
col host_name form a15
col instance_name form a12
col name form a8
select
i.host_name,
i.instance_name,
d.name,
a.start_time,
a.end_time,
round(a.ELAPSED_SECONDS/3600,2) “ELAPSED(H)” ,
round(a.INPUT_BYTES/1024/1024/1024,2) “INPUT(GB)”,
round(a.OUTPUT_BYTES/1024/1024/1024,2) “OUTPUT(GB)”,
b.TYPE,
CASE a.status
WHEN ‘COMPLETED’ THEN ‘COMPLETED’
WHEN ‘COMPLETED WITH WARNINGS’ THEN ‘COMPLETED WITH WARNINGS’
WHEN ‘FAILED’ THEN ‘FAILED’
WHEN ‘RUNNING’ THEN ‘RUNNING’
END STATUS
from v$rman_backup_job_details a,
(select session_key,session_recid,
decode(incremental_level,’0′,’DBFULL’,’1′,’DBINCR’) “TYPE”,
to_char(min(start_time),’dd-mon-rr hh24:mi’) “Start”,
to_char(max(completion_time),’dd-mon-rr hh24:mi’) “End”,
status
from v$backup_set_details
where incremental_level in (‘0′)
group by session_key,session_recid,backup_type,incremental_level,to_char(start_time,’dd-mon-rrrr’),status) b,v$instance i,v$database d
where a.session_key=b.session_key
–and start_time>=sysdate-10
–and a.status in (‘COMPLETED’)
group by a.session_key,a.session_recid,a.start_time,a.end_time,
round(a.ELAPSED_SECONDS/3600,2),round(a.INPUT_BYTES/1024/1024/1024,2),round(a.OUTPUT_BYTES/1024/1024/1024,2),b.type,a.status,i.host_name,i.instance_name,d.name
/

HOST_NAME INSTANCE_NAM NAME START_TIM END_TIME ELAPSED(H) INPUT(GB) OUTPUT(GB) TYPE STATUS
————— ———— ——– ——— ——— ———- ———- ———- —— ———-
RAC_NODE1 TEST1 TEST 18-MAR-23 18-MAR-23 2.69 479.45 349.04 DBFULL FAILED
RAC_NODE1 TEST1 TEST 25-MAR-23 25-MAR-23 3.49 479.94 349.52 DBFULL COMPLETED
RAC_NODE1 TEST1 TEST 01-APR-23 01-APR-23 3.49 480.23 349.83 DBFULL COMPLETED
RAC_NODE1 TEST1 TEST 08-APR-23 08-APR-23 3.32 479.91 349.54 DBFULL COMPLETED
RAC_NODE1 TEST1 TEST 10-APR-23 10-APR-23 2.35 479.6 349.22 DBFULL COMPLETED
RAC_NODE1 TEST1 TEST 22-APR-23 22-APR-23 3.47 480.44 350.1 DBFULL COMPLETED

 

c) Query to display INCR backup details of database by using v$views

set lines 199
col host_name form a15
col instance_name form a12
col name form a8

select
i.host_name,
i.instance_name,
d.name,
a.start_time,
a.end_time,
round(a.ELAPSED_SECONDS/3600,2) “ELAPSED(H)” ,
round(a.INPUT_BYTES/1024/1024/1024,2) “INPUT(GB)”,
round(a.OUTPUT_BYTES/1024/1024/1024,2) “OUTPUT(GB)”,
b.TYPE,
CASE a.status
WHEN ‘COMPLETED’ THEN ‘COMPLETED’
WHEN ‘COMPLETED WITH WARNINGS’ THEN ‘COMPLETED WITH WARNINGS’
WHEN ‘FAILED’ THEN ‘FAILED’
WHEN ‘RUNNING’ THEN ‘RUNNING’
END STATUS
from v$rman_backup_job_details a,
(select session_key,session_recid,
decode(incremental_level,’0′,’DBFULL’,’1′,’DBINCR’) “TYPE”,
to_char(min(start_time),’dd-mon-rr hh24:mi’) “Start”,
to_char(max(completion_time),’dd-mon-rr hh24:mi’) “End”,
status
from v$backup_set_details
where incremental_level in (‘1′)
group by session_key,session_recid,backup_type,incremental_level,to_char(start_time,’dd-mon-rrrr’),status) b,v$instance i,v$database d
where a.session_key=b.session_key
–and start_time>=sysdate-10
–and a.status in (‘COMPLETED’)
group by a.session_key,a.session_recid,a.start_time,a.end_time,
round(a.ELAPSED_SECONDS/3600,2),round(a.INPUT_BYTES/1024/1024/1024,2),round(a.OUTPUT_BYTES/1024/1024/1024,2),b.type,a.status,i.host_name,i.instance_name,d.name
/
HOST_NAME INSTANCE_NAM NAME START_TIM END_TIME ELAPSED(H) INPUT(GB) OUTPUT(GB) TYPE STATUS
————— ———— ——– ——— ——— ———- ———- ———- —— ———-
RAC_NODE1 TEST1 TEST 05-APR-23 05-APR-23 1.28 480.48 4.94 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 06-APR-23 06-APR-23 1.26 479.96 .78 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 07-APR-23 07-APR-23 1.25 479.84 .57 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 09-APR-23 09-APR-23 1.26 479.99 .95 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 10-APR-23 10-APR-23 1.25 479.9 .66 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 11-APR-23 11-APR-23 1.15 479.78 .52 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 12-APR-23 12-APR-23 1.27 479.98 .86 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 13-APR-23 13-APR-23 1.27 480.38 1.5 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 14-APR-23 14-APR-23 1.28 480.36 4.63 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 16-APR-23 16-APR-23 1.38 480.84 2.05 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 17-APR-23 17-APR-23 1.34 479.9 .64 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 18-APR-23 18-APR-23 1.34 479.88 .72 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 19-APR-23 19-APR-23 1.34 479.86 .62 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 20-APR-23 20-APR-23 1.34 479.89 .63 DBINCR COMPLETED
RAC_NODE1 TEST1 TEST 21-APR-23 21-APR-23 1.39 479.85 .67 DBINCR COMPLETED

2) By Using Catalog views

connect to catalog database

SQL> conn rman_cat/rman@rman_dev
Connected.
SQL>

RC Views

  • rc_rman_backup_job_details
  • rc_backup_set_details

a) Query to display FULL and INCR backup details of database by using RC views

set lines 199
col host_name form a15
col instance_name form a8
col name form a8

select a.db_name,
a.start_time,
a.end_time ,
round(a.ELAPSED_SECONDS/3600,2) ELAPSED_H,
round(a.INPUT_BYTES/1024/1024/1024,2) INPUT_GB,
round(a.OUTPUT_BYTES/1024/1024/1024,2) OUTPUT_GB,
b.TYPE,
CASE a.status
WHEN ‘COMPLETED’ THEN ‘COMPLETED’
WHEN ‘COMPLETED WITH WARNINGS’ THEN ‘COMPLETED WITH WARNINGS’
WHEN ‘FAILED’ THEN ‘FAILED’
WHEN ‘RUNNING’ THEN ‘RUNNING’
END STATUS
from rc_rman_backup_job_details a,
(select session_key,session_recid,db_name,
decode( incremental_level,’0′,’Full’,’1′,’INCR’) “TYPE”,
to_char(min(start_time),’dd-mon-rr hh24:mi’) “Start”,
to_char(max(completion_time),’dd-mon-rr hh24:mi’) “End”,
status
from rc_backup_set_details
where incremental_level in (‘0′,’1′)
group by session_key,session_recid,db_name,backup_type,incremental_level,to_char(start_time,’dd-mon-rrrr’),status) b
where a.session_key=b.session_key
and start_time>=sysdate-14 and a.db_name in (‘TEST’)
group by a.session_key,a.session_recid,a.db_name,start_time,a.end_time,
round(a.ELAPSED_SECONDS/3600,2),round(a.INPUT_BYTES/1024/1024/1024,2),round(a.OUTPUT_BYTES/1024/1024/1024,2),b.type,a.status
order by a.db_name,a.start_time
/

DB_NAME START_TIME END_TIME ELAPSED_H INPUT_GB OUTPUT_GB TYPE STATUS
——– ———- ———- ———- ———- ———- —- ———-
TEST 09-04-2023 09-04-2023 1.26 479.99 .95 INCR COMPLETED
TEST 10-04-2023 10-04-2023 1.25 479.9 .66 INCR COMPLETED
TEST 10-04-2023 10-04-2023 2.35 479.6 349.22 Full COMPLETED
TEST 11-04-2023 11-04-2023 1.15 479.78 .52 INCR COMPLETED
TEST 12-04-2023 12-04-2023 1.27 479.98 .86 INCR COMPLETED
TEST 13-04-2023 13-04-2023 1.27 480.38 1.5 INCR COMPLETED
TEST 14-04-2023 14-04-2023 1.28 480.36 4.63 INCR COMPLETED
TEST 16-04-2023 16-04-2023 1.38 480.84 2.05 INCR COMPLETED
TEST 17-04-2023 17-04-2023 1.34 479.9 .64 INCR COMPLETED
TEST 18-04-2023 18-04-2023 1.34 479.88 .72 INCR COMPLETED
TEST 19-04-2023 19-04-2023 1.34 479.86 .62 INCR COMPLETED
TEST 20-04-2023 20-04-2023 1.34 479.89 .63 INCR COMPLETED
TEST 21-04-2023 21-04-2023 1.39 479.85 .67 INCR COMPLETED
TEST 22-04-2023 22-04-2023 3.47 480.44 350.1 Full COMPLETED

Note: There is no HOST_NAME field in the RC views

b) Query to display FULL backup details of database by using RC views

set lines 199
col db_name form a8

select a.db_name,
a.start_time,
a.end_time ,
round(a.ELAPSED_SECONDS/3600,2) ELAPSED_H,
round(a.INPUT_BYTES/1024/1024/1024,2) INPUT_GB,
round(a.OUTPUT_BYTES/1024/1024/1024,2) OUTPUT_GB,
b.TYPE,
CASE a.status
WHEN ‘COMPLETED’ THEN ‘COMPLETED’
WHEN ‘COMPLETED WITH WARNINGS’ THEN ‘COMPLETED WITH WARNINGS’
WHEN ‘FAILED’ THEN ‘FAILED’
WHEN ‘RUNNING’ THEN ‘RUNNING’
END STATUS
from rc_rman_backup_job_details a,
(select session_key,session_recid,db_name,
decode( incremental_level,’0′,’Full’,’1′,’INCR’) “TYPE”,
to_char(min(start_time),’dd-mon-rr hh24:mi’) “Start”,
to_char(max(completion_time),’dd-mon-rr hh24:mi’) “End”,
status
from rc_backup_set_details
where incremental_level in (‘0′)
group by session_key,session_recid,db_name,backup_type,incremental_level,to_char(start_time,’dd-mon-rrrr’),status) b
where a.session_key=b.session_key
and start_time>=sysdate-14 and a.db_name in (‘TEST’)
group by a.session_key,a.session_recid,a.db_name,start_time,a.end_time,
round(a.ELAPSED_SECONDS/3600,2),round(a.INPUT_BYTES/1024/1024/1024,2),round(a.OUTPUT_BYTES/1024/1024/1024,2),b.type,a.status
order by a.db_name,a.start_time
/

DB_NAME START_TIME END_TIME ELAPSED_H INPUT_GB OUTPUT_GB TYPE STATUS
——– ———- ———- ———- ———- ———- —- ———-
TEST 10-04-2023 10-04-2023 2.35 479.6 349.22 Full COMPLETED
TEST 22-04-2023 22-04-2023 3.47 480.44 350.1 Full COMPLETED

c) Query to display INCR backup details of database by using RC views

set lines 199
col db_name form a8

select a.db_name,
a.start_time,
a.end_time ,
round(a.ELAPSED_SECONDS/3600,2) ELAPSED_H,
round(a.INPUT_BYTES/1024/1024/1024,2) INPUT_GB,
round(a.OUTPUT_BYTES/1024/1024/1024,2) OUTPUT_GB,
b.TYPE,
CASE a.status
WHEN ‘COMPLETED’ THEN ‘COMPLETED’
WHEN ‘COMPLETED WITH WARNINGS’ THEN ‘COMPLETED WITH WARNINGS’
WHEN ‘FAILED’ THEN ‘FAILED’
WHEN ‘RUNNING’ THEN ‘RUNNING’
END STATUS
from rc_rman_backup_job_details a,
(select session_key,session_recid,db_name,
decode( incremental_level,’0′,’Full’,’1′,’INCR’) “TYPE”,
to_char(min(start_time),’dd-mon-rr hh24:mi’) “Start”,
to_char(max(completion_time),’dd-mon-rr hh24:mi’) “End”,
status
from rc_backup_set_details
where incremental_level in (‘1′)
group by session_key,session_recid,db_name,backup_type,incremental_level,to_char(start_time,’dd-mon-rrrr’),status) b
where a.session_key=b.session_key
and start_time>=sysdate-14 and a.db_name in (‘TEST’)
group by a.session_key,a.session_recid,a.db_name,start_time,a.end_time,
round(a.ELAPSED_SECONDS/3600,2),round(a.INPUT_BYTES/1024/1024/1024,2),round(a.OUTPUT_BYTES/1024/1024/1024,2),b.type,a.status
order by a.db_name,a.start_time
/

DB_NAME START_TIME END_TIME ELAPSED_H INPUT_GB OUTPUT_GB TYPE STATUS
——– ———- ———- ———- ———- ———- —- ———
TEST 09-04-2023 09-04-2023 1.26 479.99 .95 INCR COMPLETED
TEST 10-04-2023 10-04-2023 1.25 479.9 .66 INCR COMPLETED
TEST 11-04-2023 11-04-2023 1.15 479.78 .52 INCR COMPLETED
TEST 12-04-2023 12-04-2023 1.27 479.98 .86 INCR COMPLETED
TEST 13-04-2023 13-04-2023 1.27 480.38 1.5 INCR COMPLETED
TEST 14-04-2023 14-04-2023 1.28 480.36 4.63 INCR COMPLETED
TEST 16-04-2023 16-04-2023 1.38 480.84 2.05 INCR COMPLETED
TEST 17-04-2023 17-04-2023 1.34 479.9 .64 INCR COMPLETED
TEST 18-04-2023 18-04-2023 1.34 479.88 .72 INCR COMPLETED
TEST 19-04-2023 19-04-2023 1.34 479.86 .62 INCR COMPLETED
TEST 20-04-2023 20-04-2023 1.34 479.89 .63 INCR COMPLETED
TEST 21-04-2023 21-04-2023 1.39 479.85 .67 INCR COMPLETED

 

See Also: