This is default featured slide 1 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions..

This is default featured slide 2 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions..

This is default featured slide 3 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions..

This is default featured slide 4 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions..

This is default featured slide 5 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions..

 

Tuesday, March 22, 2011

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Cause:
The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.


Solution:
1. Check your tnsnames.ora as shown in my post on ora-12154.
2. Check if your listener service are on, if not start your lisnter using lsnrctl. More information on this take a look at the same post at ora-12154
3. Check if the service is on using command line lsnrctl services
4. Check your listener.log 

ORA-12154: TNS:could not resolve the connect identifier specified

Cause:
A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.

Solution:
1. Check your tnsnames.ora.
Check your tnsnames.ora if it is configured property the key to check is SID, dba host name or ip address. The syntax are something similar this this:


ORA_TNS =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA-10)
 )
)



2. Check your listener.ora if sid_list_listenner setup is there.
By Default when you install new oracle database the listener.ora would contain only listener configuration section but no sid_list_listenner setup. In many occasion when I have this ora-12154 problem the solution that help me is modifying listener.ora, adding SID_LIST_LISTENER section the syntax should be something similar to the below syntax:





SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/10.1.0/db_1)
      (SID_NAME = ORA-10)
    )
  )



The complete file are as shown below



# listener.ora Network Configuration File: /u01/app/oracle/product/10.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (ORACLE_HOME = /u01/app/oracle/product/10.1.0/db_1)
       (SID_NAME = ORA-10)
     )
   )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

After adding SID_LIST_LISTENER in your listener.ora, restart or reload your listener service. I prefer using command line lnsrctl follow by parameter reload or restart, or you can just type lnsrctl and then enter and type reload or restart. With this command line (lsnrctl) you can also check oracle listener status by enter parameter status. 


3. Check the environmental variables
Check if the environmental variables like ORACLE_HOME, ORACLE_SID, ORACLE_BASE, ORACLE_PATH do exist. The most important environmental variables are the first two variable ORACLE_HOME, ORACLE_SID.

Tools
The very handy tool you can use to check the connection is the command tnsping follow by your service name configured in your tnsnames.ora from the example above is ORA_TNS.