Recover accidentally deleted database data files in Linux using a handle

Recover accidentally deleted database data files in Linux using a handle
Environment: OS: Redhat EnterPrise 5.4
DB: Oracle EnterPrise Database 11gR2 (11.2.3.0)

 

When the database runs normally, the O & M personnel accidentally delete some data files. At this time, the Database Administrator does not know that the database runs normally and does not immediately throw errors or alarms; but when developers update a table, the ORA-01110 and ORA-27041 errors are reported in the deleted data file. Then the database administrator finds that a file has been deleted from the system level and the database has not been restarted. Therefore, the business has not been affected when no updates are made.

Fortunately, when the data file is deleted, the database has not been restarted, making the recovery easier. Based on this recovery, I retried it on my lab device and recorded the complete steps for deleting and recovering data files, as shown below:

 

1. Prepare the test environment and create new tablespaces and data files:

 

SQL> create tablespace woo datafile  2  '/u01/app/oracle/oradata/PROD1/woo01.dbf'  3  size 20m autoextend on;Tablespace created.SQL> select name from v$dbfile;NAME                                                                            --------------------------------------------------------------------------------/u01/app/oracle/oradata/PROD1/users01.dbf                                       /u01/app/oracle/oradata/PROD1/undotbs01.dbf                                     /u01/app/oracle/oradata/PROD1/sysaux01.dbf                                      /u01/app/oracle/oradata/PROD1/system01.dbf                                      /u01/app/oracle/oradata/PROD1/example01.dbf                                     /u01/app/oracle/oradata/PROD1/tools.dbf                                         /u01/app/oracle/oradata/PROD1/test.dbf                                          /u01/app/oracle/oradata/PROD1/woo01.dbf                                         8 rows selected.

2. simulate a fault and delete data files at the system level:

 

 

SQL> !rm -rf /u01/app/oracle/oradata/PROD1/woo01.dbf

3. Check the database status and create Test Data

 

 

SQL>! Tail-100f/u01/app/oracle/diag/rdbms/prod1/PROD1/trace/alert * Wed Aug 26 14:31:01 2015 create tablespace woo datafile '/u01/app/oracle/oradata /PROD1/woo01.dbf 'size 20 m autoextend onCompleted: create tablespace woo datafile '/u01/app/oracle/oradata/PROD1/woo01.dbf' size 20 m autoextend on # Here we can see that the database is not generated after the data file is deleted at the system level alarm. SQL> create table test tablespace woo as select * from dba_users; create table test tablespace woo as select * from dba_users * ERROR at line 1: ORA-01116: error in opening database file 8 ORA-01110: data file 8: '/u01/app/oracle/oradata/PROD1/woo01.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 # the data file is deleted only when we need to write data to the deleted file. SQL>! Tail-50/u01/app/oracle/diag/rdbms/prod1/PROD1/trace/alert * Wed Aug 26 14:34:33 2015 Errors in file/u01/app/oracle/diag/rdbms /prod1/PROD1/trace/PROD1_smon_9564.trc: ORA-01116: error in opening database file 8ORA-01110: data file 8: '/u01/app/oracle/oradata/PROD1/woo01.dbf' ORA-27041: unable to open fileLinux Error: 2: no such file or directoryAdditional information: 3Wed Aug 26 14:34:33 2015 Checker run found 1 new persistent data failuresWed Aug 26 14:39:44 2015 Errors in file/u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_m000_14015.trc: ORA-01116: error in opening database file 8ORA-01110: data file 8: '/u01/app/oracle/oradata/PROD1/woo01.dbf' ORA-27041: unable to open fileLinux Error: 2: no such file or directoryAdditional information: 3SQL> select instance_name, status from v $ instance; INSTANCE_NAME STATUS ---------------- ---------- PROD1 OPEN

# Even if the database already knows that the database file is lost because it is not the data file of the system tablespace, the operation of the database has not been affected.

4. Search for data files

 

Because the database does not stop running, we can use dbwr to write the data file process to find the Process Handle number, and enter the handle number to find the data file locked by the process.

 

