Home » RDBMS Server » Backup & Recovery » When was last full backup? (Oracle 11.1.0.6.0 64 bit.)
When was last full backup? [message #488089] Tue, 04 January 2011 11:23 Go to next message
brian313313
Messages: 2
Registered: January 2011
Location: Atlanta, GA
Junior Member
I am trying to figure out when the last full backup for datafiles was run on a database.

We are using the rman catalog and I have a few queries but they are not working as expected. The first is against rc_rman_status but I can't distinguish between full and incremental backups in this view.

I have another version running against the view rc_backup_set but it is showing full backups when I know they were incremental. (i.e. incrmental level = 0 or null)

I need a query since this is to go in an automated report/pager setup. The rman commands such as list will not work for this. Can someone recommend a query that will show the last full backup?

Thanks,
Brian.
Re: When was last full backup? [message #488090 is a reply to message #488089] Tue, 04 January 2011 11:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no difference between incremental 0 and full in your version. They are synonymous.

Post your queries.

Regards
Michel
Re: When was last full backup? [message #488091 is a reply to message #488090] Tue, 04 January 2011 12:12 Go to previous message
brian313313
Messages: 2
Registered: January 2011
Location: Atlanta, GA
Junior Member
-- This does not distinguish incremental vs. full
SELECT *
FROM rman_11_1.rc_rman_status
WHERE start_time > sysdate - 7 -- Only looking back one week
AND db_name IN('DB_NAME') -- List will be configuration driven.
AND object_type NOT IN('CONTROLFILE','ARCHIVELOG')
AND OPERATION = 'BACKUP'
ORDER BY db_name, command_id desc;

-- This works partially, but shows multiple backup sets and I don't know how to group them to see start and stop time.
SELECT db1.name, 
    MAX(bs1.start_time) last_backup_start, -- Wrong
    MAX(bs1.completion_time) last_backup_complete
FROM rman_11_1.rc_backup_set bs1
    INNER JOIN rman_11_1.rc_database db1 ON db1.db_key = bs1.db_key
WHERE bs1.start_time > sysdate - 7
  AND db1.name IN('DB_NAME') -- List will be configuration driven.
  AND bs1.backup_type = 'D' -- Full backups only
  AND bs1.incremental_level = 0
GROUP BY db1.name;


*< code tags > added by BlackSwan. Please do so yourself in the future

[Updated on: Tue, 04 January 2011 12:35] by Moderator

Report message to a moderator

Previous Topic: ORA-0036: file 57 can not be read - Controlfile Recreate Not of Help
Next Topic: Environment variable not evaluated in rman
Goto Forum:
  


Current Time: Thu Mar 28 20:38:33 CDT 2024