oracle 误删除数据文件恢复

测试目的：数据库开启状态下，执行物理删除数据文件 ，然后执行恢复。（仅测试环境下，练习使用，生产数据库误操作）

测试环境:Centos7.6 +Oracle 11.2.0.4 

 

登陆数据库查询数据文件位置

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

[root@oraback ~]# su - oracle Last login: Thu Jun 30 00:09:55 EDT 2022 on pts/1 [oracle@oraback ~]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 30 01:31:31 2022   Copyright (c) 1982, 2013, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options   SQL> set lines 200 SQL> column file_name format a50 SQL> select file_name,tablespace_name from dba_data_files;   FILE_NAME                                          TABLESPACE_NAME -------------------------------------------------- ------------------------------ /u01/app/oradata/racdg/users01.dbf                 USERS /u01/app/oradata/racdg/undotbs01.dbf               UNDOTBS1 /u01/app/oradata/racdg/sysaux01.dbf                SYSAUX /u01/app/oradata/racdg/system01.dbf                SYSTEM /u01/app/oradata/racdg/t_data.dbf                  T_DATA /u01/app/oradata/racdg/t_data02.dbf                T_DATA /u01/app/oradata/racdg/t_data03.dbf                T_DATA /u01/app/oradata/racdg/t_data04.dbf                T_DATA   8 rows selected.   SQL>

　执行删除

 

?

1 2 3 4 5 6 7 8 9

