Oracle tnsnames.ora

[Kalin Evtimov]

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)
)
)

[Tomek Gryszkiewicz · Accepted solution]

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

[Kalin Evtimov]

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]

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"

[Oviwan]

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]

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.

[Kalin Evtimov]

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

[Oviwan]

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]

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

[Simon Wickham_6]

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]

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]

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

[Eric Antunes]

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

[Eric Antunes]

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

[renarios]

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

[Yogeeraj_1]

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