Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> The listener supports no services, Tried everything, can't make oracle connect
Nigel Dams
post Jan 6 2015, 10:26 AM
Post #1


Advanced Member
***

Group: Members
Posts: 36
Joined: 22-September 14
Member No.: 49,984



Hi all ... this is baffling. I'm running 11g on a virtual windows 2008 server, and it's a clone of a working system. Whatever I try I get "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor" when I try to connect via a listener (it works fine if I connect while avoiding the listener).

The weird thing is that I had this same problem on the original machine before I cloned this, and fixed it. But I can't remember what I did to fix it, it might have been putting a SID list in the listener.ora file, it might have been changing the order of elements in the PATH variable, it might have been eye of newt and toe of frog for all I know, it's a baffling and bewildering maze of permutations of things that I've tried.

I cloned the machine, and changed all the machine-specific stuff I could think of, and which has been suggsted in various postings here and elsewhere. Specifically, I changed the hostname in tnsnames.ora and in listener.ora, both in the dbhome and in the client paths.

This is the output from v$version:

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

This is listener.ora:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = correct ip address changed after cloning)(PORT = 1521))
)
)


ADR_BASE_LISTENER = D:\oracle

tnsnames.ora:


CUTLAS2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = correct ip address changed after cloning)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CUTLAS2)
)
)


Here is the output from lsnrctl:

C:\Users\Administrator>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 06-JAN-2015 15:00:52

Copyright © 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.50.70)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 06-JAN-2015 14:19:58
Uptime 0 days 0 hr. 40 min. 54 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\oracle\diag\tnslsnr\test-cdb\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.50.70)(PORT=1521)))
The listener supports no services
The command completed successfully


These are the last few lines of 'log.xml' for tnslsnr:

<msg time='2015-01-06T14:47:25.343+00:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='TEST-CDB'
host_addr='fe80::8d4a:b664:34b9:4a16%11'>
<txt>06-JAN-2015 14:47:25 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CUTLAS2)(CID=(PROGRAM=D:\oracle\product\11.2.0\dbhome_1\bin\sqlplus.exe)(HOST=TEST-CDB)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.50.70)(PORT=49803)) * establish * CUTLAS2 * 12514
</txt>
</msg>
<msg time='2015-01-06T14:47:25.343+00:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='TEST-CDB'
host_addr='fe80::8d4a:b664:34b9:4a16%11'>
<txt>TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
</txt>
</msg>
<msg time='2015-01-06T15:00:52.484+00:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='TEST-CDB'
host_addr='fe80::8d4a:b664:34b9:4a16%11'>
<txt>06-JAN-2015 15:00:52 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Administrator))(COMMAND=status)(ARGUME
NTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * status * 0
</txt>
</msg>



Note above how it says "host=test-cdb" which is correct, that's the alias for the ip address, but I don't know where it got that from, as I've hard coded every ip address I could think of, so that would be nice to know.

I've tried issuing "alter system register" then bouncing the database, I've stopped and restarted the databases and the listener numerous times in different orders, I've tried waiting for the instance to auto-register ....

I can't think of anything else that I needed to change, apart from what you see here, everything else is a literal clone of a system that works just fine.

What have I missed?
Go to the top of the page
 
+Quote Post
burleson
post Jan 6 2015, 11:21 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,566
Joined: 26-January 04
Member No.: 13



Hi Nigel,

>> it's a baffling and bewildering maze of permutations of things that I've tried.

Ha! Yes, I have been there!

*********************************************

>> it works fine if I connect while avoiding the listener

That is an important clue!

Does a "lsnrctl stat" show the service?


First, follow these diagnostic steps and let's see where it fails:

http://www.dba-oracle.com/t_troubleshootin...vity_errors.htm




--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Nigel Dams
post Jan 7 2015, 05:03 AM
Post #3


Advanced Member
***

Group: Members
Posts: 36
Joined: 22-September 14
Member No.: 49,984



QUOTE (burleson @ Jan 6 2015, 05:21 PM) *
Hi Nigel,

>> it's a baffling and bewildering maze of permutations of things that I've tried.

Ha! Yes, I have been there!

*********************************************

>> it works fine if I connect while avoiding the listener

That is an important clue!

Does a "lsnrctl stat" show the service?


First, follow these diagnostic steps and let's see where it fails:

http://www.dba-oracle.com/t_troubleshootin...vity_errors.htm



Hi and thanks for replying. You can see from my first posting that lsnrcrl stat doesn't know about the service.

I've tried your diagnostic steps (had tried them before posting actually) and the results are/were:

I can ping the machine using both ip address and dns name
telnet doesn't seem to work on windows
tnsping returns 'ok' as below

TNS Ping Utility for 64-bit Windows: Version 11.2.0.3.0 - Production on 07-JAN-2015 09:

Copyright © 1997, 2011, Oracle. All rights reserved.

Used parameter files:
c:\oracle\64\product\11.2.0\client_1\network\admin\sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP
)
OK (110 msec)

