Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Help please! Oracle scripting

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help please! Oracle scripting
# 1  
Old 03-30-2011
Help please! Oracle scripting
Hi All,

I'm completely new to shell scripting and i've found some old code (10 years), which would be suitable for me to facilitate database shutdowns throughout our systems. I'm on AIX 6 and i'm using 11gr2 Oracle.

The problems seems to be with while read portion of this code:

Code:
FLAG_SET=0
cat "$ORATAB_LOC" | sed -n '/^[^#||^*]/ p'| awk -F: '{if ("$2" -eq "$ORACLE_HOME") print $1}'|sort |&
while read -p  ORASID
do
ORAUP=`ps -ef |grep ora_pmon_"$ORASID"| grep -v grep`

if [ -z "$ORAUP" ]
then
echo "\t\t$ORASID"
FLAG_SET=1
fi
done

if [ $FLAG_SET -eq 0 ]; then
echo "\n\n\t All database instances are up and running."
echo "\t If you need to start, then you first have to shutdown the instance\n\n"
exit 0;
fi

When i run this portion of the script i get :

Code:
awk: 0602-562 Field $() is not correct.
 The input line number is 1.
 The source line number is 1.

Can anyone give me some pointers please.

Thanks in advance!
Moderator's Comments:
Mod Comment
Please use code tags when posting data and code samples!
Last edited by vgersh99; 03-30-2011 at 12:20 PM.. Reason: code tags, please!
# 2  
Old 03-30-2011
Could you tell us how you are running the script?

Example:
Code:
<your_script_name> <parameter1> <parameter2>

# 3  
Old 03-30-2011
Hi Shell life,

Thanks for your reply.

This is a small part of the overall script. I found it posted on another forum but it needs updating:

Code:
#!/bin/ksh

################################################################################
#########
#
#
# Purpose: This shell program would allow a user to startup/shutdown some of the
# databases and related applications, in an interactive manner. 
# Some of the applications are as follows
#
#
#
# Oracle Database Instances
#
# Oracle Default Listener
#
# Oracle Intelligent Agent
#
# Oracle Data Gatherer
#
# Oracle Enterprise Management Server
#
# <b>Use at your own risk </b>
################################################################################
#########


#######################################################
# #
# Set the Environment variables #
# #
#######################################################
set -a

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
if [ ! -d $ORACLE_HOME ]; then

echo "Cannot find the $ORACLE_HOME.\n Exiting. "
exit 1;

fi
export ORACLE_HOME

OEM_HOME="/u01/app/oracle/product/11.2.0/dbhome_1"
if [ ! -d $OEM_HOME ]
then

echo "Cannot find the $OEM_HOME.\n Exiting"
exit 1;

fi
export OEM_HOME


ORATAB_LOC=/etc/oratab
HOSTN=`uname -n`
################################################################################
#########
#
#
# Interactive mode Module
#
#
#
#
#
# Description: This module would post questions to the user and based on the
#
# replies perform the operation, against the database. The way
#
# this module had been designed is as follows
#
#
#
# 1. User would be prompted for the type of operation (start/stop)
#
#
#
# 2. The user would then be provided with the series of operation
#
# that he/she could perform form his/her previous choice of
#
# start/stop. Say for example that the user wants to perform
#
# a startup of listeners, then he/she would have to choose
#
# "lsnr". If the opertion is to start/stop the database then
#
# the user would be prompted with the different instances that
#
# he/she could start
#
#
#
################################################################################
#########

echo "Please specify the type of operation that you would like to perform ?\n"
echo "\t start\n\t stop\n\n"
echo "Enter your choice: "; read RESPONSE

case "$RESPONSE" in

'start') echo "Your choice was $RESPONSE"
echo "\n\n\tPlease specify the application that you would like to start:\n"
echo "\t1) Database db\n"
echo "\t2) Listener lsnr\n"
echo "\t3) OEM oem\n"
echo "\t4) OAS oas\n"
echo "\t5) All all\n\n"

echo "\tEnter your choice :"
read APPLICATION

case "$APPLICATION" in

'db') echo "\tPlease choose the database that you want to $RESPONSE\n"

