Redo Log的恢复

完全恢复和不完全恢复：完全恢复一定不丢数据；不完全恢复可以丢数据
数据库对完全恢复的定义：控制文件知道数据库的恢复终点（on disk rba）；并且按照自己设定的路线恢复到了终点
数据库对不完全恢复的定义：
               1 控制文件 不知道数据库的恢复终点在哪里（例如：你拿备份的控制文件，里面的信息只记录到备份时刻）
                  这时恢复要使用recover database using backup controlfile;
               2 控制文件 知道数据库的恢复终点，但是， 控制文件无法到达终点（例如 ：日志断档 recover恢复到断档时而中止）
               3 控制文件 知道数据库的恢复终点，但是，是实例崩溃的实例恢复，又 因为ACTIVE的日志损坏，无法完成恢复，但是有归档，
                  这时DBA可以手动执行:recover database until cancel;让数据库找归档来恢复，此时，将实例恢复变成了介质恢复。
               4 控制文件 知道数据库的恢复终点， 但是 DBA不想恢复到终点（例如：数据库在操作时，把表给删除了，但只想恢复到删除表之前的位置）

不完全恢复一定要使用alter database open resetlogs;来打开数据库，但是数据可能丢失也可能不会丢失！
recover databse until ...;只要有until，一定是不完全恢复
recover database using backup controlfile;一定是不完全恢复


redo log的三种状态：
INACTIVE：表示数据已修改好，并且写到磁盘上/已经归档；
ACTIVE：表示数据已经修改好，并且已经归档，但日志中涉及到脏数据还没被DBWR写到磁盘上
CURRENT：表示正在修改数据。

===========实验1：INACTIVE状态的日志损坏=================
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------
1    1       2   52428800     1 NO  CURRENT     518838 23-APR-11
2    1       1   52428800     1 NO  INACTIVE     518704 23-APR-11
3    1       0   52428800     1 YES UNUSED  0

SQL> select * from v$logfile;

    GROUP# STATUS     TYPE    MEMBER    IS_
---------- ---------- ------- --------------------------------------------- ---
3      ONLINE  /u01/oracle/oradata/ora10g/redo03.log    NO
2      ONLINE  /u01/oracle/oradata/ora10g/redo02.log    NO
1      ONLINE  /u01/oracle/oradata/ora10g/redo01.log    NO

SQL> select l.group#,l.sequence#,l.status,lf.member from v$log l,v$logfile lf where l.group#=lf.group#;－－－－save log.sql

SQL> ! rm /u01/oracle/oradata/ora10g/redo02.log——损坏INACTIVE状态的日志redo02.log

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  285212672 bytes
Fixed Size    1218992 bytes
Variable Size   83887696 bytes
Database Buffers  197132288 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: '/u01/oracle/oradata/ora10g/redo02.log'

清洗当前日志组内的每个成员内容：
SQL> alter database clear logfile group 2;
Database altered.

SQL> alter database open;
Database altered.

========实验2：有归档， ACTIVE状态的redo损坏 库崩溃================================
方法一：
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- -------------------
1    1       5   52428800     1 YES ACTIVE     595065 2011-06-13 05:27:10
2    1       6   52428800     1 NO  CURRENT     595070 2011-06-13 05:27:11
3    1       4   52428800     1 YES ACTIVE     595058 2011-06-13 05:27:02


SQL> select * from v$logfile;
    GROUP# STATUS     TYPE    MEMBER    IS_
---------- ---------- ------- --------------------------------------------- ---
3      ONLINE  /u01/oracle/oradata/db20/redo03.log    NO
2      ONLINE  /u01/oracle/oradata/db20/redo02.log    NO
1      ONLINE  /u01/oracle/oradata/db20/redo01.log    NO


第一组和第三组 都是 active，损坏redo01.log日志文件
SQL> ! cp /etc/passwd /u01/oracle/oradata/db20/redo01.log

