DSXchange

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

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

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

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

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

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.

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.

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.

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.

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

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

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

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

Thanks

Yay!
:D

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