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

ゝ一世哀愁。 2022-06-16 10:59 185阅读 0赞

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数据文件，说明故障模拟成功

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

SQL> alter database datafile 4 offline;
    alter database datafile 4 offline
    *
    ERROR at line 1:
    ORA-01145: offline immediate disallowed unless media recovery enabled
    
    SQL> recover datafile 4;
    ORA-00283: recovery session canceled due to errors
    ORA-01124: cannot recover data file 4 - file is in use or recovery
    ORA-01110: data file 4: '/oracle/app/oracle/oradata/orcl/users01.dbf'

可以看到无法恢复

由于数据刚刚删除不久，可以在os层面尝试恢复

查看db的进程

[oracle@oratest ~]$ ps -ef | grep ora | grep dbw
    oracle    4658     1  0 06:51 ?        00:00:00 ora_dbw0_orcl
    oracle    4833  4434  0 07:04 pts/3    00:00:00 grep dbw
    [oracle@oratest ~]$ 
    which lsof
    /usr/bin/which: no lsof in (/oracle/app/oracle/product/11.2.0/dbhome_1/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin)
    
    我的测试主机上没有lsof命令，因此直接到proc进行查看，
    [oracle@oratest fd]$ cd /proc/4658/fd
    [oracle@oratest fd]$ ll
    total 0
    lr-x------ 1 oracle oinstall 64 May 14 07:06 0 -> /dev/null
    l-wx------ 1 oracle oinstall 64 May 14 07:06 1 -> /dev/null
    l-wx------ 1 oracle oinstall 64 May 14 07:06 10 -> /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4597.trc
    l-wx------ 1 oracle oinstall 64 May 14 07:06 11 -> /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4597.trm
    lr-x------ 1 oracle oinstall 64 May 14 07:06 12 -> /oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
    lr-x------ 1 oracle oinstall 64 May 14 07:06 13 -> /dev/zero
    lr-x------ 1 oracle oinstall 64 May 14 07:06 14 -> /proc/4658/fd
    lr-x------ 1 oracle oinstall 64 May 14 07:06 15 -> /dev/zero
    lrwx------ 1 oracle oinstall 64 May 14 07:06 16 -> /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_orcl.dat
    lrwx------ 1 oracle oinstall 64 May 14 07:06 17 -> /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/lkORCL
    lrwx------ 1 oracle oinstall 64 May 14 07:06 18 -> /oracle/app/oracle/oradata/orcl/control01.ctl
    lrwx------ 1 oracle oinstall 64 May 14 07:06 19 -> /oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
    l-wx------ 1 oracle oinstall 64 May 14 07:06 2 -> /dev/null
    lrwx------ 1 oracle oinstall 64 May 14 07:06 20 -> /oracle/app/oracle/oradata/orcl/system01.dbf
    lrwx------ 1 oracle oinstall 64 May 14 07:06 21 -> /oracle/app/oracle/oradata/orcl/sysaux01.dbf
    lrwx------ 1 oracle oinstall 64 May 14 07:06 22 -> /oracle/app/oracle/oradata/orcl/undotbs01.dbf
    lrwx------ 1 oracle oinstall 64 May 14 07:06 23 -> /oracle/app/oracle/oradata/orcl/users01.dbf (deleted)
    lrwx------ 1 oracle oinstall 64 May 14 07:06 24 -> /oracle/app/oracle/oradata/orcl/example01.dbf
    lrwx------ 1 oracle oinstall 64 May 14 07:06 25 -> /oracle/app/oracle/oradata/orcl/temp01.dbf
    lr-x------ 1 oracle oinstall 64 May 14 07:06 26 -> /oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
    l-wx------ 1 oracle oinstall 64 May 14 07:06 3 -> /oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/log/orcl_ora_4597.trc
    lr-x------ 1 oracle oinstall 64 May 14 07:06 4 -> /dev/null
    lr-x------ 1 oracle oinstall 64 May 14 07:06 5 -> /dev/null
    lr-x------ 1 oracle oinstall 64 May 14 07:06 6 -> /dev/null
    lrwx------ 1 oracle oinstall 64 May 14 07:06 7 -> /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_orcl.dat
    lrwx------ 1 oracle oinstall 64 May 14 07:06 8 -> /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/lkinstorcl (deleted)
    lr-x------ 1 oracle oinstall 64 May 14 07:06 9 -> /proc/4658/fd
    [oracle@oratest fd]$

查看到23

lrwx------ 1 oracle oinstall 64 May 14 07:06 23 -> /oracle/app/oracle/oradata/orcl/users01.dbf (deleted)

恢复到原来的目录

[oracle@oratest fd]$ cp 23 /oracle/app/oracle/oradata/orcl/users01.dbf
    [oracle@oratest fd]$ 
    [oracle@oratest fd]$ 
    [oracle@oratest fd]$ ll /oracle/app/oracle/oradata/orcl/users01.dbf
    -rw-r----- 1 oracle oinstall 5251072 May 14 07:07 /oracle/app/oracle/oradata/orcl/users01.dbf
    [oracle@oratest fd]$

os恢复到目录之后，在db层面

SQL> create table testfile tablespace users as select * from dba_tablespaces;
    
    Table created.
    
    SQL>

可以创建成功了  
有时候需要进行恢复，恢复过程如下：

SQL> alter database datafile 4 offline;
    alter database datafile 4 offline
    *
    ERROR at line 1:
    ORA-01145: offline immediate disallowed unless media recovery enabled
    
    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.
    
    SQL> 
    SQL> 
    SQL> alter tablespace USERS offline;
    
    Tablespace altered.
    
    SQL> 
    
    
    SQL> set line 200 
    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 OFFLINE          4 /oracle/app/oracle/oradata/orcl/users01.dbf
             5 ONLINE           6 /oracle/app/oracle/oradata/orcl/example01.dbf
    
    SQL> recover datafile 4;
    Media recovery complete.
    SQL> alter database datafile 4 online;
    
    Database altered.
    
    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                          OFFLINE
    EXAMPLE                        ONLINE
    
    6 rows selected.
    
    #置为在线
    
    SQL> alter tablespace users online;
    
    Tablespace altered.
    
    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.
    
    SQL> 
    SQL> create table testfile tablespace users as select * from dba_tablespaces;
    
    Table created.
    SQL>

恢复完成