TNS-12564 Errors and the Importance of the Parameter LOCAL_LISTENER During a Data Guard Switchover

Posted in: Oracle, Technical Track

A couple of weeks ago a client of mine had a situation where a Data Guard (DG) switchover that should have taken five minutes, took over one hour in troubleshooting and resolution. Right after the switchover completed, their application had issues connecting to the database. We discovered that the database parameter LOCAL_LISTENER was set incorrectly.

We went ahead and fixed this parameter, then went on to rebuild the DG broker. Immediately after that, everything went back to normal.

What I will try to do in this blog post is to build an example of how this happened. It will be a long read, so please bear with me.

In this example I’m using a single instance non-RAC database, so the setting of LOCAL_LISTENER is a bit different than it is for RAC. Further down I will show how to set LOCAL_LISTENER for the 19c versions of both RAC and single instance databases.

What I want to show first is that when I started, everything looked normal in the environment for the switchover to happen.

[oracle@primary ~]$ dgmgrl sys@TESTDB
Password:
Connected to "TESTDB"
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - db_broker_config

  Protection Mode: MaxPerformance
  Members:
  testdb       - Primary database
    testdb_stdby - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 13 seconds ago)

DGMGRL> show database testdb;

Database - testdb

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    TESTDB

Database Status:
SUCCESS

DGMGRL> show database testdb_stdby;

Database - testdb_stdby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 1.83 MByte/s
  Real Time Query:    ON
  Instance(s):
    TESTDB

Database Status:
SUCCESS

DGMGRL> validate database testdb_stdby;

  Database Role:     Physical standby database
  Primary Database:  testdb

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    testdb      :  On
    testdb_stdby:  Off

  Managed by Clusterware:
    testdb      :  NO             
    testdb_stdby:  NO             
    Validating static connect identifier for the primary database testdb...
    The static connect identifier allows for a connection to database "testdb".

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (testdb)               (testdb_stdby)                      
    1         3                       1                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (testdb_stdby)         (testdb)                            
    1         3                       0                       Insufficient SRLs
    Warning: standby redo logs not configured for thread 1 on testdb

After this, I actually did a Health Check using scripts in MOS note 1581388.1, on both primary and standby. I saw no lag and no errors in the v$dataguard_status of the standby so everything looked good from my end to perform the switchover.

################################################################################################
# Brief summary from Primary HC
################################################################################################

Primary Site last generated SCN

*******************************

DB_UNIQUE_NAME	SWITCHOVER_STATUS	  CURRENT_SCN
--------------- -------------------- ----------------
DB193H1 	TO STANDBY		      2784649

1 row selected.

...
Data Guard Redo Shipping Progress

*********************************

SYSTIMESTAMP
---------------------------------------------------------------------------
27-OCT-20 09.49.07.725370 AM -06:00

1 row selected.


PROCESS STATUS	     CLIENT_P CLIENT_PID	  THREAD#	 SEQUENCE#	     BLOCK#    ACTIVE_AGENTS	 KNOWN_AGENTS
------- ------------ -------- ---------- ---------------- ---------------- ---------------- ---------------- ----------------
ARCH	CLOSING      ARCH     22960			1		58		  1		   0		    0
ARCH	CLOSING      ARCH     22956			1		58		  1		   0		    0
ARCH	CLOSING      ARCH     22958			1		59		  1		   0		    0
ARCH	CLOSING      ARCH     22950			1		60	      16384		   0		    0
LNS	WRITING      LNS      1288			1		61		423		   0		    0
DGRD	ALLOCATED    N/A      N/A			0		 0		  0		   0		    0
DGRD	ALLOCATED    N/A      N/A			0		 0		  0		   0		    0
DGRD	ALLOCATED    N/A      N/A			0		 0		  0		   0		    0
DGRD	ALLOCATED    N/A      N/A			0		 0		  0		   0		    0

9 rows selected.
...

