DSXchange

I am new to datastage and need help in debugging an existing oracle datastage connector stage job.
The job is using value file stored in ../ParameterSets/Ps_Src folder on unix. I see entry for db name , user name and password in the value file. So, I hoping to see the db name reference in tnsnames.ora file to see host details.

I checked log file for TNS_ADMIN but couldn't find it. The I looked for ORACLE_HOME path and then I found tnsnames.ora under $ORACLE_HOME/network/admin. It has bunch of entries but not the one I am looking for. how is then the oracle connection working because it is running file. What other location of tnsnames.ora should I be looking ?

Well, the tnsnames.ora file you found is in the default location that Oracle looks for it, which is the general answer why TNS_ADMIN doesn't need to be set. How are the connection properties in the stage setup? Specifically the "Server" information... as discussed here. From reading what's there, it seems to me like it may not even need the tnsnames file if you have an entry with "the complete content of the connect descriptor in the format that is used in the Oracle tnsnames.ora file" stashed away in there.

The schema/user, Database name and password and stored in value file. I am trying to figure out where is host[:port][/service_name] stored on the unix as mentioned in the link you posted

I have no DataStage access so can't check directly but did you check the property inside the stage that the article mentions?