CODEWITHSUNDEEP
oracle-databaseoracle11gtnsnames
learner
learner

Reputation: 1023

ORA-12505 error when connecting to databases on the same machine that listen through the same port

I have a database and it's copy in the same machine called DB1 and DB2.

I am trying to connect to both the databases through SQL Developer. I don't have an Oracle Client installed on my windows.

The tnsnames.ORA looks like this:

DB1 =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.com)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = DB1)
 )
)

DB2 =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.com)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = DB2)
 )
)

The listener.ora looks like below:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtPROC)
      (ORACLE_HOME = /product/11.2.0.4/)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.com)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB1)
      (SID_NAME = DB1)
      (ORACLE_HOME = /product/11.2.0.4/)
      (PRESPAWN_MAX = 50)
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB2)
      (SID_NAME = DB2)
      (ORACLE_HOME = /product/11.2.0.4/)
      (PRESPAWN_MAX = 50)
    )
  )

I am able to connect to DB2 but not to DB1. When I connect to DB1 from SQL Developer I get the ORA-12505 error.

When I run $ ps -ef | grep pmon, I get 2 processes - ora_DB1 and ora_DB2

When I run $ ps -ef | grep tns, I get 1 process - LISTENER

When I run $ lsnrctl status LISTENER, I get the output

Services Summary

Service "DB2" has 1 instance(s). Instance "DB2", status UNKNOWN, has 1 handler(s) for this service...

Why am I unable to connect to "DB1" ?

Upvotes: 0

Views: 359

Answers (2)

learner
learner

Reputation: 1023

Thank all for the help !

It had something to do with dynamic registration. It was set to OFF on the first line of the listener.ora file. Once that was commented out, it was fine. The port remains the same, there was no need to listen through another port.

Also, apparently when I ran the $ lsnrctl status LISTENER, I got the output as status UKNOWN because dynamic registraion was turned OFF.

Service "DB2" has 1 instance(s). Instance "DB2", status UNKNOWN, has 1 handler(s) for this service...

So, 2 databases on the same machine can be configured to listen through the same port.

Upvotes: 0

Can
Can

Reputation: 39

Create another listener for DB2 and change port with 1522 in listener.ora file then you can connect. Also your SID_LIST_LISTENER names are same. You have 2 SID_LIST_LISTENER and they look for DB1 and DB2 but their names are same. Change their names like SID_LIST_LISTENER1 and SID_LIST_LISTENER2.

dont forget the restart listener

(optional) you dont need to add another SID_LIST_LISTENER. If you want you can add a SID_DESC in default SID_LIST_LISTENER like that ;

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB1)
      (SID_NAME = DB1)
      (ORACLE_HOME = /product/11.2.0.4/)
      (PRESPAWN_MAX = 50)
    )
     (SID_DESC =
      (GLOBAL_DBNAME = DB2)
      (SID_NAME = DB2)
      (ORACLE_HOME = /product/11.2.0.4/)
      (PRESPAWN_MAX = 50)
    )
  )

Upvotes: 0

Related Questions