Thanks for the question, Adel.

Asked: May 13, 2019 - 7:33 am UTC

Last updated: May 22, 2019 - 12:28 am UTC

Version: 12.2C

Viewed 10K+ times! This question is

You Asked

I use a single instance 12.2C 64-bit Oracle database on a window server 2012R2. suddenly this error ORA-12560: TNS:protocol adapter error
began to show when I try to enter the sqlplus. whatever I have searched the internet a lot for a solution but didn't get any. This is the result of

lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 13-MAY-2019 10:18:30

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=TST01)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Production
Start Date                11-MAY-2019 11:13:21
Uptime                    1 days 23 hr. 5 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   E:\app\oracle\product\12.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         E:\app\oracle\diag\tnslsnr\TST01\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TST01)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=TST01)(PORT=5500))(Security=(my_wallet_directory=E:\APP\ORACLE\admin\ORCL\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 2 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl" has 2 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

And this is the result of

tnsping localhost 

TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 13-MAY-2019 10:22:51

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

Used parameter files:
E:\app\oracle\product\12.2.0\dbhome_1\network\admin\sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
OK (0 msec)

also:

tnsping 192.168.0.13

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 13-MAY-2019 10:27:07

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

Used parameter files:
E:\app\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=(PROTOCOL=TCP)(HOST=192.168.0.13)(PORT=1521)))
OK (10 msec)

some solutions said to use sqlplus /nolog, with this command I can enter, but issuing any command in this sqlplus /nolog(which is not a dbuser) results "not connected", so there is no use to enter in nolog mode. By the way, the OracleServiceORCL service is running.

and Connor said...

Let's make sure you are picking up the right environment.

Try this:

set ORACLE_HOME=E:\app\oracle\product\12.2.0\dbhome_1\
set PATH=E:\app\oracle\product\12.2.0\dbhome_1\bin;%PATH%
set ORACLE_SID=orcl

then try "sqlplus myuser/mypass"

and see how you go.

Rating

(8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, May 15, 2019 - 10:22 am UTC

when each time I open command prompt so each time I need to set environment variable??

May 17, 2019 - 9:12 am UTC

See the comment below

To "A reader"

J. Laurindo Chiappa, May 15, 2019 - 8:44 pm UTC

Hi : your answer is IT DEPENDS... See, in Windows the Oracle RDBMS software maintains ORACLE_SID and ORACLE_HOME variables in the REGISTRY, and in the case of multiple HOMEs the last one will be the active...
SO, if you have only ONE ORACLE_HOME in the machine then NO NEED to set manually the variables in command prompt, the only one existing WILL be used - BUT, when you have multiple HOMEs for multiple Oracle software installation YES, you WILL NEED to indicate which one you want to use...

I myself have a lot of Oracle RDBMS versions installed in teh same machine, so I have .BAT SCRIPTs to set the version I want, this way :

C:\Users\jlchi_000>dir *.bat

01/03/2019 13:17 109 client11gR2x64.bat
05/03/2019 19:45 120 client12cR2x64.bat
05/03/2019 19:48 120 client12cR2x86.bat
22/03/2017 10:09 18 flush_dns.bat
08/02/2019 15:18 135 instantclient18c.bat
27/01/2019 15:33 113 o11gR2.bat
03/03/2018 12:47 118 o11gr2SE.bat
20/04/2017 12:49 117 o12cR2.bat
11/04/2019 13:19 124 o18c.bat
13/03/2019 14:38 126 xe.bat

the content for each .BAT is such :

C:\Users\jlchi_000>type o11gr2.bat
SET ORACLE_SID=O11GR2
SET ORACLE_HOME=c:\app\oracle\product\11gr2\dbhome_r2
SET PATH=%ORACLE_HOME%\BIN;%PATH%

C:\Users\jlchi_000>type o12cr2.bat
SET ORACLE_SID=ORCL12c
SET ORACLE_HOME=C:\app\oracle12cR2\product\12.2.0\dbhome
SET PATH=%ORACLE_HOME%\BIN;%PATH%

C:\Users\jlchi_000>type xe.bat
SET ORACLE_SID=XE
SET LOCAL=xepdb1
SET ORACLE_HOME=c:\app\oracle\product\xe18c\dbhomexe
SET PATH=%ORACLE_HOME%\BIN;%PATH%

C:\Users\jlchi_000>

==> Then, when I want to use Oracle 11g I run o11gr2.bat, when I want to use o12cR2 I run o12cr2.bat, and so on....

Regards,

Chiappa

May 17, 2019 - 9:11 am UTC

Thanks for sharing.

working one time only

Adel Altaee, May 20, 2019 - 12:27 pm UTC

it works, but, I must set the variables each time I open CMD. When setting the variables, I can enter sqlplus, but when closing CMD and opening a new one, I have to set them again. I don't have to set them again if I Exit the sqlplus, only when I close CMD

May 21, 2019 - 4:10 am UTC

You can set environment variables for your user, ie

Right click "Your PC"
Properties
Advanced System Settings
Environment Variables

or

Choose "PC Settings"
type "environment variables" to get to the same place

To Adel

J. Laurindo Chiappa, May 20, 2019 - 1:36 pm UTC

C:\Users\jlchi_000>type db1.bat
@echo off
SET ORACLE_SID=O11GR2
SET ORACLE_HOME=c:\app\oracle\product\11gr2\dbhome_r2
SET PATH=%ORACLE_HOME%\BIN;%PATH%
sqlplus

C:\Users\jlchi_000>db1

SQL*Plus: Release 11.2.0.4.0 Production on Seg Mai 20 10:33:05 2019

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

Informe o nome do usuário:

Best regards,

Chiappa

To Adel

J. Laurindo Chiappa, May 20, 2019 - 1:38 pm UTC

hi : yes, if you have installed multiple Oracle products there is no way to allow Oracle to guess what ORACLE_HOME to use, yes....
Suppose you have two ORACLE_HOMEs for two databases (DB1 and DB2), what you can do is something like :

a) write a .BAT scripts setting the variables AND calling sqlplus, much in this sense :

C:\Users\jlchi_000>type db1.bat
@echo off
SET ORACLE_SID=O11GR2
SET ORACLE_HOME=c:\app\oracle\product\11gr2\dbhome_r2
SET PATH=%ORACLE_HOME%\BIN;%PATH%
sqlplus

and write another called db2.bat setting the correct values for accessing db2, so on... This way, when I want to access db1 via sqlplus I just type db1 :

C:\Users\jlchi_000>db1

SQL*Plus: Release 11.2.0.4.0 Production on Seg Mai 20 10:33:05 2019

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

Informe o nome do usuário:

==> No need for setting the env variables each time you open the cmd prompt....

OR

b) put a shortcut in the desktop pointing to the db1/db2 scripts...

Best regards,

Chiappa

Adel Altaee, May 21, 2019 - 6:23 am UTC

Hi
But I only have one Oracle product. Though, there are multiple databases is there, but only one product is installed

To Adel

J. Laurindo Chiappa, May 21, 2019 - 12:46 pm UTC

If you have only one Oracle product installed (the RDBMS, I suppose) BUT this RDBMS is controlling multiple databases , the ORACLE_HOME will be always the same (of course) BUT the ORACLE_SID will change for each database, right ?
So, if you want to connect without one entry in TNSNAMES.ORA for each databaseyou WILL neeed to have multiple .BAT scripts setting the ORACLE_SID properly for each one....

OF COURSE, is your call to choose local connections via ORACLE_SID OR simply maintain the entries in TNSNAMES.ORA for each database, and thus if you want to connect via sqlplus to the db1, you use :

sqlplus user/password@entryinTNSNAMES.ORAfordb1

when you want to connect to db2 you do :

sqlplus user/password@entryinTNSNAMES.ORAfordb2

And so on....

regards,

Chiappa

Adel Altaee, May 21, 2019 - 1:42 pm UTC

Hi
actually, all have the same SID, which is orcl
but I was able to enter through this command

sqlplus user/password@orcl

thank you for your assistance

May 22, 2019 - 12:28 am UTC

glad we could help

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

To "A reader"

working one time only

To Adel

To Adel

To Adel

More to Explore

Administration

Ora-12560: TNS: protocol adapter error

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

To "A reader"

working one time only

To Adel

To Adel

To Adel

More to Explore

Administration