Fix Ora-12514:tns: Listener currently does not recognize service requested in connection descriptor

The speed is too slow click on the optimization, appeared ORA-12514, search the solution record, the first basic service solved.

Transferred from: http://apps.hi.baidu.com/share/detail/19115407

PS: I do not know, everyone has not like me to toss, I always like to close not always use the service, etc., and then manually start, in addition to frequent replacement of IP address

System environment: Windows 2003 Ent R2 CHS 32bit,oracle 11.2.0.1.0 R2

The following actions are run by default on the server where the Oralce database is installed.

1) Ensure that Oracle basic services are started

Oracledbconsoleorcl

Oracleoradb11g_home1tnslistener

Oracleserviceorcl

2) Open the command line and log in to Oracle using the Sqlplus nokia_user/[email PROTECTED]/ORCL command, which appears as follows:

C:\Documents and Settings\administrator>sqlplus Nokia_user/[email PROTECTED]/ORCL

Sql*plus:release 11.2.0.1.0 Production on Friday December 23 10:37:522011

Copyright (c) 1982, Oracle. All rightsreserved.

ERROR:
Ora-12541:tns: No listening program

In Ora-12541:tns: No listener error, regardless of whether the local login is prompted ora-12541:tns: No listener Error

3) on the command line, perform the Lsnrctl status

C:\Documents and Settings\administrator>lsnrctl status

Lsnrctl for 32-bit windows:version 11.2.0.1.0-production on23-12 Month-2011 09:27:35

Copyright (c) 1991, Oracle. All rightsreserved.

Connecting to (Description= (address= (PROTOCOL=IPC) (key=extproc1521))
The STATUS of LISTENER
------------------------
Alias LISTENER
Version Tnslsnr for 32-bit windows:version 11.2.0.1.0-production
Start date 2 March-December-2011 09:17:19
Uptime 0 days 0 hours 10 minutes 16 seconds
Trace level off
Security On:local OS Authentication
SNMP OFF
Listener parameter File C:\Program_Files\Oracle\DataBase_11G_R2\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener log File C:\program_files\oracle\database_11g_r2\diag\tnslsnr\dbserver-7ec4a9\listener\alert\log.xml
Listener Endpoint Summary ...
(Description= (address= (PROTOCOL=IPC) (PIPENAME=\\.\PIPE\EXTPROC1521IPC)))
(Description= (address= (protocol=tcp) (host=127.0.0.1) (port=1521)))
Service Summary:
The service "Clrextproc" consists of 1 instances.
The instance "Clrextproc", State UNKNOWN, contains 1 handlers for this service ...
The service "ORCL" consists of 1 instances.
Instance "ORCL", state ready, contains 1 handlers for this service ...
The service "Orclxdb" consists of 1 instances.
Instance "ORCL", state ready, contains 1 handlers for this service ...
Command execution succeeded

Here you can see the location of the listener parameter file

4) Modify the listener parameter file

When first opened, the contents are as follows

