6 Replies Latest reply: Sep 3, 2012 2:05 AM by Laurenz Albe RSS

    Configure SQL*plus with InstantClient to use TNSNAMES

    insanepi
      I got following configuration:
      Database - 10.2.0.4 EE x64 running on separate server, e.g. 192.168.1.1 with SID test

      Client - clean CentOS 6.3 x86 installation.

      I've downloaded oracle-instantclient-basic-10.2.0.4-1.i386.rpm and oracle-instantclient-sqlplus-10.2.0.4-1.i386.rpm and made rpm -i on them to install.

      Made following exports:
      LD_LIBRARY_PATH="/usr/lib/oracle/10.2.0.4/client/lib"
      TNS_ADMIN="/usr/lib/oracle/10.2.0.4/client/admin"
      
      /usr/lib/oracle/10.2.0.4/client
      ├── admin
      │   ├── sqlnet.ora
      │   └── tnsnames.ora
      ├── bin
      │   ├── genezi
      │   └── sqlplus
      └── lib
          ├── glogin.sql
          ├── libclntsh.so.10.1
          ├── libnnz10.so
          ├── libocci.so.10.1
          ├── libociei.so
          ├── libocijdbc10.so
          ├── libsqlplusic.so
          ├── libsqlplus.so
          └── ojdbc14.jar
      cat /usr/lib/oracle/10.2.0.4/client/admin/sqlnet.ora
      TRACE_LEVEL_CLIENT = OFF
      names.directory_path = (TNSNAMES, HOSTNAME)
      names.default_domain = world
      name.default_zone = world
      cat /usr/lib/oracle/10.2.0.4/client/admin/tnsnames.ora
      test.world=
         (DESCRIPTION =
           (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
           (CONNECT_DATA = (SID = test))
         )
      sqlplus access/access@192.168.1.1:1521/test
      SQL*Plus: Release 10.2.0.4.0 - Production on Tue Aug 28 14:45:18 2012
      Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
      
      ERROR:
      ORA-12154: TNS:could not resolve the connect identifier specified
      sqlplus access/access@test
      SQL*Plus: Release 10.2.0.4.0 - Production on Tue Aug 28 14:45:18 2012
      Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
      
      ERROR:
      ORA-12154: TNS:could not resolve the connect identifier specified
      Adding .world change nothing.


      Using direct connect string works fine:
      sqlplus access/access@"(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))(CONNECT_DATA = (SID = test)))"
      SQL*Plus: Release 10.2.0.4.0 - Production on Tue Aug 28 14:45:18 2012
      Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
      
      
      Connected to:
      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
      Where am I wrong and is it possible to make it work with TNS?

      Thanks in advance.