ORA-12154: TNS:could not resolve service name error in SQL Navigator

If the above suggestion does not work, check the value of the NAMES.DEFAULT_DOMAIN parameter in your sqlnet.ora.

For example:

NAMES.DEFAULT_DOMAIN = company.com

and in the tnsnames.ora, you should have:

DB1.company.com =     (note the suffix here)
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = TESTDB1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTDB1)
    )
  )


Then try:

sqlplus username/passwd@db1

You have to make sure that you are using the tnsnames.ora of the product you use as client. There are many such files, so you have to find the appropriate one.
I send you the instruction for Developer/2000, but the solution seems to be the same.

Oracle Server <---> Developer 6i:
Oracle Developer6i and the Oracle8i database require separate Oracle home directories.
Even if you configure SQL*net (Net8) in the Oracle home that your database Oracle server uses, so that you can connect via SQL*Plus, that does not allow Developer2000 to connectsuccessfuly to the Oracle server.  You also need to configure SQL*Net (in tnsnames.ora file) in the
Oracle home for Developer2000.  You can do that either using the GUI tools, or to copy two files:
sqlnet.ora and tnsnames.ora from your 8i Oracle home to your Developer2000 Oracle home.  In your 8i
Oracle home these files are in: \Network\admin, but in your Developer2000 home they are in: \Net80\Admin. A safety measure is to save a copy of them first, before you make changes or copy other ones in. This is so because you have to keep a way for restoring the files if you make a mistake.
--------
This is a generic advise I collected for a year answering to this kind of questions.
About connection between Personal Oracle and Forms 6 look at
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:447620106871

You can use the "Oracle Net 8 assistant", "Oracle Net 8 Easy config" to see if you have the appropriate entry to the server in the local tnsnames.ora file (find it!). I mean that one in the Client or Forms&Reports home, not that one in the Oracle_Home.
Under local I mean the file tnsnames.ora  in your application, not at the Oracle Server - this is a common mistake.

You have to have there an entry like:

REKS816.RILA.us =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bigbluenew.rila.us)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = REKS816)
     )
  )


Be aware that in old versions of Oracle instead of SERVICE_NAME you have to use SID. So (SERVICE_NAME = REKS816)will be (SID = REKS816)
     
It is a bad practice, but you can add such entry using simple text editor. But before that make a copy of tnsnames.ora in order to restore it if you do not succeed.

After that go to "control panel" and run "services".
Look at your Oracle_Home_TNSlistener80 process. Stop it! Start it again!

If the error received is only via sql navigator. Then try to restart the listerner.


c:\>lsnrctl
lsnrctl> stop

and...

lsnrctl> start

and even check if the output of "tnsping <instance name>" is OK.

I tried the both suggestions listed above and I still could not make a connection.  I decided I'd check out the .ora files on another machine that I knew worked.  It turns out that the sqlnet.ora file had a NAMES.DEFAULT_DOMAIN set to our internal network... after I commented this line out  I was able to make the connection.

Just try WinSID tool...
It doesn't use Oracle to connect to listener an has no *.ora config files...If IP an port is accessible...you'll et it connected...
Will generate a (simplistic) valid connection descriptor. I'm sorry, I don't think i can post information where is (no advertising) but you'll find with search engines...

Please feed-back...

Hi! I found my problem. Before installing "10g Client", i did'nt remove old Oracle freeware software. So, my Oracle HOME was not definated to Oracle home directory. Solution: i opened regedit (regsvr32) and correct Oracle home directory.
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

Use of USE_SHARED_SOCKET with Oracle 8.1.7 and Windows NT/2000

In short:
1. If you are using Oracle 8.1.7 install patch 2376472, that is Oracle 8.1.7.4, you can get it at Metalink
2. Add USE_SHARED_SOCKET to your registry (there are 2 possible locations, depending on your Oracle version)
3. Restart the computer
4. Do some local connection testing using a client computer (tnsping, sqlplus, netstat)
5. Open the port in the firewall
6. Do some connection testing from outside the firewall (tnsping, sqlplus, netstat)

Here are the details:

The USE_SHARED_SOCKET parameter allows you to use a single port for oracle communications, by default TCP 1521.
You must have in mind that this might slow down connections a little when many clients are connected (50 or more, depending on different conditions).
Also if the listener service shuts down all connections will be broken.

This parameter only works fine with Oracle Patch 8.1.7.4, you can get it at Oracle Metalink homepage, and here is the detail for the patch:

Patch 2376472
Oracle Database Family: Patchset 8.1.7.4 PATCH SET FOR ORACLE DATA SERVER
Release 8.1.7.4
Updated 14-JUN-2002
Size 112MB
File name p2376472_8174_WINNT.zip

