oracle tutorial webinars

ORA-12505: TNS: Listener does not currently know of SID given in connect descriptor tips 

A user will often see error ORA-12505 in cases where the listener receives a request to establish a database connection or some other service. The error may be caused by one of the following reasons:

  1. The connect descriptor received by the listened specifies a SID for an instance that has not been dynamically registered with the listener.
  2. The connect descriptor received by the listened specifies a SID for an instance that has not been statically configured for the listener.

Oracle states that this could possibly be a temporary condition, such as after the listener has started but before the database instance has been registered with the listener.

There are several methods of correcting error ORA-12505. Your first action to resolve this issue should be to wait a few seconds, then try to re-connect to the database again. If this attempt is unsuccessful, try checking to see which instances are known by the listener. To do this, execute: lsnrctl services <listener name>.

You can also check the status of the listener. If you see the message “listener support no service,” this means that your listener is not registered with the database. If this message appears, log in to sysdba by typing “/as sysdba.” Then alter system register, stop and start the listener. Typing in “lsnrctl status” should allow the listener to run. Re-test to make sure your listener has now been successfully connected to the database.

The SID parameter in the connect descriptor should specify an instance that is known by the listener. You should also check for an event in the listener.log file.

In an Oracle 12c Container Database environment, you may see this error because you failed to specify a service name to connect to the database. To resolve this problem, use the listener.ora parameter USE_SID_AS_SERVICE_listener_name. This parameter will tell the listener to use the SID in the connect descriptor as a service name and allow you to connect the client to this particular database. The default value for the parameter is set to “off” and must be explicitly set to “on” to be used. In order to set it to “on” mode, follow the syntax:  follow the syntax: USE_SID_AS_SERVICE_LISTENER=on.

As with any error, make sure to double check your spelling and syntax. For example, the problem may be a matter of a missing character in your jdbc string.