Oracle数据文件在open状态被删除的恢复记录

时间：2021-05-27 12:24:38
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;
 
3、日志没有异常
Mon
Mar ::
Thread advanced to log sequence
(LGWR switch)
   seq# mem# : seq# mem# : /data/hand/redo03b.log
Mon Mar
::
Starting background process CJQ0
Mon Mar ::
CJQ0 started with
pid, OS id 
Mon Mar ::
Thread advanced to log sequence
(LGWR switch)
   seq# mem# : seq# mem# : /data/hand/redo01b.log
Mon Mar
::
SERVER COMPONENT id ::
 
4、查看数据文件的句柄
[oracle@www
trace]$ ps -ef|grep
ora_dbw
oracle           Mar17
?        00:00: ora_dbw0_hand
oracle       : pts    00:00:00 grep
ora_dbw
[oracle@www trace]$ cd
[oracle@www ]$ cd fd
[oracle@www fd]$ ls
lr-x------
oracle oinstall
: -> /dev/null
l-wx------
oracle oinstall
: -> /dev/null
l-wx------
oracle oinstall
: -> /dev/null
lr-x------
oracle oinstall
: /fd
lrwx------
oracle oinstall
: ../dbs/lkinsthand
(deleted)
lrwx
oracle oinstall : ../dbs/hc_hand.dat
lr-x------
oracle oinstall
: -> /dev/null
lr-x------
oracle oinstall
: -> /dev/null
lr-x------
oracle oinstall
: -> /dev/null
l-wx------
oracle oinstall
:
oracle oinstall : -> /data/hand/users01.dbf (deleted)
lrwx------
oracle oinstall
: -> /data/hand/temp01.dbf (deleted)
lrwx------
oracle oinstall
: -> /data/hand/undotbs01.dbf (deleted)
lrwx------
oracle oinstall
: -> /data/hand/sysaux01.dbf (deleted)
lrwx------
oracle oinstall
: -> /data/hand/system01.dbf (deleted)
lrwx------
oracle oinstall
:
oracle oinstall : -> /u01/oracle/oradata/hand/control01.ctl
lr-x------
oracle oinstall
: ../rdbms/mesg/oraus.msb
lrwx------
oracle oinstall
: ..
oracle oinstall : ../dbs/hc_hand.dat
lr-x------
oracle oinstall
:
oracle oinstall :
oracle oinstall :
oracle oinstall : ..
oracle oinstall :
oracle oinstall : -> /u01/oracle/diag/rdbms/hand/hand/trace/hand_ora_16835.trc
 
5、恢复数据文件
[oracle@www
fd]$ mkdir /data/hand
[oracle@www
fd]$ cp

oracle oinstall
:00 sysaux01.dbf
-rw-r-----
oracle oinstall : system01.dbf
-rw-r-----
oracle oinstall   : temp01.dbf
-rw-r-----
oracle oinstall : undotbs01.dbf
-rw-r-----
oracle oinstall :
users01.dbf

 
6、恢复日志文件
[oracle@www
fd]$ ps -ef |grep
ora_lgwr
oracle           Mar17
?        00::
ora_lgwr_hand
oracle       : pts    00:00:00 grep
ora_lgwr
[oracle@www fd]$ pwd
/fd
[oracle@www fd]$ ll

l-wx------
oracle oinstall
: -> /dev/null
lr-x------
oracle oinstall
:
oracle oinstall : ../dbs/lkinsthand
(deleted)
lrwx
oracle oinstall : ../dbs/hc_hand.dat
lr-x------
oracle oinstall
: -> /dev/null
lr-x------
oracle oinstall
: -> /dev/null
lr-x------
oracle oinstall
: -> /dev/null
l-wx------
oracle oinstall
:
oracle oinstall : -> /dev/null
lr-x------
oracle oinstall
:
oracle oinstall : ../rdbms/mesg/oraus.msb
lrwx------
oracle oinstall
: ..
oracle oinstall : ../dbs/hc_hand.dat
lr-x------
oracle oinstall
:
oracle oinstall :
oracle oinstall :
oracle oinstall : ..
oracle oinstall :
oracle oinstall :
oracle oinstall :
oracle oinstall :
oracle oinstall :
oracle oinstall :
oracle oinstall :
oracle oinstall :
oracle oinstall :
oracle oinstall : /data/hand/redo01a.log
 
 
7、创建测试表，此时日志可以写入
SQL> create table
test(id number);

Table created.

SQL> insert into test
);

row created.

 
8、重启数据库，但是报错，日志中也有错误了
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE
instance started.

Total System Global
Area   bytes
Fixed bytes
Variable
bytes
Database
Buffers           bytes
Redo
Buffers                 bytes
Database
mounted.
ORA:

Session ID:

[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 ;

Database
altered.

;

Database altered.

SQL> alter database
clear unarchived logfile ;

Database
altered.

SQL> alter database open;

Database altered.

SQL> select * from test;
select * from
test
              :
ORA: 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

相关文章