Skip Headers
Oracle® Database Backup and Recovery Advanced User's Guide
10g Release 2 (10.2)

Part Number B14191-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Keeping Records of Current and Backup Database Files

One of the most important aspects of user-managed backup and recovery is keeping records of all current database files as well as the backups of these files. For example, you should have records for the location of the following files:

Recording the Locations of Datafiles, Control Files, and Online Redo Logs

The following useful SQL script displays the location of all control files, datafiles, and online redo log files for the database:

SELECT NAME FROM V$DATAFILE
UNION ALL
SELECT MEMBER FROM V$LOGFILE
UNION ALL
SELECT NAME FROM V$CONTROLFILE;

See Also:

Oracle Database Reference for more information on the V$ views

Recording the Locations of Archived Redo Logs

You can determine the location of the default archived log destinations by executing the following SQL script:

SELECT NAME, VALUE 
FROM V$PARAMETER 
WHERE NAME LIKE log_archive_dest%
AND VALUE IS NOT NULL;

NAME                               VALUE
---------------------------------- -------------------------------------------
log_archive_dest_1                 LOCATION=/oracle/work/arc_dest/arc
log_archive_dest_state_1           enable

Determine the format for archived logs by running SHOW as follows:

SHOW PARAMETER LOG_ARCHIVE_FORMAT

To see a list of all the archived logs recorded in the control file, issue this query:

SELECT NAME FROM V$ARCHIVED_LOG;

Recording the Locations and Dates of Backup Files

It is not enough to merely record the location of backup files: you must correlate the backups with the original files. If possible, name the backups with names derived from the name of the primary file. Whatever naming system you use, keep a table containing the relevant information. For example, you could keep the following table as a record of database file locations in case of a restore emergency.

Datafile # Tbs Current Datafiles Backup Datafiles
0 (cf) 0 (cf) /oracle/oradata/trgt/control01.dbf /d2/control01_10_31_02.dbf
1 SYSTEM /oracle/oradata/trgt/system01.dbf /d2/system01_10_31_02.dbf
2 undo /oracle/oradata/trgt/undo01.dbf /d2/undo01_10_31_02.dbf
3 cwmlite /oracle/oradata/trgt/cwmlite01.dbf /d2/cwmlite01_10_31_02.dbf
4 drsys /oracle/oradata/trgt/drsys01.dbf /d2/drsys01_10_31_02.dbf