[oracle@oraback ~]$ rm -rf /u01/app/oradata/racdg/*.dbf [oracle@oraback ~]$ [oracle@oraback ~]$ ls -l /u01/app/oradata/racdg/ total 163228 -rw-r----- 1 oracle oinstall  9846784 Jun 30 01:37 control01.ctl -rw-r----- 1 oracle oinstall 52429312 Jun 29 23:23 redo01.log -rw-r----- 1 oracle oinstall 52429312 Jun 29 23:23 redo02.log -rw-r----- 1 oracle oinstall 52429312 Jun 30 01:36 redo03.log [oracle@oraback ~]$

　　此时数据库已经已经不可使用，仅可使用查询

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

SQL> create table t_test   2  as   3  select * from dba_users; select * from dba_users               * ERROR at line 3: ORA-01116: error in opening database file 4 ORA-01110: data file 4: '/u01/app/oradata/racdg/users01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3     SQL> select username from dba_users where rownum <10;   USERNAME ------------------------------ SYS SYSTEM WX TEST TEST2 OUTLN MGMT_VIEW FLOWS_FILES MDSYS   9 rows selected.   SQL> create table t_test (   2  i_nu number,   3  i_str varchar2(100)   4  )   5  /   Table created.   SQL> insert into t_test values(1,'str'); insert into t_test values(1,'str')             * ERROR at line 1: ORA-01116: error in opening database file 4 ORA-01110: data file 4: '/u01/app/oradata/racdg/users01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3     SQL>

　

linux 下查找进程 ora_dbw0

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

[oracle@oraback ~]$ ps -ef | grep ora_dbw oracle    3731     1  0 Jun29 ?        00:00:01 ora_dbw0_racdg oracle   10231  6244  0 01:27 pts/1    00:00:00 grep --color=auto ora_dbw [oracle@oraback ~]$ cd /proc/3731/fd [oracle@oraback fd]$ ll total 0 lr-x------ 1 oracle oinstall 64 Jun 30 01:27 0 -> /dev/null l-wx------ 1 oracle oinstall 64 Jun 30 01:27 1 -> /dev/null lrwx------ 1 oracle oinstall 64 Jun 30 01:27 10 -> /u01/app/oracle/product/11.2.0/db_1/dbs/lkRACDG lr-x------ 1 oracle oinstall 64 Jun 30 01:27 11 -> /u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb l-wx------ 1 oracle oinstall 64 Jun 30 01:27 2 -> /dev/null lrwx------ 1 oracle oinstall 64 Jun 30 01:27 256 -> /u01/app/oradata/racdg/control01.ctl lrwx------ 1 oracle oinstall 64 Jun 30 01:27 257 -> /u01/app/oracle/fast_recovery_area/racdg/control02.ctl lrwx------ 1 oracle oinstall 64 Jun 30 01:27 258 -> /u01/app/oradata/racdg/system01.dbf (deleted) lrwx------ 1 oracle oinstall 64 Jun 30 01:27 259 -> /u01/app/oradata/racdg/sysaux01.dbf (deleted) lrwx------ 1 oracle oinstall 64 Jun 30 01:27 260 -> /u01/app/oradata/racdg/undotbs01.dbf (deleted) lrwx------ 1 oracle oinstall 64 Jun 30 01:27 261 -> /u01/app/oradata/racdg/users01.dbf (deleted) lrwx------ 1 oracle oinstall 64 Jun 30 01:27 262 -> /u01/app/oradata/racdg/t_data.dbf (deleted) lrwx------ 1 oracle oinstall 64 Jun 30 01:27 263 -> /u01/app/oradata/racdg/t_data02.dbf (deleted) lrwx------ 1 oracle oinstall 64 Jun 30 01:27 264 -> /u01/app/oradata/racdg/t_data03.dbf (deleted) lrwx------ 1 oracle oinstall 64 Jun 30 01:27 265 -> /u01/app/oradata/racdg/t_data04.dbf (deleted) lrwx------ 1 oracle oinstall 64 Jun 30 01:27 266 -> /u01/app/oradata/racdg/temp01.dbf (deleted) lr-x------ 1 oracle oinstall 64 Jun 30 01:27 3 -> /dev/null lr-x------ 1 oracle oinstall 64 Jun 30 01:27 4 -> /dev/null lr-x------ 1 oracle oinstall 64 Jun 30 01:27 5 -> /dev/null lr-x------ 1 oracle oinstall 64 Jun 30 01:27 6 -> /u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb lr-x------ 1 oracle oinstall 64 Jun 30 01:27 7 -> /proc/3731/fd lr-x------ 1 oracle oinstall 64 Jun 30 01:27 8 -> /dev/zero lrwx------ 1 oracle oinstall 64 Jun 30 01:27 9 -> /u01/app/oracle/product/11.2.0/db_1/dbs/hc_racdg.dat [oracle@oraback fd]$

　　

 执行恢复编辑命令如下：

 

cp 258 /u01/app/oradata/racdg/system01.dbf
cp 259 /u01/app/oradata/racdg/sysaux01.dbf
cp 260 /u01/app/oradata/racdg/undotbs01.dbf
cp 261 /u01/app/oradata/racdg/users01.dbf
cp 262 /u01/app/oradata/racdg/t_data.dbf
cp 263 /u01/app/oradata/racdg/t_data02.dbf
cp 264 /u01/app/oradata/racdg/t_data03.dbf
cp 265 /u01/app/oradata/racdg/t_data04.dbf
cp 266 /u01/app/oradata/racdg/temp01.dbf

切换到/proc/3731/fd目录下执行

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

[oracle@oraback fd]$ cp 258  /u01/app/oradata/racdg/system01.dbf cp 259  /u01/app/oradata/racdg/sysaux01.dbf cp 260  /u01/app/oradata/racdg/undotbs01.dbf cp  261  /u01/app/oradata/racdg/users01.dbf cp 262  /u01/app/oradata/racdg/t_data.dbf cp  263  /u01/app/oradata/racdg/t_data02.dbf cp  264  /u01/app/oradata/racdg/t_data03.dbf cp 265  /u01/app/oradata/racdg/t_data04.dbf cp 266  /u01/app/oradata/racdg/temp01.dbf [oracle@oraback fd]$ cp 259  /u01/app/oradata/racdg/sysaux01.dbf [oracle@oraback fd]$ cp 260  /u01/app/oradata/racdg/undotbs01.dbf [oracle@oraback fd]$ cp  261  /u01/app/oradata/racdg/users01.dbf [oracle@oraback fd]$ cp 262  /u01/app/oradata/racdg/t_data.dbf [oracle@oraback fd]$ cp  263  /u01/app/oradata/racdg/t_data02.dbf [oracle@oraback fd]$ cp  264  /u01/app/oradata/racdg/t_data03.dbf [oracle@oraback fd]$ cp 265  /u01/app/oradata/racdg/t_data04.dbf [oracle@oraback fd]$ cp 266  /u01/app/oradata/racdg/temp01.dbf [oracle@oraback fd]$ ls -lh  /u01/app/oradata/racdg/ total 3.5G -rw-r----- 1 oracle oinstall 9.4M Jun 30 01:54 control01.ctl -rw-r----- 1 oracle oinstall  51M Jun 29 23:23 redo01.log -rw-r----- 1 oracle oinstall  51M Jun 29 23:23 redo02.log -rw-r----- 1 oracle oinstall  51M Jun 30 01:53 redo03.log -rw-r----- 1 oracle oinstall 531M Jun 30 01:53 sysaux01.dbf -rw-r----- 1 oracle oinstall 751M Jun 30 01:53 system01.dbf -rw-r----- 1 oracle oinstall 501M Jun 30 01:53 t_data02.dbf -rw-r----- 1 oracle oinstall 501M Jun 30 01:53 t_data03.dbf -rw-r----- 1 oracle oinstall 501M Jun 30 01:53 t_data04.dbf -rw-r----- 1 oracle oinstall 501M Jun 30 01:53 t_data.dbf -rw-r----- 1 oracle oinstall  30M Jun 30 01:53 temp01.dbf -rw-r----- 1 oracle oinstall  76M Jun 30 01:53 undotbs01.dbf -rw-r----- 1 oracle oinstall  61M Jun 30 01:53 users01.dbf [oracle@oraback fd]$

　　

恢复完毕之后，此时进行表数据插入已经显示正常（红色为恢复后执行的插入，前面的报错的之前执行的）

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

SQL> create table t_test (   2  i_nu number,   3  i_str varchar2(100)   4  )   5  /   Table created.   SQL> insert into t_test (1,'str'); insert into t_test (1,'str')                     * ERROR at line 1: ORA-00928: missing SELECT keyword     SQL> insert into t_test values(1,'str'); insert into t_test values(1,'str')             * ERROR at line 1: ORA-01116: error in opening database file 4 ORA-01110: data file 4: '/u01/app/oradata/racdg/users01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3     SQL> l   1* insert into t_test values(1,'str') SQL> /   1 row created.   SQL> /   1 row created.   SQL> /   1 row created.   SQL> commit;   Commit complete.   SQL>

　　

重新启动数据库（生产数据库禁用。仅供测试使用）

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

SQL> startup force ORA-01031: insufficient privileges SQL> show user USER is "WX" SQL> conn / as sysdba Connected. SQL> startup force ORACLE instance started.   Total System Global Area 1586708480 bytes Fixed Size                  2253624 bytes Variable Size             973081800 bytes Database Buffers          603979776 bytes Redo Buffers                7393280 bytes Database mounted. ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [], [], [], [], [], [], [], [], []     SQL> RECOVER DATABASE ; Media recovery complete. SQL> ALTER DATABASE OPEN;   Database altered.   SQL> SELECT  STATUS FROM V$INSTANCE;   STATUS ------------ OPEN   SQL>

　　

至此，数据库误删除数据文件恢复完毕。误删除数据文件后，不要停止数据库实例。