linux利用文件句柄恢复oracle数据

linux利用文件句柄恢复数据

实用于数据刚刚删除，db依然是running状态，文件句柄还没有释放，并且数据文件所在磁盘未进行大文件写入即磁盘上的数据还没有被擦写

实验准备:
模拟数据文件删除

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE
SQL> set line 200
SQL> col name for a60
SQL> select FILE#,STATUS,TS#,NAME from v$datafile;

     FILE# STATUS         TS# NAME
---------- ------- ---------- ------------------------------------------------------------
         1 SYSTEM           0 /oracle/app/oracle/oradata/orcl/system01.dbf
         2 ONLINE           1 /oracle/app/oracle/oradata/orcl/sysaux01.dbf
         3 ONLINE           2 /oracle/app/oracle/oradata/orcl/undotbs01.dbf
         4 ONLINE           4 /oracle/app/oracle/oradata/orcl/users01.dbf
         5 ONLINE           6 /oracle/app/oracle/oradata/orcl/example01.dbf

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE

6 rows selected.

删除user
SQL> host rm -rf /oracle/app/oracle/oradata/orcl/users01.dbf

数据文件删除后，db状态需要是运行状态，否则文件句柄释放，无法使用该方法恢复，若磁盘已经多次擦写也可能无法恢复
查看db状态

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> 

SQL> select FILE#,STATUS,TS#,NAME from v$datafile;

     FILE# STATUS         TS# NAME
---------- ------- ---------- ------------------------------------------------------------
         1 SYSTEM           0 /oracle/app/oracle/oradata/orcl/system01.dbf
         2 ONLINE           1 /oracle/app/oracle/oradata/orcl/sysaux01.dbf
         3 ONLINE           2 /oracle/app/oracle/oradata/orcl/undotbs01.dbf
         4 ONLINE           4 /oracle/app/oracle/oradata/orcl/users01.dbf
         5 ONLINE           6 /oracle/app/oracle/oradata/orcl/example01.dbf
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE

6 rows selected.

对比之前的信息发现，db仍然在运行，并且数据文件和表空间的状态都是online的状态

创建一个表放入users表空间，看看是否能够执行成功

SQL> create table testfile tablespace users as select * from dba_tablespaces;
create table testfile tablespace users as select * from dba_tablespaces
                                                        *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oracle/app/oracle/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

提示找不到/oracle/app/oracle/oradata/orcl/users01.dbf数据文件，说明故障模拟成功

尝试恢复，当然这是不可能直接恢复的，或者使用有恢复目录