Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02 |
|
|
View PDF |
Before making a backup, you must identify all the files in your database and decide what to back up. Several V$ views can provide the necessary information.
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:
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;
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;
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.
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.
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.