SQL>! Ps-ef | grep dbw | grep-v greporacle 9554 1 0 07:16? 00:00:01 ora_dbw0_PROD1 # We can see that the process ID is 9554, and find all the files locked under the process through the process id SQL>! Ls-rtl/proc/9554/fd [oracle @ edbjr2p1 trace] $ ls-rtl/proc // 9554/fdtotal 0lr-x ------ 1 oracle oinstall 64 Aug 26 0->/dev /nulllr-x ------ 1 oracle oinstall 64 Aug 26 9->/dev/nulllr-x ------ 1 oracle oinstall 64 Aug 26 8->/u01/app/oracle/product /11.2.0/dbhome_1/bin/oraclelr-x ------ 1 oracle oinstall 64 Aug 26 7->/dev/nulllrwx ------ 1 oracle oinstall 64 Aug 26 6->/u01/app /oracle/product/11.2.0/dbhome_1/hpatch/orapatchPROD1.cfglrwx ------ 1 oracle oinstall 64 Aug 26 5->/u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_PROD1.datlr-x ------ 1 oracle oinstall 64 Aug 26 4->/dev/nulllr-x ------ 1 oracle oinstall 64 Aug 26 3->/dev/nulll-wx ------ 1 oracle oinstall 64 Aug 26 PM 2->/dev/nulllrwx ------ 1 oracle oinstall 64 Aug 26 PM 19->/u01/app/oracle/product/11.2.0/dbhome_1/hpatch/orapatchPROD1.cfglr-x ------ 1 oracle oinstall 64 Aug 26 18->/proc/9554/fdlr-x ------ 1 oracle oinstall 64 Aug 26 17->/u01/app/oracle/product/11.2.0/dbhome_1/ rdbms/mesg/oraus. msblrwx ------ 1 oracle oinstall 64 Aug 26 16->/u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_PROD1.datlr-x ------ 1 oracle oinstall 64 Aug 26 15->/ u01/app/oracle/product/11.2.0/dbhome_1/bin/oraclelr-x ------ 1 oracle oinstall 64 Aug 26 14->/dev/zerolr-x ------ 1 oracle oinstall 64 Aug 26 13->/dev/zerolrwx ------ 1 oracle oinstall 64 Aug 26 12->/u01/app/oracle/product/11.2.0/dbhome_1/hpatch/orapatchPROD1.cfglr-x ------ 1 oracle oinstall 64 Aug 26 11->/dev/nulllr-x ------ 1 oracle oinstall 64 Aug 26 10->/dev/nulll-wx ------ 1 oracle oinstall 64 Aug 26 1->/dev/nulllrwx ------ 1 oracle oinstall 64 Aug 26 268->/u01/app/oracle/oradata/PROD1/woo01.dbf (deleted) // We found this file, at deletedlrwx ------ 1 oracle oinstall 64 Aug 26 267->/u01/app/oracle/oradata/PROD1/temp2.dbflrwx ------ 1 oracle oinstall 64 Aug 26 266->/u01/app /oracle/oradata/PROD1/temp1.dbflrwx ------ 1 oracle oinstall 64 Aug 26 265->/u01/app/oracle/oradata/PROD1/temp01.dbflrwx ------ 1 oracle oinstall 64 Aug 26 264 ->/u01/app/oracle/oradata/PROD1/test. dbflrwx ------ 1 oracle oinstall 64 Aug 26 263->/u01/app/oracle/oradata/PROD1/tools. dbflrwx ------ 1 oracle oinstall 64 Aug 26 262->/u01/app/oracle/oradata/PROD1/example01.dbflrwx ------ 1 oracle oinstall 64 Aug 26 261->/u01/app/ oracle/oradata/PROD1/users01.dbflrwx ------ 1 oracle oinstall 64 Aug 26 260->/u01/app/oracle/oradata/PROD1/undotbs01.dbflrwx ------ 1 oracle oinstall 64 Aug 26 259- >/u01/app/oracle/oradata/PROD1/sysaux01.dbflrwx ------ 1 oracle oinstall 64 Aug 26 258->/u01/app/oracle/oradata/PROD1/system01.dbflrwx ------ 1 oracle oinstall 64 Aug 26 257->/u01/app/oracle/oradata/PROD1/control02.ctllrwx ------ 1 oracle oinstall 64 Aug 26 256->/u01/app/oracle/oradata/PROD1 /control01.ctllr-x ------ 1 oracle oinstall 64 Aug 26->/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus. msblrwx ------ 1 oracle oinstall 64 Aug 26 23->/u01/app/oracle/product/11.2.0/dbhome_1/dbs/lkPROD1lrwx ------ 1 oracle oinstall 64 Aug 26 22->/ u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_PROD1.datlr-x ------ 1 oracle oinstall 64 Aug 26 21->/u01/app/oracle/product/11.2.0/dbhome_1/bin /oraclelr-x ------ 1 oracle oinstall 64 Aug 26 20->/dev/zerolrwx ------ 1 oracle oinstall 64 Aug 26-> socket: [173283]

