oracle 误删除数据文件恢复

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

测试环境:Centos7.6 +Oracle 11.2.0.4 

 

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

 

[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> 

　执行删除

 

[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 ~]$ 

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

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

[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目录下执行

 

[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]$ 

　　

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

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> 

　　

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

 

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> 

　　

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