接上文：http://m.sunline.cc/db/589259
環境描述：
OS：CentOS Linux release 7.6.1810 (Core)
DB：ORACLE 19.17
兩個數據庫實例分別為 orcl 和 yxxt
rm 的數據文件的實例沒有被關閉（也就是dbf的訪問具柄沒有釋放）。

[root@dsmart:/root]# ps -ef |grep ora_pmon|grep -v grep
oracle   10710     1  0 Dec20 ?        00:00:07 ora_pmon_orcl
oracle   11331     1  0 Dec20 ?        00:00:07 ora_pmon_yxxt

其中 orcl 實例的 /oradata/ORCL/tbs01.dbf被 rm掉，然后此數據文件又被創建到了 yxxt 實例。orcl 實例沒有重啟過。相應數據文件對應表空間的表仍然可以查詢數據。
查看 yxxt 實例的tbs 創建語句：

sys@YXXT 10:52:05> select dbms_metadata.get_ddl('TABLESPACE','TBS') ddl FROM dual;
DDL
--------------------------------------------------------
  CREATE TABLESPACE "TBS" DATAFILE
  '/oradata/ORCL/tbs01.dbf' SIZE 104857600
  AUTOEXTEND ON NEXT 1048576000 MAXSIZE
32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOA

查看 orcl 實例的 tbs 創建語句：

sys@ORCL 10:37:10> select dbms_metadata.get_ddl('TABLESPACE','TBS') ddl FROM dual;
DDL
------------------------------------------------------------
  CREATE TABLESPACE "TBS" DATAFILE
  '/oradata/ORCL/tbs01.dbf' SIZE 104857600
  AUTOEXTEND ON NEXT 1048576000 MAXSIZE
32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOA

可以看到 orcl 實例和yxxt 都有tbs的表空間和 /oradata/ORCL/tbs01.dbf 的數據文件。

sys@ORCL 11:00:51> select segment_name,segment_type,owner from dba_segments where tablespace_name='TBS';

SEGMENT_NAME	     SEGMENT_TYPE	OWNER
-------------------- ------------------ ---------------------------------
A		     TABLE		SYS
sys@ORCL 10:53:58> select count(1) from a;
  COUNT(1)
----------
   5242880
sys@YXXT 11:01:30>  select segment_name,segment_type,owner from dba_segments where tablespace_name='TBS';
SEGMENT_NAME	     SEGMENT_TYPE	OWNER
-------------------- ------------------ --------------------
Y		     TABLE		SYS
sys@YXXT 11:07:05> select count(1) from y;
  COUNT(1)
----------
   1048576

其中orcl 實例對應的 a 表有5242880行數據，yxxt 實例對應的 y 表有1048576 行數據。
通過lsof |grep delete命令查看刪除文件的信息，這里通過 grep 過濾 dbf 文件?？梢钥吹絫bs01.dbf 的狀態是 deleted，但是仍然有進城在訪問，句柄沒有釋放，這樣此dbf文件可以有效的恢復。

[root@dsmart:/root]# lsof |grep delete|grep dbf
ora_dbw0_ 10767               oracle  263uW     REG              253,1  104865792     917524 /oradata/ORCL/tbs01.dbf (deleted)
ora_ckpt_ 10775               oracle  261u      REG              253,1  104865792     917524 /oradata/ORCL/tbs01.dbf (deleted)
[root@dsmart:/root]# ps -ef |grep 10767|grep -v grep
oracle   10767     1  0 Dec20 ?        00:00:09 ora_dbw0_orcl
[root@dsmart:/root]# ps -ef |grep 10775|grep -v grep
oracle   10775     1  0 Dec20 ?        00:00:29 ora_ckpt_orcl

/oradata/ORCL/tbs01.dbf 的句柄被 orcl 實例的 ckpt和dbw進程占用。
由于目前 /oradata/ORCL/tbs01.dbf 文件目前被 yxxt 實例占有，這里將 yxxt 實例占有的dbf 重命名，如下操作：

