Running a oracle script for multible database on same server

[ashish_vikas]

Hello friends

I am thinking of creating a script in HP UX which will run a SQL script on multiple oracle in same server. I can do this for one oracle but i'm unable to do it for multiple database.

I need to do newid oraSID user every time for diff. database .. can u suggest something..

regards

Ashish

[markus_doehr2]

You can either use

"su - ora<sid> -c ...." as root user

in your script or set ORACLE_HOME and ORACLE_SID for the databases manually in the script, something like

#!/bin/ksh
#
for i in T01 T02 T03 T04 T04
do
  export ORACLE_HOME=/oracle/$i/102_64
  export ORACE_SID=$i
  sqlplus <user/password>@$i script.sql
done

Markus

[ashish_vikas]

Hi Markus,

thanks for your reply, i will try 2nd option ..

We don't have root access

regards

Ashish

[ashish_vikas]

Hi Markus,

Its not working .. When i run this script from my ID, it says SQLPLUS not found. see this .. i have created 1 sql file and 1 script file to call it with exporting ORACLE_HOME & ORACE_SID for one of the system E40.

Can you suggest something ..

*******************************************************************************************************************************************************

er3asu82:(/tmp)(xchason)#ll /tmp/testsql.sql /tmp/testscr.dat

-rwxrwxrwx 1 xchason dba 109 Aug 9 11:10 /tmp/testscr.dat

-rwxrwxrwx 1 xchason dba 32 Aug 9 11:08 /tmp/testsql.sql

er3asu82:(/tmp)(xchason)#cat /tmp/testsql.sql

select * from v$instance;

quit

er3asu82:(/tmp)(xchason)#cat /tmp/testscr.dat

#! /bin/ksh

export ORACLE_HOME=/oracle/E40/102_64

export ORACE_SID=E40

sqlplus "/as sysdba" @testsql.sql

er3asu82:(/tmp)(xchason)#/tmp/testscr.dat

/tmp/testscr.dat[5]: sqlplus: not found

*******************************************************************************************************************************************************

Again when i try to run this from other orasid, it does not work as well..

er3asu82:oraq52 1> /tmp/testscr.dat

Error 6 initializing SQL*Plus

Message file sp1<lang>.msb not found

SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

*******************************************************************************************************************************************************

thanks

ashish

[markus_doehr2]

> Its not working .. When i run this script from my ID, it says SQLPLUS not found. see this .. i have created 1 sql file and 1 script file to call it with exporting ORACLE_HOME & ORACE_SID for one of the system E40.

You have to set the complete environment of course. If sqlplus is not in your path then it can't work.

Add $ORACLE_HOME/bin to your PATH.

Markus

[ashish_vikas]

Hi Markus.

This is still not working .. I'm running my unix script from oras07 environment for E18 system..

cat /oracle/E18/testscr.dat

#! /bin/ksh

export ORACLE_PATH=/oracle/E18/102_64

export ORACLE_HOME=/oracle/E18/102_64

export ORACE_SID=E18

sqlplus "/as sysdba" @/tmp/testsql.sql

er3asu82:oras07 1> /tmp/testscr.dat

SQL*Plus: Release 10.2.0.2.0 - Production on Sun Aug 9 12:37:34 2009

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

Connected to an idle instance.

select * from v$instance

*

ERROR at line 1:

ORA-01034: ORACLE not available

Disconnected

thanks

Ashish

[markus_doehr2]

Is the user assigned to the "dba" group? works perfect here:

testuser@shell ~>cat ~/.profile 
#       This is the default standard profile provided to a user.
#       They are expected to edit it to meet their own needs.

MAIL=/usr/mail/${LOGNAME:?}

export ORACLE_HOME=/oracle/SHE/102_64
export ORACLE_SID=SHE

export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

export PS1="\u@\h \w>"

testuser@shell ~>cat test.sql 
select * from v$instance;

testuser@shell ~>sqlplus / as sysdba @test

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Aug 9 15:22:04 2009

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


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


INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 SHE
shell
10.2.0.4.0        09-JUL-09 OPEN         NO           1 STOPPED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO

[Former Member]

select * from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available

Try to start the oracle first and then give a go.

[ashish_vikas]

HI,

I know what ORA 01034 means.. oracle was always up..still didn't manage to solve this.

ashish

[markus_doehr2]

What's the problem?

Markus

[ashish_vikas]

Same problem.. but i am not using export PS1="\u@\h \w>" .. what is this for ..

[markus_doehr2]

> Same problem.. but i am not using export PS1="\u@\h \w>" .. what is this for ..

These are the settings for the prompt.

May need to explicity use a user to logon if there doesn't exist an OPS$ user in the target database for the user you're logged in and executing the script.

So you may try

sqlplus sys/<password>@<SID>

instead of "sqlplus / as sysdba"

Markus

[ashish_vikas]

thanks for help, let me explain my case ..

we have a HP UX server where we have 5 SAP systems..with their database on same box. We have unix id say mine is ashish . Now i want to run a .sql file on oracle db. what env i need to pass .. i dont have sql plus for my account.

Can i run similar .sql file using script from one of the database account say orap01 on database of of other sap say p02. what env i need ..

thanks

ashish

[markus_doehr2]

> we have a HP UX server where we have 5 SAP systems..with their database on same box. We have unix id say mine is ashish . Now i want to run a .sql file on oracle db. what env i need to pass

I understood your problem.

> .. i dont have sql plus for my account.

?? What do you mean with that?

> Can i run similar .sql file using script from one of the database account say orap01 on database of of other sap say p02. what env i need ..

Usually you need ORACLE_HOME and ORACLE_SID - but you need, of course, the permission to execute the programs in the corresponding /oracle/<SID>/102_64 subdirectories.

Markus