Oracle linux下 rm 数据文件恢复测试详解

末蓝、 2022-07-12 04:20 134阅读 0赞

Oracle下rm 删除数据文件恢复

下面是整个测试步骤

1.在数据库open的时候，直接删除test表空间中的数据文件  
Last login: Tue Feb  7 10:24:27 2017 from 192.168.36.103  
\[root@oracle1 ~\]\# su - oracle  
ocrl:/home/oracle@oracle1>ps -ef |grep pmon  
oracle    6098     1  0 Feb07 ?        00:00:05 ora\_pmon\_ocrl  
oracle   18634 18603  0 11:34 pts/1    00:00:00 grep pmon  
ocrl:/home/oracle@oracle1>sqlplus  /  as sysdba  
  
SQL\*Plus: Release 12.1.0.2.0 Production on Fri Feb 10 11:36:15 2017  
  
Copyright (c) 1982, 2014, Oracle.  All rights reserved.  
  
  
Connected to:  
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production  
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
  
SQL> select name from v$datafile;  
  
NAME  
\--------------------------------------------------------------------------------  
/data/oradata/ocrl/datafile/system01.dbf  
/data/oradata/ocrl/datafile/sysaux01.dbf  
/data/oradata/ocrl/datafile/undotbs01.dbf  
/data/oradata/ocrl/datafile/users01.dbf  
/data/oradata/ocrl/datafile/test.dbf  
  
SQL> exit  
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production  
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
ocrl:/home/oracle@oracle1>cd /data/oradata/ocrl/datafile/  
ocrl:/data/oradata/ocrl/datafile@oracle1>ls  
sysaux01.dbf  system01.dbf  test.dbf  undotbs01.dbf  users01.dbf  
ocrl:/data/oradata/ocrl/datafile@oracle1>rm -rf test.dbf   
ocrl:/data/oradata/ocrl/datafile@oracle1>ls  
sysaux01.dbf  system01.dbf  undotbs01.dbf  users01.dbf  
ocrl:/data/oradata/ocrl/datafile@oracle1>sqlplus / as sysdba  
  
SQL\*Plus: Release 12.1.0.2.0 Production on Fri Feb 10 11:37:23 2017  
  
Copyright (c) 1982, 2014, Oracle.  All rights reserved.  
  
  
Connected to:  
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production  
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
2.尝试在tets表中间中创建表，开始报错  
SQL> create table t tablespace test as select \* from dual;  
create table t tablespace test as select \* from dual  
                                                \*  
ERROR at line 1:  
ORA-01116: error in opening database file 5  
ORA-01110: data file 5: '/data/oradata/ocrl/datafile/test.dbf'  
ORA-27041: unable to open file  
Linux-x86\_64 Error: 2: No such file or directory  
Additional information: 3  
  
  
SQL> exit  
3.检查dbwr的进程PID  
ocrl:/data/oradata/ocrl/datafile@oracle1>ps -ef|grep dbw0  
oracle    6121     1  0 Feb07 ?        00:00:32 ora\_dbw0\_ocrl  
oracle   19015 18603  0 11:52 pts/1    00:00:00 grep dbw0  
4.dbwr会打开所有数据文件的句柄，在proc目录中可以查到，目录是进程PID，fd表示文件描述符  
ocrl:/data/oradata/ocrl/datafile@oracle1>cd /proc/6121/fd  
ocrl:/proc/6121/fd@oracle1>ls -l  
total 0  
lr-x------ 1 oracle dba 64 Feb  7 10:11 0 -> /dev/null  
l-wx------ 1 oracle dba 64 Feb  7 10:11 1 -> /dev/null  
l-wx------ 1 oracle dba 64 Feb  7 10:11 2 -> /dev/null  
lrwx------ 1 oracle dba 64 Feb  7 10:11 256 -> /data/oradata/ocrl/control/control01.ctl  
lrwx------ 1 oracle dba 64 Feb  7 10:11 257 -> /data/oradata/ocrl/control/control02.ctl  
lrwx------ 1 oracle dba 64 Feb 10 11:42 258 -> /data/oradata/ocrl/datafile/system01.dbf  
lrwx------ 1 oracle dba 64 Feb 10 11:42 259 -> /data/oradata/ocrl/datafile/sysaux01.dbf  
lrwx------ 1 oracle dba 64 Feb 10 11:42 260 -> /data/oradata/ocrl/datafile/undotbs01.dbf  
lrwx------ 1 oracle dba 64 Feb 10 11:42 261 -> /data/oradata/ocrl/datafile/users01.dbf  
lrwx------ 1 oracle dba 64 Feb 10 11:42 262 -> /data/oradata/ocrl/datafile/test.dbf (deleted)  
lrwx------ 1 oracle dba 64 Feb 10 11:42 263 -> /data/oradata/ocrl/tempfile/temp01.dbf  
lr-x------ 1 oracle dba 64 Feb  7 10:11 3 -> /dev/null  
lr-x------ 1 oracle dba 64 Feb  7 10:11 4 -> /u01/app/oracle/product/12.1.0/db\_1/rdbms/mesg/oraus.msb  
lr-x------ 1 oracle dba 64 Feb  7 10:11 5 -> /proc/6121/fd  
lrwx------ 1 oracle dba 64 Feb  7 10:11 6 -> /u01/app/oracle/product/12.1.0/db\_1/dbs/hc\_ocrl.dat  
l-wx------ 1 oracle dba 64 Feb  7 10:11 7 -> /u01/app/oracle/product/12.1.0/db\_1/rdbms/mesg/oraus.msb

