I am trying to change oracle tnslsnr port number from default to some other port. After I change port number for tnslsnr from 1521 to 2000 I start geting
ORA-12505, TNS:listener does not currently know of SID from JDBC driver. Everything works fine as long as port is 1521.
Content of my listener.ora file:
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 2000))
)
)
Content of my tnsnames.ora file:
TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 2000))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb.localdomain)
)
)
JDBC URL I am trying to connect to:
jdbc:oracle:thin:@testhost:2000:testdb
Database version is 12c, OS is CentOS 5.8
Best Answer
TNSNAMES is a "connection-information-abstraction". This is where you hide hostnames, failover-information, port-number, servicename and god knows what.
Why do you connect using hostname, port and sid?
Hey! Because you are not using TNSNAMES! Your connect-string is a HOST connection. A host-connection goes straight to the host and makes no notice of your tnsnames.ora file. (You haven't instructed JDBC to look there...)
Next, it is not recommended to connect to an oracle database specifying the SID. Rather specify the SERVICE_NAME. (For 12c, SID should not be used anymore)
To identify the service_name of your instance:
To utilize TNSNAMES for jdbc have a look here
Is the listener LISTENING on port 2000?
The instance tries to register to the listener with a default port value of 1521. It is called dynamic instance registration. The registration to the listener will fail when using a non-default port-value. To adjust for this, the init parameter LOCAL_LISTENER comes into the picture like this:
Now it will work. 100%
Go here for a thorough explanation of the LOCAL_LISTENER parameter