Logon database Tiger causes Dataguard to be out of sync

Tag: TNS try post message limit error triggers journey thread

After the Dataguard has upgraded the CPU. found that the main repository can not be synchronized, the log error. Therefore, after the upgrade of the main library runs, the post-script "@catbundle. SQL CPU Apply" cannot be synchronized to the standby library

Phenomenon:

Broker Status:

[Email protected] ~]$ DGMGRL sys/oracle

DGMGRL for Linux:version 11.2.0.3.0-64bit Production

Copyright (c), Oracle. Allrights reserved.

Welcome to DGMGRL, please type "help" for information.

is connected.

Dgmgrl> Show Configuration

Configuration-WebDB

Protection mode: maxavailability

Database:

WEBDB-master Database

WEBDB2-Physical Standby database

High-speed boot failover: DISABLED

Configuration Status:

ORA-16664: Unable to receive results from the database

DGM-17017: Unable to determine configuration status

--View the status of the main library:

Sql> Select timestamp, message fromv$dataguard_status Where Rownum < 5;

TIMESTAMP MESSAGE

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

2014-10-23 Error 604 received logging on to the standby

2014-10-23 Ping[arc2]: Heartbeat failed to connect Tostandby (description= (address= (protocol=tcp) (HOST=WEBDB2) (port= 1521)) (Connect_data= (SERVICE_NAME=WEBDB2) (server=dedicated))) '. Error is 604.

2014-10-23 Error 604 received logging on to the standby

2014-10-23 Ping[arc2]: Heartbeat failed to connect to standby ' (description= (address= (protocol=tcp) (HOST=WEBDB2) port= 1521)) (Connect_data= (SERVICE_NAME=WEBDB2) (server=dedicated))) '. Error is 604.

--View log at this time

Main Library log:

ORA-00604: A recursive SQL Level 1 error occurred

Ora-20154:tns: Listener currently does not recognize the service requested by the connection descriptor

ORA-06512: On line 29

Thu Oct 23 00:25:52 2014

Errors in FILE/U01/APP/ORA11G/DIAG/RDBMS/WEBDB/WEBDB/TRACE/WEBDB_ORA_18833.TRC:

ORA-00604: Recursive SQL level 1 error occurred

Ora-20154:tns: Listener currently does not recognize the service requested by the connection descriptor

ORA-06512: On line 29

Thu Oct 23 00:25:52 2014

Errors in FILE/U01/APP/ORA11G/DIAG/RDBMS/WEBDB/WEBDB/TRACE/WEBDB_ORA_18842.TRC:

ORA-00604: A recursive SQL Level 1 error occurred

Ora-20154:tns: Listener currently does not recognize the service requested by the connection descriptor

ORA-06512: On line 29

Thu Oct 23 00:25:53 2014

Error 604 receivedlogging on to the standby

PING[ARC2]: heartbeatfailed to connect to standby ' (description= (address= (protocol=tcp) (HOST=WEBDB2) (port=1521)) ( Connect_data= (SERVICE_NAME=WEBDB2) (server=dedicated)) '. Error is 604.

Thu Oct 23 00:26:12 2014

Errors in FILE/U01/APP/ORA11G/DIAG/RDBMS/WEBDB/WEBDB/TRACE/WEBDB_ORA_18961.TRC:

ORA-00604: A recursive SQL Level 1 error occurred

Ora-20154:tns: Listener currently does not recognize the service requested by the connection descriptor

ORA-06512: On line 29

Repository log:

ORA-00604: Recursive SQL level 1 error occurred

ORA-16000: open a database for read-only access

ORA-06512: On line 3

Thu Oct 23 00:27:39 2014

Errors in FILE/U01/APP/ORA11G/DIAG/RDBMS/WEBDB2/WEBDB2/TRACE/WEBDB2_ORA_30130.TRC:

ORA-00604: Recursive SQL level 1 error occurred

Ora-20154:tns: Listener currently does not recognize the service requested by the connection descriptor

ORA-06512: On line 29

Errors in FILE/U01/APP/ORA11G/DIAG/RDBMS/WEBDB2/WEBDB2/TRACE/WEBDB2_ORA_30130.TRC:

