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

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