Wednesday, March 25, 2015

A Simple Example With V$BACKUP

I tested this in an Oracle 12.1 database. The V$BACKUP view tells you if a datafile is in hot backup mode. I started off with none of the datafiles in hot backup mode so they were all shown as NOT ACTIVE:

SQL> select file#, status from v$backup
  2  /
 
     FILE# STATUS
---------- ------------------
         1 NOT ACTIVE
         2 NOT ACTIVE
         3 NOT ACTIVE
         5 NOT ACTIVE
         6 NOT ACTIVE
 
SQL>

I put the USERS tablespace into hot backup mode and its datafile changed to ACTIVE in V$BACKUP until I took the tablespace out of hot backup mode:

SQL> alter tablespace users begin backup
  2  /
 
Tablespace altered.
 
SQL> select file_id from dba_data_files
  2  where tablespace_name = 'USERS'
  3  /
 
   FILE_ID
----------
         6
 
SQL> select file#, status from v$backup
  2  /
 
     FILE# STATUS
---------- ------------------
         1 NOT ACTIVE
         2 NOT ACTIVE
         3 NOT ACTIVE
         5 NOT ACTIVE
         6 ACTIVE
 
SQL> alter tablespace users end backup
  2  /
 
Tablespace altered.
 
SQL> select file#, status from v$backup
  2  /
 
     FILE# STATUS
---------- ------------------
         1 NOT ACTIVE
         2 NOT ACTIVE
         3 NOT ACTIVE
         5 NOT ACTIVE
         6 NOT ACTIVE
 
SQL>

… and when I put the whole database into hot backup mode, all the datafiles were shown as ACTIVE:

SQL> alter database begin backup
  2  /
 
Database altered.
 
SQL> select file#, status from v$backup
  2  /
 
     FILE# STATUS
---------- ------------------
         1 ACTIVE
         2 ACTIVE
         3 ACTIVE
         5 ACTIVE
         6 ACTIVE
 
SQL> alter database end backup
  2  /
 
Database altered.
 
SQL> select file#, status from v$backup
  2  /
 
     FILE# STATUS
---------- ------------------
         1 NOT ACTIVE
         2 NOT ACTIVE
         3 NOT ACTIVE
         5 NOT ACTIVE
         6 NOT ACTIVE
 
SQL>

No comments: