We help IT Professionals succeed at work.

I am getting below error on windows 7 oracle database

Skumar29oct
Skumar29oct asked
on

Hi,

I am getting below error while connecting to my oracle database 11g on windows 7 after restarting my laptop. How ever I was able to connect before restart.
======
C:\Users\Sanjay.Kumar>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 3 17:51:06 2011

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

ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:
=========


Additional information.

1. I have installed below software on the same laptop.
 A. 11g client with 32 bit OS.
 B. 11g DB (EE) with 64 bit OS.
 C. SQL Developer 32 bit
3. This laptop is also having ERStudio 32 bit.
===========

I have tried setting
 A. set oracle_sid=DB_NAME
 B. set local=DB_NAME

=======
I have checked listener is up and running.
------------
C:\Users\Sanjay.Kumar>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.1.0.6.0 - Production on 03-NOV-2011 17:50:58

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.1.0.6.0 - Production
Start Date                03-NOV-2011 16:19:09
Uptime                    0 days 1 hr. 31 min. 49 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\Sanjay.Kumar\product\11.1.0\db_1\network\admin\listener.ora
Listener Log File         c:\app\sanjay.kumar\diag\tnslsnr\LONLT1098W\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LONLT1098W)(PORT=1521)))
Services Summary...
Service "sanjay" has 1 instance(s).
  Instance "sanjay", status READY, has 1 handler(s) for this service...
Service "sanjayXDB" has 1 instance(s).
  Instance "sanjay", status READY, has 1 handler(s) for this service...
Service "sanjay_XPT" has 1 instance(s).
  Instance "sanjay", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\Users\Sanjay.Kumar>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 3 17:51:06 2011

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

ERROR:
ORA-12560: TNS:protocol adapter error

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
The Oracle Service isn't running.

Look for OracleServicesanjay in Services.

Author

Commented:
It is running.

Author

Commented:
Just FYI,

Same issue is reported on another laptop having windows 7.
Here only oracle db OS 32 bit is installed.
===

C:\Users\Sanjay>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 3 21:36:43 2011

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

ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:

=========
I can re install the db if needed. But need to resolve it fast.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Check the alert log and see if you can see the database trying to start.

99% of the time when I see this error it is due the service.

I also do not use double quotes.  Have you tried: sqlplus / as sysdba

See if this helps:
http://www.question-defense.com/2011/02/10/sqlplus-error-ora-12560-tnsprotocol-adapter-error
Praveen Kumar ChandrashekatrDatabase Analysist Senior

Commented:
I will fully agree with slightwv

but sometime restarting the listener and oracle sevices will also work in windows.


and then conect to the database.
Commented:
You are trying to connect to the database as sysdba from the client's oracle_home.

Run command prompt:
1.) Make a cmd file PATH >> %userprofile%\desktop\oradb_env.cmd
2.) echo CMD >> %userprofile%\desktop\oradb_env.cmd

Now open the cmd file in a text editor and remove 32bit Oracle client directories from the path.
Save it.
Double-click the oradb_env.cmd file. Run sqlplus in the cmd window that opens.
Commented:
I do assume that you installed the database and client into different ORACLE_HOMEs.

You probably installed 32bit client after you installed the database. The installation modified the global PATH variable and it takes effect after reboot.
Also if you take a look at version numbers of lsnrctl and sqlplus, you will notice that lsnrctl is 11.1 and sqlplus is 11.2, which are a different product version.

Author

Commented:
Hi,

I am sorry for being late.
My it is brand new laptop and rebuild after few issues.
======
Now when I installed oracle 11.1.0.7 database again from the scratch.
I was able to connect before restarting the laptop.
But after restart getting below error.
=======
Note: I need to maintain few software with 32 bit therefore, I tried to install 32 bit database before the current one i.e. 64 bit.
===========


C:\Users\Sanjay.Kumar>
C:\Users\Sanjay.Kumar>sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Nov 8 09:49:50 2011

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

ERROR:
ORA-12541: TNS:no listener


Enter user-name:
C:\Users\Sanjay.Kumar>
C:\Users\Sanjay.Kumar>
C:\Users\Sanjay.Kumar>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.1.0.7.0 - Production on 08-NOV-2011 09:49:59

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 61: Unknown error

