Identifying Datafiles Requiring Media Recovery

Identifying Datafiles Requiring Media Recovery

When and how to recover depends on the state of the database and the location of its datafiles.

Identifying Datafiles with RMAN

An easy technique for determining which datafiles are missing is to run a VALIDATE DATABASE command, which attempts to read all specified datafiles. 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-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 10/20/2007 13:05:43

RMAN-06056: could not access datafile 7

The output in that datafile 7 is inaccessible. You can then run the REPORT SCHEMA command to obtain the tablespace name and filename for datafile 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

Although VALIDATE DATABASE is a good technique for determining whether files are inaccessible, you may want to use SQL queries to obtain more detailed information.

To determine whether datafiles require media recovery:

1. Start SQL*Plus and connect to the target database instance with administrator privileges.
2. Determine the status of the database by executing the following SQL query:

3.  SELECT STATUS FROM V$INSTANCE;

If the status is OPEN, then the database is open. Nevertheless, some datafiles may require media recovery.

4. Query V$DATAFILE_HEADER to determine the status of your datafiles. Run the following SQL statements to check the datafile headers:

5.  SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME

6.  FROM   V$DATAFILE_HEADER

7.  WHERE  RECOVER = 'YES'

8.  OR     (RECOVER IS NULL AND ERROR IS NOT NULL);

Each row returned represents a datafile that either requires media recovery or has an error requiring a restore. Check the RECOVER and ERROR columns. RECOVERindicates whether a file needs media recovery, and ERROR indicates whether 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).

9. Optionally, query V$RECOVER_FILE to list datafiles requiring recovery by datafile number with their status and error information. For example, execute the following query:

10.SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME

11.FROM   V$RECOVER_FILE;

Note:

To find datafile and tablespace names, you can also perform useful joins using the datafile number and the V$DATAFILE and V$TABLESPACE views. For example:

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.

Determining the DBID of the Database

In situations requiring the recovery of your server parameter file or control file from autobackup, you need to know the DBID. You should record the DBID along with other basic information about your database.

If you do not have a record of the DBID of your database, then you can find it in the following places without opening your database:

• If you have any text files that preserve the output from an RMAN session, then the DBID is displayed by the RMAN client when it starts up and connects to your database. Typical output follows:

·         % rman TARGET /

·          

·         Recovery Manager: Release 11.1.0.6.0 - Production on Wed Jul 11 17:51:30 2007

·          

·         Copyright (c) 1982, 2007, Oracle.  All rights reserved.

·          

connected to target database: PROD (DBID=36508508)