On 3 separate 64 HP boxes running hpux 11, I have 3
instances of Oracle 22.214.171.124.1 64 bit. (one db per server)
From my desktop PC, I can connect to 2 of them with
my tnsnames.ora file looking like this :
(ADDRESS = (COMMUNITY = tcp.world)
(PROTOCOL = TCP )
(Host = server1.mycompany.com ) (Port = 12345 )
(CONNECT_DATA = (SID= SID_NAME))
and I can change the 2nd to last line to :
(CONNECT_DATA = (SERVICE_NAME= SID_NAME))
and it still works.
But, connecting to one of the databases with my tnsnames file
having the 'service_name = ' gives me a ORA-12504 error.
Now, I know that the simple fix is to change my tnsnames.ora file and I have done that. But I am interested in this from
an educational standpoint...
I think all of my databases are setup on my servers the same
way. They are all running the same version of the database,
and the same version of the listener. What could it be?
thanks in advance!
The service_name may or may not be the ORACLE_SID. It is setup via the init.ora file with the "service_names" parameter. You can check this parameter by:
select name, value from v$parameters
where name like 'service%'
Thanks for the reply. Unfortunately, that does not seem to be the problem. I checked in v$parameter for all 3 databases, they all have
where SID_NAME is the sid for the database
except for the '.world' part it matchs the INSTANCE_NAME in v$instance and NAME in v$database.
any other ideas?
The world part normally get to ge setted up at sqlnet.ora file and tnsnames.ora files. You can just have the service name set as Jeff explained but have the world/domain option to tag it. This you would do through tnsnames.ora and sqlnet.ora files normally found under $ORACLE_HOME/network/admin
thanks for your help.
In my sqlnet.ora file I have the following :
NAMES.DEFAULT_DOMAIN = WORLD
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
And in my tnsnames.ora I actually list the entries twice,
once like :
(DESCRIPTION = blah blah blah...
and anther time like this :
(DESCRIPTION = blah blah blah...
with the .world on the name of it being the only difference.
I have tried to connect specifying the .world and leaving it
off, but to no avail.
Is this what you were talking about or did I misss something?
If you do not want the world part to be used, then go ahead and comment out the names.default_domain and on the tnsnames.ora remove the community and then try
it should work.
Same results. :-(
Using Net8 configuration Assistant tool to add entry to "tnsname.ora" file
and selecting to create using: Oracle8i release 8.1 database or service
Or manually creating your "tnsnames.ora" file using the service_name or
instance_name parameters .
After configuring the tnsnames file you try to connect and receive the
ORA-12504: TNS:listener was not given the SID in CONNECT_DATA
Cause: The SID was missing from the CONNECT_DATA.
Action: Check that the connect descriptor corresponding to the service
name in TNSNAMES.ORA has an SID component in the CONNECT_DATA.
ORA-12154: TNS:could not resolve service name
Cause: The service name specified is not defined correctly in the
Action: Make the following checks and correct the error:
- Verify that a TNSNAMES.ORA file exists and is in the proper
place and accessible. See the operating system specific manual
for details on the required name and location.
- Check to see that the service name exists in one of the
TNSNAMES.ORA files and add it if necessary.
- Make sure there are no syntax errors anywhere in the file.
Particularly look for unmatched parentheses or stray characters.
Any error in a TNSNAMES.ORA file makes it unusable. See
Chapter 4 in the SQL*Net V2 Administrator's Guide. If
possible, regenerate the configuration files using the Oracle
This is happening due to the fact that the Oracle8i Instance or Listener is
not set up to use the Oracle8i New Features for database configuration.
The "init.ora" file does not contain the following entries:
service_names or instance_name
The "listener.ora" file does not contain:
Solution: Configure "tnsnames.ora" file with appropriate entries
Depending on how your instance is set up with Oracle8i you need to set up
your tnsnames.ora file accordingly:
If your Oracle8i database is configured to use the NewFeatures for Oracle 8i:
In your "INIT.ora" file you have set up your instance to use
service_names and instance_name
And have set up your "listener.ora" file with global_dbname than you will be
able to configure your "tnsnames.ora" file to include service_name and/or
This will enable you to take advantage of the Oracl8i New Features for
Otherwise you would use the tnsnames.ora file for V2 or V8.
Example1 Tnsnames.ora file for using the new Oracle8i New Features:
(address = (protocol_address_information))
(service_name = )
Example2 Tnsnames.ora file using old features:
(COMMUNITY = SAMPLE_COMMUNITY) <=== not needed with Net8 (V2 only)
(PROTOCOL = TCP)
(HOST = )
(PORT = 1521)
(CONNECT_DATA = (SID = )
NOTE: If using the Oracle Tool Net8 Congfiguration Assistant to configure your
For example1 select :
Oracle8i release 8.1. database or service (3rd screen of the config tool)
For example2 select:
Oracle8i release 8.0 or previous database or service (3rd screen of
the config tool)
In order to use or take advantage of ththe New Network Features using service
name you must have the following information in the "init.ora" file:
the "listener.ora" file must have global_dbname (which should match the value
contained in the service_names from the "init.ora" file)
If you don't have any of this configured than you must configure your tnsnames.
ora file using the old network Net8 convention mode (see example2 above)
CHECK THIS OUT
Thanks for sticking with me, Sam, I owe ya one.
I do not have global_names set, and I am using the old
config in my listener.ora. Still I don't see the difference
between my different instances, they all appear to be the same way, but at least I see somthing to change now. I'll give it a try when I can get a few minutes of downtime...
From what I remember from the past with regards to using SID= vs. SERVICE_NAME= is that when you choose to use SERVICE_NAME, you need to have the listener.ora entry to have the GLOBAL_NAME= and have it equaling the same value.
If this is what you've found, then I can confirm it.