ORA-00604: A recursive SQL Level 1 error occurred

ORA-16000: Open the database for read-only access

ORA-06512: On line 3

--View trace files further

Main Library Trace:

[Email protected] trace]$ MORE/U01/APP/ORA11G/DIAG/RDBMS/WEBDB/WEBDB/TRACE/WEBDB_ORA_8589.TRC

Trace FILE/U01/APP/ORA11G/DIAG/RDBMS/WEBDB/WEBDB/TRACE/WEBDB_ORA_8589.TRC

Oracle Database 11g Enterprise editionrelease 11.2.0.3.0-64bit Production

With the partitioning, Automatic storagemanagement, OLAP, Data Mining

and Real Application Testing options

Oracle_home =/u01/app/ora11g/product/11.2.0/db_1

System Name:linux

Node NAME:WEBDB1

Release:2.6.32-200.13.1.el5uek

Version: #1 SMP Wed 21:02:33 EDT 2011

Machine:x86_64

Instance NAME:WEBDB

Redo thread mounted by this instance:1

Oracle Process number:176

Unix process pid:8589, Image:[email protected]

2014-10-14 13:19:18.860

SESSION ID: (21.12621) 2014-10-1413:19:18.860

CLIENT ID: () 2014-10-14 13:19:18.860

SERVICE NAME: (sys$users) 2014-10-1413:19:18.860

MODULE NAME: ([email protected] (TNSV1-V3)) 2014-10-14 13:19:18.860

ACTION NAME: () 2014-10-14 13:19:18.860

Error in executing triggers on connectinternal

2014-10-14 13:19:18.861

Dbkeddefdump (): Starting a non-incidentdiagnostic dump (flags=0x0, level=0, mask=0x0)

-----Error Stack Dump-----

ORA-00604: Recursive SQL level 1 error occurred

Ora-20154:tns: Listener currently does not recognize the service requested by the connection descriptor

ORA-06512: On line 28

Trace FILE/U01/APP/ORA11G/DIAG/RDBMS/WEBDB/WEBDB/TRACE/WEBDB_ORA_8589.TRC

Oracle Database 11g Enterprise editionrelease 11.2.0.3.0-64bit Production

With the partitioning, Automatic storagemanagement, OLAP, Data Mining

and Real Application Testing options

Oracle_home =/u01/app/ora11g/product/11.2.0/db_1

System Name:linux

Node NAME:WEBDB1

Release:2.6.32-200.13.1.el5uek

Version: #1 SMP Wed 21:02:33 EDT 2011

Machine:x86_64

Instance NAME:WEBDB

Redo thread mounted by this instance:1

Oracle Process number:163

Unix process pid:8589, Image:[email protected]

Repository Trace:

[Email protected] trace]$ MORE/U01/APP/ORA11G/DIAG/RDBMS/WEBDB2/WEBDB2/TRACE/WEBDB2_ORA_29791.TRC

Trace FILE/U01/APP/ORA11G/DIAG/RDBMS/WEBDB2/WEBDB2/TRACE/WEBDB2_ORA_29791.TRC

Oracle Database 11g Enterprise editionrelease 11.2.0.3.0-64bit Production

With the partitioning, Automatic storagemanagement, OLAP, Data Mining

and Real Application Testing options

Oracle_home =/u01/app/ora11g/product/11.2.0/db_1

System Name:linux

Node NAME:WEBDB2

Release:2.6.32-200.13.1.el5uek

Version: #1 SMP Wed 21:02:33 EDT 2011

Machine:x86_64

Instance NAME:WEBDB2

Redo thread mounted by this instance:1

Oracle Process number:22

Unix process pid:29791, Image:[email protected]

2014-10-23 11:30:21.649

SESSION ID: (854.10603) 2014-10-2311:30:21.649

CLIENT ID: () 2014-10-23 11:30:21.649

SERVICE NAME: (sys$users) 2014-10-2311:30:21.649

MODULE NAME: ([email protected] (TNSV1-V3)) 2014-10-23 11:30:21.649

ACTION NAME: () 2014-10-23 11:30:21.649

Error in executing triggers on connectinternal

2014-10-23 11:30:21.649