sys@YXXT 11:26:50> alter tablespace tbs offline;
Tablespace altered.
sys@YXXT 11:26:56> !mv /oradata/ORCL/tbs01.dbf /oradata/ORCL/tbs01_yxxt.dbf
sys@YXXT 11:27:23>  alter tablespace tbs rename datafile '/oradata/ORCL/tbs01.dbf' to '/oradata/ORCL/tbs01_yxxt.dbf';
Tablespace altered.
sys@YXXT 11:28:14> alter tablespace tbs online;
Tablespace altered.
sys@YXXT 11:28:23>  select dbms_metadata.get_ddl('TABLESPACE','TBS') ddl FROM dual;
DDL
-----------------------------------------------
  CREATE TABLESPACE "TBS" DATAFILE
  '/oradata/ORCL/tbs01_yxxt.dbf' SIZE 10485
7600
  AUTOEXTEND ON NEXT 1048576000 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL
sys@YXXT 11:29:08>  select count(1) from y;
  COUNT(1)
----------
   1048576

將 yxxt 實例的表空間的 /oradata/ORCL/tbs01_yxxt.dbf rename 到/oradata/ORCL/tbs01_yxxt.dbf
這樣 yxxt 實例跟 /oradata/ORCL/tbs01.dbf 沒有任何關系了。目前yxxt實例是正常的，能夠正常查詢 tbs 表空間上的 y表，數據仍然是 1048576.
下面恢復 orcl 庫的 /oradata/ORCL/tbs01.dbf 文件

[oracle@dsmart:/home/oracle]$  lsof |grep delete|grep dbf
ora_dbw0_ 10767               oracle  263uW     REG              253,1  104865792    917524 /oradata/ORCL/tbs01.dbf (deleted)
ora_ckpt_ 10775               oracle  261u      REG              253,1  104865792    917524 /oradata/ORCL/tbs01.dbf (deleted)
[oracle@dsmart:/home/oracle]$  ps -ef |grep -E "10767|10775"|grep -v grep
oracle   10767     1  0 Dec20 ?        00:00:09 ora_dbw0_orcl
oracle   10775     1  0 Dec20 ?        00:00:30 ora_ckpt_orcl
[oracle@dsmart:/home/oracle]$

