The techniques for determining which files require restore or recovery depend upon the type of file that is lost.
Recognizing a Lost Control File
- The database shuts down immediately if any of the control file copies becomes inaccessible and reports an error.
- Loss of some but not all copies of your control file does not require recovery of the control file from backup.
- Copy an intact copy of the control file over the damaged or missing control file, OR update the parameter file so that does not refer to the damaged or missing control file.
If control file restored from backup, media recovery of the whole database is required and then perform an OPEN RESETLOGS
Identifying Datafiles Requiring Recovery
When and how to recover depends on the state of the database and the location of its data files.
Identifying Datafiles with RMAN
An easy technique for determining which data files are missing is to run a VALIDATE DATABASE command, which attempts to read all specified data files. For example, start the RMAN client and run the following commands to validate the database (sample output included).
RMAN> VALIDATE DATABASE;
Starting validate at 20-OCT-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=90 device type=DISK
could not read file header for datafile 7 error reason 4
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-03002: failure of backup command at 10/20/2007 13:05:43
RMAN-06056: could not access datafile 7
The output in Example 17-1 indicates that data file 7 is inaccessible. You can then run the REPORT SCHEMA command to obtain the tablespace name and file name for data file 7 as follows (sample output included):
RMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name RDBMS
List of Permanent Datafiles
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 450 SYSTEM *** +DATAFILE/tbs_01.f
2 86 SYSAUX *** +DATAFILE/tbs_ax1.f
3 15 UD1 *** +DATAFILE/tbs_undo1.f
4 2 SYSTEM *** +DATAFILE/tbs_02.f
5 2 TBS_1 *** +DATAFILE/tbs_11.f
6 2 TBS_1 *** +DATAFILE/tbs_12.f
7 2 TBS_2 *** +DATAFILE/tbs_21.f
List of Temporary Files
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 40 TEMP 32767 +DATAFILE/tbs_tmp1.f
Identifying Datafiles with SQL
VALIDATE DATABASE is a good technique for determining whether files are inaccessible, you may want to use SQL queries to obtain more detailed information.
- Start SQL*Plus and connect to the target database instance with administrator privileges.
- Execute following queries to know the status of database files
V$INSTANCEto determine the status of the database.
SQL> SELECT STATUS FROM V$INSTANCE;
If the status is OPEN, then, some datafiles may require media recovery.
V$DATAFILE_HEADERto determine the status of your data files. Run the following SQL statements to check the data file headers
#First format query output for better display:
SQL> COL FILE# FORMAT 999
SQL> COL STATUS FORMAT A7
SQL> COL ERROR FORMAT A10
SQL> COL TABLESPACE_NAME FORMAT A10
SQL> COL NAME FORMAT A30
SQL> SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
WHERE RECOVER ='YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);
Each row returned a datafile that either requires media recovery or has an error requiring a restore. Check the RECOVER and ERROR columns. RECOVER indicates whether a file needs media recovery, and ERROR indicates whether there was an error reading and validating the data file header.
If ERROR is not NULL, then the data file header cannot be read and validated. Check for a temporary hardware or operating system problem causing the error. If there is no such problem, you must restore the file or switch to a copy.
If the ERROR column is NULL and the RECOVER column is YES, then the file requires media recovery (and may also require a restore from backup).
Because V$DATAFILE_HEADER only reads the header block of each data file, it does not detect all problems that require the data file to be restored. For example, this view cannot tell whether a data file contains corrupt data blocks.
- Optionally, query
V$RECOVER_FILEto list data files requiring recovery by data file number with their status and error information.
SQL> SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME
You can not use V$RECOVER_FILE with a control file restored from backup or a control file that was re-created. A restored or re-created control file does not contain the information needed to update V$RECOVER_FILE accurately.
V$TABLESPACE views to find data file and tablespace names using joins using:
#First format query output for better display:
COL DF# FORMAT 999
COL DF_NAME FORMAT A35
COL TBSP_NAME FORMAT A7
COL STATUS FORMAT A7
COL ERROR FORMAT A10
COL CHANGE# FORMAT 99999999
SQL> SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name, d.STATUS, r.ERROR, r.CHANGE#, r.TIME
FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS# AND d.FILE# = r.FILE#;
The ERROR column identifies the problem for each file requiring recovery.
Recovery of Read-Only Tablespaces
Recovery is not needed on any read-only tablespace during crash or instance recovery.
If you restore a read-only tablespace from a backup taken before the tablespace was made read-only, then you cannot access the tablespace until you perform media recovery on it.