Sunday, September 20, 2020

java.sql.SQLException: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

 You received such a error when you started to connect to your Oracle database via JDBC.

java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
 
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:673)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:715)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:385)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:30)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:564)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:247)
        at com.tds.db.DB.connectToDB(DB.java:118)


This means that the TNS listener is running, but it cannot connect you to the database. It may due to:

  • the database has not been started up,
  • the database has not registered with the listener, e.g. because the database was started before the listener. (When the database starts, it registers itself with a listener if it is already running. If the listener isn't running, the database doesn't register itself, and if the listener starts, it doesn't go looking for databases that might register with it.)
To fix the problem:

1. login as the oracle user
      >$ su - oracle
           enter your oracle user password

2. Execute the below command
      >$ lsnrctl status

     If you see these in your output:
          Services Summary...
          Service "your database service" has 1 instance(s).
          Instance "your database service", status READY, has 1 handler(s) for this service...

      It means that your database is up and running. 

3. Login to sqlplus as sysdba
      >$ sqlplus /NOLOG
      SQL> connect / as sysdba;

4. If your database is not running according to step 2, start the database.
          SQL> STARTUP

5. Register the listener
          SQL> conn system
          SQL> alter system set local_listener = 'ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)' scope = both;
          SQL> alter system register;
          SQL> quit

6. Check to see if things are right.
          $ lsnrctl status

          You should see something like below.
          
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-SEP-2020 17:32:34

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                02-SEP-2020 16:12:28
Uptime                    16 days 1 hr. 20 min. 6 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /12c/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /12c/app/oracle/diag/tnslsnr/oraclelog/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracleserver)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracleserver)(PORT=5500))(Security=(my_wallet_directory=/12c/app/oracle/admin/db/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "your database service" has 1 instance(s).
  Instance "your database service", status READY, has 1 handler(s) for this service...
Service "oracleXDB" has 1 instance(s).
  Instance "your database service", status READY, has 1 handler(s) for this service...
The command completed successfully

-----------------------------------------------------------------------------------------------------------------
Watch the blessing and loving online channel: SupremeMasterTV live




If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book for free here.

No comments:

Post a Comment