Use a linux File handle to restore accidentally deleted Oracle data files

Use the linux File handle to restore the Oracle Data File test environment accidentally deleted: OEL + Oracle 11.2.0.1 first check the current data file and Data File status:

SQL> select file_id,tablespace_name,file_name,status from dba_data_files;   FILE_ID TABLESPACE FILE_NAME                              STATUS---------- ---------- -------------------------------------- ---------         4 USERS      /oradata/orcl2/users01.dbf             AVAILABLE         3 UNDOTBS1   /oradata/orcl2/undotbs01.dbf           AVAILABLE         2 SYSAUX     /oradata/orcl2/sysaux01.dbf            AVAILABLE         1 SYSTEM     /oradata/orcl2/system01.dbf            AVAILABLE         6 BOOK       /oradata/orcl2/book.dbf                AVAILABLE         5 SG         /oradata/orcl2/SG01.dbf                AVAILABLESQL> select file#,status,name from v$datafile;     FILE# STATUS  NAME---------- ------- ----------------------------------------         1 SYSTEM  /oradata/orcl2/system01.dbf         2 ONLINE  /oradata/orcl2/sysaux01.dbf         3 ONLINE  /oradata/orcl2/undotbs01.dbf         4 ONLINE  /oradata/orcl2/users01.dbf         5 ONLINE  /oradata/orcl2/SG01.dbf         6 ONLINE  /oradata/orcl2/book.dbf

 

Prepare to delete the data file/oradata/orcl2/SG01.dbf from rm and restore it using the Linux handle. Remember not to shutdown the instance after the deletion (it does not matter after shutdown, it will only make recovery more troublesome); Delete the data file/oradata/orcl2/SG01.dbf

[oracle@DBA2 orcl2]$ rm SG01.dbf[oracle@DBA2 orcl2]$

 

View the file handle and find that the/oradata/orcl2/SG01.dbf status is (delete), as shown in red:

[oracle@DBA2 orcl2]$ ps -ef|grep dbw|grep -v grep|awk '{print $2}'3140

 

PS: 3140 is the pid of the DBWn process. The DBWn process references this handle to operate data files. In fact, it can also use other Oracle processes such as LGWR. It is not necessary to use the pid of the DBWn process;

[oracle@DBA2 orcl2]$ cd /proc/3140/fd[oracle@DBA2 fd]$ ls -altotal 0dr-x------ 2 oracle oinstall  0 Sep  4 09:28 .dr-xr-xr-x 7 oracle oinstall  0 Sep  4 09:28 ..lr-x------ 1 oracle oinstall 64 Sep  4 09:41 0 -> /dev/nulll-wx------ 1 oracle oinstall 64 Sep  4 09:41 1 -> /dev/nulll-wx------ 1 oracle oinstall 64 Sep  4 09:41 10 -> /u/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_3056.trcl-wx------ 1 oracle oinstall 64 Sep  4 09:41 11 -> /u/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_3056.trmlr-x------ 1 oracle oinstall 64 Sep  4 09:41 12 -> /u/oracle/product/11.2/rdbms/mesg/oraus.msblr-x------ 1 oracle oinstall 64 Sep  4 09:41 13 -> /dev/zerolr-x------ 1 oracle oinstall 64 Sep  4 09:41 14 -> /proc/3140/fdlr-x------ 1 oracle oinstall 64 Sep  4 09:41 15 -> /dev/zerolrwx------ 1 oracle oinstall 64 Sep  4 09:41 16 -> /u/oracle/product/11.2/dbs/hc_orcl2.datlrwx------ 1 oracle oinstall 64 Sep  4 09:41 17 -> /u/oracle/product/11.2/dbs/lkORCL2lrwx------ 1 oracle oinstall 64 Sep  4 09:41 18 -> /oradata/orcl2/control01.ctllrwx------ 1 oracle oinstall 64 Sep  4 09:41 19 -> /u/oracle/flash_recovery_area/orcl2/control02.ctll-wx------ 1 oracle oinstall 64 Sep  4 09:41 2 -> /dev/nulllrwx------ 1 oracle oinstall 64 Sep  4 09:41 20 -> /oradata/orcl2/system01.dbflrwx------ 1 oracle oinstall 64 Sep  4 09:41 21 -> /oradata/orcl2/sysaux01.dbflrwx------ 1 oracle oinstall 64 Sep  4 09:41 22 -> /oradata/orcl2/undotbs01.dbflrwx------ 1 oracle oinstall 64 Sep  4 09:41 23 -> /oradata/orcl2/users01.dbflrwx------ 1 oracle oinstall 64 Sep  4 09:41 24 -> /oradata/orcl2/SG01.dbf (deleted)lrwx------ 1 oracle oinstall 64 Sep  4 09:41 25 -> /oradata/orcl2/book.dbflrwx------ 1 oracle oinstall 64 Sep  4 09:41 26 -> /oradata/orcl2/temp01.dbflrwx------ 1 oracle oinstall 64 Sep  4 09:41 27 -> /oradata/orcl2/temp02.dbflrwx------ 1 oracle oinstall 64 Sep  4 09:41 28 -> /oradata/orcl2/temo01.dbflr-x------ 1 oracle oinstall 64 Sep  4 09:41 29 -> /u/oracle/product/11.2/rdbms/mesg/oraus.msbl-wx------ 1 oracle oinstall 64 Sep  4 09:41 3 -> /u/oracle/product/11.2/rdbms/log/orcl2_ora_3056.trclr-x------ 1 oracle oinstall 64 Sep  4 09:41 4 -> /dev/nulllr-x------ 1 oracle oinstall 64 Sep  4 09:41 5 -> /dev/nulllr-x------ 1 oracle oinstall 64 Sep  4 09:41 6 -> /dev/nulllrwx------ 1 oracle oinstall 64 Sep  4 09:41 7 -> /u/oracle/product/11.2/dbs/hc_orcl2.datlrwx------ 1 oracle oinstall 64 Sep  4 09:41 8 -> /u/oracle/product/11.2/dbs/lkinstorcl2 (deleted)lr-x------ 1 oracle oinstall 64 Sep  4 09:41 9 -> /proc/3140/fd

 