SQL> shut abort
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area  285212672 bytes
Fixed Size    1218992 bytes
Variable Size  113247824 bytes
Database Buffers  167772160 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: '/u01/oracle/oradata/db20/redo01.log'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
—— open时检测到日志文件头不对

尝试清洗日志：
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 db20 (thread 1)
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/db20/redo01.log'

SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- -------------------
1    1       5   52428800     1 YES ACTIVE     595065 2011-06-13 05:27:10
3    1       4   52428800     1 YES ACTIVE     595058 2011-06-13 05:27:02
2    1       6   52428800     1 NO  CURRENT     595070 2011-06-13 05:27:11
——检查日志组 发现第一组上一次状态就是ACTIVE时崩溃的

通过日志序列号得知 第一组之前的第三组日志中的记录也是需要恢复的 先恢复下

SQL> recover database;—— 报错原因是，此命令要去找redo，但此时redo已经损坏了
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/db20/redo01.log'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1

当恢复读取到redo01.log中时 文件格式不对
因为有归档  但数据并不认为此次恢复需要归档, 手动提供归档 

SQL> ! ls  /home/oracle/arc1/1_*_753686542.dbf —— 归档号到5结束
/home/oracle/arc1/1_1_753686542.dbf  /home/oracle/arc1/1_3_753686542.dbf  /home/oracle/arc1/1_5_753686542.dbf
/home/oracle/arc1/1_2_753686542.dbf  /home/oracle/arc1/1_4_753686542.dbf

SQL> recover database until cancel;——此时会直接找归档，而不去找redo了
ORA-00279: change 595065 generated at 06/13/2011 05:27:10 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/1_5_753686542.dbf
ORA-00280: change 595065 for thread 1 is in sequence #5
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 595070 generated at 06/13/2011 05:27:11 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/1_6_753686542.dbf
ORA-00280: change 595070 for thread 1 is in sequence #6
ORA-00278: log file '/home/oracle/arc1/1_5_753686542.dbf' no longer needed for this recovery
————5号文件这次恢复并没用到 因为我们之前做的recover database已经把这份日志应用过了,对应的就是group 3
下一个就是需要6号归档 但我们没有,实际就是current日志,还没切换数据库就崩溃了 所以没归档 提供current在线日志
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/db20/redo02.log
Log applied.
Media recovery complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

此时只能resetlogs起库 因为日志文件不在了， 没有丢数据 所有日志都应用了。
SQL> alter database open resetlogs;
Database altered.


方法二：
1.直接启动数据库到mount状态：
startup mount；

2.清洗unarchived的联机日志：
alter database clear unarchived logfile group 1；

3.打开数据库：
alter database open；

=======实验3：没归档，ACTIVE状态的REDO LOG损坏 库崩溃==========

利用隐含参数强制恢复

SQL> shut immediate
SQL> startup mount
SQL> alter database noarchivelog;－－无归档，无备份
Database altered.
SQL> alter database open;

产生交易 切换日志  数据库直接断电
SQL> select * from v$Log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- -------------------
1    1       8   52428800     1 NO  ACTIVE     617432 2011-06-13 05:47:27
2    1       9   52428800     1 NO  CURRENT     617437 2011-06-13 05:47:30
3    1       7   52428800     1 NO  ACTIVE     617420 2011-06-13 05:47:02
SQL> select * from v$Logfile;
    GROUP# STATUS     TYPE    MEMBER    IS_
---------- ---------- ------- --------------------------------------------- ---
3      ONLINE  /u01/oracle/oradata/db20/redo03.log    NO
2      ONLINE  /u01/oracle/oradata/db20/redo02.log    NO
1      ONLINE  /u01/oracle/oradata/db20/redo01.log    NO

损坏ACTIVE状态的日志redo01.log
SQL> ! cp /etc/passwd /u01/oracle/oradata/db20/redo01.log

断电：
SQL> shut abort
ORACLE instance shut down.