C:\Users\Sanjay.Kumar>lsnrctl start

C:\Users\Sanjay.Kumar>
C:\Users\Sanjay.Kumar>
C:\Users\Sanjay.Kumar>lsnrctl start LISTENER

LSNRCTL for 64-bit Windows: Version 11.1.0.7.0 - Production on 08-NOV-2011 09:51:57

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

Starting tnslsnr: please wait...

TNS-12560: TNS:protocol adapter error
 TNS-00530: Protocol adapter error


C:\Users\Sanjay.Kumar>set local=sanjay

C:\Users\Sanjay.Kumar>set oracle_sid=sanjay

C:\Users\Sanjay.Kumar>lsnrctl start

LSNRCTL for 64-bit Windows: Version 11.1.0.7.0 - Production on 08-NOV-2011 09:52:55

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

Starting tnslsnr: please wait...

TNS-12560: TNS:protocol adapter error
 TNS-00530: Protocol adapter error


C:\Users\Sanjay.Kumar>lsnrctl

LSNRCTL for 64-bit Windows: Version 11.1.0.7.0 - Production on 08-NOV-2011 09:59:15

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 61: Unknown error
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Myhost)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 61: Unknown error
LSNRCTL>

Author

Commented:
Below is screenshot.
======
I am not able to start
-6F48035225D8F617.bmp

Author

Commented:
Not able to start the 'OracleOraDb11g_home1TNSListener' service.
Below is screenshot.
========

-BA04927B51E8274.bmp
Praveen Kumar ChandrashekatrDatabase Analysist Senior
Commented:
the error states that the listener.ora file is missing.

please check that the file is present in Oracle_home/network/admin/ directory.

Commented:
So the system configuration is no longer the same?
Could you specify the oracle homes and installation types for these two installations?

Commented:
And since you are installing on a laptop: Did you use a loopback adapter for listener ip address?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
I'm also thinking it is a loopback adapter issue.

Did you follow the installation guide for Windows?

This is the 11.2 docs but it should be the same for 11.1:
http://download.oracle.com/docs/cd/E11882_01/install.112/e16773/reqs.htm#BABGCEAI
Commented:
There are two separate issues

1) Listener fails to start, probably because it is bound to a DHCP IP address.

2) sqlplus / as sysdba does not work, because there are two different oracle homes in global path and commandline probably picks up the wrong one.

Author

Commented:
Hi,

Now I can connect to sql and able to do all stuffs with db how ever still not able to start the listener.
In the mean time I checked the listener.ora , sqlnet.ora and tnsnames.ora all are at right place.
Also I tried to add one more listener and this one also getting the same error.
========
C:\Users\Sanjay.Kumar>sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Nov 8 22:15:50 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Sanjay.Kumar>lsnrctl status listener2

LSNRCTL for 64-bit Windows: Version 11.1.0.7.0 - Production on 08-NOV-2011 22:16:05

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Myhost)(PORT=1530)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 61: Unknown error

C:\Users\Sanjay.Kumar>lsnrctl

LSNRCTL for 64-bit Windows: Version 11.1.0.7.0 - Production on 08-NOV-2011 22:16:12

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> start listener2
Starting tnslsnr: please wait...

TNS-12560: TNS:protocol adapter error
 TNS-00530: Protocol adapter error

LSNRCTL>
LSNRCTL>
LSNRCTL>
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Myhost)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 61: Unknown error
LSNRCTL> exit

C:\Users\Sanjay.Kumar>sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Nov 8 22:30:12 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Author

Commented:
Your question:
Could you specify the oracle homes and installation types for these two installations?

My Ans:
Till below both are same:
C:\app\Sanjay.Kumar\product
After that 2 folders named 11.2.0 and 11.1.0
=======

Your question:
Did you use a loopback adapter for listener ip address?

My Ans:
No
========

Your question:
Did you follow the installation guide for Windows?
My Ans:
Yes, I have installed oracle multiple times and have troubleshooted the issues.
=======

Note: I agree with your point it may be loop back or DHCP related issue.

Please let me know your observation.

Regards,
Sanjay
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Why are you wanting to start a second listener?

