Tuesday, October 20, 2009

Oracle Wallet and tracing didn't work when executed by cron jobs

For those who may find it helpful, here's a problem I ran into along with the solution.

When a Korn shell script using Oracle 10gR2 client's sqlplus was run manually at the command prompt, the script successfully used an Oracle Wallet to log in and run an SQL script.

However, when the same ksh script was run with a cron job, I would get the following error:
"ORA-12534: TNS:operation not supported".

When the ksh script was changed to use a hard coded user name and password instead of the Oracle Wallet, the script ran fine when run as a cron job or when run manually.

The script sets and exports the TNS_ADMIN variable so that the script will know where to find the customized sqlnet.ora file which contains the Oracle Wallet information. I added a line to the script to print the TNS_ADMIN variable's value right before the script attempted to run sqlplus, and verified that the value of the variable was the same whether I ran the script manually or with a cron job. The TNS_ADMIN variable's value is that of the path that contains the custom sqlnet.ora file with the Oracle Wallet information.

To further troubleshoot this problem, I tried turning on the Oracle client's tracing feature by editing the customized sqlnet.ora file. I found that the tracing feature worked when I ran the script manually, but the trace didn't work when the script was run by the cron job. This told me that the cron job wasn't using the sqlnet.ora file I was editing, even though I could see that the cron job was picking up the correct TNS_ADMIN variable value.

I found that that the user running the cron job had a hidden file, .sqlnet.ora, in the user's home directory. After I renamed the hidden file, the cron job then used the sqlnet.ora file in the directory specified by the TNS_ADMIN variable.

1 comment:

malli said...

Hi Rich,

ewallet.p12,cwallet.sso also needs to have read permission if the jobs are executed from different Os Id.

Eg: Didnt worked:

-rw------- 1 oracle oinstall 8280 Jun 01 16:20 ewallet.p12
-rw------- 1 oracle oinstall 8308 Jun 01 16:20 cwallet.sso


jobs ran from test OS id.

sqlplus /@test

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 2 12:42:35 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-12534: TNS:operation not supported


Worked:

total 56
-rwxr-xr-x 1 oracle oinstall 8280 Jun 01 16:20 ewallet.p12
-rwxr-xr-x 1 oracle oinstall 8308 Jun 01 16:20 cwallet.sso


$ sqlplus /@test

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 2 12:46:52 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> show user
USER is "TEST"