tnsnames.ora

[djam]

Hi,
I'm trying to connect to a database and not sure what I'm doing wrong. I changed the tnsnames.ora file and moved it to oracle\ora92\network\ADMIN
Can I just open up SQL Plus and it will directly connect me to this database? Do I need to do anything else?
When I run SQL Plus, it just freezes up.

Here is the only info that I have eg.

Host=214.104.147.212
SID=JAM

I have username and pwd

Here is what I've done to the tnsnames.ora file

DJAM= [ (DESCRIPTION_LIST =
(DESCRIPTION=
[ (SDU=2048) ]
[ (ADDRESS_LIST=
(ADDRESS=
[ (COMMUNITY=<community_name>) ]
(PROTOCOL=tcp)
(HOST=214.104.147.212)
(PORT=1521)
)
[ (ADDRESS=
(PROTOCOL=ipc)
(KEY=PNPKEY)
)
]
[ (ADDRESS=
[ (COMMUNITY=<community_name>) ]
(PROTOCOL=decnet)
(NODE=<nodename>)
(OBJECT=<objectname>)
)
]
... # More addresses
[ ) ]
[ (CONNECT_DATA=
(SID=JAM)
[ (GLOBAL_NAME=<global_database_name>) ]
)
]
[ (SOURCE_ROUTE=yes) ]
)

Any help would be great
THANKS

&quot; ahhh computers, how they made our lives much simpler &quot;

 

[rzs0502]

Moving your TNS from the default location would be fine as long as the registry value corresponds.
[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\TNS_ADMIN=D:\ORA...]

If you can then TNSPING the database, you should be fine.
As for SQLPlus hanging, it should not if it cannot read TNS.
Check your path settings to make sure you have an entry for Oracle ie. d:\oracle\ora9\bin

 

[djam]

there was no TNS_ADMIN in the registry... so i added it?

I can TNSPING to the database
The path is set
SQLPlus is still hanging and I still can't connect to the database.

I'm using Aqua Data Studio right now, it's very easy to connect to the database. But I would like to get this to work soon.

Anymore suggestions? Is the tnsnames.ora correct?

thanks

&quot; ahhh computers, how they made our lives much simpler &quot;

 

[rzs0502]

If your tnsping and Aqua work, then your tnsnames.ora is fine.
Are you using SQLPlus in DOS or the Win32 version
Can you SQLPlus to this database from another machine.

Could be a problem with the actual sqlplus .exe


tcpnew1 = (description=
(source_route=yes)
(address=(protocol=tcp)(port=1610)(host=spcstn))
(address=(protocol=tcp)(port=1580)(host=spcstn)))
(connect_data=(service=sales1))
)

spx2tcp = (description=
(source_route=yes)
(address=(protocol=spx)(service=orasrvc1))
(address=(protocol=tcp)(port=1580)(host=spcstn)))
(connect_data=(service_name=sales2))

 

[palmela17]

try:
SQL> connect username/password@jam

also give hostname of the machine instead of ip address in the concerned files.

best of luck

 

[muzz1e]

The tnsping is a valid suggestion, but it doesn't confirm your tnsnames is ok, it just confirms a listener is listening on that socket.
Firstly I would make sure that you are using the tnsping from oracle\ora92\bin, which would use the tnsnames file from oracle\ora92\network\ADMIN
Assuming you can do this:
oracle\ora92\bin\tnsping.exe DJAM
then chances are you will be able to do the following
oracle\ora92\bin\sqlplus.exe <username/password>@DJAM
Lots of things can make this fail though.
Try it and post the output

 

[sem]

If you try to connect to LOCAL database, the only thing you really need is to set environment variable ORACLE_SID. In your case (back slashes in path show that you're on Windows):

set ORACLE_SID=JAM
SQLPLUS SCOTT/TIGER


Regards, Dima

 

[sem]

One more precaution: this doesn't work if LOCAL environment variable is defined. This variable points to DEFAULT tns alias.

Regards, Dima

 

[djam]

Got it...

Ya.. tnsping doesn't tell you anything, even if you can connect with sqltools and Aqua Data Studio, it doesn't mean that the tnsnames.ora is working or not.

I was missing the service name. It was a little confusing using the template tnsnames.ora provided.

Now it works fine...

thanks for you help


djam =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 111.111.111.11)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = djam.blah.com)

)

)


&quot; ahhh computers, how they made our lives much simpler &quot;

 

[appi]

Hi,

I guess what happens here is that Oracle changes their parameters.
They use in 9i the SERVICE_NAME instead of SID, which is used in 8i.
rgds
Uwe

 

[sem]

In fact SERVICE_NAME is not the same as SID. Especially in OPS or its more recent reincarnation RAC.

Regards, Dima