OnlineRedoLog损坏处理实验(中)

下面我们进行更复杂情况的处理,就是非一致性关闭的情况下日志损坏的情况。这种情况是很有实际意义的。因为在出现错误的时候,Oracle可能不会允许一致性关闭。没有经验的处理人员往往会贸然的强制关闭,我们获得到的场景往往也是非一致的情况。 

员工经过长期磨合与沉淀,具备了协作精神,得以通过团队的力量开发出优质的产品。创新互联坚持“专注、创新、易用”的产品理念,因为“专注所以专业、创新互联网站所以易用所以简单”。公司专注于为企业提供成都做网站、网站设计、微信公众号开发、电商网站开发,小程序设计,软件按需求定制网站等一站式互联网企业服务。

 

 

4、非一致性关闭非当前日志组处理——Inactive日志组

 

非一致性关闭情况下,online日志组全员损坏是很复杂的,也是潜在会有数据损失的。我们这里说的非一致性关闭,就是shutdown abort或者强制断电之后。由于Oracle数据库采用写日志在先的策略,关闭点上我们不能保证脏块(Dirty Block)全都被写入到数据文件,各个数据文件和控制文件在SCN时间上保证一致性。

 

但是当数据库重新启动时,在open阶段,Oracle会进行instance recovery,从最后一个增量检查点check point追起,重演事务并且回滚事务。Instance Recovery过程也被称为前滚回滚过程,或者成为Cache RecoveryTransaction Recovery过程。

 

如果进行不一致关闭就会需要进行instance recovery,进行instance recovery最需要的内容就是online redo log。如果我们删除online redo log group恰好是进行instance recovery所需要的,那么我们就只能牺牲掉一部分数据,进行不完全恢复。

 

我们删除的非当前日志组也有很大风险,因为在日志切换成非当前日志之后,日志组有一段时间对应脏块是不能写入到数据文件中的。这也就是日志文件的active状态。本节讨论非当前日志成员被删除的情况,具体划分为Inactive日志组和非Inactive日志组。

 

我们下面实验选择Oracle 11gLinux版本进行。

 

 

SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE       11.2.0.1.0        Production

 

 

当前日志情况如下:

 

 

 

SQL> select group#, status, archived, sequence# from v$log;

 

   GROUP# STATUS          ARCHIVED SEQUENCE#

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

        1 INACTIVE        YES            108

        2 CURRENT         NO             110

        3 INACTIVE        YES            109

 

 

SQL> drop table t;

Table dropped

 

SQL> create table t as select * from dba_objects;

Table created

 

SQL> select group#, status, member from v$logfile;

 

   GROUP# STATUS MEMBER

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

        3        /u01/oradata/WILSON/onlinelog/o1_mf_3_7xt456o0_.log

        3        /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_3_7xt45bvp_.log

        2        /u01/oradata/WILSON/onlinelog/o1_mf_2_7xt44w3k_.log

        2        /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_7xt450rv_.log

        1        /u01/oradata/WILSON/onlinelog/o1_mf_1_870jlj6v_.log

        1        /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_1_870jljvk_.log

 

6 rows selected

 

强制停机之后,删除日志组1对象。

 

 

[oracle@bspdev ~]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 22:27:57 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

 

SQL> conn / as sysdba    

Connected.

SQL> shutdown abort

ORACLE instance shut down.

 

[oracle@bspdev ~]$ mv  /u01/oradata/WILSON/onlinelog/o1_mf_1_870jlj6v_.log  /u01/oradata/WILSON/onlinelog/redogroup1.log

[oracle@bspdev ~]$ rm /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_1_870jljvk_.log

[oracle@bspdev ~]$

 

 

重新启动之后,无法找到对应的日志文件,直接报错。

 

 

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

 

Total System Global Area 849530880 bytes

Fixed Size                 1339824 bytes

Variable Size            562040400 bytes

Database Buffers         281018368 bytes

Redo Buffers               5132288 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 5059

Session ID: 1 Serial number: 5

 

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/diag/rdbms/wilson/wilson/trace/wilson_ora_5059.trc:

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

ORA-00312: online log 1 thread 1: '/u01/oradata/WILSON/onlinelog/o1_mf_1_870jlj6v_.log'

ORA-00312: online log 1 thread 1: '/u01/flash_recovery_area/WILSON/onlinelog/o1_mf_1_870jljvk_.log'

USER (ospid: 5059): terminating the instance due to error 313

Instance terminated by USER, pid = 5059

 

 

重新启动到mount状态,进行处理。

 

 

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area 849530880 bytes

Fixed Size                 1339824 bytes

Variable Size            562040400 bytes

Database Buffers         281018368 bytes

Redo Buffers               5132288 bytes

Database mounted.

SQL> select open_mode from v$database;

 

OPEN_MODE

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

MOUNTED

 

