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:
Download Software
Go to Instant Client Downloads for Microsoft Windows (32-bit) download page:
http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html
And download basic-win32 and sqlplus-win32 files to your PC (for example):
- instantclient-basic-win32-11.1.0.7.0.zip
- instantclient-sqlplus-win32-11.1.0.7.0.zip
Create Oracle Directory
Create a C:\oracle directory on your C drive (if you don’t already have one) and move both files into C:\oracle.
Unzip both files
- first unzip
instantclient-basic-win32-11.1.0.7.0.zip - 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. Make sure it’s before all other path values that point to your OLD Oracle clients, then clickOk
Now find a variable called TNS_ADMIN – it could be in either panel (System / User), if you don’t have 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 already have an existing copy of tnsnames.ora file then place it to:
C:\oracle\instantclient_11_1
If you don’t – then 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’s prompt – enter the following: 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>
Known Errors:
If you get the ORA-12705 error while running sqlplus:
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_LANGfrom 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).
Bonus PDF
If you’d like to get a copy of this article in an easy to share PDF – please sign up for my newsletter – Confessions of an Oracle DBA where I share tips, scripts and tricks I’ve learned during almost two decades in the tech field as an Oracle DBA:
SUBSCRIBEAdditional Resources
- If you have any additional questions please join our Oracle DBA Community and post them there.
- And if you need any further Oracle DBA help – I can be reached at: http://www.hashjoin.com/contact
End.
171 Comments
Vitaliy Mogilevskiy August 26, 2009
Posted In: Operations
Tags: instantclient basic, instantclient sqlplus, oracle instant client, win32
[…] the tnsnames.ora file, and quickly realized that there wasn’t one. A little googling led me to a helpful blog post at […]
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 !
@Nicolas
I am glad it helped you out. Thanks a lot for your comment!
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……
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……..
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:
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
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……..
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
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…….
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
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…….
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
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:
That should get you in. You can then change your password using the following command
PS: In 11g passwords are case sensitive by default.
HTH,
– Vitaliy
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
Actually, I just set environment ORACLE_HOME and now able to login to sqlplus with command “sqlplus /nolog”.
Thanks
Nancy Yuan
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
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
you are missing “connect / as sysdba”
SQL> connect / as sysdba
ERROR:
ORA-12560: TNS:protocol adapter error
SQL>
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
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
This post is excellent!!! Congratulations!
Worked perfectly for me
Thanks a lot!!
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
Nancy,
Once you are connect “as sysdba” you can do anything. You can for instance change SYSTEM password using the following command:
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:
HTH,
– Vitaliy
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
Simon,
Yes, [WIN]11gR1 client –> [LINUX]11gR2 server should be just fine.
HTH,
– Vitaliy
Vitaly,
Thanks for the very fast response, I will test exp,imp from windows with client 11gr1 and report back.
Simon
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
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
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.
My thoughts also… Thanks,
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.
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.
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,
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
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):
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
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…
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
nevermind, I checked my tnsnames.ora, and I had the wrong host name listed, now it works!
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.
@Rob
Is your TNS_ADMIN set?
You can check it using echo %TNS_ADMIN% from command prompt (cmd)
-HTH
Yes, I just tripple checked it, and it is set.
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.
@Rob
No problem — I am glad you sorted it out!
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.
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
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
@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.
I only used Rman once… Can it be used from my 11g server to “backup” schemas from another server (9i, 10g)?
@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.
@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).
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.
@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%.
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.
Just another thank you for creating this page. After a lot of searching, I finally got the help I needed, thanks to you!
Chris
Thank you, this helped a lot, I was stuck on the NLS error
Of course Oracle’s own pages offered no help… 😛
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…
@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?
Thanks.
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.
thanks…..
Thank you SO MUCH for posting this! That saved me a ton of time!
Thanks Vitaliy,
It indeed worked out well.
Rahul
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.
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.
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
@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:
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
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.
@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
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.
i installed oracle insta client i added tnsnames.ora file
but i cannot find sqlnet.ora file
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
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…
@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:
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.
@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
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
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
@Andrei
What’s in your tnsnames.ora? Did you define “service_name” in there?
– Vitaliy
@Chris
Make sure you’ve defined the TNS_ADMIN and PATH variables for the new user.
– Vitaliy
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
… 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
@Andrei
Can you ping database.domain.com?
– Vitaliy
Vitaliy, i get a “request timed out”.
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
@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
Thanks for putting this down, really appreciated.
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!
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.
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!
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.
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!
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.
@anony
I’d suggest to startup sqlplus without login and then issue these commands:
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:
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).
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
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
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
Thanks! You have been a great, great help to me.
Kind regards,
Darrell
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.
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.
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
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.
Thanks very much
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??????
@William please double check your TNS_ADMIN variable to ensure it points to the location where tnsnames.ora file is stored.
Thanks for info, very useful !
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.
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
You are awesome
nice guide for new guy like me..very straight forward..thumbs up
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..
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
Thank you! I followed the instructions and everything works just fine.
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.
@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
thanks
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
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.
@Jyoti EXP utility should be ran from the server — login to your database server as oracle user and run EXP from there.
Please guide me how to install oracle 11g on Window server 2008 R2.
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?
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.
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;
}
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
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..
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
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.
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
wolve, you need install 32 bit instantclient(lite) in 64 bit OS.
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
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???
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
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!
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?
Thanks for this detailed information for the client installation. It was really helpful.
Hello, really nice post. I can now connect to my database !
thanks a lot !
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
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.
Thanks.. helped me a lot in setting up Oracle instant client on top of my existing ones.
This help me is quickly setting up perl DBI/DBD::Oralce environment on my windows box.
Thanks a lot
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…
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..
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
(PS: My TNS_ADMIN and PATH variables are correct, and there are no other oracle client instances installed.)
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 …
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)
Adding to my above post, I am using Windows 7 64-bit OS.
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
Thanks for your suggestion Ashok.
But the problem is I don’t have access permission to the oracle folders on server.
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!
Worked like a charm.Cheers m8
Very very useful,thank you very much.Cheers…
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 .
mine is windows7 64 bit
thanks,非常感谢
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.
I’m getting an error:
Connect failed because target host or object does not exists.
How to solve it?
[…] the tnsnames.ora file, and quickly realized that there wasn’t one. A little googling led me to a helpful blog post at […]
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.
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.
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!
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
Thank you! I’ve installed full-blown Oracle databases before, but never just the client. Your post was very helpful.
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…
WOW just ԝhat I was looking for. Came here by searching for
instantclient basic
Thanks Vitaliy. It was a big help.
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
Thanks! This helped really well.
A GREAT help, thank you!
Thank you for your lucid and accurate instructions!
Thank you a thousand for your guide.