Oracle Remote Server

rajkraj · Post by **rajkraj** » Mon Apr 14, 2008 3:03 pm

Hi,

I have to connect to server abc,using usid :xyz and password :abc.
I have an entry in the TnsNames.ora file for the particular server.
I am able to login using Oracle Sql plus,but when i enter the same
details in Oracle Enterprise stage as remote server :abc,user id as xyz and password as abc it says TNS could not resolve service name.

Any reason why i can connect through Sql plus but not through Oracle Enterprise stage.

Thanks

ray.wurlod · Post by **ray.wurlod** » Mon Apr 14, 2008 4:25 pm

Are the Oracle environment variables - such as ORACLE_HOME - correctly set (in dsenv) for DataStage processes?

rajkraj · Post by **rajkraj** » Mon Apr 14, 2008 7:14 pm

Yes,they are set up correctly.
I am able to access another oracle server but not this one.

John Smith · Post by **John Smith** » Mon Apr 14, 2008 7:19 pm

Hi,

You should still check the Oracle Env variables. Being able to access another oracle database does not mean anything.
You could have 2 oracle home directories e.g due to upgrades etc and your DS is pointing to a different one.

Just my 2 cents.

rgds

ray.wurlod · Post by **ray.wurlod** » Mon Apr 14, 2008 7:41 pm

In that case you need to check that that particular entry in tnsnames.ora is correct.

rajkraj · Post by **rajkraj** » Mon Apr 14, 2008 7:44 pm

The entry in tnsnames.ora is correct,i have checked it.
I am able to connect through Oracle Sql plus,I checked all the parameters i am passing in the Oracle Enterprise stage too.
They are also set up correctly.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 14, 2008 7:50 pm

Good progress, we're eliminating some possibilities. You now need to check that the connection parameters in your DataStage job are correct.

Ideally these are job parameters, so you can just look at the "job started" event in the job log to determine which values were supplied.

Otherwise you have to inspect the job design itself, in particular the connection properties of every Oracle stage (including DRS with Oracle selected as the database type) in the job.

rajkraj · Post by **rajkraj** » Mon Apr 14, 2008 7:55 pm

I have checked the job log and the parameters are the same values(user_id,passowrd,Remote server) which i am using to connect through Oracle Sql Plus.

And i am using Oracle Enterprise stage to connect to Oracle.
I am passing values into Userid,Password and Remote Server.

John Smith · Post by **John Smith** » Mon Apr 14, 2008 11:07 pm

rajkraj wrote:I have checked the job log and the parameters are the same values(user_id,passowrd,Remote server) which i am using to connect through Oracle Sql Plus.

And i am using Oracle Enterprise stage to connect to Oracle.
I am passing values into Userid,Password and Remote Server.

Just curious,when you say Oracle SQL Plus do you mean the client version or running sqlplus in Unix? SQL Plus on the client uses the client's tnsnames.ora whereas the server uses the server copy. Just to be sure we're talking about the same things.

One other test: login to your unix server as the user that is used to run DS, source the dsenv file and then type "tnsping <Oracle server>"
and see if you get a response.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 14, 2008 11:24 pm

You can do that from the Administrator client if you can not access the server machine. Get to the command window for the project and execute the command

Code: Select all

SH -c "tnsping servername"

If you get "command not found" then your PATH does not include $ORACLE_HOME/bin, so try it entering the full pathname of the tnsping command.

rajkraj · Post by **rajkraj** » Tue Apr 15, 2008 7:14 am

when i enetered SH -c "tnsping servername1" in administrator,i got the below output

Used Parameter files /xx/orasoft/product/9.2.0/network/admin/sqlnet.ora.

USED HOSTNAME adapter to resolve the alias

Attemping to contact (DESCRIPTION=(CONNECT_DATA=(SID=*) (SERVICE_NAME=servername1.)) (ADDRESS=(PROTOCOL=TCP)(HOST=servername1)(PORT=xxx)))

OK

So by this i understand that it had connected to the server "servername1".
But when i tried to give the same name (servername1) as Remote Server
in the Oracle Enterprise stage, it says

ORA-12154 : TNS: could not resolve service name.

Can any one guide me in this matter.

Thanks

ray.wurlod · Post by **ray.wurlod** » Tue Apr 15, 2008 3:30 pm

There seems to be a "." character on the end of the SERVICENAME entry. Can you please verify?

rajkraj · Post by **rajkraj** » Tue Apr 15, 2008 6:44 pm

Yes,that was the problem with a ".".

Thanks

ray.wurlod · Post by **ray.wurlod** » Tue Apr 15, 2008 6:45 pm

Yay!
:D

pratyusha · Post by **pratyusha** » Thu Nov 05, 2009 8:16 am

I do not know if I can post on a resolved topic. But I am getting the same error what rajkraj has got. I too have the '.' at the end of the server name. But my tnsnames.ora file entry does not have this '.'. So from where we need to remove this '.'. Can someone please let me know

So many thanks
Prathyusha