Windows – EMCA insists that it cannot connect to the service

oracleoracle-11goracle-enterprise-managerwindows-server

Really new to Oracle and being a DBA in general. I am trying to set up a development environment so that I can play an learn oracle better. Enterprise Mananger failed to configure itself when I first created the database using the Database Configuration Assistant. No biggie. Just need to user emca.exe

I had some issues with the Listener but those might have just been me being impatient in waiting for the service to register or the service not running. Right now my issue is this from the emca log:

CONFIG: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

According to post here and on SO it should just be an issue with the service name. Problem is it looks right to me.

Listener.ora

ADR_BASE_VLISTENER = D:\app\Administrator

VLISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dvp-oracle)(PORT = 1521))
    )
  )

lsnrctl status

.... truncated ....
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dvp-oracle)(PORT=1521)))
Services Summary...
Service "prodbkp" has 1 instance(s).
  Instance "prodbkp", status READY, has 1 handler(s) for this service...
Service "prodbkpXDB" has 1 instance(s).
  Instance "prodbkp", status READY, has 1 handler(s) for this service...
The command completed successfully

That tells me that the database registered itself dynamically correctly. prodbkp is my SID. This might be a case issue since I named the DB "PRODbkp" but everything else seems to be fine since I can connect with sqlplus sys@prodbkp as sysdba just fine. Case should not be an issue with service names as per docs.oracle.com

tsnnames.ora

PRODBKP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dvp-oracle)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prodbkp)
    )
  )

Which also looks OK. Most of the solutions point to SERVICE_NAME mismatches. However I don't appear to have one.

v$parameter output

SQL> select value from v$parameter where name='service_names';

VALUE
--------------------------------------------------------------------------------

prodbkp

That gap you see above prodkbp is in the output as well. Not sure if that matters either. My production server has that anomaly as well so I can't imagine that has anything to do with it.

Basically everything looks right but something is still wrong. I cannot install Enterprise Manager

Environment

Windows Server 2008R2
Oracle 11g

Best Answer

So it would seem the issue was not the service name specifically but that the request was going to the IPv6 address which was not set up in any of the required files. Looking at listener.log ( which for me was located D:\app\Administrator\diag\tnslsnr\dvp-oracle\listener\trace\listener.log) I found these entries associated to my connnection attempts.

28-AUG-2017 08:41:58 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=prodbkp)(CID=(PROGRAM=D:\app\Administrator\product\11.2.0\dbhome_1\bin\sqlplus.exe)(HOST=DVP-ORACLE)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=fe80::7197:6503:9e81:5454%11)(PORT=49246)) * establish * prodbkp * 0

The important portion was HOST=fe80::7197:6503:9e81:5454%11. Since I would never need to support IPv6 I removed it from the network adapter and restarted the server to ensure all services were updated accordingly.

While it might not be the ideal solution I was able to recreate the EM dbcontrol successfully after that change.

Related Solutions

Check listener status other than 1521

Run:

lsnrctl status LISTENERNAME

... where LISTENERNAME is the name of the listener that is listening on port 1531. You can get this name from your $ORACLE_HOME/network/admin/listener.ora file.

For example:

LISTENER1531 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1531))
      )
    )
  )

LISTENER1531 would be the listener name for the above listener.ora entry.

If you need to add databases to the listener, add an entry to the listener.ora, as follows:

SID_LIST_LISTENER1531 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = YOURDB)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
      (SID_NAME = YOURDB)
    )
  )

Obviously you'll need to add your own database and listener names & change the path to your $ORACLE_HOME.

You can reload the listener config with:

lsnrctl reload LISTENER1531

The above works fine for me with a `tnsnames.ora as follows:

YOURDB =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = node1)(Port = 1531))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = YOURDB)
 )
)

Oracle connection suddenly refused on windows 8

Solved! The issue was LOCAL_LISTENER was not set to orcl. So

SQL> alter system register;

SQL> alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope=both;

did the trick!