Dbkeddefdump (): Starting a non-incidentdiagnostic dump (flags=0x0, level=0, mask=0x0)

-----Error Stack Dump-----

ORA-00604: A recursive SQL Level 1 error occurred

Ora-20154:tns: Listener currently does not recognize the service requested by the connection descriptor

ORA-06512: On line 29

Error in executing triggers on connectinternal

2014-10-23 11:30:21.650

Dbkeddefdump (): Starting a non-incidentdiagnostic dump (flags=0x0, level=0, mask=0x0)

-----Error Stack Dump-----

ORA-00604: A recursive SQL Level 1 error occurred

ORA-16000: Open the database for read-only access

ORA-06512: On line 3

Analysis:

The ability to see the trace log is related to the Tiger run, and the two logon on database tiger that was created in the main library

The workaround:

--Disable Login Tiger

Put a login IP limit on tiger after it is disabled. of the main library

ORA-00604: A recursive SQL Level 1 error occurred

Ora-20154:tns: Listener currently does not recognize the service requested by the connection descriptor

The error disappears, leaving only the following error:

Error 604 received logging on to Thestandby

PING[ARC2]: Heartbeat failed to connect Tostandby ' (description= (address= (protocol=tcp) (HOST=WEBDB2) (port=1521)) ( Connect_data= (SERVICE_NAME=WEBDB2) (server=dedicated)) '. Error is 604.

Repository still error

--Start repository to Mount state

sql> shutdown Immediate

Sql> Startup Mount

The ORACLE routine has been started.

Total System Global area 1.0456E+10 bytes

Fixed Size 2237488 bytes

Variable Size 5435821008 bytes

Database buffers 4999610368 bytes

Redo buffers 17973248 bytes

The database mount is complete.

Sql> select Open_mode from V$database;

Open_mode

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

Mounted

--View Log

Main Library:

Thu Oct 23 13:45:15 2014

Archived Log Entry 2225 added for thread 1sequence 1122 ID 0xc8d579d3 dest 1:

Arc3:standby Redo LogFile selected Forthread 1 sequence 1122 for destination log_archive_dest_2

Thu Oct 23 13:46:46 2014

Arc3:archive Log rejected (thread 1sequence 1120) at host ' (Description= (address= (protocol=tcp) (HOST=WEBDB2) (port=1521 )) (Connect_data= (SERVICE_NAME=WEBDB2) (server=dedicated))) '

Thu Oct 23 13:46:46 2014

Arc0:archive Log rejected (thread 1sequence 1121) at host ' (Description= (address= (protocol=tcp) (HOST=WEBDB2) (port=1521 )) (Connect_data= (SERVICE_NAME=WEBDB2) (server=dedicated))) '

Fal[server, ARC3]: FAL archive failed, seetrace file.

Arch:fal Archive failed. Archivercontinuing

ORACLE Instance Webdb-archival Error.archiver continuing.

Fal[server, ARC0]: FAL archive failed, seetrace file.

Arch:fal Archive failed. Archivercontinuing

ORACLE Instance Webdb-archival Error.archiver continuing.

Thu Oct 23 13:46:49 2014

Destination log_archive_dest_2 issynchronized

Lgwr:standby Redo LogFile Selected toarchive thread 1 sequence 1124

Lgwr:standby Redo LogFile selected Forthread 1 sequence 1124 for destination log_archive_dest_2

Thread 1 advanced to log sequence 1124 (LGWR switch)

Current log# 2 seq# 1124 mem# 0:+data/webdb/onlinelog/group_2.262.819826783

Current log# 2 seq# 1124 mem# 1:+fra/webdb/onlinelog/group_2.258.819826785

Archived Log Entry 2244 added for thread 1sequence 1123 ID 0xc8d579d3 dest 1

Standby Library:

Using standby_archive_dest Parameterdefault value as Use_db_recovery_file_dest

Thu Oct 23 13:45:15 2014

RFS[1]: Assigned to RFS process 17293

RFS[1]: Opened log for thread 1 sequence1102 dbid-925543465 Branch 819826777

Thu Oct 23 13:45:15 2014

Primary database is in MAXIMUM Availabilitymode

