10g TNS 13541 listening error tnsping can but Conn system/manager@mult1.net the wrong error

Oracle 10g:ora-12514:tns:listener does not currently know the service requested in Connect descriptor

Error message:

Ora-12514:tns:listener does not currently know of service requested in Connect descriptor

There are many reasons for the error, if you check the service inside all the services are started, it means that the monitoring service and database instance service active and passive relationship causes.

The problem solved is 2,
Method 1. Is let listener active load service
Reason is to add
(Sid_desc =
(Sid_name = ORCL)
(Oracle_home = C:\oracle\product\10.2.0\db_1)
)
, the Listener service is registered to the Process Monitor (Pmon) when the Lsnrctl start listener is used

Method 2.listener Passive Load service

within the service Stop listener service and database instance services, then restart the listening service first, then start the database instance service.

The reason is that if there is no such content, then by the instance of the Pmon process in the Listener Registration service, for listener, is passive.
This is why the start of the monitoring after the start of the database can be normal connection, the other is not the reason.

dynamically register and Local_listener parameters

Because a test library installs a special service, it automatically sets the value to the Local_listener parameter, which causes the database rebuilt with the original parameter file to be unable to register the listening service automatically.
Other databases of the same host can register the listening service automatically.
During < retry reload Delete LISTENER re-build a new LISTENER etc no effort >

Non-default listener becomes dynamic registration due to the setting of the Local_listener parameter

Change parameters

Sql> alter system set local_listener= ';

The system has changed.

Sql> Show Parameter Lis

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Local_listener string
Recovery_parallelism integer 0
Remote_listener string

Finally solved it.

lsnrctl> status

Service "TEST2" has 1 instance (s).
Instance "TEST2", Status READY, has 1 handler (s) for the This service ...
Service "Test2xdb" has 1 instance (s).
Instance "TEST2", Status READY, has 1 handler (s) for the This service ...

A, the Pmon process 1 minutes to run once, when the monitoring has just started, the service may not be registered, this time is not able to use this service
b, when the Local_listener parameter is modified, the Pmon process is also registered again
C, if the database is shutdown, the Pmon process is stopped, the service is removed from listening, and the database can only be connected using the OS authentication method.



Ora-12514:tns:listener does not currently know of service requested in Connect descriptor

This is a rather disgusting mistake, like epileptic patients, do not know when to commit, good use, eat dinner and then even, on the report no listener up the monitoring, after this is the error. Make a mess, no, restart OK. Do not know where the problem, because it is a test environment, often restart, this error is like a ghost when not. Oracle is so hard to wait for now. Tired of a lot of finally determined to find out the problem, so in the Internet to read stickers countless, and finally found a way to solve the problem according to a certain law, reprinted over

There are a lot of cases about this mistake, I know there are 2 kinds of cases, the following is excerpts from the Internet:

1, recently in the configuration of Oracle 10G streaming replication environment, encountered a problem, shut down the database (shutdown immediate), through the SQL plus connection database: Conn sys/his@orc0 as SYSDBA, the following error occurred:

Ora-12514:tns: The listener is currently unable to recognize the service requested in the connection descriptor

Starting the database by restarting the service, connecting again but successfully logging on, which means that the server cannot be connected when the database is shut down.

The beginning thought is the system environment variable ORACLE_SID configuration problem, because the machine has several instances, after a toss still cannot connect. Later, the information was found:

After oracle9i, the background process Pmon automatically registers the service name defined in the system parameter Service_names in the Listener, service_names defaults to Db_name+domain_name. Listener configuration file Listener.ora You do not have to specify a service name for listening. However, when the database is turned off and the Pmon process is not started, the listener instance name is not automatically registered, so a ORA-12514 error occurs using the Sqlplus sys/his@orc0 as SYSDBA.

If you specify a listener's instance name in the Listener.ora file, you can still connect even if the database is turned off.

Listener.ora

Sid_list_listener =

