Querying V$ Views to Obtain Backup Information

Listing Database Files Before a Backup

Use V$DATAFILE, V$LOGFILE and V$CONTROLFILE to identify the datafiles, log files and control files for your database. This same procedure works whether you named these files manually or allowed Oracle Managed Files to name them.

To list datafiles, online redo logs, and control files:

1. Start SQL*Plus and query V$DATAFILE to obtain a list of datafiles. For example, enter:

   SQL> SELECT NAME FROM V$DATAFILE;
   
   

   You can also join the V$TABLESPACE and V$DATAFILE views to obtain a listing of datafiles along with their associated tablespaces:

   SELECT t.NAME "Tablespace", f.NAME "Datafile"
     FROM V$TABLESPACE t, V$DATAFILE f
     WHERE t.TS# = f.TS#
     ORDER BY t.NAME;
   
   

2. Obtain the filenames of online redo log files by querying the V$LOGFILE view. For example, issue the following query:

   SQL> SELECT MEMBER FROM V$LOGFILE;
   
   

3. Obtain the filenames of the current control files by querying the V$CONTROLFILE view. For example, issue the following query:

   SQL> SELECT NAME FROM V$CONTROLFILE;
   
   

   Note that you only need to back up one copy of a multiplexed control file.

4. If you plan to take a control file backup with the ALTER DATABASE BACKUP CONTROLFILE TO 'filename' statement, then save a list of all datafiles and online redo log files with the control file backup. Because the current database structure may not match the database structure at the time a given control file backup was created, saving a list of files recorded in the backup control file can aid the recovery procedure.

Determining Datafile Status for Online Tablespace Backups

To check whether a datafile is part of a current online tablespace backup, query the V$BACKUP view.

This view is useful only for user-managed online tablespace backups, because neither RMAN backups nor offline tablespace backups require the datafiles of a tablespace to be in backup mode.

The V$BACKUP view is most useful when the database is open. It is also useful immediately after an instance failure because it shows the backup status of the files at the time of the failure. Use this information to determine whether you have left any tablespaces in backup mode.

V$BACKUP is not useful if the control file currently in use is a restored backup or a new control file created after the media failure occurred. A restored or re-created control file does not contain the information the database needs to populate V$BACKUP accurately. Also, if you have restored a backup of a file, this file's STATUS in V$BACKUP reflects the backup status of the older version of the file, not the most current version. Thus, this view can contain misleading data about restored files.

For example, the following query displays which datafiles are currently included in a tablespace that has been placed in backup mode:

SELECT t.name AS "TB_NAME", d.file# as "DF#", d.name AS "DF_NAME", b.status
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';

The following sample output shows that the tools and users tablespaces currently have ACTIVE status:

TB_NAME                 DF#        DF_NAME                           STATUS
----------------------  ---------- --------------------------------  ------
TOOLS                   7          /oracle/oradata/trgt/tools01.dbf  ACTIVE
USERS                   8          /oracle/oradata/trgt/users01.dbf  ACTIVE

In the STATUS column, NOT ACTIVE indicates that the file is not currently in backup mode (that is, you have not executed the ALTER TABLESPACE ... BEGIN BACKUP or ALTER DATABASE BEGIN BACKUP statement), whereas ACTIVE indicates that the file is currently in backup mode.