备份与恢复（ Redo log 恢复篇）

 

环境： oracle 版本 10.2.0.1   无归档模式下

系统： contes5.6

 

总结：

一、无归档模式， shutdown immediate 关库：

1、   redo log 损坏， status 为 active ,current 不能直接 clear 直接恢复，可以进行不完全恢复（ recover database until cancel ）。

2、   redo log 损坏， stauts 为 inactive 可以直接 clear 进行恢复。

二、无归档模式， shutdown abort 关库：

1 、 redo log 损坏， status 为 active ,current 可以使用隐含参数 _allow_resetlogs_corruption=true ，强制开启数据，然后导出数据，重新建库，导入数据。 { 不能直接 clear 直接恢复。也不能直接进行不完全恢复（ recover database until cancel ） } 。

2、   redo log 损坏， stauts 为 inactive 可以直接 clear 进行恢复。

 

 

实验（一） Redo log 日志 stauts 为 current 恢复

1 、查看当前日志情况

SQL> col group# for 999999

SQL> col status for a10

SQL> col member for a40

SQL> select a.group#,a.status,member from v$logfile b,v$log a where a.group#=b.group#;

 

 GROUP# STATUS      MEMBER

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

      1 INACTIVE    /opt/oracle/oradata/research/redo1.dbf

      2 INACTIVE    /opt/oracle/oradata/research/redo2.dbf

      3 CURRENT     /opt/oracle/oradata/research/redo3.dbf

2 、直接删除 group 3 (current)

[ora@dg-pp research]$ rm -rf redo3.dbf

[ora@dg-pp research]$ ls （这里 redo3.dbf 已经删除了）

control01.ctl   control03.ctl   redo2.dbf      system01.dbf   undo01.dbf

control02.ctl   redo1.dbf       sysaux01.dbf   temp01.dbf     users01.dbf

 

3 、切换日志看看，数据库变化 （切换 3 次， redo log group3 又被重写了）。数据库正常运行！

 

SQL> alter system switch logfile;

 

System altered.

 

SQL> /

 

System altered.

 

SQL> /

 

System altered.

 

SQL> select a.group#,a.status,member from v$logfile b,v$log a where a.group#=b.group#;

 

 GROUP# STATUS      MEMBER

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

      1 ACTIVE      /opt/oracle/oradata/research/redo1.dbf

      2 INACTIVE    /opt/oracle/oradata/research/redo2.dbf

      3 CURRENT     /opt/oracle/oradata/research/redo3.dbf

 

4 、正常关闭数据库（ shutdown immediate ）

5 、开启数据库报错信息

SQL> startup

ORACLE instance started.

 

Total System Global Area   314572800 bytes

Fixed Size                   1219184 bytes

Variable Size               96470416 bytes

Database Buffers           213909504 bytes

Redo Buffers                 2973696 bytes

Database mounted.

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/research/redo3.dbf'

 

恢复：

1、   查看数据库现在的状态

SQL> select status from v$instance;

 

STATUS

----------

MOUNTED

2、   查看 redo log 状态

SQL> select a.group#,a.status,member from v$logfile b,v$log a where a.group#=b.group#;

 

 GROUP# STATUS      MEMBER

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

      1 INACTIVE    /opt/oracle/oradata/research/redo1.dbf

      2 INACTIVE    /opt/oracle/oradata/research/redo2.dbf

      3 CURRENT     /opt/oracle/oradata/research/redo3.dbf

 

3、   尝试 clear 恢复 redo log

SQL> alter database clear logfile group 3;

alter database clear logfile group 3

*

ERROR at line 1:

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/research/redo3.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

这里不让 clear ， oracle 发现根本没有 redo3.dbf 数据文件的存在。（这里直接尝试把 redo2.dbf copy 并修改名为 redo3.dbf ）

[ora@dg-pp research]$ cp redo2.dbf redo3.dbf

[ora@dg-pp research]$ ls

control01.ctl   control03.ctl   redo2.dbf   sysaux01.dbf   temp01.dbf   users01.dbf

