DBAToolZ

Lightweight Oracle DBA Tools

 

Installing Oracle instantclient basic and instantclient sqlplus on win32

Sometimes basic things like installing the latest Oracle instantclient on the PCs of all of your developers can take considerable time. I typically setup a dedicated DBA Portal website wherever I work and then write up instructions for repetitive things like these. It’s all about — do it once and forget it. Here’s the copy of the writeup I did to install the latest 11g Oracle instantclient basic and instantclient sqlplus on win32:

Go to Instant Client Downloads for Microsoft Windows (32-bit) download page:

http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html

Download basic-win32 and sqlplus-win32 files to your PC:

instantclient-basic-win32-11.1.0.7.0.zip
instantclient-sqlplus-win32-11.1.0.7.0.zip

Create a C:\oracle directory on your C drive (if you don’t already have one)

move both files into C:\oracle

Unzip both files

1) first unzip instantclient-basic-win32-11.1.0.7.0.zip
2) then unzip instantclient-sqlplus-win32-11.1.0.7.0.zip

to unzip right click on file
point to WinZip
Click Extract to here …

End result should be a new folder called instantclient_11_1 in your C:\oracle:

Setup your system %PATH% and %TNS_ADMIN% variables

In Windows explorer LEFT pane, right click on “My Computer” icon and click “Properties”
Click “Advanced” tab
Click Environment variables button:

In the “System Variables” panel select “Path” variable and click “Edit” button:

In the “Variable Value” field paste the following:
C:\oracle\instantclient_11_1
BEFORE all other path values that point to your OLD Oracle clients, then click “Ok”

Now find a variable called TNS_ADMIN — it could be in either panel (System / User),
if you have no such variable, create it in the User panel by clicking “New”:
Variable Name: TNS_ADMIN
Variable Value: C:\oracle\instantclient_11_1
Click Ok

Now click “Ok” on the Environment Variables screen
and click “Ok” on the System Properties window to close it

Setup tnsnames.ora file

If you have an existing Oracle client copy tnsnames.ora file from there and place it to:

C:\oracle\instantclient_11_1

If you don’t have an existing Oracle client create a new tnsnames.ora file, for example:

- - - - - - - - - - - - - - - - - - - - - - - 
XRACQ_MYSERVICE_TAF =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vip-qarac1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vip-qarac2)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vip-qarac4)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vip-qarac3)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XRACQ_MYSERVICE_TAF.domain.com)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

Test 11g client by running sqlplus

Open a new CMD window by going to Start->Run
In the Run Window type in: cmd and press “Ok”:

in the CMD window prompt type in:

sqlplus user/pass@TNS_ALIAS

where:

user: is the database username given to you by the DBA
pass: is the database password given to you by the DBA
TNS_ALIAS: The alias you setup in the tnsnames.ora file

example:

   C:\>sqlplus xxx/xxxxxxxx@XRACQ_MYSERVICE_TAF
 
   SQL*Plus: Release 11.1.0.7.0 - Production on Wed Aug 26 16:24:23 2009
 
   Copyright (c) 1982, 2008, Oracle.  All rights reserved.
 
 
   Connected to:
   Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
   With the Partitioning, Real Application Clusters, OLAP, Data Mining
   and Real Application Testing options
 
   SQL>

If you get the ORA-12705 error while running sqlplus test:

C:\>sqlplus xxxxxx/xxxxxxxx@xxxxxxxxxxx
 
SQL*Plus: Release 11.1.0.7.0 - Production on Wed Aug 26 16:58:33 2009
 
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
 
ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified
 
Enter user-name:

Here’s the solution:

To fix the “ORA-12705: Cannot access NLS data files or invalid environment specified” error, go to your registry editor (run regedit from Start->Run) and then:

- Navigate to Windows Registry in \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
- Remove the NLS_LANG from there (select it and delete)

Re-run the sqlplus test to make sure it works (you will need to open a new CMD window for the registry changes to take place).

End.

No related posts.


