ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA - Oracle Database Express Edition (XE)

I have this error
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
when I start sqlplus on a remote host. I can be able to login to Oracle XE locally with sqlplus however.
Any ideal? 

Do yo have any other Oracle installs on the box ?
If you've already got a tnsnames.ora kicking around - this may be causing confusion.
Dougie McGibbon
Oops, sorry, mis-read that.
Message was edited by:
DMcG 

You need to add the XE service name to the tnsnames.ora file you have on the remote box. Copy the XE entry you have in your local tnsnames.ora. On my machine this looks like
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mtownsen-lap.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
You don't give the platforms for your local or remote box, so I can't guess at the locations of these files, but they are typically under $ORACLE_HOME/network/admin
On my local WIndows box, the direct path for the Oracle Database XE installation is
C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora 

Let me clear my words a little bit, I am running sqlplus on my local box, and trying to connect to the remote box. That is doing somthing like sqlplus user1#db.ahostname.com
Now do I need to put that XE entry that you mention into the remote box? If that's the case, I would need to ask the DBA on that remote box to do that for me. 

I only have Oracle XE install on my box. However I did uninstall it by using rpm -e oracle-xe
And then reistall XE again. Does it also make any confusion? 

In that case you need to add the entry for db.ahostname.com to your local tnsnames.ora. Ask your DBA for this entry. 

That means I need to know how the DBA definds the SERVICE_NAME in it's CONNECT_DATA? The remote DBA might not have a entry XE in the tnsnames.ora, but whatever the entry is for the SERVICE_NAME, would be fine? 

There are two possible scenario's here. It's not clear which one you are referring to
1. You are trying to connect from your machine, where you have installed Oracle Database XE, to another Oracle database on a remote machine.
If so, you need to tell SQLPLus locally how it can 'see' the remote Oracle Database. So you need to add an entry to your local tnsnames.ora that identifies the remote database. I do not know that that entry will be, as I have not idea what the remote machine is called, or what the database running on that remote machine is called. But whoever set up that remote machine should be able to advise you
2. If you are trying to connect from a remote machine to the XE database on your machine, then you will need the Oracle client software installed on the remote machine. You (or the DBA) will also need to add an entry to the tnsnames.ora on the remote machine so that they can 'see' the XE database you have on your machine. This entry will be something like
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mtownsen-lap.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
) 

I am in the scenario 1 that you mention. However I could get away with it last time when I were using an Oracle 10g which was installed on another local box, and I can use sqlplus on that local box connect to a remote database, without adding an entry to the tnsnames.ora config file. How come? 

To the TNSNAMES??? Isn't that, to the LISTENER.ora???
Jim 

I dunno - could be that
1) Somebody had already added the entry to tnsnames.ora
2) Your site is using directory naming 

Jim - in both scenario's described it's tnsnames.ora that needs the update. 

What is directory naming? 

This is outside of the scope of this forum, but here is some information. Note that this is a feature of the licensed versions only
http://download-west.oracle.com/docs/cd/B19306_01/network.102/b14212/config_concepts.htm#sthref255 

While waiting for my DBA to respone.
If I have the hostname and the database name of the remote host. It is how the entry look like:
XE_2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db.uk.oracle2.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = databasename)
)
)

Related

Connecting to Oracle database Express Edition

Hi, how can I connect via ODBC? Where can I read something about this?
Oracle Client is already installed and I don´t know what to specify in TNS Service Name.
Regards, 
For XE, the TNS serivice name is XE. If you're on the same box as the database, you should be good to go. If not, copy the XE entry from the database server tnsnames.ora file and copy it to your local tnsnames.ora. That's about the esiest way to get started.
If you want to read more about tns and oracle networking, you can view the oracle 10gr2 documentation at tahiti.oracle.com.
Thanks,
LewisC
http://blogs.ittoolbox.com/oracle/guide/ 
The service name is called XE by default
Is this a different machine you are trying to connect from - if so, you will probably need to add the XE definition to the local tnsnames.ora if you have one - copy the values below and update the host name
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = your_fully_qualified_host_name_here)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
) 
Thank you for the Reply.
Yes, I´m trying to connect from another WorkStation in the Network.
Where to I store tnsnames.ora at the Client WorkStation?
Regards, 
You said that you had the Oracle Client already installed ? In which case, look for a /network/admin directory in the corresponding directory structure. There may already be a tnsnames.ora there.
(Or did you mean Oracle Instant Client ?) 
There was no .ora there, but I copied the one from the Server and everything is fine now.
Thank you again.

