#!/bin/bash
# ************************************************#
# Attention : this shell script is used to export #
# data from oracle db and import data to sybase #
# db,before you execute the script,make sure the #
# config file: inoutdb.ini has been modified and #
# the workpath has been created And The client For#
# Oracle has been installed #
# Tested By:Rocky@2011.06.08 #
#*************************************************#
#set -x
osname=`uname`
if [ "$osname" = "Linux" ];then
. $HOME/.bash_profile >/dev/null
else
. $HOME/.profile >/dev/null
fi
PRGNAME=`basename $0`
expsequence=`date +"%Y%m%d%H%M%S"`"$$"
ShowUsageInfo()
{
cat << EOF
==========================================================================
| This program is used to get system info for SRS |
==========================================================================
Usage: $PRGNAME [Options]
Options:
-S specialtag : Special deal tag.
-M mainstencil : Main stencil file.
-T tempstencil : Temp stencil file.
-F finalstencil : Final stencil file.
==============================ATTENTION===================================
| if you do not need to turn on the special switch,you just
| do not use the '-S' Option, e.g: $PRGNAME -Ftask_ora.ini
| if the '-S' Option is used, you need to appoint the main
| stencil file with '-M' Option, the temp stencil file with
| '-T' Option, the final stencil file with '-F' Option e.g:
| $PRGNAME -S -Mtask_ora.ini -Ttemplet.ini -Ftask_run.ini
==========================================================================
EOF
}
work_path=$HOME/zxinstat/StatTask/shell
srcdbname=ORACLE
SPECIALTAG=0
MAINSTENCIL=
TEMPSTENCIL=
FINALSTENCIL=
while getopts "SM:T:F:" opt
do
case $opt in
S) SPECIALTAG=1;;
M) MAINSTENCIL=$OPTARG;;
T) TEMPSTENCIL=$OPTARG;;
F) FINALSTENCIL=$OPTARG;;
*) ShowUsageInfo
exit ;;
esac
done
echo "specialtag: $SPECIALTAG, mainstencil: $MAINSTENCIL, tempstencil: $TEMPSTENCIL, finalstencil: $FINALSTENCIL"
if [ "$SPECIALTAG" = "1" ];then #special deal
if [ $# -ne 4 ];then
echo "THE ARGUMENTS NUMBER ERROR: "
echo "THE SWITCH FOR SPECIAL DEAL IS TURN ON,NEED TO USE '-M' '-T' '-F' OPTION"
echo "PROCESS EXIT !!!"
ShowUsageInfo
exit 1
else
if [ -n "$MAINSTENCIL" -a -n "$TEMPSTENCIL" -a -n "$FINALSTENCIL" ];then
:
else
echo "THE ARGUMENTS OPTION ERROR: "
echo "THE SPECIAL DEAL SWITCH IS TURN ON,NEED TO USE '-M' '-T' '-F' OPTION"
echo "PROCESS EXIT !!!"
ShowUsageInfo
exit 1
fi
fi
if [ -f "$work_path/$MAINSTENCIL" -a -f "$work_path/$TEMPSTENCIL" ];then
:
else
echo "THE FILE $work_path/$MAINSTENCIL OR "
echo "$work_path/$TEMPSTENCIL NOT EXIST,PLEASE CREATE FIRST ... ..."
echo "PROCESS EXIT !!!"
exit 1
fi
sh createconfile.sh $MAINSTENCIL $TEMPSTENCIL $FINALSTENCIL
if [ $? -ne 0 ];then
echo "CREATE $FINALSTENCIL ERROR,PROCESS EXIT !!! "
exit 1
fi
else
if [ $# -ne 1 ];then
echo "THE ARGUMENTS ERROR: "
echo "THE SWITCH FOR SPECIAL DEAL IS TURN OFF,NEED TO USE '-F' OPTION ONLY"
echo "PROCESS EXIT !!!"
ShowUsageInfo
exit 1
else
if [ $1 = "--help" ];then
ShowUsageInfo
exit 1
fi
fi
fi
cd $work_path
CONFIGFILE=$FINALSTENCIL
eval data_path=`grep LOCALPATH $CONFIGFILE | awk -F= '{ print $2 } '`
eval back_path=`grep BACKUPPATH $CONFIGFILE | awk -F= '{ print $2 } '`
dbentry_ora=`grep Oraentryfile $CONFIGFILE | awk -F= '{ print $2 } ' |awk -F"." '{ print $1 } '`
dbentry_syb=`grep Sybentryfile $CONFIGFILE | awk -F= '{ print $2 } ' |awk -F"." '{ print $1 } '`
conf_file=$work_path/$CONFIGFILE.data
################################################################################
#Get The user/Password FOR SYBASE AND ORACLE #
################################################################################
if [ -f "$dbentry_ora" ]
then
:
else
echo "ERROR: THE FILE $dbentry_ora NOT FOUND"
exit 1
fi
filename=$CONFIGFILE.`grep begin $dbentry_ora |awk '{print $3}'`
uudecode -o $filename $dbentry_ora
if [ $? -eq 0 ]
then
ousername=`grep user $filename |awk '{print $2}'`
opassword=`grep password $filename |awk '{print $2}'`
oservname=`grep servname $filename |awk '{print $2}'`
fi
rm $filename
if [ -f "$dbentry_syb" ]
then
:
else
echo "ERROR: THE FILE $dbentry_syb NOT EXIST"
exit 1
fi
filename=$CONFIGFILE.`grep begin $dbentry_syb |awk '{print $3}'`
uudecode -o $filename $dbentry_syb
if [ $? -eq 0 ]
then
username=`grep user $filename |awk '{print $2}'`
password=`grep password $filename |awk '{print $2}'`
servname=`grep servname $filename |awk '{print $2}'`
else
echo "ERROR: WHEN DECODE $dbentry_syb"
exit 1
fi
rm $filename
process()
{
execsql=$finaltabletag".sql"
gettimesql=$finaltabletag"_time.sql"
outputfile=$finaltablesrc".data"
gettimefile=$finaltabletag".time"
importtable=$importdb".."$finaltabletag
truncatesql="truncate_"$finaltabletag".sql"
if [ -f "$work_path/sql/$execsql" ]
then
rm $work_path/sql/$execsql
fi
echo "set echo off" >$work_path/sql/$execsql
echo "set feedback off" >>$work_path/sql/$execsql
echo "set heading off" >>$work_path/sql/$execsql
echo "set pagesize 0" >>$work_path/sql/$execsql
echo "set termout off" >>$work_path/sql/$execsql
echo "set trimout on" >>$work_path/sql/$execsql
echo "set trimspool ON" >>$work_path/sql/$execsql
echo "spool $data_path/$outputfile">>$work_path/sql/$execsql
echo "set lines 2000" >>$work_path/sql/$execsql
if [ "$gathertype" = "F" -o "$gathertype" = "f" ];then
echo "$SQL"|sed 's/?/=/g' >>$work_path/sql/$execsql
echo "quit; ">>$work_path/sql/$execsql
else
case $period in
"M")
sqlstring="select to_char(trunc(SYSDATE,'mm'),'yyyymmddhh24miss') FROM dual;"
;;
"D")
sqlstring="SELECT to_char(trunc(SYSDATE,'dd'),'yyyymmddhh24miss') FROM dual;"
;;
"H")
sqlstring="select to_char(trunc(SYSDATE,'hh24'),'yyyymmddhh24miss') FROM dual;"
;;
"m")
sqlstring="select to_char(trunc(SYSDATE,'mi'),'yyyymmddhh24miss') FROM dual;"
;;
*)
echo "ERROR: period type definition exit process"
exit 1
;;
esac
################################################################################
# GET THE CURRENT TIME FOR GATHER ENDTIME FROM SOURCE DATABASE #
################################################################################
if [ -f "$work_path/sql/${finaltablesrc}_time.sql" ];then
rm $work_path/sql/${finaltablesrc}_time.sql
fi
echo "set echo off" >$work_path/sql/$gettimesql
echo "set feedback off" >>$work_path/sql/$gettimesql
echo "set heading off" >>$work_path/sql/$gettimesql
echo "set pagesize 0" >>$work_path/sql/$gettimesql
echo "set termout off" >>$work_path/sql/$gettimesql
echo "set trimout on" >>$work_path/sql/$gettimesql
echo "set trimspool ON" >>$work_path/sql/$gettimesql
echo "spool $work_path/sql/$gettimefile">>$work_path/sql/$gettimesql
echo "set lines 2000" >>$work_path/sql/$gettimesql
echo "$sqlstring" >>$work_path/sql/$gettimesql
echo "quit;" >>$work_path/sql/$gettimesql
$ORACLE_HOME/bin/sqlplus $ousername/$opassword@$oservname @$work_path/sql/$gettimesql
#sleep 1
if [ $? -ne 0 ]; then
echo "ERROR: WHEN GET ENDTIME FOR : $finaltable "
exit 1
fi
if [ -f "$work_path/sql/$gettimefile" ];then
xxx_getime_xxx=`grep '[0-9]\{14,14\}' $work_path/sql/$gettimefile `
if [ -n "$xxx_getime_xxx" ];then
:
else
echo "THE GATHER ENDTIME IS NULL PROCESS EXIT!"
exit 1
fi
else
echo "TIME FILE NOT FOUND FOR :$finaltable EXIT PROCESS!"
exit 1
fi
################################################################################
# GET THE LAST GATHER TIME FOR THIS GATHER STARTTIME FROM SYBASE #
################################################################################
$SYBASE/$SYBASE_OCS/bin/bcp $importdb..vw_td_export_record out $work_path/sql/$finaltabletag.syb.time -c -Y -U$username -P$password -S$servname -t "|"
if [ $? -ne 0 ];then
echo "ERROR: WHEN GET LAST GATHER TIME FOR : $finaltabletag,EXIT PROCESS !!!"
exit 1
fi
xxx_gstime_xxx=`awk -v xxxtable=$finaltabletag -v xxxtabsrc=$finaltablesrc -F"|" '$2 == xxxtable && $3 == xxxtabsrc {print $5}' $work_path/sql/$finaltabletag.syb.time`
if [ $? -ne 0 ];then
echo "ERROR: WHEN GET THE LAST GATHERTIME"
exit 1
fi
if [ -n "$xxx_gstime_xxx" ];then
:
else
echo "THE GATHER STARTTIME IS NULL"
xxx_gstime_xxx="19000101000000"
fi
rm $work_path/sql/$finaltabletag.syb.time
rm $work_path/sql/$gettimefile
echo "$SQL"|sed 's/?/=/g'|eval sed 's/$srctab/$finaltablesrc/g'|eval sed 's/xxx_gstime_xxx/$xxx_gstime_xxx/g'|eval sed 's/xxx_getime_xxx/$xxx_getime_xxx/g'>>$work_path/sql/$execsql
echo "quit;">>$work_path/sql/$execsql
fi
if [ `expr match $trunctag [Yy][Ee][Ss]` -ne 0 ]
then
if [ -f "$work_path/sql/$truncatesql" ]
then
echo "file : $truncatesql checked ok"
else
echo "dump transaction $importdb with no_log" >>$work_path/sql/$truncatesql
echo "go" >>$work_path/sql/$truncatesql
echo "use $importdb" >>$work_path/sql/$truncatesql
echo "go" >>$work_path/sql/$truncatesql
echo "begin" >>$work_path/sql/$truncatesql
echo "declare @v_sql varchar(500)" >>$work_path/sql/$truncatesql
echo "select @v_sql='truncate table $finaltabletag'" >>$work_path/sql/$truncatesql
echo "exec(@v_sql)" >>$work_path/sql/$truncatesql
echo "end" >>$work_path/sql/$truncatesql
echo "go" >>$work_path/sql/$truncatesql
echo "dump transaction $importdb with no_log" >>$work_path/sql/$truncatesql
echo "go" >>$work_path/sql/$truncatesql
fi
fi
exec_time=`date +"%Y.%m.%d %H:%M:%S"`
echo "===============START PROCESSING TABLE: $finaltablesrc =================="
$ORACLE_HOME/bin/sqlplus $ousername/$opassword@$oservname @$work_path/sql/$execsql
if [ $? -eq 0 ]
then
echo "=============== TABLE: $finaltablesrc EXPORT FROM ORACLE SUCCESSED =================="
if [ `expr match $trunctag [Yy][Ee][Ss]` -ne 0 ]
then
$SYBASE/$SYBASE_OCS/bin/isql -U$username -P$password -S$servname -i$work_path/sql/$truncatesql
if [ $? -ne 0 ]
then
echo "ERROR: WHEN TRUNCATE TABLE: $finaltabletag "
continue
fi
fi
echo "=============== START BCPING TABLE: $finaltabletag INTO SYBASE =================="
if [ "$gathertype" = "I" -o "$gathertype" = "i" ];then
deletesql=del_$finaltabletag.sql
if [ -f "$work_path/sql/$deletesql" ];then
rm $work_path/sql/$deletesql
fi
echo "dump transaction $importdb with no_log" >>$work_path/sql/$deletesql
echo "go" >>$work_path/sql/$deletesql
echo "use $importdb" >>$work_path/sql/$deletesql
echo "go" >>$work_path/sql/$deletesql
echo "begin" >>$work_path/sql/$deletesql
echo "exec p_data_sync_clear '$finaltabletag','$finaltablesrc','$xxx_gstime_xxx','$xxx_getime_xxx'" >>$work_path/sql/$deletesql
echo "end" >>$work_path/sql/$deletesql
echo "go" >>$work_path/sql/$deletesql
echo "dump transaction $importdb with no_log" >>$work_path/sql/$deletesql
echo "go" >>$work_path/sql/$deletesql
if [ "$SPECIALTAG" = "1" ];then
xtag=0
cat $work_path/$TEMPSTENCIL|grep "TARGET_TABLE=$finaltabletag" && xtag=1
if [ $xtag -eq 1 -a "${!finaltabletag}" != "1" ];then
echo "===================BEGIN DELETE RECORD IN $finaltabletag ==================="
$SYBASE/$SYBASE_OCS/bin/isql -U$username -P$password -S$servname -i$work_path/sql/$deletesql
if [ $? -ne 0 ];then
echo "DELETE HISTORY DATA ERROR,EXIT"
exit 1;
else
echo "===================DELETE RECORD IN $finaltabletag SUCCESSFUL ==================="
fi
eval $finaltabletag=1
else
echo "===================PASS DELETE RECORD IN $finaltabletag ==================="
fi
else
echo "===================BEGIN DELETE RECORD IN $finaltabletag ==================="
$SYBASE/$SYBASE_OCS/bin/isql -U$username -P$password -S$servname -i$work_path/sql/$deletesql
if [ $? -ne 0 ];then
echo "DELETE HISTORY DATA ERROR,EXIT"
exit 1;
else
echo "===================DELETE RECORD IN $finaltabletag SUCCESSFUL ==================="
fi
fi
fi
echo "===================BEGIN BCP RECORD INTO $finaltabletag ==================="
$SYBASE/$SYBASE_OCS/bin/bcp $importtable in $data_path/$outputfile -c -Y -U$username -P$password -S$servname -t "|"
if [ $? -ne 0 ]
then
echo "ERROR: WHEN IMPORT DATA TO $finaltabletag "
continue
else
echo "=============== BCPING TABLE: $finaltabletag INTO SYBASE SUCCESSED =================="
fi
else
echo "========ERROR: WHEN $finaltabletag DATA EXPORT FROM ORACLE==========="
exit 1
fi
if [ -f "$data_path/$outputfile" ]
then
mv $data_path/$outputfile $back_path/`date +%Y%m%d`_$outputfile
fi
if [ "$gathertype" = "F" -o "$gathertype" = "f" ];then
:
else
echo "=============== START INSERT A RECORD TO TD_EXPORT_RECORD =================="
gatherlogsql=$finaltabletag"_record.sql"
if [ -f "$work_path/sql/$gatherlogsql" ]
then
rm $work_path/sql/$gatherlogsql
fi
echo "dump transaction $importdb with no_log" >>$work_path/sql/$gatherlogsql
echo "go" >>$work_path/sql/$gatherlogsql
echo "use $importdb" >>$work_path/sql/$gatherlogsql
echo "go" >>$work_path/sql/$gatherlogsql
echo "begin" >>$work_path/sql/$gatherlogsql
echo "declare @v_sql varchar(500)" >>$work_path/sql/$gatherlogsql
echo "select @v_sql='insert into td_export_record(exp_table_name,src_table_name,exp_start_time,exp\
_end_time,exec_start_time,exec_end_time,exp_sequence) values(''$finaltabletag'',''$finaltablesrc'',''$xxx_gstime_xxx'',''$xxx_ge\
time_xxx'',''$exec_time'',''`date +"%Y.%m.%d %H:%M:%S"`'',''$expsequence'')'" >>$work_path/sql/$gatherlogsql
echo "exec(@v_sql)" >>$work_path/sql/$gatherlogsql
echo "end" >>$work_path/sql/$gatherlogsql
echo "go" >>$work_path/sql/$gatherlogsql
echo "dump transaction $importdb with no_log" >>$work_path/sql/$gatherlogsql
echo "go" >>$work_path/sql/$gatherlogsql
$SYBASE/$SYBASE_OCS/bin/isql -U$username -P$password -S $servname -i$work_path/sql/$gatherlogsql
if [ $? -ne 0 ];then
echo "ERROR: INSERT RECORD INTO TD_EXPORT_RECORD PROCESS EXIT"
exit 1
fi
fi
################################################################################
# INSERT EXPORT LOG IN TO SYBASE DATABASE #
################################################################################
insertlogsql=$finaltabletag"_log.sql"
if [ -f "$work_path/sql/$insertlogsql" ]
then
rm $work_path/sql/$insertlogsql
fi
echo "dump transaction $importdb with no_log">>$work_path/sql/$insertlogsql
echo "go" >>$work_path/sql/$insertlogsql
echo "use $importdb" >>$work_path/sql/$insertlogsql
echo "go" >>$work_path/sql/$insertlogsql
echo "begin" >>$work_path/sql/$insertlogsql
echo "declare @v_sql varchar(200)" >>$work_path/sql/$insertlogsql
echo "select @v_sql='insert into prt_inoutdb_log(src_dbname,table_name,exe\
c_day,exec_time,finish_time) values(''$srcdbname'',''$finaltabletag'',''`date +%Y%m\
%d`'',''$exec_time'',''`date +"%Y.%m.%d %H:%M:%S"`'')'">>$work_path/sql/$insertlogsql
echo "exec(@v_sql)" >>$work_path/sql/$insertlogsql
echo "end" >>$work_path/sql/$insertlogsql
echo "go" >>$work_path/sql/$insertlogsql
echo "dump transaction $importdb with no_log">>$work_path/sql/$insertlogsql
echo "go" >>$work_path/sql/$insertlogsql
#All the data imported Insert A record to target database
$SYBASE/$SYBASE_OCS/bin/isql -U$username -P$password -S $servname -i$work_path/sql/$insertlogsql
}
################################################################################
# LOOP BY CONFIG FILE #
################################################################################
while read task
do
if [ -z "$task" ]
then
continue
elif [ "$task" = "[TASK]" ]
then
echo $task >$conf_file
elif [ "$task" != "[/TASK]" ]
then
echo $task >>$conf_file
else
echo $task >>$conf_file
srctab=`grep SOURCE_TABLE $conf_file |awk -F= '{ print $2 }'`
tartab=`grep TARGET_TABLE $conf_file |awk -F= '{ print $2 }'`
trunctag=`grep TRUNCATE $conf_file |awk -F= '{ print $2 }'`
cycletag=`grep CYCLETAG $conf_file |awk -F= '{ print $2 }'`
minvalue=`grep MINVALUE $conf_file |awk -F= '{ print $2 }'`
maxvalue=`grep MAXVALUE $conf_file |awk -F= '{ print $2 }'`
step=`grep STEP $conf_file |awk -F= '{ print $2 }'`
gathertype=`grep GATHERTYPE $conf_file |awk -F= '{ print $2 }'`
period=`grep PERIOD $conf_file |awk -F= '{ print $2 }'`
importdb=`grep TAGGET_DBNAME $conf_file |awk -F= '{ print $2 }'`
SQL=`grep SELECTSQL $conf_file |awk -F= '{ print $2 }'`
if [ `expr match $cycletag [Yy][Ee][Ss]` -ne 0 ]
then
x=`echo $minvalue|sed '/^0*$/{s/.*/0/;q;};s/^0*//'`
max=`echo $maxvalue|sed '/^0*$/{s/.*/0/;q;};s/^0*//'`
while [ $x -le $max ]
do
if [ ${#minvalue} -eq 2 -a $x -lt 10 ]
then
suffix=0$x
else
suffix=$x
fi
finaltablesrc=$srctab$suffix
finaltabletag=$tartab$suffix
process
x=`expr $x + $step`
done
else
finaltablesrc=$srctab
finaltabletag=$tartab
process
fi
fi
done <$CONFIGFILE
rm $conf_file
exit 0
转载于:https://my.oschina.net/whwei1982/blog/157930