首页 > 代码库 > Oracle数据文件在open状态被删除的恢复记录
Oracle数据文件在open状态被删除的恢复记录
1、查看当前状态：
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> show parameter name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string hand
db_unique_name string hand
global_names boolean FALSE
instance_name string hand
lock_name_space string
log_file_name_convert string
service_names string hand.likun.com
STATUS
------------
OPEN
SQL> show parameter name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string hand
db_unique_name string hand
global_names boolean FALSE
instance_name string hand
lock_name_space string
log_file_name_convert string
service_names string hand.likun.com
2、删除数据文件，数据库扔正常运行
[oracle@www data]$ rm -rf hand
SQL> select count(*) from dba_segments;
COUNT(*)
----------
2520
3、日志没有异常
Mon Mar 18 11:18:01 2013
Thread 1 advanced to log sequence 6 (LGWR switch)
Current log# 3 seq# 6 mem# 0: /data/hand/redo03a.log
Current log# 3 seq# 6 mem# 1: /data/hand/redo03b.log
Mon Mar 18 11:20:14 2013
Starting background process CJQ0
Mon Mar 18 11:20:14 2013
CJQ0 started with pid=25, OS id=13967
Mon Mar 18 11:21:04 2013
Thread 1 advanced to log sequence 7 (LGWR switch)
Current log# 1 seq# 7 mem# 0: /data/hand/redo01a.log
Current log# 1 seq# 7 mem# 1: /data/hand/redo01b.log
Mon Mar 18 11:23:38 2013
SERVER COMPONENT id=CATPROC: timestamp=2013-03-18 11:23:38
Thread 1 advanced to log sequence 6 (LGWR switch)
Current log# 3 seq# 6 mem# 0: /data/hand/redo03a.log
Current log# 3 seq# 6 mem# 1: /data/hand/redo03b.log
Mon Mar 18 11:20:14 2013
Starting background process CJQ0
Mon Mar 18 11:20:14 2013
CJQ0 started with pid=25, OS id=13967
Mon Mar 18 11:21:04 2013
Thread 1 advanced to log sequence 7 (LGWR switch)
Current log# 1 seq# 7 mem# 0: /data/hand/redo01a.log
Current log# 1 seq# 7 mem# 1: /data/hand/redo01b.log
Mon Mar 18 11:23:38 2013
SERVER COMPONENT id=CATPROC: timestamp=2013-03-18 11:23:38
4、查看数据文件的句柄[oracle@www trace]$ ps -ef|grep ora_dbw
oracle 16871 1 0 Mar17 ? 00:00:09 ora_dbw0_hand
oracle 28645 28271 0 21:22 pts/3 00:00:00 grep ora_dbw
[oracle@www trace]$ cd /proc/16871
[oracle@www 16871]$ cd fd
[oracle@www fd]$ ls -ltr
总计 0
lr-x------ 1 oracle oinstall 64 03-18 21:10 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 03-18 21:10 2 -> /dev/null
l-wx------ 1 oracle oinstall 64 03-18 21:10 1 -> /dev/null
lr-x------ 1 oracle oinstall 64 03-18 21:10 9 -> /proc/16871/fd
lrwx------ 1 oracle oinstall 64 03-18 21:10 8 -> /u01/oracle/product/11.2.0/dbs/lkinsthand (deleted)
lrwx------ 1 oracle oinstall 64 03-18 21:10 7 -> /u01/oracle/product/11.2.0/dbs/hc_hand.dat
lr-x------ 1 oracle oinstall 64 03-18 21:10 6 -> /dev/null
lr-x------ 1 oracle oinstall 64 03-18 21:10 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 03-18 21:10 4 -> /dev/null
l-wx------ 1 oracle oinstall 64 03-18 21:10 3 -> /u01/oracle/admin/hand/udump/hand_ora_16835.trc
lrwx------ 1 oracle oinstall 64 03-18 21:10 25 -> /data/hand/users01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 03-18 21:10 24 -> /data/hand/temp01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 03-18 21:10 23 -> /data/hand/undotbs01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 03-18 21:10 22 -> /data/hand/sysaux01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 03-18 21:10 21 -> /data/hand/system01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 03-18 21:10 20 -> /u01/oracle/flash_recovery_area/hand/control02.ctl
lrwx------ 1 oracle oinstall 64 03-18 21:10 19 -> /u01/oracle/oradata/hand/control01.ctl
lr-x------ 1 oracle oinstall 64 03-18 21:10 18 -> /u01/oracle/product/11.2.0/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 03-18 21:10 17 -> /u01/oracle/product/11.2.0/dbs/lkHAND
lrwx------ 1 oracle oinstall 64 03-18 21:10 16 -> /u01/oracle/product/11.2.0/dbs/hc_hand.dat
lr-x------ 1 oracle oinstall 64 03-18 21:10 15 -> /dev/zero
lr-x------ 1 oracle oinstall 64 03-18 21:10 14 -> /proc/16871/fd
lr-x------ 1 oracle oinstall 64 03-18 21:10 13 -> /dev/zero
lr-x------ 1 oracle oinstall 64 03-18 21:10 12 -> /u01/oracle/product/11.2.0/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 03-18 21:10 11 -> /u01/oracle/diag/rdbms/hand/hand/trace/hand_ora_16835.trm
l-wx------ 1 oracle oinstall 64 03-18 21:10 10 -> /u01/oracle/diag/rdbms/hand/hand/trace/hand_ora_16835.trc
oracle 16871 1 0 Mar17 ? 00:00:09 ora_dbw0_hand
oracle 28645 28271 0 21:22 pts/3 00:00:00 grep ora_dbw
[oracle@www trace]$ cd /proc/16871
[oracle@www 16871]$ cd fd
[oracle@www fd]$ ls -ltr
总计 0
lr-x------ 1 oracle oinstall 64 03-18 21:10 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 03-18 21:10 2 -> /dev/null
l-wx------ 1 oracle oinstall 64 03-18 21:10 1 -> /dev/null
lr-x------ 1 oracle oinstall 64 03-18 21:10 9 -> /proc/16871/fd
lrwx------ 1 oracle oinstall 64 03-18 21:10 8 -> /u01/oracle/product/11.2.0/dbs/lkinsthand (deleted)
lrwx------ 1 oracle oinstall 64 03-18 21:10 7 -> /u01/oracle/product/11.2.0/dbs/hc_hand.dat
lr-x------ 1 oracle oinstall 64 03-18 21:10 6 -> /dev/null
lr-x------ 1 oracle oinstall 64 03-18 21:10 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 03-18 21:10 4 -> /dev/null
l-wx------ 1 oracle oinstall 64 03-18 21:10 3 -> /u01/oracle/admin/hand/udump/hand_ora_16835.trc
lrwx------ 1 oracle oinstall 64 03-18 21:10 25 -> /data/hand/users01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 03-18 21:10 24 -> /data/hand/temp01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 03-18 21:10 23 -> /data/hand/undotbs01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 03-18 21:10 22 -> /data/hand/sysaux01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 03-18 21:10 21 -> /data/hand/system01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 03-18 21:10 20 -> /u01/oracle/flash_recovery_area/hand/control02.ctl
lrwx------ 1 oracle oinstall 64 03-18 21:10 19 -> /u01/oracle/oradata/hand/control01.ctl
lr-x------ 1 oracle oinstall 64 03-18 21:10 18 -> /u01/oracle/product/11.2.0/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 03-18 21:10 17 -> /u01/oracle/product/11.2.0/dbs/lkHAND
lrwx------ 1 oracle oinstall 64 03-18 21:10 16 -> /u01/oracle/product/11.2.0/dbs/hc_hand.dat
lr-x------ 1 oracle oinstall 64 03-18 21:10 15 -> /dev/zero
lr-x------ 1 oracle oinstall 64 03-18 21:10 14 -> /proc/16871/fd
lr-x------ 1 oracle oinstall 64 03-18 21:10 13 -> /dev/zero
lr-x------ 1 oracle oinstall 64 03-18 21:10 12 -> /u01/oracle/product/11.2.0/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 03-18 21:10 11 -> /u01/oracle/diag/rdbms/hand/hand/trace/hand_ora_16835.trm
l-wx------ 1 oracle oinstall 64 03-18 21:10 10 -> /u01/oracle/diag/rdbms/hand/hand/trace/hand_ora_16835.trc
5、恢复数据文件[oracle@www fd]$ mkdir /data/hand
[oracle@www fd]$ cp 24 /data/hand/temp01.dbf
[oracle@www fd]$ cp 25 /data/hand/users01.dbf
[oracle@www fd]$ cp 23 /data/hand/undotbs01.dbf
[oracle@www fd]$ cp 22 /data/hand/sysaux01.dbf
[oracle@www fd]$ cp 21 /data/hand/system01.dbf
[oracle@www hand]$ pwd
/data/hand
[oracle@www hand]$ ll
总计 975116
-rw-r----- 1 oracle oinstall 340795392 03-18 22:00 sysaux01.dbf
-rw-r----- 1 oracle oinstall 340795392 03-18 21:55 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 03-18 21:32 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 03-18 21:44 undotbs01.dbf
-rw-r----- 1 oracle oinstall 104865792 03-18 21:36 users01.dbf
[oracle@www fd]$ cp 24 /data/hand/temp01.dbf
[oracle@www fd]$ cp 25 /data/hand/users01.dbf
[oracle@www fd]$ cp 23 /data/hand/undotbs01.dbf
[oracle@www fd]$ cp 22 /data/hand/sysaux01.dbf
[oracle@www fd]$ cp 21 /data/hand/system01.dbf
[oracle@www hand]$ pwd
/data/hand
[oracle@www hand]$ ll
总计 975116
-rw-r----- 1 oracle oinstall 340795392 03-18 22:00 sysaux01.dbf
-rw-r----- 1 oracle oinstall 340795392 03-18 21:55 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 03-18 21:32 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 03-18 21:44 undotbs01.dbf
-rw-r----- 1 oracle oinstall 104865792 03-18 21:36 users01.dbf
6、恢复日志文件
[oracle@www fd]$ ps -ef |grep ora_lgwr
oracle 16873 1 0 Mar17 ? 00:01:18 ora_lgwr_hand
oracle 30019 28271 0 22:03 pts/3 00:00:00 grep ora_lgwr
[oracle@www fd]$ pwd
/proc/16871/fd
[oracle@www fd]$ cd /proc/16873/fd
[oracle@www fd]$ ll -trl
总计 0
l-wx------ 1 oracle oinstall 64 03-18 22:03 1 -> /dev/null
lr-x------ 1 oracle oinstall 64 03-18 22:03 0 -> /dev/null
lrwx------ 1 oracle oinstall 64 03-18 22:03 8 -> /u01/oracle/product/11.2.0/dbs/lkinsthand (deleted)
lrwx------ 1 oracle oinstall 64 03-18 22:03 7 -> /u01/oracle/product/11.2.0/dbs/hc_hand.dat
lr-x------ 1 oracle oinstall 64 03-18 22:03 6 -> /dev/null
lr-x------ 1 oracle oinstall 64 03-18 22:03 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 03-18 22:03 4 -> /dev/null
l-wx------ 1 oracle oinstall 64 03-18 22:03 3 -> /u01/oracle/admin/hand/udump/hand_ora_16835.trc
l-wx------ 1 oracle oinstall 64 03-18 22:03 2 -> /dev/null
lr-x------ 1 oracle oinstall 64 03-18 22:03 9 -> /proc/16873/fd
lr-x------ 1 oracle oinstall 64 03-18 22:03 18 -> /u01/oracle/product/11.2.0/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 03-18 22:03 17 -> /u01/oracle/product/11.2.0/dbs/lkHAND
lrwx------ 1 oracle oinstall 64 03-18 22:03 16 -> /u01/oracle/product/11.2.0/dbs/hc_hand.dat
lr-x------ 1 oracle oinstall 64 03-18 22:03 15 -> /dev/zero
lr-x------ 1 oracle oinstall 64 03-18 22:03 14 -> /proc/16873/fd
lr-x------ 1 oracle oinstall 64 03-18 22:03 13 -> /dev/zero
lr-x------ 1 oracle oinstall 64 03-18 22:03 12 -> /u01/oracle/product/11.2.0/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 03-18 22:03 11 -> /u01/oracle/diag/rdbms/hand/hand/trace/hand_ora_16835.trm
l-wx------ 1 oracle oinstall 64 03-18 22:03 10 -> /u01/oracle/diag/rdbms/hand/hand/trace/hand_ora_16835.trc
lrwx------ 1 oracle oinstall 64 03-18 22:03 26 -> /data/hand/redo03b.log (deleted)
lrwx------ 1 oracle oinstall 64 03-18 22:03 25 -> /data/hand/redo03a.log (deleted)
lrwx------ 1 oracle oinstall 64 03-18 22:03 24 -> /data/hand/redo02b.log (deleted)
lrwx------ 1 oracle oinstall 64 03-18 22:03 23 -> /data/hand/redo02a.log (deleted)
lrwx------ 1 oracle oinstall 64 03-18 22:03 22 -> /data/hand/redo01b.log (deleted)
lrwx------ 1 oracle oinstall 64 03-18 22:03 21 -> /data/hand/redo01a.log (deleted)
lrwx------ 1 oracle oinstall 64 03-18 22:03 20 -> /u01/oracle/flash_recovery_area/hand/control02.ctl
lrwx------ 1 oracle oinstall 64 03-18 22:03 19 -> /u01/oracle/oradata/hand/control01.ctl
[oracle@www fd]$ cp 26 /data/hand/redo03b.log
[oracle@www fd]$ cp 25 /data/hand/redo03a.log
[oracle@www fd]$ cp 24 /data/hand/redo02b.log
[oracle@www fd]$ cp 23 /data/hand/redo02a.log
[oracle@www fd]$ cp 22 /data/hand/redo01b.log
[oracle@www fd]$ cp 21 /data/hand/redo01a.log
oracle 16873 1 0 Mar17 ? 00:01:18 ora_lgwr_hand
oracle 30019 28271 0 22:03 pts/3 00:00:00 grep ora_lgwr
[oracle@www fd]$ pwd
/proc/16871/fd
[oracle@www fd]$ cd /proc/16873/fd
[oracle@www fd]$ ll -trl
总计 0
l-wx------ 1 oracle oinstall 64 03-18 22:03 1 -> /dev/null
lr-x------ 1 oracle oinstall 64 03-18 22:03 0 -> /dev/null
lrwx------ 1 oracle oinstall 64 03-18 22:03 8 -> /u01/oracle/product/11.2.0/dbs/lkinsthand (deleted)
lrwx------ 1 oracle oinstall 64 03-18 22:03 7 -> /u01/oracle/product/11.2.0/dbs/hc_hand.dat
lr-x------ 1 oracle oinstall 64 03-18 22:03 6 -> /dev/null
lr-x------ 1 oracle oinstall 64 03-18 22:03 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 03-18 22:03 4 -> /dev/null
l-wx------ 1 oracle oinstall 64 03-18 22:03 3 -> /u01/oracle/admin/hand/udump/hand_ora_16835.trc
l-wx------ 1 oracle oinstall 64 03-18 22:03 2 -> /dev/null
lr-x------ 1 oracle oinstall 64 03-18 22:03 9 -> /proc/16873/fd
lr-x------ 1 oracle oinstall 64 03-18 22:03 18 -> /u01/oracle/product/11.2.0/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 03-18 22:03 17 -> /u01/oracle/product/11.2.0/dbs/lkHAND
lrwx------ 1 oracle oinstall 64 03-18 22:03 16 -> /u01/oracle/product/11.2.0/dbs/hc_hand.dat
lr-x------ 1 oracle oinstall 64 03-18 22:03 15 -> /dev/zero
lr-x------ 1 oracle oinstall 64 03-18 22:03 14 -> /proc/16873/fd
lr-x------ 1 oracle oinstall 64 03-18 22:03 13 -> /dev/zero
lr-x------ 1 oracle oinstall 64 03-18 22:03 12 -> /u01/oracle/product/11.2.0/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 03-18 22:03 11 -> /u01/oracle/diag/rdbms/hand/hand/trace/hand_ora_16835.trm
l-wx------ 1 oracle oinstall 64 03-18 22:03 10 -> /u01/oracle/diag/rdbms/hand/hand/trace/hand_ora_16835.trc
lrwx------ 1 oracle oinstall 64 03-18 22:03 26 -> /data/hand/redo03b.log (deleted)
lrwx------ 1 oracle oinstall 64 03-18 22:03 25 -> /data/hand/redo03a.log (deleted)
lrwx------ 1 oracle oinstall 64 03-18 22:03 24 -> /data/hand/redo02b.log (deleted)
lrwx------ 1 oracle oinstall 64 03-18 22:03 23 -> /data/hand/redo02a.log (deleted)
lrwx------ 1 oracle oinstall 64 03-18 22:03 22 -> /data/hand/redo01b.log (deleted)
lrwx------ 1 oracle oinstall 64 03-18 22:03 21 -> /data/hand/redo01a.log (deleted)
lrwx------ 1 oracle oinstall 64 03-18 22:03 20 -> /u01/oracle/flash_recovery_area/hand/control02.ctl
lrwx------ 1 oracle oinstall 64 03-18 22:03 19 -> /u01/oracle/oradata/hand/control01.ctl
[oracle@www fd]$ cp 26 /data/hand/redo03b.log
[oracle@www fd]$ cp 25 /data/hand/redo03a.log
[oracle@www fd]$ cp 24 /data/hand/redo02b.log
[oracle@www fd]$ cp 23 /data/hand/redo02a.log
[oracle@www fd]$ cp 22 /data/hand/redo01b.log
[oracle@www fd]$ cp 21 /data/hand/redo01a.log
7、创建测试表，此时日志可以写入
SQL> create table test(id number);
Table created.
SQL> insert into test values(1);
1 row created.
Table created.
SQL> insert into test values(1);
1 row created.
8、重启数据库，但是报错，日志中也有错误了
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 322228224 bytes
Fixed Size 1336288 bytes
Variable Size 205524000 bytes
Database Buffers 109051904 bytes
Redo Buffers 6316032 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 30385
Session ID: 1 Serial number: 5
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 322228224 bytes
Fixed Size 1336288 bytes
Variable Size 205524000 bytes
Database Buffers 109051904 bytes
Redo Buffers 6316032 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 30385
Session ID: 1 Serial number: 5
[oracle@www trace]$ tail -30 alert*
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 3937093700
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Mon Mar 18 22:14:39 2013
ALTER DATABASE RECOVER database
Media Recovery Start
Serial Media Recovery started
Media Recovery failed with error 264
ORA-283 signalled during: ALTER DATABASE RECOVER database ...
Mon Mar 18 22:15:24 2013
alter database open
Mon Mar 18 22:15:24 2013
Errors in file /u01/oracle/diag/rdbms/hand/hand/trace/hand_lgwr_30473.trc:
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: ‘/data/hand/redo01b.log‘
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: ‘/data/hand/redo01a.log‘
Errors in file /u01/oracle/diag/rdbms/hand/hand/trace/hand_lgwr_30473.trc:
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: ‘/data/hand/redo01b.log‘
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: ‘/data/hand/redo01a.log‘
Errors in file /u01/oracle/diag/rdbms/hand/hand/trace/hand_ora_30494.trc:
ORA-00322: log 1 of thread is not current copy
ORA-00312: online log 1 thread 1: ‘/data/hand/redo01a.log‘
ORA-00312: online log 1 thread 1: ‘/data/hand/redo01b.log‘
USER (ospid: 30494): terminating the instance due to error 322
Instance terminated by USER, pid = 30494
9、清空在线日志，open，可以起来，但是故障期间的数据丢失
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> alter database open;
Database altered.
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00942: table or view does not exist
Database altered.
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> alter database open;
Database altered.
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00942: table or view does not exist
10、经过再次试验，发现在恢复redo时，只恢复状态为inactive的日志，最后重启数据库前，多次alter system switch logfile来切日志，把日志中信息写盘。最后重启数据库，但是启动还是报错，但是日志中已经没有未存盘的数据，此时可以clear日志再open就不会丢数据了。SQL> startup
ORACLE instance started.
Total System Global Area 322228224 bytes
Fixed Size 1336288 bytes
Variable Size 209718304 bytes
Database Buffers 104857600 bytes
Redo Buffers 6316032 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 651
Session ID: 1 Serial number: 5
Oracle数据文件在open状态被删除的恢复记录
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。