Standby Controlfile consistent with primary

Standby Controlfile consistent with primary

RFS[2]: Assigned to RFS process 17297

RFS[2]: Selected log 7 for thread 1sequence 1123 dbid-925543465 branch 819826777

Deleted Oracle Managed file+fra/webdb2/archivelog/2014_10_13/thread_1_seq_1049.350.860808821

Archived Log Entry 1104 added for thread 1sequence 1102 RLC 819826777 ID 0xc8d579d3 dest 2:

Be able to see the main standby to recover the heartbeat. Repository can restore Application log

To open a standby operation:

sql> ALTER DATABASE open;

The database has changed.

Sql> select Open_mode from V$database;

Open_mode

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

READ only

sql> ALTER Database RECOVER managedstandby database USING current LOGFILE DISCONNECT;

ALTER DATABASE RECOVER MANAGED standbydatabase USING current LOGFILE DISCONNECT

*

An error occurred on line 1th:

ORA-01153: Incompatible Media recovery activated

Sql> select Open_mode from V$database;

Open_mode

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

READ only with APPLY

--Main Library switch log

sql> alter system switch logfile;

The system has changed.

--View Log

Main Library:

Thu Oct 23 14:19:52 2014

Lgwr:standby Redo LogFile Selected toarchive thread 1 sequence 1125

Lgwr:standby Redo LogFile selected Forthread 1 sequence 1125 for destination log_archive_dest_2

Thread 1 advanced to log sequence 1125 (LGWR switch)

Current log# 3 seq# 1125 mem# 0:+data/webdb/onlinelog/group_3.263.819826787

Current log# 3 seq# 1125 mem# 1:+fra/webdb/onlinelog/group_3.259.819826789

Thu Oct 23 14:19:53 2014

Archived Log Entry 2246 added for thread 1sequence 1124 ID 0xc8d579d3 dest 1:

Standby Library:

Completed:alter database RECOVER managedstandby database THROUGH all switchoverdisconnect USING current LOGFILE

Thu Oct 23 14:15:51 2014

ALTER DATABASE RECOVER MANAGED standbydatabase USING current LOGFILE DISCONNECT

ORA-1153 signalled During:alter databaserecover MANAGED STANDBY DATABASE USING current LOGFILE DISCONNECT ...

Thu Oct 23 14:19:52 2014

Standby Controlfile consistent with primary

RFS[2]: Selected log 7 for thread 1sequence 1125 dbid-925543465 branch 819826777

Thu Oct 23 14:19:52 2014

Archived Log Entry 1123 added for thread 1sequence 1124 ID 0xc8d579d3 dest 1:

Thu Oct 23 14:19:52 2014

Media Recovery waiting for thread 1sequence 1125 (in transit)

Recovery of Online Redo log:thread 1 Group7 Seq 1125 Reading Mem 0

mem# 0: +data/webdb2/onlinelog/group_7.282.819884421

mem# 1: +fra/webdb2/onlinelog/group_7.278.819884423

Can see that the log application is still normal after reading only the application state open repository

--View Dataguard status

[Email protected] trace]$ DGMGRL sys/oracle

DGMGRL for Linux:version 11.2.0.3.0-64bit Production

Copyright (c), Oracle. Allrights reserved.

Welcome to DGMGRL, please type "help" for information.

is connected.

Dgmgrl> Show Configuration

Configuration-WebDB

Protection mode: maxavailability

Database:

WEBDB-master Database

WEBDB2-Physical Standby database

High-speed boot failover: DISABLED

Configuration Status:

SUCCESS

Summarize:

Through the error analysis, it is shown that the primary and standby synchronization is not directly related to the CPU patch escalation, and is associated with the logon on database trigger created.

Assuming that a logon on database tiger is created on the master data, it may cause problems with the master repository not synchronizing the transfer log.

At this point, we are going to mount the repository again and then open to read only the application state (read with apply)

As the repository restarts, the repository starts directly to the read-only application state (read with apply) and the problem arises.

References:

Http://www.cnblogs.com/Spiritual-journey/archive/2012/10/21/2732806.html

Logon database Tiger causes Dataguard to be out of sync