【DG】错误的log_file_name_convert参数导致物理Data Guard配置故障分析与处理

在同一台主机上部署物理DataGuard，或部署的DataGuard对应的数据库文件、日志文件目录不一致时，便会使用到db_file_name_convert参数和log_file_name_convert参数。 本文讨论当log_file_name_convert参数未设置或者设置不当而导致的Data Guard部署故障场景及处理分析过程。   关于同一台主机部署物理Data Guard的方法请参考文章《【DataGuard】同一台主机实现物理Data Guard配置安装》（http://space.itpub.net/519536/viewspace-578181）。   这里有关Data Guard配置过程中需要准备的数据文件、日志文件以及Standby控制文件均已准备完毕，准备方法这里不赘述。这里我们将备库pfile文件中的log_file_name_convert参数内容注释掉。 1.调整后的备库pfile内容 secdg@secdb /home/oracle$ cat $ORACLE_HOME/dbs/initsecdg.ora *.audit_file_dest='/u01/app/oracle/admin/secdg/adump' *.background_dump_dest='/u01/app/oracle/admin/secdg/bdump' *.control_files='/u01/app/oracle/oradata/secdg/cfile/control01.ctl','/u01/app/oracle/oradata/secdg/cfile/control02.ctl','/u01/app/oracle/oradata/secdg/cfile/control03.ctl' *.core_dump_dest='/u01/app/oracle/admin/secdg/cdump' *.db_file_multiblock_read_count=8# SMALL *.db_files=80# SMALL *.db_name='secdb' *.global_names=TRUE *.job_queue_processes=5 *.log_buffer=32768# SMALL *.log_checkpoint_interval=10000 *.max_dump_file_size='10240'# limit trace file size to 5 Meg each *.parallel_max_servers=5# SMALL *.processes=200# SMALL *.sga_max_size=300M *.sga_target=300M *.undo_management='AUTO' *.user_dump_dest='/u01/app/oracle/admin/secdg/udump' ## Parameters for Standby Database. db_name='secdb' db_unique_name=secdg log_archive_format=log%t_%s_%r.arc log_archive_config='DG_CONFIG=(secdb,secdg)' fal_server=secdb fal_client=secdg standby_file_management=AUTO db_file_name_convert='/u01/app/oracle/oradata/secdb/dfile/','/u01/app/oracle/oradata/secdg/dfile/' #log_file_name_convert='/u01/app/oracle/oradata/secdb/lfile/','/u01/app/oracle/oradata/secdg/lfile/' standby_archive_dest='/home/oracle/arch/secdg' ## Parameters which using for switch over from Standby to Primary. LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/arch/secdg VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=secdg' LOG_ARCHIVE_DEST_2='SERVICE=secdb LGWR SYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=secdb' log_archive_dest_state_1=ENABLE log_archive_dest_state_2=ENABLE remote_login_passwordfile=EXCLUSIVE 注意，这里我们注释掉了“log_file_name_convert”参数，表示在启动Data Guard库的时候日志文件将不被转换。看一下结果。 2.启动备库物理Data Guard secdb@secdb /home/oracle$ export ORACLE_SID=secdg secdg@secdb /home/oracle$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 17 18:11:48 2011 Copyright (c) 1982, 2005, Oracle.  All rights reserved. Connected to an idle instance. NotConnected@> startup nomount ORACLE instance started. Total System Global Area  314572800 bytes Fixed Size                  1219184 bytes Variable Size              96470416 bytes Database Buffers          213909504 bytes Redo Buffers                2973696 bytes NotConnected@> alter database mount standby database; Database altered. 3.确认log_file_name_convert参数信息 NotConnected@> show parameter convert NAME                   TYPE    VALUE ---------------------- ------- ------------------------------------- db_file_name_convert   string  /u01/app/oracle/oradata/secdb/dfile/,                                /u01/app/oracle/oradata/secdg/dfile/ log_file_name_convert  string 此处显示“log_file_name_convert”内容为空，表示未设置。 4.由log_file_name_convert引发的配置故障结果 NotConnected@> col GROUP# for 999999 NotConnected@> col STATUS for a5 NotConnected@> col TYPE for a7 NotConnected@> col MEMBER for a50 NotConnected@> select * From v$logfile order by 1,4;  GROUP# STATU TYPE    MEMBER                                             IS_ ------- ----- ------- -------------------------------------------------- ---       1 STALE ONLINE  /u01/app/oracle/oradata/secdb/lfile/redo01.log     NO       1 STALE ONLINE  /u01/app/oracle/oradata/secdb/lfile/redo01_b.log   NO       2       ONLINE  /u01/app/oracle/oradata/secdb/lfile/redo02.log     NO       2       ONLINE  /u01/app/oracle/oradata/secdb/lfile/redo02_b.log   NO       3       ONLINE  /u01/app/oracle/oradata/secdb/lfile/redo03.log     NO       3       ONLINE  /u01/app/oracle/oradata/secdb/lfile/redo03_b.log   NO       4       ONLINE  /u01/app/oracle/oradata/secdb/lfile/redo04_a.log   NO       4       ONLINE  /u01/app/oracle/oradata/secdb/lfile/redo04_b.log   NO       5       ONLINE  /u01/app/oracle/oradata/secdb/lfile/redo05_a.log   NO       5       ONLINE  /u01/app/oracle/oradata/secdb/lfile/redo05_b.log   NO       6       STANDBY /u01/app/oracle/oradata/secdb/lfile/log6a.rdo      NO       6       STANDBY /u01/app/oracle/oradata/secdb/lfile/log6b.rdo      NO       7       STANDBY /u01/app/oracle/oradata/secdb/lfile/log7a.rdo      NO       7       STANDBY /u01/app/oracle/oradata/secdb/lfile/log7b.rdo      NO       8       STANDBY /u01/app/oracle/oradata/secdb/lfile/log8a.rdo      NO       8       STANDBY /u01/app/oracle/oradata/secdb/lfile/log8b.rdo      NO       9       STANDBY /u01/app/oracle/oradata/secdb/lfile/log9a.rdo      NO       9       STANDBY /u01/app/oracle/oradata/secdb/lfile/log9b.rdo      NO      10       STANDBY /u01/app/oracle/oradata/secdb/lfile/log10a.rdo     NO      10       STANDBY /u01/app/oracle/oradata/secdb/lfile/log10b.rdo     NO      11       STANDBY /u01/app/oracle/oradata/secdb/lfile/log11a.rdo     NO      11       STANDBY /u01/app/oracle/oradata/secdb/lfile/log11b.rdo     NO 22 rows selected. 这里便体现了这种配置的问题，由于没有设置log_file_name_convert参数，导致备库使用到的日志文件不正确，这里显示的是主库日志信息！ 为方便比较，正确的内容如下： NotConnected@> select * From v$logfile order by 1,4;  GROUP# STATU TYPE    MEMBER                                             IS_ ------- ----- ------- -------------------------------------------------- ---       1       ONLINE  /u01/app/oracle/oradata/secdg/lfile/redo01.log     NO       1       ONLINE  /u01/app/oracle/oradata/secdg/lfile/redo01_b.log   NO       2       ONLINE  /u01/app/oracle/oradata/secdg/lfile/redo02.log     NO       2       ONLINE  /u01/app/oracle/oradata/secdg/lfile/redo02_b.log   NO       3       ONLINE  /u01/app/oracle/oradata/secdg/lfile/redo03.log     NO       3       ONLINE  /u01/app/oracle/oradata/secdg/lfile/redo03_b.log   NO       4       ONLINE  /u01/app/oracle/oradata/secdg/lfile/redo04_a.log   NO       4       ONLINE  /u01/app/oracle/oradata/secdg/lfile/redo04_b.log   NO       5       ONLINE  /u01/app/oracle/oradata/secdg/lfile/redo05_a.log   NO       5       ONLINE  /u01/app/oracle/oradata/secdg/lfile/redo05_b.log   NO       6       STANDBY /u01/app/oracle/oradata/secdg/lfile/log6a.rdo      NO       6       STANDBY /u01/app/oracle/oradata/secdg/lfile/log6b.rdo      NO       7       STANDBY /u01/app/oracle/oradata/secdg/lfile/log7a.rdo      NO       7       STANDBY /u01/app/oracle/oradata/secdg/lfile/log7b.rdo      NO       8       STANDBY /u01/app/oracle/oradata/secdg/lfile/log8a.rdo      NO       8       STANDBY /u01/app/oracle/oradata/secdg/lfile/log8b.rdo      NO       9       STANDBY /u01/app/oracle/oradata/secdg/lfile/log9a.rdo      NO       9       STANDBY /u01/app/oracle/oradata/secdg/lfile/log9b.rdo      NO      10       STANDBY /u01/app/oracle/oradata/secdg/lfile/log10a.rdo     NO      10       STANDBY /u01/app/oracle/oradata/secdg/lfile/log10b.rdo     NO      11       STANDBY /u01/app/oracle/oradata/secdg/lfile/log11a.rdo     NO      11       STANDBY /u01/app/oracle/oradata/secdg/lfile/log11b.rdo     NO 22 rows selected. 5.尝试主备库Switch Over后在secdg实例切换日志 secdg实例的日志中将会记在如下报错 Fri Jun 17 22:51:56 2011 Errors in file /u01/app/oracle/admin/secdg/bdump/secdg_lgwr_1165.trc: ORA-00314: log 2 of thread 1, expected sequence# 44 doesn't match 0 ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/secdb/lfile/redo02_b.log' ORA-00314: log 2 of thread 1, expected sequence# 44 doesn't match 0 ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/secdb/lfile/redo02.log' Fri Jun 17 22:51:56 2011 Errors in file /u01/app/oracle/admin/secdg/bdump/secdg_lgwr_1165.trc: ORA-00314: log 2 of thread 1, expected sequence# 44 doesn't match 0 ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/secdb/lfile/redo02_b.log' ORA-00314: log 2 of thread 1, expected sequence# 44 doesn't match 0 ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/secdb/lfile/redo02.log' Fri Jun 17 22:51:56 2011 LGWR: terminating instance due to error 314 Instance terminated by LGWR, pid = 1165 这便是由于log_file_name_convert参数没有设置导致备库错误地用到了“原主库日志文件”的严重后果！ secdg数据库实例因此而被杀死！尝试重新启动的话，仍然会报同样的错误。 secdg@secdb /home/oracle$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 17 23:00:47 2011 Copyright (c) 1982, 2005, Oracle.  All rights reserved. Connected to an idle instance. NotConnected@> startup; ORACLE instance started. Total System Global Area  314572800 bytes Fixed Size                  1219184 bytes Variable Size              96470416 bytes Database Buffers          213909504 bytes Redo Buffers                2973696 bytes Database mounted. ORA-00314: log 1 of thread 1, expected sequence# 43 doesn't match 0 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/secdb/lfile/redo01_b.log' ORA-00314: log 1 of thread 1, expected sequence# 43 doesn't match 0 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/secdb/lfile/redo01.log' 到此切换后的主库（原备库）将不在可用！ 6.故障处理 既然问题出在备库的配置上，并且备库已经不在可用。最后的可行的出路便是将现在的备库（secdb实例，及原主库）调整为主库以供生产所用。 我们能采用的方法便是强制将备库做Failover处理。具体操作如下。 NotConnected@> alter database recover managed standby database finish force; Database altered. NotConnected@> alter database commit to switchover to primary; Database altered. sys@secdb> select DATABASE_ROLE from v$database; DATABASE_ROLE ---------------- PRIMARY sys@secdb> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE    10.2.0.1.0      Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production 后续的拯救措施便是，根据这个Failover出来的主库重新按照正确的方法重建Data Guard备库。 7.另外一种故障场景   另外一种故障场景是当主库切换日志时遇到类似故障，主库将不再可用。此时的处理方式只能是放弃主库，然后将备库切换为主库，然后再重建整个Data Guard环境。 类似的报错信息如下： Errors in file /u01/app/oracle/admin/secdb/udump/secdb_ora_2201.trc: ORA-00314: log 1 of thread 1, expected sequence# 60 doesn't match 0 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/secdb/lfile/redo01_b.log' ORA-00314: log 1 of thread 1, expected sequence# 60 doesn't match 0 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/secdb/lfile/redo01.log' 8.小结   通过这个故障场景的再现及处理过程可以得出一个结论，我们在配置Data Guard环境时一定要对每个参数的含义和作用做到了如指掌，防止出现故障素手无策。   另外一个老生常谈的结论是：“有备无患”！，在准备对生产库做重大调整前一定要对数据库做好有效的备份。这样在出现类似文章提到的Data Guard配置故障后，可以非常高效的进行恢复。 Good luck. secooler 11.06.17 -- The End --