• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1083
  • Last Modified:

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.

0
slightwv (䄆 Netminder)
Asked:
slightwv (䄆 Netminder)
  • 4
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now