Is there a Backup Status (number of days since last backup) checkpoint for the oracle probe?

Document ID : KB000034374
Last Modified Date : 14/02/2018
Show Technical Document Details

Question: 

Does the oracle probe have a checkpoint that tells you if a database has been backed up in the last n days?

Answer: 

There is such a checkpoint in the sqlserver probe (number of days since last backup), but there is NO equivalent checkpoint in the oracle probe.

Additional Information:

You will find Oracle to be far more flexible than MS SQL Server, hence it is also far more complicated.  Unlike MS SQL Server, Oracle provides many backup methods:

- RMAN
- OS backups
- 3rd party backup tools (symatec, veritas, omniback etc.)

If you are using RMAN for your backups, there is a catalog database you can query with as set of RMAN commands:

http://dba-oracle.com/googlesearchsite_pro...=rmjan+commands

CODE
RMAN> list archivelog all backed up 1 times to device type xxx;

You also have to know the backup type (full, cumulative, incremental. level 1):

http://www.dba-oracle.com/t_rman_backup_types.htm

Try using the RMAN catalog tables:

- rc_backupset_summary
- rc_backup_set

Or query the v$ views for RMAN backup completion details:

- v$rman_status
- v$basckup_set

These SQL queries will find the number of days since the last backup:

CODE
-- *****************************************
-- SQL for v$backup_set to see last backup
-- *****************************************
select *
from
v$backup_set
where
completion_time > sysdate-7
order by
completion_time desc;

-- *****************************************
-- SQL for rc_backup_set to see most recent backup
-- *****************************************
select *
from
rc_backup_set
where
completion_time > sysdate-7
order by
completion_time desc;


-- *****************************************
-- Display most recently completed RMAN backup
-- *****************************************
select *
from
rc_backup_set
where
backup_type = 'D'
and
controlfile_included = 'BACKUP'
order by
completion_time desc

-- *****************************************
-- Display newest_backupset_time for RMAN backup
-- *****************************************
select
db_name,
newest_backup_time
from
rman.rc_backupset_summary
where
dn_name = 'MKYSID';


-- *****************************************
-- You can also outer join rc_backupset_summary
-- into rc_database to see the RMAN latest backup start time:
-- *****************************************
select
name,
max(start_time)
from r
man.rc_database d,
rman.rc_backup_set s
where
db_id(+) = dbid
group by
name
order by 2 desc;

-- *****************************************
-- Display RMAN backup status start and end times:
-- *****************************************
select
operation,
status,
object_type,
TO_CHAR(START_TIME,'MM/DD/YYYY:hh:mi:ss') as START_TIME,
TO_CHAR(END_TIME,'MM/DD/YYYY:hh:mi:ss') as END_TIME
from
v$rman_status
where
start_time > SYSDATE -1
and
operation = 'BACKUP'
and
object_type = 'DB FULL'
order by
start_time desc;