重启报错中：
SQL> startup
ORACLE instance started.
Total System Global Area  285212672 bytes
Fixed Size    1218992 bytes
Variable Size  117442128 bytes
Database Buffers  163577856 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: '/u01/oracle/oradata/db20/redo01.log'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1

恢复：
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/db20/redo01.log'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1

SQL> recover database until cancel;
ORA-00279: change 617432 generated at 06/13/2011 05:47:27 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/1_8_753687918.dbf
ORA-00280: change 617432 for thread 1 is in sequence #8
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/home/oracle/arc1/1_8_753687918.dbf'
ORA-27037: unable to obtain file status
Linux 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: '/u01/oracle/oradata/db20/system01.dbf'

查看没有可用的归档：
SQL> !  ls /home/oracle/arc1/1_[5-9]_753687918.dbf
ls: /home/oracle/arc1/1_[5-9]_753687918.dbf: 没有那个文件或目录
——数据库根本没归档 但数据库还根据 log_archive_dest_1 和 log_archive_format 参数的记录去找归档

借助隐藏参数 不要再恢复了,也不要redo了,直接启库(隐含参数屏敝了库的正常机制)
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.

SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  285212672 bytes
Fixed Size    1218992 bytes
Variable Size  117442128 bytes
Database Buffers  163577856 bytes
Redo Buffers    2973696 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open RESETLOGS;
Database altered.

打开数据库之后，取消隐含参数：
SQL> alter system set "_allow_resetlogs_corruption"=false scope=spfile;
System altered.

SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  285212672 bytes
Fixed Size    1218992 bytes
Variable Size  117442128 bytes
Database Buffers  163577856 bytes
Redo Buffers    2973696 bytes
Database mounted.
Database opened.


============实验4：有归档和备份的Current状态日志损坏=====================
SQL> select * from v$Log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- -------------------
1    1       5   52428800     1 YES ACTIVE     628547 2011-06-14 01:47:50
2    1       4   52428800     1 YES ACTIVE     628542 2011-06-14 01:47:49
3    1       6   52428800     1 NO  CURRENT     628553 2011-06-14 01:47:55

SQL> select * from v$Logfile;
    GROUP# STATUS     TYPE    MEMBER    IS_
---------- ---------- ------- --------------------------------------------- ---
3      ONLINE  /u01/oracle/oradata/db20/redo03.log    NO
2      ONLINE  /u01/oracle/oradata/db20/redo02.log    NO
1      ONLINE  /u01/oracle/oradata/db20/redo01.log    NO

损坏current状态的日志redo03.log:
SQL> ! cp /etc/passwd /u01/oracle/oradata/db20/redo03.log


模拟生产库 产生事务 ，日志切换一圈，current日志损坏，导致 LGWR进程无法写 数据库崩溃
alter system switch logfile;
ERROR:
ORA-03114: not connected to ORACLE
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
ERROR:
ORA-03114: not connected to ORACLE
ERROR:
ORA-03114: not connected to ORACLE
ERROR:
ORA-03114: not connected to ORACLE

SQL> startup
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist

退出重启数据库，查看报错信息：
SQL> exit
[oracle@sql ~]$ sql /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 14 01:48:31 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.
Total System Global Area  285212672 bytes
Fixed Size    1218992 bytes
Variable Size   79693392 bytes
Database Buffers  201326592 bytes
Redo Buffers    2973696 bytes
Database mounted.
ORA-00316: log 3 of thread 1, type 14900 in header is not log file
ORA-00312: online log 3 thread 1: '/u01/oracle/oradata/db20/redo03.log'

SQL> select * from v$Log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- -------------------
1    1       5   52428800     1 YES ACTIVE     628547 2011-06-14 01:47:50
3    1       6   52428800     1 NO  CURRENT     628553 2011-06-14 01:47:55
2    1       0   52428800     1 YES UNUSED  0 2011-06-14 01:47:49