#------------------------------------------------------------------------------------
#
# Read all the database instance names from the oratab file and then check to
# make sure that they are not up and running. Lists only those instance names
# to the user to perform the statup operation.
#
#----------------------------------------------------------
--------------------------
FLAG_SET=0
cat "$ORATAB_LOC" | sed -n '/^[^#||^*]/ p'| awk -F: '{if ("$2" -eq "$ORACLE_HOME") print $1}'|sort |&
while read -p  ORASID
do
ORAUP=`ps -ef |grep ora_pmon_"$ORASID"| grep -v grep`

if [ -z "$ORAUP" ]
then
echo "\t\t$ORASID"
FLAG_SET=1
fi
done

if [ $FLAG_SET -eq 0 ]; then
echo "\n\n\t All database instances are up and running."
echo "\t If you need to start, then you first have to shutdown the instance\n\n"
exit 0;
fi

#-------------------------------------------------------
#
# Read the instance names from the user input and
# start those instances on the host system
#
#-------------------------------------------------------
echo "Enter the database name(s):"
read ORASID

for SID in $ORASID
do
echo "\n";
ORACLE_SID=$SID
export ORACLE_SID
echo $RESPONSE"ing database instance $SID"
$ORACLE_HOME/bin/svrmgrl <<- END_OF_FILE
connect internal
startup
select instance_name, version, database_status from v/$instance;
END_OF_FILE
done
break;;

'lsnr') #----------------------------------------------------------
#
# Start the listeners that are being used by different
# database applications
#
#---------------------------------------------------------

$ORACLE_HOME/bin/lsnrctl start
$ORACLE_HOME/bin/lsnrctl services
$ORACLE_HOME/bin/lsnrctl status


#======================================
# Make sure that the process had been
# started on the system
#======================================
ORALSNR=`ps -ef|grep $ORACLE_HOME | grep -v grep |awk '$(NF-2) ~/tnslsnr/ {print $(NF-1)}'`
if [ -z "$ORALSNR" ]
then

echo "\n\nThe listener has not been started. "

fi

#-----------------------------------------------------
# Check the status of the intelligent agent
#-----------------------------------------------------
ORAGENT=`ps -ef|grep dbsnmp|grep -v grep`
if [ -z "$ORAGENT" ]; then

echo "\n\nThe oracle intelligent agent has not been started."
echo "Perform a trouble shoot "

fi

#-------------------------------------------
# Check the status of the Data gatherer
#-------------------------------------------
ORAGATHER=`ps -ef|grep $ORACLE_HOME | grep -v grep |awk -F/ '{if ($NF == "vppdc") print $NF}'`
if [ -z "$ORAGATHER" ]
then

echo "\n\nThe oracle data gatherer is not started"
fi
break;;

'oem') #-----------------------------------------------------------
#
# Start the Oracle Enterprise Management Server
#
#----------------------------------------------------------
$OEM_HOME/bin/oemctrl start oms |&wait
$OEM_HOME/bin/oemctrl status oms sysman/Enterthepassword

ORAOEM=`ps -ef |grep $ORACLE_HOME |grep -v grep |awk -F/ '{ if($NF == "launchOms") print $NF}'`
if [ -z "$ORAOEM" ]
then
echo "\n\nThe Oracle Enterprise Manager had not been started "
fi
break;;

'oas') echo "execute the environment setup script and proceed further"
echo "force clean"
echo "start the node manager"
echo "start the oracle web application server"
break;;

'all') #----------------------------------------------------------------------------------------
#
# This would start all the databases that are down and then the listeners, the oracle
# intelligent agent, oracle data gatherer and then the oracle management server
#
#---------------------------------------------------------------------------------------
cat "$ORATAB_LOC" | sed -n '/^[^#||^*]/ p'| awk -F: '{if ($2 -eq $ORACLE_HOME) print $1}' | sort |&
while read -p ORASID
do

ORAUP=`ps -ef|grep ora_pmon_"$ORASID"| grep -v grep`

if [ -z "$ORAUP" ]
then
echo "\n\n"
echo $RESPONSE"ing the instance $ORASID"
ORACLE_SID=$ORASID
export ORACLE_SID
$ORACLE_HOME/bin/svrmgrl <<- EOF
connect internal
startup
select instance_name, version, database_status from v/$instance;
EOF
fi
done
echo "\n\n Instance startup successful"

#---------------------------------------
#
# Start the listeners on the host
#
#---------------------------------------

