rookie Oracle connection question

I can successfully connect to a specific Oracle database if I use SQL-Plus to connect (version 11.2.0.1.0). I can also successfully connect to that same database if use Toad (version11.0.0.116). But, when I try to create the same connection via Oracle SQL Developer (version 3.2.20.09) , the specific database is not in the drop-down list of databases from which to choose if I choose a connection-type TNS. Lots of other databases are in the drop-down, but not the "target one" I'm looking for.

I verified that the database-name is correctly set up in my tnsnames.ora file, and I verified that my Windows TNS_ADMIN environment-variable does have the correct path to that file.

What am I missing?

Thanks!
DaveSlash
LVL 18
Dave FordSoftware Developer / Database AdministratorAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
>>somehow corrupted

Could be.  Oracle is pretty picky when it comes to the layout and format of that file.

Check for 'special' characters.
0
 
slightwv (䄆 Netminder) Commented:
I'm a rookie when it comes to SQL Developer so we're even...

Everything I've read is that it should go be TNS_ADMIN.

The question I have is where is it getting the values from if not the tnsnames.ora file pointed to be TNS_ADMIN?

Sounds like you have more than one Oracle product installed.

I've read that SQL Developer under Tools/Preferences then Database/Advanced has a place to enter the location for the tnsnames.ora file.  Try that.
0
 
Dave FordSoftware Developer / Database AdministratorAuthor Commented:
Thanks, slightwv. Based on your suggestion, I checked that "Tnsnames Directory" entry in Preferences. It was blank, so I clicked the "Browse" button, and I selected the appropriate folder. Just for paranoia's sake, I exited SQL Developer and re-started it. After verifying that the change I made "stuck", I tried to create the connection again, but the target database is still not in the drop-down.

I'm wondering if maybe the tnsnames.ora file is somehow corrupted .... since one of the "network aliases" listed appears garbled. All the rest are OK, but one of them doesn't look right.

I'll keep looking. Any other ideas?

Thanks again for your help!
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Mark GeerlingsDatabase AdministratorCommented:
There are two possibilities I can think of:
1. Maybe you have multiple Oracle_homes (that is: multiple, but separate, installations of Oracle software on your PC so you may have more than one tnsnames.ora file.
2. Maybe the garbled entry in your tnsnames.ora file is the problem.  A garbled entry in that file *WILL* cause problems.

Make a copy of your tnsnames.ora file (just in case) then edit the original one with a simple text editor (like Notepad) since Word or WordPad can add control characters that will cause problems.  Try to remove or correct the garbled entry.
0
 
Dave FordSoftware Developer / Database AdministratorAuthor Commented:
Thank you both for your help!

I got around the problem by changing the connection-type from TNS to Basic ... manually specifying the host-name, port, and SID. Now, it works like a charm.

As with many things in life, I don't have to like it, but I do have to live with it and adapt.

Thanks again!
DaveSlash
0
 
slightwv (䄆 Netminder) Commented:
Unfortunately that only masks the issue.

If you have a 'bad' tnsnames file, it really should be fixed.

All you've done is hang a picture over the hole in the wall when you should look at patching the hole.
0
 
Mark GeerlingsDatabase AdministratorCommented:
In my experience, usually the trickiest part of setting up (or connecting the first time) to a new Oracle database is getting the TNS info specified correctly.  After that is done, usually everything else just works as expected.

If you want to upload your tnsnames.ora file, we can review it to look for errors.  (Edit it first to remove confidential server names, IP addresses, etc. if you want.  Just carefully replace letters with all "X"s and numbers with all "9"s for example.)

You can test individual entries in your tnsnames.ora file from the command line like this:

tnsping [alias] [Enter]

For example, if your tnsnames.ora file looks something like this one:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
mydb =
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=xyz123.orgname.com)(PORT=1521))
            (CONNECT_DATA=
                (SERVICE_NAME=mydb)
                (INSTANCE_NAME=mydb)
            )
        )

other_db =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 123.45.6.789)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = othdb)
    )
  )
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

You can test those entries by navigating to your ORACLE_HOME\bin directory, then typing:

tnsping mydb

or
tnsping other_db

Those commands should return about a three-line response with "OK" on the last line.  That confirms that this entry is valid in your tnsnames.ora file, and that your network connection to that database is working.  If you see something instead. like "ORA-xxxxx" or "TNS-xxxxx" on the last line, that indicates a problem.
0
 
Dave FordSoftware Developer / Database AdministratorAuthor Commented:
slightwv:
> Unfortunately that only masks the issue.
> If you have a 'bad' tnsnames file, it really should be fixed.
> All you've done is hang a picture over the hole in the wall when you should look at patching the hole.

I completely agree with you (and I love your analogy), but I don't have time to mess with it right now. Sometimes, when guests are coming over for dinner in 30 minutes, you hang the picture over the hole in the wall, and you hope to have time to patch the hole later.

markgeer:
> You can test individual entries in your tnsnames.ora file ... tnsping [alias] [Enter]

Thanks! I did that, and it appears to work correctly.

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = xxxxxx1db.yy.zzzzzzzz.www)(PORT = 1521))) (CONNECT_DATA = (SID = xyzxyzx)))

OK (160 msec)

Open in new window


Thanks again!
0
 
slightwv (䄆 Netminder) Commented:
>>you hang the picture over the hole in the wall, and you hope to have time to patch the hole later.

Agreed!  Been there!

Then I had to explain the picture is hung that low so the children can enjoy it or it is to allow me to appreciate it from a non-standard perspective!
0
 
Dave FordSoftware Developer / Database AdministratorAuthor Commented:
LOL ! Thanks for the new phrase. I'm going to use that.

"This allows me to appreciate it from a non-standard perspective"

:-D
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.