Oracle备份恢复-redo文件损坏的各种场景恢复专题

接着上一篇《控制文件损坏的各种场景恢复专题》的文章之后继续跟大家分享备份恢复专题《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'

5、通过将current状态切换到active状态，并初始化redo01.log文件
SQL> alter system switch logfile;
System altered.

SQL> alter database clear logfile group 1;
Database altered.

6、验证数据是否丢失，数据没有丢失
SQL> select * from t_redo;
ID
----------
1

原文链接：https://blog.csdn.net/liaocongyuan1314/article/details/50847529