echo "\n Starting the Listeners "
$ORACLE_HOME/bin/lsnrctl << EOF
start
services
status
EOF
#======================================
# Make sure that the process had been
# started on the system
#======================================
ORALSNR=`ps -ef|grep $ORACLE_HOME|grep -v grep |awk '$(NF-2) ~/tnslsnr/ {print $(NF-1)}'`
if [ -z "$ORALSNR" ]
then

echo "\n\nThe listener has not beed started. "

fi

#
# Check the status of the intelligent agent
#
ORAGENT=`ps -ef|grep dbsnmp |grep -v grep`
if [ -z "$ORAGENT" ]; then

echo "\n\nThe oracle intelligent agent has not been started."
echo "Perform a trouble shoot "

fi

echo "\n Starting the datagatherer "
$ORACLE_HOME/bin/vppcntl -start
$ORACLE_HOME/bin/vppcntl -ping

#-------------------------------------------------
# Check the status of the Data gatherer
#-------------------------------------------------
ORAGATHER=`ps -ef|grep $ORACLE_HOME |grep -v grep |awk -F/ '{if ($NF == "vppdc") print $NF}'`
if [ -z "$ORAGATHER" ]
then

echo "\n\nThe oracle data gatherer is not started"
fi

echo "\n\nStarting up of listeners successful"

#---------------------------------------------------
#
# Start the oracle enterprise management server
#
#---------------------------------------------------

echo "Starting Oracle Enterprise Management Server\n"
$OEM_HOME/bin/oemctrl start oms|&wait
$OEM_HOME/bin/oemctrl status oms sysman/Enterthepasswordhere

ORAOEM=`ps -ef |grep $ORACLE_HOME |grep -v grep |awk -F/ '{ if($NF == "launchOms") print $NF}'`
if [ -z "$ORAOEM" ]
then
echo "\n\nThe Oracle Enterprise Manager had not been started "
fi

exit 0;;

*) echo "Sorry you have to specify the operation"
exit 1;;

esac
exit 0;;


'stop') echo "Your choice was $RESPONSE"
echo "\n\n\tPlease specify the application that you would like to $RESPONSE:\n"
echo "\t1) Database db\n"
echo "\t2) Listener lsnr\n"
echo "\t3) OEM oem\n"
echo "\t4) OAS oas\n"
echo "\t5) ALL all\n\n"

echo "\tEnter your choice :"
read APPLICATION

case "$APPLICATION" in

'db') echo "\tPlease choose the database that you want to $RESPONSE\n"

#---------------------------------------------------------------------------
#
# Read the Instance name from the oratab file and display it to the user
# only those instances that are up and running currently, to allow him/her
# to do a shutdown operation
#
#----------------------------------------------------------------------------
FLAG_SET=0
cat "$ORATAB_LOC" | sed -n '/^[^#||^*]/ p'| awk -F: '{if ("$2" -eq "$ORACLE_HOME") print $1}'|sort |&
while read -p ORASID
do
ORADOWN=`ps -ef |grep ora_pmon_"$ORASID" |grep -v grep`

if [ -n "$ORADOWN" ]
then
echo "\t\t$ORASID"
FLAG_SET=1
fi
done

if [ $FLAG_SET -eq 0 ]; then
echo "\n\n\t All your database instances were down now."
echo "\t They need to be running inorder to do a shutdown\n\n"
exit 0;
fi

#---------------------------------------------------
#
# If the databases are up and if the user gives
# the instance names, then read them one by one
# and stop the instance(s)
#
#----------------------------------------------------
echo "Enter the database name(s):"
read ORASID

for SID in $ORASID
do
echo "\n";
ORACLE_SID=$SID
export ORACLE_SID
echo $RESPONSE"ing database instance $SID"
$ORACLE_HOME/bin/svrmgrl <<- END_OF_FILE
connect internal
select instance_name, version, database_status from v\$instance;
shutdown immediate
END_OF_FILE
done
break;;

'lsnr') #------------------------------------------------------
# Stop the listeners that the database has been
# using for different kind of purposes
#
#-----------------------------------------------------

$ORACLE_HOME/bin/lsnrctl << EOF
stop
services
status
EOF
#======================================
# Make sure that the process had been
# stopped on the system
#======================================
ORALSNR=`ps -ef|grep $ORACLE_HOME |grep -v grep |awk '$(NF-2) ~/tnslsnr/ {print $(NF-1)}'`
if [ -n "$ORALSNR" ]
then

