cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle tnsnames.ora

SOLVED
Go to solution
Kalin Evtimov
Regular Advisor

Oracle tnsnames.ora

Hello!
I have a big problem:

just set up an oracle Client and try to connect to the database over sqlplus...

sqlplus /nolog
connect / as sysdba

I get:
ORA-12545: Connect failed because target host or object does not exist

BUT, if I test with tnsping, it works..
What is the problem here?

Here is my tnsnames.ora:

ORASERV.TEST01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraserv)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test01)
)
)
15 REPLIES
Tomek Gryszkiewicz
Trusted Contributor
Solution

Re: Oracle tnsnames.ora

tnsping is not testing a connection, it is just pinging the host.
Is it listener up and running on "oraserv" ?
Kalin Evtimov
Regular Advisor

Re: Oracle tnsnames.ora

No, I think it isn't running. But I don't have a lsnrctl in the bin directory :( So how can I start it. And why it isn't there?
TwoProc
Honored Contributor

Re: Oracle tnsnames.ora

Well,

to connect "/ as sysdba" you should be ON THE BOX hosting the database, as well as logged in as the user that is hosting the database, AND the ORACLE_HOME should be set to the database's ORACLE_HOME that is running the database.

What I'm seeing is wanting to connect to a remote database (hence the worry about tnsnames.ora), and a wish to control it locally (no connect string).

If you want to sqlplus as a sysdba to a remote database you'll have to use:

sqlplus "sys/[password]@[DBNAME] as sysdba"

So if the database you are "tnsping"ing is "TEST" then you'd have to use:

sqlplus "sys/password@TEST as sysdba"
We are the people our parents warned us about --Jimmy Buffett
Oviwan
Honored Contributor

Re: Oracle tnsnames.ora

Hi

try it like that:
oraserv.test01=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=oraserv)(PORT =1521))
(CONNECT_DATA=(SERVICE_NAME = test01))

or replace the hostname with the ip address.

can you connect from an other box?

Regards
TwoProc
Honored Contributor

Re: Oracle tnsnames.ora

Kalin,

The lsnrctl isn't there because you are apparently NOT on the host server. You are on a client only connection. See my previous post on how to connnect from a remote connection.
We are the people our parents warned us about --Jimmy Buffett
Kalin Evtimov
Regular Advisor

Re: Oracle tnsnames.ora

I can connect from other machines. Maybe my sqlplus is not correct, I am doing Oracle for a very first time..
Oviwan
Honored Contributor

Re: Oracle tnsnames.ora

copy the tnsnames.ora and also sqlnet.ora from a machine where it works.

if it doesn't work with this two files, reinstall sql*plus
renarios
Trusted Contributor

Re: Oracle tnsnames.ora

Hi Kalin,

So your database (instance) name is TEST01
The default domain in your sqlnet.ora is probably WORLD. So if you add the following entry in your tnsnames.ora it should work:
TEST01.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORASERV)(PORT = 1521))
)
(CONNECT_DATA = (SID = test01)(SERVER = DEDICATED))
)

If you like to change the default domain (WORLD) to MY_DOMAIN, change the intry in the tnsnames.ora to:
TEST01.MY_DOMAIN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORASERV)(PORT = 1521))
)
(CONNECT_DATA = (SID = test01)(SERVER = DEDICATED))
)

and change it in the sqlnet.ora (VERY IMPORTANT!!) to
NAMES.DEFAULT_DOMAIN = MY_DOMAIN

Cheerio,

Renarios
Nothing is more successfull as failure
Simon Wickham_6
Regular Advisor

Re: Oracle tnsnames.ora

Hi,

By the error message of ORA-12545 Oracle suggest that this error indicates that the address specified (in the alias definition) is not valid.

Possible Remedies:

1. Oracle suggest ensuring that the ADDRESS parameters (in the TNSNAMES.ORA file) have been entered correctly; the most likely incorrect parameter is the node name.

2. Ensure that the executable for the server exists (perhaps "oracle" is missing.)

3. If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the host name to a numeric IP address and try again.

4. It could be a network connectivity problem. Try pinging the host. This error has been observed on a laptop when the database was local, TCP/IP protocol used but the network card had been removed


Regards,
Simon
Simon Wickham_6
Regular Advisor

Re: Oracle tnsnames.ora

Sorry just one quick thing first try to set the ip address and host name of the servers in the /etc/hosts file of your client.

Regard,
Simon
Frank de Vries
Respected Contributor

Re: Oracle tnsnames.ora

I would check two variables are set and
if yes what are the values ?
TNS_ADMIN
and
TWO_TASK

Because if you run sqlplus /nolog
or connect / as sysdba you should
not use NET8 as the / implies
a local connect.
A remote connect would be like this
system/passwd@ORASERV.TEST01

So you get the ORA-12545 on a local
connect implies something else like
TWO_TASK is set wrong or something.



Regards,
Frank

Look before you leap
Eric Antunes
Honored Contributor

Re: Oracle tnsnames.ora

Hi Kalin,

Lanch SQL*Net Easy Configuration (it is normally installed with SQL*Plus) to configure the local (in the client) tnsnames.ora.

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Eric Antunes
Honored Contributor

Re: Oracle tnsnames.ora

In the SQL*Net Easy configuration, create 2 TNS aliases, one with the host name and another with the host IP: if only the IP works it is because your client is in a different subnet than the database server...

Best Regards,

Eric Antunes


Each and every day is a good day to learn.
renarios
Trusted Contributor

Re: Oracle tnsnames.ora

The Oracle SID and domain do not compare to what's in the sqlnet.ora

Please try the following:
tnsping ORASERV.TEST01. Does that work?

Lookup in your sqlnet.ora what the value of your default_domain is (i.e. WORLD) and use that in your Oracle name. Thus, change 'ORASERV.TEST01 =' to TEST01.WORLD.

Check also that you can ping your oracle server (ping oraserv).

If you checked the above, you can also tnsping TEST01 and start an sqlplus session to it.

Please give some feedback, so we can help you out.

Cheers,

Renarios
Nothing is more successfull as failure
Yogeeraj_1
Honored Contributor

Re: Oracle tnsnames.ora

hi kalin,

just for a test, can you try to connect as:

sqlplus
scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraserv)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=test01)))

(nb. if you don't have the scott schema, you can always substitute accordinlgy)

thanks

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)