Recovery from missing or corrupted datafile(s):

Case 1: Recovery from corrupted or missing datafile

This scenario deals with a situation where a datafile has gone missing, or is corrupted beyond repair. For concreteness, we look at a case where a datafile is missing. Below is a transcript of an SQL Plus session that attempts to open a database with a missing datafile (typed commands in bold, lines in italics are my comments, all other lines are feedback from SQL Plus):

--open SQL Plus from the command line without

--logging on to database

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jan 25 14:52:41 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

--Connect to the idle Oracle process as a privileged user and start up instance

SQL> connect / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 131555128 bytes

Fixed Size 454456 bytes

Variable Size 88080384 bytes

Database Buffers 41943040 bytes

Redo Buffers 1077248 bytes

Database mounted.



ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: 'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'

SQL>

The error message tells us that file# 4 is missing. Note that although the startup command has failed, the database is in the mount state. Thus, the database control file, which is also the RMAN repository can be accessed by the instance and by RMAN. We now recover the missing file using RMAN. The transcript of the recovery session is reproduced below (bold lines are typed commands, comments in italics, the rest is feedback from RMAN):

--logon to RMAN

C:\>rman target /

Recovery Manager: Release 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: ORCL (DBID=1507972899)



--restore missing datafile

RMAN> restore datafile 4;



Starting restore at 26/JAN/05

using target database controlfile instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=14 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=15 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:\BACKUP\0QGB0UEC_1_1.BAK tag=TAG20050124T152708 params=NULL

channel ORA_DISK_1: restore complete

Finished restore at 26/JAN/05

--recover restored datafile - RMAN applies all logs automatically

RMAN> recover datafile 4;

Starting recover at 26/JAN/05 using channel ORA_DISK_1

using channel ORA_DISK_2

starting media recovery

archive log thread 1 sequence 4 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_4.ARC

archive log thread 1 sequence 5 is already on disk as file C:\ORACLE_ARCHIVE\ORCL\1_5.ARC

archive log thread 1 sequence 6 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_6.ARC

archive log thread 1 sequence 7 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_7.ARC

archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_4.ARC thread=1 sequence=4

archive log filename=C:\ORACLE_ARCHIVE\ORCL\1_5.ARC thread=1 sequence=5

media recovery complete

Finished recover at 26/JAN/05

--open database for general use

RMAN> alter database open;

database opened

RMAN>

In the above scenario, the database is already in the mount state before the RMAN session is initiated. If the database is not mounted, you should issue a "startup mount" command before attempting to restore the missing datafile. The database must be mounted before any datafile recovery can be done.

If the database is already open when datafile corruption is detected, you can recover the datafile without shutting down the database. The only additional step is to take the relevant tablespace offline before starting recovery. In this case you would perform recovery at the tablespace level. The commands are:

C:\>rman target /

Recovery Manager: Release 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: ORCL (DBID=1507972899)

--offline affected tablespace

RMAN> sql 'alter tablespace USERS offline immediate';

using target database controlfile instead of recovery catalog

sql statement: alter tablespace USERS offline immediate

--recover offlined tablespace

RMAN> recover tablespace USERS;

Starting recover at 26/JAN/05

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=14 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=12 devtype=DISK

starting media recovery

media recovery complete

Finished recover at 26/JAN/05

--online recovered tablespace

RMAN> sql 'alter tablespace USERS online';

sql statement: alter tablespace USERS online

RMAN>

Here we have used the SQL command, which allows us to execute arbitrary SQL from within RMAN.

Case 2: Recovery from block corruption

It is possible to recover corrupted blocks using RMAN backups. This is a somewhat exotic scenario, but it can be useful in certain circumstances, as illustrated by the following example. Here's the situation: a user connected to SQLPlus gets a data block corruption error when she queries a table. Here's a part of the session transcript:

SQL> connect testuser/testpassword

Connected.

SQL> select count(*) from test_table;

select count(*) from test_table

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 4, block # 2015)

ORA-01110: data file 4: 'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'

Since we know the file and block number, we can perform block level recovery using RMAN. This is best illustrated by example:

C:\>rman target /

Recovery Manager: Release 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: ORCL (DBID=1507972899)

--restore AND recover specific block

RMAN> blockrecover datafile 4 block 2015;

Starting blockrecover at 26/JAN/05

using target database controlfile instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=19 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=20 devtype=DISK

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00004

channel ORA_DISK_1: restored block(s) from backup piece 1

piece handle=E:\BACKUP\0QGB0UEC_1_1.BAK tag=TAG20050124T152708 params=NULL

channel ORA_DISK_1: block restore complete

starting media recovery

media recovery complete

Finished blockrecover at 26/JAN/05

RMAN>

Now our user should be able to query the table from her SQLPlus session. Here's her session transcript after block recovery.

SQL> select count(*) from test_table;

COUNT(*)

----------

217001

SQL>

A couple of important points regarding block recovery:

1. Block recovery can only be done using RMAN.

2. The entire database can be open while performing block recovery.

3. Check all database files for corruption. This is important - there could be other corrupted blocks. Verification of database files can be done using RMAN or the dbverify utility. To verify using RMAN simply do a complete database backup with default settings. If RMAN detects block corruption, it will exit with an error message pointing out the guilty file/block.