Oracle 11.2.0.3 Service Name Mismatch issue

oracleoracle-11g

My Environment

During Installation

SID lab3

Service_Name lab3.domain.com

Changes

Some time ago I changed my service_name to just lab3 to match my SID

Select instance_name from v$instance;

lab3

Select value from v$parameter where name like '%service_name%';

lab3

Issue

when i try to connect from my application
jdbc:mercury:oracle://192.168.1.203:1521;servicename=lab3 doesn't work

jdbc:mercury:oracle://192.168.1.203:1521;servicename=lab3.domain.com works while this is not my service name anymore

My application doesn't require any oracle client installed so there is no tons file or anything else on the client side. it's all on server side

Error in Application Logs

java.sql.SQLNonTransientConnectionException: [Mercury][Oracle JDBC Driver][Oracle]ORA-12514 Connection refused, the specified service name (lab3) was not recognized by the Oracle server. at com.mercury.jdbc.oraclebase.ddcr.b(Unknown Source) at com.mercury.jdbc.oraclebase.ddcr.a(Unknown Source) at

lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 – Production on 06-MAR-2016 20:34:40
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 64-bit Windows: Version 11.2.0.3.0 – Production
Start Date 06-MAR-2016 20:02:45
Uptime 0 days 0 hr. 32 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\Administrator\product\11.2.0\network\admin\listener.ora
Listener Log File C:\oracle\Administrator\diag\tnslsnr\Lab3\listener\alert\log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Lab3.domain.com)(PORT=1521)))

Services Summary…

Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service…

Service "lab3.domain.com" has 1 instance(s).
Instance "lab3", status READY, has 1 handler(s) for this service…

Service "lab3XDB.domain.com" has 1 instance(s).
Instance "lab3", status READY, has 1 handler(s) for this service…
The command completed successfully

SQL> show parameter db_domain;

NAME TYPE VALUE

db_domain string domain.com

Best Answer

SERVICE_NAME is made up of the values db_unique_name and db_domain parameters. Here is the reference to Oracle Documentation about SERVICE_NAME.
Even if you set lab3 as service name oracle automatically adds value of db_domain on it. Your service_name is 'lab3' and db_domain is 'domain.com' so you end up with the orgianl service name. Instead you can create tns service name to point that service. Example is given below

lab3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lab3.domain.com)
    )
  )

Then you can use this tnsname(lab3).

Related Question