【RMAN】rm -rf 误操作的恢复过程
----数据库在无备份且open情况下的恢复
本文地址URL: http://blog.itpub.net/26736162/viewspace-1623938/
很多一定对深恶痛绝吧,没准哪天自己一个犯迷糊就把数据库给消灭了,然后,就没有然后了……那万一……真的发生了这样的不幸,是否真的就无药可救了吗?未必,还是有解决方法的,也许某天当你不幸遇到,就可以用来救自己了。这里做恢复操作的前提是没有可用的备份,或者数据库冷备份等,也就是说,没有任何备份
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
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
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)
[root@orcltest fd]#
256266的文件末尾被标记,这是由刚才的操作所导致的,误删除后只要数据库未重启,进程就不会停止,那么就可以通过 DBWn进程号目录中的文件句柄号,来对这些被的文件进行恢复,方法就是文件句柄到原路径,注意一点这里如果不是在目录,那就要用绝对路径来指定文件句柄,如果删除文件后就,又对数据库进行了关闭操作,那就无解了,只能想想了。
如果采用软件来作为终端查看的话,可以看到这几个的文件是一直闪动的,截了张图:
执行恢复,执行如下脚本:cp 256 /u02/app/oracle/oradata/oratest/control01.ctl cp 257 /u02/app/oracle/flash_recovery_area/oratest/control02.ctl cp 258 /u02/app/oracle/oradata/oratest/redo01.log cp 259 /u02/app/oracle/oradata/oratest/redo02.log cp 260 /u02/app/oracle/oradata/oratest/redo03.log cp 261 /u02/app/oracle/oradata/oratest/system01.dbf cp 262 /u02/app/oracle/oradata/oratest/sysaux01.dbf cp 263 /u02/app/oracle/oradata/oratest/undotbs01.dbf cp 264 /u02/app/oracle/oradata/oratest/users01.dbf cp 265 /u02/app/oracle/oradata/oratest/example01.dbf cp 266 /u02/app/oracle/oradata/oratest/temp01.dbf
注意,这里一定要注意权限问题,要用oracle用户去cp,如果用root去cp出来的文件,oracle进程是没有权限操作的,当然,你也可以在用root恢复完后,再chown一下:
[root@ora10g fd]# chown oracle.oinstall /u01/app/oracle -R
但是一定要注意,必须等全部数据文件恢复后才可以做chown操作,因为一旦执行了该操作,原来的ora_进程会停止!!!你再也无法恢复丢失的数据了!!!这也是为什么推荐用oralce用户来cp的原因,有的时候oracle的权限不足,就只能使用root来copy了。
[root@orcltest fd]# cp 256 /u02/app/oracle/oradata/oratest/control01.ctl
[root@orcltest fd]# cp 257 /u02/app/oracle/flash_recovery_area/oratest/control02.ctl
[root@orcltest fd]# cp 258 /u02/app/oracle/oradata/oratest/redo01.log
[root@orcltest fd]# cp 259 /u02/app/oracle/oradata/oratest/redo02.log
[root@orcltest fd]# cp 260 /u02/app/oracle/oradata/oratest/redo03.log
[root@orcltest fd]# cp 261 /u02/app/oracle/oradata/oratest/system01.dbf
[root@orcltest fd]# cp 262 /u02/app/oracle/oradata/oratest/sysaux01.dbf
[root@orcltest fd]# cp 263 /u02/app/oracle/oradata/oratest/undotbs01.dbf
[root@orcltest fd]# cp 264 /u02/app/oracle/oradata/oratest/users01.dbf
[root@orcltest fd]# cp 265 /u02/app/oracle/oradata/oratest/example01.dbf
[root@orcltest fd]# cp 266 /u02/app/oracle/oradata/oratest/temp01.dbf
[root@orcltest fd]# chown oracle.oinstall /u02/app/oracle/oradata/oratest/*
[root@orcltest fd]# chown oracle.oinstall /u02/app/oracle/flash_recovery_area/oratest/control02.ctl
对文件进行恢复以后,直接查询目录下的文件状态,依然可以看到是的,但是没关系,实际上文件已经恢复成功了
[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:53 19 -> /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_lgwr_31863.trc
l-wx------ 1 oracle oinstall 64 May 5 14:48 2 -> /dev/null
l-wx------ 1 oracle oinstall 64 May 5 14:53 20 -> /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_lgwr_31863.trm
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]#
此时,不用管了,只要我们保证所有的文件以及到制定位置就可以了,下边关闭数据库即可。
3.3 如果数据库还没有关掉则关闭数据库
[oracle@orcltest fd]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue May 5 15:01: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> archive log list;
ORA-03113: end-of-file on communication channel
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 fd]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue May 5 15:03:40 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> set line 9999
SQL> col HOST_NAME format a10
SQL> select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS,ACTIVE_STATE,INSTANCE_ROLE,DATABASE_STATUS from gv$INSTANCE;
INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS ACTIVE_ST INSTANCE_ROLE DATABASE_STATUS
---------------- ---------- ----------------- ------------------- ------------ --------- ------------------ -----------------
oratest orcltest 11.2.0.3.0 2015-05-05 15:03:11 OPEN NORMAL PRIMARY_INSTANCE ACTIVE
SQL> select sysdate from dual;
SYSDATE
-------------------
2015-05-05 15:06:48
SQL>
SQL> ! ps -ef|grep ora_lgwr_
oracle 32173 1 0 15:03 ? 00:00:00 ora_lgwr_oratest
oracle 32285 32274 0 15:09 pts/4 00:00:00 /bin/bash -c ps -ef|grep ora_lgwr_
oracle 32287 32285 0 15:09 pts/4 00:00:00 grep ora_lgwr_
SQL> select count(1) from aa;
COUNT(1)
----------
150406
SQL>
数据库自动重启了,而且数据也没有丢失,如果没有重启,我们可以手动重启一下,我们再次查看目录。
[root@orcltest ~]# cd /proc/32173/fd
[root@orcltest fd]# ll
total 0
lr-x------ 1 root root 64 May 5 15:10 0 -> /dev/null
l-wx------ 1 root root 64 May 5 15:10 1 -> /dev/null
lr-x------ 1 root root 64 May 5 15:10 10 -> /dev/null
lrwx------ 1 root root 64 May 5 15:10 11 -> /u02/app/oracle/product/11.2.0/dbhome_1/dbs/hc_oratest.dat
lr-x------ 1 root root 64 May 5 15:10 12 -> /dev/null
lr-x------ 1 root root 64 May 5 15:10 13 -> /dev/null
lr-x------ 1 root root 64 May 5 15:10 14 -> /dev/null
lr-x------ 1 root root 64 May 5 15:10 15 -> /dev/null
lr-x------ 1 root root 64 May 5 15:10 16 -> /dev/zero
lr-x------ 1 root root 64 May 5 15:10 17 -> /u01/app/11.2.0/grid/dbs/hc_+ASM.dat
l-wx------ 1 root root 64 May 5 15:10 18 -> /u01/app/11.2.0/grid/log/orcltest/alertorcltest.log
lr-x------ 1 root root 64 May 5 15:10 19 -> /dev/zero
l-wx------ 1 root root 64 May 5 15:10 2 -> /dev/null
lrwx------ 1 root root 64 May 5 15:10 20 -> /u02/app/oracle/product/11.2.0/dbhome_1/dbs/hc_oratest.dat
lr-x------ 1 root root 64 May 5 15:10 21 -> /u02/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
lr-x------ 1 root root 64 May 5 15:10 22 -> /proc/32173/fd
lr-x------ 1 root root 64 May 5 15:10 23 -> /dev/zero
lrwx------ 1 root root 64 May 5 15:10 24 -> /u02/app/oracle/product/11.2.0/dbhome_1/dbs/hc_oratest.dat
lrwx------ 1 root root 64 May 5 15:10 25 -> /u02/app/oracle/product/11.2.0/dbhome_1/dbs/lkORATEST
lrwx------ 1 root root 64 May 5 15:10 256 -> /u02/app/oracle/oradata/oratest/control01.ctl
lrwx------ 1 root root 64 May 5 15:10 257 -> /u02/app/oracle/flash_recovery_area/oratest/control02.ctl
lrwx------ 1 root root 64 May 5 15:10 258 -> /u02/app/oracle/oradata/oratest/redo01.log
lrwx------ 1 root root 64 May 5 15:10 259 -> /u02/app/oracle/oradata/oratest/redo02.log
lr-x------ 1 root root 64 May 5 15:10 26 -> /u02/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
lrwx------ 1 root root 64 May 5 15:10 260 -> /u02/app/oracle/oradata/oratest/redo03.log
lrwx------ 1 root root 64 May 5 15:10 261 -> /u02/app/oracle/oradata/oratest/system01.dbf
lrwx------ 1 root root 64 May 5 15:10 262 -> /u02/app/oracle/oradata/oratest/sysaux01.dbf
lrwx------ 1 root root 64 May 5 15:10 263 -> /u02/app/oracle/oradata/oratest/undotbs01.dbf
lrwx------ 1 root root 64 May 5 15:10 264 -> /u02/app/oracle/oradata/oratest/users01.dbf
lrwx------ 1 root root 64 May 5 15:10 265 -> /u02/app/oracle/oradata/oratest/example01.dbf
lrwx------ 1 root root 64 May 5 15:10 266 -> /u02/app/oracle/oradata/oratest/temp01.dbf
lrwx------ 1 root root 64 May 5 15:10 3 -> /u01/app/11.2.0/grid/log/orcltest/agent/ohasd/oraagent_grid/oraagent_gridOUT.log
l-wx------ 1 root root 64 May 5 15:10 4 -> /u01/app/11.2.0/grid/log/orcltest/agent/ohasd/oraagent_grid/oraagent_grid.log
lr-x------ 1 root root 64 May 5 15:10 5 -> /dev/null
lrwx------ 1 root root 64 May 5 15:10 6 -> socket:[25060]
lrwx------ 1 root root 64 May 5 15:10 7 -> socket:[25061]
lrwx------ 1 root root 64 May 5 15:10 8 -> socket:[25062]
lrwx------ 1 root root 64 May 5 15:10 9 -> socket:[25063]
[root@orcltest fd]#
[root@orcltest ~]# cd /proc/31863/fd
-bash: cd: /proc/31863/fd: No such file or directory
此时数据文件都已经正常了,每次启动数据库实例后,进程都会再下生成一个相应的以进程号命名的目录,存放操作中涉及到的文件句柄,此时进程对应的目录已经变为,而原来的目录已经不存在了
4 总结:
当我们进行操作系统命令的时候,切忌不可随意加参数,就算一定要用,也要确定再三后才能执行,否则对于数据库而言,可以说是灾难性的。这里只是测试了一下删除下的全部文件,试想一下,如果你当初执行的是呢?可能情况就要更加复杂一点了,恢复需要的步骤也就更多了。
由于是在数据库状态下直接进行了破坏性操作,对于还来不及写入的那部分操作,肯定是会丢失的,因为我们通过文件句柄号恢复出来的日志文件中,并不一定包含数据库的最新变更,即便如此,对于误操作的恢复,还是有一定意义的,至少可以在你没有任何备份的情况下,多提供了一根救命稻草来拯救你的数据库,再次强调一下,后,千万不要着急地关闭数据库重启!!!否则就等着哭吧!!!
5 其他相关文章链接
RMAN相关连接:
【RMAN】利用备份片还原数据库(上): http://blog.itpub.net/26736162/viewspace-1621581/
【RMAN】利用备份片还原数据库(中):http://blog.itpub.net/26736162/viewspace-1621661/
【RMAN】利用备份片还原数据库(下):http://blog.itpub.net/26736162/viewspace-1621672/
【RMAN】利用备份片还原数据库(中)-附加 :http://blog.itpub.net/26736162/viewspace-1621938/
Rman跨版本恢复:
关于10g的跨小版本恢复参考:http://blog.chinaunix.net/uid-26736162-id-4942816.html
关于11g的跨小版本恢复参考:http://blog.itpub.net/26736162/viewspace-1565655/
关于在不同版本和平台之间进行还原或复制的常见问题 :http://blog.itpub.net/26736162/viewspace-1549041/
RMAN跨版本恢复(下)--大版本异机恢复: http://blog.itpub.net/26736162/viewspace-1562583/
undo表空间的恢复:
undo表空间文件丢失恢复(1)--有备份的情况下恢复:http://blog.itpub.net/26736162/viewspace-1458654/
undo表空间文件丢失恢复(2)--无备份有redo的情况下恢复:http://blog.itpub.net/26736162/viewspace-1458663/
undo表空间文件丢失恢复(3)--无备份无redo的情况下恢复:http://blog.itpub.net/26736162/viewspace-1458750/
undo表空间文件丢失恢复(4)--无备份无recover的情况下恢复:http://blog.itpub.net/26736162/viewspace-1458787/
About Me
...............................................................................................................................
● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用
● 本文在itpub(http://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新
● 本文itpub地址:http://blog.itpub.net/26736162/viewspace-1623938/
● 本文博客园地址:http://www.cnblogs.com/lhrbest
● 本文pdf版及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群:230161599 微信群:私聊
● 联系我请加QQ好友(646634621),注明添加缘由
● 于 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解
● 版权所有,欢迎分享本文,转载请保留出处
...............................................................................................................................
拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号:xiaomaimiaolhr,扫描右边的二维码加入小麦苗的QQ群,学习最实用的数据库技术。