SID not specified in TNSNAMES.ORA

I installed Oracle XE and the services is as follows.
Why there is not SID specified in the services?
Please guide.
XE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = S434.mgm.org)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
) 
no idea, but the default sid seams to be xe 
Since Oracle 8i - the use of the SID in the tns connect entry has been optional and in most cases "not the preferred method"
8i introduce the ability for the database to dynamically register with the listener - so when the database is started, it'll contact the listener and tell it all about itself.
Then, a client just says "I would like to connect to this service", the listener sees what that service is all about and makes the connection happen. 
I am tryinig to connect Oracle SCM with XE which is not connecting without a SID. Any Suggestion? 
please describe what you mean by "not connecting without a sid"
explain the topology here (what piece of which software is where, and what is in the tnsnames.ora file on each machine...) 
I am trying to logon to Oracle SCM what can be given in Connect String field? 
Please elaborate. Please give us a lot more description of what you are trying to accomplish and what you are actually typing and what is actually failing.
As it stands - I have no idea where your SCM is or how your SCM relates to XE or what a connection between SCM and XE would look like.
Perhaps ...
you have installed XE. you now want to use the SQLPlus installed with XE to logon to your SCM instance (hopefully on a separate computer). it is providing an error and you want to determine how to resolve the error?

When ODBC is created I have "tns: could not resolve the connect..." error