################################################################################################
# Brief summary from Standby HC
################################################################################################
...
Data Guard Apply Status

***********************

SYSTIMESTAMP
---------------------------------------------------------------------------
27-OCT-20 09.48.33.410311 AM -06:00

1 row selected.


PROCESS STATUS	     CLIENT_P CLIENT_PID	  THREAD#	 SEQUENCE#	     BLOCK#    ACTIVE_AGENTS	 KNOWN_AGENTS
------- ------------ -------- ---------- ---------------- ---------------- ---------------- ---------------- ----------------
ARCH	CONNECTED    ARCH     30543			0		 0		  0		   0		    0
ARCH	CONNECTED    ARCH     30546			0		 0		  0		   0		    0
ARCH	CONNECTED    ARCH     30548			0		 0		  0		   0		    0
ARCH	CLOSING      ARCH     30550			1		60	      16384		   0		    0
RFS	IDLE	     Archival 22952			1		 0		  0		   0		    0
RFS	IDLE	     LGWR     1288			1		61		381		   0		    0
DGRD	ALLOCATED    N/A      N/A			0		 0		  0		   0		    0
DGRD	ALLOCATED    N/A      N/A			0		 0		  0		   0		    0
MRP0	APPLYING_LOG N/A      N/A			1		61		381		   3		    3
RFS	IDLE	     UNKNOWN  22958			0		 0		  0		   0		    0
RFS	IDLE	     UNKNOWN  22956			0		 0		  0		   0		    0

11 rows selected.
...
Data Guard Apply Lag

********************

NAME	     LAG_TIME		  DATUM_TIME	       TIME_COMPUTED
------------ -------------------- -------------------- --------------------
apply lag    +00 00:00:00	  10/27/2020 09:48:52  10/27/2020 09:48:53

1 row selected.


Data Guard Gap Problems

***********************

no rows selected


Data Guard Errors in the Last Hour

**********************************

no rows selected

I went ahead and did the switchover and, as you can see in the command below, it seems like the switchover did not happen.

[oracle@primary ~]$ dgmgrl sys@TESTDB
Password:
Connected to "TESTDB"
Connected as SYSDBA.
DGMGRL> switchover to 'testdb_stdby';
Performing switchover NOW, please wait...
Operation requires a connection to database "testdb_stdby"
Connecting ...
Connected to "TESTDB_STDBY"
Connected as SYSDBA.
Error: ORA-1034: ORACLE not available
Error: ORA-16625: cannot reach member "testdb"

Failed.
Unable to switchover, primary database is still "testdb"

However, looking at the logs of the primary database, it seems the switchover did happen. What seemed fishy was that I started getting errors ORA-12514 and TNS-12564. First, I wanted to concentrate on the status of both databases and review the logs.

################################################################################################
# From the Primary Database Log
################################################################################################

2020-10-27T12:12:34.240-06:00
Forwarding MON_PROPERTY operation to member testdb_stdby for processing
2020-10-27T12:22:17.368-06:00
Forwarding MON_PROPERTY operation to member testdb_stdby for processing
Forwarding MON_PROPERTY operation to member testdb_stdby for processing
2020-10-27T12:22:33.178-06:00
Forwarding MON_PROPERTY operation to member testdb_stdby for processing
2020-10-27T12:27:39.365-06:00
Initiating a healthcheck...
SWITCHOVER TO testdb_stdby
Switchover to physical standby database cannot be initiated from the primary database
redirecting connection to switchover target database testdb_stdby...
...using connect identifier: testdb_stdby
SWITCHOVER TO testdb_stdby
Notifying Oracle Clusterware to prepare primary database for switchover
2020-10-27T12:27:40.847-06:00
Executing SQL: [ALTER DATABASE SWITCHOVER TO 'testdb_stdby']
2020-10-27T12:27:57.750-06:00
SQL [ALTER DATABASE SWITCHOVER TO 'testdb_stdby'] executed successfully
2020-10-27T12:28:12.255-06:00
Switchover in progress...
2020-10-27T12:30:19.497-06:00
Failed to connect to remote database testdb_stdby. Error is ORA-12514
Failed to send message to member testdb_stdby. Error code is ORA-12514.
***********************************************************************