171 Responses to “Installing Oracle instantclient basic and instantclient sqlplus on win32”

  1. 1
    Gary Sherman : Some notes on using Oracle 11 Database Instant Client:

    [...] the tnsnames.ora file, and quickly realized that there wasn’t one. A little googling led me to a helpful blog post at [...]

  2. 2
    Nicolas:

    Hi Vitaliy Mogilevskiy,

    your post is very usefull for for Oracle rookie like me :)
    I just followed your tutorial line by line and everything worked properly !

    Thanks a lot !

  3. 3
    MVE:

    @Nicolas
    I am glad it helped you out. Thanks a lot for your comment!

  4. 4
    Udit:

    Hi Vitaliy Mogilevskiy,

    I am really grateful to this post…..

    I don’t know anything about oracle…….but i need to setup this instant client on my pc because i need to practice the sql plus commands as a student……..

    I tried to create a tnsnames.ora file but when i try to run the sqlplus it gives an error:

    C:\Documents and Settings\Udit Parmar>cd c:\sq\cl

    C:\sq\cl>sqlplus scott/tiger@UDIT

    SQL*Plus: Release 11.1.0.7.0 – Production on Thu Oct 8 21:32:01 2009

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

    ERROR:
    ORA-12541: TNS:no listener

    Enter user-name:

    I created the following tnsnames.ora file:

    UDIT =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = UDIT)
    (FAILOVER_MODE =
    (TYPE = SELECT)
    (METHOD = BASIC)
    (RETRIES = 180)
    (DELAY = 5)
    )
    )
    )

    I tried scott and tiger as this is the username and password used at my school where they have installed a standard oracle client……..

    where should i obtain a username and password from, and why is it giving a no listener error?

    please help…

    Thank you very much……

  5. 5
    Udit:

    And even if i delete the tnsnames.ora file it gives the same “no listener” error…….

    I have set the environment variables properly……I don’t know what is wrong……sorry but i really don’t know anything about all these things…..

    Thank you……..

  6. 6
    MVE:

    Udit, in simple terms, the purpose of Oracle Client is to connect you to the Oracle database using SQL*Net. On the Oracle database node (server) there’s a special program running called Oracle Listener. The listener typically listens for incoming requests on PORT=1521, but a DBA can set it to anything.

    The error “ORA-12541: TNS:no listener” just tells you that there’s no Oracle listener running on server with IP address 192.168.1.2. Also, you don’t need to configure the load balancing / fail-over parameters if you are connecting to a single/non-RAC database node. Here’s a simpler TNS alias that you can use (example):

    MY_ALIAS =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = dbnode.mydomain.com)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = SERVICENAME.mydomain.com)
        )
      )

    To connect to remote database you need to know three pieces of information (get them from the DBA that is responsible for the remote database you are connecting to):

    1. db-host-name
    2. db-port
    3. db-SERVICENAME

    You then construct the following TNS alias and store it in your tnsnames.ora:

    MY_TNS_ALIAS =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = db-host-name)(PORT = db-port))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = db-SERVICENAME)
        )
      )

    Once you have above information do this:

    1. Open CMD and echo out TNS_ADMIN like this:

    Microsoft Windows XP [Version 5.1.2600]
    (C) Copyright 1985-2001 Microsoft Corp.
     
    C:\Documents and Settings\username>echo %TNS_ADMIN%
    C:\oracle\instantclient_11_1
     
    C:\Documents and Settings\username>cd C:\oracle\instantclient_11_1
     
    C:\oracle\instantclient_11_1>notepad tnsnames.ora

    2. Go to the directory returned by step# 1 and edit tnsnames.ora enter the TNS-alias you constructed (See above) and save the file.

    3. Get correct username/password from DBA and try to connect: sqlplus username/password@MY_TNS_ALIAS

    -HTH
    - Vitaliy

  7. 7
    Udit:

    Hi Vitaliy, I now know how to make a tnsnames.ora file but the problem is that….i want to use this instant client not to connect to any remote database or something…….and there is no one like Database Administrator here from whom i can get a username or password…..i am using this instant client locally to practice sql plus commands as a student……….

    then what should i write in the “db-hostname” and “db-SERVICENAME”? And what username and password should i use?

    Thank you very much to explain the tnsnames.ora file clearly……..

  8. 8
    MVE:

    Udit,

    If there’s no remote database that you can connect to and you want to practice Oracle then you will have to install Oracle RDBMS Server on your local machine/PC:

    go to: http://www.oracle.com/technology/software/products/database/oracle11g/111060_win32soft.html
    download: win32_11gR1_database_1013.zip

    If you can setup a Linux box, you really should, it would be much better because then you can get the latest Oracle 11gR2 for Linux:
    go to: http://www.oracle.com/technology/software/products/database/oracle11g/112010_linuxsoft.html
    download both: linux_11gR2_database_1of2.zip and linux_11gR2_database_2of2.zip

    PS: If you install Oracle on your local PC then you don’t really need instant client because you can use sqlplus which will be installed with Oracle server software

    PSS: I strongly suggest you go Linux route if you want to learn Oracle. You can download Oracle Enterprise Linux free from here: http://edelivery.oracle.com/linux

    -HTH
    - Vitaliy

  9. 9
    Udit:

    so instant client won’t work for my purpose?

    Anyways…..thanks for your help all this long……i will now download the oracle standard client……

    Thank you very much…….

  10. 10
    MVE:

    No problem Udit, but you don’t need “oracle standard client” you need an Oracle Server :) Clients are just clients — they make connections to servers. If you have no Server to connect to, there’s no need for a client. If you want to practice Oracle but have no Oracle Server to connect to you have to install Oracle Server.

    -HTH
    - Vitaliy

  11. 11
    Udit:

    oh got it…….i realised my mistake :) …….

    I was trying to connect to nothing with my instant client…….so i will have to install an oracle server on my pc so that i can connect my client to it…….oh how stupid i was being……..sorry to bother you all this time……

    Thank you very much…….

  12. 12
    Nancy Yuan:

    I am a db beginner. I want to practice sqlplus on my computer(Vista). I thought I can install sqlplus without Oracle on my computer. I downloaded sqlplus the basic and instant client packages. Then I unzip them to c:\instantclient and run sqlplus, then tried to login sqlplus but failed for ORA-12560: TNS:protocol adapter error. I searched help from google and created tnsnames.ora following your advise but was not able to catch every detail. Here is the sqlplus zip files and the unzip directory on my computer. I have cygwin on my computer, here is the log I entered from my xterm prompt:

    Nancy@Nancy-PC /cygdrive/c/Users/Nancy/Documents/Downloads
    $ ls instant*.zip
    instantclient-basic-win-x86-64-11.1.0.7.0.zip
    instantclient-sqlplus-win-x86-64-11.1.0.7.0.zip

    Nancy@Nancy-PC /cygdrive/c/instantclient/instantclient_11_1
    $ ls
    BASIC_README mfc71.dll ojdbc5.jar orasql11.dll
    Orasqlplusic11.dll msvcr71.dll ojdbc6.jar orasql11.sym
    SQLPLUS_README oci.dll orannzsbb11.dll sqlplus.exe
    adrci.exe oci.sym orannzsbb11.sym sqlplus.sym
    adrci.sym ocijdbc11.dll oraocci11.dll tnsnames.ora
    genezi.exe ocijdbc11.sym oraocci11.sym vc71
    genezi.sym ociw32.dll oraociei11.dll vc8
    glogin.sql ociw32.sym oraociei11.sym

    Nancy@Nancy-PC /cygdrive/c/instantclient/instantclient_11_1
    $ more tnsnames.ora
    XRACQ_MYSERVICE_TAF =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Nancy-PC)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = XRACQ_MYSERVICE_TAF)
    (FAILOVER_MODE =
    (TYPE = SELECT)
    (METHOD = BASIC)
    (RETRIES = 180)
    (DELAY = 5)
    )
    )
    )

    $ sqlplus SYSTEM/nancy@XRACQ_MYSERVICE_TAF
    SQL*Plus: Release 11.1.0.7.0 – Production on Mon Oct 12 11:36:29 2009
    Copyright (c) 1982, 2008, Oracle. All rights reserved.
    ERROR:
    ORA-12541: TNS:no listener

    Enter user-name: SYSTEM
    Enter password: nancy
    ERROR:
    ORA-12560: TNS:protocol adapter error

    Enter user-name:
    ERROR:
    ORA-12560: TNS:protocol adapter error

    SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
    Nancy@Nancy-PC /cygdrive/c/instantclient/instantclient_11_1
    $

    Then I installed Oracle 11.1 and set variables ORACLE_SID, ORACLE_HOME and TNS_ADMIN but I don’t know how to login to Oracle either. I installed Oracle several times. When I first installed Oracle 10.2.0, it asked my passwords for SYSTEM and the other 2 (I could not remember), but then when I installed 11.1, it did ask for me to set up passwords again. I used my password(nancy) to login to sqlplus but it did not work.

    Nancy@Nancy-PC /cygdrive/c/instantclient/instantclient_11_1
    $ echo $TNS_ADMIN
    C:\instantclient\instantclient_11_1
    Nancy@Nancy-PC /cygdrive/c/instantclient/instantclient_11_1
    $ echo $ORACLE_SID
    orcl
    Nancy@Nancy-PC /cygdrive/c/instantclient/instantclient_11_1
    $ echo $ORACLE_HOME
    /cygdrive/c/product/11.1.0/bin

    Thanks
    Nancy Yuan

  13. 13
    MVE:

    Nancy,

    FYI: you don’t need Oracle Instant Client if you are installing Oracle Server software because full Oracle Client and sqlplus are part of the Oracle Server distro. It doesn’t hurt installing instant client but it’s not necessary. Also you should change your PATH variable to include the server’s ORACLE_HOME directory before instant client’s.

    If you got Oracle Server installed and have selected to have your database created during installation then you should be able to login to ORACLE using sqlplus even if you don’t know the password (make sure your ORACLE_SID is setup, then:

    sqlplus /nolog
    connect / as sysdba

    That should get you in. You can then change your password using the following command

    alter user system identified by my_new_password;

    PS: In 11g passwords are case sensitive by default.

    HTH,
    - Vitaliy

  14. 14
    Nancy Yuan:

    Thank you for quick response. I will re-do installation with Oracle Server software. Is this the correct vesion 11.1.0 package?
    win64_11gR1_database_111070.zip

    Thanks
    Nancy Yuan

  15. 15
    Nancy Yuan:

    Actually, I just set environment ORACLE_HOME and now able to login to sqlplus with command “sqlplus /nolog”.

    Thanks
    Nancy Yuan

  16. 16
    MVE:

    Nancy,

    That’s for Win 2008 Server 64bit … Don’t’ you have Vista? Is it 32 or 64 bit?

    The 32bit version win32_11gR1_database_1013.zip would be in here — http://www.oracle.com/technology/software/products/database/oracle11g/111060_win32soft.html

    And as I mentioned to Udit, if you want to learn Oracle take the effort and install Linux workstation, then install the latest 11gR2 for Linux.

    Why bother with 11gR1 if there’s 11gR2 out? Besides, Oracle on Linux is what you’d be mostly running these days in a real Data Center so why bother with Windows? DBA/admin stuff is very different (scripting etc..).

    Make you time count and get 64bit Linux installed (not that hard and it’s free from http://edelivery.oracle.com/ ) then get Oracle 11gR2 — http://www.oracle.com/technology/software/products/database/oracle11g/112010_linx8664soft.html!

    HTH,
    - Vitaliy

  17. 17
    Nancy Yuan:

    I have cygwin and maintaining some perl scripts for file management work. If I install Linux, will that break my cygwin stuff? If not, I do hope to install Linux. Also I don’t know if mine Vista 32 bit or 64 bit, how I can tell? I just randomly chose 11R1.

    I was able to login to sqlplus but got error to connect and I can’t change password either:
    $ sqlplus /nolog

    SQL*Plus: Release 11.1.0.7.0 – Production on Mon Oct 12 15:16:24 2009

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

    SQL> desc dual;
    SP2-0640: Not connected
    SP2-0641: “DESCRIBE” requires connection to server
    SQL> alter user system identified by ‘nancy’;
    SP2-0640: Not connected
    SQL>

    What is missing?

    My major goal is to practice as a developer but not DBA yet.

    Thanks
    Nancy Yuan

  18. 18
    MVE:

    you are missing “connect / as sysdba”

  19. 19
    Nancy Yuan:

    SQL> connect / as sysdba
    ERROR:
    ORA-12560: TNS:protocol adapter error

    SQL>

  20. 20
    MVE:

    Check your environment:

    1. PATH — should include RDBMS ORACLE_HOME before instant client
    2. ORACLE_SID — should point to the SID you created during RDBMS install
    3. ORACLE_HOME — should point to RDBMS
    4. make sure to unset TWO_TASK (if set)
    5. TNS_ADMIN make sure it points to ORACLE_HOME/network/admin (not instant client)

    These are just few that I can remember of (I don’t work on WINDOWS much).

    HTH,
    - Vitaliy

  21. 21
    Nancy Yuan:

    I thought I don’t need to install Oracle but just download sqlplus. It is too much trouble to debug my system setup. I think I will nstall Oracle Server software instead. Linux is another option.

    Thank you very much for helping me.

    Nancy

  22. 22
    erbuncho:

    This post is excellent!!! Congratulations!

    Worked perfectly for me

    Thanks a lot!!

  23. 23
    Nancy Yuan:

    Vitaliy,

    I installed 11gR1 Oracle Server software on my Vista. I still can’t login to sqlplus as SYSTEM directly but I followed your advise login ‘sqlplus /nolog’ and then ‘connect / as sysdba’ at the sql prompt. I finally got connected.

    $ sqlplus /nolog

    SQL*Plus: Release 11.1.0.7.0 – Production on Sat Oct 17 12:45:40 2009

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

    SQL> connect / as sysdba
    Connected.
    SQL> desc dual;
    Name Null? Type
    —————————————– ——– —————————-
    DUMMY VARCHAR2(1)

    SQL>

    Thank you very much again for helping me the issue.

    Nancy

  24. 24
    MVE:

    Nancy,

    Once you are connect “as sysdba” you can do anything. You can for instance change SYSTEM password using the following command:

    alter user system identified by newpassword;
    connect system/newpassword

    If you want to learn Oracle get in the habit of using a non-privileged schema (do not use system). Just create one yourself using the following command (first login to Oracle using sqlplus as sysdba), next issue these commands:

    -- first create a tablespace where all your 
    -- testing/development data will be stored
    --
    create tablespace dev_data
    datafile size 200m autoextend on 
    next 1280k maxsize unlimited
    extent management local
    segment space management auto;
     
    -- next create development schema
    -- and make the above tablespace it's default
    --
    create user dev identified by devpass
    default tablespace dev_data
    temporary tablespace temp;
     
    -- grant necessary privileges to dev schema
    --
    grant connect, resource to dev;
    revoke unlimited tablespace from dev;
    alter user dev quota unlimited on dev_data;
     
     
    -- finally connect to your newly created schema
    -- and start learning oracle
    --
    connect dev/devpass

    HTH,
    - Vitaliy

  25. 25
    Simon Filiatrault:

    I am working in a client-server setup. If I have 11gR2 a linux server, can I use 11gR1 clients on windows to access it?

    My server need to be 11gR2 and my clients are Windows

    Thanks,

    Simon

  26. 26
    MVE:

    Simon,

    Yes, [WIN]11gR1 client –> [LINUX]11gR2 server should be just fine.

    HTH,
    - Vitaliy

  27. 27
    Simon Filiatrault:

    Vitaly,

    Thanks for the very fast response, I will test exp,imp from windows with client 11gr1 and report back.

    Simon

  28. 28
    MVE:

    Simon,

    You might have a problem with exp/imp. These two utilities are very sensitive to database versions, even minor version changes sometimes break these. In 10g and up it’s best to use expdp / impdb as it’s run by the server process instead of client.

    HTH,
    - Vitaliy

  29. 29
    Simon Filiatrault:

    Ok thanks, here’s more detail on my problem. We need to test our apps on 11g. We need to be compliant by 2010 on 11g. Looking at the oracle site, I saw 11gR2 is out but only on linux. So I decided to go for it and install it on linux. but now, there is no 11gR2 client on windows and our apps run on windows and are doing OLE, ODBC, EXP and IMP work. So do I still go ahead with my plan and start testing with 11gR1 client on windows connected to 11gR2 server?

    If I test on 11gR1 server and client, will I need to test everything again on R2?

    Hum… life simple pleasures

  30. 30
    MVE:

    I would not install 11gR1 server today. I’d give it a shoot with 11gR1 client –> 11gR2 server first, and if that fails hold out until 11gR2 client becomes available for Win platform.

  31. 31
    Simon Filiatrault:

    My thoughts also… Thanks,

  32. 32
    Bikash:

    Hi Vitaliy Mogilevskiy,
    i am new in oracle i try to connect a remote oracle database followed by your tutorial.but when i run sqlplus in cmd the following error was given
    C:\Documents and Settings\USER>sqlplus ****/***@TNS_ALIAS
    Error 6 initializing SQL*Plus
    Message file sp1.msb not found
    SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
    when i try with vs2008 through oracle client the following error was given :
    ora-12170 TNS:Connect Timeout occurred.

    pls help me

    thaks in advance.

  33. 33
    Bikash:

    Hi, Vitaliy Mogilevskiy

    The following error was given when i try to connect through Microsoft odbc datasource in visual studio 2008.

    ERROR [NA000] [Microsoft][ODBC driver for Oracle][Oracle]ORA-12504: TNS:listener was not given the SERVICE NMAME in CONNECT.DATA
    ERROR [IM006] [Microsoft][ODBC Driver Manager] Drivers SQLSetConnectAttr failed
    ERROR [01000] [Microsoft [ODBC] Driver Manager] The driver doesn’t support the version of ODBC
    behavior that the application requested (see SOLSetEnvAttr).

    suggest me.

  34. 34
    Simon Filiatrault:

    Vitaliy, Hi again,

    I managed to install 11gR2 on Oracle linux R5. Everything works fine. I installed a 11gr1 windows client on XP and Exp,Imp works fine from 11gr1(client) => 11gr2(server).

    I am now trying to autostart the instance automatically on the linux box. I can probably do a script to start the listener and DB, but was wondering if there is an oracle script to do this job. On windows hosts, this is done by services automatically.

    Thanks,

  35. 35
    MVE:

    Simon,

    There are two scripts provided by Oracle called dbstart/dbshut. On Linux these scripts read /etc/oratab file where you specify which SID:ORACLE_HOME should be automatically started or shutdown.

    You can incorporate dbstart/dbshut into a script which you’ll place into /etc/init.d/ (RedHat/OEL Linux). Typically you’ll call it /etc/init.d/dbora … This script should be able to process stop|start|restart parameters.

    Once that’s done you register the script with Linux services using chkconfig –add dbora, that will create /etc/rc*.d/S99dbora and /etc/rc*.d/K10dbora files which will be called at appropriate run levels, effectively stopping and starting the database for you.

    For more details please refer to the following Metalink Document:

    How to Configure a Linux x86 Box for Oracle DB Auto Start / Shutdown [ID 281912.1]

    HTH,
    - Vitaliy

  36. 36
    MVE:

    Bikash,

    The error “Error 6 initializing SQL*Plus Message file sp1.msb not found SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory” probably indicates that you have an erroneous ORACLE_HOME variable set on your PC. You can check using the following command (from cmd):

    echo %ORACLE_HOME%

    If the above command returns a directory to your “old” ORACLE Software you should remove this variable from your system, then restart cmd and try sqlplus once again.

    PS: your ODBC issues with ORA-12504 error is most likely caused by an incorrect TNS alias specification, make sure to get correct TNS alias from your DBA.

    HTH,
    - Vitaliy

  37. 37
    Bikash:

    Thank you Vitaliy Mogilevskiy for your quick response.
    i sloved the problem by change the home directory.
    your post is excelent. it works fine.
    again thanks a lot…

  38. 38
    Peter Gurneau:

    I get this error after following the steps above:

    SQL*Plus: Release 11.1.0.7.0 – Production on Wed Jan 6 09:38:05 2010

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

    ERROR:
    ORA-12545: Connect failed because target host or object does not exist

    Not sure what to do next, please help

  39. 39
    Peter Gurneau:

    nevermind, I checked my tnsnames.ora, and I had the wrong host name listed, now it works!

  40. 40
    Rob:

    Thanks for the great post. I had previously installed 11GR2 server on Linux 64, and needed to get windows client working. I followed your instructions, and it worked like a champ. Only issue I have now is that I am able to login with sqlplus as long as I am in the C:\oracle\instantclient_11_1. This is where I put my tnsnames.ora file. However if I start sqlplus while in another directory, it times out and reprompts me to connect. It seems not to find tnsnames.ora. Is there an environment variable I am missing? Thanks again for a great post – very helpful.

  41. 41
    MVE:

    @Rob

    Is your TNS_ADMIN set?
    You can check it using echo %TNS_ADMIN% from command prompt (cmd)

    -HTH

  42. 42
    Rob:

    Yes, I just tripple checked it, and it is set.

  43. 43
    Rob:

    Sorry, my TNS_ADMIN had a space in front of the “C:\…”. I removed it, and all is working just as you described. Thanks again for your post – it was a big help.

  44. 44
    MVE:

    @Rob

    No problem — I am glad you sorted it out!

  45. 45
    Rob:

    Thanks again. I’ve marked your site on my short-list of Oracle favorites.

    Now that I have SQLPLus working as a baseline client interface, I am looking for a more prodctive PL/SQL development environment.

    I was able to install SQL Developer. which is working pretty well, and I’m also looking at Quest’s Toad For Oracle. Toad looks to be a pretty productive and popular IDE for PL/SQL.

  46. 46
    MVE:

    Toad is great and if the company you work for pays for it — it’s even better! But for the price (free) — no one can beat SQL Developer :)

  47. 47
    Simon Filiatrault:

    11g related question…

    Since I installed my 11gR2 server on linux, I figured I could use this server (11g) to backup (exp) schema of my other servers (10g and 9i). But I quickly found that doing an EXP (11g client) of a 10g schema does not work.

    I get and ORA-00008 followed by ORA-00904. According to many on the web, this is normal when the EXP version does not match the server version.

    I assumed and hoped that would work, since the EXP 11g client is of higher version. I would like to be able to centrally do all my EXP from one server.

    Any way around this or suggestion on how to EXP schemas from 9i, 10g, 11g on one “backup” server?

    Thanks,

    Simon

  48. 48
    MVE:

    @Simon

    - 11g’s EXP tool is not designed to export data from older versions of oracle — it’s whole purpose in 11g is for backward compatibility where you might have to bring a small subset of data from <=9i versions to 11g.

    - For backup/recovery procedure you should be using RMAN instead — that’s what it’s for — backup/recovery. Not only that EXP is not intended as a backup it’s also a deprecated tool — replaced by data-pump.

    Learn RMAN if you value your data.

  49. 49
    Simon Filiatrault:

    I only used Rman once… Can it be used from my 11g server to “backup” schemas from another server (9i, 10g)?

  50. 50
    MVE:

    @Simon

    RMAN is meant to be used as a server side tool because in a real life-cycle of a database server (not in a lab) the software stacks will have many variations (patch-levels) from one node to the other. You should be using the RMAN utility right out of the same ORACLE_HOME that the ORACLE server is running out of.

    What you want to centralize is your RMAN scripts and RMAN backup-set destination — not the actual RMAN utility. Good way of doing that is using NAS storage — put your RMAN scripts and RMAN backup sets on the NAS but run the actual backups from each individual ORACLE server node so you are using RMAN utility from the local ORACLE_HOME.

  51. 51
    MVE:

    @Simon

    Also, RMAN backs up database as a whole not schemas. Using RMAN you are backing up either:

    a) DATABASE as a whole
    b) TABLESPACE
    c) DATAFILE

    Never a schema (not directly).

  52. 52
    Simon Filiatrault:

    The environment I have is:

    1x 9i on Win2003
    2x 10g on Win2003
    2x 11g on linux.

    Users are working in users/schemas (by design for our apps). Thus when I need a “restore” I am stuck with EXP,IMP.

    That is why I was looking for a central way of EXPorting all my important schemas from one central location.

    I guess I will have to create windows/linux scripts on all my server separately.

    Thanks for the info on RMAN, I will study it more.

  53. 53
    MVE:

    @Simon
    - Place each schema into a separate TABLESPACE then backup the whole database.
    - Have a recovery instance setup for each of your databases — you need to have that anyway to test your recovery procedure.

    If you ever have to recover a schema because someone dropped a table/pkg etc.. recover the database on the recovery instance and get what you need from there. Don’t rely on EXP/IMP to protect your data.

    For 10g and above look into flashback features.

    This should take care of your schema based recoveries 100%.

  54. 54
    Simon Filiatrault:

    Thanks for the info, but the tablespace name is hard-coded in the application. So your plan could not apply to my situation. Each schema is an entity on its own.

  55. 55
    Chris T:

    Just another thank you for creating this page. After a lot of searching, I finally got the help I needed, thanks to you!

    Chris

  56. 56
    jaywink:

    Thank you, this helped a lot, I was stuck on the NLS error :) Of course Oracle’s own pages offered no help… :P

  57. 57
    Yadiel:

    Hi Simon, i am having a problem, i use pl sql developer as my client to conect to a database server. And i want to configure pl sql to use tnsnames.ora. How can i do this???

    Because i already do what you were suggesting, creating also the enviroment variable, but my client doesnt read that file…

  58. 58
    MVE:

    @Yadiel

    What’s “pl sql developer”? Are you referring to Oracle SQL Developer [ http://www.oracle.com/technology/products/database/sql_developer/index.html ]? Also who’s Simon? :)

  59. 59
    GregC:

    Thanks.

  60. 60
    Craig:

    Easy to understand -as an Oracle newbie it was very usefull.Thx
    I followed your tutorial and I’m connected..the Q&A from users also insightful.
    Keep up the good work.

  61. 61
    gurpreet:

    thanks…..

  62. 62
    Rick:

    Thank you SO MUCH for posting this! That saved me a ton of time!

  63. 63
    Rahul:

    Thanks Vitaliy,

    It indeed worked out well.

    Rahul

  64. 64
    Ruffneck:

    Good day, MVE.
    Want to get my biggest thanx. You are godlike one. I can install and start SQL*Plus (v.11) from your screenshots.

  65. 65
    Anu:

    Hi Vitaliy,
    I am trying to access database remotely. I have 10g db running on laptop(vista-64bit) and installed a 11g instant client in another one (windows7 64bit). i even copied tnsnames.ora and set the variable too. but still am facing with error “ORA-12154: TNS:could not resolve the connect identifier specified”.
    Please can you tell me where am i going wrong.
    Thak You,
    Anu.

  66. 66
    Anu:

    Hi Vitaliy,
    Adding to my previous comment. using the instant client, am able to work on isqlplus, but not able to work on sqlplus, still facing “ORA-12154 :tns:could not resolve the connect identifier specified”.

    Thankyou,
    Anu

  67. 67
    MVE:

    @Anu

    You probably have more than one ORACLE clients on your PC — make sure 11g client libraries are FIRST in the PATH and TNS_ADMIN.

    Open command prompt and type in the following:

    echo %TNS_ADMIN%

    What does it say? Go to that directory and edit tnsnames.ora file – make sure your TNS alias is properly defined in there.

    HTH,
    - Vitaliy

  68. 68
    Anu:

    Hi Vitaliy,

    sqlplus on instantclient is working now. But as you said, i checked all the paths and tnsnames.ora,everything was fine..only problem i found was tnsnames.ora file was by mistake saved with hidden .txt extension.I changed it and sqlplus started working. but what i don’t understand is how did my web-based sqlplus(isqlplus ) was working before with that file. If am not wrong will islplus doesn’t depend on same parameters as sqlplus to work from client pc? what exactly is the difference between sqlplus and isqlplus ?

    Thank you very much..your suggestion made me look again into those files.

    Anu.

  69. 69
    MVE:

    @Anu

    Great — I am glad you found the problem and solved it.

    RE isqlplus:

    I don’t use isqlplus, but here’s what I know — it didn’t come with my installation of 11g instant client so it’s entirely possible you were using it from another oracle_home. And I believe isqlplus doesn’t depend on the “thick Oracle client” so it doesn’t have to read tnsnames.ora file to work.

    - Vitaliy

  70. 70
    Anu:

    Hi Vitaliy,

    Thanks for your response.Based on your comment and reading isqlplus&sqlplus topic in 10g adminstration book i guess am bit clear about how sqlplus n isqlplus work.

    Thanks,
    Anu.

  71. 71
    dinu:

    i installed oracle insta client i added tnsnames.ora file
    but i cannot find sqlnet.ora file

  72. 72
    dinu:

    i am getting this error
    error “ORA-12154: TNS:could not resolve the connect identifier specified”.
    i set Tns_admin variable and added tnsnames.ora

  73. 73
    Texrat:

    Very helpful tutorial, thanks. But like others I get the 12154 error even though everythign is correct, including TNS_ADMIN and TSNAMES.ora (latter copied from co-worker with working client). Mystifying…

  74. 74
    MVE:

    @Texrat

    It’s called tnsnames.ora not “TSNAMES.ora”. Make sure the alias you are using with sqlplus is defined in your %TNS_ADMIN%\tnsnames.ora

    Also, make sure the file is in fact named “tnsnames.ora” not “tnsnames.ora.txt”

    see:
    folder options for oracle client PC

  75. 75
    troy:

    I have a quick oracle question for you. I just installed oracle 11g on my desktop and would like to get access to it remotely from the outside. The problem that I am having is that apparently port 1521 is always closed from the outside. From the inside I can do everything fine but the minute I try to get access to it from the outside it says that it cannot find a tns listener.

    I have turned off my windows xp firewall and have also eliminated my router by connecting my machine directly to the DSL line. The listener is up and running when I use netstat; however, it seems to be blocked at the oracle application and not the operating system level. I would appreciate any insights into this.

  76. 76
    MVE:

    @troy
    If you can connect to your 1521 port from local network (from another machine) but cannot from external network then it’s blocked at the network layer.

    There’s only one place in ORACLE that I know of where you can block/allow connections to Oracle Listener and if it were enabled you would be getting the following errors:

    TNS-12546: TNS:permission denied
    TNS-12560: TNS:protocol adapter error
    TNS-00516: Permission denied

    this security layer would be enabled by sqlnet.ora:

    $ORACLE_HOME/network/admin/sqlnet.ora
    tcp.validnode_checking = yes
    tcp.invited_nodes = (x.x.x.x | name, x.x.x.x | name)
    tcp.excluded_nodes= ( x.x.x.x | name, x.x.x.x | name)

    You don’t get these errors so I would suggest you to look at your ISP/DSL router to see if there’s any built-in firewall in there.

    PS: I hope you understand that opening your database port to the Internet is not a good idea?

    HTH,
    - Vitaliy

  77. 77
    chris:

    My sqlplus is not working with my new install of 10g…but only work when I run as administrator. Any chance you can help me with this?

    thanks
    Chris

  78. 78
    Andrei:

    Hi Vitaliy,

    I followed your tutorial above and everything went well up to the point of connecting to the remote database.
    If I don’t use the “sqlplus user/pass@service_name” I get the “TNS-12560: TNS:protocol adapter error”.
    When I use “sqlplus user/pass@service_name” I get the “ORA-12170: TNS:Connect timeout occurred”.
    Now I just gor the “ORA-12154: TNS:could not resolve the connect identifier specified”… using the same credentials as above.

    I do not have the sqlnet.ora defined. Do I need it? I only have the tnsnames.ora. On a side note, I am able to connect to the database using the SQL Developer tool on the same machine.

    Please advise.

    Thanks in advance,

    Andrei

  79. 79
    MVE:

    @Andrei

    What’s in your tnsnames.ora? Did you define “service_name” in there?

    - Vitaliy

  80. 80
    MVE:

    @Chris

    Make sure you’ve defined the TNS_ADMIN and PATH variables for the new user.

    - Vitaliy

  81. 81
    Andrei:

    Hi Vitaliy,

    I got it to work on my Mac using “sqlplus username/passwd@hostname/servicename” but now my Windows machine keeps timing out.

    My tnsnames.ora (on both Windows and Mac) is as follows:
    ————————————————————————-
    ORACLEAC =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = database.domain.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = xx01)
    )
    )
    ————————————————————————–

    thanks in advance,

    andrei

  82. 82
    Andrei:

    … forgot to mention:

    the Mac is my personal computer that i use at home, and the windows machine is the one i use at work. i would blame it on the firewall but then why is the SQL Developer able to connect??

    Just a thought…

    many thanks,

    andrei

  83. 83
    MVE:

    @Andrei

    Can you ping database.domain.com?

    - Vitaliy

  84. 84
    Andrei:

    Vitaliy, i get a “request timed out”.

    andrei

  85. 85
    Andrei:

    Hi Vitaliy, i got it to work when I turned on the VPN. But strange enough, when I’m at home it works without a VPN as well. By the way, I can also ping it when the VPN is on. What do you think?

    Many thanks for your tutorial above,

    andrei

  86. 86
    MVE:

    @Andrei

    Oracle SQL*Net works on top of TCP/IP layer, so if you cannot ping a host you will not be able to connect to it using SQL*Net either. I don’t exactly understand what you are trying to do but in order to connect to your ORACLE instance from a different machine you must be able to ping the database server and have access to listener port.

    HTH,
    - Vitaliy

  87. 87
    Wilma:

    Thanks for putting this down, really appreciated.

  88. 88
    YS:

    Hello Vitaliy,

    This post has been of great help. I just followed your steps and installed Instant client.

    I was given the following information: server, port and db_name. with this how should I configure ny tnsnames.ora file? Do I need something else? BTW, I am getting an error ora-12154: TNS:could not resolve the connect identifier specified.

    Thanks!

  89. 89
    Anu:

    Hi Vitaliy,

    I have oracle client 11.1.0.6 installed in my local pc.when i try to connect to remote server,
    sometimes connection fails with ORA-12545 connection failed as target or host doesn’t exist.

    Its not same always, it works sometimes if i keep trying to connect.
    My database am trying connect remotely is 11.1.0.7 and my client is 11.1.0.6.Will that be an issue?

    I checked all other parameters as per metalink, like local_listener, remote _listenere and the tns entires . everything looks fine.

    Please help in solving this issue.

    Thanks,
    Anu.

  90. 90
    Jeny:

    Hello,

    I have followed the steps which you have given above and i reached the point where I use the command ‘sqlplus’ in the cmd. But the problem is, that when I execute it I receive the following message: ‘sqlplus’ is not recognized as an internal and external command,operable programme, or batch file.
    I am using Windows 7, 64bits and from the oracle site I have downloaded the corresponding versions of the sqlDeveloper,Java and InstantClient BASIC & sqlplus. As your article is for 32 bit OS, and mine is 64, do you think that this might be the problem or not? In both cases, what do you suggest me to do?

    Thanks in advance I would really appreciate your help!

  91. 91
    Kim:

    Hi Vitaliy,
    I have a problem downloading installer client basic pack from oracle website, is there any free download link?. I installed Oracle 10g XE, n i’m using Windows 7 Ultimate 32 Bit.

    thanks in advance, i’d appreciate for your help.

  92. 92
    Richard Berthet:

    Followed your instructions to install 64 bit Instant Client on Windows 7 for Oracle 11.2.

    Worked first go.

    Thank you. Pity Oracle can’t manage to do the same!

  93. 93
    anony:

    hi vitaliy,

    iam connecting to Oracle server from sqlplus instant client

    nohup sqlplus -l UNAME/PWORD@IPADDRESS/orcl @/dev/ora.pipe

    When network goes down and up after sometime .The sqlprocess is still up but it is not writing to db.It shows Connect error.How to identify automatically and restore db connection.

    Help is appreciated.

  94. 94
    MVE:

    @anony
    I’d suggest to startup sqlplus without login and then issue these commands:

    sqlplus /nolog
    whenever oserror exit failure
    whenever sqlerror exit failure
    connect [YOUR_CONNECT_STRING]
    @/dev/ora.pipe
    

    This will ensure that when there’s an error sqlplus will exit and give you a status > 0; and you can then check for the exit code in the outer script:

    if [ $? -gt 0]; then
       [restart_sqlplus]
    fi
    

    One thing you have to watch-out for is TCP/IP Timeout which can be substantial issue that can “hang up” your sqlplus call for a while (depends on network configuration on the database server).

  95. 95
    Amir:

    Dear MVE

    Great page, not everyone is willing to share their knowledge these days!

    I have installed an oracle server in my laptop and configured name alias and listener.I’m trying to connect to it via another laptop in a wireless home network. I know I,m suppose to create a tnsnames.ora on the client machine. I downloaded the instant client, but their is no oracle product path on my non server laptop to set the path to. basically i want to connect to a server via a computer with no oracle products installed. i’m sure I,m missing something obviously stupid!!! I can get the tnsnames from the server and configure it, but cant install sqlplus!!
    please help!

    regards
    Amir

  96. 96
    Anja:

    Dear MVE,

    very helpful post. I was looking for 2 days for exactly this information. And there is lots of information out. Just nothing as clear, complete and understandable as your brief tutorial.
    Thank you very much.

    best regards, Anja

  97. 97
    Vera:

    Hi Vitaliy,

    Pls helm me!!!!
    I need to start my Database ASAP! My Base was worked but with 14.12.2010 it stoped :( .
    ORACLE 10g (Windows)
    When I try to start sqlplus on Server DB, I have error: SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory.
    I tried:
    set ORACLE_SID=
    set oracle_home=…

    May be you send to my email your number phone and I can call you.

    Best regards,
    Vera

  98. 98
    Darrell:

    Thanks! You have been a great, great help to me.
    Kind regards,
    Darrell

  99. 99
    Kurt Look:

    I’d just like to thank you as well. I installed the 11.2.0.1 version and your instructions still worked perfectly. The quality and completeness of your work is excellent.

  100. 100
    Michael:

    Thanks!! Like Kurt, I just used this procedure for 11.2.0.1.0 and it worked perfect! And also worked for my TNS connection using Navicat for Oracle. :-) Very easy instructions to follow.

  101. 101
    nassim:

    bonjour, j’ai un petit problem lors de la connection a ma base de données sur oracle 9i en utilisant php 5.3.0 j recois l’erreur ORA-12560 . merci d’avance

  102. 102
    Jon:

    Thank you Vitaliy. I just used this to download the Windows x86 install for 11gR2 on Windows7 and it was very useful. I just substituted 11_1 for 11_2.
    Up and running in minutes. Thanks again.

  103. 103
    Ali:

    Thanks very much

  104. 104
    William:

    Vitaliy – I installed instantclient using your instructions and it works fine – if I execute: sqlplus ewbs1bq/welcome1@DEVA and get ORA-12154 – yet, if I logon with the following: sqlplus ewbs1bq/welcome1@nrwddb01.mckesson.com:1521/DEVA it works – the tnsnames.ora file is:
    DEVA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = nrwddb01.mckesson.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = DEVA)
    )
    )

    Doesn’t make sense??????

  105. 105
    MVE:

    @William please double check your TNS_ADMIN variable to ensure it points to the location where tnsnames.ora file is stored.

  106. 106
    Vlad:

    Thanks for info, very useful !

  107. 107
    Svein:

    Hi and thanks for a nice article that helped me a lot.
    I ran into one problem using your approach tho, and that was that you say to “add a User variable” for TNS_ADMIN.
    This does not work if you say have a web site running on the NETWORK SERVICE account, because you cannot log on to the account and add user variables.
    Adding all the variables as System Variables solves the problem. Took me a while to figure out, but now it works.

    Also I added variables for “ORA_HOME” and “ORACLE_HOME” pointing to the same folder as the TNS_ADMIN variable.
    Not sure if it is required.

    Again, thanks! :) Keep up the good work.

  108. 108
    my2cents:

    Hi i’m a complete newb but i managed to use the instant client locally using these values

    - – - – - – - – - – - – - – - – - – - – - – -
    orcl =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE NAME=XE)
    (FAILOVER_MODE =
    (TYPE = SELECT)
    (METHOD = BASIC)
    (RETRIES = 180)
    (DELAY = 5)
    )
    )
    )

    and then just logging in as

    yourusername@orcl
    yourpassword

    hope this helps others who just want to practice locally

  109. 109
    Nag:

    You are awesome

  110. 110
    adli:

    nice guide for new guy like me..very straight forward..thumbs up

  111. 111
    karan:

    hi i have instantclient oracle 11.2 with me and write now i dnt now where DB server is located but i want to finish configuration of client on my system. the instantclient oracle 11.2 what i have does not contain any exe file with it only some dll files are there , can you please help me out..

  112. 112
    Liyaqath:

    Hi Vitaliy Mogilevskiy,
    I have been trying to connect Oracle databse which has been hosted remotely from my PC through C++.
    Can you guide on procedure to connect

  113. 113
    Cristina:

    Thank you! I followed the instructions and everything works just fine.

  114. 114
    Monjur:

    Hello MVE,
    I facing a problem. I hope you could help me out. Situation is, I have two different network. One is Class B type and other is Class C network. I am running an Asp.net web server in B and Oracle database server in C. I have configured oracle instant client in my asp.net web server(B) with editing path variable. The Problem is Oracle database server(C) is in Linux platform and can not pingable from B network. But I need access the database. I’ve installed Oracle client in a windows PC in C network that is pingable from B network. Now question is, is it feasible to deploy this kind of architecture? Note: I’m getting tns:connection time out message while trying to connect oracle client in C network.

  115. 115
    MVE:

    @Monjur

    Yours is a typical setup — Database Nodes are often protected by a Firewall and sometimes are on a different subnet from Database Clients.

    In either case a network administrator will have to patch a hole in a firewall for your asp.net web server to connect to your database server or if there’s no firewall simply adjust your asp.net server’s network configuration to properly route requests to your database server by creating a special network route rule.

    -HTH

  116. 116
    priyanthie:

    thanks

  117. 117
    Jo:

    Hi Vitaly
    can you help me.
    The context is
    Windows Vista – 10.0.0.109 – Instantclient 11gr2
    Windows 2000 – 10.0.0.204 – Oracle Database 9i
    tnsnames.ora is in instantclient_11_2 directory :
    ORCL =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.109)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
    )
    )

    I have created a data source name and tested it successfuly but when I create a link to this db in Microsoft Access using this dsn, i get message “ODBC call failed ORA-12541 TNS NoListener”.
    Database and listener are started on the server side.
    Note that I can connect to the database via sqlplus : sqlplus hr/hr@orcl

    Thanks

  118. 118
    Jyoti:

    Hello ,

    I need your help.
    My Oracle database at my server is Oracle 8i.
    My new PC, has Oracle client 10g.
    My application residing at the client PC uses exp to export data of few tables based on user’s input parameters.

    Now, I can not export as exp.exe does not reside in the client PC. Please help how can I solve this..

    Thanks in advance.

  119. 119
    MVE:

    @Jyoti EXP utility should be ran from the server — login to your database server as oracle user and run EXP from there.

  120. 120
    Bimal:

    Please guide me how to install oracle 11g on Window server 2008 R2.

  121. 121
    Steve:

    I’m running on a Windows Server 2008 R2. I’ve downloaded Version 11.2.0.2.0 of Instant Client Basic, Instant Client Package – SQL*Plus, and Instant Client Package – ODBC. Following your (excellent) instructions I was able to successfully connect to my Oracle 9i database using SQL*Plus. I have some old “Classic ASP” code that needs to connect to this same Oracle 9i DB. On my old NT server I was able to use Microsoft’s MSDAORA provider. This provider is no longer supported and not supplied with Win28k R2 server. So, I need a provider to use. I tried OraOLEDB but get the message “Provider cannot be found”. Also I don’t see any Oracle drivers listed in the Windows ODBC Data Source Administrator. I had hoped that installing the Instant Client would have actually installed a provider/driver. Would prefer OLEDB but ODBC will work. I assume there’s something I haven’t yet done. Any advice?

  122. 122
    Sreekar:

    Hello Vitaly,
    Im working as an IT Professional in an org. I had to install oracle 10g client for win7 64 bit..I have installed administrator console one tat machine.everything was done perfectly and we got tns names folder also. In the below line
    ADDRESS = (PROTOCOL = TCP)(HOST = QABLR)(PORT = 1521)) instead of QABLR for host we are givin an ip address and wen we try to save it , it throws an error which says something something ending with access denied. The employee has admin access to the system and we have made everything fine but the prob stil exists. can you please suggest me on this..and is dis info enough for you to come to an understanding about my issue?? Please help

    Regards,
    Sreekar.

  123. 123
    Michael:

    Hi all,
    I’m using vc6 to connect oracle, the code below. But if I using instanceclient, it cannot work. What should I do?

    #include “StdAfx.h”
    #include “DBAssistant.h”

    CDBAssistant::CDBAssistant(void) {
    IsFirstRecord = true;
    }

    CDBAssistant::~CDBAssistant(void) {
    }

    bool CDBAssistant::OpenConnect(ORACLEDB_PARA dbinfo) {
    if (m_pConnection != NULL && m_pConnection->State)
    return true;
    try {
    m_pConnection.CreateInstance(__uuidof(Connection));
    _bstr_t connStr = “Provider=OraOLEDB.Oracle.1;User ID=”
    + dbinfo.Username + “;Password=”
    + dbinfo.Password + “;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=”
    + dbinfo.Hostname + “)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=”
    + dbinfo.SID + “)));Persist Security Info=True”;
    m_pConnection->Open(connStr,”",”",adModeUnknown);
    } catch (_com_error e) {
    AfxMessageBox(“Connection failed, Please check the config!”);
    return false;
    }
    return true;
    }

    bool CDBAssistant::ExecuteQuery(_bstr_t sql) {
    try {
    m_pResultset.CreateInstance(__uuidof(Recordset));
    m_pResultset->Open(sql, m_pConnection->GetConnectionString(),adOpenStatic,adLockOptimistic,adCmdText);

    if (m_pResultset->adoEOF) {
    m_pResultset->Close();
    return false;
    }
    IsFirstRecord = TRUE;
    } catch (_com_error e) {
    return false;
    }
    return true;
    }

    bool CDBAssistant::CloseRecordSet() {
    try {
    if (m_pResultset != NULL && m_pResultset->State) //GetState() == adStateOpen)
    m_pResultset->Close();
    } catch (_com_error e) {
    return false;
    }
    return true;
    }

    bool CDBAssistant::CloseConnection() {
    try {
    if (m_pConnection->State)
    m_pConnection->Close();
    } catch (_com_error e) {
    return false;
    }
    return true;
    }

    bool CDBAssistant::Next() {
    try {
    if (m_pResultset->adoEOF) {
    return false;
    }
    if (IsFirstRecord) {
    IsFirstRecord = false;
    } else {
    m_pResultset->MoveNext();
    if (m_pResultset->adoEOF) //Whether the next record is adoEOF
    return false;
    }
    } catch (_com_error e) {
    return false;
    }
    return true;
    }

    _bstr_t CDBAssistant::GetString(_bstr_t fieldname) {
    try {
    _variant_t fieldvalue = m_pResultset->GetCollect(fieldname);
    if (fieldvalue.vt == VT_NULL)
    return “”;
    return fieldvalue;
    } catch (_com_error e) {
    }
    return “”;
    }

    int CDBAssistant::GetRecordCount() {
    try {
    _variant_t fieldvalue = m_pResultset->GetRecordCount();
    return fieldvalue.intVal;
    } catch (_com_error e) {
    }
    return 0;
    }

  124. 124
    deddy:

    Hi All,

    I am installing oracle 11gR2 x64 on my Windows 7 x64bit environment and working fine.
    I want to access/administer the database using TOAD but always failed.
    I am using TOAD version 8.6.10 for x32 environment.
    I installed oracle client x32 since toad only support x32 environment.

    Am I doing incorrectly ?

    Thanks & Best Regards,
    deddy

  125. 125
    NISHANT:

    I have to shutdown the database using “shutdown immediate” and startup the database using “startup mount” in Sqlplus.If I type shutdown immediate,database is shutdown after I type startup mount,I’m getting this error: ORA-12514 : TNS: listener does not currently know How to overcome this particular issue.

    please advice..

  126. 126
    NISHANT:

    Yes I got the solution.

    In environment variable ,Path the was wrongly mentioned
    E:\oradata\product\11.1.0\client_1\bin

    it was actually in D:\app\Administrator\product\11.1.0\db_1\bin.

    So i guess it was first looking in E , where the one in E was not actual installation

  127. 127
    Firend:

    Great ! It works when I install the 11g server and Toad10.1 in same machine.

    But I feel strange why I must install some extra software? I have already installed 11g server on the same machine.

  128. 128
    wolve:

    hi

    i loaded oracle instant client 64 bit ,i tried connecting using Toad but am getting the following error “Cannot load OCI DLL: C:\Oracle\instantclient_10_2\oci.dll”

    can you assist
    THanks

  129. 129
    Friend:

    wolve, you need install 32 bit instantclient(lite) in 64 bit OS.

  130. 130
    Saurabh:

    Hi!

    You have a great post – very useful.

    I have Windows 7 32bit and downloaded –
    instantclient-basic-win32-11.1.0.7.0
    instantclient-sqlplus-win32-11.1.0.7.0

    After unzipping, I had to renae the basic file folder to ‘product’ to match you scrrenshot

    I followed the steps described but on trying to connect, I get the message – “The program can’t start because OCI.dll is missing from your computer”.

    Plz help

    thanks
    Saurabh

  131. 131
    Mauricio:

    Hi,

    actually i dont have an oracle instance in local…i’d like to know if all this works to connect a remote service. Where should i put the tns file???

  132. 132
    Dumas Philippe:

    Hi,

    Thanks a lot for your post. It was very useful to install even in a w2008r2 (64b) platform
    God bless you for your help.

    Philippe

  133. 133
    Mike:

    Thank you so much for this post.
    Two of us spent about 2 or 3 hours yesterday trying to accomplish this without success.
    Today, thanks to you, I got it!

  134. 134
    asha:

    I have a Windows 2008 Machine (64 bit OS). Which version of Oracle 10g client should I install (32 bit client or 64 bit client) on this 64 bit OS?

  135. 135
    Bharath Kumar:

    Thanks for this detailed information for the client installation. It was really helpful.

  136. 136
    Toufik HAMDANI:

    Hello, really nice post. I can now connect to my database !

    thanks a lot !

  137. 137
    Installer instant client oracle sous Windows | Iteas : Ideas about IT:

    [...] dans une console Windows.Voilà, j’espère que ça en aidera plus d’un. Je vous met le lien vers l’article en Anglais qui m’a aidé à écrire cet article (qui n’en est [...]

  138. 138
    taiwo:

    please my users are getting the following errors, i need help to resove the issues..thanks in advance

    error 6 initializing SQL*Plus

    Message file sp1.msbnot found

    SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

  139. 139
    Dave MacLean:

    Just used your post (for basic and client).
    We use UNIX-based Oracle with PC-based Oracle client/ODBC to connect Oracle tables into Esri ArcGIS.
    Thanks very much for the info – worked like a charm.

  140. 140
    Vishal:

    Thanks.. helped me a lot in setting up Oracle instant client on top of my existing ones.

  141. 141
    Praveen:

    This help me is quickly setting up perl DBI/DBD::Oralce environment on my windows box.

    Thanks a lot

  142. 142
    Roshan:

    Hi, I have installed Oracle Database 10g Client Release 2 (10.2.0.4) on windows server 64 bit,
    Can I now install Oracle 10g server on same machine?
    If yes then will i have to uninstall client first..?

    Any help will be appreciable…

  143. 143
    Roshan:

    Hi, I have installed Oracle Database 10g Client Release 2 (10.2.0.4) on windows server 64 bit.

    when i use below cmd.

    c:\>set oracle_sid=xxx
    c:\>sqlplus xxx/xxx as sysdba;

    I get ERROR:
    ORA-12560: TNS:protocol adapter error.

    Please help to solve this issue..

  144. 144
    Gino:

    Trying Win XP with 11.2 instant client.
    I get the below even with no “ORACLE_HOME” value defined. Any ideas?

    C:\Documents and Settings\wilfredd>echo %ORACLE_HOME%
    %ORACLE_HOME%

    C:\Documents and Settings\wilfredd>sqlplus willife@mfadsii
    Error 6 initializing SQL*Plus
    Message file sp1.msb not found
    SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

  145. 145
    Gino:

    (PS: My TNS_ADMIN and PATH variables are correct, and there are no other oracle client instances installed.)

  146. 146
    Fik:

    Nice, thx a lot m8. You helped me a lot. BTW I wonder how can some1 publish installation like this, without some serious help within installation files. Official read me suck so much …

  147. 147
    Lokesh:

    Hi,

    I have done the below steps but still getting the error “ORA-12154: TNS:could not resolve the connect identifier specified”. I’m trying to connect using “PL/SQL Developer” software. Can anybody please suggest me what more settings do I need to check??. Thanks a lot for your response.

    1. I have downloaded instant client “instantclient-basic-nt-11.2.0.3.0.zip” and unzipped it. The folder paths are now : “C:\oracle\instantclient-basic-nt-11.2.0.3.0\instantclient_11_2″
    2. Set the TNS_ADMIN system variable to point to the above path as well as “C:\oracle\instantclient-basic-nt-11.2.0.3.0″
    3. Included the same 2 paths at the beginning of ‘path’ system variable
    4. Placed tnsnames.ora(it’s perfectly tnsnames.ora, not tnsnames.ora.txt) file under “C:\oracle\instantclient-basic-nt-11.2.0.3.0\instantclient_11_2″
    5. The content of tnsnames.ora is as shown:

    MYDB =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = poulpi6srv)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = POULPI6)
    )
    )
    6. There is no other version of Oracle installed on my local machine (neither instant Client nor the full version)

  148. 148
    Lokesh:

    Adding to my above post, I am using Windows 7 64-bit OS.

  149. 149
    Ashok:

    Hi Lokesh,
    U need to copy the oci.dll file from the server and need to replace in the instant client folder.
    I hope this will solve ur problem.

    Ashok

  150. 150
    Lokesh:

    Thanks for your suggestion Ashok.

    But the problem is I don’t have access permission to the oracle folders on server.

  151. 151
    Keith Saby:

    MVE – you are the man! I was searching all over the Oracle sites/forums for instantclient install steps. I could not find clear, concise directions anywhere on the Oracle pages. You have beautiful instructions here my man. Much appreciated!

  152. 152
    Gazmend:

    Worked like a charm.Cheers m8

  153. 153
    arjuna:

    Very very useful,thank you very much.Cheers…

  154. 154
    yuga:

    in my system oracle odbc dirver not showing ,i have installed oracle 10g and jdk 7
    but not showing dirver
    plz help me
    thanks in advance .

  155. 155
    yuga:

    mine is windows7 64 bit

  156. 156
    huakun:

    thanks,非常感谢

  157. 157
    Mack:

    Years after posting this, people, me included, are still praising it. Clear and gets the job done. Pity Oracle couldn’t write a proper installer!
    Thanks again.

  158. 158
    Paul:

    I’m getting an error:

    Connect failed because target host or object does not exists.

    How to solve it?

  159. 159
    Some notes on using Oracle 11 Database Instant Client | Clarify Solutions | Dovetail Software:

    [...] the tnsnames.ora file, and quickly realized that there wasn’t one. A little googling led me to a helpful blog post at [...]

  160. 160
    Daniel:

    Getting the same error as paul, “Connect failed because target host or object does not exist”, let me know what I can do to get past this.

  161. 161
    Oyadioo:

    Please i need help….

    I am trying to Create a test Environment on a linux VM.

    From the Vm,Every thing seems to be fine.

    I can Start the Listerner and this is what i get After i start it.

    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Roman-OEL)(PORT=1521)))

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
    STATUS of the LISTENER
    ————————
    Alias REMOTE_LISTENER2
    Version TNSLSNR for Linux: Version 11.2.0.1.0 – Production
    Start Date 15-AUG-2013 06:52:44
    Uptime 0 days 0 hr. 0 min. 1 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /home/Roman/app/Roman/product/11.2.0/dbhome_2/network/admin/listener.ora
    Listener Log File /home/Roman/app/Roman/diag/tnslsnr/Roman-OEL/remote_listener2/alert/log.xml
    Listening Endpoints Summary…
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Roman-OEL)(PORT=1521)))
    Services Summary…
    Service “ORCL” has 1 instance(s).
    Instance “ORCL”, status UNKNOWN, has 1 handler(s) for this service…
    Service “PLSExtProc” has 1 instance(s).

    I Was also able to connect to Database

    [Roman@Roman-OEL ~]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 15 06:52:54 2013

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

    ERROR:
    ORA-01031: insufficient privileges

    Enter user-name:
    ERROR:
    ORA-01017: invalid username/password; logon denied

    Enter user-name:
    ERROR:
    ORA-01017: invalid username/password; logon denied

    SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
    [Roman@Roman-OEL ~]$ sqlplus /nolog

    SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 15 06:53:45 2013

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

    SQL> connect sys/fundtech as sysdba
    Connected to an idle instance.
    SQL> startup
    ORACLE instance started.

    Total System Global Area 841162752 bytes
    Fixed Size 1339768 bytes
    Variable Size 566234760 bytes
    Database Buffers 268435456 bytes
    Redo Buffers 5152768 bytes
    Database mounted.
    Database opened.

    Further more,I can run Query and all of that….

    Please see my listerner settings

    # listener.ora Network Configuration File: /home/Roman/app/Roman/product/11.2.0/dbhome_2/network/admin/listener.ora
    # Generated by Oracle configuration tools.

    SID_LIST_REMOTE_LISTENER2 =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = /home/Roman/app/Roman/product/11.2.0/dbhome_2)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = ORCL)
    (ORACLE_HOME = /home/Roman/app/Roman/product/11.2.0/dbhome_2)
    (SID_NAME = ORCL)
    )
    )

    REMOTE_LISTENER2 =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = Roman-OEL)(PORT = 1521))
    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    )
    ADR_BASE_LISTENER = /home/Roman/app/Roman

    Tns Setting

    # tnsnames.ora Network Configuration File: /home/Roman/app/Roman/product/11.2.0/dbhome_2/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.

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

    SQLNET.ORA

    # sqlnet.ora Network Configuration File: /home/Roman/app/Roman/product/11.2.0/dbhome_2/network/admin/sqlnet.ora
    # Generated by Oracle configuration tools.

    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

    SQLNET.EXPIRE_TIME=10
    SQLNET.AUTHENTICATION_SERVICES=NONE
    NAMES.DIRECTORY_PATH= (TNSNAMES,ONAMES,HOST NAME)
    ADR_BASE = /home/Roman/app/Roman

    When i try to connect from my host machine,i get this error

    ORA-01034 :ORACLE not available
    ORA-27101: Shared memory realm does not Exist
    LINUX ERROR:2: NO Such File or Directory
    Process ID:0
    Session ID:0 Serial Number :0

    I am trying to connect from a PL/Sql Developer client.

    On my host machine i have a database server running as plsqldeveloper could not reach a server without installing a server on that machine ao i have two tns files and i have added this address to both tns files.

    Please See information below.
    # tnsnames.ora Network Configuration File: C:\app\oyadioo\product\11.2.0\Client_1\network\admin\tnsnames.ora
    # Generated by Oracle configuration tools.
    ORCL2 =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.61.139)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
    )
    )
    LISTENER_TEST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

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

    OYADIOO_TEST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = TEST)
    )
    )

    ACCESS_LIVE =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.236.8.108)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = ghadb)
    )
    )

    FCJLIVE =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.9.200.140)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = FCJLIVE)
    )
    )

    FCCUBS =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.9.200.139)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = FCCUBS)
    )
    )

    Oracle Db on Host

    # tnsnames.ora Network Configuration File: C:\app\oyadioo\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
    # Generated by Oracle configuration tools.
    ORCL2 =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.61.139)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
    )
    )
    ACCESS_LIVE =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.236.8.108)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = ghadb)
    )
    )

    FUNDTECH =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = Fundtech)
    )
    )

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

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

    FCJLIVE =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.140)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = FCJLIVE)
    )
    )
    TEST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = TEST)
    )
    )

    FCCUBS =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.139)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = FCCUBS)
    )
    )

    SQLNET.ORA of DB ON HOST SYSTEM

    # sqlnet.ora Network Configuration File: C:\app\oyadioo\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
    # Generated by Oracle configuration tools.

    # This file is actually generated by netca. But if customers choose to
    # install “Software Only”, this file wont exist and without the native
    # authentication, they will not be able to connect to the database on NT.

    SQLNET.AUTHENTICATION_SERVICES= (NTS)

    SQLNET.AUTHENTICATION_SERVICES= (NONE)

    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

    SQLNET.ORA of ORACLE CLIENT

    # sqlnet.ora Network Configuration File: C:\app\oyadioo\product\11.2.0\client_1\network\admin\sqlnet.ora
    # Generated by Oracle configuration tools.

    # This file is actually generated by netca. But if customers choose to
    # install “Software Only”, this file wont exist and without the native
    # authentication, they will not be able to connect to the database on NT.

    SQLNET.AUTHENTICATION_SERVICES= (NTS)

    SQLNET.AUTHENTICATION_SERVICES= (NONE)

    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

    When i do a tnsping ORCL@i19.168.61.139(IP OF VMWARE) from Host Syatem,i get

    TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 – Production on 15-AUG-2
    013 07:21:20

    Copyright (c) 1997, 2010, Oracle. All rights reserved.

    Used parameter files:
    C:\app\oyadioo\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

    TNS-03505: Failed to resolve name

    When i do Same tnsping 192.168.61.139 from host to linux server(VM),I get

    C:\Users\oyadioo>tnsping 192.168.61.139

    TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 – Production on 15-AUG-2
    013 07:49:00

    Copyright (c) 1997, 2010, Oracle. All rights reserved.

    Used parameter files:
    C:\app\oyadioo\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

    Used EZCONNECT adapter to resolve the alias
    Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTO
    COL=TCP)(HOST=192.168.61.139)(PORT=1521)))
    OK (40 msec)

    Please Help,I really need to be able to connect to this Vm without running my Queries withing the VM.

  162. 162
    baw:

    THANK YOU for this wonderful post, Vitaliy. Too bad Oracle doesn’t include this documentation on their site. It would save quite a bit of trouble.

    All the best!

  163. 163
    George:

    hello V,

    I have an application that is bundled with Oracle Instant client.the application uses the instant client to connect remotely to an oracle database using sqlplus.
    I get the error message TNS:protocol adapter error when i want to connect to the database.

    i have checked the environment variable and the PATH is “C:\oracle\instantclient” TNS_ADMIN is “C:\oracle\instantclient”.
    the oracle installation didn’t come with a tnsnames.ora file, so i created one in the instant client folder with the details below and :

    UBATEST.WORLD =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.4.71)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = UBADB)
    )
    )
    But I still get the error message TNS:protocol adapter error

    please advise

    George

  164. 164
    Nick:

    Thank you! I’ve installed full-blown Oracle databases before, but never just the client. Your post was very helpful.

  165. 165
    Anders Persson:

    Saved my day, thanks !! As a side note backward compability is two down…installed 12 when we run an Oracle 8 instance, which broke. Getting 11_2 got me going…

  166. 166
    derm exclusive a:

    WOW just ԝhat I was looking for. Came here by searching for
    instantclient basic

  167. 167
    Kevin:

    Thanks Vitaliy. It was a big help.

  168. 168
    vivek:

    I am getting following error

    SQL*Plus: Release 11.1.0.7.0 – Production on Thu May 22 18:42:01 2014

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

    Enter user-name: RALZZ006580
    Enter password:
    ERROR:
    ORA-12560: TNS:protocol adapter error

  169. 169
    Sachin:

    Thanks! This helped really well.

  170. 170
    E Konopka:

    A GREAT help, thank you!

  171. 171
    NJGIN:

    Thank you for your lucid and accurate instructions!

Leave a Reply

Vitaliy Mogilevskiy - Solving ORACLE problems since 1996.

View Vitaliy Mogilevskiy's profile on LinkedIn

What you will find here is what works for me day in day out - 24x7x365 - simple ORACLE DBA Tools written using components that are available on every Unix platform with an ORACLE database -- sqlplus and KSH.

Do you have DBA questions? Head over to Ask DBA.

RSS DBA Forums


Recent Comments

Categories

Blogroll


Archives