asked on

can not connect to database: TNS-12538: TNS:no such protocol adapter

Hello,

I'm trying to connect to my database service name : orcl in command line, but I got an error:

Q1
=====================================================
C:\>sqlplus system/dba123@orcl

Message 133 not found; product=SQLPlus; facility=SP2
: Release 9.0.1.4.0 - Production on Thu Mar 16 16:02:49 2006

(c) Copyright 2001 Oracle Corporation. All rights reserved.

ERROR:
ORA-12538: TNS:no such protocol adapter
======================================================

If I do tnsping orcl:
the error is:

Q2
======================================================
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =mypcname.mydomanname)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = o
rcl)))
TNS-12538: TNS:no such protocol adapter
======================================================

My tnsnames.ora file is :
================================================
# tnsnames.ora Network Configuration File: E:\oracle\product\10.1.0\Db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

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

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

So there is a service name called : orcl

My listener.ora file is :

================================================
# listener.ora Network Configuration File: E:\oracle\product\10.1.0\Db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oracle\product\10.1.0\Db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = mypcanme.mydomainname)(PORT = 1521))
)
)
)
=======================================================

Q3
Before set up oracle_home variable, I can log into sqlplus by using in command line.

user name: sys as sysdba ; password : dba123, But after I set oracle_home variable: I got an error when I tried log into sqlplus:(I still can log into the sqlplus from sqlplus interface though)

==========================================
ERROR:
ORA-12538: TNS:no such protocol adapter
=========================================

My pc os is windows xp and has oracl10g and htmldb2.0 installed.

Thanks so much for replying my above three questions,
Emily

ceecil

Your Listener.ora should look like this. You have failed to include a listener for ocrl in the SID_LIST_LISTENER. see below.

Also check that the brackets are correct.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
(ADDRESS = (PROTOCOL = TCP)(HOST = mypcanme.mydomainname)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oracle\product\10.1.0\Db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = ORCL)
(ORACLE_HOME = E:\oracle\product\10.1.0\Db_1)
)
)

Mark Geerlings

Usually with Oracle on Windows, it is not necessary to set an Oracle_home manually. Usually, if you need that, the Oracle installer will do that for you. (On Unix or Linux it is different, there you have to set that value manually.)

This part of your Q3 confuses me: "But after I set oracle_home variable: I got an error when I tried log into sqlplus:(I still can log into the sqlplus from sqlplus interface though)"

So can you log in to the database using SQL*Plus, or not?

lilyyan

ASKER

Hi,

Thanks for your reply.

I don't have to explicitly set / register my database in the listener.ora ,?because the database will autoregister itself to the listener.

when I run lsnrctl status,
========================================================
C:\ >lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 17-MAR-2006 09:09:14

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Produ
ction
Start Date 16-MAR-2006 22:13:07
Uptime 0 days 10 hr. 56 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File E:\oracle\product\10.1.0\Db_1\network\admin\listener.o
ra
Listener Log File E:\oracle\product\10.1.0\Db_1\network\log\listener.log

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mypcname.mydomianname)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= mypcname.mydomianname)(PORT=8080))(Pre
sentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= mypcname.mydomianname)(PORT=2100))(Pre
sentation=FTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
=========================================================

so from above result, "orcl" already registered itself .

Is this right?

Thank you for your reply,
Emily

Mark Geerlings

Yes, it looks like your instance is automatically registered with the listener.

Do you have any firewall or spyware software running that may block Oracle connections?

ceecil

When you bring up the Listener, you receive the following errors:

TNS-12538: TNS:no such protocol adapter
Cause: The protocol adapter requested (by way of the "(PROTOCOL=..)"
keyword-value pair in a TNS address) is unknown. If the supplied
address is typographically correct then the protocol adaptor is
not installed.
Action: Install the protocol adapter or correct typographically error,
is appropriate. Note: if the supplied address was derived from
resolving the service name, check the address in the appropriate
file TNSNAMES.ORA, LISTENER.ORA or TNSNET.ORA).

First what I noticed is that, your listener.ora file has the contents a bit out of the way. It should start with

LISTENER =

The second part with be
SID_LIST_LISTENER
==============================

Your HOST= (... ) is not correct in your listener.ora

it should be
HOST =(mypcname.mydomainname)

in the listener. ora it is spelled wrong i.e mypcanme instead of mypcname.
Also, make sure. that it is (mypcname.domainname) in all places in the tnsnames.ora and the listener.ora file where ever it exists now. That should work and remove the errora ssuming that you ahave not typed it out for this message.

===============================
if that does not work, just make host = (ip address)
at dos, typed ipconfig to get the ip address.

lilyyan

ASKER

Hi,

Thanks for all reply.

to markgeer:

----------------------------------------------------------------------------------------------------------
<<This part of your Q3 confuses me: "But after I set oracle_home variable: I got an error when I tried log into sqlplus:(I still can log into the sqlplus from sqlplus interface though)"