Hi All,
I have client and server machines with Oracle10gr1 DB installed on the server, and Oracle 10gr1 Client installed on the client machine. I have created a database called UNICODE on the server machine, using DB Configuration wizard. I am able to login in it from the client machine using Management Console.
The problem I have is that I need to craete ODBC data source on the client machine for the database created on the server. When I create it and try to test it the error "tns: could not resolve the connect identifier specified" is shown. The ODBC driver installed on the client machine is "10.01.00.04". The strange thing, is that I am able to create an ODBC data source, using exactly the same credentials etc. on the server machine, and it is working. The only difference is that on the server machine the driver version is "10.01.00.02". But the driver version on the client is must condition, and I can not change it. Another strange thing I see is that services "OracleDBConsoleOra10r1" and "OracleDBConsoleUni10r1" on the server machine are set on "Automatic" start, but are not started, and I am not able to do so.
Below I have copied the "tnsnames.ora" and "sqlnet.ora" files. TNS Service Name used is "UNICODE". Data Source name used is "Oracle10r1.sofia.domain.bg"
Any help will be appriciated :-)))!!!!
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# tnsnames.ora Network Configuration File
UNI10R1_ORACLE10R1.SOFIA.DOMAIN.BG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Oracle10r1.sofia.domain.bg)(PORT = 1521))
)
(CONNECT_DATA =
(SID = Uni10r1)
(SERVER = DEDICATED)
)
)
UNICODE_ORACLE10R1.SOFIA.DOMAIN.BG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Oracle10r1.sofia.domain.bg)(PORT = 1521))
)
(CONNECT_DATA =
(SID = UNICODE)
(SERVER = DEDICATED)
)
)
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# sqlnet.ora Network Configuration File
SQLNET.AUTHENTICATION_SERVICES= (NONE)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) 
Hi All,
Is this problem so strange, or I have post it in the wrong forum? :(((
Georgi... 
When you craete ODBC data source, what source name given UNICODE or UNICODE_ORACLE10R1 or UNICODE_ORACLE10R1.SOFIA.DOMAIN.BG.
Run following command on command prompt and post the output in reply
tnsping UNICODE_ORACLE10R1.SOFIA.DOMAIN.BG
Virag 
Hi Virag,
I used all combinations you have specified, still the ODBC is properly working on the server machine with UNICODE and I believe it is the proper way. About tnsping - the result is "OK" so I think that the problem may be in the driver ....
Regards,
Georgi 
Just to narrow the scope of the problem.
It can be seen from the above post you were able to tnsping listener using the tns alias, which means your path environment variables and tns_admin related entries are well configured on the oracle side.
If oracle client is able to open a session using sqlplus and the tns entry just mentioned, then you can be sure it is a driver issue.
Are you already working with 10.1.0.4 patchset at the client machine? This patchset is supposed to fix some ODBC connectivity issues on the windows platform.
HR Madrid 
Hi Madrid,
I am not sure what exactly do you mean by using patchset "10.1.0.4 ", because I was not able to find a patchset with such number in metalink. Can you tell how to check this? If you are speaking about the ODBC driver I am using - it is "10.01.00.40".
Best Regards,
Georgi 
No, I am talking about Oracle Patchset. You can verify it. Start the Oracle universal Installer, you'll be able to find it from the start menu. Once you have openned a OUI session, check the installed software components. In case 10.1.0.4 is installed it will appear as another component. Otherwise, it is advisable to have it installed. Before proceeding confirm this fact.
Madrid.

Problems Installing Oracle SCM Repository

Running on Windows 2000 Professional: I'm not an Oracle DBA, just a lowly developer trying to get an
environment set up for testing J2EE programs. Grin.
This is probably something simple, but it's driving me nuts.
I need to create a new, first time SCM repository so I can use Designer 9i.
I'm using the 9i SCM Installation Guide, and trying to use the SQL Script Method.
Here's the rub, I cannot "connect" to my freshly created Oracle 9i, Enterprise database using SQLPLUS, so I can run
the scripts.
Depending on the host connect string syntax I use (tried many, none work), I get these errors:
ORA-06401 NETCMN: Invalid Driver Designator or
ORA-12560 TNS: protocol adapter error or
ORA-??? Invalid username / password.
Now, I have the Oracle 9i DB located in Oracle Home (OraHome92), and the
Developer Suite (Designer 9i) located in a different home (OraHome92_DS).......on the same box.... on the
same drive partition. Is any of this a problem? I noticed each home has it's own, sometimes
duplicate set of directories and utilities.
I can communicate with the Database just fine with either JDeveloper (local and net) and/or Enterprise Manager.
So why can't I seem to connect using SQLPLUS? I've checked JDBC drivers, classpaths,
TNSnames, and a number of other things. I'm stumped, and the docs I've seen so far don't go into enough
detail to troubleshoot. I will try looking at trace files next, but am hoping someone out there
has "That Simple Answer".
Thanks!!!
Glen 
Glen,
Just going through the same steps after a thorough clean of my laptop.
Just installed the database 9i R2 on Win2000 and about to install 9iDS. I suspect it is an issue with tnsnames.ora. Are the tnsnames.ora files identical in the 9i and iDS network directories? Can you post both of them and I will compare with my install.
David 
Glen,
Also have a look at
http://otn.oracle.com/docs/products/designer/doc_library/des9i_rel2_doc/CDOC72/rep_relnotes/rn_rep_9i.htm
David 
Thanks for the info David,
I too, am loading the 9i DS software on a laptop. How is your loading process going? Mine is not so good....
Are the TNSnames.ora files identical in both 9i and Oracle DS? No, they are not, so I made them the same....still no go.
I'm beginning to think that both Oracle DB and Oracle DS should go into the same Oracle Home.
Also, somehow something else has changed, I can no longer access my database with Enterprise Manager....gives me an
ORA-12638, "Credential Retrieval Failed". I can still talk to the DB from JDeveloper though. Interesting?
The release notes you mentioned point out that what used to be called a "SID", should now be called a "SERVICE NAME"
in the TNSnames.ora file. In my files, all the 9i entries came out correct, and the 8i entries used SID, which I
suspect they should.
Here's a sample of my TNSnames.ora for the 9i database. It obviously shows some of my experiments and
needs cleanup:
# TNSNAMES.ORA Network Configuration File: c:\OraHome92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
GDL.NW.NOS.BOEING.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = E266299)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = GDL.NW)
)
)
AWFOC4J_WF-AUB-03.NW.NOS.BOEING.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = wf-aub-03)(PORT = 1521))
)
(CONNECT_DATA =
(SID = awfoc4j)
(SERVER = DEDICATED)
)
)
INST1_HTTP.NW.NOS.BOEING.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = E266299)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)
GDL_E266299.NW.NOS.BOEING.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = E266299)(PORT = 1521))
)
(CONNECT_DATA =
(SID = GDL)
(SERVER = DEDICATED)
)
)
_E266299.NW.NOS.BOEING.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = E266299)(PORT = 1521))
)
(CONNECT_DATA =
(SID = GDL)
(SERVER = DEDICATED)
)
)
I did notice that under certain circumstances, I used to be able to log in to the database using the windows version of
SQLPLUS "if" I didn't enter a connect string, i.e. only the User ID and password.
Some material I read, stated that the SQLPLUS jdbc syntax used, depends on which Net8 protocol is used to
communicate with the DB. So, I took a stroll through the Net configuration tool. There doesn't seem to be any problem there.
I also have an Oracle 8.1.7 DB on another machine. I can talk to it just fine from all the tools. It's just this 9i stuff that
is giving me headaches. I'm just about ready to wipe it all out and start over again from scratch. I've probably introduced
enough extra problems by now, that it may be value added to start over.
Another question that arose is about authentication and the internet domain naming of database instances/services.
I wonder if there are any hooks into the Microsoft OS authentication mechanism that could interfere?
Have a Great Day, David,
Glen
Hi David,
Here's some interesting info I learned while trying to solve the 9i repository install.
I tried loading Oracle 9i DS into the same home as Oracle 9i database. I got the following message:
"Oracle 9iAS (OC4J) cannot be installed into an existing Oracle 9i, v9.2.0.1.0 or higher Home."
The failing point here may be that I'm loading from downloaded files, not off of media.
I've noticed that Universal Installer is v2.1 in DS, while it's v2.2 in Oracle Enterprise DB.
The DS Universal Installer does not allow for a custom install, say to avoid loading OC4J. So, it has to go into
a separate Oracle Home, which is the way I started this project.
This causes other problems, specifically, the Oracle Developer Suite does not seem to install PL/SQL correctly
or the standard set of configuration scripts, such as the ones for setting up the repository. During the repository install
a package, "STANDARD" is looked for, but cannot be found.
So, I'm now looking for some help directly from Oracle to find out what installation process is best for SCM
and Designer 9i being used with Oracle DB, v 9.2 or later.
I figured out my authentication problem. I had loaded the Oracle software while my laptop was disconnected from
my company's Win2k network domain. It's funny, but I found that I could authenticate my access to the DB fine while
offline, but the minute I re-attached, I could no longer authenticate. The sqlnet.ora file contains the command
about which auth method to use.
Thanks for you Help, David
Glen

