Logon database Tiger causes the slave uard to be not synchronized. tigerdataguard

Logon database Tiger causes the slave uard to be not synchronized. tigerdataguard

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