Need date of last time tablespace was put in backup mode

I'm either getting real lazy or this isn't all that easy.

Is there a view or low level table that keeps track of the last date a tablespace was placed in backup mode?

Background:
Using 3rd party backup software to do a hot backup every night.  I would like to be able to query that all tablespaces were at least placed in backup mode last night w/o having to migrate over to the server and check the alert log.

LVL 79
slightwv (䄆 Netminder) Asked:
Who is Participating?
 
helpneedCommented:
hi

use v$backup view it will help u out in this check time column

or u can create a table for that and put a trigger it should insert the time and name of the tablespace during backup... and later on u can use this

regards

SQL> desc v$backup;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------

 FILE#                                              NUMBER
 STATUS                                             VARCHAR2(18)
 CHANGE#                                            NUMBER
 TIME                                               DATE

SQL> select file#,status,time from v$backup;

     FILE# STATUS             TIME
---------- ------------------ ---------
         1 NOT ACTIVE
         2 NOT ACTIVE
         3 NOT ACTIVE
         4 NOT ACTIVE
         5 NOT ACTIVE
         6 NOT ACTIVE
         7 NOT ACTIVE
         8 NOT ACTIVE
         9 NOT ACTIVE

9 rows selected.

SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                 478812 28-APR-05
         2 NOT ACTIVE                  0
         3 NOT ACTIVE                  0
         4 NOT ACTIVE                  0
         5 NOT ACTIVE                  0
         6 NOT ACTIVE                  0
         7 NOT ACTIVE                  0
         8 NOT ACTIVE                  0
         9 NOT ACTIVE                  0

9 rows selected.

SQL> alter tablespace system end backup;

Tablespace altered.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 NOT ACTIVE             478812 28-APR-05
         2 NOT ACTIVE                  0
         3 NOT ACTIVE                  0
         4 NOT ACTIVE                  0
         5 NOT ACTIVE                  0
         6 NOT ACTIVE                  0
         7 NOT ACTIVE                  0
         8 NOT ACTIVE                  0
         9 NOT ACTIVE                  0

9 rows selected.

SQL>


regards
0
 
helpneedCommented:
hi
if u want time
please query this

 select to_char(time,'dd/mm/yy:hh:mi:ss') from v$backup
SQL> /

TO_CHAR(TIME,'DD/
-----------------
28/04/05:01:06:42


regards
0
 
kripa_odbaCommented:
As helpneed said we can get the date when the last time the tablespace was put in to backup mode from v$backup.

And we can also get the information is from alert log file.... Oracle will update alert log file each and every time once the tablespace has been taken to backup mode and vise versa............






0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
helpneedCommented:
hi kripa

i think he mentioned like he dont wanna search alert log

regards
0
 
slightwv (䄆 Netminder) Author Commented:
Back in the day, the time column of v$backup only showed the date while a backup was active (and I thought I had already tested this.......).  Any idea when this changed?

0
 
helpneedCommented:
hi

can u please explain me the exact requirements of yours check this i think this is the one you are looking

this was the firt date where table space ws in backup

SQL> select file#,status,time from v$backup;

     FILE# STATUS             TIME
---------- ------------------ ---------
         1 NOT ACTIVE         28-APR-05
         2 NOT ACTIVE
         3 NOT ACTIVE
         4 NOT ACTIVE
         5 NOT ACTIVE
         6 NOT ACTIVE
         7 NOT ACTIVE
         8 NOT ACTIVE
         9 NOT ACTIVE

9 rows selected.

then here iam again putting in backup mode next day

SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                 479788 29-APR-05
         2 NOT ACTIVE                  0
         3 NOT ACTIVE                  0
         4 NOT ACTIVE                  0
         5 NOT ACTIVE                  0
         6 NOT ACTIVE                  0
         7 NOT ACTIVE                  0
         8 NOT ACTIVE                  0
         9 NOT ACTIVE                  0

9 rows selected.

SQL> alter tablespace system end backup;

Tablespace altered.

this is the latest day were that table space is in backup mode

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 NOT ACTIVE             479788 29-APR-05
         2 NOT ACTIVE                  0
         3 NOT ACTIVE                  0
         4 NOT ACTIVE                  0
         5 NOT ACTIVE                  0
         6 NOT ACTIVE                  0
         7 NOT ACTIVE                  0
         8 NOT ACTIVE                  0
         9 NOT ACTIVE                  0

9 rows selected.

if u want the time also u can query this

SQL> select to_char(time,'dd/mm/yy:hh:mi:ss') from v$backup;

TO_CHAR(TIME,'DD/
-----------------
29/04/05:01:07:59

9 rows selected.

SQL> ed
Wrote file afiedt.buf

  1* select file#,status,to_char(time,'dd/mm/yy:hh:mi:ss') from v$backup
SQL> /

     FILE# STATUS             TO_CHAR(TIME,'DD/
---------- ------------------ -----------------
         1 NOT ACTIVE         29/04/05:01:07:59
         2 NOT ACTIVE
         3 NOT ACTIVE
         4 NOT ACTIVE
         5 NOT ACTIVE
         6 NOT ACTIVE
         7 NOT ACTIVE
         8 NOT ACTIVE
         9 NOT ACTIVE

9 rows selected.

please clear if this is not the one u required

regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.