oracle还原加日志,oracle利用日志进行数据恢复的理解

oracle利用日志进行数据恢复时，一般按照一下两步进行：[@more@]

1：首先查找redo，如果redo有可供恢复的信息，就那redo中的信息进行恢复，此时一般在恢复时，类似如下:

SQL> recover database;

Media recovery complete.

2：如果在redo中没有找到可供恢复的信息，oracle会去找archive log进行恢复，此时会有下面的提示：

SQL> recover database;

ORA-00279: change 25317158 generated at 08/27/2006 18:36:58 needed for thread 1

ORA-00289: suggestion : /opt/oracle/product/9.2.0/dbs/arch/T0001S0000000030.ARC

ORA-00280: change 25317158 for thread 1 is in sequence #30

oracle在恢复完归档信息后，不会在去找redo文件继续进行完全恢复，此时要人工去指定在线redo进行恢复，这也是此种恢复的一个缺陷，rman恢复就不会有此问题。

以下是测试步骤：

[oracle@test cicro]$ ls

control01.ctl cwmlite01.dbf indx01.dbf redo02.log system01.dbf test test3.dbf undotbs01.dbf control02.ctl drsys01.dbf odm01.dbf redo03.log temp test1.dbf test4.dbf users01.dbf control03.ctl example01.dbf redo01.log sq.sql temp01.dbf test2.dbf tools01.dbf xdb01.dbf

[oracle@test exitgogo]$ cp test1.dbf test2.dbf test3.dbf test4.dbf test

/首先备份test表空间数据文件

/以下为创建测试表

[oracle@test exitgogo]$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Aug 28 11:50:27 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 235999352 bytes

Fixed Size 450680 bytes

Variable Size 201326592 bytes

Database Buffers 33554432 bytes

Redo Buffers 667648 bytes

Database mounted.

Database opened.

SQL> create table exitgogo8 tablespace test as select * from all_objects;

Table created.

/以下操作为添加测试数据

SQL> insert into exitgogo8 select * from exitgogo8;

29618 rows created.

SQL> /

59236 rows created.

SQL> commit;

Commit complete.

/以下为查看当前的redo信息

SQL> select * from v$log

2 ;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIM

------------- ---------

1 1 40 104857600 1 NO CURRENT

25533341 28-AUG-06

2 1 0 104857600 1 YES UNUSED

25344443 27-AUG-06

3 1 39 104857600 1 YES INACTIVE

25344445 27-AUG-06

/继续添加数据，强制日志切换，归档上面的操作记录

SQL> insert into exitgogo8 select * from exitgogo8;

118472 rows created.

SQL> alter system switch logfile;

System altered.

/在线日志从group 1转到了group 2

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIM

------------- ---------

1 1 40 104857600 1 NO ACTIVE

25533341 28-AUG-06

2 1 41 104857600 1 NO CURRENT

25536951 28-AUG-06

3 1 39 104857600 1 YES INACTIVE

25344445 27-AUG-06

/继续切换日志

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIM

------------- ---------

1 1 40 104857600 1 YES INACTIVE

25533341 28-AUG-06

2 1 41 104857600 1 YES ACTIVE

25536951 28-AUG-06

3 1 42 104857600 1 NO CURRENT

25537044 28-AUG-06

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> quit

Disconnected from Oracle9i Release 9.2.0.1.0 - Production

JServer Release 9.2.0.1.0 - Production

/删除现在的test表空间数据文件，拿备份恢复

[oracle@test exitgogo]$ mv test1.dbf test2.dbf test3.dbf test4.dbf temp

[oracle@test exitgogo]$ mv test/* .

[oracle@test exitgogo]$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Aug 28 11:54:32 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 235999352 bytes

Fixed Size 450680 bytes

Variable Size 201326592 bytes

Database Buffers 33554432 bytes

Redo Buffers 667648 bytes

Database mounted.

ORA-01113: file 11 needs media recovery

ORA-01110: data file 11: '/opt/oracle/oradata/exitgogo/test1.dbf'

/进行恢复数据

SQL> recover database;

Media recovery complete.

以下是恢复时刻的日志信息：sequence 40以及41正是redo文件，

Media Recovery Start

Starting datafile 11 recovery in thread 1 sequence 40

Datafile 11: '/opt/oracle/oradata/cicro/test1.dbf'

Starting datafile 12 recovery in thread 1 sequence 40

Datafile 12: '/opt/oracle/oradata/cicro/test2.dbf'

Starting datafile 13 recovery in thread 1 sequence 40

Datafile 13: '/opt/oracle/oradata/cicro/test3.dbf'

Starting datafile 14 recovery in thread 1 sequence 40

Datafile 14: '/opt/oracle/oradata/cicro/test4.dbf'

Media Recovery Log

Recovery of Online Redo Log: Thread 1 Group 1 Seq 40 Reading mem 0

Mem# 0 errs 0: /opt/oracle/oradata/cicro/redo01.log

Recovery of Online Redo Log: Thread 1 Group 2 Seq 41 Reading mem 0

Mem# 0 errs 0: /opt/oracle/oradata/cicro/redo02.log

Recovery of Online Redo Log: Thread 1 Group 3 Seq 42 Reading mem 0

Mem# 0 errs 0: /opt/oracle/oradata/cicro/redo03.log

SQL> alter database open;

Database altered.

恢复完成。

总结：

由于我的操作日志已经归档，但是在redo中的操作记录还没有被覆盖掉，此时oracle自动从redo中恢复。如果redo文件丢失或者损坏，oracle自动从archive log开始恢复。

如果是从归档开始恢复，应该有类似

ORA-00279: change 25317158 generated at 08/27/2006 18:36:58 needed for thread 1

ORA-00289:suggestion :/opt/oracle/product/9.2.0/dbs/arch/T0001S0000000030.ARC

ORA-00280: change 25317158 for thread 1 is in sequence #30

的提示，此时说明要用到归档来进行恢复。