Oracle redo文件损坏怎么恢复

138次阅读

共计 8031 个字符，预计需要花费 21 分钟才能阅读完成。

这篇“Oracle redo 文件损坏怎么恢复”文章的知识点大部分人都不太理解，所以丸趣 TV 小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“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: {RET =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 文件损坏怎么恢复”这篇文章的内容，相信大家都有了一定的了解，希望丸趣 TV 小编分享的内容对大家有帮助，若想了解更多相关的知识内容，请关注丸趣 TV 行业资讯频道。

正文完