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

备份与恢复（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: {<RET>=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.

恢复成功！