Datafile Recovery
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_DATADATAFILESORCLUSERS01.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_DATADATAFILESORCLUSERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:BACKUP QGB0UEC_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_ARCHIVEORCL1_4.ARC
archive log thread 1 sequence 5 is already on disk as file C:ORACLE_ARCHIVEORCL1_5.ARC
archive log thread 1 sequence 6 is already on disk as file E:ORACLE_ARCHIVEORCL1_6.ARC
archive log thread 1 sequence 7 is already on disk as file E:ORACLE_ARCHIVEORCL1_7.ARC
archive log filename=E:ORACLE_ARCHIVEORCL1_4.ARC thread=1 sequence=4
archive log filename=C:ORACLE_ARCHIVEORCL1_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_DATADATAFILESORCLUSERS01.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 QGB0UEC_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.
Responses