Recovering from removed/deleted datafile

In this post will discuss on how to recover from the scenario where someone somehow deleted any datafile. But before proceeding make sure you have a backup of the database, and the archive logs from the backup till date. So to recover from that scenario we must create that scenario.

For creating scenarios follow this:

• Make sure you’ve a Permanent Tablespace which is not that important (In Test Env). If you don’t want to use any existing tablespace then create a new tablespace to take it to this scenario.

SYS@DBACODE > CREATE TABLESPACE USERS DATAFILE '/oracle_11204/oracle/oradata/dbacode/datafile/users01.dbf' SIZE 60M;

Tablespace created.

• If you’ve a user then assign this tablespace to that user as default tablespace and create a table with some entries. Or you can create a new user and create a table with some entries. Else you can assign some quota on the tablespace which you are going to test this scenario and create a table with TABLESPACE clause in CREATE TABLE syntax.

SYS@DBACODE > CREATE USER ajinkya IDENTIFIED BY girme
  2  DEFAULT TABLESPACE USERS QUOTA 30M ON USERS;

User created.

SYS@DBACODE > GRANT create session,create table TO ajinkya;

Grant succeeded.

SYS@DBACODE > conn ajinkya/girme
Connected.
AJINKYA@DBACODE > CREATE TABLE seqs (num NUMBER(2));

Table created.

AJINKYA@DBACODE > BEGIN
  2  FOR i IN 1..15 LOOP
  3  INSERT INTO seqs VALUES (i);
  4  END LOOP
  5  COMMIT;
  6  END;
  7  /

PL/SQL procedure successfully completed.

AJINKYA@DBACODE > SELECT count(*) FROM seqs;

  COUNT(*)
----------
        15

• If the backup of database is not taken then take the backup of the database or take backup of the tablespace on which this scenario is going to be tested. (For this I am going to take backup of full database with archivelogs)
  Note : Before using BACKUP SPFILE FORMAT, make sure your database is up with spfile.

RMAN> run
2> {
3> ALLOCATE CHANNEL disk1 TYPE DISK FORMAT '/oracle_11204/oracle/rman/dbacode/DBACODE_29117_%U.bak';
4> ALLOCATE CHANNEL disk2 TYPE DISK FORMAT '/oracle_11204/oracle/rman/dbacode/DBACODE_29117_%U.bak';
5> BACKUP DATABASE PLUS ARCHIVELOG;
6> BACKUP CURRENT CONTROLFILE FORMAT '/oracle_11204/oracle/rman/dbacode/DBACODE_29117_control.bak';
7> BACKUP SPFILE FORMAT '/oracle_11204/oracle/rman/dbacode/DBACODE_29117_spfile.bak';
8> RELEASE CHANNEL disk1;
9> RELEASE CHANNEL disk2;
10> }

• Now get the file location of the tablespace which is going to be tested for this scenario, after getting the file name go to Linux/Unix terminal and remove/move the file.

SYS@DBACODE > SELECT file_name FROM dba_data_files WHERE tablespace_name='USERS';

FILE_NAME
--------------------------------------------------------------------------------
/oracle_11204/oracle/oradata/dbacode/datafile/users01.dbf

SYS@DBACODE > exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dbrhel:/home/oracle]$ cd /oracle_11204/oracle/oradata/dbacode/datafile
[oracle@dbrhel:/oracle_11204/oracle/oradata/dbacode/datafile]$ ls -lrt
total 921636
-rw-r-----. 1 oracle oinstall 524296192 Jan 29 18:05 system01.dbf
-rw-r-----. 1 oracle oinstall 262152192 Jan 29 18:05 sysaux01.dbf
-rw-r-----. 1 oracle oinstall  94380032 Jan 29 18:05 undotbs1_01.dbf
-rw-r-----. 1 oracle oinstall  62922752 Jan 29 18:05 users01.dbf
[oracle@dbrhel:/oracle_11204/oracle/oradata/dbacode/datafile]$ mv users01.dbf users01.dbf_bk29
[oracle@dbrhel:/oracle_11204/oracle/oradata/dbacode/datafile]$ ls -ltr
total 921636
-rw-r-----. 1 oracle oinstall 524296192 Jan 29 18:05 system01.dbf
-rw-r-----. 1 oracle oinstall 262152192 Jan 29 18:05 sysaux01.dbf
-rw-r-----. 1 oracle oinstall  94380032 Jan 29 18:05 undotbs1_01.dbf
-rw-r-----. 1 oracle oinstall  62922752 Jan 29 18:05 users01.dbf_bk29
[oracle@dbrhel:/oracle_11204/oracle/oradata/dbacode/datafile]$

