修复联机日志.docx

最近一直在弄RMAN的东西，因此最近的分享也以这方面为主。今天讨论下联机日志的事。联机日志一般来说是非常坚固的一个东西（我没说，ORACLE说的），但一旦断电，或者正在运行的服务器中了病毒破坏了这些文件，或者干脆被地震火灾炸弹给炸了呢？这时，我们需要不同情况不同处理，而对于联机日志损坏要根据日志状态进行分析。我们知道，联机日志一般会有Current、Active和Inactive三种状态。Inactive状态的日志不会造成数据丢失，因为已写入磁盘。而Active和Current状态的日志一般会造成数据丢失。下面分别用两个例子演示。这都是最常见的2种联机日志被损坏会的恢复手段。文章是张晓明写的，刚才又找出来，很经典实用的一篇，COPY之后直接分享给大家，以后遇到联机日志坏了之后，不需要再重建实例，直接恢复却可。实例1：Inactive状态的联机日志损坏。本实例的场景描述如下： 两个节点的RAC环境； 实例1正常工作，实例2启动失败； 检查后发现是联机日志丢失，并且丢失的日志状态时Inactive的。如果联机日志的状态是Inactive的，说明这个日志包含的数据修改都已经同步到数据文件中了，这个日志内容在Instance Recovery过程中不需要，直接drop就可以了。因为每个thread至少要有两个日志组，如果删除后少于两组，则需要先创建一组然后再删除。（1）模拟灾难场景。首先关闭所有实例，因为要在ASM上删除文件，如果文件正在被使用，ASM不会允许删除，所以要关闭数据库。启动实例1到MOUNT状态，查看日志分布：SQL select thread#,group#,status from v$log; THREAD# GROUP# STATUS - - - 1 1 INACTIVE 1 2 CURRENT 2 3 INACTIVE 2 4 CURRENT 当前操作的是实例1，所以删除实例1的日志组1，这个日志组的状态是Inactive的。export ORACLE_SID=+ASM1 asmcmd -p ASMCMD + ls DATA/ RECOVERY/ ASMCMD + cd data ASMCMD +data ls WXXRDB/ ASMCMD +data cd wxxrdb ASMCMD +data/wxxrdb ls CONTROLFILE/ DATAFILE/ ONLINELOG/ PARAMETERFILE/ TEMPFILE/ spfilewxxrdb.ora ASMCMD +data/wxxrdb cd onlinelog ASMCMD +data/wxxrdb/onlinelog ls group_1.266.651072779 group_2.265.651072781 group_3.258.651072783 group_4.257.651072785 ASMCMD +data/wxxrdb/onlinelog rm group_1.266.651072779 ASMCMD +data/wxxrdb/onlinelog ls group_2.265.651072781 group_3.258.651072783 group_4.257.651072785 ASMCMD +data/wxxrdb/onlinelog exit 启动实例：SQL alter database open; alter database open ERROR at line 1: ORA-03113: end-of-file on communication channel 查看日志和trace文件：more /oracle/admin/wxxrdb/udump/wxxrdb1_ora_19937.trc /oracle/admin/wxxrdb/udump/wxxrdb1_ora_19937.trc Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options ORACLE_HOME = /oracle/product/database System name: Linux Node name: dbs Release: 2.6.9-42.ELsmp Version: #1 SMP Wed Jul 12 23:32:02 EDT 2006 Machine: x86_64 Instance name: wxxrdb1 Redo thread mounted by this instance: 1 Oracle process number: 19 Unix process pid: 19937, image: oracledbs (TNS V1-V3) SERVICE NAME:() 2008-04-03 16:48:50.659 SESSION ID:(151.3) 2008-04-03 16:48:50.659 ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: +DATA/wxxrdb/onlinelog/group_1.266.651072779 ksuitm: waiting for 5 seconds before killing DIAG （2）在实例1中，添加一个日志组。对于Inactive状态的联机日志，可以直接删除。但因为每个thread至少要有两组日志，所以在删除之前，先添加一组，注意粗体部分的语法：SQL alter database add logfile thread 1 group 5; Database altered. （3）删除丢失的日志组：SQL alter database drop logfile group 1; Database altered. （4）打开数据库：SQL alter database open;Database altered. （5）做一次日志切换，确认新日志可用：SQL alter system switch logfile; System altered. SQL select thread#,group#,status from v$log; THREAD# GROUP# STATUS - - - 1 2 ACTIVE 2 3 INACTIVE 2 4 CURRENT 1 5 CURRENT 实例2：Current / Active状态的联机日志损坏。Active状态说明这个日志覆盖的修改还没有记录到数据文件，Current是数据库当前正在使用的日志。如果数据库正常关闭，关闭过程中Oracle会把这些日志覆盖的修改全部同步到数据文件，遇到没有提交的事务就回滚该事务，从而保证数据库内没有未决事务。所以如果正常关闭后，即使这些日志发生损坏，在启动过程中重建这些日志就可以，不会发生任何数据丢失。但如果是异常关闭，数据库中还有未决事务，需要使用这些日志进行恢复，这时日志的损坏就会造成数据丢失，所进行的就是不完全恢复。本实例的场景描述如下： 两个节点的RAC环境； 实例1正常工作，实例2启动失败； 检查后发现是联机日志丢失，并且丢失的日志状态是Current或Active的。（1）模拟灾难场景。启动实例2失败，查看实例2启动时的alert日志：Thu Apr 3 09:10:52 2008 Errors in file /oracle/admin/wxxrdb/bdump/wxxrdb2_lgwr_8036.trc: ORA-00314: log 3 of thread 2, expected sequence# 17 doesnt match 0 ORA-00312: online log 3 thread 2: +DATA/wxxrdb/onlinelog/group_3.265.650827703 Thu Apr 3 09:10:52 2008 Errors in file /oracle/admin/wxxrdb/bdump/wxxrdb2_lgwr_8036.trc: ORA-00314: log 3 of thread 2, expected sequence# 17 doesnt match 0 ORA-00312: online log 3 thread 2: +DATA/wxxrdb/onlinelog/group_3.265.650827703 Thu Apr 3 09:10:52 2008 Errors in file /oracle/admin/wxxrdb/udump/wxxrdb2_ora_8447.trc: ORA-00314: log 3 of thread 2, expected sequence# doesnt match ORA-00312: online log 3 thread 2: +DATA/wxxrdb/onlinelog/group_3.265.650827703 Thu Apr 3 09:10:52 2008 USER: terminating instance due to error 314 Thu Apr 3 09:10:52 2008 System state dump is made for local instance System State dumped to trace file /oracle/admin/wxxrdb/bdump/wxxrdb2_diag_7997.trc 查看trc日志：cat /oracle/admin/wxxrdb/bdump/wxxrdb2_lgwr_8036.trc /oracle/admin/wxxrdb/bdump/wxxrdb2_lgwr_8036.trc Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options ORACLE_HOME = /oracle/product/database System name: Linux Node name: dbp Release: 2.6.9-42.ELsmp Version: #1 SMP Wed Jul 12 23:32:02 EDT 2006 Machine: x86_64 Instance name: wxxrdb2 Redo thread mounted by this instance: 2 Oracle process number: 11 Unix process pid: 8036, image: oracledbp (LGWR) 2008-04-03 09:10:52.343 SERVICE NAME:() 2008-04-03 09:10:52.342 SESSION ID:(160.1) 2008-04-03 09:10:52.342 ORA-00314: log 3 of thread 2, expected sequence# 17 doesnt match 0 ORA-00312: online log 3 thread 2: +DATA/wxxrdb/onlinelog/group_3.265.650827703 ORA-00314: log 3 of thread 2, expected sequence# 17 doesnt match 0 ORA-00312: online log 3 thread 2: +DATA/wxxrdb/onlinelog/group_3.265.650827703 所有的线索都证明实例2的日志组3被破坏。（2）确认日志状态。在实例1上查看日志状态：SQL select group#,thread#,archived,status from v$log; GROUP# THREAD# ARC STATUS - - - - 1 1 NO CURRENT 2 1 YES INACTIVE 3 2 YES INACTIVE 4 2 NO CURRENT 日志组3已经被归档，状态是Inactive，这个日志内容在instance recovery时不需要。这种日志可以简单地清空。（3）第一次尝试清空Inactive的联机日志。注意这个时候，实例1是OPEN的。在实例2上执行：SQL startup mount; ORACLE instance started. Total System Global Area 2147483648 bytes Fixed Size 2022144 bytes Variable Size 503317760 bytes Database Buffers 1627389952 bytes Redo Buffers 14753792 bytes Database mounted. 试图清空：SQL alter database clear logfile group 3; alter database clear logfile group 3 ERROR at line 1: ORA-01138: database must either be open in this instance or not at all 这个错误的解释：01138, 00000, database must either be open in this instance or not at all / Cause: The requested operation can not be done when the database is / mounted but not open in this instance, and another instance has / the database open. / Action: Execute the operation in an open instance, open the datbase in / this instance, or close the database in the other instances. 根据这个提示在实例1上执行。SQL alter database clear logfile group 3; Database altered. （4）尝试启动实例2。在实例2上执行：SQL select group#,thread#,sequence#,archived,status ,members from v$log; GROUP# THREAD# SEQUENCE# ARC STATUS MEMBERS - - - - - - 1 1 29 NO CURRENT 1 2 1 28 YES INACTIVE 1 3 2 0 YES UNUSED 1 4 2 18 NO CURRENT 1 日志组3确实被清空了：SQL alter database open; alter database open ERROR at line 1: ORA-03113: end-of-file on communication channel 查看实例2的alert日志：ORA-00314: log 4 of thread 2, expected sequence# doesnt match ORA-00312: online log 4 thread 2: +DATA/wxxrdb/onlinelog/group_4.266.650827705 Thu Apr 3 09:24:53 2008 USER: terminating instance due to error 314 Thu Apr 3 09:24:53 2008 System state dump is made for local instance System State dumped to trace file /oracle/admin/wxxrdb/bdump/wxxrdb2_diag_21734.trc （5）尝试清空Current的联机日志。从日志可见，日志组4也丢失了，但是注意，日志组4是实例2的Current日志，无论用哪种方法试图清空都失败。于是在实例1执行：SQL alter database clear logfile group 4; alter database clear logfile group 4 ERROR at line 1: ORA-00350: log 4 of instance wxxrdb2 (thread 2) needs to be archived ORA-00312: online log 4 thread 2: +DATA/wxxrdb/onlinelog/group_4.266.650827705 SQL alter database clear unarchived logfile group 4; alter database clear unarchived logfile group 4 ERROR at line 1: ORA-00322: log 4 of thread 2 is not current copy ORA-00312: online log 4 thread 2: +DATA/wxxrdb/onlinelog/group_4.266.650827705 甚至在实例1 MOUNT状态下，执行清空也失败。SQL shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL startup mount; ORACLE instance started. Total System Global Area 2147483648 bytes Fixed Size 2022144 bytes Variable Size 503317760 bytes Database Buffers 1627389952 bytes Redo Buffers 14753792 bytes Database mounted. SQL alter database clear unarchived logfile group 4; alter database clear unarchived logfile group 4 ERROR at line 1: ORA-00322: log 4 of thread 2 is not current copy ORA-00312: online log 4 thread 2: +DATA/wxxrdb/onlinelog/group_4.266.650827705 （6）解决Current、Active联机日志损坏。首先关闭所有实例：SQLshutdown immediate; 在发生日志损坏的实例启动到MOUNT，本例中是实例2；在节点2执行：SQL startup mount; ORACLE instance started. Total System Global Area 2147483648 bytes Fixed Size 2022144 bytes Variable Size 503317760 bytes Database Buffers 1627389952 bytes Redo Buffers 14753792 bytes Database mounted. 然后执行alter database open resetlogs语句。第一次执行，