echo "\n\nThe listener has not beed stopped. "

fi

#------------------------------------------------
# Check the status of the intelligent agent
#------------------------------------------------
ORAGENT=`ps -ef|grep dbsnmp |grep -v grep`
if [ -n "$ORAGENT" ]; then

echo "\n\nThe oracle intelligent agent has not been stopped."

fi

$ORACLE_HOME/bin/vppcntl -stop

#
# Check the status of the Data gatherer
#
ORAGATHER=`ps -ef|grep $ORACLE_HOME | grep -v grep |awk -F/ '{if ($NF == "vppdc") print $NF}'`
if [ -n "$ORAGATHER" ]
then

echo "\n\nThe oracle data gatherer is not stopped"
fi

break;;


'oem') $OEM_HOME/bin/oemctrl stop oms sysman/Enterthepasswordhere
$OEM_HOME/bin/oemctrl status oms sysman/Enterthepasswordhere

ORAOEM=`ps -ef |grep $ORACLE_HOME | grep -v grep |awk -F/
'{ if($NF == "launchOms") print $NF}'`
if [ -n "$ORAOEM" ]
then
echo "\n\nOracle Enterprise Manager has not been stopped "
fi
break;;

'oas') echo "execute the environment setup script and proceed further"
echo "force clean"
echo "start the node manager"
echo "start the oracle web application server"
break;;

'all') #----------------------------------------------------------------------------------------
#
# This would stop all the databases that are up and then the listeners, the oracle
# intelligent agent, oracle data gatherer and then the oracle management server
#
#---------------------------------------------------------------------------------------
cat "$ORATAB_LOC" | sed -n '/^[^#||^*]/ p'| awk -F: '{if ("$2" -eq "$ORACLE_HOME") print $1}' | sort |&
while read -p ORASID
do

ORADOWN=`ps -ef|grep ora_pmon_"$ORASID" | grep -v grep`

if [ -n "$ORADOWN" ]
then
echo $RESPONSE"ing the instance $ORASID"
ORACLE_SID=$ORASID
export ORACLE_SID
$ORACLE_HOME/bin/svrmgrl <<- EOF
connect internal
select instance_name, version, database_status from v\$instance;
shutdown immediate
EOF
fi
done
echo "\n\n Instance shutdown successful"

#---------------------------------------
#
# Stop the listeners on the host
#
#---------------------------------------

echo "\n Stopping the Listeners "
$ORACLE_HOME/bin/lsnrctl << EOF
services
status
stop
EOF
#======================================
# Make sure that the process had been
# stopped on the system
#======================================
ORALSNR=`ps -ef|grep $ORACLE_HOME | grep -v grep |awk '$(NF-2) ~/tnslsnr/ {print $(NF-1)}'`
if [ -n "$ORALSNR" ]
then

echo "\n\nThe listener has not been stopped. "

fi

#--------------------------------------------------
# Check the status of the intelligent agent
#-------------------------------------------------
sleep 2
ORAGENT=`ps -ef|grep dbsnmp`
if [ -n "$ORAGENT" ]; then

echo "\n\nThe oracle intelligent agent had not been stopped."

fi


echo "\n Stopping the datagatherer "
$ORACLE_HOME/bin/vppcntl -stop
$ORACLE_HOME/bin/vppcntl -ping

#----------------------------------------------
# Check the status of the Data gatherer
#----------------------------------------------
ORAGATHER=`ps -ef|grep $ORACLE_HOME | grep -v grep |awk -F/ '{if ($NF == "vppdc") print $NF}'`
if [ -n "$ORAGATHER" ]
then

echo "\n\nThe oracle data gatherer is not stopped"
fi



#---------------------------------------------------
#
# Stop the oracle enterprise management server
#
#---------------------------------------------------

echo "Stopping Oracle Enterprise Management Server\n"
$OEM_HOME/bin/oemctrl stop oms sysman/T3l3ph0n3

ORAOEM=`ps -ef |grep $ORACLE_HOME | grep -v grep |awk -F/ '{ if($NF == "launchOms") print $NF}'`
if [ -n "$ORAOEM" ]
then
echo "\n\nThe Oracle Enterprise Manager had not been stopped "
fi