Anyway this question was how to connect to the database after a reboot.  Has that been resolved?

If so, what resolved the problem?
Commented:
So you can sqlplus / as sysdba now.
This completely bypasses the listener so the rest of the environment is working.

To fix the listener, you will need to make a loopback adapter as per the oracle docs. For the loopback adapter, pick up one static address of an unused private subnet. Try first if that is sufficient to make the listener start. If not, we will have to modify the configuration slightly.



Author

Commented:
Hi,

I would request you to dig it further if possible.
I tried it again...installed 11.2 on my laptop and both db and listener were up and running fine before I restarted my laptop. But after restart listener is not coming up...
See the error here.
=========

After installation.....

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                10-NOV-2011 22:27:27
Uptime                    0 days 0 hr. 17 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   e:\app\Sanjay\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         e:\app\sanjay\diag\tnslsnr\Sanjay-Lap\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "Sanjay2XDB" has 1 instance(s).
  Instance "sanjay2", status READY, has 1 handler(s) for this service...
Service "sanjay2" has 1 instance(s).
  Instance "sanjay2", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
LSNRCTL>
LSNRCTL> exit

C:\Users\Sanjay>
C:\Users\Sanjay>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 10 22:45:14 2011

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


Connected to:
Personal Oracle Database 11g Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

=======
C:\Users\Sanjay>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 10 22:54:45 2011

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


Connected to:
Personal Oracle Database 11g Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL>
SQL>
SQL> exit
Disconnected from Personal Oracle Database 11g Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options




=========
After laptop reboot.
=========

C:\Users\Sanjay>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 10-NOV-2011 22:54:53

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   32-bit Windows Error: 61: Unknown error

C:\Users\Sanjay>




SQL> select name from v$database ;

NAME
---------
SANJAY2

SQL>
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
From a command prompt:
lsnrctl start

Commented:
could you show this file?

e:\app\Sanjay\product\11.2.0\dbhome_1\network\admin\listener.ora

Author

Commented:
Hi,

Please find the same.

==========

# listener.ora Network Configuration File: e:\app\Sanjay\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

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

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = e:\app\Sanjay



Regards,
Sanjay
Commented:
find out the hostname of your laptop:
ipconfig /all

edit e:\app\Sanjay\product\11.2.0\dbhome_1\network\admin\listener.ora
change the line      
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

into      
(ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))

where HOSTNAME is your laptop's hostname

Author

Commented:

Hi,

Just for your information.
Issue got resolved on its own. I did nothing.
I just checked the listener before implementing the suggestion provided by you and for my surprise it was up and running fine. I tried to restart the laptop again and still it is working fine.

I am not sure, if again it will go down or not.
======

# listener.ora Network Configuration File: e:\app\Sanjay\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

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

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = e:\app\Sanjay

==========
Listener is up and running fine.
==========

C:\Users\Sanjay>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 13-NOV-2011 19:54:13

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                13-NOV-2011 19:51:47
Uptime                    0 days 0 hr. 2 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   e:\app\Sanjay\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         e:\app\sanjay\diag\tnslsnr\Sanjay-Lap\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "Sanjay2XDB" has 1 instance(s).
  Instance "sanjay2", status READY, has 1 handler(s) for this service...
Service "sanjay2" has 1 instance(s).
  Instance "sanjay2", status READY, has 1 handler(s) for this service...
The command completed successfully
=====

Thanks a lot.

Regards,
Sanjay

Author

Commented:
How ever I am still getting the same problem on my another laptop i.e. 64 bit.

C:\Users\Sanjay.Kumar>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.1.0.7.0 - Production on 13-NOV-2011 21:39:56

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 61: Unknown error

C:\Users\Sanjay.Kumar>





Commented:
This may depend on whether you have the laptop's ethernet adapter connected or not.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>TNS-12541: TNS:no listener


re: http:#a37117826

What happens when you try and start the listener?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
>>where HOSTNAME is your laptop's hostname

localhost is fine for stand-alone systems.  It is actually preferred.

>>This may depend on whether you have the laptop's ethernet adapter connected or not.

It shouldn't.
Commented:
Also did you allow TNSLSNR.EXE to bypass the windows firewall and act as a server?