I would advise you to install this patch before you create any databases in your system, it's easier.
If you already have some databases created there's no problem, just follow the additional steps after installing the patch.

Troubleshooting: Oracle services have to be stopped during installation of the patch, in my case I started windows in safe mode and then installed the patch. Also because my computer has an Intel Xeon I had to rename 2 files called symcjit.dll to symcjit.old, because (at least in my case) this java jit compiler does not work with this processor, the same is true for Intel Pentium 4.
Almost for sure TNSPING command already works at this point but not SQLPLUS, isn't it? Don't worry, the update will make USE_SHARED_SOCKET work.

Step 1: Installing patch, done.


Next we have to add the USE_SHARED_SOCKET parameter to the registry.
Place the parameter under the following locations:
    For Oracle 8.0 use            \\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
    For Oracle 8i or newer use    \\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME#
    (Where # is the number of your HOME, usually zero "HOME0")

Add USE_SHARED_SOCKET (New, Alphanumeric Value), then set its value to TRUE

Then restart your computer.


Step 2: Add USE_SHARED_SOCKET parameter into the registry, set it to TRUE and restart, done.


From some client computer do some connection tests.
Use:
     C:\> tnsping your_oracle_service 100
(That will ping you database service 100 times to check connection times)
Usually tnsping works fine even before USE_SHARED_SOCKETS have been set, because it doesn't make a connection to the service.

Use:
     C:\> sqlplus user/password@your_oracle_service

If connection succeeds you'll see something like this:

     SQL*Plus: Release 8.1.7.0.0 - Production on Jue Sep 22 12:32:19 2005
     (c) Copyright 2000 Oracle Corporation.  All rights reserved.
     
     Connected to:
     Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
     JServer Release 8.1.7.4.1 - Production
     
     SQL>

Then in the SQL> prompt you can do some query to do some more testing, but we are already connected.

While doing your tnsping and during your connection with sqlplus try this command in the server:
C:\> NETSTAT

There you can see if connections are being made using only your oracle port (usually 1521)
You'll see something like this:

Active Connections                                                
                                                                   
  Proto  Local Address        Foreign Address           State      
  TCP    server:1036          server:1521               ESTABLISHED
  TCP    server:1043          server:1088               ESTABLISHED
  TCP    server:1088          server:1043               ESTABLISHED
  TCP    server:1521          server:1036               ESTABLISHED
  TCP    server:1316          CLIENT02:netbios-ssn      TIME_WAIT  
  TCP    server:1317          CLIENT02:netbios-ssn      TIME_WAIT  
  TCP    server:1318          CLIENT02:netbios-ssn      TIME_WAIT  
  TCP    server:1521          CLIENT04:1336             TIME_WAIT  
  TCP    server:1521          CLIENT04:1337             TIME_WAIT  
  TCP    server:1521          CLIENT04:1338             TIME_WAIT  
  TCP    server:1521          CLIENT04:1339             TIME_WAIT  
  TCP    server:1521          CLIENT04:1340             TIME_WAIT  
  TCP    server:1521          CLIENT04:1341             TIME_WAIT  
  TCP    server:1521          CLIENT04:1342             TIME_WAIT  
  TCP    server:1521          CLIENT04:1343             TIME_WAIT  
  TCP    server:1521          CLIENT04:1344             TIME_WAIT  
  TCP    server:1521          CLIENT04:1345             TIME_WAIT  
  TCP    server:1521          CLIENT04:1347             ESTABLISHED

There we see that in column "Local Address" only port 1521 is being used for our client computer "CLIENT04" (among other ports and clients connected)


You can use any other kind of connections you would like to test, in my case tnsping and sqlplus were enough.

Troubleshooting:
On your server check the files listener.log and sqlnet.log they can give you some good information about connections.
Delete them both to start fresh (you'll have to stop the listener service to delete the listener.log file, you do so with running "C:\> LSNRCTL" then using the STOP command, deleting the file, then restarting the service using the START command and then the EXIT command)


Step 3: Local tests using tnsping and sqlplus, done.


Now you have to open your Oracle port in the firewall, usually TCP port 1521

Troubleshooting:
Sometimes you'll have a modem, a router, a firewall or a mix of them, consider that you might configure some of them or all to make port forwarding work.
Also considering security issues: 1521 is a very well known port, you can change it. Also consider Listener password and some others, check your options well.

After opening the port do some testing from outside the firewall (depending on your configuration you can even try testing using an internet Dial-up connection).

Do your TNSPING, your SQLPLUS connection, your NETSTAT on the server and so on.

Troubleshooting:
In my experience I had no problems with clients working with a personal firewall so I had not to open any port on those personal firewalls.

I hope this might be of help.