exit 0;;



*) echo "Sorry you have to specify the operation"
exit 1;;

esac

exit 0;;

*) echo "Please enter the operation you want to perform"
exit 1;;
esac



I've been executing it with sh -x <script_name>

Hope this helps - i am a novice with scripting!!
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Forum Support Area for Unregistered Users & Account Problems

Creation of Oracle table through shell scripting

Hi, I am trying to create a table through shell scripting . I used a command sqlplus -s to connect to sqlplus and user as sysdba. but there is a problem in it. can anyone please solve this . #!/bin/bash $(`sqlplus -s / as sysdba <<eof create table sample (id,int); insert into sample... (1 Reply)
Discussion started by: Unregistered
1 Replies

2. UNIX for Dummies Questions & Answers

Oracle cursors in UNIX shell scripting

Hello, I need to get all the members information from an oracle table whose flag value is enabled. Later on i need to perform several computation based upon the flag value and other columns. For example, Member ID Flag Frequency date 1 ... (2 Replies)
Discussion started by: Krishraj
2 Replies

3. Shell Programming and Scripting

How to update a Oracle table through shell scripting?

My Code is get_week_date() { `sqlplus -s ${DQM_SQL_LOGON}@${DQM_SID} << EOF SET ECHO OFF SET FEEDBACK OFF SET PAGES 0 SET SERVEROUTPUT ON SET VERIFY OFF SET TRIMSPOOL ON (update file_level_qc fq set FQ.DATA_FILE_NAME='Hyvee_Pharmacy_Solutions_201304_v1.txt'... (2 Replies)
Discussion started by: karthick.cho
2 Replies

4. Shell Programming and Scripting

Error in Scripting to monitor tablespace in Oracle DB

Buddies, I am writing the below script 'tab.sh' to monitor the tablespaces in Database:- ------------------------------- export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2 export PATH=$PATH:$ORACLE_HOME/bin export ORACLE_SID=orcl sqlplus system/oracle as sysdba <<EOF spool... (5 Replies)
Discussion started by: sandip250382
5 Replies

5. Shell Programming and Scripting

Help needed for shell scripting for oracle.

Hi, Please see contains both files created for automating the data from oracle through shell. 1)a_p.ksh #!/bin/ksh LOG=/home/A_P.log MESSAGE=/home/MESSAGE_A_P.txt mail_list=/home/AP_MAIL_LIST.txt data=/home/spooled_A_P.log echo "`date` Starting execution for A_P COUNT" > $LOG ... (2 Replies)
Discussion started by: fidelis
2 Replies

6. Shell Programming and Scripting

Help need urgently for oracle cursors in k shell scripting

Hi, My Oracle Stored procedure returns sys_refcursor to shell script. I have to iterate through it in script and use those retrieved values further in my script. I am using K Shell Scrpting. Stored Procedure is: create or replace PROCEDURE p_test(job_id IN VARCHAR2, c1 OUT SYS_REFCURSOR)... (4 Replies)
Discussion started by: rajeshorpu
4 Replies

7. Shell Programming and Scripting

UNIX shell scripting for retrieving from oracle

Hello folks, Please find the below code:(sample5.sh -> filename) echo "Selecting dat afrom Cause code" echo "set appinfo Causecode $preamble set serveroutput on size 10000 select * from RMI003_CAUSE_CODE /" | sqlplus -S $username@$hoststring/$password >> test2.dat When i tried executing... (5 Replies)
Discussion started by: sundar_ravi4
5 Replies

8. Shell Programming and Scripting

Shell Scripting with Oracle...

Hi guys, I have searched all the forums and searched the net as much as possible, but I carn't find any tutorials about shell scripting with Oracle. Has anyone come across any? Thanks for reading and in advance of posts, B14... (2 Replies)
Discussion started by: B14speedfreak
2 Replies

9. Shell Programming and Scripting

Unix and Oracle scripting

Hello all, I have a script that is fully functional and satisfactory in oracle. I recently started spooling it's output into unix for processing and manipulation. I do have a few problems however: The output of some columns are altered in the unix output: 1) On oracle, the date comes with a... (7 Replies)
Discussion started by: Khoomfire
7 Replies
Login or Register to Ask a Question