从alert日志看Oracle 11g DataGuard日志传输

Oracel DG技术本身，是借助redo log的传递和应用，确保在standby端数据和primary端保持一致数据。在这个过程中，Redo Transport和Redo Apply是两个核心动作。Redo Transport是将Redo Log信息传递到Standby端，等待进行Apply。而Redo Apply就是将这些日志应用执行，更改Standby端的数据，来实现一致。
下面实验，就是利用alert log来观察一对Primary和Standby在启动过程、工作过程中传递日志的情况。从而证明Oracle DG的工作特点和机制。相关参考:Oracle Data Guard 重要配置参数 http://www.linuxidc.com/Linux/2013-08/88784.htm基于同一主机配置 Oracle 11g Data Guard http://www.linuxidc.com/Linux/2013-08/88848.htm探索Oracle之11g DataGuard http://www.linuxidc.com/Linux/2013-08/88692.htmOracle Data Guard （RAC+DG）归档删除策略及脚本 http://www.linuxidc.com/Linux/2013-07/87782.htmOracle Data Guard 的角色转换 http://www.linuxidc.com/Linux/2013-06/86190.htmOracle Data Guard的日志FAL gap问题 http://www.linuxidc.com/Linux/2013-04/82561.htmOracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 处理方法 http://www.linuxidc.com/Linux/2013-03/82009.htm1、环境介绍我们在Oracle 11g上进行试验，版本为11.2.0.4。由于环境限制，笔者Primary和Physical Standby在相同服务器上。Primary实例名称为ora11g，Standby实例名为ora11gsy。

监听程序首先关闭，来查看数据库行为。[oracle@SimpleLinux ~]$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 27-APR-2014 13:40:15Copyright （c） 1991, 2013, Oracle. All rights reserved. Connecting to （DESCRIPTION=（ADDRESS=（PROTOCOL=TCP）（HOST=SimpleLinux）（PORT=1521）））

TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 111: Connection refusedConnecting to （DESCRIPTION=（ADDRESS=（PROTOCOL=IPC）（KEY=EXTPROC1521）））TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 111: Connection refused 主库Primary日志。 [root@SimpleLinux ~]# su - oracle[oracle@SimpleLinux ~]$ cd /u01/app/diag/rdbms/ora11g/ora11g/trace/[oracle@SimpleLinux trace]$ ls -l | grep alert-rw-r-----. 1 oracle oinstall 176813 Apr 21 21:58 alert_ora11g.log 2、Primary端启动过程首先启动数据库到nomount状态，此时pmon是进行工作的。 [oracle@SimpleLinux ~]$ env | grep ORACLE_SIDORACLE_SID=ora11g[oracle@SimpleLinux ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 27 13:54:15 2014 Copyright （c） 1982, 2013, Oracle. All rights reserved. SQL> conn / as sysdbaConnected to an idle instance.SQL> startup nomountORACLE instance started. Total System Global Area 372449280 bytesFixed Size 1364732 bytesVariable Size 331353348 bytesDatabase Buffers 33554432 bytesRedo Buffers 6176768 bytes 这个阶段日志是没有什么额外特殊的信息的，只有正常的后台实例启动。 Sun Apr 27 13:54:58 2014Starting ORACLE instance （normal）LICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0Initial number of CPU is 1CELL communication is configured to use 0 interface（s）:CELL IP affinity details: NUMA status: non-NUMA system cellaffinity.ora status: N/ACELL communication will use 1 IP group（s）: Grp 0:（篇幅原因，有省略……）starting up 1 dispatcher（s） for network address "（ADDRESS=（PARTIAL=YES）（PROTOCOL=TCP））"...

Sun Apr 27 13:55:07 2014MMNL started with pid=16, OS id=1776starting up 1 shared server（s） ...ORACLE_BASE from environment = /u01/app 切换到mount状态。 SQL> alter database mount;Database altered. 日志中，定位到mount状态。 Sun Apr 27 14:03:18 2014alter database mountSun Apr 27 14:03:23 2014Successful mount of redo thread 1, with mount id 4242195174Database mounted in Exclusive ModeLost write protection disabledCompleted: alter database mount 之后启动数据库，到open状态。在mount之前，数据库是不会生成和执行redo相关的动作的。从mount到open阶段，是需要进行一个instance recovery过程的，也就是日志前滚后滚的动作。在mount和mount之前，是不会有Redo Transport过程的。