lrwx------ 1 oracle dba 64 Feb  7 10:11 9 -> /u01/app/oracle/product/12.1.0/db\_1/dbs/lkOCRL

注意：注意其中“/data/oradata/ocrl/datafile/test.dbf (deleted)”字样，表示该文件已经被删除  
5.直接cp该句柄文件名回原位置  
ocrl:/proc/6121/fd@oracle1>cp 262 /data/oradata/ocrl/datafile/test.dbf  
  
ocrl:/proc/6121/fd@oracle1>ls  
0  1  2  256  257  258  259  260  261  262  263  3  4  5  6  7  9  
ocrl:/proc/6121/fd@oracle1>cd /data/oradata/ocrl/tempfile/  
ocrl:/data/oradata/ocrl/tempfile@oracle1>cd /data/oradata/ocrl/datafile/  
ocrl:/data/oradata/ocrl/datafile@oracle1>ls  
sysaux01.dbf  system01.dbf  test.dbf  undotbs01.dbf  users01.dbf  
6.进行数据文件recover--在归档模式下  
ocrl:/data/oradata/ocrl/datafile@oracle1>sqlplus / as sysdba  
  
SQL\*Plus: Release 12.1.0.2.0 Production on Fri Feb 10 11:44:24 2017  
  
Copyright (c) 1982, 2014, Oracle.  All rights reserved.  
  
  
Connected to:  
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production  
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
  
SQL> alter database datafile 5 offline;  
  
Database altered.  
  
SQL> recover datafile 5;  
Media recovery complete.  
SQL> alter database datafile 5 online;  
  
Database altered.  
  
  
SQL> select file\#,name from v$datafile;  
  
     FILE\#  
\----------  
NAME  
\--------------------------------------------------------------------------------  
     1  
/data/oradata/ocrl/datafile/system01.dbf  
  
     2  
/data/oradata/ocrl/datafile/sysaux01.dbf  
  
     3  
/data/oradata/ocrl/datafile/undotbs01.dbf  
  
  
     FILE\#  
\----------  
NAME  
\--------------------------------------------------------------------------------  
     4  
/data/oradata/ocrl/datafile/users01.dbf  
  
     5  
/data/oradata/ocrl/datafile/test.dbf  
  
  
SQL> create table t tablespace test as select \* from dual;  
  
Table created.  
  
  
SQL> select \* from t;  
  
D  
\-  
X  
  
SQL> exit  
至此完成数据文件恢复  
在非归档模式下,需要shutdown后,进入mount模式,offline,recover,online,open  
恢复的原理是，在Linux操作系统中，如果文件从操作系统级别被rm掉，之前打开该文件的进程仍然持有相应的文件句柄，所指向的文件仍然可以读写，并且该文件的文件描述符可以从/proc目录中获得。但是要注意的是，此时如果关闭数据库，则此句柄会消失，那么除了扫描磁盘进行文件恢复之外就没有其它方法了，因此在数据库出现问题的时候，如果不确认情况的复杂程度，千万不要随便关闭数据库。重启数据库往往是没有意义的，甚至是致命的