Oracle redo文件损坏怎么恢复

这篇“Oracle redo文件损坏怎么恢复”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“Oracle redo文件损坏怎么恢复”文章吧。

redo文件损坏涉及到多种多样场景，具体场景可以分四大部分：

1、按照redo的状态可以分为current、active和inactive；

2、按照数据库归档模式可以分为归档和非归档；

3、按照脏块有没写入数据文件可以分为有和无；

4、按照损坏时数据库的状态可以分为在线和关闭；

现在主要通过两部分来介绍redo文件恢复相关的内容：

1、按照redo状态维度来介绍各种场景的恢复方法；

2、模拟几种恢复方法的操作；

一、按照redo状态维度来介绍各种场景的恢复方法。

1.1、current redo文件恢复介绍：

1.2、active redo文件恢复介绍：

1.3、inactive redo文件恢复介绍：

二、模拟几种恢复方法的操作；

下面主要选取 “current的redo文件在归档模式下采用不正常关闭数据库时还有脏块没有写入数据文件时的损坏” 和 “current的redo文件在非归档模式下数据库在线时还有脏块没有写入数据文件时的损坏” 这两个场景来模拟和恢复，其他场景的恢复请参考上面的恢复操作。

2.1、current的redo文件在归档模式下采用不正常关闭数据库时还有脏块没有写入数据文件时的损坏模拟恢复：

1、数据库基本信息和redo情况

SQL> show parameter db_name

NAME                                 TYPE        VALUE

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

db_name                              string      leonliao

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/oracle/oradata/leonliao/arch

Oldest online log sequence     7

Next log sequence to archive   9

Current log sequence           9

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

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

1          1          9   52428800        512          1 NO  CURRENT                1250771 07-MAR-16   2.8147E+14

2          1          8   52428800        512          1 YES INACTIVE               1250768 07-MAR-16      1250771 07-MAR-16

3          1          7   52428800        512          1 YES INACTIVE               1250765 07-MAR-16      1250768 07-MAR-16

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                        IS_

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

3         ONLINE  /home/oracle/oradata/leonliao/redo03.log      NO

2         ONLINE  /home/oracle/oradata/leonliao/redo02.log      NO

         1         ONLINE  /home/oracle/oradata/leonliao/redo01.log      NO

2、在t_redo表插入一条记录2，并shutdown abort关闭数据库

SQL> select * from t_redo;

        ID

----------

         1

SQL> insert into t_redo values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> shutdown abort

ORACLE instance shut down.

3、删掉current 的redo文件

[oracle@leon1 leonliao]$ pwd

/home/oracle/oradata/leonliao

[oracle@leon1 leonliao]$ rm -rf redo01.log 

4、启动数据库到mount状态并尝试打开数据库

SQL> startup mount

ORACLE instance started.

Total System Global Area  626327552 bytes

Fixed Size                  2230952 bytes

Variable Size             184550744 bytes

Database Buffers          432013312 bytes

Redo Buffers                7532544 bytes

Database mounted.

SQL> alter database open;

alter database open

*

ERROR at line 1:

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

ORA-00312: online log 1 thread 1: /home/oracle/oradata/leonliao/redo01.log

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

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 leonliao (thread 1)

ORA-00312: online log 1 thread 1: /home/oracle/oradata/leonliao/redo01.log

SQL> alter database clear unarchived logfile group 1;

alter database clear unarchived logfile group 1

*

ERROR at line 1:

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

ORA-00312: online log 1 thread 1: /home/oracle/oradata/leonliao/redo01.log

SQL> recover database until cancel;

ORA-00279: change 1250771 generated at 03/07/2016 07:35:44 needed for thread 1

ORA-00289: suggestion : /home/oracle/oradata/leonliao/arch/1_9_905840705.dbf

ORA-00280: change 1250771 for thread 1 is in sequence #9

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

auto

ORA-00308: cannot open archived log /home/oracle/oradata/leonliao/arch/1_9_905840705.dbf

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log /home/oracle/oradata/leonliao/arch/1_9_905840705.dbf

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

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: /home/oracle/oradata/leonliao/system01.dbf

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

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

ORA-01110: data file 1: /home/oracle/oradata/leonliao/system01.dbf

5、设置隐含参数_allow_resetlogs_corruption为true

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  626327552 bytes

Fixed Size                  2230952 bytes

Variable Size             184550744 bytes

Database Buffers          432013312 bytes

Redo Buffers                7532544 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

6、验证数据是否丢失，数据为2的记录已经丢失

SQL> select * from t_redo;

        ID

----------

         1

2.2、current的redo文件在非归档模式下数据库在线时还有脏块没有写入数据文件时的损坏模拟恢复：

1、数据库基本信息和redo情况

SQL> show parameter db_name

NAME                                 TYPE        VALUE

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

db_name                              string      leonliao

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /home/oracle/oradata/leonliao/arch

Oldest online log sequence     2

Current log sequence           4

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

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

1          1          4   52428800        512          1 NO  CURRENT                1250086 07-MAR-16   2.8147E+14

2          1          2   52428800        512          1 NO  INACTIVE               1250080 07-MAR-16      1250083 07-MAR-16

3          1          3   52428800        512          1 NO  INACTIVE               1250083 07-MAR-16      1250086 07-MAR-16

SQL> select * from v$logfile;

GROUP# STATUS  TYPE    MEMBER                                        IS_

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

         3         ONLINE  /home/oracle/oradata/leonliao/redo03.log      NO

2         ONLINE  /home/oracle/oradata/leonliao/redo02.log      NO

1         ONLINE  /home/oracle/oradata/leonliao/redo01.log      NO

2、创建t_redo表并插入一条数据

SQL> create table t_redo (id number);

Table created.         

SQL> insert into t_redo values(1);

1 row created.

SQL> commit;

Commit complete.

SQL>  select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

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

1          1          4   52428800        512          1 NO  CURRENT                1250086 07-MAR-16   2.8147E+14

2          1          2   52428800        512          1 NO  INACTIVE               1250080 07-MAR-16      1250083 07-MAR-16

3          1          3   52428800        512          1 NO  INACTIVE               1250083 07-MAR-16      1250086 07-MAR-16

3、删除current的redo01.log文件         

[oracle@leon1 leonliao]$ pwd

/home/oracle/oradata/leonliao

[oracle@leon1 leonliao]$ rm -rf redo01.log          

4、尝试直接通过不归档等方式初始化redo01.log文件，无法初始化current的redo文件

SQL> alter database clear unarchived logfile group 1;

alter database clear unarchived logfile group 1

*

ERROR at line 1:

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

ORA-00312: online log 1 thread 1: /home/oracle/oradata/leonliao/redo01.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 leonliao (thread 1)

ORA-00312: online log 1 thread 1: /home/oracle/oradata/leonliao/redo01.log

5、通过将current状态切换到active状态，并初始化redo01.log文件

SQL> alter system switch logfile;

System altered.

SQL>  alter database clear logfile group 1;

Database altered.

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

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

1          1          0   52428800        512          1 NO  UNUSED                 1250086 07-MAR-16      1250147 07-MAR-16

2          1          5   52428800        512          1 NO  CURRENT                1250147 07-MAR-16   2.8147E+14

3          1          3   52428800        512          1 NO  INACTIVE               1250083 07-MAR-16      1250086 07-MAR-16

6、验证数据是否丢失，数据没有丢失

SQL> select * from t_redo;

        ID

----------

         1

以上就是关于“Oracle redo文件损坏怎么恢复”这篇文章的内容，相信大家都有了一定的了解，希望小编分享的内容对大家有帮助，若想了解更多相关的知识内容，请关注行业资讯频道。