Error ORA-12505 when connecting to Database Oracle 11g SQL Developer

oracle-11g

recently I installed Oracle XE 11g with SQL Developer.

When I first ran it and created a database and a new user, everything worked perfectly – I was able to connect to the database and was able to run queries and scripts.
However, when I restart my laptop (for example, the following day), I am unable to connect to my database using that new user I created, as well as unable to use SYS/SYSTEM accounts.
I get an error with the code ORA-12505 that states:

"An error was encountered performing the requested operation:

Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Vendor code 12505".

Nothing has changed since I restarted my laptop! I have also Googled the error but I am still unable to solve it. I know the error has something to do with my 'tnsnames.ora' file, but the service_name is correct from what I can tell.

My tnsnames.ora file is as follows:

  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Mark-Prescher-Laptop)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
    ) 
    (CONNECT_DATA = 
      (SID = CLRExtProc) 
      (PRESENTATION = RO) 
    ) 
  )

Thank you for any help in advance!

Best Answer

The way you are connecting to the database (connection type "basic") isn't using your tnsnames.ora file at all. It is using a basic JDBC connect string - which is fine. That said, the error indicates that the database "xe" hasn't registered with the listener, meaning that the database itself probably isn't running at all. Check to make sure that your database instance is actually up. Check this document for details: https://docs.oracle.com/cd/E17781_01/admin.112/e18585/toc.htm#XEGSG101

Related Solutions

Check listener status other than 1521

Run:

lsnrctl status LISTENERNAME

... where LISTENERNAME is the name of the listener that is listening on port 1531. You can get this name from your $ORACLE_HOME/network/admin/listener.ora file.

For example:

LISTENER1531 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1531))
      )
    )
  )

LISTENER1531 would be the listener name for the above listener.ora entry.

If you need to add databases to the listener, add an entry to the listener.ora, as follows:

SID_LIST_LISTENER1531 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = YOURDB)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
      (SID_NAME = YOURDB)
    )
  )

Obviously you'll need to add your own database and listener names & change the path to your $ORACLE_HOME.

You can reload the listener config with:

lsnrctl reload LISTENER1531

The above works fine for me with a `tnsnames.ora as follows:

YOURDB =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = node1)(Port = 1531))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = YOURDB)
 )
)

Oracle 11g listener fails with ORA-12514 and ORA-12505 errors

So, with thanks to @YasirArsanukaev for the time he put in, I have found a solution which works, but which I can't really explain.

Riffing on the LOCAL_LISTENER thought, I was reading this other answer where it said:

The database uses the LOCAL_LISTENER parameter to identify the listener it should register with. By default that is null, which according to the documentation is equivalent to hostname:1521.

So I tried to ping my own hostname and couldn't - it looks like some IPv6 problem, receiving a general failure message.

So I took the advice from that answer

SQL> alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope=both;
SQL> alter system register;

and it now works, presumably because it can resolve the localhost reference, where it was failing the resolve the actual hostname.

Best Answer

Related Solutions

Check listener status other than 1521

Oracle 11g listener fails with ORA-12514 and ORA-12505 errors

Related Question