5. copy the file handle to the original file location:

 

 

SQL> !cp /proc//9554/fd/268 /u01/app/oracle/oradata/PROD1/woo01.dbf

6. Perform regular data recovery operations on the copied files, that is, update the scn Number of the data file header:

 

 

SQL> col name format a50SQL> select file #, status, name from v $ datafile; FILE # status name ---------- ------- running 1 SYSTEM/u01/app/oracle/oradata/PROD1/system01.dbf 2 ONLINE/u01/app/oracle/oradata/PROD1/sysaux01.dbf 3 ONLINE/u01/ app/oracle/oradata/PROD1/undotbs01.dbf 4 ONLINE/u01/app/oracle/oradata/PROD1/users01.dbf 5 ONLINE/u01/app/oracle/oradata/P ROD1/example01.dbf 6 ONLINE/u01/app/oracle/oradata/PROD1/tools. dbf 7 ONLINE/u01/app/oracle/oradata/PROD1/test. dbf 8 ONLINE/u01/app/oracle/oradata/PROD1/woo01.dbf # We can see that the data file is ONLINE 8 rows selected at this time. # Because this is an online database with other services, you cannot stop the machine at will. In this case, you can directly restore the data file offline online. SQL> alter database datafile 8 offline; Database altered. SQL> select file #, status, name from v $ datafile; FILE # status name ---------- ------- running 1 SYSTEM/u01/app/oracle/oradata/PROD1/system01.dbf 2 ONLINE/u01/app/oracle/oradata/PROD1/sysaux01.dbf 3 ONLINE/u01/ app/oracle/oradata/PROD1/undotbs01.dbf 4 ONLINE/u01/app/oracle/oradata/PROD1/users01.dbf 5 ONLINE/u01/app/oracle/oradata/PROD1/example01.dbf 6 ONLINE/u01/app/oracle/oradata/PROD1/tools. dbf 7 ONLINE/u01/app/oracle/oradata/PROD1/test. dbf 8 RECOVER/u01/app/oracle/oradata/PROD1/woo01.dbf # when operating the data file, this file is triggered and the scn of the file header is found to be inconsistent, the system prompts that a recovery is required. '8 rows selected. # perform online recovery and online the data file. SQL> recover datafile 8; Media recovery complete. SQL> alter database datafile 8 online; Database altered.

7. Verify that the data file can be used properly after recovery

 

SQL> select file#,status,name from v$datafile;     FILE# STATUS  NAME                                                         ---------- ------- --------------------------------------------------                    1 SYSTEM  /u01/app/oracle/oradata/PROD1/system01.dbf                            2 ONLINE  /u01/app/oracle/oradata/PROD1/sysaux01.dbf                            3 ONLINE  /u01/app/oracle/oradata/PROD1/undotbs01.dbf                           4 ONLINE  /u01/app/oracle/oradata/PROD1/users01.dbf                             5 ONLINE  /u01/app/oracle/oradata/PROD1/example01.dbf                           6 ONLINE  /u01/app/oracle/oradata/PROD1/tools.dbf                               7 ONLINE  /u01/app/oracle/oradata/PROD1/test.dbf                                8 ONLINE  /u01/app/oracle/oradata/PROD1/woo01.dbf                      8 rows selected.SQL> create table test tablespace woo as select * from dba_users;Table created.

8. Now the data file has been restored.