ORA-12505:USE_SID_AS_SERVICE_LISTENER=on.

-

ORA-12505: TNS: LISTENER DOES NOT CURRENTLY KNOW OF SID GIVEN IN CONNECT DESCRIPTOR TIPS 


This is new issue introduce in 12c versions 
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.

dont forget to comment !!

cheers!

Comments

Post a Comment

Popular posts from this blog

Query for Tablespace usage with Autoextend

-
                                         Monitoring Tablespace Usage in Oracle I often see questions in Oracle newsgroups and forums about monitoring space in tablespaces, and many of those questions are regarding Enterprise Manager 12c and the alerts it generates reporting the percentage of used space. Of course how you monitor free space depends on whether or not the datafiles are autoextensible. Let's look at several ways to monitor free and used space, starting with methods used in older releases. In versions prior to Oracle 10 DBA_TABLESPACES, DBA_DATA_FILES and DBA_FREE_SPACE were used to generate such reports. One script, from MOS, uses these three views to compute the used space percentage: SELECT SUM(s.used_gb)/SUM(f.total_gb)*100 FROM dba_tablespaces t, ( SELECT tablespace_name, SUM(NVL(bytes,0))/(1024*1024*1024) total_gb FROM dba_data_files ...
Read more

How to configure multiple Oracle listeners

-
How to configure multiple Oracle listeners It happened to me few times during the stress tests that Oracle listener became a bottleneck and not be able to handle the required workload. It was resolved by creating  multiple  listeners which appeared to be a quick solution.  Here is a short step-by-step procedure to configure multiple Oracle listeners  on Solaris for standalone Oracle 9i and 10g environment. 1)  First of all, add additional NIC and cables. They can be on separate subnetwork or the same. In the later, make sure to set static route if needed.    2) Assume that we are going to configure two listeners,   LISTENER and LISTENER2 Modify listener.ora and tnsnames.ora as following: Here is a sample of   listener.ora  LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.3)(PORT = 1521))     ) ...
Read more

sheel script - automatic tablespace addition in oracle 11g

-
      sheel script - automatic tablespace addition in oracle 11g #!/bin/bash a=`sqlplus -s / as sysdba <<END set pagesize 0 feedback off verify off heading off echo off; select tablespace_name from dba_segments group by tablespace_name having SUM(BYTES)/1024/1024>700; END` f=${#a} c=0 for file in $a ; do eval "var$c=$file"; c=$((c+1)); done echo $var0 array1=$(df -kh | grep '/u0*'|awk '{ print $6}') array2=$(df -kh | grep '/u0*'|awk '{ print $5}') size=${#array1} size1=${#array2} if ["$size" -eq 4] && ["$size1" -eq 3] && ["$array2" -gt 85%] && ["$f" -eq 1] then d=${array1[0]:0:4} sqlplus /nolog <<END connect /as sysdba alter tablespace $a add datafile '$d/$a01.dbf' size 10m autoextend on maxsize 28G; END` fi if ["$size" -eq 9] && ["$size1" -eq 3] && ["$array2 -gt...
Read more