Datafile Recovery After Deleting Datafile On OS

It is not so rare case that due to the user or hardware error you finish with missing critical database file.
Then you can thank God that you have fresh backup and needed skills to perform datafile recovery.

I'll perform datafile recovery in this small and simple demo case.

OS: Linux 32-bit
DB: Oracle 10.2.0.4
ARCHIVELOG MODE ON




First let's make database backup using RMAN interface:

$ rman target /
RMAN> backup database include current controlfile;

Starting backup at 08-OCT-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=367 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=/oradata/sysaux01.dbf
input datafile fno=00001 name=/oradata/system01.dbf
input datafile fno=00005 name=/oradata/undotbs02.dbf
input datafile fno=00004 name=/oradata/new_location/users01.dbf
channel ORA_DISK_1: starting piece 1 at 08-OCT-09
channel ORA_DISK_1: finished piece 1 at 08-OCT-09
piece handle=/home/oracle/rmanbkp/full_s9kr9vvd_1_1 tag=TAG20091008T165052 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 08-OCT-09
channel ORA_DISK_1: finished piece 1 at 08-OCT-09
piece handle=/home/oracle/rmanbkp/full_sakra030_1_1 tag=TAG20091008T165052 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-OCT-09

Create test table in USERS tablespace:

SQL> create table test_recovery (a number) tablespace users;

Table created.

SQL> insert into test_recovery values (5);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_recovery;

A
----------
5


SQL> set lines 200
SQL> col file_name for a40
SQL> col tablespace_name for a15
SQL> col status for a15
SQL> select tablespace_name, file_name, status
2  from dba_data_files;

TABLESPACE_NAME FILE_NAME                                STATUS
--------------- ---------------------------------------- ---------------
UNDOTBS2        /oradata/undotbs02.dbf                   AVAILABLE
USERS           /oradata/new_location/users01.dbf        AVAILABLE
SYSAUX          /oradata/sysaux01.dbf                    AVAILABLE
SYSTEM          /oradata/system01.dbf                    AVAILABLE

Now to delete datafile 'users01.dbf' while database is up and running.

SQL> !rm /oradata/new_location/users01.dbf

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from test_recovery;
select * from test_recovery
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/oradata/new_location/users01.dbf'

I've received error due to the file missing.


Proceed with datafile recovery.

What will happen when we just execute 'restore datafile 4' from RMAN interface:

RMAN> restore datafile 4;

Starting restore at 08-OCT-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=379 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 /oradata/new_location/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rmanbkp/full_s5kr9tuh_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/08/2009 16:24:31
ORA-19870: error reading backup piece /home/oracle/rmanbkp/full_s5kr9tuh_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 4

To avoid this error we must start database in mount mode, but what will happen when we simple execute 'shutdown immediate':

SQL> shutdown immediate;
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oradata/new_location/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

Oracle is looking for database file I deleted and it cannont find it. So I first must offline and drop datafile in Oracle to be able shutdown database.

SQL> alter database datafile '/oradata/new_location/users01.dbf'
2  offline drop;

Database altered.

Shutdown database and start in mount mode:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1224736768 bytes
Fixed Size                  1267188 bytes
Variable Size             771754508 bytes
Database Buffers          436207616 bytes
Redo Buffers               15507456 bytes
Database mounted.

Restore and recover datafile from RMAN interface:

RMAN> restore datafile 4;

Starting restore at 08-OCT-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=379 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 /oradata/new_location/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rmanbkp/full_s9kr9vvd_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/rmanbkp/full_s9kr9vvd_1_1 tag=TAG20091008T165052
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 08-OCT-09

RMAN> recover datafile 4;

Starting recover at 08-OCT-09
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:04

Finished recover at 08-OCT-09

First check:

SQL> select * from test_recovery;
select * from test_recovery
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/oradata/new_location/users01.dbf'

Let's check state of the datafile:

SQL> set lines 200
SQL> col tablespace_name for a10
SQL> col file_name for a30
SQL> col online_status for a15
SQL> col status for a10
SQL> select tablespace_name, online_status, status
2  from dba_data_files where tablespace_name='USERS';

TABLESPACE ONLINE_STATUS   STATUS
---------- --------------- ----------
USERS      OFFLINE         AVAILABLE

Datafile in tablespace USERS is offline so I must bring datafile online.

SQL> alter tablespace users online;

Tablespace altered.

SQL> select tablespace_name, online_status, status
2  from dba_data_files where tablespace_name='USERS';

TABLESPACE ONLINE_STATUS   STATUS
---------- --------------- ----------
USERS      ONLINE          AVAILABLE

Final check:

SQL> select * from test_recovery;

A
----------
5