Creating wrapper for another user

[Kalin Evtimov]

Hi!

I have following problem:

I vreated a script for Oracle9i that just does a simple Select-Query. If I run it under user oracle, I have no problems. But it hast to be run by another tool as root. This is why I wrote a second script, that just does

su - oracle -c "oracle-script.pl"

But I get Error (here is a part from the output)
___________________________________________
SQL> Connected to an idle instance.
SQL> SELECT to_char(sysDATE,'DD-MON-YY HH24:MI:SS') Zeit FROM dual
*
ERROR at line 1:
ORA-01034: ORACLE not available


SQL> Disconnected
logout
---------------------------------------------

For me, he cannot find some ENV-Vars, but I set them all within the sciprt.

Do you have any idea how to solve this?

Regards!

Kalin

[Peter Godron]

Kalin,
jsut before the sqlplus command is issued from the script, display all your env-vars with "env | grep -i oracle".

You should defioned have at least:
ORACLE_HOME
ORACLE_SID

Also is the database up and running?

[Muthukumar_5]

Try this in your environment:

Login as oracle user:

Oracle$ env > /tmp/oracle.env

Login as root user.

root#su - oracle -c "env > /tmp/root.env"
root#diff /tmp/oracle.env /tmp/root.env

will tell you the reason.

--
Muthu

[Kalin Evtimov]

I have set ORACLE_HOME and ORACLE_SID
Database is up.

Under root the oracle-script itself says
SQL> SQL> ERROR:
ORA-01031: insufficient privileges
which is what u usually get if doing some queries under root.

But why didn't the wrapper worked, which calls the oracle script with user oracle, is a secret for me.

[Kalin Evtimov]

Comparing was a great idea!

For some reason logging with su doesn't take the SID.
Is there a way for specifying environment variables in the same session opened from su?

[Arunvijai_4 · Accepted solution]

Hi Kalin,

There is a variable called SU_KEEP_ENV_VARS, Check man su for more information about how to configure ?

-Arun

[Peter Godron]

Kalin,
where did you set the ORACLE_SID?
Within your script or oracle's .profile?

[Kalin Evtimov]

Well, ORACLE_SID is set nowhere, and I usually set it manually under oracle when I have to do something with the corresponding database.

I tried setting it up from the oracle script, getting it as a call-parameter from the root-script, but no success.

But there must be a sollution. Until now I have found solutions for many crazy problems :)

[Arunvijai_4]

Hi Kalin,

Instead of setting manually, try to set in .profile for oracle user.

-Arun

[Kalin Evtimov]

Oh, hardcoding solved the problem I think, so there must be something wrong with the programming...

Thanks to everyone!

[Peter Godron]

Kalin,
I tested this on my machine:

su - oracle

grep ORACLE .profile
gave:
ORACLE_HOME=/u01/app/oracle/product/10g
export ORACLE_HOME
ORACLE_BASE=/u01/app/oracle/product
export ORACLE_BASE
PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
ORACLE_SID=tpol
export ORACLE_SID
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/dt/lib:/usr/lib; export LD_LIBRARY_PATH
SHLIB_PATH=$ORACLE_HOME/lib:/usr/dt/lib:/usr/lib; export SHLIB_PATH

Then created in /tmp
a.sh
#!/usr/bin/sh
cd /tmp
sqlplus system/manager @a.sql

and a.sql
select sysdate from dual;
exit;

chmod 777 a.sh a.sql

exit back to root user
I can then call:
su - oracle -c "/tmp/a.sh"