Cp to a new location:

[oracle@DBA2 fd]$ cp 24 /oradata/SG.dbf

 

Restore data files

SQL> select file#,status,name from v$datafile;     FILE# STATUS  NAME---------- ------- --------------------------------------         1 SYSTEM  /oradata/orcl2/system01.dbf         2 ONLINE  /oradata/orcl2/sysaux01.dbf         3 ONLINE  /oradata/orcl2/undotbs01.dbf         4 ONLINE  /oradata/orcl2/users01.dbf         5 ONLINE  /oradata/orcl2/SG01.dbf         6 ONLINE  /oradata/orcl2/book.dbf

 

Offline data file:

SQL> alter database datafile 5 offline;Database altered.SQL> select file#,status,name from v$datafile;     FILE# STATUS  NAME---------- ------- --------------------------------------         1 SYSTEM  /oradata/orcl2/system01.dbf         2 ONLINE  /oradata/orcl2/sysaux01.dbf         3 ONLINE  /oradata/orcl2/undotbs01.dbf         4 ONLINE  /oradata/orcl2/users01.dbf         5 RECOVER /oradata/orcl2/SG01.dbf         6 ONLINE  /oradata/orcl2/book.dbf

 

Rename:

SQL> alter database rename file '/oradata/orcl2/SG01.dbf' to '/oradata/SG.dbf';Database altered.

 

At this time, the data file status is still in the RECOVER state. You need to restore it:

SQL> recover datafile 5;Media recovery complete.SQL> select file#,status,name from v$datafile;     FILE# STATUS  NAME---------- ------- --------------------------------------         1 SYSTEM  /oradata/orcl2/system01.dbf         2 ONLINE  /oradata/orcl2/sysaux01.dbf         3 ONLINE  /oradata/orcl2/undotbs01.dbf         4 ONLINE  /oradata/orcl2/users01.dbf         5 OFFLINE /oradata/SG.dbf         6 ONLINE  /oradata/orcl2/book.dbf

 

Now the data file is online:

SQL> alter database datafile 5 online;Database altered.SQL> select file#,status,name from v$datafile;     FILE# STATUS  NAME---------- ------- --------------------------------------         1 SYSTEM  /oradata/orcl2/system01.dbf         2 ONLINE  /oradata/orcl2/sysaux01.dbf         3 ONLINE  /oradata/orcl2/undotbs01.dbf         4 ONLINE  /oradata/orcl2/users01.dbf         5 ONLINE  /oradata/SG.dbf         6 ONLINE  /oradata/orcl2/book.dbf

 

OK. Now, the data file accidentally deleted is restored using the Linux handle and can be used from the new online!