ORA-12505, TNS:listener does not currently know of SID after changing port number

Question

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

Solution

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?

 jdbc:oracle:thin:@testhost:2000:testdb

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)

 jdbc:oracle:thin:@testhost:2000/SERVICE_NAME  

To identify the service_name of your instance:

$sqlplus / as sysdba
SQL>show parameter service

To utilize TNSNAMES for jdbc have a look here

Is the listener LISTENING on port 2000?

#netstat -tulpn | grep :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:

$sqlplus / as sysdba     
SQL>alter system set LOCAL_LISTENER='(ADDRESS =(PROTOCOL=TCP)(HOST=localhost)(PORT=2000);

Now it will work. 100%

Go here for a thorough explanation of the LOCAL_LISTENER parameter

Comments

[user2391]

Port 2000 is opened and I can connect to it from remote machine. tnsnames.ora file is from server and tnsping testdb run on server returns OK. Without SID i get 'ORA-12504, TNS:listener was not given the SID in CONNECT_DATA', with slash before service name or sid I get 'ORA-12514, TNS:listener does not currently know of service requested in connect descriptor'. As noted everything works ok as long as I dont change port from 1521.

[user2391]

alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT‌​=2000))' scope=both; did the trick.