Sunday, November 23, 2014

ORA-12514 During Switchover to Standby

Recently I was performing a switchover from primary to standby database using DGMGRL utility (My Dataguard environment utilizes Dataguard broker). I logged into the server which was hosting current standby database and initiated the DGMGRL command line interface. When I issued the switchover statement, DG Broker was able to convert
primary to standby and standby to primary and bringing up the current primary database, but it returned ORA-12514 while starting up the standby (previous primary on the remote host) and suggested to start the standby manually as can be seen bellow


DGMGRL> switchover to 'MYDB_PROD';
Performing switchover NOW, please wait...
New primary database "MYDB_PROD" is opening...
Operation requires shutdown of instance "mydb" on database "MYDB_STBY"
Shutting down instance "mydb"...
ORACLE instance shut down.
Operation requires startup of instance "mydb" on database "MYDB_STBY"
Starting instance "mydb"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
start up instance "mydb" of database "MYDB_STBY"


Reason of the Issue
The reason for this issue was that my both primary and standby databases were not registered with the respective listeners. If we check the StaticConnectIdentifier property of primary and standby database, we can see that SERVICE_NAME by default has a value in the format  <unique_db_name>_DGMGRL.DOMAIN_NAME ( In my case domain_name was not specified and hence it was null) and this SERVICE_NAME is required to be registered with the listener so that DG Broker can connect to the respective database to complete the switchover operation.

DGMGRL> show instance verbose 'mydb' on database 'MYDB_PROD';
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.23.23.16)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=
mydb_prod_DGMGRL)(INSTANCE_NAME=mcydb)(SERVER=DEDICATED)))'

DGMGRL> show instance verbose 'mydb' on database 'MYDB_STBY';
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.23.23.17)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=
mydb_stby_DGMGRL)(INSTANCE_NAME=mydb)(SERVER=DEDICATED)))'

When I checked that whether my listeners on both primary and standby having abovementioned service_name(s) registered with them or not, and I found that these are not registered as can be seen bellow.
[grid@PRIMARYDB01 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 18-NOV-2014 11:31:33

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                11-NOV-2014 10:53:15
Uptime                    7 days 0 hr. 38 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11203/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/PRIMARYB01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PRIMARYB01.mydomain.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "MYDB" has 1 instance(s).
  Instance "mydb", status READY, has 1 handler(s) for this service...
Service "MYDBXDB" has 1 instance(s).
  Instance "mydb", status READY, has 1 handler(s) for this service...
Service "mydb_prod" has 1 instance(s).
  Instance "mydb", status READY, has 1 handler(s) for this service...
Service "mydb_prod_DGB" has 1 instance(s).
  Instance "mydb", status READY, has 1 handler(s) for this service...
The command completed successfully

Above command was issued on primary server where it can be seen that no “mydb_prod_DGMGRL” is registered with the listener and this actually caused ORA-12514 error and hence DBA intervention was required to start up the remote standby database after the switchover.
It will be pertinent to mention here that that service “mydb_prod_DGB” is a default service registered to the listener used by DMON process to perform different kind of maintenance tasks.

Solution
To solve the issue, it is required to add static registration entries in LISTENER.ORA file on both primary and standby servers. In my case, I added following entries on my primary and standby listener.ora files

On Primary LISTENER.ORA
SID_LIST_LISTENER =
        (SID_LIST =
                (SID_DESC =
                        (GLOBAL_DBNAME = mydb_prod_DGMGRL)
                        (SID_NAME = mydb)
                        (ORACLE_HOME = /u01/app/oracle/product/11203/db1)
                )
        )



On Standby LISTENER.ORA
SID_LIST_LISTENER =
        (SID_LIST =
                (SID_DESC =
                        (GLOBAL_DBNAME = mydb_stby_DGMGRL)
                        (SID_NAME = mydb)
                        (ORACLE_HOME = /u01/app/oracle/product/11203/db1)
                )
        )

After this change, both listeners (primary and standby) were able to register these services. For example, primary server’s listener showed following services registered with it.

[grid@PRIMARYB01 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 18-NOV-2014 11:31:33

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                11-NOV-2014 10:53:15
Uptime                    7 days 0 hr. 38 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11203/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/PRIMARYB01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PRIMARYB01.mydomain.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "MYDB" has 1 instance(s).
  Instance "mydb", status READY, has 1 handler(s) for this service...
Service "MYDBXDB" has 1 instance(s).
  Instance "mydb", status READY, has 1 handler(s) for this service...
Service "mydb_prod" has 1 instance(s).
  Instance "mydb", status READY, has 1 handler(s) for this service...
Service "mydb_prod_DGB" has 1 instance(s).
  Instance "mydb", status READY, has 1 handler(s) for this service...
Service "mydb_prod_DGMGRL" has 1 instance(s).
  Instance "mydb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

No comments:

Popular Posts - All Times