Fatal NI connect error 12514, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=standby)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=testd b_stdby)(INSTANCE_NAME=TESTDB)(CID=(PROGRAM=oracle)(HOST=primary)(USER=oracle))))

  VERSION INFORMATION:
    TNS for Linux: Version 19.0.0.0.0 - Production
    TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
  Version 19.9.0.0.0
  Time: 27-OCT-2020 12:30:20
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12564
    
TNS-12564: TNS:connection refused
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0


################################################################################################
# From the Primary Database DataGuard Broker Log
################################################################################################

2020-10-27T12:27:39.535487-06:00
SWITCHOVER VERIFY: Send VERIFY request to switchover target testdb_stdby
SWITCHOVER VERIFY COMPLETE: READY FOR SWITCHOVER
SWITCHOVER VERIFY: Send VERIFY request to switchover target testdb_stdby
SWITCHOVER VERIFY COMPLETE: READY FOR SWITCHOVER
2020-10-27T12:27:40.848477-06:00
ALTER DATABASE SWITCHOVER TO 'testdb_stdby'
2020-10-27T12:27:40.848600-06:00
RSM0 (PID:23638): The Time Management Interface (TMI) is being enabled for role transition
RSM0 (PID:23638): information.  This will result in messages beingoutput to the alert log
RSM0 (PID:23638): file with the prefix 'TMI: '.  This is being enabled to make the timing of
RSM0 (PID:23638): the various stages of the role transition available for diagnostic purposes.
RSM0 (PID:23638): This output will end when the role transition is complete.
TMI: dbsdrv switchover to target BEGIN 2020-10-27 12:27:40.849258
RSM0 (PID:23638): Starting switchover [Process ID: 23638]
TMI: kcv_switchover_to_target convert to physical BEGIN 2020-10-27 12:27:40.927250
2020-10-27T12:27:40.927438-06:00
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 23638] (TESTDB)
...
RSM0 (PID:23638): Sending request(convert to primary database) to switchover target testdb_stdby
2020-10-27T12:27:44.474112-06:00
Process (ospid 22733) is suspended due to switchover to physical standby operation.
2020-10-27T12:27:45.753622-06:00
Process (ospid 22479) is suspended due to switchover to physical standby operation.
2020-10-27T12:27:57.749843-06:00
RSM0 (PID:23638): Switchover complete. Database shutdown required
TMI: dbsdrv switchover to target END 2020-10-27 12:27:57.749864
Completed: ALTER DATABASE SWITCHOVER TO 'testdb_stdby'
2020-10-27T12:27:58.811463-06:00

Next, I moved on to check the standby database log and Data Guard broker log, and I confirmed that the switchover did happen.

################################################################################################
# From the Standby Database log
################################################################################################

PR00 (PID:19640): MRP0: Background Media Recovery cancelled with status 16037
2020-10-27T12:27:44.243985-06:00
Errors in file /u01/app/oracle/diag/rdbms/testdb_stdby/TESTDB/trace/TESTDB_pr00_19640.trc:
ORA-16037: user requested cancel of managed recovery operation
PR00 (PID:19640): Managed Standby Recovery not using Real Time Apply
2020-10-27T12:27:44.353706-06:00
Recovery interrupted!
stopping change tracking
...
Standby became primary SCN: 2408641
 rmi (PID:20473): RT: Role transition work is not done
 rmi (PID:20473): The Time Management Interface (TMI) is being enabled for role transition
 rmi (PID:20473): information.  This will result in messages beingoutput to the alert log
 rmi (PID:20473): file with the prefix 'TMI: '.  This is being enabled to make the timing of
 rmi (PID:20473): the various stages of the role transition available for diagnostic purposes.
 rmi (PID:20473): This output will end when the role transition is complete.
 rmi (PID:20473): Redo network throttle feature is disabled at mount time
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
2020-10-27T12:27:57.747028-06:00
 rmi (PID:20473): Database role cleared from PHYSICAL STANDBY [kcvs.c:1069]
