文档课题:rm -rf误删数据库数据文件、控制文件、临时文件的恢复演练.
系统:AnolisOS 7.9
数据库:oracle 11.2.0.4
1、环境准备
1.1、建库
[root@liujun ~]# mkdir -p /u02/app/oracle/oradata/
[root@liujun ~]# mkdir -p /u02/app/oracle/flash_recovery_area
[root@liujun ~]# chown -R oracle:oinstall /u02
[oracle@liujun ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc \
-gdbname oratest \
-sid oratest \
-sysPassword oracle_4U \
-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
11% complete
18% complete
26% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
57% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/oratest/oratest.log" for further details.
1.2、数据库信息
SQL>select 'datafile' file_type,file#,name FILE_NAME,status,enabled from v$datafile
union all
select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile
union all
select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile
union all
select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile

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> show parameter name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
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 /u02/app/oracle/archivelog
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
1.3、数据准备
SQL> create table obj as select * from dba_objects;

Table created.

SQL> insert into obj select * from obj;

86953 rows created.

SQL> select count(*) from obj;

COUNT(*)
----------
173906
说明:暂不提交,测试数据是否能恢复.
1.4、删数据
模拟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
查看数据库状态,显示正常.
[oracle@liujun trace]$ ps -ef|grep ora_smon
oracle 74135 1 0 15:32 ? 00:00:00 ora_smon_oratest
oracle 74937 16092 0 15:44 pts/5 00:00:00 grep --color=auto ora_smon
2、恢复
2.1、后台进程
数据库在未关闭的情况下,所有的进程都会存在,以下ora开头的都是数据库后台进程.
[oracle@liujun trace]$ ps -ef | grep ora_
oracle 74110 1 0 15:32 ? 00:00:00 ora_pmon_oratest
oracle 74112 1 0 15:32 ? 00:00:00 ora_psp0_oratest
oracle 74115 1 0 15:32 ? 00:00:08 ora_vktm_oratest
oracle 74119 1 0 15:32 ? 00:00:00 ora_gen0_oratest
oracle 74121 1 0 15:32 ? 00:00:00 ora_diag_oratest
oracle 74123 1 0 15:32 ? 00:00:00 ora_dbrm_oratest
oracle 74125 1 0 15:32 ? 00:00:00 ora_dia0_oratest
oracle 74127 1 0 15:32 ? 00:00:00 ora_mman_oratest
oracle 74129 1 0 15:32 ? 00:00:00 ora_dbw0_oratest
oracle 74131 1 0 15:32 ? 00:00:00 ora_lgwr_oratest
oracle 74133 1 0 15:32 ? 00:00:00 ora_ckpt_oratest
oracle 74135 1 0 15:32 ? 00:00:00 ora_smon_oratest
oracle 74137 1 0 15:32 ? 00:00:00 ora_reco_oratest
oracle 74139 1 0 15:32 ? 00:00:00 ora_mmon_oratest
oracle 74141 1 0 15:32 ? 00:00:00 ora_mmnl_oratest
oracle 74143 1 0 15:32 ? 00:00:00 ora_d000_oratest
oracle 74145 1 0 15:32 ? 00:00:00 ora_s000_oratest
oracle 74243 1 0 15:34 ? 00:00:00 ora_arc0_oratest
oracle 74246 1 0 15:34 ? 00:00:00 ora_arc1_oratest
oracle 74248 1 0 15:34 ? 00:00:00 ora_arc2_oratest
oracle 74250 1 0 15:34 ? 00:00:00 ora_arc3_oratest
oracle 74252 1 0 15:34 ? 00:00:00 ora_qmnc_oratest
oracle 74266 1 0 15:34 ? 00:00:00 ora_cjq0_oratest
oracle 74276 1 0 15:34 ? 00:00:00 ora_q000_oratest
oracle 74278 1 0 15:34 ? 00:00:00 ora_q001_oratest
oracle 74586 1 0 15:39 ? 00:00:00 ora_smco_oratest
oracle 74590 1 0 15:39 ? 00:00:00 ora_w000_oratest
oracle 74941 1 0 15:44 ? 00:00:00 ora_w001_oratest
oracle 75163 16092 0 15:48 pts/5 00:00:00 grep --color=auto ora_
2.2、句柄目录
根据进程判断需要恢复文件句柄所在目录.
[oracle@liujun trace]$ ps -ef | grep ora_lgwr
oracle 74131 1 0 15:32 ? 00:00:00 ora_lgwr_oratest
oracle 75295 16092 0 15:50 pts/5 00:00:00 grep --color=auto ora_lgwr
由此可知被删除的文件句柄在/proc/74131/fd目录下.
[oracle@liujun fd]$ pwd
/proc/74131/fd
[oracle@liujun fd]$ ll
total 0
lr-x------ 1 oracle oinstall 64 Sep 11 15:51 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 Sep 11 15:51 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 10 -> /u01/app/oracle/product/11.2.0/db_1/dbs/lkORATEST
lr-x------ 1 oracle oinstall 64 Sep 11 15:51 11 -> /u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 Sep 11 15:51 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 256 -> /u02/app/oracle/oradata/oratest/control01.ctl (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 257 -> /u02/app/oracle/flash_recovery_area/oratest/control02.ctl (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 258 -> /u02/app/oracle/oradata/oratest/redo01.log (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 259 -> /u02/app/oracle/oradata/oratest/redo02.log (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 260 -> /u02/app/oracle/oradata/oratest/redo03.log (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 261 -> /u02/app/oracle/oradata/oratest/system01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 262 -> /u02/app/oracle/oradata/oratest/sysaux01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 263 -> /u02/app/oracle/oradata/oratest/undotbs01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 264 -> /u02/app/oracle/oradata/oratest/users01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 265 -> /u02/app/oracle/oradata/oratest/example01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 266 -> /u02/app/oracle/oradata/oratest/temp01.dbf (deleted)
lr-x------ 1 oracle oinstall 64 Sep 11 15:51 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 Sep 11 15:51 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 Sep 11 15:51 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 Sep 11 15:51 6 -> /u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 Sep 11 15:51 7 -> /proc/74131/fd
lr-x------ 1 oracle oinstall 64 Sep 11 15:51 8 -> /dev/zero
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 9 -> /u01/app/oracle/product/11.2.0/db_1/dbs/hc_oratest.dat
2.3、告警日志
以下为trace文件中的告警日志:
Sun Sep 11 15:42:44 2022
Errors in file /u01/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_m000_74782.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 /u01/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_m000_74782.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 /u01/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_m000_74782.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 /u01/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_m000_74782.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 /u01/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_m000_74782.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 /u01/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_m000_74782.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
Sun Sep 11 15:42:44 2022
Errors in file /u01/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_m001_74784.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
2.4、被删文件
[root@liujun fd]# ll | grep deleted
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 256 -> /u02/app/oracle/oradata/oratest/control01.ctl (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 257 -> /u02/app/oracle/flash_recovery_area/oratest/control02.ctl (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 258 -> /u02/app/oracle/oradata/oratest/redo01.log (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 259 -> /u02/app/oracle/oradata/oratest/redo02.log (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 260 -> /u02/app/oracle/oradata/oratest/redo03.log (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 261 -> /u02/app/oracle/oradata/oratest/system01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 262 -> /u02/app/oracle/oradata/oratest/sysaux01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 263 -> /u02/app/oracle/oradata/oratest/undotbs01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 264 -> /u02/app/oracle/oradata/oratest/users01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 265 -> /u02/app/oracle/oradata/oratest/example01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Sep 11 15:51 266 -> /u02/app/oracle/oradata/oratest/temp01.dbf (deleted)
2.5、恢复文件
256—266的文件末尾被标记为deleted,此为之前误删除文件,只要数据库未重启,进程就不会停止.此时可通过DBWn进程号目录中的文件句柄号对误删除的文件进行恢复.
由于权限问题,此处强烈建议oracle用户去执行cp命令.
[oracle@liujun fd]$ cp 256 /u02/app/oracle/oradata/oratest/control01.ctl
[oracle@liujun fd]$ cp 257 /u02/app/oracle/flash_recovery_area/oratest/control02.ctl
[oracle@liujun fd]$ cp 258 /u02/app/oracle/oradata/oratest/redo01.log
[oracle@liujun fd]$ cp 259 /u02/app/oracle/oradata/oratest/redo02.log
[oracle@liujun fd]$ cp 260 /u02/app/oracle/oradata/oratest/redo03.log
[oracle@liujun fd]$ cp 261 /u02/app/oracle/oradata/oratest/system01.dbf
[oracle@liujun fd]$ cp 262 /u02/app/oracle/oradata/oratest/sysaux01.dbf
[oracle@liujun fd]$ cp 263 /u02/app/oracle/oradata/oratest/undotbs01.dbf
[oracle@liujun fd]$ cp 264 /u02/app/oracle/oradata/oratest/users01.dbf
[oracle@liujun fd]$ cp 265 /u02/app/oracle/oradata/oratest/example01.dbf
[oracle@liujun fd]$ cp 266 /u02/app/oracle/oradata/oratest/temp01.dbf

注意:若是root cp出来的文件,oracle进程没有权限操作,需要改变所属主. 而且必须等全部数据文件恢复后才可以进行chown操作,因为一旦执行了该操作,原来的ora_进程会停止,稍不注意就会导致数据库不能使用.
[root@liujun fd]# chown -R oracle.oinstall /u02/app/oracle

可以看到文件已恢复.
[oracle@liujun fd]$ ll /u02/app/oracle/oradata/oratest/
total 1911276
-rw-r----- 1 oracle oinstall 9748480 Sep 11 16:06 control01.ctl
-rw-r----- 1 oracle oinstall 328343552 Sep 11 16:06 example01.dbf
-rw-r----- 1 oracle oinstall 52429312 Sep 11 16:06 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Sep 11 16:06 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Sep 11 16:06 redo03.log
-rw-r----- 1 oracle oinstall 524296192 Sep 11 16:07 sysaux01.dbf
-rw-r----- 1 oracle oinstall 807411712 Sep 11 16:06 system01.dbf
-rw-r----- 1 oracle oinstall 30416896 Sep 11 16:06 temp01.dbf
-rw-r----- 1 oracle oinstall 94380032 Sep 11 16:06 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Sep 11 16:06 users01.dbf
[oracle@liujun fd]$ ll /u02/app/oracle/flash_recovery_area/oratest/
total 9520
-rw-r----- 1 oracle oinstall 9748480 Sep 11 16:06 control02.ctl
重启数据库.
[oracle@liujun backup]$ ps -ef | grep ora_smon
oracle 74135 1 0 15:32 ? 00:00:00 ora_smon_oratest
oracle 76802 15581 0 16:12 pts/4 00:00:00 grep --color=auto ora_smon
[oracle@liujun backup]$ kill -9 74135
[oracle@liujun ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 11 16:13:29 2022

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 409194496 bytes
Fixed Size 2253744 bytes
Variable Size 301993040 bytes
Database Buffers 96468992 bytes
Redo Buffers 8478720 bytes
Database mounted.
Database opened.
3、验证
SQL> select INSTANCE_NAME,HOST_NAME,VERSION,to_char(startup_time,'yyyy-mm-dd hh24:mi:ss'),STATUS,ACTIVE_STATE,INSTANCE_ROLE,DATABASE_STATUS from gv$INSTANCE;

INSTANCE_NAME HOST_NAME VERSION TO_CHAR(STARTUP_TIM STATUS ACTIVE_ST INSTANCE_ROLE DATABASE_STATUS
---------------- --------------- ----------------- ------------------- ------------ --------- ------------------ -----------------
oratest liujun 11.2.0.4.0 2022-09-11 16:13:36 OPEN NORMAL PRIMARY_INSTANCE ACTIVE
SQL> ! ps -ef | grep ora_lgwr
oracle 76885 1 0 16:13 ? 00:00:00 ora_lgwr_oratest
oracle 77115 76846 0 16:16 pts/1 00:00:00 /bin/bash -c ps -ef |grep ora_lgwr
oracle 77117 77115 0 16:16 pts/1 00:00:00 grep ora_lgwr

SQL> select count(*) from obj;

COUNT(*)
----------
173906

说明:数据库重启后,数据未丢失.
再次查看目录.
[oracle@liujun trace]$ ll /proc/76885/fd
total 0
lr-x------ 1 oracle oinstall 64 Sep 11 16:20 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 Sep 11 16:20 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 Sep 11 16:20 10 -> /u01/app/oracle/product/11.2.0/db_1/dbs/lkORATEST
lr-x------ 1 oracle oinstall 64 Sep 11 16:20 11 -> /u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 Sep 11 16:20 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Sep 11 16:20 256 -> /u02/app/oracle/oradata/oratest/control01.ctl
lrwx------ 1 oracle oinstall 64 Sep 11 16:20 257 -> /u02/app/oracle/flash_recovery_area/oratest/control02.ctl
lrwx------ 1 oracle oinstall 64 Sep 11 16:20 258 -> /u02/app/oracle/oradata/oratest/redo01.log
lrwx------ 1 oracle oinstall 64 Sep 11 16:20 259 -> /u02/app/oracle/oradata/oratest/redo02.log
lrwx------ 1 oracle oinstall 64 Sep 11 16:20 260 -> /u02/app/oracle/oradata/oratest/redo03.log
lrwx------ 1 oracle oinstall 64 Sep 11 16:20 261 -> /u02/app/oracle/oradata/oratest/system01.dbf
lrwx------ 1 oracle oinstall 64 Sep 11 16:20 262 -> /u02/app/oracle/oradata/oratest/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Sep 11 16:20 263 -> /u02/app/oracle/oradata/oratest/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Sep 11 16:20 264 -> /u02/app/oracle/oradata/oratest/users01.dbf
lrwx------ 1 oracle oinstall 64 Sep 11 16:20 265 -> /u02/app/oracle/oradata/oratest/example01.dbf
lrwx------ 1 oracle oinstall 64 Sep 11 16:20 266 -> /u02/app/oracle/oradata/oratest/temp01.dbf
lr-x------ 1 oracle oinstall 64 Sep 11 16:20 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 Sep 11 16:20 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 Sep 11 16:20 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 Sep 11 16:20 6 -> /u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 Sep 11 16:20 7 -> /proc/76885/fd
lr-x------ 1 oracle oinstall 64 Sep 11 16:20 8 -> /dev/zero
lrwx------ 1 oracle oinstall 64 Sep 11 16:20 9 -> /u01/app/oracle/product/11.2.0/db_1/dbs/hc_oratest.dat

总结:可以看到此时数据文件已恢复正常. 每次启动数据库实例后,进程都会在/proc目录下生成一个相应的以进程号命名的目录,存放操作中涉及到的文件句柄.
由于是在数据库开启的状态下进行的破坏操作,对于还没来得及写入的部分操作,数据会丢失,因为通过文件句柄号恢复出来的日志文件中,并不包含数据库的最新变更.
不过这也是一种恢复数据库的方法,所以当数据文件、控制文件、临时文件全被删除后,千万不要着急关数据库,否则无法恢复.

参考网址:http://blog.itpub.net/26736162/viewspace-1623938/