Forum Stats

  • 3,770,593 Users
  • 2,253,137 Discussions
  • 7,875,502 Comments

Discussions

SQlcl: Easy Connect Naming with INSTANCE_NAME

Franck Pachot
Franck Pachot Member Posts: 912 Bronze Trophy
edited Feb 23, 2018 10:01AM in SQLcl

Hi,

It seems that SQLcl doesn't handle the /instance_name in EZCONNECT ( doc: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/netag/configuring-naming-methods.html  )

SQLcl: Release 17.4.0 Production on Fri Jan 12 15:05:33 2018

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

15:05:38 SQL> connect scott/[email protected]//localhost:1521/PDB1:DEDICATED/CDB1  USER          = scott

  URL           = jdbc:oracle:oci8:@//localhost:1521/PDB1:DEDICATED/CDB1

  Error Message = ORA-12541: TNS:no listener

  USER          = scott

  URL           = jdbc:oracle:thin:@//localhost:1521/PDB1:DEDICATED/CDB1

  Error Message = Listener refused the connection with the following error:

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

Warning: You are no longer connected to ORACLE.

But this is ok in sqlplus:

15:06:02 SQL>

15:06:05 SQL> host sqlplus scott/[email protected]//localhost:1521/PDB1:DEDICATED/CDB1

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 12 15:06:11 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Fri Jan 12 2018 15:04:02 EUROPE/ZURICH CET

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

This should be translated to:

(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=PDB1)(INSTANCE_NAME=CDB1)(SERVER=DEDICATED))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))

Regards,

Franck.

Tagged:
Franck Pachot

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Jan 16, 2018 3:01AM

    Hey Franck, you should raise an SR at support.oracle.com for this, sqlcl is misinterpreting the connection string.

    A bit more info for anyone who is interested:

    sqlplus -l -s gaz/[email protected]//169.101.1.9:1521/ora12pdb:dedicated/ora12cr2

    listener.log snippet for sqlplus login:

    16-JAN-2018 17:39:14 * (CONNECT_DATA=(SERVICE_NAME=ora12pdb)(INSTANCE_NAME=ora12cr2)(SERVER=dedicated)(CID=(PROGRAM=F:\oracle\instantclient_12_2\sqlplus.exe)(HOST=GAZ-PC)(USER=Gaz))) * (ADDRESS=(PROTOCOL=tcp)(HOST=169.101.1.9)(PORT=49768)) * establish * ora12pdb * 0

    sql -l -s gaz/[email protected]//169.101.1.9:1521/ora12pdb:dedicated/ora12cr2

    listener.log snippet for sqlcl login:

    16-JAN-2018 17:43:28 * (CONNECT_DATA=(SERVICE_NAME=ora12pdb:dedicated/ora12cr2)(CID=(PROGRAM=C:\Program?Files\Java\jdk1.8.0_144\jre\bin\java.exe)(HOST=GAZ-PC)(USER=Gaz))) * (ADDRESS=(PROTOCOL=tcp)(HOST=169.101.1.9)(PORT=49772)) * establish * ora12pdb:dedicated/ora12cr2 * 1251416-JAN-2018 17:43:29 * (CONNECT_DATA=(CID=(PROGRAM=SQLcl)(HOST=__jdbc__)(USER=Gaz))(SERVICE_NAME=ora12pdb:dedicated/ora12cr2)) * (ADDRESS=(PROTOCOL=tcp)(HOST=169.101.1.9)(PORT=49773)) * establish * ora12pdb:dedicated/ora12cr2 * 12514

    ...so "we" can see that the connection string passed to the listener is ok for sqlplus, and connection is successfully handed off to a dedicated port. sqlcl how ever, errors with 12514.

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Jan 16, 2018 1:32PM

    Yes, the SR route is the way to go, both with regard to parsing of the CONNECT command in SQLcl and how SQLcl parses the arguments passed in when launched from the OS command line.

    Doing the same check of the listener.log when opening TNS or Advanced (with a custom JDBC URL) connection types from SQL Developer with INSTANCE_NAME specified, it works fine.  Same thing when connecting in SQLcl with a TNS alias, so there is your workaround.  As far as I know, this case is only important for RAC scenarios where you need to connect to a specific instance. Of course for a single instance with one or more PDBs, only the service name of the PDB is required.

  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy
    edited Feb 23, 2018 9:06AM

    Hi,

    I'll open an SR. I was hoping that this forum was sufficient.

    Just to add to "only the service name of the PDB is required" that's usually right but you may have a standby database on the same server as the primary, registered to the same listener, and then you need to mention the instance.

    Regards,

    Franck.

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Feb 23, 2018 10:01AM
    I'll open an SR. I was hoping that this forum was sufficient.

    Sometimes bugs do get fixed (with or without anyone actually logging a bug) based just on forum input but, as Jeff always says, the forum is no substitute for My Oracle Support.  Thanks for the additional explanation on INSTANCE_NAME use cases.

    Franck Pachot