Switchover: Complete - Database mounted as primary
TMI: kcv_commit_to_so_to_primary Switchover from physical END 2020-10-27 12:27:57.747449
SWITCHOVER: completed request from primary database.
2020-10-27T12:28:43.126856-06:00
ARC0 (PID:18672): Becoming the 'no SRL' ARCH
2020-10-27T12:30:19.584435-06:00
ALTER SYSTEM SET fal_server='testdb' SCOPE=BOTH;
2020-10-27T12:43:00.679979-06:00

################################################################################################
# From the Standby Database DataGuard Broker Log
################################################################################################

Forwarding EDIT_RES_PROP operation to member testdb for processing
Apply Instance for Database testdb_stdby set to TESTDB
2020-10-27T12:07:54.013-06:00
Updated broker configuration file available, loading from "/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/dr1TESTDB_STDBY.dat"
2020-10-27T12:08:07.080-06:00
Starting redo apply services...
2020-10-27T12:11:51.444-06:00
Stopping apply to allow database open to proceed
2020-10-27T12:27:39.368-06:00
Initiating a healthcheck...
Forwarding CTL_SWITCH operation to member testdb for processing
FSFO SetState(st=12 "SET OBID", fl=0x0 "", ob=0x1, tgt=0, v=0)
Updated broker configuration file available, loading from "/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/dr2TESTDB_STDBY.dat"
2020-10-27T12:27:40.839-06:00
Switchover processing to this database has started
Notifying Oracle Clusterware to prepare target standby database for switchover
2020-10-27T12:27:54.410-06:00
Switchover in progress...
2020-10-27T12:30:19.564-06:00
Updated broker configuration file available, loading from "/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/dr1TESTDB_STDBY.dat"
2020-10-27T12:42:46.387-06:00
Failed to connect to remote database testdb. Error is ORA-1034
Failed to send message to member testdb. Error code is ORA-1034.
2020-10-27T12:43:00.701-06:00
Failed to connect to remote database testdb. Error is ORA-12514
Failed to send message to member testdb. Error code is ORA-12514.
2020-10-27T12:44:02.112-06:00
Failed to connect to remote database testdb. Error is ORA-12514
Failed to send message to member testdb. Error code is ORA-12514.

The next step was to verify the status of both the new primary and the new standby databases. It seemed the new primary never moved from MOUNT state to OPEN state, so I opened it. Similar to the new standby, the new primary had never started, so I started the DB.

################################################################################################
# New Primary Database
################################################################################################

SQL> set lines 200 pages 99
SELECT NAME,DB_UNIQUE_NAME,OPEN_MODE,PROTECTION_MODE,
       PROTECTION_LEVEL,
       DATABASE_ROLE ROLE,
       SWITCHOVER_STATUS
from gv$DATABASE;  

NAME      DB_UNIQUE_NAME         OPEN_MODE          PROTECTION_MODE       PROTECTION_LEVEL    ROLE         SWITCHOVER_STATUS
--------- ------------------------------ -------------------- -------------------- -------------------- ---------------- --------------------
TESTDB   TESTDB_STDBY          MOUNTED          MAXIMUM PERFORMANCE  UNPROTECTED        PRIMARY      NOT ALLOWED

SQL> alter database open;

Database altered.

SELECT NAME,DB_UNIQUE_NAME,OPEN_MODE,PROTECTION_MODE,
       PROTECTION_LEVEL,
       DATABASE_ROLE ROLE,
       SWITCHOVER_STATUS
