ACTIVE日志损坏（有归档）

归档模式下active日志损坏

active代表日志切换过可能已经归档；也可能代表日志中的脏块还没有被DBWR写盘

查看归档路径

SQL> show parameter recovery



NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /u01/tiger/flash_recovery_area

db_recovery_file_dest_size           big integer 2G

recovery_parallelism                 integer     0

如果是刚创建的数据库那么就会出现unused状态

SQL> select * from v$log;



   GROUP#    THREAD#  SEQUENCE#     BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

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

        1          1          2  52428800          1 NO  CURRENT                452423 07-SEP-12

        2          1          0  52428800          1 YES UNUSED                       0

        3          1          1  52428800          1 YES INACTIVE                446075 07-SEP-12

第一，查看日志的状态和对应的日志文件

SQL> select * from v$logfile;



   GROUP# STATUS  TYPE    MEMBER                                   IS_

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

        3         ONLINE  /u01/tiger/oradata/orcl/redo03.log       NO

        2         ONLINE  /u01/tiger/oradata/orcl/redo02.log       NO

        1         ONLINE  /u01/tiger/oradata/orcl/redo01.log       NO



SQL> select * from v$log;



   GROUP#    THREAD#  SEQUENCE#     BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

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

        1          1          2  52428800          1 YES ACTIVE                  460765 07-SEP-12

        2          1          3  52428800          1 NO  CURRENT                 460767 07-SEP-12

        3          1          1  52428800          1 YES ACTIVE                  459268 07-SEP-12

第二破坏日志做测试

SQL> !cp /etc/passwd/u01/tiger/oradata/orcl/redo01.log

SQL> conn /as sysdba

Connected.

SQL> shut abort

Oracle instance shut down.

SQL> startup

ORACLE instance started.



Total System Global Area  314572800 bytes

Fixed Size                  1219136 bytes

Variable Size             104859072 bytes

Database Buffers          201326592 bytes

Redo Buffers                7168000 bytes

Database mounted.

ORA-00313: open failed for members of loggroup 1 of thread 1

ORA-00312: online log 1 thread 1:'/u01/tiger/oradata/orcl/redo01.log'

ORA-27046: file size is not a multiple oflogical block size

Additional information: 1



第三恢复日志

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

ORA-00312: online log 1 thread 1:'/u01/tiger/oradata/orcl/redo01.log'

------当我们清洗日志组的时候发现因为视力恢复的时候需要日志组1所以清洗不了

SQL> select * from v$log;



   GROUP#    THREAD#  SEQUENCE#     BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

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

        1          1          2  52428800          1 YES ACTIVE                  460765 07-SEP-12

        3          1          1  52428800          1 YES ACTIVE                  459268 07-SEP-12

        2          1          3  52428800          1 NO  CURRENT                 460767 07-SEP-12

------检查日志组的状态发现是在active的时候损坏的

SQL> recover database;

ORA-00283: recovery session canceled due toerrors

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

ORA-00312: online log 1 thread 1:'/u01/tiger/oradata/orcl/redo01.log'

ORA-27046: file size is not a multiple oflogical block size

Additional information: 1

------当恢复读取日志组1的redo01.dbf的时候格式不对打开失败

因为有归档但是数据不认为此次恢复需要归档，我们手工提供归档

SQL> recover database until cancel;

ORA-00279: change 460765 generated at09/07/2012 13:32:38 needed for thread 1

ORA-00289: suggestion :/u01/tiger/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_2_%u_.arc

ORA-00280: change 460765 for thread 1 is insequence #2





Specify log: {<RET>=suggested |filename | AUTO | CANCEL}



ORA-00279: change 460767 generated at09/07/2012 13:32:40 needed for thread 1

ORA-00289: suggestion :/u01/tiger/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_3_%u_.arc

ORA-00280: change 460767 for thread 1 is insequence #3

ORA-00278: log file'/u01/tiger/flash_recovery_area/ORCL/archivelog/2012_09_07/o1_mf_1_2_84m1qrxr_.arc'no longer neededfor this recovery



------2号文件我们没有用到因为刚才recover databas恢复的时候应用了2号日志文件对应的是第三组下一个是3号日志文件实际上就是current日志还没有来得及切换就已经崩溃了所以没有归档我们提供在线的current日志，我们的在线日志是第二组里的日志文件

Specify log: {<RET>=suggested |filename | AUTO | CANCEL}

/u01/tiger/oradata/orcl/redo02.log

Log applied.

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS orNORESETLOGS option for database open





SQL> alter database open resetlogs;



Database altered.