Once common issue related to data processing, involving batch scripts relates to Oracle Path Environment variable. Most of the time we defined and export the Oracle Database Environment variables in our unix scripts for example ORACLE_HOME, ORACLE_BASE, ORACLE_SID. However, there is one scenario where the ORACLE_SID will not work.
Let’s say that you have installed the Oracle Client on a server and then your database server is located on a remote server. The scenario is that in my batch processing i want to call SQLPlus with <username>/<password> only, i don’t want to specify the Oracle Identifier each time i want to perform a SQLPlus operation. Inorder to do that, i will add and export the ORACLE_SID and normally that should work.
However, if your database server is remote, by specifying the ORACLE_SID, it might still not work. In that case, you will need to define and export the TWO_TASK variable just like the ORACLE_SID and specify your identifier. By doing this, it will resolve the SQLPlus connection problem.
export ORACLE_SID=testdb
export TWO_TASK=testdb
Running the command: > sqlplus user/password , will result in the SQL prompt command.
If you want to troubleshoot your connection issues, some interesting commands are:
a) $TNS_ADMIN (to verify if you are using the proper tnsnames.ora)
b) tnsping <oracle identifier>, example tnsping testdb (It does not validate your database connection but verify your tns alias definition)
Reference Links:
https://docs.oracle.com/cd/E11882_01/server.112/e10839/admin_ora.htm#UNXAR117