from gv$DATABASE;SQL>   2    3    4    5  

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      PROTECTION_MODE	   PROTECTION_LEVEL	ROLE		 SWITCHOVER_STATUS
--------- ------------------------------ -------------------- -------------------- -------------------- ---------------- --------------------
DB193H1   DB193H1_STDBY 		 READ WRITE	      MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE	PRIMARY 	 TO STANDBY

################################################################################################
# New Standby Database
################################################################################################

[oracle@primary ~]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 28 11:03:09 2020
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2415917880 bytes
Fixed Size		    8899384 bytes
Variable Size		  520093696 bytes
Database Buffers	 1879048192 bytes
Redo Buffers		    7876608 bytes
Database mounted.
Database opened.
SQL> set lines 200 pages 99
SELECT NAME,DB_UNIQUE_NAME,OPEN_MODE,PROTECTION_MODE,
       PROTECTION_LEVEL,
       DATABASE_ROLE ROLE,
       SWITCHOVER_STATUS
from gv$DATABASE;  SQL>   2    3    4    5  

NAME	  DB_UNIQUE_NAME		 OPEN_MODE	      PROTECTION_MODE	   PROTECTION_LEVEL	ROLE		 SWITCHOVER_STATUS
--------- ------------------------------ -------------------- -------------------- -------------------- ---------------- --------------------
DB193H1   DB193H1			 READ ONLY	      MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE	PHYSICAL STANDBY NOT ALLOWED

When I checked the broker status, everything seemed bogus. As you’ll see, the broker is still showing the old roles of the databases and as you see above, the roles had switched correctly.

[oracle@standby ~]$ dgmgrl sys@TESTDB_STDBY
Password:
Connected to "TESTDB_STDBY"
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - db_broker_config

  Protection Mode: MaxPerformance
  Members:
  testdb       - Primary database
    Error: ORA-16810: multiple errors or warnings detected for the member

    testdb_stdby - Physical standby database 
      Error: ORA-16810: multiple errors or warnings detected for the member

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 25 seconds ago)

Instead of immediately fixing the broker configuration, I focused on the next error after the switchover, which was the TNS-12564: TNS:connection refused error.

################################################################################################
# From the new Primary Database log
################################################################################################

SWITCHOVER: completed request from primary database.
2020-10-27T06:55:26.572528-06:00
ARC0 (PID:18472): Becoming the 'no SRL' ARCH
2020-10-27T07:10:55.228901-06:00
***********************************************************************

Fatal NI connect error 12514, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=primary)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db193h1)(INSTANCE_NAME=DB193H1)(CID=(PROGRAM=oracle)(HOST=standby)(USER=oracle))))

  VERSION INFORMATION:
        TNS for Linux: Version 19.0.0.0.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
  Version 19.9.0.0.0
  Time: 27-OCT-2020 07:10:55
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12564

TNS-12564: TNS:connection refused
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0

################################################################################################
# From the new Standby Database log
################################################################################################

TT02 (PID:13281): All non-current ORLs have been archived
2020-10-27T07:16:08.979966-06:00
***********************************************************************
Fatal NI connect error 12514, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=standby)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db193h1_stdby)(CID=(PROGRAM=oracle)(HOST=primary)(USER=oracle))))

  VERSION INFORMATION:
        TNS for Linux: Version 19.0.0.0.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
  Version 19.9.0.0.0
  Time: 27-OCT-2020 07:16:08
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12564

TNS-12564: TNS:connection refused
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0

After troubleshooting connectivity via the connector descriptor from the old primary database log above, I found that the service db193h1_stdby was not registered in the standby listener, and that the service registered was incorrect. The same was true for the old primary database.

[oracle@standby ~]$ lsnrctl services
...
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standby)(PORT=1521)))
Services Summary...
Service "TESTDB_DGMGRL" has 1 instance(s).
  Instance "TESTDB", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:31 refused:0
         LOCAL SERVER
The command completed successfully