(Sid_list =

(Sid_desc =

(Sid_name = Plsextproc)

(Oracle_home = G:\oracle\product\10.2.0\db_1)

(program = Extproc)

)

(Sid_desc =

(global_dbname = ORCL) (http://blog.sina.com.cn/s/blog_4abe7f3a0100g7mm.html)-Ora-12514:tns: The listener is not currently aware of the connection descriptor _

(oracle_home = G:\oracle\product\10.2.0\db_1)

(sid_name = ORCL)

)

)

LISTENER =

(Description_list =

(DESCRIPTION =

(address = (PROTOCOL = TCP) (HOST = Zyk) (PORT = 1521))

)

)

The above bold parts for the added content, modified after the restart of the listening service can be.

Attach: By entering set display verbose in Lsnrctl, and then through the command service, the service status is ready to indicate Pmon autoenrollment service name. Unknown, however, indicates that the service is a database service that is manually configured in Listener.ora.

2, may be the database server jumped, in this case, the light restart service is not, restart the machine is useless. can be resolved in the following way.

Open the cmd window, input Sqlplus/as Sysdba;startup, after that is back to normal, execution speed a bit slow, patiently waiting.

This issue may be accompanied by an error:

Oracle Not available
is shared memory realm does not exist
the above is the article I reproduced, tried the first one, successful. should be useful.
Use Sqlplus login times error
Error:ora-12514:tns:listener does not currently know of service requested in Connect
------solution Method--------------------------------------------------------
If the server and client are in the same subnet, do not fill in the IP in the listener's configuration file and fill in the computer name.
------Workaround--------------------------------------------------------
If the same network segment, you can now fill in the computer IP to the corresponding computer name, However, it is required that the computer name in the network segment be unique
------workaround--------------------------------------------------------


About Listener.ora,tnsnames. Problems with Ora and Sqlnet.ora

A recent inspection of the customer's database found a small exception, in the $oracle_home/network/admin directory incredibly only a Tnsnames.ora file, Sqlnet.ora and Listener.ora files are not,
Check environment variable settings and current listener process, no exception, try to log into the database through TNS, all is normal, immediately think of the question is whether this file was mistakenly deleted, The second is that if you restart the database in this case, you will not be able to start listening properly because there is no Listener.ora file. Because it is the customer online production library, so did not rashly try, because from the listening log to see the current connection is still very frequent, on average a minute of about 20 of the connected, relatively busy situation.
Because the customer's database runs on the 10.2.0.4, so the next experiment to find a 10.2.0.5 library to do, the version is relatively close, of course, on this issue can be estimated as long as the 10G version will not be too big difference.
First, the current three files are moved,
wilson-> pwd
/u01/app/oracle/product/10.2.0/db_1/network/admin
wilson-> mkdir Temp
Wilson-> mv./*.ora./temp
Then try to start listening and databases,
Listen to boot success, check process status is normal, then the conclusion is that if there is only one instance of the current machine running, and the port is using the default of 1521, then there is no Listener.ora file, Lsnrctl will try to start the default listener, listening to the current machine 1521 ports. However, the Listener.ora file currently in use can be seen in the Listener.log file,
Log using the Listener.ora file:
Tnslsnr for linux:version 10.2.0.1.0-production on 26-apr-2011 13:07:09

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

System parameter File Is/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages Written To/u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Trace Information Written TO/U01/APP/ORACLE/PRODUCT/10.2.0/DB_1/NETWORK/TRACE/LISTENER.TRC
Trace level is currently 0

There are no logs using the Listener.ora file:
Tnslsnr for linux:version 10.2.0.5.0-production on 21-jul-2011 03:40:57

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

Log messages Written To/u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Trace Information Written TO/U01/APP/ORACLE/PRODUCT/10.2.0/DB_1/NETWORK/TRACE/LISTENER.TRC
Trace level is currently 0
Of course, this is not just a superficial distinction, but the following are the outputs of LSNRCTL status in two cases:
Log using the Listener.ora file:
wilson-> lsnrctl Status

Lsnrctl for linux:version 10.2.0.5.0-production on 21-jul-2011 04:50:46

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

Connecting to (Address= (PROTOCOL=TCP) (host=) (port=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version Tnslsnr for Linux:version 10.2.0.5.0-production
Start Date 21-jul-2011 03:40:57
Uptime 0 days 1 hr. 9 min. sec
Trace level off
Security On:local OS Authentication
SNMP off
Listener Log File/u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary ...
(Description= (address= (protocol=tcp) (Host=wilson.mycorp) (port=1521))
Services Summary ...
Service "ORCL" has 1 instance (s).
Instance "ORCL", Status READY, has 1 handler (s) for the This service ...
Service "Orclxdb" has 1 instance (s).
Instance "ORCL", Status READY, has 1 handler (s) for the This service ...
Service "ORCL_XPT" has 1 instance (s).
Instance "ORCL", Status READY, has 1 handler (s) for the This service ...
The command completed successfully

There are no logs using the Listener.ora file:
wilson-> lsnrctl Status

Lsnrctl for linux:version 10.2.0.5.0-production on 21-jul-2011 04:52:25

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

Connecting to (description= address= (PROTOCOL=IPC) (Key=extproc1))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version Tnslsnr for Linux:version 10.2.0.5.0-production
Start Date 21-jul-2011 04:51:32
Uptime 0 days 0 hr. 0 min. sec
Trace level off
Security On:local OS Authentication
SNMP off
Listener Parameter File/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File/u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary ...
(Description= (address= (PROTOCOL=IPC) (Key=extproc1))
(Description= (address= (protocol=tcp) (Host=wilson.mycorp) (port=1521))
Services Summary ...
Service "Plsextproc" has 1 instance (s).
Instance "Plsextproc", Status UNKNOWN, has 1 handler (s) for the This service ...
Service "ORCL" has 1 instance (s).
Instance "ORCL", Status READY, has 1 handler (s) for the This service ...
Service "Orclxdb" has 1 instance (s).
Instance "ORCL", Status READY, has 1 handler (s) for the This service ...
Service "ORCL_XPT" has 1 instance (s).
Instance "ORCL", Status READY, has 1 handler (s) for the This service ...
The command completed successfully
Visible in two cases, whether to start external monitoring is a difference, that is, if the program used in the Extproc way, then do not configure Listener.ora may have problems, the second is whether to use the difference between the parameters file.
As for the existence of the Sqlnet.ora file, this needless to say, if there is no Sqlnet.ora file, Oracle will use the default Tnsnames,onames,hostname three ways to try to connect,
It's an experiment with three files that don't exist.
Wilson-> Sqlplus/nolog

Sql*plus:release 10.2.0.5.0-production on Thu June 21 05:09:47 2011

Copyright (c) 1982, Oracle. All Rights Reserved.

Sql> Conn Scott/tiger
Connected.
Sql> Conn
ERROR:
Ora-12154:tns:could not resolve the connect identifier specified

Warning:you are no longer connected to ORACLE.
Sql> Conn
Connected.
Sql> Conn
Connected.
(If the port number is 1521, you can omit it)
The following tests have Sqlnet.ora, but only use the TNSNames method,
#NAMES. Directory_path= (HOSTNAME, TNSNAMES, Ezconnect)
NAMES. Directory_path= (TNSNAMES)

Wilson-> Sqlplus/nolog

Sql*plus:release 10.2.0.5.0-production on Thu June 21 05:14:15 2011
、
Copyright (c) 1982, Oracle. All Rights Reserved.

Sql> Conn/as SYSDBA
Connected.
Sql> Conn Scott/tiger
Connected.
Sql> Conn
ERROR:
Ora-12154:tns:could not resolve the connect identifier specified
Warning:you are no longer connected to ORACLE.

It can be seen that there is no hostname way or ezconnect way to cause this simple connection to fail.
Sql> Conn
ERROR:
Ora-12514:tns:listener does not currently know the service requested in Connect
Descriptor

Warning:you are no longer connected to ORACLE.
Sql> Conn
ERROR:
Ora-12514:tns:listener does not currently know the service requested in Connect
Descriptor

As for hostname and ezconnect These two ways, personal feeling in 10g has no big difference, feeling is artificially made in oracle10g a new FEATURE, and the corresponding hostname way has been replaced,
On 10.2.0.5 This version, I could not experiment successfully hostname the direct way connection succeeded.

The following refers to Oracle's official statement,
Oracle 10g Easy Connect naming

Easy Connect naming isn't allowed in large or complex environments that require connect additional. (Large or complex environments employ advanced features, such as connection pooling, external procedure calls, or Heteroge Neous Services.) In these cases, your must use another naming method.

With Oracle 10g, easy Connect naming are automatically configured by default at installation. However, before attempting to "use" this feature, your may want to ensure that Ezconnect is specified the Ctory_path parameter in the Sqlnet.ora file. The Names.directory_path parameter is used to specify the order of naming methods this Oracle Net can use to resolve Conne CT identifiers to connect descriptors. Verification is easy. To verify the Names.directory_path settings are correct, follow these instructions:

Start Oracle Net Manager.

In the Navigator pane, expand > Profile.

From the to the right pane, select naming.

Click the Methods tab. Ensure that ezconnect are listed in the Selected Methods list. If It isn't, then proceed to step 5.

From the Available Methods list, select Ezconnect, then click the Right-arrow button.

From the Selected Methods list, select Ezconnect, then use the Promote button to move the "selection to" the top of the list .

Choose File > Save network Configuration. The Sqlnet.ora file updates with the Names.directory_path parameter listing Ezconnect = (first:names.directory_path Nect, TNSNames).

As can be seen from the above statement, in fact, the hostname connection is already reserved for the previous version of the support, 11GR2WIN32 version, Oracle's NETMGR program can also see this connection method, But there is no way to see this connection in the NETCA program that is used more often.

Finally say Tnsnames.ora, this file actually depends on the current application needs of the connection method, in the TCP/IP environment is basically essential, otherwise you can only through a similar Java connection string form to the library. If you need to use the tnsnames, which is the local service name way to connect to the database, then it is necessary to use the file.