Keeping Records of Current and Backup Database 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;

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.