Oracle中rm -rf 误操作删除数据文件的恢复过程--数据库在无备份且open情况下的恢复

由小麦苗 · 2022年11月7日

0 150 1

Tags： Oracle

👉 本文共约12794个字，系统预计阅读时间或需49分钟。

本页目录 隐藏

1 登录**SQLPLUS**查看基本信息

2 模拟**rm -rf**误操作

3 开始恢复

3.1 判断句柄位置

3.2 恢复数据文件、控制文件、tmp文件和online log文件

3.3 如果数据库还没有关掉则关闭数据库

4 总结：

很多一定对深恶痛绝吧，没准哪天自己一个犯迷糊就把数据库给消灭了，然后，就没有然后了……那万一……真的发生了这样的不幸，是否真的就无药可救了吗？未必，还是有解决方法的，也许某天当你不幸遇到，就可以用来救自己了。这里做恢复操作的前提是没有可用的备份，或者数据库冷备份等，也就是说，没有任何备份

1 登录SQLPLUS查看基本信息

先创建一个测试库，在测试库上来练习：

[oracle@orcltest ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname oratest -sid oratest -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination /u02/app/oracle/oradata/ -redoLogFileSize 50 -recoveryAreaDestination /u02/app/oracle/flash_recovery_area -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -totalMemory 200 -databaseType OLTP -emConfiguration NONE -automaticMemoryManagement true

Copying database files

1% complete

3% complete

10% complete

17% complete

24% complete

35% complete

Creating and starting Oracle instance

37% complete

42% complete

47% complete

52% complete

53% complete

56% complete

58% complete

Registering database with Oracle Restart

64% complete

Completing Database Creation

68% complete

71% complete

75% complete

85% complete

96% complete

100% complete

Look at the log file "/u02/app/oracle/cfgtoollogs/dbca/oratest/oratest.log" for further details.

[oracle@orcltest ~]$ ORACLE_SID=oratest

[oracle@orcltest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue May 5 13:42:00 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter name

NAME TYPE VALUE

db_file_name_convert string

db_name string oratest

db_unique_name string oratest

global_names boolean FALSE

instance_name string oratest

lock_name_space string

log_file_name_convert string

processor_group_name string

service_names string oratest

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 4

Next log sequence to archive 6

Current log sequence 6

SQL>

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL> set line 9999 pagesize 9999

SQL> col FILE_NAME format a60

SQL> select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile

2 union all

3 select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile

4 union all

5 select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile

6 union all

7 select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile

8 ;

FILE_TYPE FILE# FILE_NAME STATUS ENABLED

datafile 1 /u02/app/oracle/oradata/oratest/system01.dbf SYSTEM READ WRITE

datafile 2 /u02/app/oracle/oradata/oratest/sysaux01.dbf ONLINE READ WRITE

datafile 3 /u02/app/oracle/oradata/oratest/undotbs01.dbf ONLINE READ WRITE

datafile 4 /u02/app/oracle/oradata/oratest/users01.dbf ONLINE READ WRITE

datafile 5 /u02/app/oracle/oradata/oratest/example01.dbf ONLINE READ WRITE

tempfile 1 /u02/app/oracle/oradata/oratest/temp01.dbf ONLINE READ WRITE

logfile 3 /u02/app/oracle/oradata/oratest/redo03.log

logfile 2 /u02/app/oracle/oradata/oratest/redo02.log

logfile 1 /u02/app/oracle/oradata/oratest/redo01.log

controlfile /u02/app/oracle/oradata/oratest/control01.ctl

controlfile /u02/app/oracle/flash_recovery_area/oratest/control02.ctl

11 rows selected.

SQL>

SQL> create table aa as select * from dba_objects;

Table created.

SQL> insert into aa select * from aa;

75203 rows created.

SQL>

SQL> select count(1) from aa;

COUNT(1)

----------

150406

SQL>

这里不提交，，，我们看看数据是否可以恢复。

2 模拟rm -rf误操作

SQL> ! rm -rf /u02/app/oracle/oradata/oratest/*

SQL> ! rm -rf /u02/app/oracle/flash_recovery_area/oratest/*

SQL> ! ls -l /u02/app/oracle/oradata/oratest/*

ls: cannot access /u02/app/oracle/oradata/oratest/*: No such file or directory

SQL>

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@orcltest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue May 5 13:58:54 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from dba_objects;

COUNT(*)

----------

75202

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL> select open_mode from v$database;

select open_mode from v$database

ERROR at line 1:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u02/app/oracle/oradata/oratest/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> create table aa as select * from dba_objects;

create table aa as select * from dba_objects

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01116: error in opening database file 1

ORA-01110: data file 1: '/u02/app/oracle/oradata/oratest/system01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL>

由于数据文件都被删除，其中包括，是存放数据字典的容器，想要再访问数据字典中得视图，当然是不可能的了，所以这里会报错，找不到文件，故障出现

--查看日志文件

Tue May 05 14:04:05 2015

Errors in file /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_m001_30851.trc:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u02/app/oracle/oradata/oratest/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

3 开始恢复

3.1 判断句柄位置

其实这个时候，所有的进程都还在，都是以开头的都是的后台进程：

SQL> ! ps -ef|grep ora_

oracle 31843 1 0 14:41 ? 00:00:00 ora_pmon_oratest

oracle 31845 1 0 14:41 ? 00:00:00 ora_psp0_oratest

oracle 31847 1 0 14:41 ? 00:00:00 ora_vktm_oratest

oracle 31851 1 0 14:41 ? 00:00:00 ora_gen0_oratest

oracle 31853 1 0 14:41 ? 00:00:00 ora_diag_oratest

oracle 31855 1 0 14:41 ? 00:00:00 ora_dbrm_oratest

oracle 31857 1 0 14:41 ? 00:00:00 ora_dia0_oratest

oracle 31859 1 0 14:41 ? 00:00:00 ora_mman_oratest

oracle 31861 1 0 14:41 ? 00:00:00 ora_dbw0_oratest

oracle 31863 1 0 14:41 ? 00:00:00 ora_lgwr_oratest

oracle 31865 1 0 14:41 ? 00:00:00 ora_ckpt_oratest

oracle 31867 1 0 14:41 ? 00:00:00 ora_smon_oratest

oracle 31869 1 0 14:41 ? 00:00:00 ora_reco_oratest

oracle 31871 1 0 14:41 ? 00:00:00 ora_mmon_oratest

oracle 31873 1 0 14:41 ? 00:00:00 ora_mmnl_oratest

oracle 31875 1 0 14:41 ? 00:00:00 ora_d000_oratest

oracle 31877 1 0 14:41 ? 00:00:00 ora_s000_oratest

oracle 31927 1 0 14:41 ? 00:00:00 ora_arc0_oratest

oracle 31935 1 0 14:41 ? 00:00:00 ora_arc1_oratest

oracle 31937 1 0 14:41 ? 00:00:00 ora_arc2_oratest

oracle 31939 1 0 14:41 ? 00:00:00 ora_arc3_oratest

oracle 31941 1 0 14:41 ? 00:00:00 ora_qmnc_oratest

oracle 31957 1 0 14:41 ? 00:00:00 ora_cjq0_oratest

oracle 31967 1 0 14:42 ? 00:00:00 ora_q000_oratest

oracle 31969 1 0 14:42 ? 00:00:00 ora_q001_oratest

oracle 31976 1 0 14:45 ? 00:00:00 ora_smco_oratest

oracle 31978 1 0 14:46 ? 00:00:00 ora_w000_oratest

oracle 32013 1 0 14:50 ? 00:00:00 ora_w001_oratest

oracle 32063 31989 0 14:57 pts/4 00:00:00 /bin/bash -c ps -ef|grep ora_

oracle 32065 32063 0 14:57 pts/4 00:00:00 grep ora_

SQL>

--查看进程，判断需要恢复文件句柄所在目录

SQL> !ps -ef|grep ora_lgwr

oracle 31863 1 0 14:41 ? 00:00:00 ora_lgwr_oratest

oracle 31995 31989 0 14:48 pts/4 00:00:00 /bin/bash -c ps -ef|grep ora_lgwr

oracle 31997 31995 0 14:48 pts/4 00:00:00 grep ora_lgwr

SQL>

由此可知，我们需要的被删除的文件句柄在/proc/31863/fd下

此时，告警日志：Tue May 05 14:09:05 2015

Errors in file /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_m000_30871.trc:

ORA-01116: error in opening database file 1

ORA-01110: data file 1: '/u02/app/oracle/oradata/oratest/system01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_m000_30871.trc:

ORA-01116: error in opening database file 2

ORA-01110: data file 2: '/u02/app/oracle/oradata/oratest/sysaux01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_m000_30871.trc:

ORA-01116: error in opening database file 4

ORA-01110: data file 4: '/u02/app/oracle/oradata/oratest/users01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_m000_30871.trc:

ORA-01116: error in opening database file 5

ORA-01110: data file 5: '/u02/app/oracle/oradata/oratest/example01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_m000_30871.trc:

ORA-01116: error in opening database file 201

ORA-01110: data file 201: '/u02/app/oracle/oradata/oratest/temp01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_m000_30871.trc:

ORA-01116: error in opening database file 3

ORA-01110: data file 3: '/u02/app/oracle/oradata/oratest/undotbs01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Tue May 05 14:09:05 2015

Errors in file /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_m001_30873.trc:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u02/app/oracle/oradata/oratest/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

3.2 恢复数据文件、控制文件、tmp文件和online log文件

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@orcltest ~]$ cd /proc/31863/fd

-bash: cd: /proc/30335/fd: Permission denied

[oracle@orcltest ~]$ cd /proc/31863/

[oracle@orcltest 30335]$ su - root

Password:

[root@orcltest ~]# cd /proc/31863/fd

[root@orcltest fd]# ll

total 0

lr-x------ 1 oracle oinstall 64 May 5 14:48 0 -> /dev/null

l-wx------ 1 oracle oinstall 64 May 5 14:48 1 -> /dev/null

lr-x------ 1 oracle oinstall 64 May 5 14:48 10 -> /dev/zero

lr-x------ 1 oracle oinstall 64 May 5 14:48 11 -> /dev/zero

lrwx------ 1 oracle oinstall 64 May 5 14:48 12 -> /u02/app/oracle/product/11.2.0/dbhome_1/dbs/hc_oratest.dat

lr-x------ 1 oracle oinstall 64 May 5 14:48 13 -> /u02/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb

lr-x------ 1 oracle oinstall 64 May 5 14:48 14 -> /proc/31863/fd

lr-x------ 1 oracle oinstall 64 May 5 14:48 15 -> /dev/zero

lrwx------ 1 oracle oinstall 64 May 5 14:48 16 -> /u02/app/oracle/product/11.2.0/dbhome_1/dbs/hc_oratest.dat

lrwx------ 1 oracle oinstall 64 May 5 14:48 17 -> /u02/app/oracle/product/11.2.0/dbhome_1/dbs/lkORATEST

lr-x------ 1 oracle oinstall 64 May 5 14:48 18 -> /u02/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb

l-wx------ 1 oracle oinstall 64 May 5 14:48 2 -> /dev/null

lrwx------ 1 oracle oinstall 64 May 5 14:48 256 -> /u02/app/oracle/oradata/oratest/control01.ctl (deleted)

lrwx------ 1 oracle oinstall 64 May 5 14:48 257 -> /u02/app/oracle/flash_recovery_area/oratest/control02.ctl (deleted)

lrwx------ 1 oracle oinstall 64 May 5 14:48 258 -> /u02/app/oracle/oradata/oratest/redo01.log (deleted)

lrwx------ 1 oracle oinstall 64 May 5 14:48 259 -> /u02/app/oracle/oradata/oratest/redo02.log (deleted)

lrwx------ 1 oracle oinstall 64 May 5 14:48 260 -> /u02/app/oracle/oradata/oratest/redo03.log (deleted)

lrwx------ 1 oracle oinstall 64 May 5 14:48 261 -> /u02/app/oracle/oradata/oratest/system01.dbf (deleted)

lrwx------ 1 oracle oinstall 64 May 5 14:48 262 -> /u02/app/oracle/oradata/oratest/sysaux01.dbf (deleted)

lrwx------ 1 oracle oinstall 64 May 5 14:48 263 -> /u02/app/oracle/oradata/oratest/undotbs01.dbf (deleted)

lrwx------ 1 oracle oinstall 64 May 5 14:48 264 -> /u02/app/oracle/oradata/oratest/users01.dbf (deleted)

lrwx------ 1 oracle oinstall 64 May 5 14:48 265 -> /u02/app/oracle/oradata/oratest/example01.dbf (deleted)

lrwx------ 1 oracle oinstall 64 May 5 14:48 266 -> /u02/app/oracle/oradata/oratest/temp01.dbf (deleted)

lr-x------ 1 oracle oinstall 64 May 5 14:48 3 -> /dev/null

lr-x------ 1 oracle oinstall 64 May 5 14:48 4 -> /dev/null

lrwx------ 1 oracle oinstall 64 May 5 14:48 5 -> /u02/app/oracle/product/11.2.0/dbhome_1/dbs/hc_oratest.dat

lr-x------ 1 oracle oinstall 64 May 5 14:48 6 -> /dev/null

lr-x------ 1 oracle oinstall 64 May 5 14:48 7 -> /dev/null

lr-x------ 1 oracle oinstall 64 May 5 14:48 8 -> /dev/null

lr-x------ 1 oracle oinstall 64 May 5 14:48 9 -> /dev/null

[root@orcltest fd]#

[root@orcltest fd]# ll | grep deleted