Logon database Tiger causes Dataguard to be out of sync

When the Dataguard upgrade CPU, found that the main standby library can not be synchronized, the log error, so in the main library after the upgrade of the execution of the 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

Quick Start 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 requested service in 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: the listener currently does not recognize the service requested in 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 requested service in 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 requested service in 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: the listener currently does not recognize the service requested in 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: Opening a 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: the listener currently does not recognize the service requested in 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 requested service in 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: Opening a database for read-only access

ORA-06512: On line 3

Analysis:

Can be seen from the trace log see all related to Tiger execution, and then thought in the main library is created two logon on database tiger

Workaround:

--Disable Login Tiger

Put a login IP limit on tiger after disabling the main library

ORA-00604: A recursive SQL Level 1 error occurred

Ora-20154:tns: Listener currently does not recognize the requested service in 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 is loaded.

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:

You can see the main backup to recover the heartbeat, the repository can restore the 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

You can see that the log application is still normal after the read-only application state open standby

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

Quick Start 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.

If you create a logon on database tiger in the master data, it may cause the primary repository to fail to synchronize the transfer log.

At this point, we have to do is to re-mount the repository, and then open to read-only application status (read with apply)

Because when the standby is restarted, the repository starts directly to the read-only application state (read with apply), and the above problem occurs.

Reference:

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

Logon database Tiger causes Dataguard to be out of sync