PROGRAM (agent) for HetConn to Interbase?

I have installed 9i on XP (all seems well except that OMS will not run - tips on that accepted, too) and need to move data from an Interbase db on a Unix machine, to a 9i db on the XP.
I have successfully configured, and am using, an ODBC connection to the Interbase db - using Power Designer to reverse engineer the Ibase db and generate scripts for 9i.
I do not seem to be able to get an Oracle heterog. connection established from 9i to Ibase. I've tried the NetMgr, as well as directly editing listener.ora, tnsnames.ora, and the .ora file needed in the ORACLE_HOME/hs directory.
I am guessing that part of my problem is not knowing, with any assurance, what should go in these fields in the NetMgr:
for listener.ora - what is the agent executable here:
SID is pretty clear
ORACLE_HOME - it wants the OraHome of the agent exe?
PROGRAM - specify the name of the agent exe?
for tnsnames.ora:
seems to want the SERVICE_NAME = SID, above
(HS-ok)
the ADDRESS descriptor - it's tcp, but are PORT
and HOST 1521 and the XP (yes?)?
Testing the connection in NetManager fails. I'm assuming that the OMS problem is unrelated.
please respond here or to sriley#veic.org -- thanks!
Sara R. 
Sara,
Your listener.ora entry should look something like this:
ORACLE_HOME is where your Oracle database is installed.
...
(SID_DESC =
(SID_NAME = gc4interbase)
(ORACLE_HOME = c:\oracle\ora92)
(PROGRAM = hsodbc)
The tnsnames.ora should look like this:
gc4interbase=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA = (SID = gc4interbase))
(HS = OK)
)
You also need to create a database link specifying the username/password to connect to the non-Oracle system.
Thanks - I put in hsodbc as the agent program name, which I think is the only substantive difference between your .ora files and mine, but this made no difference.
OEM's Oracle Management Server will not stay STARTed, could this be part of the problem? OMS starts, then stops, failing to initialize, with tons of Java lang nullPointer exceptions.
anyhow thanks for replying, 3531 (sorry, don't know your name)
Sara
Sara,
Accessing non-Oracle systems is not related to OMS at all. They are totally independent of each other.
What is the error that you are getting?

Categories

Resources