LogondatabaseTiger causes uard non-sync

When the slave uard upgrades the CPU, it finds that the master and slave databases cannot be synchronized and the log reports an error. Therefore, the script @ catbundle after the upgrade is executed in the master database. sqlcpuapply cannot be synchronized to the slave database. phenomenon: broker status: [oracle @ webdb2 ~] $ DgmgrlsysoracleDGMGRLforLinux: Version11.2.0.3.0-64 bitProductionCopy

When the slave uard upgrades the CPU, it finds that the master and slave databases cannot be synchronized and the log reports an error. Therefore, the script @ catbundle after the upgrade is executed in the master database. SQL cpu apply cannot be synchronized to the slave database. The broker status is [oracle @ webdb2 ~]. $ Dgmgrl sys/oracle DGMGRL for Linux: Version 11.2.0.3.0-64bit Production Copy

When the slave uard upgrades the CPU, it finds that the master and slave databases cannot be synchronized and the log reports an error. Therefore, the script "@ catbundle. SQL cpu apply" after the upgrade is executed in the master database cannot be synchronized to the slave database.

Symptom:

Broker status:

[Oracle @ webdb2 ~] $ Dgmgrl sys/oracle

DGMGRL for Linux: Version 11.2.0.3.0-64bit Production

Copyright (c) 2000,200 9, Oracle. Allrights reserved.

Welcome to DGMGRL. For more information, enter "help ".

Connected.

DGMGRL> show configuration

Configuration-webdb

Protection Mode: MaxAvailability

Database:

Webdb-master database

Webdb2-physical standby Database

Fast Start failover: DISABLED

Configuration status:

ORA-16664: unable to receive results from database

DGM-17017: Unable to determine configuration status

-- View the status of the master database:

SQL> Select timestamp, message FROMV $ DATAGUARD_STATUS Where Rownum <5;

TIMESTAMP MESSAGE

Bytes -------------------------------------------------------------------------------------------

Error 604 caught ed logging on to the standby

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.

Error 604 caught ed logging on to the standby

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

Master database log:

ORA-00604: recursive SQL Level 1 error

ORA-20154: TNS: The Listener currently cannot identify the Service requested in the connection Descriptor

ORA-06512: In line 29

Thu Oct 23 00:25:52 2014

Errors in file/u01/app/ora11g/diag/rdbms/webdb/trace/webdb_ora_18833.trc:

ORA-00604: recursive SQL Level 1 error

ORA-20154: TNS: The Listener currently cannot identify the Service requested in the connection Descriptor

ORA-06512: In line 29

Thu Oct 23 00:25:52 2014

Errors in file/u01/app/ora11g/diag/rdbms/webdb/trace/webdb_ora_18842.trc:

ORA-00604: recursive SQL Level 1 error

ORA-20154: TNS: The Listener currently cannot identify the Service requested in the connection Descriptor

ORA-06512: In 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/trace/webdb_ora_18961.trc:

ORA-00604: recursive SQL Level 1 error

ORA-20154: TNS: The Listener currently cannot identify the Service requested in the connection Descriptor

ORA-06512: In line 29

Slave database log:

ORA-00604: recursive SQL Level 1 error

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

ORA-06512: In 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

ORA-20154: TNS: The Listener currently cannot identify the Service requested in the connection Descriptor

ORA-06512: In line 29

Errors in file/u01/app/ora11g/diag/rdbms/webdb2/webdb2/trace/webdb2_ora_30130.trc:

ORA-00604: recursive SQL Level 1 error

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

ORA-06512: In line 3

-- Further view the trace file

Master database trace:

[Oracle @ webdb1 trace] $ more/u01/app/ora11g/diag/rdbms/webdb/trace/webdb_ora_8589.trc

Trace file/u01/app/ora11g/diag/rdbms/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 Jul 27 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: oracle @ webdb1

* ** 13:19:18. 860

* ** Session id: (21.12621) 2014-10-1413: 19: 18.860

* ** Client id: () 13:19:18. 860

* ** Service name :( SYS $ USERS) 2014-10-1413: 19: 18.860