SQL> select group#, status, archived, sequence# from v$log;

 

   GROUP# STATUS          ARC SEQUENCE#

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

        1 INACTIVE        YES       108

        3 INACTIVE        YES       109

        2 CURRENT         NO        110

 

SQL> select checkpoint_change#, last_change# from v$datafile;

 

CHECKPOINT_CHANGE# LAST_CHANGE#

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

          1950243     1950243

          1950243     1950243

          1950243     1950243

          1950243     1950243

          1950243     1950243

          1950243     1950243

          1950243     1950243

 

7 rows selected.

 

SQL> select checkpoint_change# from v$datafile_header;

 

CHECKPOINT_CHANGE#

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

          1950243

          1950243

          1950243

          1950243

          1950243

          1950243

          1950243

 

7 rows selected.

 

SQL>

 

 

虽然启动失效,但是我们没有看到很严重的一致性问题。我们尝试启动数据库。

 

 

SQL> alter database clear logfile group 1;

Database altered.

 

SQL> alter database open;

Database altered.

 

SQL> select group#, status, archived, sequence# from v$log;

 

   GROUP# STATUS          ARCHIVED SEQUENCE#

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

        1 CURRENT         NO             111

        2 INACTIVE        YES            110

        3 INACTIVE        YES            109

 

 

 

这种场景是比较好处理的。下面我们讨论如果是Active状态的日志被删除如何?

 

5、非一致性关闭非当前日志组处理——Active日志组

 

我们构造出Active日志组对象。

 

 

SQL> select group#, status, member from v$logfile;

 

   GROUP# STATUS MEMBER

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

        3        /u01/oradata/WILSON/onlinelog/o1_mf_3_7xt456o0_.log

        3        /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_3_7xt45bvp_.log

        2        /u01/oradata/WILSON/onlinelog/o1_mf_2_7xt44w3k_.log

        2        /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_7xt450rv_.log

        1        /u01/oradata/WILSON/onlinelog/o1_mf_1_870kg322_.log

        1        /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_1_870kg3mr_.log

 

6 rows selected

 

SQL> alter system switch logfile;

System altered

 

 

SQL> alter system switch logfile;

 

System altered

 

SQL> select group#, status, archived, sequence# from v$log;

 

   GROUP# STATUS          ARCHIVED SEQUENCE#

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

        1 CURRENT         NO             114

        2 ACTIVE          YES            113

        3 INACTIVE        YES            112

 

之后强制关闭数据库,删除对应的日志组2

 

 

SQL> shutdown abort;

ORACLE instance shut down.

SQL>

 

[oracle@bspdev trace]$ mv /u01/oradata/WILSON/onlinelog/o1_mf_2_7xt44w3k_.log /u01/oradata/WILSON/onlinelog/redogroup2.log

[oracle@bspdev trace]$ rm /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_7xt450rv_.log

 

 

重新启动数据库。

 

 

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

 

Total System Global Area 849530880 bytes

Fixed Size                 1339824 bytes

Variable Size            562040400 bytes

Database Buffers         281018368 bytes

Redo Buffers               5132288 bytes

Database mounted.

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

ORA-00312: online log 2 thread 1:

'/u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_7xt450rv_.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 2 thread 1:

'/u01/oradata/WILSON/onlinelog/o1_mf_2_7xt44w3k_.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

SQL> select open_mode from v$database;

 

OPEN_MODE

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

MOUNTED

 

 

检查文件状态情况。

 

 

 

SQL> select checkpoint_change#, last_change# from v$datafile;

 

CHECKPOINT_CHANGE# LAST_CHANGE#

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

          1970578

          1970578

          1970578

          1970578

          1970578

          1970578

          1970578

 

7 rows selected.

 

SQL> select checkpoint_change# from v$datafile_header;

 

CHECKPOINT_CHANGE#

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

          1970578

          1970578

          1970578

          1970578

          1970578

          1970578

          1970578

 

7 rows selected.

 

 

v$datafile的情况,我们就可以看出关闭是不完全关闭情况。我们只能尝试进行恢复。

 

 

SQL> recover database;

ORA-00279: change 1970578 generated at 10/06/2012 22:59:02 needed for thread 1

ORA-00289: suggestion :

/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_113_870kwzkl_.arc

ORA-00280: change 1970578 for thread 1 is in sequence #113

 

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

cancel

Media recovery cancelled.

 

 

提出恢复使用sequence#=113的日志,但是这个恰恰是被删除的对象。所以,只能退而求其次选择使用不完全恢复。

 

 

SQL> recover database until cancel;

ORA-00279: change 1970578 generated at 10/06/2012 22:59:02 needed for thread 1

ORA-00289: suggestion :

/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_113_870kwzkl_.arc

ORA-00280: change 1970578 for thread 1 is in sequence #113

 

 

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

auto

ORA-00279: change 1970779 generated at 10/06/2012 23:00:47 needed for thread 1

ORA-00289: suggestion :

/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_114_%u_.arc

ORA-00280: change 1970779 for thread 1 is in sequence #114

