inoutdb4ORA_PLUS.sh

于 2013-08-30 09:13:00 发布
阅读量144 收藏
点赞数
文章标签： awk 数据库 shell
原文链接：https://my.oschina.net/whwei1982/blog/157930
版权 
#!/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

chiliuyong3321
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值