Executing Oracle script from UNIX Script

Shell Programming and Scripting
1

Hi,

I am new to UNIX and want to execute oracle script from unix script.
I have written below script but i am getting below error.

#!/bin/bash
file="/home/usr/control/control_file1"
while read line
do
#$line=@$line
#echo $line
sqlplus tiger/scott@DB
@$line
exit
#echo "$line"
done <"$file"

Can someone please help me out to correct the error.
Thanks,
Vipin Kumar Rai

2

Here is a script that I wrote for analyzing schemas.
This should give you a frame work for writing your script.
The set_oracle_env.sh set ORACLE_HOME, TNS_ADMIN,
LS_LIBRARY_PATH and put the $ORACLE_HOME/bin at
the beginning of the PATH.

#!/bin/bash
# set -x
# This job simply runs dbms_stats.gather_schema_stats for the given DB and
# user on the command line.  It logs into the DB as the sysdba user and is
# run from the shell as the Oracle user.  It is executed from crontab file
# and not a DB job because I don't like the drift inherent in the DB jobs.
if [ $# -lt 2 ]; then
   echo "Usage: Sid_name schema_name"
   exit 1
fi
# Set the ORACLE_SID with the first parameter.
. ${HOME}/scripts/bin/set_oracle_env.sh ${1}
export USER=$2
# Log into the DB through SQLPLUS.  You must be the oracle user.
$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' << EOF
set term off
set echo off
set feedback off
begin
dbms_stats.gather_schema_stats (ownname=>'$2',granularity=>'ALL',cascade=>true);
end;
/
exit
EOF
3

I don't see the error in your post.

From Oracle Docs:-

@{url|file_name[.ext] } [arg...]

"at" sign expects a file name or URL. Verify you are passing file name/URL.

4

Hi, I hope this may be helpful.

We use a HP EVA SAN, and I need to run a lot of jobs to replicate a running database using RSM (Replication Solutions Manager) to put my running database into Hot backup mode, shutting down a running database etc.

In this example I need to shutdown a replicated database so that my RSM process can create a fresh replication.

You will notice that, in this case, I am using a remote shell to run the Oracle command. That is because I have a Tru64 Cluster of 5 hosts.
The RSM host agent can only run on one of the cluster members, and the database I want to shutdown is on one of the other cluster members.

Best of luck

sdunor09 #more shutdown_RPT1_rsh.ksh
#! /bin/ksh
#
##########################################################################
#
# File:         /RSM_Host_actions/UNIX_actions/shutdown_RPT1_rsh.ksh
# Usage:        Always run from a scheduled RSM job
# Author:       smckibbin
# Purpose:      This script runs an Oracle shutdown from a RSM job using rsh to cluster member sdunor10
# Version:
#               0.1 20130815 testing for RPT1 processing.
#               0.2 20130821 ready for production testing
#               0.3 20150310 revise for changed FQDN
#                         
###################################################################
# un-comment to run in debug
#set -x
###########################################################################
# set the constants
# source unix machine constants
#--------------------------------------------------------------------------
export BOX=`hostname -s | tr [A-Z] [a-z]`  MYTIME=`date '+%m-%d-%Y-%H%M'`  MO=$1  DY=$2  YR=$3
export LOGDB="/RSM_Host_actions/UNIX_actions/log/is_database_running.txt"
export ADMINS="user1@xyz.com uset2@ABC.com"
 
#this is a test to see which HOST is returned when run using rsh
#------------------------------------------------------------------
if [ `hostname -s` != "sdunor10" -o `whoami` != "root" ]; then
  echo "#########################################################"
  echo ""
  echo "This script has failed!!!!!!!"
  echo ""
  echo "You must execute this script as root from node sdunor10"
  echo ""
  echo "#########################################################"
  echo $BOX $0 |mailx -s "CANNOT run script on host other than sdunor10" $ADMINS
  #exit 1   exit with error will KILL the RSM job so exit 0 and let RSM clobber the database
  exit 0    #exit here because we don't want to run the heart of the script on the wrong host
fi
 
#------------------------------------------------------------------
# determine if the master Oracle process is running for the instance RPT1
  ps -ef | grep smon_RPT1 > $LOGDB
  # read the output file, looking for a line starting with oracle, use the value of field No. 8 
  masterdb=`more $LOGDB | awk '$1 ~ /oracle/ { print  $8 }'`
  if  [ $masterdb = ora_smon_RPT1 ]
      then
       #the master Oracle process is running, hence the database is running
       #run a remote rsh command as oracle to shut-down the database
      rsh sdunor10 '/usr/bin/su - oracle -c "/RSM_Host_actions/ORACLE_actions/RPT1_10g/stopdb_rpt1.sh"'
      exit 0
      else
       #the master Oracle process is NOT running, exit with success back to RSM
       exit0
  fi
echo "finished running script $0"
 
exit 0
#  script is  shutdown_RPT1_rsh.ksh