View Oracle 11g transaction uard log transmission from alert logs

The mongoel DG technology uses redo log transmission and application to ensure consistent data on the standby end and the primary end. In this process, Redo Transport and Redo Apply are two core actions. Redo Transport transmits the Redo Log information to the Standby end and waits for the Apply operation. Redo Apply is to execute these log applications and change the Standby data to achieve consistency.
 

The following experiment uses alert log to observe the situation where a pair of Primary and Standby transmits logs during startup and operation. This proves the features and Mechanisms of Oracle DG.

References:

Important configuration parameters of Oracle Data Guard

Configure Oracle 11g Data Guard based on the same host

Explore Oracle 11g elastic uard

Oracle Data Guard (RAC + DG) archive deletion policies and scripts

Role conversion for Oracle Data Guard

FAL gap in Oracle Data Guard logs

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby

1. Environment Introduction

We performed a test on Oracle 11g with version 11.2.0.4. Due to environmental restrictions, the author Primary and Physical Standby are on the same server. The Primary Instance name is ora11g, and the Standby Instance name is ora11gsy.
 
The listener first closes to view database behavior.

[Oracle @ SimpleLinux ~] $ Lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0-Production on 27-APR-2014 13:40:15

Copyright (c) 1991,201 3, 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 refused

Connecting 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

 

Master database Primary log.

 

[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 alert_ora11g.log

 

2. Primary end Startup Process

 

First, start the database to the nomount State. At this time, pmon is working.

 

[Oracle @ SimpleLinux ~] $ Env | grep ORACLE_SID

ORACLE_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,201 3, Oracle. All rights reserved.

 

SQL> conn/as sysdba

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area 372449280 bytes

Fixed Size 1364732 bytes

Variable Size 331353348 bytes

Database Buffers 33554432 bytes

Redo Buffers 6176768 bytes

 

Logs in this phase have no additional special information, and only normal backend instances can be started.

 

Sun Apr 27 13:54:58 2014

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Initial number of CPU is 1

CELL communication is configured to use 0 interface (s ):

Cell ip affinity details:

NUMA status: non-NUMA system

Cellaffinity. ora status: N/

CELL communication will use 1 IP group (s ):

Grp 0:

(Space reasons, omitted ......)

Starting up 1 dispatcher (s) for network address '(ADDRESS = (PARTIAL = YES) (PROTOCOL = TCP ))'...
 
Sun Apr 27 13:55:07 2014

MMNL started with pid = 16, OS id = 1776

Starting up 1 shared server (s )...

ORACLE_BASE from environment =/u01/app

 

Switch to the mount status.

 

SQL> alter database mount;

Database altered.

 

Log, locate the mount status.

 

Sun Apr 27 14:03:18 2014

Alter database mount

Sun Apr 27 14:03:23 2014

Successful mount of redo thread 1, with mount id 4242195174

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: alter database mount

 

Then start the database and go to the open state. Before mounting, the database does not generate any actions related to executing redo. From the mount to open stage, an instance recovery process is required, that is, the log rollback action. There is no Redo Transport process before mount and mount.
 
 

Sun Apr 27 14:24:56 2014

Alter database open

Beginning crash recovery of 1 threads

Started redo scan

Completed redo scan

Read 78 KB redo, 26 data blocks need recovery

Started redo application

Thread 1: logseq 32, block 47

Recovery of Online Redo Log: Thread 1 Group 1 Seq 32 Reading mem 0

Mem #0:/u01/app/oradata/ORA11G/onlinelog/ow.mf_00009mnjwtj9 _. log

Mem #1:/u01/app/fast_recovery_area/ORA11G/onlinelog/ow.mf_00009mn1_vdm _. log

Completed redo application of 0.02 MB

Completed crash recovery

Thread 1: logseq 32, block 203, scn 815633

26 data blocks read, 26 data blocks written, 78 redo k-bytes read

Sun Apr 27 14:24:58 2014

LGWR: STARTING ARCH PROCESSES

Sun Apr 27 14:24:58 2014

 

Fatal NI connect error 12541, connecting:

(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 continuing

ORACLE Instance ora11g-Archival Error. Archiver continuing.

Starting background process QMNC

Sun Apr 27 14:25:11 2014

QMNC started with pid = 25, OS id = 2129

Sun Apr 27 14:25:25 2014

Completed: alter database open

Sun Apr 27 14:25:28 2014

Starting background process CJQ0

Sun Apr 27 14:25:28 2014

CJQ0 started with pid = 28, OS id = 2149

Setting Resource Manager plan sched1_[ 0x32DF]: DEFAULT_MAINTENANCE_PLAN via sched1_window
 
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

Sun Apr 27 14:25:34 2014

Starting background process VKRM

Sun Apr 27 14:25:34 2014

VKRM started with pid = 29, OS id = 2151

 

The Open process is completed, but database logs continuously report errors. It is mainly reflected in two aspects: Listener failure. When Primary transfers logs, archive_log_dest configures two types: location and service. The service stores the corresponding service name. The service name is implemented using Oracle NET, and the Oracle NET appearance is the listener. Another type of error is the heartbeat mechanism. Oracle Primary is constantly accessing standby.
 
 

In the subsequent process, errors are continuously reported in logs. This process is a periodic validation process.

 

(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: 0

Error 12541 removed ed logging on to the standby

Check whether the listener is up and running.

PING [ARC1]: Heartbeat failed to connect to standby 'ora11gsy '. Error is 12541.

 

The heartbeat error 12504 indicates that the listener is not started.

 

[Oracle @ SimpleLinux ~] $ Oerr ora 1, 12504

12504,000 00, "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 encoded ed 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 shoshould 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.

For more details, please continue to read the highlights on the next page:

1 2 Next Page