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

 

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

 

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

 

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

 

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

 

 

7、创建测试表，此时日志可以写入

SQL> create table test(id number);

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

[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

 

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