3

I need to develop in PL SQL using PL/SQL developer but I don't have idea how to make both oracle 11g and pl/sql developer work in the same machine because pl/sql developer need 32 bit oracle client installed when oracle did not require client when we work in local machine. This is what I did:

1) I installed oracle 11g 64 bit Enterprise on drive d:\ 2) Then I installed oracle client 32 bit on drive c:(pl/sql developer can work only with 32 bit version of oci.dll) 3) After I went to the client_home%->Network->Admin folder and corrected tnsname.ora file and wrote there my connection setting which was copied from my oracle 11 g home_dir..tnsnames.ora

    MyOracle =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        (CONNECT_DATA =
          (SERVICE_NAME = orcl)
)

After I installed PL/SQL developer in c:\plsqldev\ and rebooted my machine.

So now I can connect to my database with sql*plus located in my oracle database directory , but when I try to connect to my oracle with my client sql*plus it says tns protocol error when I try to connect using pl/sql developer it says could not resolve the connection identifier specified. What should I do? Could you help me please

Added: I erased TNSNAMES parameter from NAMES.DIRECTORY_PATH (in both - server and client sqlnet.ora files) and now when i login it says: ora-12154 tns could not resolve the connect identifier specified , but it still sucessfully connecting to DB when i try to connect with SERVER - s sql*plus.

Here is my config files:

**servers listener.ora **

        SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = CLRExtProc)
          (ORACLE_HOME = D:\app\Light\product\11.2.0\dbhome_1)
          (PROGRAM = extproc)
          (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Light\product\11.2.0\dbhome_1\bin\oraclr11.dll")
        )
      )

    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        )
      )
ADR_BASE_LISTENER = D:\app\Light

servers sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NONE)

NAMES.DIRECTORY_PATH= (HOSTNAME)

servers tnsname.ora

 LISTENER_ORCL =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


    ORACLR_CONNECTION_DATA =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
        (CONNECT_DATA =
          (SID = CLRExtProc)
          (PRESENTATION = RO)
        )
      )

    ORCL =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = orcl)
        )
      )

Clients sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NONE)

NAMES.DIRECTORY_PATH= (HOSTNAME)

Clients tnsnames.ora

    ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

ADD: Yess!)) Finally i found the solution: :) We need to use server's config file instead of clients one - but the oci.dll(32bit) must be taken from clients directory. I will describe the basic steps - how i made pl/sql developer 10.0.2 work with locally installed Oracle 11.2 x64:

1) Fist we need to install Oracle (I installed it to d:\app)

2)Then wee need to download and install oracle instant client (i founded that there are not any config files (not even network->admin directory) in the instant client , just needed dll's like oci.dll and etc.. note: the first time i've choosen "administrator" one which have it's own config file inside, seems this kind of client is needed only for remote access to Oracle). I installed(unpacked) my instant client to c:\app\ ;

3) Open PL/SQL developer , go to tools->preferences->connection and set the "Oracle Home" filed to the home directory of your instant client (I puted c:\app), but usually it already exist in the drop down list and we just need to click the drop down list and choose OraClient11g_home1_32bit , by this way i set the oci library path(choosen from drop down list). Click apply and exit from pl/sql developer;

4) Now we need to create two environment variables(the fist one will show the path to folder with connection configs , the second the language(if i'm not right please correct me)) ->

  • TNS_ADMIN = %SERVER_HOME%\NETWORK\ADMIN\ (i puted D:\app\Light\product\11.2.0\dbhome_1\NETWORK\ADMIN)

  • TNS_LANG = AMERICAN_CIS.CL8MSWIN1251

5) Reboot (optionally) and then start pl/sql developer - there must be three connection alias , i choosed orcl , entered my login ,password and hiited OK and everything worked )));

Thank's to everyone who answered in this topic, hope it'll help someone

Tnsman
  • 31
  • 1
  • 1
  • 3
  • [Just install](http://forums.allroundautomations.com/ubb/ubbthreads.php?ubb=showflat&Number=43291#Post43291) [Oracle 32bit client](http://www.oracle.com/technetwork/topics/winsoft-085727.html) on same machine and point PL/SQL Developer to it's %ORACLE_HOME%. – ThinkJet Sep 11 '13 at 10:22
  • Thank you , ThinkJet but i already installed 32 bit client in my machine and pointed pl/sql developer the home path of my client (tools->preferences->connection ->Oracle Home=c:\Oracle_client_home and also set OCI library=C:\app\product\11.2.0\client_1\bin\oci.dll). I dont have idea what to do :( – Tnsman Sep 11 '13 at 11:35
  • TNS could not resolve the connection identifier specified - thats the error i take while trying to connect to my DB – Tnsman Sep 11 '13 at 11:37
  • Try to change `sqlnet.ora` file located in the same directory as `tnsnames.ora` : set `SQLNET.AUTHENTICATION_SERVICES = (NONE)` and `NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)` – ThinkJet Sep 11 '13 at 11:41
  • I did what you said . Now when I log on it stays for 10 seconds and then alerts: "Connection timeout ocured" and then the log on window closes. – Tnsman Sep 11 '13 at 12:06
  • When i connect with client's sql* plus it says TNS: Protocol adapter error. But when I connect with servers SQL* plus it connects normally – Tnsman Sep 11 '13 at 12:11
  • Ok. Last try: remove `TNSNAMES` from list: `NAMES.DIRECTORY_PATH= (HOSTNAME)` . If this suggestion not help then you need to provide more information about your config (add text of all configuration files discussed above plus `listener.ora` to question text). P.S. Please also review formatiing of your question text. – ThinkJet Sep 11 '13 at 13:11
  • If your client's tnsnames.ora is *exactly* as you posted it, it is erroneous - you *must not* have spaces in front of ORCL. – Frank Schmitt Sep 11 '13 at 14:44
  • I fixed it (space in front of ORCL) , but it still not working . – Tnsman Sep 11 '13 at 15:13
  • Checked my own configs and found only one significant difference: in all places `127.0.0.1` specified instead of `localhost`. Also review [related questions on StackOverflow](http://stackoverflow.com/search?q=ORA-12154). – ThinkJet Sep 11 '13 at 16:37
  • You should probably add the solution as an answer and accept it so it doesn't show as an open question. – Mateus Schneiders May 01 '19 at 03:12

1 Answers1

0

check the port name in tns file it sholud be 1521 and where XE is my local db name like this E = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Shantu)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) )

)

EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )

ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) )

veeru
  • 1