Sun Apr 27 14:24:56 2014alter database openBeginning crash recovery of 1 threadsStarted redo scanCompleted redo scan read 78 KB redo, 26 data blocks need recoveryStarted redo application at Thread 1: logseq 32, block 47Recovery of Online Redo Log: Thread 1 Group 1 Seq 32 Reading mem 0 Mem# 0: /u01/app/oradata/ORA11G/onlinelog/o1_mf_1_9mnjwtj9_.log Mem# 1: /u01/app/fast_recovery_area/ORA11G/onlinelog/o1_mf_1_9mnjwvdm_.logCompleted redo application of 0.02MBCompleted crash recovery at Thread 1: logseq 32, block 203, scn 815633 26 data blocks read, 26 data blocks written, 78 redo k-bytes readSun Apr 27 14:24:58 2014LGWR: STARTING ARCH PROCESSESSun Apr 27 14:24:58 2014 Fatal NI connect error 12541, connecting to: （DESCRIPTION=（ADDRESS_LIST=（ADDRESS=（PROTOCOL=TCP）（HOST=192.168.137.88）（PORT=1521）））（CONNECT_DATA=（SERVICE_NAME=ora11gsy）（CID=（PROGRAM=oracle）（HOST=SimpleLinux.localdomain）（USER=oracle））））

VERSION INFORMATION: TNS for Linux: Version 11.2.0.4.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production Time: 27-APR-2014 14:25:03 Tracing not turned on. Tns error struct: ns main err code: 12541 TNS-12541: TNS:no listener ns secondary err code: 12560 nt main err code: 511 TNS-00511: No listener nt secondary err code: 111 nt OS err code: 0 Check whether the listener is up and running.FAL[server, ARC3]: Error 12541 creating remote archivelog file "ora11gsy"FAL[server, ARC3]: FAL archive failed, see trace file.ARCH: FAL archive failed. Archiver continuingORACLE Instance ora11g - Archival Error. Archiver continuing.Starting background process QMNCSun Apr 27 14:25:11 2014QMNC started with pid=25, OS id=2129Sun Apr 27 14:25:25 2014Completed: alter database openSun Apr 27 14:25:28 2014Starting background process CJQ0Sun Apr 27 14:25:28 2014CJQ0 started with pid=28, OS id=2149Setting Resource Manager plan SCHEDULER[0x32DF]:DEFAULT_MAINTENANCE_PLAN via scheduler window

Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameterSun Apr 27 14:25:34 2014Starting background process VKRMSun Apr 27 14:25:34 2014VKRM started with pid=29, OS id=2151 Open过程完成，但是数据库日志不断报错。主要体现在两个方面，一个是监听器故障，Primary在传递日志的时候，archive_log_dest配置两种类型，location和service。其中service就存放对应服务名称。这个服务名称是借助Oracle NET实现，Oracle NET外表体现就是监听器。另一种类型错误就是心跳机制，Oracle Primary在不断的访问standby。

在之后过程，日志中不断报错。说明这个过程是一个周期性确认的过程。（DESCRIPTION=（ADDRESS_LIST=（ADDRESS=（PROTOCOL=TCP）（HOST=192.168.137.88）（PORT=1521）））（CONNECT_DATA=（SERVICE_NAME=ora11gsy）（CID=（PROGRAM=oracle）（HOST=SimpleLinux.localdomain）（USER=oracle））））

VERSION INFORMATION: TNS for Linux: Version 11.2.0.4.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production Time: 27-APR-2014 14:30:00 Tracing not turned on. Tns error struct: ns main err code: 12541 TNS-12541: TNS:no listener ns secondary err code: 12560 nt main err code: 511 TNS-00511: No listener nt secondary err code: 111 nt OS err code: 0Error 12541 received logging on to the standbyCheck whether the listener is up and running.PING[ARC1]: Heartbeat failed to connect to standby "ora11gsy". Error is 12541. 心跳12504错误，信息为监听器没有启动。 [oracle@SimpleLinux ~]$ oerr ora 1250412504, 00000, "TNS:listener was not given the SERVICE_NAME in CONNECT_DATA"// *Cause: The listener was not configured with a default service and// SERVICE_NAME was missing from the CONNECT_DATA received by the listener.// *Action: Possible solutions are:// - Configure DEFAULT_SERVICE parameter in LISTENER.ORA with a valid service// name. Reload the listener parameter file using reload [<listener_name>].// This should be done by the LISTENER administrator.// - If using a service name, Check that the connect descriptor corresponding// to the service name in TNSNAMES.ORA has a SERVICE_NAME or SID component// in the CONNECT_DATA.更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2014-05/101100p2.htm

Java连接数据库步骤Oracle 11g Active Dataguard Failover实验相关资讯 DataGuard Oracle 11g日志 Oracle alert日志

DataGuard如何传送Redo到Standby （05月04日）
RAC环境下DataGuard到单机（04月08日）
Oracle 11G R2 DataGuard日常维护（02月20日）

MRP0- Background Media Recovery （04月16日）
DataGuard环境中的密码维护（03月17日）
Oracle 11G R2利用RMAN搭建（02月20日）

本文评论查看全部评论（0）