Home » Infrastructure » Unix » Executing SQL STATEMENT from Shell
Executing SQL STATEMENT from Shell [message #97542] Thu, 18 July 2002 22:53 Go to next message
Rajesh Kumar
Messages: 3
Registered: July 2002
Junior Member
Hi,
I want to SHUTDOWN AND STARTUP the Oracle database from my UNIX CRON JOB shell script. WITHOUT executing a SQL file, can i shutdown the database and startup ?

This script i want to write for to take the Closed Consistency Full Backup.

Rajesh
Re: Executing SQL STATEMENT from Shell [message #97544 is a reply to message #97542] Fri, 19 July 2002 09:48 Go to previous message
Grant
Messages: 578
Registered: January 2002
Senior Member
I created this script to shut all DB's down. You are welcome to modify it as you see fit. You can also modify it to startup the DB's.

#!/bin/ksh
#
# Script to shutdown all databases and listeners that are running.
#
# Created 12-19-01 Grant Howell
#
BOLD=`tput smso`
OFF=`tput rmso`

# Ask to make sure
echo ""
echo "${BOLD}WARNING${OFF} this script will shutdown all databases running on this server!"
echo ""
echo "Proceed y/n: c"
read REPLY
case $REPLY in
"y"&#124"Y") break;;
"n"&#124"N") echo "Nothing done. Exiting."; exit 1;;
*) echo "Invalid Option. Exiting."; exit 1;;
esac

# Check if any databases are running. If so shut them down.
dbase=`ps -ef | grep _lgwr_ | grep -v grep | cut -f3 -d "_" | wc -l`
if [[ $dbase -eq 0 ]]; then
echo "Nothing is Running!"
else
echo " Shutting down the following databases"
ps -ef | grep _lgwr_ | grep -v grep | cut -f3 -d "_"
sleep 2
for item in `ps -ef | grep _lgwr_ | grep -v grep | cut -f3 -d "_"`
{
. $HOME/$item
svrmgrl << EOF
connect internal
shutdown immediate
exit
EOF
}
fi
#
# Stop Listeners if they are running
L1=`ps -ef | grep LISTENER-database1 | grep -v grep | wc -l`
if [[ $L1 -ne 0 ]]
then
echo ""
echo "Stopping the Listener process LISTENER-database1..."
. $HOME/cyber
lsnrctl stop LISTENER-database1
fi

L2=`ps -ef | grep LISTENER-khamdb | grep -v grep | wc -l`
if [[ $L2 -ne 0 ]]
then
echo ""
echo "Stopping the Listener process LISTENER-khamdb..."
. $HOME/prod
lsnrctl stop LISTENER-khamdb
fi
L3=`ps -ef | grep LISTENER-database2 | grep -v grep | wc -l`
if [[ $L3 -ne 0 ]]
then
echo ""
echo "Stopping the Listener process LISTENER-database2..."
. $HOME/edge
lsnrctl stop LISTENER-database2
fi

Here is an example script it calls to define environment variables:

#!/bin/ksh
echo ""
## echo "USAGE: . prod"
echo ""

export ORACLE_SID="prod"
# get ORACLE_HOME from /var/opt/oracle/oratab
export ORACLE_HOME=`grep ^$ORACLE_SID: /var/opt/oracle/oratab | cut -d: -f2`
## export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' # This fixes ORA-01861 when importing
export ORACLE_BASE="/oradb/kham/u1/app/oracle/product"
export LD_LIBRARY_PATH="$ORACLE_HOME/lib:/usr/lib"
export SHLIB_PATH="$ORACLE_HOME/lib"
export NLS_LANG="AMERICAN_AMERICA.WE8ISO8859P1"
# export NLS_LANG="AMERICAN_AMERICA.US7ASCII"
export PATH=$ORACLE_HOME/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/ucb:/usr/sbin:/usr/loc
al/bin:.
export TNS_ADMIN="/var/opt/oracle"
## export TNS_ADMIN="$ORACLE_HOME/network/admin"

echo "Be sure to check the ORA environment variables..."
echo "================================================="
echo ""
env &#124grep ORA
echo ""
Previous Topic: Re: VMS to UNIX Migration (Oracle)
Next Topic: Passing Parameters from Ref_Cursors SPs to C ++ Pgms in UNIX
Goto Forum:
  


Current Time: Fri Mar 29 02:28:12 CDT 2024