* ** Module name :( emagent @ webdb1 (TNSV1-V3) 13:19:18. 860

* ** Action name: () 13:19:18. 860

Error in executing triggers on connectinternal

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

ORA-20154: TNS: The Listener currently cannot identify the Service requested in the connection Descriptor

ORA-06512: In line 28

Trace file/u01/app/ora11g/diag/rdbms/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 Jul 27 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: oracle @ webdb1

Slave database trace:

[Oracle @ webdb2 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 Jul 27 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: oracle @ webdb2

* ** 11:30:21. 649

* ** Session id: (854.10603) 2014-10-2311: 30: 21.649

* ** Client id: () 11:30:21. 649

* ** Service name :( SYS $ USERS) 2014-10-2311: 30: 21.649

* ** Module name :( emagent @ webdb2 (TNSV1-V3) 11:30:21. 649

* ** Action name: () 11:30:21. 649

Error in executing triggers on connectinternal

* ** 11:30:21. 649

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

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

ORA-00604: recursive SQL Level 1 error

ORA-20154: TNS: The Listener currently cannot identify the Service requested in the connection Descriptor

ORA-06512: In line 29

Error in executing triggers on connectinternal

* ** 11:30:21. 650

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

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

ORA-00604: recursive SQL Level 1 error

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

ORA-06512: In line 3

Analysis:

From the trace log, we can see that all operations are related to tiger execution, so we thought that two logon on database tiger created in the master database

Solution:

-- Disable logon to tiger

After a tiger with a logon IP address is disabled

ORA-00604: recursive SQL Level 1 error

ORA-20154: TNS: The Listener currently cannot identify the Service requested in the connection Descriptor

The error disappears, and only the following error is returned:

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.

The slave database still reports an error.

-- Start the slave database to the mount status

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 has been loaded.

SQL> select open_mode from v $ database;

OPEN_MODE

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

MOUNTED

-- View logs

Master database:

Thu Oct 23 13:45:15 2014

Archived Log entry 2225 added for thread 1 sequence 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 1 sequence 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 1 sequence 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 1 sequence 1123 ID 0xc8d579d3 dest 1

Slave database:

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 1 sequence 1123 dbid-925543465 branch 819826777

Deleted Oracle managed file + FRA/webdb2/archivelog/2014_10_13/thread_eclipseq_1049.350.860808821

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

You can see that the master and slave databases restore the heartbeat, and the slave database can restore application logs.

Open the standby database:

SQL> alter database open;

The database has been 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

*

Row 3 has an error:

ORA-01153: activates incompatible media recovery

SQL> select open_mode from v $ database;

OPEN_MODE

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

READ ONLY WITH APPLY

-- Master database switch log

SQL> alter system switch logfile;

The system has been changed.

-- View logs

Master database:

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 1 sequence 1124 ID 0xc8d579d3 dest 1:

Slave database:

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 1 sequence 1125 dbid-925543465 branch 819826777

Thu Oct 23 14:19:52 2014

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

Thu Oct 23 14:19:52 2014

Media Recovery Waiting for thread 1 sequence 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

It can be seen that the log application is still normal after the read-only application status is open standby Database

-- View the DataGuard status

[Oracle @ webdb1 trace] $ dgmgrl sys/oracle

DGMGRL for Linux: Version 11.2.0.3.0-64bit Production

Copyright (c) 2000,200 9, Oracle. Allrights reserved.

Welcome to DGMGRL. For more information, enter "help ".

Connected.

DGMGRL> show configuration

Configuration-webdb

Protection Mode: MaxAvailability

Database:

Webdb-master database

Webdb2-physical standby Database

Fast Start failover: DISABLED

Configuration status:

SUCCESS

Summary:

The error analysis shows that the master-slave synchronization is not directly related to the CPU patch upgrade, but to the created logon database trigger,

If you create a logon database tiger in the primary data, it may cause the primary and standby databases to fail to transmit logs synchronously,

In this case, we need to re-mount the slave database and then open it to the READ-ONLY application status (read only with apply)

When the slave database is restarted, the slave database directly starts to the READ-ONLY application status (read only with apply.

Refer:

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