# Listener.ora Network Configuration File:c:\program_files\oracle\database_11g_r2\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 =c:\program_files\oracle\database_11g_r2\product\11.2.0\dbhome_1)
(program = Extproc)
(Envs = "Extproc_dlls=only:c:\program_files\oracle\database_11g_r2\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 = C:\Program_Files\Oracle\DataBase_11G_R2

Modified to:

# Listener.ora Network Configuration File:c:\program_files\oracle\database_11g_r2\product\11.2.0\dbhome_1\network\ Admin\listener.ora
# Generated by Oracle configuration tools.

Sid_list_listener =
(Sid_list =
(sid_desc=
(Sid_name =Orcl
(Oracle_home =c:\program_files\oracle\database_11g_r2\product\11.2.0\dbhome_1)
# (program = Extproc)
(Envs = "Extproc_dlls=only:c:\program_files\oracle\database_11g_r2\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)

LISTENER =
(Description_list =
(description=
(ADDRESS = (PROTOCOL = IPC) (KEY = ORCL))
(ADDRESS = (PROTOCOL = TCP) (HOST =202.206.0.233) (PORT = 1521))
)
)

Adr_base_listener = C:\Program_Files\Oracle\DataBase_11G_R2

The value of Sid_name,key is modified to the name of the Oracle instance to be accessed, and the HOST addresses the address used by the Oracle server

5) Restart Listener, execute Lsnrctl stop,lsnrctl start in the command line in turn

C:\Documents and Settings\administrator>lsnrctl stop

Lsnrctl for 32-bit windows:version 11.2.0.1.0-production on23-12 Month-2011 09:46:17

Copyright (c) 1991, Oracle. All rightsreserved.

Connecting to (Description= (address= (PROTOCOL=IPC) (KEY=ORCL))
Tns-12541:tns: No listening program
TNS-12560:TNS: Protocol Adapter error
TNS-00511: No listening program
32-bit Windows error:2: Nosuch file or directory
Connecting to (Description= (address= (protocol=tcp) (host=202.206.0.233) (port=1521))
Command execution succeeded

C:\Documents and Settings\administrator>lsnrctl start

Lsnrctl for 32-bit windows:version 11.2.0.1.0-production on23-12 Month-2011 09:46:31

Copyright (c) 1991, Oracle. All rightsreserved.

Start Tnslsnr: Please wait ...

Tnslsnr for 32-bit windows:version 11.2.0.1.0-production
System parameter file is C:\Program_Files\Oracle\DataBase_11G_R2\product\11.2.0\dbhome_1\network\admin\listener.ora
Log information written to C:\program_files\oracle\database_11g_r2\diag\tnslsnr\dbserver-7ec4a9\listener\alert\log.xml
Monitoring: (Description= (Address= (PROTOCOL=IPC) (PIPENAME=\\.\PIPE\ORCLIPC)))
Monitoring: (Description= (Address= (protocol=tcp) (host=202.206.0.233) (port=1521)))

Connecting to (Description= (address= (PROTOCOL=IPC) (KEY=ORCL))
The STATUS of LISTENER
------------------------
Alias LISTENER
Version Tnslsnr for 32-bit windows:version 11.2.0.1.0-production
Start date 2 March-December-2011 09:46:34
Uptime 0 days 0 hours 0 minutes 3 seconds
Trace level off
Security On:local OS Authentication
SNMP OFF
Listener parameter File C:\Program_Files\Oracle\DataBase_11G_R2\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener log File C:\program_files\oracle\database_11g_r2\diag\tnslsnr\dbserver-7ec4a9\listener\alert\log.xml
Listener Endpoint Summary ...
(Description= (address= (PROTOCOL=IPC) (PIPENAME=\\.\PIPE\ORCLIPC)))
(Description= (address= (protocol=tcp) (host=202.206.0.233) (port=1521)))
Service Summary:
The service "ORCL" consists of 1 instances.
The instance "ORCL", State UNKNOWN, contains 1 handlers for this service ...
Command execution succeeded

6) View status Lsnrctl start

C:\Documents and Settings\administrator>lsnrctl start

Lsnrctl for 32-bit windows:version 11.2.0.1.0-production on23-12 Month-2011 09:46:31

Copyright (c) 1991, Oracle. All rightsreserved.

Start Tnslsnr: Please wait ...

Tnslsnr for 32-bit windows:version 11.2.0.1.0-production
System parameter file is C:\Program_Files\Oracle\DataBase_11G_R2\product\11.2.0\dbhome_1\network\admin\listener.ora
Log information written to C:\program_files\oracle\database_11g_r2\diag\tnslsnr\dbserver-7ec4a9\listener\alert\log.xml
Monitoring: (Description= (Address= (PROTOCOL=IPC) (PIPENAME=\\.\PIPE\ORCLIPC)))
Monitoring: (Description= (Address= (protocol=tcp) (host=202.206.0.233) (port=1521)))

Connecting to (Description= (address= (PROTOCOL=IPC) (KEY=ORCL))
The STATUS of LISTENER
------------------------
Alias LISTENER
Version Tnslsnr for 32-bit windows:version 11.2.0.1.0-production
Start date 2 March-December-2011 09:46:34
Uptime 0 days 0 hours 0 minutes 3 seconds
Trace level off
Security On:local OS Authentication
SNMP OFF
Listener parameter File C:\Program_Files\Oracle\DataBase_11G_R2\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener log File C:\program_files\oracle\database_11g_r2\diag\tnslsnr\dbserver-7ec4a9\listener\alert\log.xml
Listener Endpoint Summary ...
(Description= (address= (PROTOCOL=IPC) (PIPENAME=\\.\PIPE\ORCLIPC)))
(Description= (address= (protocol=tcp) (host=202.206.0.233) (port=1521)))
Service Summary:
The service "ORCL" consists of 1 instances.
The instance "ORCL", State UNKNOWN, contains 1 handlers for this service ...
Command execution succeeded

7) Log in again Sqlplus nokia_user/[email PROTECTED]/ORCL

C:\Documents and Settings\administrator>sqlplusnokia_user/[email PROTECTED]/ORCL

Sql*plus:release 11.2.0.1.0 Production on Friday December 23 11:06:282011

Copyright (c) 1982, Oracle.

All rightsreserved.


Connect to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-production
With the partitioning, OLAP, Data Mining and Real applicationtesting options

Sql>

The above information indicates that the login was successful

Fix Ora-12514:tns: Listener currently does not recognize the requested service in the connection descriptor