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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.