Now you’ve created the scenario where a datafile is missing from a tablespace, to be sure you can try to query the table which was created in the tablespace. You will get the error saying cannot open the file.

SYS@DBACODE > conn ajinkya/girme
Connected.
AJINKYA@DBACODE > CREATE TABLE seqs_1 AS SELECT * FROM seqs;
CREATE TABLE seqs_1 AS SELECT * FROM seqs
                                     *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4:
'/oracle_11204/oracle/oradata/dbacode/datafile/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

To recover from this scenario use below steps:

• Query the v$recover_file dictionary to see if it has entry of the deleted file or not, if it has then well and good if doesn’t have the entry then also there is no need to worry.
• Connect with SYSDBA or DBA privilege, and take that datafile offline. You can use its FILE# or its name to do the same. After taking the datafile offline query the v$recover_file, you will have the entry of the datafile.

SYS@DBACODE > SELECT file_name,status FROM dba_data_files WHERE tablespace_name='USERS';

FILE_NAME                                                              STATUS
---------------------------------------------------------------------- ---------
/oracle_11204/oracle/oradata/dbacode/datafile/users01.dbf              AVAILABLE

SYS@DBACODE > ALTER DATABASE DATAFILE '/oracle_11204/oracle/oradata/dbacode/datafile/users01.dbf' OFFLINE;

Database altered.

SYS@DBACODE > SELECT * FROM v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
         4 OFFLINE OFFLINE FILE NOT FOUND                                                             0

• Use RMAN to recover from this situation, use below commands to recover from the scenario:

RMAN> run
2> {
3> RESTORE DATAFILE 4;
4> RECOVER DATAFILE 4;
5> }

• After successful restoration and recovery, make the datafile online so that it can be visible to all.
  

AJINKYA@DBACODE > SELECT count(*) FROM seqs;
SELECT count(*) FROM seqs
                     *
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/oracle_11204/oracle/oradata/dbacode/datafile/users01.dbf'


AJINKYA@DBACODE > conn /as sysdba
Connected.
SYS@DBACODE > ALTER DATABASE DATAFILE '/oracle_11204/oracle/oradata/dbacode/datafile/users01.dbf' ONLINE;

Database altered.

SYS@DBACODE > conn ajinkya/girme
Connected.
AJINKYA@DBACODE > SELECT count(*) FROM seqs;

  COUNT(*)
----------
        15

Notes :

• If the datafile which we are recovering is not present physically then we first have to use RESTORE command of RMAN and then only we can use the RECOVER command of RMAN. Else it will throw an error that datafile must be restored.
• You've to use the FILE# of the datafile, if you use RESTORE DATAFILE command with the datafile path then the RMAN will give you the following error:

RMAN-00558: error encountered while parsing input commands
RMAN-01006: error signaled during parse
RMAN-02001: unrecognized punctuation symbol "/"

• When you try to use this RESTORE DATAFILE 4; from RMAN client, and the neither the Tablespace which it belongs nor the datafile is offline then the RMAN will give you the following error:
  

RMAN-03002: failure of restore command at 01/29/2017 13:02:21
ORA-19870: error while restoring backup piece /oracle_11204/oracle/rman/dbacode/DBACODE_29117_0brr8nh5_1_1.bak
ORA-19573: cannot obtain exclusive enqueue for datafile 4