I next verified the value of the parameter LOCAL_LISTENER on the new primary and saw it had the incorrect value; it was the value of an old listener that was no longer present. So I changed it to the current LISTENER.

SQL> show parameter local_listener

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
local_listener			     string	 LISTENER_STBY

SQL> alter system set local_listener='LISTENER';

System altered.

It’s important to note that as of Oracle 12.1 in a RAC Data Guard environment, it’s critical not to have the parameter LOCAL_LISTENER set as per documentation. So you don’t want to do what I did above; what you want to do is the following:  SQL> ALTER SYSTEM RESET LOCAL_LISTENER SCOPE=BOTH SID='*'; 

It is important to know that after modifying the parameter LOCAL_LISTENER, you will have to recreate the DG broker configuration.

I had to verify the tnsnames.ora file had a network name that resolved to an address. Since this wasn’t the case, I proceeded to add it. Remember, this is only for non-RAC instances; there’s no need to add the address list to the tnsnames.ora in a RAC environment.

[oracle@standby admin]$ cat tnsnames.ora | grep LISTENER | wc -l
0
[oracle@standby admin]$ vi tnsnames.ora
[oracle@standby admin]$ cat tnsnames.ora | head -2
LISTENER =
  (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
[oracle@standby admin]$ lsnrctl reload
...
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standby)(PORT=1521)))
The command completed successfully

[oracle@standby admin]$ lsnrctl services
...
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standby)(PORT=1521)))
Services Summary...
Service "DB193H1XDB" has 1 instance(s).
  Instance "DB193H1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: standby, pid: 2094>
         (ADDRESS=(PROTOCOL=tcp)(HOST=standby.localdomain)(PORT=21723))
Service "DB193H1_CFG" has 1 instance(s).
  Instance "DB193H1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "DB193H1_STDBY" has 1 instance(s).
  Instance "DB193H1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "DB193H1_STDBY_DGMGRL" has 1 instance(s).
  Instance "DB193H1", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

Once I had corrected this, I proceeded to recreate the Data Guard broker, as when you change the parameter LOCAL_LISTENER, the Data Guard configuration will start behaving abnormally if you don’t recreate it.


Recreating the Data Guard broker

1. From the new primary:

[oracle@standby ~]$ dgmgrl sys@TESTDB_STDBY
Password:
Connected to "TESTDB_STDBY"
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - db_broker_config

  Protection Mode: MaxPerformance
  Members:
  testdb       - Primary database
    Error: ORA-16810: multiple errors or warnings detected for the member

    testdb_stdby - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 25 seconds ago)

DGMGRL> remove configuration;
Removed configuration

DGMGRL> exit

[oracle@standby ~]$ sqlplus / as sysdba

SQL> alter system set dg_broker_start=false scope=both;

System altered.

SQL> show parameter dg_broker_config_file

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1		     string	 /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/dr1TESTDB_STDBY.dat
dg_broker_config_file2		     string	 /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/dr2TESTDB_STDBY.dat

SQL> ! rm /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/dr*TESTDB_STDBY.dat

SQL> alter system set dg_broker_start=true scope=both;

System altered.
SQL> exit

2. From the new standby:

[oracle@primary ~]$ sqlplus / as sysdba

SQL> alter system set dg_broker_start=false scope=both;

System altered.

SQL> show parameter dg_broker_config_file

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1		     string	 /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/dr1TESTDB.dat
dg_broker_config_file2		     string	 /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/dr2TESTDB.dat

SQL> ! rm /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/dr*TESTDB.dat

SQL> alter system set dg_broker_start=true scope=both;

System altered.

3. From the new primary:

[oracle@primary ~]$ dgmgrl sys@TESTDB_STDBY
Password:
Connected to "TESTDB_STDBY"
Connected as SYSDBA.

