I have a baffling problem. I have an Oracle 11g set up on Linux that was working fine. I was able to connect remotely from other machines using a SID based connection string such as
sqlplus myschema@192.168.184.102:1521:orcl
then for some inexplicable reason it stopped working. I'm not sure what I or someone else jiggled loose.
now i'm only able to connect remotely using a SERVICE_NAME or TNSNAME based methods
sqlplus myschema@192.168.184.102:1521/orcl
sqlplus myschema@ORCL
Both work and this would be fine and well, but I have applications that rely on SID connection pattern that I can change. Could some kind soul help me figure out what I'm missing
Below is my configuration:
LISTENER.ORA:
[oracle@dbora112 ~]$ cat /opt/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /opt/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dbora112)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /opt/u01/app/oracle
LISTENER STATUS:
[oracle@dbora112 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 04-MAR-2015 14:50:05
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 Linux: Version 11.2.0.3.0 - Production
Start Date 04-MAR-2015 14:37:12
Uptime 0 days 0 hr. 12 min. 52 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /opt/u01/app/oracle/diag/tnslsnr/dbora112/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbora112)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
TNSNAMES.ORA:
[oracle@dbora112 ~]$ cat /opt/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.184.102)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = orcl)
)
)
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.184.102)(PORT = 1521))
SQLNET:
[oracle@dbora112 ~]$ cat /opt/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /opt/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE = /opt/u01/app/oracle
Can connect with not problem using TNS
[oracle@dbora112 ~]$ sqlplus schema/xxx@ORCL
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 4 14:54:10 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Can connect with no problem using Service Name
[oracle@dbora112 ~]$ sqlplus schema/xxx@192.168.184.102/ORCL
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 4 14:55:52 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Cannnot connect with SID
[oracle@dbora112 ~]$ sqlplus schema/xxx@192.168.184.102:orcl
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 4 14:56:38 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12545: Connect failed because target host or object does not exist
Database Instance (SID)
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
orcl
Since you can connect with your TNS alias, and that is using the SID in the connection description, the SID is working.
But your EasyConnect string is incorrect. From the SQL*Plus documentation:
The easy or abbreviated connection identifier has the syntax:
[//]host[:port][/service_name]
You cannot use the SID with Easy Connect, only the service name; so your connect using:
sqlplus schema/xxx@192.168.184.102:orcl
or even
sqlplus schema/xxx@192.168.184.102:1521:orcl
... can never have worked. You can use that form with a JDBC URL using the older style:
jdbc:oracle:thin:@192.168.184.102:1521:orcl
... but that is not the same thing as an SQL*Plus connection, despite looking similar.
If you're trying to check if a JDBC connection of that form will work, you cannot do so with SQL*Plus, but your TNS alias check is verifying it is set up as you expect anyway - as long as it uses the same host, port and SID. It would probably be better to use the newer JDBC syntax, but from the question it sounds like you can't change that.