[oracle@dsmart:/proc/10767/fd]$ cd /proc/10767/fd
[oracle@dsmart:/proc/10767/fd]$ ls -l
total 0
lr-x------ 1 oracle oinstall 64 Dec 23 11:34 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 Dec 23 11:34 1 -> /dev/null
l-wx------ 1 oracle oinstall 64 Dec 23 11:34 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 256 -> /oradata/ORCL/control01.ctl
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 257 -> /oradata/ORCL/control02.ctl
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 258 -> /oradata/ORCL/system01.dbf
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 259 -> /oradata/ORCL/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 260 -> /oradata/ORCL/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 261 -> /oradata/ORCL/users01.dbf
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 262 -> /oradata/ORCL/temp01.dbf
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 263 -> /oradata/ORCL/tbs01.dbf (deleted)
lr-x------ 1 oracle oinstall 64 Dec 23 11:34 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 Dec 23 11:34 4 -> /u01/app/oracle/product/19.3.0/db/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 Dec 23 11:34 5 -> /proc/10767/fd
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 6 -> /u01/app/oracle/product/19.3.0/db/dbs/hc_orcl.dat
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 7 -> /u01/app/oracle/product/19.3.0/db/dbs/lkORCL
lr-x------ 1 oracle oinstall 64 Dec 23 11:34 8 -> /u01/app/oracle/product/19.3.0/db/rdbms/mesg/oraus.msb
[oracle@dsmart:/proc/10767/fd]$ cp 263  /tmp/263_tbs01.dbf
[oracle@dsmart:/proc/10767/fd]$ cd /proc/10775/fd
[oracle@dsmart:/proc/10775/fd]$ ls -l
total 0
lr-x------ 1 oracle oinstall 64 Dec 23 11:34 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 Dec 23 11:34 1 -> /dev/null
l-wx------ 1 oracle oinstall 64 Dec 23 11:34 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 256 -> /oradata/ORCL/control01.ctl
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 257 -> /oradata/ORCL/control02.ctl
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 258 -> /oradata/ORCL/system01.dbf
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 259 -> /oradata/ORCL/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 260 -> /oradata/ORCL/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 261 -> /oradata/ORCL/tbs01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 262 -> /oradata/ORCL/users01.dbf
lr-x------ 1 oracle oinstall 64 Dec 23 11:34 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 Dec 23 11:34 4 -> /u01/app/oracle/product/19.3.0/db/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 Dec 23 11:34 5 -> /proc/10775/fd
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 6 -> /u01/app/oracle/product/19.3.0/db/dbs/hc_orcl.dat
lrwx------ 1 oracle oinstall 64 Dec 23 11:34 7 -> /u01/app/oracle/product/19.3.0/db/dbs/lkORCL
lr-x------ 1 oracle oinstall 64 Dec 23 11:34 8 -> /u01/app/oracle/product/19.3.0/db/rdbms/mesg/oraus.msb
[oracle@dsmart:/proc/10775/fd]$ cp 261  /tmp/261-tbs01.dbf
[oracle@dsmart:/proc/10767/fd]$ cd /tmp/
[oracle@dsmart:/tmp]$ ls -l *tbs*
-rw-r----- 1 oracle oinstall 104865792 Dec 23 11:37 261-tbs01.dbf
-rw-r----- 1 oracle oinstall 104865792 Dec 23 11:37 263_tbs01.dbf
[oracle@dsmart:/tmp]$ md5sum 261-tbs01.dbf
99bf4495aef775392d53f741fbb88ad7  261-tbs01.dbf
[oracle@dsmart:/tmp]$ md5sum  263_tbs01.dbf
99bf4495aef775392d53f741fbb88ad7  263_tbs01.dbf
[oracle@dsmart:/tmp]$

可以看到倆進程復制出來的文件是同一個，md5都是一樣的。
保險起見把 a 表的數據exp出來

[oracle@dsmart:/home/oracle]$ exp

Export: Release 19.0.0.0.0 - Production on Fri Dec 23 15:11:18 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
Enter array fetch buffer size: 4096 >
Export file: expdat.dmp > a.dmp
(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > t
Export table data (yes/no): yes > yes
Compress extents (yes/no): yes > yes
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > a
. . exporting table                              A    5242880 rows exported
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >
Export terminated successfully with warnings.

由于是sys下的對象，這里通過exp交互的方式將sys.a 表dump出來。

[oracle@dsmart:/tmp]$ cp 263_tbs01.dbf  /oradata/ORCL/tbs01.dbf
[oracle@dsmart:/tmp]$ ll /oradata/ORCL/tbs01.dbf
-rw-r----- 1 oracle oinstall 104865792 Dec 23 15:15 /oradata/ORCL/tbs01.dbf
[oracle@dsmart:/tmp]$ s
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 23 15:15:21 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
sys@ORCL 15:15:22> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORCL 15:15:49> startup
ORACLE instance started.
Total System Global Area  973077208 bytes
Fixed Size		    8895192 bytes
Variable Size		  385875968 bytes
Database Buffers	  570425344 bytes
Redo Buffers		    7880704 bytes
Database mounted.
Database opened.
sys@ORCL 23-DEC-22>

將從 /proc/10767/fd.263 句柄里的文件復制到原來的位置，重啟數據庫，能夠正常啟動。
這樣 orcl 和yxxt 實例都恢復正常了。
總結：
這次 rm 數據文件的恢復，關鍵是orcl 實例沒有關閉數據庫，刪除的數據文件一直被進程訪問，這樣的話就可以通過以上方式進行恢復了。