DGMGRL> CREATE CONFIGURATION db_broker_config AS PRIMARY DATABASE IS testdb_stdby CONNECT IDENTIFIER IS testdb_stdby;
Configuration "db_broker_config" created with primary database "testdb_stdby"

DGMGRL> ADD DATABASE testdb AS CONNECT IDENTIFIER IS testdb;
Database "testdb" added

DGMGRL> ENABLE CONFIGURATION;
Enabled.

Finally, after modifying the LOCAL_LISTENER and recreating the DG Broker, I was able to switch back and forth between both databases without any issues.

DGMGRL> switchover to testdb;     
Performing switchover NOW, please wait...
Operation requires a connection to database "testdb"
Connecting ...
Connected to "TESTDB"
Connected as SYSDBA.
New primary database "testdb" is opening...
Operation requires start up of instance "TESTDB" on database "testdb_stdby"
Starting instance "TESTDB"...
Connected to an idle instance.
ORACLE instance started.
Connected to "TESTDB_STDBY"
Database mounted.
Database opened.
Connected to "TESTDB_STDBY"
Switchover succeeded, new primary is "testdb"

DGMGRL> switchover to testdb_stdby;
Performing switchover NOW, please wait...
Operation requires a connection to database "testdb_stdby"
Connecting ...
Connected to "TESTDB_STDBY"
Connected as SYSDBA.
New primary database "testdb_stdby" is opening...
Operation requires start up of instance "TESTDB" on database "testdb"
Starting instance "TESTDB"...
Connected to an idle instance.
ORACLE instance started.
Connected to "TESTDB"
Database mounted.
Database opened.
Connected to "TESTDB"
Switchover succeeded, new primary is "testdb_stdby"

From the above post, you can see the importance of having the database parameter LOCAL_LISTENER set properly. Otherwise you’ll experience a set of headaches during a switchover, even if everything else seems set to work out.

Note: This was originally posted on rene-ace.com.

email

Authors

Interested in working with Rene? Schedule a tech call.

About the Author

Currently I am an Oracle ACE ; Speaker at Oracle Open World, Oracle Developers Day, OTN Tour Latin America and APAC region and IOUG Collaborate ; Co-President of ORAMEX (Mexico Oracle User Group); At the moment I am an Oracle Project Engineer at Pythian. In my free time I like to say that I'm Movie Fanatic, Music Lover and bringing the best from México (Mexihtli) to the rest of the world and in the process photographing it ;)

4 Comments. Leave new

Massimo Tinelli
November 23, 2020 3:56 am

Good morning, we had same issue in 12c, but in that situation the instance did not start:
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name ‘LISTENER_FRRC1’
We removed the “local_listener” from spfile converting it to pfile (https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=181220805316640&id=359386.1&displayIndex=5&_afrWindowMode=0&_adf.ctrl-state=17pfdgt620_172#SYMPTOM)

In oracle 19c there was the same wrong configuration in local_listener with LISTENER_ (from DBCA), but the instance started and switchover had problems.
We resolved resetting local listener on both primary and standby:
SQL>alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PRIMARY)(PORT=1521)))’ scope=both;

SQL>alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=STANDBY)(PORT=1521)))’ scope=both;

BUT
we need not to re-create DG broker config.
Regards
Massimo

Reply

I actually highly recommend that you do recreate the broker if you are using a version lower than 18.x as per MOS doc Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1) . If you are using a version 18.x or higher, you can use VALIDATE STATIC CONNECT IDENTIFIER FOR PRIMARY_db;

Reply
Massimo Tinelli
November 23, 2020 11:02 am

Thanks Rene. Thanks for the clarification.
We have a 19c couple of instances, I am adding another physical standby, next month.
Did you make an article about this ? ;-)
I will use this oracle note: https://support.oracle.com/knowledge/Oracle%20Database%20Products/842822_1.html
Thanks again
Bye
Massimo

Reply

I haven’t but just adding a second standby is similar to adding a first. That MOS document is a good one, so you shouldn’t have any issues doing that

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *