Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
Showing results for 
Search instead for 
Did you mean: 

How to find timestamp of last rman backup from catalog

Go to solution

How to find timestamp of last rman backup from catalog

Hi All,
We have a rman catalog which has information about all the rman backup for more than 60 database instances. I needed to find out the last time a full backup was taken for each instance.
Generally, list backup of database would give the summary of all the backups, but the time format is 28-OCT-04. Its does not give timestamp. Problem is that I cannot change the nls_date_format and reboot this instance.
I needed to know if any one has a SQL that can query the catalog views to get me the timestamp for last full backup for each instances.

Thank you all
Sushil Singh
James A. Donovan
Honored Contributor

Re: How to find timestamp of last rman backup from catalog

I grabbed this from
Looks basically like what you're asking for...

rem Filename: rmanstat.sql
rem Purpose: List completed RMAN backups for the last 24-hours
rem (use info from Dictionary - Control File Views)
rem Date: 12-Feb-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

tti "RMAN Backups for last 24-hours"

col type format a4
col handle format a35 trunc
col file# format 9999
col duration format a9

select decode(BACKUP_TYPE, 'L', 'ARCH', 'D', 'DB', 'I', 'INC',
'Unknown type='||BACKUP_TYPE) TYPE,
to_char(a.start_time, 'DDMON HH24:MI') start_time,
to_char(a.elapsed_seconds/60, '99.9')||' Min' DURATION,
substr(handle, -35) handle,
nvl(d.file#, l.sequence#) file#, nvl(d.blocks, l.blocks) blocks
where a.start_time between sysdate-1 and sysdate
and a.SET_STAMP = d.SET_STAMP(+)
and a.SET_STAMP = l.SET_STAMP(+)
order by start_time, file#
Remember, wherever you go, there you are...
Indira Aramandla
Honored Contributor

Re: How to find timestamp of last rman backup from catalog

Hi Sushil,

While inside RMAN you can use the list command to query the contents of the recovery catalog, or the target database control file if no recovery catalog is used.

To list all backups of files in tablespace tbs_1 that were made since November

RMAN> list until time 'Nov 1 1996 00:00:00' backupset of tablespace tbs_1;

If you are not using a recovery catalog, RMAN information is stored in the target database's control file .

This view displays backup set information from the controlfile. A backup set record is inserted after the backup set is successfully
RECID Backup set record ID
STAMP Backup set record timestamp
SET_STAMP Backup set stamp.
SET_COUNT Backup set count. The backup set count is incremented by one
every time a new backup set is started
BACKUP_TYPE Type of files that are in this backup.
archived redo logs = \QL
datafile full backup = \QD
incremental backup = \QI
INCREMENTAL_LEVEL Location where this backup set fits into the
database's backup strategy.
0 = full datafile backups
non-zero = incremental datafile backups
NULL = archivelog backups
PIECES Number of distinct backup pieces in backup set
COMPLETION_TIME Time when the backup completes successfully
ELAPSED_SECONDS The number of elapsed seconds.
BLOCK_SIZE Block size of the backup set

Indira A
Never give up, Keep Trying
Honored Contributor

Re: How to find timestamp of last rman backup from catalog


to the above great replies, please allow me to add the following:

to change the date format without restarting the database, you can also try:
alter session set nls_date_format='dd-mon-yyyy hh24:mi';

hope this helps too!
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)