Thursday, October 30, 2014

ORA-12514 During OEM Database Control Configuration for RAC

While configuring OEM Database Control for 2 nodes 11.2.0.3 RAC (On windows), I faced error "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor", about which I would be discussing in the following that what was the reason for this error and how I resolved it. 

Following screenshot shows the error message during configuration steps.


EMCA Log was showing following messages. Error has also been highlighted in bold/red

#############################################
...
...
Sep 25, 2014 12:18:42 PM oracle.sysman.emcp.util.ClusterUtil getLocalNode
CONFIG: isLocalNodeDone: true localNode: hjpmsdb1
Sep 25, 2014 12:18:42 PM oracle.sysman.emcp.util.ClusterUtil getHostName
CONFIG: Specified node: hjpmsdb1
Sep 25, 2014 12:18:44 PM oracle.sysman.emcp.ParamsManager getHost
CONFIG: Host having instnces running for database db : hjpmsdb2
Sep 25, 2014 12:18:44 PM oracle.sysman.emcp.ParamsManager setParam
CONFIG: Setting param: REP_DB_RUNNING_HOST value: hjpmsdb2
Sep 25, 2014 12:18:44 PM oracle.sysman.emcp.ParamsManager getParam
CONFIG: No value was set for the parameter SYSMAN_PWD.
Sep 25, 2014 12:18:44 PM oracle.sysman.emcp.ParamsManager getRepDbPort
CONFIG: Listener Port: 1522
Sep 25, 2014 12:18:44 PM oracle.sysman.emcp.util.GeneralUtil isLocalConnectionRequired
CONFIG: isLocalConnectionRequired: false
Sep 25, 2014 12:18:44 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngine
CONFIG: isLocalConnectionRequired: false. Connecting to database instance remotely.
Sep 25, 2014 12:18:45 PM oracle.sysman.emcp.util.ClusterUtil getScanName
CONFIG: Sacn Name: HJPMSDBSCAN
Sep 25, 2014 12:18:45 PM oracle.sysman.emcp.util.ClusterUtil getScanPort
CONFIG: Sacn Port: 1522
Sep 25, 2014 12:18:45 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngineRemotely
CONFIG: SQLEngine connecting with Service Name: db, oracleHome: D:\oracle\11203\DB, and user: SYS scan name: HJPMSDBSCAN scan port: 1522
Sep 25, 2014 12:18:46 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngineRemotely
WARNING: Error during db connection : ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Sep 25, 2014 12:18:46 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngineRemotely
CONFIG: Waiting for 5 second before reconnection
Sep 25, 2014 12:18:51 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngineRemotely
CONFIG: SQLEngine connecting with Service Name: db, oracleHome: D:\oracle\11203\DB, and user: SYS host: hjpmsdb2 port: 1522
Sep 25, 2014 12:18:51 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngineRemotely
CONFIG: SQLEngine created successfully and connected
Sep 25, 2014 12:18:51 PM oracle.sysman.emcp.util.ClusterUtil getLocalNode
CONFIG: isLocalNodeDone: true localNode: hjpmsdb1
Sep 25, 2014 12:18:51 PM oracle.sysman.emcp.util.ClusterUtil getHostName
CONFIG: Specified node: hjpmsdb1
Sep 25, 2014 12:18:51 PM oracle.sysman.emcp.util.ClusterUtil getLocalNode
CONFIG: isLocalNodeDone: true localNode: hjpmsdb1
Sep 25, 2014 12:18:51 PM oracle.sysman.emcp.ParamsManager getParam
CONFIG: No value was set for the parameter NODE_LIST.
Sep 25, 2014 12:18:51 PM oracle.sysman.emcp.ParamsManager getInaccessibleNodeList
CONFIG: Invalid Node/Sid List specified.
Sep 25, 2014 12:18:51 PM oracle.sysman.emcp.ParamsManager getVersion
CONFIG: Failed to obtain paraller server version
Sep 25, 2014 12:18:53 PM oracle.sysman.emcp.ParamsManager setConfiguredNodeSidList
CONFIG: db1 running from node : hjpmsdb1
Sep 25, 2014 12:18:53 PM oracle.sysman.emcp.ParamsManager setConfiguredNodeSidList
CONFIG: db2 running from node : hjpmsdb2
Sep 25, 2014 12:18:54 PM oracle.sysman.emcp.util.ClusterUtil isCardinal
...
...
#############################################

Reason of the Problem
This will be pertinent to mention that OEM Database control for RAC needs SCAN Listener to be configured and all instances (including ASM) are registered with the SCAN. If SCAN is configured on port 1521, we don't need any extra steps, but in my case; my SCAN (and local listeners) were listening on port 1522 and hence SCAN Listener was not able to register both instances - as can been seen in bellow screenshot where SCAN Listener has no registered instance. 

Since my application does not utilize SCAN Listener and connects through local listeners so I did not face any issue in normal database operations by my application.


Solution
Starting 11.2, Oracle recommends to use SCAN Listener which runs from Grid Infrastructure home, and set REMOTE_LISTENER parameter to the address of SCAN Listener so that each instance (including ASM instances) register themselves with the SCAN Listener. Since my application does not support SCAN, I needed to set REMOTE_LISTENER init parameter value to be resolved to the TNS listeners. In this case, I needed to add ADDRESS of SCAN Listener to my TNS entry I was using in REMOTE_LISTENER so that SCAN Listener can also register my instances. In the following you can see (highlighted in red) how I added address of SCAN Listener. This needs to be done for ASM instances REMOTE_LISTENER TNS entry also so that ASM instances from both nodes could also get themselves registered with the SCAN Listener.

LISTENER_SCAN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost1vip)(PORT = 1522))
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost2vip)(PORT = 1522))
      (ADDRESS = (PROTOCOL = TCP)(HOST = db_scan)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db)
    )

  )

Note again that LISTENER_SCAN is the TNS entry I am using in my REMOTE_LISTENER init parameter.
After this, my SCAN Listener was able to register both RAC instances and also both ASM instances


After this, I was successfully able to configure the OEM for this RAC setup


C:\>emca -config dbcontrol db -repos recreate -cluster -CLUSTER_NAME GRID -EM_NODE hjpmsdb1 -EM_SID_LIST db1,db2

STARTED EMCA at Sep 25, 2014 2:04:26 PM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:
Database unique name: db
Service name: db
Listener port number: 1522
Listener ORACLE_HOME [ D:\oracle\11203\grid ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ D:\oracle\11203\grid ]:
ASM port [ 1522 ]:
ASM username [ ASMSNMP ]:
ASM user password:
ASM user password: -----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ D:\oracle\11203\DB

Database instance hostname ................ Listener ORACLE_HOME ................ D:\oracle\11203\grid
Listener port number ................ 1522
Cluster name ................ GRID
Database unique name ................ db
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
ASM ORACLE_HOME ................ D:\oracle\11203\grid
ASM port ................ 1522
ASM user role ................ SYSDBA
ASM username ................ ASMSNMP

-----------------------------------------------------------------
----------------------------------------------------------------------
WARNING : While repository is dropped the database will be put in quiesce mode.
----------------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Sep 25, 2014 2:05:12 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at D:\orabase\cfgtoollogs\emca\db\emca_2014_09_25_14_04_25.log.
Sep 25, 2014 2:05:24 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Sep 25, 2014 2:07:26 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Sep 25, 2014 2:07:27 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Sep 25, 2014 2:13:52 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Sep 25, 2014 2:13:59 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Sep 25, 2014 2:14:47 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Sep 25, 2014 2:14:49 PM oracle.sysman.emcp.EMDBCConfig instantiateOC4JConfigFiles
INFO: Propagating D:\oracle\11203\DB\oc4j\j2ee\OC4J_DBConsole_hjpmsdb1_db to remote nodes ...
Sep 25, 2014 2:14:49 PM oracle.sysman.emcp.EMDBCConfig instantiateOC4JConfigFiles
INFO: Propagating D:\oracle\11203\DB\oc4j\j2ee\OC4J_DBConsole_hjpmsdb2_db to remote nodes ...
Sep 25, 2014 2:14:53 PM oracle.sysman.emcp.EMAgentConfig deployStateDirs
INFO: Propagating D:\oracle\11203\DB\hjpmsdb1_db to remote nodes ...
Sep 25, 2014 2:14:54 PM oracle.sysman.emcp.EMAgentConfig deployStateDirs
INFO: Propagating D:\oracle\11203\DB\hjpmsdb2_db to remote nodes ...
Sep 25, 2014 2:15:13 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Sep 25, 2014 2:15:24 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Sep 25, 2014 2:16:46 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Sep 25, 2014 2:16:46 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://HJPMSDB1:1158/em <<<<<<<<<<<
Sep 25, 2014 2:16:48 PM oracle.sysman.emcp.EMDBPostConfig showClusterDBCAgentMessage
INFO:
****************  Current Configuration  ****************
 INSTANCE            NODE           DBCONTROL_UPLOAD_HOST
----------        ----------        ---------------------

Db1             hjpmsdb1             HJPMSDB1
Db2             hjpmsdb2             HJPMSDB1


Sep 25, 2014 2:16:48 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: D:/oracle/11203/DB/hjpmsdb1_db/sysman/config/emkey.ora. Ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Sep 25, 2014 2:16:48 PM

C:\>