oracle11g Ora-12514:tns: The listener does not currently recognize the requested service in the connection descriptor

Source: Internet
Author: User
Tags snmp

1,TNSConnection Error

My colleague told me to connect to the Oracle database error ORA-12514:TNS: The listener currently does not recognize the service requested in the connection descriptor as follows:


2, view localtnsping

To view the local TNS configuration:

WXX =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.121.58) (PORT = 1521))

(Connect_data =

(SERVER = dedicated)

(service_name = ORCL)

)

)

Check the local tnsping is OK

C:\users\administrator>tnsping WXX

TNS Ping Utility for 32-bit windows:version 11.2.0.1.0-production on 2 June-October-2016 11:31:04

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

Used parameter file:

D:\app\Administrator\product\11.2.0\client_2\network\admin\sqlnet.ora

Used the TNSNames adapter to resolve aliases

Attempt to connect (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.121.58) (PORT = 1521)) (Connect_data = (SERVER = dedicated) (service_name = ORCL)))

OK (30 milliseconds)

C:\users\administrator>

The local tnsping appears to be normal for the time being and is OK.

3, view network Connections

Telnet 192.168.121.58 1521, also can go in, prove the network OK

The Oracle server's iptables is also off

Article source address: http://blog.csdn.net/mchdba/article/details/52949382, Bo master mchdba (Douglas Fir) declined reprint.

4, view the server'sLsnrctlService

Go to the server to see the Lsnrctl status:

[Email protected] admin]$ lsnrctl status

Lsnrctl for linux:version 11.2.0.1.0-production on 26-oct-2016 11:33:32

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

Connecting to (Description= (address= (PROTOCOL=IPC) (key=extproc1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version Tnslsnr for Linux:version 11.2.0.1.0-production

Start Date 26-oct-2016 11:09:33

Uptime 0 days 0 hr. Min. sec

Trace level off

Security on:local OS Authentication

SNMP OFF

Listener Parameter File/usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File/usr/oracle/app/diag/tnslsnr/iz23vluqpmmz/listener/alert/log.xml

Listening Endpoints Summary ...

(Description= (address= (PROTOCOL=IPC) (key=extproc1521)))

(Description= (address= (protocol=tcp) (host=192.168.121.58) (port=1521)))

The listener supports no services

The command completed successfully

[Email protected] admin]$

See No Services , which means Lsnrctl The listening service starts up without any Oracle instance, so the question came, no Oracle instance, the client passes Lsnrctl If you listen, you cannot connect to the instance information. So here we need to add the instance information, add the instance information, just inside the listener.ora config file.

5, inListener.oraAdd instance information inside

Perfect Listener.ora configuration:

[email protected] admin]$ CP Listener.ora Z_listener.ora.bak

[Email protected] admin]$ vim Listener.ora

[Email protected] admin]$

[Email protected] admin]$ more Listener.ora

# Listener.ora Network Configuration File:/usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(Description_list =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.121.58) (PORT = 1521))

)

# Below is the new instance information added

Sid_list_listener =

(Sid_list =

(Sid_desc =

(Global_dbname = ORCL)

(Sid_name = ORCL)

)

)

Adr_base_listener =/usr/oracle/app

[Email protected] admin]$

After setting up, restart the Monitoring Lsnrctl service, you can see that there will be a instance service started up:

[[email protected] admin]$ Lsnrctl stop

Lsnrctl for linux:version 11.2.0.1.0-production on 26-oct-2016 11:38:29

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

Connecting to (Description= (address= (PROTOCOL=IPC) (key=extproc1521)))

The command completed successfully

[[email protected] admin]$ lsnrctl start

Lsnrctl for linux:version 11.2.0.1.0-production on 26-oct-2016 11:38:33

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

Starting/usr/oracle/app/product/11.2.0/dbhome_1/bin/tnslsnr:please wait ...

Tnslsnr for Linux:version 11.2.0.1.0-production

System parameter File Is/usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora

Log messages Written To/usr/oracle/app/diag/tnslsnr/iz23vluqpmmz/listener/alert/log.xml

Listening on: (Description= (Address= (PROTOCOL=IPC) (key=extproc1521)))

Listening on: (Description= (Address= (protocol=tcp) (host=192.168.75.58) (port=1521)))

Connecting to (Description= (address= (PROTOCOL=IPC) (key=extproc1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version Tnslsnr for Linux:version 11.2.0.1.0-production

Start Date 26-oct-2016 11:38:33

Uptime 0 days 0 hr. 1 min. 3 sec

Trace level off

Security on:local OS Authentication

SNMP OFF

Listener Parameter File/usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File/usr/oracle/app/diag/tnslsnr/iz23vluqpmmz/listener/alert/log.xml

Listening Endpoints Summary ...

(Description= (address= (PROTOCOL=IPC) (key=extproc1521)))

(Description= (address= (protocol=tcp) (host=192.168.75.58) (port=1521)))

Services Summary ...

Service "ORCL" has 1 instance (s).

Instance "ORCL", status UNKNOWN, have 1 handler (s) for the This service ...

The command completed successfully

[Email protected] admin]$

OK, ask a colleague, colleague said can connect, problem solve.

oracle11g Ora-12514:tns: The listener does not currently recognize the requested service in the connection descriptor

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.