control02.ctl   redo1.dbf       redo3.dbf   system01.dbf   undo01.dbf

 

继续 clear 看看

SQL> alter database clear logfile group 3;

alter database clear logfile group 3

*

ERROR at line 1:

ORA-00341: log 3 of thread 1, wrong log # 2 in header

ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/research/redo3.dbf'

 

分析：这里报错很明显了 oracle 发现你骗它了，文件头不同。所以不能恢复。（思考： clear 会先去检测 redo log 文件头是否和控制文件的记录相同。不同是不让你 clear ）

综上所述：我们这里恢复 redo log (current) 。直接 clear 是不可能了。

 

直接删除 redo3.dbf   进行不完全恢复。

 

[ora@dg-pp research]$ rm -rf redo3.dbf

[ora@dg-pp research]$ ls

control01.ctl   control03.ctl   redo2.dbf      system01.dbf   undo01.dbf

control02.ctl   redo1.dbf       sysaux01.dbf   temp01.dbf     users01.dbf

 

4 、不完全恢复。

SQL> recover database until cancel;

Media recovery complete.

查看是否生成了 redo3.dbf

[ora@dg-pp research]$ ls

control01.ctl   control03.ctl   redo2.dbf      system01.dbf   undo01.dbf

control02.ctl   redo1.dbf       sysaux01.dbf   temp01.dbf     users01.dbf

很显然这个时候，没有生成 redo3.dbf 物理文件。但是控制文件已经被恢复了。

SQL> select a.group#,a.status,member from v$logfile b,v$log a where a.group#=b.group#;

 

 GROUP# STATUS      MEMBER

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

      1 INACTIVE    /opt/oracle/oradata/research/redo1.dbf

      2 UNUSED      /opt/oracle/oradata/research/redo2.dbf

      3 CLEARING_C /opt/oracle/oradata/research/redo3.dbf

        URRENT

 

Status 状态为 CLEARING_CURRENT

一个正在被 clear 的 CURRENT group （ Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header. ）出现在中状态一般是有错误的。

这里来源于 eygle 老师的 http://www.eygle.com/archives/2008/05/oracle_logfile.html

 

5 、最后 resetlogs 开启数据库

SQL> alter database open resetlogs;

 

Database altered.

SQL> select a.group#,a.status,member from v$logfile b,v$log a where a.group#=b.group#;

 

 GROUP# STATUS      MEMBER

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

      1 CURRENT     /opt/oracle/oradata/research/redo1.dbf

      2 UNUSED      /opt/oracle/oradata/research/redo2.dbf

      3 INACTIVE    /opt/oracle/oradata/research/redo3.dbf

 

查看物理 redo log 文件是否存在，在 open 数据库的同时 redo log 文件会被建立。

[ora@dg-pp research]$ ls

control01.ctl   control03.ctl   redo2.dbf   sysaux01.dbf   temp01.dbf   users01.dbf

control02.ctl   redo1.dbf       redo3.dbf  system01.dbf   undo01.dbf

物理文件存在了。

 

继续切换看看数据库状态。

System altered.

 

SQL> /

 

System altered.

 

SQL> /

 

System altered.

 

SQL> select a.group#,a.status,member from v$logfile b,v$log a where a.group#=b.group#;

 

 GROUP# STATUS      MEMBER

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

      1 INACTIVE    /opt/oracle/oradata/research/redo1.dbf

      2 ACTIVE      /opt/oracle/oradata/research/redo2.dbf

      3 CURRENT     /opt/oracle/oradata/research/redo3.dbf

 

恢复成功！

 

实验（二） Redo log 日志 stauts 为 active 恢复

 

1 、创建实验的环境省略（也是把 redo log 文件直接删除）

SQL> select a.group#,a.status,member from v$logfile b,v$log a where a.group#=b.group#;

 

 GROUP# STATUS      MEMBER

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

      1 INACTIVE    /opt/oracle/oradata/research/redo1.dbf

      2 ACTIVE     /opt/oracle/oradata/research/redo2.dbf

      3 CURRENT     /opt/oracle/oradata/research/redo3.dbf

