Identifying Datafiles Requiring Recovery
To determine which if any files require media recovery using V$DATAFILE_HEADER, it does not detect all problems that require the datafile to be restored you can also query V$RECOVER_FILE to list datafiles requiring recovery by datafile number with their status and error information:
# 1-Connect to the target database using SQL.
sqlplus 'SYS/srdc@devtest AS SYSDBA'
# 2-Determine the status of the database
# If the status is OPEN, then, some datafiles may require media recovery.
# 3-Determine the status of datafiles headers
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 file needs media recovery
- • ERROR there was an error reading and validating the datafile header.
If ERROR is not NULL, then the datafile 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).
- 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.
SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME
Example-3 [V$DATAFILE and V$TABLESPACE]
To find datafile and tablespace names, that requires recovery using V$DATAFILE and V$TABLESPACE views. For example:
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
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.
Restoring and recovering a datafile is a very similar operation to restoring a tablespace. Once the missing or corrupted datafile is identified using the V$DATAFILE_HEADER view; the tablespace is taken offline, the datafile(s) are restored and recovered, and the tablespace is brought back online. If only file number 7(USERS tablespace) was lost recovery of the tablespace is possible while the database remains open and available, the recover and restore commands are as simple as this:
SQL>SELECT FILE#, STATUS, ERROR, TABLESPACE_NAME, NAME
The alert log would give you another clue the next time you tried to start the database with a missing or corrupted datafile:
ORA-01157: CANNOT IDENTIFY/LOCKDATA FILE 4- SEE DBWR TRACE FILE
ORA-01110: DATA FILE 4: '+DATA/DW/DATAFILE/USERS.259.630244583'
After replacing the disk drive, we initiate an RMAN session and find out that file number 7 corresponds to the USERS tablespace:
RMAN> REPORT SCHEMA;
#Force tablespace offline
RMAN> SQL 'ALTER TABLESPACE USERS OFFLINE IMMEDIATE';
#Copy latest image of datafile to their orignal location
RMAN> RESTORE TABLESPACE USERS;
#Applied redo form redo log
RMAN> RECOVER TABLESPACE USERS;
#Force tablespace back again online
RMAN> SQL 'ALTER TABLESPACE USERS ONLINE';
RMAN> SQL 'ALTER DATABASE DATAFILE 7 OFFLINE';
RMAN> RESTORE DATAFILE 7;
RMAN> RECOVER DATAFILE 7;
SQL 'ALTER DATABASE DATAFILE 7 ONLINE'
RMAN will restore from backup any archived redo logs required during the recovery operation. If backups are stored on a media manager.
One very useful option in managing disk space associated with these restored files is the DELETE ARCHIVELOG option, which causes the deletion of restored archived redo logs from disk once they are no longer needed for the RECOVER operation:
RMAN> RECOVER TABLESPACE users, tools DELETE ARCHIVELOG;
Note that when RMAN restores archived redo log files to the flash recovery area in order to perform a RECOVER operation, the restored logs are automatically deleted after they are applied to the datafiles, even if you do not use the DELETE ARCHIVELOG option.
if two datafile or tablespace are missing
RMAN> RECOVER DATAFILE '/newdisk/users01.dbf','/newdisk/tools01.dbf';