So can you log in to the database using SQL*Plus, or not?

>>

No, I can not log into the sqlplus from command. the error is : ORA-12538: TNS:no such protocol adapter. however I still can log into sqlplus through the oracle sqlplus interface.

--------------------------------------------------------------------------------------------------------

to ceecil:

there is some typo in my listener.ora file. sorry about this

<< TNS-12538: TNS:no such protocol adapter
Cause: The protocol adapter requested (by way of the "(PROTOCOL=..)"
keyword-value pair in a TNS address) is unknown. If the supplied
address is typographically correct then the protocol adaptor is
not installed.
Action: Install the protocol adapter or correct typographically error,
is appropriate. Note: if the supplied address was derived from
resolving the service name, check the address in the appropriate
file TNSNAMES.ORA, LISTENER.ORA or TNSNET.ORA).

>>

I did installed the MS Lookup adapter, but After I installed, When I open the Network Connetcions, I found there are TWO local area connetcions. One of them is called local area connection 2, and it's MS loopback adapter. I got a warning for local area connection 2, it says that my pc does not have a IP address assigned, and its status is : limited or no connectivity.

ceecil

I am not sure what you are doing.
What OS are you using?.
Does your Pc have a network card.?
Does your listener.ora and tnsnames.ora have HOSTname the same without any typos.
Are they consistent at every point in which they are being referred?.

Make sure that all these are correct first.

You should first make sure that your TNSnames.ora and Listener.ora have the following consistently.

HOST= mypcname.domainname . if they are not consistent, then you are wasting your time posting.

Secondly, you must be able to get the ip adress from your pc. Did you do what you were asked to do?. Y or N.

Every pc with a network card must have an ip address. you get this by typing iponfig at the dos prompt.

I am not knowledgeable about MS lookup and how you have set up your OS. I use Win2k server and I have no problems.
if you do not want to specify the ip address for HOSt, then you must specify the computer name given at the type of setting up the OS. And that has to be consistent in both tnsnames and listener.ora.

First do all that and then logout and login again.
Then start Oracle.

Mark Geerlings

This is still confusing: "I can not log into the sqlplus from command. the error is : ORA-12538: TNS:no such protocol adapter. however I still can log into sqlplus through the oracle sqlplus interface."

Does this mean that when you try to run SQL*Plus from a command prompt, you get the error, but if you try to run SQL*Plus through the Windows Start button menus it works? If that is true, I would guess that you have two different Oracle_homes (Oracle software installations) on your PC and one has SQL*Net configured correctly, and the other one does not.

Please confirm whether you have just one Oracle_home, or multiple ones.

lilyyan

ASKER

Hello all,

to ceecil:
------------------------------------------------------------------------------------------------------------
What OS are you using?.
A. windows xp

Does your Pc have a network card.?
A. yes, my pc is winthin a LAN and IP is assugn by DHCP

Does your listener.ora and tnsnames.ora have HOSTname the same without any typos.
A. yes, the two files are generation by installar during installation

Are they consistent at every point in which they are being referred?.
A. same as above

Did you do what you were asked to do?. Y or N.
A. surely i did.
------------------------------------------------------------------------------------------------------------

to markgeer

<<
Does this mean that when you try to run SQL*Plus from a command prompt, you get the error, but if you try to run SQL*Plus through the Windows Start button menus it works?
>>

yes, exactly i got the eror when i tried to use sqlplus from dos command line

<<If that is true, I would guess that you have two different Oracle_homes (Oracle software installations) on your PC and one has SQL*Net configured correctly, and the other one does not.

>>

i have installed oracle10g and HTMLDB in my pc, but i have one oracle home i guess.

SOLUTION

ceecil

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ceecil

With due respect to Markgeer for his contribution and intelligence, the poster has made the last mail the ACCEPTED ANSWER. That is NOT the answer to the problem that was posted and I say this because people searching for answers to a similar problem might be misled.

The solution was plain and simple. The poster had the HOSTnames spelled incorrectly both in the listener.ora and the Tnsnames.ora. That was the reason why she could not connect.

Another thing is that the poster claimed that the contents of the listener.ora and tnsnames.ora posted was generated by the system. That is untrue. from my experience, there will never be syntax errors or spelling errors in a system generated .ora file.

lilyyan

ASKER

<<the poster has made the last mail the ACCEPTED ANSWER.>>

Markgeer's reply is partially related to Q3 in my first posting. Any user read this thread please be aware of this.

<<The poster had the HOSTnames spelled incorrectly both in the listener.ora and the Tnsnames.ora.>>

Yes, I already clarified this my previous posting.

<<That was the reason why she could not connect.>>

No it's not.
The hostname are exactly identical in the listener.ora and tnsnames.ora. When I post the two files here, I typed the host name, unfortunately there was a typo.

<<the poster claimed that the contents of the listener.ora and tnsnames.ora posted was generated by the system>>

Answer as above.