Let me first start by stating I am not a DBA and I am not an expert when it comes to Oracle. I’ve messed around with Oracle for a few years now and I simply document these things to give you some guidance and tips, in case you run into similar problems, so if I misstate something, then I apologize, drop me a note 😉
I had a situation where I started losing connections to my databases because of the following error code: ora-12505, indicating the connection could not find the SID. End result was something messed up in my listener.ora file. So let me try to explain what I found with this file and with the tnsnames.ora file.
So Oracle seems to make connections to a listener before it can get to your database. It’s not as simple as say mysql, where you define some JDBC URL and then boom, you’re connected, this Oracle listener must be configured and running before you can ever make a connection. There is an Oracle tool called Net Config Assistant that can help you configure the listener, it’s actually quite easy to. Just remember, you need this if you want to make connections via SQL Developer, or some other JDBC like clients.
So great, you have a listener and you have your database created, or we’ll assume you have a database (you can create a database via the Oracle Database Configuration Assistant). Now let’s talk about these 2 Oracle files. Ends up when the listener is configured, it needs to know about the active databases to communicate with, and this listener.ora file is used to configure that. Oracle has the tools to configure this file, generally you should not need to go in and update it (especially if you are not knowledgeable on what you are doing), but I have had occasions where this file seems to get out of whack. Mine looks like this:
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = mybigdatabase) (ORACLE_HOME = c:/Oracle/product/11.2.0/dbhome_1) ) (SID_DESC = (SID_NAME = myseconddatabase) (ORACLE_HOME = c:/Oracle/product/11.2.0/dbhome_1) ) )
This file was setup to point to 2 databases, mybigdatabase and myseconddatabase. The listener therefore knows about these and note the format of the SID_LIST_LISTENER, very critical as this bit me in the ass. The top LISTENER section is the main LISTENER and it was configured with the Net Config Assistant.
The tnsnames.ora file contains the database entries themselves and mine looks like this:
myseconddatabase = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =myseconddatabase ) ) ) mybigdatabase = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mybigdatabase ) ) )
So that is what I understand around making sure these files are correct and in the proper format.