steps 5 and 6 both give error 12514
step 7 gives error 02019
Go to the top of the page
 
+Quote Post
burleson
post Jan 7 2015, 10:15 AM
Post #4


Advanced Member
***

Group: Members
Posts: 13,566
Joined: 26-January 04
Member No.: 13



Hi Nigel.

>> Windows does not have

ARRGH! I hate Windoze . . .

*******************************************

>> steps 5 and 6 both give error 12514

OK, so we have to register the service with the listener and bounce the listener process!

I have some notes here:

http://dba-oracle.com/t_ora_12514_tns_list...e_requested.htm

If you are positive that the IP address is right, try changing it to your computer name:

CODE
LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = dnsname)(PORT = 1521))
     )
   )


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Nigel Dams
post Jan 7 2015, 10:48 AM
Post #5


Advanced Member
***

Group: Members
Posts: 36
Joined: 22-September 14
Member No.: 49,984



Hi Don .... I'm a little foncused by your recommendation to register the service and bounce the listener. I mentioned in my first post that I'd issued "alter system register" and bounced the databases and the listener various times in various orders. Do you mean something different by "register"?

I have followed the link you included, thank you, but there's nothing I haven't tried in there. There's no way the problem is down to virus filters or updates, because this is a fresh clone made by me of a virtual machine on which everything is running properly.

I've altered the host name in the listener file, and went to stop and restart the listener. This is what happened:

C:\Users\Administrator>lsnrctl

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 07-JAN-2015 15:40:40

Copyright © 1991, 2010, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test-cdb)(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> start
Starting tnslsnr: please wait...

Service OracleOraDb11g_home1TNSListener already running.
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test-cdb)(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>

First it says "no listener", then it says "listener already running" ...... starting to feel a bit like Alice down the rabbit hole here. Maybe I've unwittingly invented the Quantum Computer and this is all happening in superposition with the original cloned machine. Btw, I've checked and they do have different mac addresses ....
Go to the top of the page
 
+Quote Post
burleson
post Jan 7 2015, 11:50 AM
Post #6


Advanced Member
***

Group: Members
Posts: 13,566
Joined: 26-January 04
Member No.: 13



Hi Nigel,

Because this is Windoze, you may have to go into the "services" section of control panel and right-mouse on the listener service.

My guess is that if you can get the listener process running with the service, you will be able to connect.

You can always open an SR on MOSC:

http://support.oracle.com






--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Nigel Dams
post Jan 8 2015, 04:06 AM
Post #7


Advanced Member
***

Group: Members
Posts: 36
Joined: 22-September 14
Member No.: 49,984



Well ..... I changed the ip address to the dns name in listener.ora, and kept getting the same nonsense I'd reported in my last post. Then rebooted the machine and suddenly it's working ...

Since then I've tried it with ip-address and dns name alternately a few times and only the dns name works. I'd love to know why that is.

Thanks for your help
Go to the top of the page
 
+Quote Post
burleson
post Jan 8 2015, 07:27 AM
Post #8


Advanced Member
***

Group: Members
Posts: 13,566
Joined: 26-January 04
Member No.: 13



Hi Nigel,

>> Then rebooted the machine and suddenly it's working ...


I'll bet that was because the listener service re-started . . .

Glad that you got it working!




--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Nigel Dams
post Jan 9 2015, 08:54 AM
Post #9


Advanced Member
***

Group: Members
Posts: 36
Joined: 22-September 14
Member No.: 49,984



But then why, as seen in my Jan 7th 04:48 post, would a stop and restart of the listener not have had the same effect? Maybe the answer is Windoze works in mysterious ways .... but I think I'm gonna stop capitalizing that word, from now on it's windoze with a small w___
Go to the top of the page
 
+Quote Post
jcdba
post Apr 20 2017, 10:42 AM
Post #10


Newbie
*

Group: Members
Posts: 1
Joined: 20-April 17
Member No.: 51,919



execute in linux:

netca

then in GUI select:

->listener configuration
->Reconfigure... Follow the steps

then execute in terminal:

lsnrctl status

Result:

Services Summary...
Service "orcl.oracle" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.oracle" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdborcl.oracle" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
2 User(s) are reading this topic (2 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 17th October 2017 - 10:15 AM