报错的相关日志  正好是当前日志
有备份，关闭数据库，只将备份的数据文件取回来 千万别取其他文件

SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

只取回原来的数据文件
SQL>! cp db20.bak/*.dbf db20/

SQL> startup
ORACLE instance started.
Total System Global Area  285212672 bytes
Fixed Size    1218992 bytes
Variable Size   79693392 bytes
Database Buffers  201326592 bytes
Redo Buffers    2973696 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/oracle/oradata/db20/system01.dbf'

查看归档 只有一份 恢复应用到这个归档结束 就不要再去找下一份(current)了 
[oracle@sql oradata]$ ls /home/oracle/arc1/*_1_753759848.dbf
/home/oracle/arc1/1_1_753759848.dbf

SQL> recover database until cancel;
ORA-00279: change 627649 generated at 06/14/2011 01:44:32 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/1_1_753759848.dbf
ORA-00280: change 627649 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
auto——尽量恢复可以利用的归档日志
ORA-00279: change 628531 generated at 06/14/2011 01:47:43 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/1_2_753759848.dbf
ORA-00280: change 628531 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arc1/1_1_753759848.dbf' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;
Database altered.

或者，不取数据文件的情况：
1，直接加载数据库： startup mount;
2，进行恢复： recover database until cancel；输入auto，尽量恢复可以利用的日志，然后重新执行
                    recover database until cancel；输入cancel，完成不完全恢复

3，使用resetlogs打开数据库： alter database open resetlogs；
=======实验5 无备份的CURRENT日志损坏——强制恢复 ===========================

SQL> select * from v$Log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------
1    1       2   52428800     1 NO  CURRENT     520199 23-APR-11
2    1       1   52428800     1 YES ACTIVE     520035 23-APR-11
3    1       0   52428800     1 YES UNUSED  0

SQL> select * from v$logfile;
    GROUP# STATUS     TYPE    MEMBER    IS_
---------- ---------- ------- --------------------------------------------- ---
3      ONLINE  /u01/oracle/oradata/ora10g/redo03.log    NO
2      ONLINE  /u01/oracle/oradata/ora10g/redo02.log    NO
1      ONLINE  /u01/oracle/oradata/ora10g/redo01.log    NO

损坏：
SQL> ! rm /u01/oracle/oradata/ora10g/redo01.log

SQL> shut abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  285212672 bytes
Fixed Size    1218992 bytes
Variable Size   88082000 bytes
Database Buffers  192937984 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: '/u01/oracle/oradata/ora10g/redo01.log'
ORA-27037: unable to obtain file status
Linux 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 ora10g (thread 1)
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/ora10g/redo01.log'

数据文件不一致 恢复时一定要用这份日志
SQL> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/ora10g/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> recover database until cancel;
ORA-00279: change 520199 generated at 04/23/2011 06:29:17 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/2_1_749197565.arc
ORA-00280: change 520199 for thread 1 is in sequence #2
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: '/u01/oracle/oradata/ora10g/system01.dbf'
ORA-01112: media recovery not started


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

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: '/u01/oracle/oradata/ora10g/system01.dbf'

只能放弃日志 不完全恢复，利用隐含参数强制恢复：
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.

SQL> startup force
ORACLE instance started.
Total System Global Area  285212672 bytes
Fixed Size    1218992 bytes
Variable Size   88082000 bytes
Database Buffers  192937984 bytes
Redo Buffers    2973696 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
Database altered.

取消隐含参数：
SQL> alter system set "_allow_resetlogs_corruption"=false scope=spfile;
System altered.

SQL> shut immediate



注意：利用隐含参数打开数据库，如果运气好的话，数据库能正常打开，但是由于使用_allow_resetlogs_corruption打开的，会造成数据的丢失，并且数据库的状态不一致。
因此，这种情况下，建议通过exp方式导出数据库，然后再重建新数据库，再导入！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29101923/viewspace-1434158/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29101923/viewspace-1434158/