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