合 Oracle中rm -rf 误操作删除数据文件的恢复过程--数据库在无备份且open情况下的恢复
Tags： Oracle
很多一定对深恶痛绝吧，没准哪天自己一个犯迷糊就把数据库给消灭了，然后，就没有然后了……那万一……真的发生了这样的不幸，是否真的就无药可救了吗？未必，还是有解决方法的，也许某天当你不幸遇到，就可以用来救自己了。这里做恢复操作的前提是没有可用的备份，或者数据库冷备份等，也就是说，没有任何备份
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
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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