ORA-00278: log file

'/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_113_870kwzkl_.arc

' no longer needed for this recovery

 

 

ORA-00308: cannot open archived log

'/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_114_%u_.arc'

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/oradata/WILSON/datafile/o1_mf_system_7xt3yzhj_.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:

'/u01/oradata/WILSON/datafile/o1_mf_system_7xt3yzhj_.dbf'

 

 

尝试使用RMAN SCN方式打开。

 

 

SQL> select group#, sequence#, first_change# from v$log;

 

   GROUP# SEQUENCE# FIRST_CHANGE#

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

        1       114      1970779

        3       112      1950574

        2       113      1970577

 

 

RMAN> recover database until sequence 112;

 

Starting recover at 06-OCT-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 10/06/2012 23:19:42

RMAN-06556: datafile 1 must be restored from backup older than SCN 1950574

 

 

System文件的日期已经超过了recover范畴。只能尝试利用RMAN备份进行还原。

 

 

RMAN> restore database;

 

Starting restore at 06-OCT-12

using channel ORA_DISK_1

 

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/WILSON/datafile/o1_mf_system_7xt3yzhj_.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/WILSON/datafile/o1_mf_sysaux_7xt3yzkb_.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/WILSON/datafile/o1_mf_users_805nxydh_.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/oradata/WILSON/datafile/o1_mf_example_7xt46m9x_.dbf

channel ORA_DISK_1: restoring datafile 00006 to /u01/oradata/WILSON/datafile/o1_mf_nbscommo_820frtg1_.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/oradata/WILSON/datafile/o1_mf_nbscommo_820ft5y5_.dbf

channel ORA_DISK_1: reading from backup piece /u01/flash_recovery_area/WILSON/backupset/2012_10_06/o1_mf_nnndf_TAG20121006T220912_870gwjoy_.bkp

channel ORA_DISK_1: piece handle=/u01/flash_recovery_area/WILSON/backupset/2012_10_06/o1_mf_nnndf_TAG20121006T220912_870gwjoy_.bkp tag=TAG20121006T220912

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:02:46

Finished restore at 06-OCT-12

 

 

--进行部分恢复

RMAN> recover database until sequence 112;

 

Starting recover at 06-OCT-12

using channel ORA_DISK_1

 

starting media recovery

 

archived log for thread 1 with sequence 106 is already on disk as file /u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_106_870gzbfg_.arc

archived log for thread 1 with sequence 107 is already on disk as file /u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_107_870jlysq_.arc

archived log for thread 1 with sequence 108 is already on disk as file /u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_108_870jo98m_.arc

archived log for thread 1 with sequence 109 is already on disk as file /u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_109_870jx2gj_.arc

archived log for thread 1 with sequence 110 is already on disk as file /u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_110_870kgmdp_.arc

archived log for thread 1 with sequence 111 is already on disk as file /u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_111_870knhwb_.arc

archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_106_870gzbfg_.arc thread=1 sequence=106

archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_107_870jlysq_.arc thread=1 sequence=107

archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_108_870jo98m_.arc thread=1 sequence=108

archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_109_870jx2gj_.arc thread=1 sequence=109

archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_110_870kgmdp_.arc thread=1 sequence=110

archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_111_870knhwb_.arc thread=1 sequence=111

media recovery complete, elapsed time: 00:00:20

Finished recover at 06-OCT-12

 

 

明显,得益于归档模式和备份集合。我们似乎成功的完成了recover过程。下面我们尝试开启数据库,注意这里已经是incomplete恢复了,我们必须承担这部分的数据损失。

 

 

SQL> conn / as sysdba

Connected.

SQL> alter database open resetlogs;

Database altered.

 

SQL> select group#, status, member from v$logfile;

 

   GROUP# STATUS MEMBER

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

        3        /u01/oradata/WILSON/onlinelog/o1_mf_3_870n4lsg_.log

        3        /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_3_870n4o31_.log

        2        /u01/oradata/WILSON/onlinelog/o1_mf_2_870n48hc_.log

        2        /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_870n4dtl_.log

        1        /u01/oradata/WILSON/onlinelog/o1_mf_1_870n42n1_.log

        1        /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_1_870n44z3_.log

 

6 rows selected

 

SQL> select group#, status, archived, sequence# from v$log;

 

   GROUP# STATUS          ARCHIVED SEQUENCE#

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

        1 CURRENT         NO               1

        2 UNUSED          YES              0

        3 UNUSED          YES              0

 

 

总结:当我们incomplete关闭数据库,又删除了Active状态日志组成员之后,我们必须要承担对应的数据损失,一部分数据可能因此丢失。笔者之后的处理,是借助之前rman的一个备份集合,进行非完全恢复到最后一个可以找到的日志文件组。这里,我们再次强调归档模式和备份的重要性。

 


网页题目:OnlineRedoLog损坏处理实验(中)
本文链接:http://scyanting.com/article/pediod.html