2 、闭关数据库（ shutdown immediate ），开启数据库报错信息

SQL> startup

ORACLE instance started.

 

Total System Global Area   314572800 bytes

Fixed Size                   1219184 bytes

Variable Size               96470416 bytes

Database Buffers           213909504 bytes

Redo Buffers                 2973696 bytes

Database mounted.

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/research/redo2.dbf

 

恢复：

1 、尝试 clear ,redo log

 

SQL> alter database clear logfile group 2;

alter database clear logfile group 2

*

ERROR at line 1:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/research/redo2.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

2 、尝试 alter database clear unarchived logfile group 2;

SQL> alter database clear unarchived logfile group 2;

alter database clear unarchived logfile group 2

*

ERROR at line 1:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/research/redo2.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

3 、不完全恢复

SQL> recover database until cancel;

Media recovery complete.

SQL> alter database open resetlogs;

 

Database altered.

恢复成功！

 

实验（三） Redo log 日志 stauts 为 inactive 恢复

1 、创建实验的环境省略（也是把 redo log 文件直接删除）

SQL>   select a.group#,a.status,member from v$logfile b,v$log a where a.group#=b.group#;

 

 GROUP# STATUS      MEMBER

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

      1 INACTIVE   /opt/oracle/oradata/research/redo1.dbf

      2 CURRENT     /opt/oracle/oradata/research/redo2.dbf

      3 ACTIVE      /opt/oracle/oradata/research/redo3.dbf

2 、 闭关数据库（ shutdown immediate ），开启数据库报错信息

SQL> startup

ORACLE instance started.

 

Total System Global Area   314572800 bytes

Fixed Size                   1219184 bytes

Variable Size               96470416 bytes

Database Buffers           213909504 bytes

Redo Buffers                 2973696 bytes

Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/research/redo1.dbf

 

恢复：

1、   尝试 clear,redo log

SQL> alter database clear logfile group 1;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

恢复成功！

 

 

实验（四） Redo log 日志 stauts 为 current 恢复，且 shutdown abort

1 、创建实验的环境省略（也是把 redo log 文件直接删除）

SQL>   select a.group#,a.status,member from v$logfile b,v$log a where a.group#=b.group#;

 

 GROUP# STATUS      MEMBER

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

      1 INACTIVE   /opt/oracle/oradata/research/redo1.dbf

      2 CURRENT     /opt/oracle/oradata/research/redo2.dbf

      3 ACTIVE      /opt/oracle/oradata/research/redo3.dbf

2、   闭关数据库（ shutdown abort ），开启数据库报错

SQL> startup

ORACLE instance started.

 

Total System Global Area   314572800 bytes

Fixed Size                   1219184 bytes

Variable Size               96470416 bytes

Database Buffers           213909504 bytes

Redo Buffers                 2973696 bytes

Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/research/redo1.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

 

恢复：

1、   尝试 clear ,redo log

SQL> alter database clear logfile group 1;

alter database clear logfile group 1

*

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of instance research (thread 1)

ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/research/redo1.dbf'

 

2、   不完全恢复

SQL> recover database until cancel;

ORA-00279: change 363386 generated at 04/02/2012 17:54:40 needed for thread 1

ORA-00289: suggestion :

/opt/oracle/product/10.2.0/db_1/dbs/arch1_14_779564616.dbf

ORA-00280: change 363386 for thread 1 is in sequence #14

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/opt/oracle/oradata/research/system01.dbf'

 

 

ORA-01112: media recovery not started

 

3 、使用 _allow_resetlogs_corruption=true 隐含参数强制打开数据库，然后导出数据。导入另一个完好的数据库。

1 ）修改： vi   initsid.ora 添加 _allow_resetlogs_corruption=true

SQL> startup

ORACLE instance started.

 

Total System Global Area   314572800 bytes

Fixed Size                   1219184 bytes

Variable Size               96470416 bytes

Database Buffers           213909504 bytes

Redo Buffers                 2973696 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

2 ） SQL> alter database open resetlogs;

Database altered.

恢复成功！