Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Unix scripting: need some help

Posted on 2014-11-16
2
Medium Priority
?
367 Views
Last Modified: 2014-11-23
Need to run sql scripts via unix/linux, against corresonding databases, basically updating some columns in different databases/schema & tables too, have all the update scripts ready, but rightnow they are all individual scripts, need to merge them all together as one shell script, in order for them to be executed as sys and globally too, basically just after the refresh job completed, they'll execute this one global scripts, which'll update all the columns in the corresponding databases/schemas & tables too.

Can any one please assist/advise/guide in writing this unix shell script, here is the req:

- should be only one script for all the databases/environments
- must be executed as sys, because can't code the password, will be kicked off by the job schedular, just after the refresh job finished.
- not all the columns are same in all the dbs, means the script need to check the databases running on this box/server and then only execute the script corresponding to that database/schema & tables only.

Also is there any way to commit in between too, because just executed one script and there is big spike on the db, means would like to commit too, in between, after some thousands of updates.

Can any one please assit/guide here ... thanks in advance.
0
Comment
Question by:Mushfique Khan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 40446362
Connecting as SYS is extremely dangerous.  If you are subject to any type of auditing, I'm sure that isn't allowed.  Creating a OS authenticated account that only have the privileges required to do what the script does would be a much more secure way to handle it.  No password and no elevated privileges would be required.

Not sure exactly how this is working.  I'm assuming that you are going to deploy one script on many machines.  One large script to maintain.

If that is the case, I would put the commands for each database into a single if block.  Then pass in the databases that are on the machine on the command line.  That can be maintained in the job scheduler as databases don't move often.

Something like ....

if [ $1 == "db1" ] ; then
  export ORACLE_SID=db1
  sqlplus / << EOF
     .....
     commit;
     exit
EOF
fi

if [ $1 == "db2" ] ; then
  export ORACLE_SID=db2
  sqlplus / << EOF
     .....
     commit;
     exit
EOF
fi

Open in new window


That is a basic idea of how I would do it.  You probably need to add more if there are multiple databases on a machine and maybe some error processing.
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 2000 total points
ID: 40455748
If you have to execute scripts on several servers, you need to:

1) have on each of the remote servers the code (script) to be executed locally (there)
2) execute these from a server that has ssh connectivity to the rest
3) use a control script similar to this:

#!/usr/bin/ksh
# eom_main.ksh -- End of Month, executed at serverA
# -----------------------------------------------------
# Set some environment variables
SCR_NM=`basename ${0}`
SCR_NM=${SCR_NM%%.*}
HOST="`hostname`"
export FPATH=/usr/local/functions
export EOMDIR=/usr/local/eom
LOGFILE=$EOMDIR/runlogs/${SCR_NM}_`date +%Y%m%d\-%H%M`.log
EMAIL="to.me@mycompany.com"
ERRMAIL="sysadmin@mycompany.com"

# List of Servers (You could put in a file)
REMOTE1=serverX.mycompany.com
REMOTE2=serverY.mycompany.com
REMOTE3=serverZ.mycompany.com

# Set specific variables for this process
. /usr/local/eom/etc/.profile.eom

# Cleanup files and logs from previous run 
rm $EOMDIR/tmp/*  2>/dev/null
find $EOMDIR/log -mtime +61 -exec rm -f {} \; 2>/dev/null

#
# --  Functions Section -------------------------------------
#
abort_eom(){
  STEP=$1
  SUBJECT="!Error: EOM Process at $HOST failed on step: $1"
  cat $LOGFILE| mail -s "$SUBJECT" $ERRMAIL
  exit 2
}
#
step_01(){
echo "+++ ${SCR_NM} `date`
# ---------------------------------------
# EOM Load . Step One . Create Database
#"
rc=0
cd $EOMDIR/bin
[ debug -gt 0 ] && echo "Step1: $EOMDIR `which ./eomcreate.ksh`"
sudi dbaccess sysmaster << eof
drop database eom$OLDPERIOD; 
eof
(( rc+=$? ))

./eomcreate.ksh
(( rc+=$? ))

return $rc
}
step_02(){
echo "+++ ${SCR_NM} `date`
# ---------------------------------------
# EOM Load . Step Two . Bank Management Load at $HOST
# "
rc=0
cd $EOMDIR/bin
./eombm.ksh
(( rc+=$? ))
return $rc
}
step_03(){
TGTBOX=$1
echo "+++ ${SCR_NM} `date`
# ---------------------------------------
# Bank Management Report . Step Three at $TGTBOX
# "
rc=0
ssh $TGTBOX "/usr/local/eom/bin/eom03_bm_report.ksh "
(( rc+=$? ))
return $rc
}
step_04(){
TGTBOX=$1
echo "+++ ${SCR_NM} `date`
# ---------------------------------------
# Load more data - Step 04 at $TGTBOX
#"
#
rc=0
ssh $TGTBOX "/usr/local/eom/bin/LoadMore.sh"
(( rc+=$? ))
return $rc
}
#
# ---------------------------------------
# Main process
#
exec >$LOGFILE
echo "+++ ${SCR_NM} Started at `date`"
rc=0
step_01
[ $? -ne 0 ] && abort_eom "01"
step_02
[ $? -ne 0 ] && abort_eom "02"
step_03 $REMOTE2
[ $? -ne 0 ] && abort_eom "03:$REMOTE2"
step_03 $REMOTE3
[ $? -ne 0 ] && abort_eom "03:$REMOTE3"
step_04 $REMOTE3
[ $? -ne 0 ] && abort_eom "04"

echo "+++ ${SCR_NM} Ended at `date`"
exit 0

Open in new window

PS: The above script is only an example of how to consolidate a process that executes scripts on several servers.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question