ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Okay so clearly tnsnames.ora is NOT missing the SERVICE_NAME in CONNECT_DATA. It's there and it looks correct to me.

When researching the error the suggested CAUSE is this:

Code:

The listener was not configured with a default service and SERVICE_NAME was missing from the CONNECT_DATA received by the listener.

and the suggested ACTION is this:

Code:

Possible solutions are:
- Configure DEFAULT_SERVICE parameter in LISTENER.ORA with a valid service name. Reload the listener parameter file using reload []. This should be done by the LISTENER administrator.
- If using a service name, Check that the connect descriptor corresponding to the service name in TNSNAMES.ORA has a SERVICE_NAME or SID component in the CONNECT_DATA.

So let me take those solutions one a time:

1. I've looked all over the place, even in at the listener.ora in the SAMPLES directory and I don't see anywhere that shows the DEFAULT_SERVICE parameter and how it is used or where to place it in the listener.ora. Does anyone know anything about this? It's suggested to do this in a million places but no where does it say where EXACTLY to place this parameter.

2. As I've already said, my tnsnames.ora already has the SERVICE_NAME in the CONNECT_DATA.

While I know a ton about MySQL, I'm very new to Oracle and how it works. Can you give me some more explaination? Like how do I check for an init parameter called "service_names"? Is it like a environment variable or something? And if it's not there, how do I register the service with the DB?

I notice that you said you were connecting locally, on the Borg host. Might not be a listener issue at all. Can you please tell us your OS, and paste in exactly what command you are typing to start sqlplus? Also, type this at the OS prompt (assuming unix) and paste in what happens:

When we've been trying to start sqlplus we've been using the following command:

Code:

sqlplus system@borg

I was unaware of the syntax you used for sqlplus (like I said I've VERY new to Oracle but learning fast) and I just tried it (logged into the server as user 'oracle' was the only way I could get it to work). And sure enough it brought up the SQL> prompt for me, no password required. Is that normal?

I appreciate your help. Any insight is helpful. I'm sure it might just be some simple little thing that is the problem. The fact that the SQL prompt it telling me that Oracle isn't running yet dbstart is starting it successfully is kind of odd.

Also in /etc/oratab I have

Code:

# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
borg:/u01/app/oracle/product/11.2.0/db_1:Y

Which from what I read online this means that Oracle should start up when the machine boots up, so I'm not sure why it isn't.

Issuing sqlplus that way gets you in as SYS using OS authentication instead of database authentication. SYS is kinda like root -- not a good idea to use most of the time, but there if you need it. You can set other users up for OS auth if you ever wanted to.

I have a suspicion you are running into case sensitivity issues. Some of the config files you listed don't care, some do. Can you type this and see if 'borg' is in there as a running database? I think you will see that 'borg' is up and running but 'BORG' is not. Also you can set $ORACLE_SID to borg and then try sqlplus again (both ways). You can try some of the parameters that failed before to see if it is running.

How come the service name comes up as borg.ourdomain.com when in the tnsnames.ora it clearly lists the service name as just "BORG" ? The only place I can see where "borg.ourdomain.com" is defined is an environment variable called $ORACLE_HOSTNAME="borg.ourdomain.com". Is that anything to do with it?