Some My Shell on Unix in 2009

最新推荐文章于 2025-10-17 10:58:58 发布

最新推荐文章于 2025-10-17 10:58:58 发布 · 112 阅读

文章标签：

#Unix #SQL #SQL Server #Oracle #F#

工作项目专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一款用于从主服务器和从服务器选择并注入公共参数数据的数据库同步工具脚本。该脚本能够获取指定表的数据，计算校验和，并将结果插入到另一张表中。同时提供了一个定时查询数据库特定表计数并记录到文件的例子。

@Author:cjcj  cj.yangjun@gmail.com <c-j.iteye.com>

 从数据库取表名称和order by条件,导出指定表数据,checksum后插入另一张表.

#! /usr/bin/ksh

################################################################################
#
#  Copyright (C) 2009
#
#  NAME: 
#		ck_dbpub.sh
#  
#  DESCRIPTION:  
#		Select and inject data about common paramenters from master server and 
#		slave server
#		
#
#  AUTHOR: 
#		cj.YangJun	
#
#  CHANGE LOG
#		2009-8-13
#
################################################################################
CKHEALTH_LOG_DIR=${CKHEALTH_LOG_DIR:-"./"}
CKHEALTH_WORK_DIR=${CKHEALTH_WORK_DIR:-"./"}
CONNECTSTRING="" 
# cfg file
DBPUB_TMP_CFG="ck_dbpub_tmp.cfg"

# sql file
DBPUB_TMP_SQL="ck_dbpub_sql.tmp"

# pub name
DBPUB_NAME="dbpub"

# split pre-name
DBPUB_TMP_ICS_DATA="ck_dbpub_ics_data_"
DBPUB_TMP_ISS_DATA="ck_dbpub_iss_data_"

DBPUB_TMP_ERR="ck_dbpub.err"



# get current conn
get_db_con(){
	CONNECTSTRING=`get_db_conn $1`
}






# get all table names which want to be checked from ckhealth_plugin_cfg table 
get_ctf_from_db(){

    if [ -r $DBPUB_TMP_CFG ];then
	rm -f $DBPUB_TMP_CFG
	fi
	ociuldr user=$CONNECTSTRING \
	query="select item_htag,item_ltag from ckhealth_plugin_cfg where plug_name='dbpub' order by item_htag,item_ltag" \
field="|" file=$DBPUB_TMP_CFG | awk '/error|Error|ERROR|failed|Failed|FAILED|not exist|ORA/' >> $DBPUB_TMP_ERR 


	while read line
	do
		LM_ERROR "$line"
	done <$DBPUB_TMP_ERR
	rm -f $DBPUB_TMP_ERR

	if [ ! -r $DBPUB_TMP_CFG ];then
		LM_ERROR "The DBPUB_CFG file is not exist!"
		exit -1
	fi

}









# get all table's check-sum and  bulid the sql-file used to insert data into ckhealth_data
build_sql(){

	SQLSTRING=""
	CKSUM=""
	TABLENAME=""
	ORDERBY=""
	ICSSQLSTR=""
	ISSSQLSTR=""
	DB=""
	if [ ! -r $1 ];then
 	 	 LM_ERROR "The DBPUB_CTG file is not exist!"
	 	 exit -1
	fi


	

# get every line from cft-file	
	while read ITEM
	do
# get the datebase name
		DB=`echo $ITEM | awk -F "|" '{print $1}'`

# get the table name
		TABLENAME=`echo $ITEM | awk -F "|" '{print $2}'`
		ORDERBY=`echo $ITEM | awk -F "|" '{print $3}'`
		ASTERISK='*'

# create the sql string
		SQLSTRING="select $ASTERISK from $TABLENAME"


# check the "order by" key
		if [ $ORDERBY ];then
				SQLSTRING=" order by $ORDERBY"
		fi



# avoid same table name between ics and iss datebase,so must split the file which used to be check-sum
		if [ -z $DB ] || [ -z $TABLENAME ];then
			LM_DEBUG "The datebase name or tablename is null"
			continue
		fi		
		if [ $DB = ics ];then
			echo "spool $DBPUB_TMP_ICS_DATA$TABLENAME.tmp;" >>ck_dbpub_ics_sql.tmp
			echo "$SQLSTRING;" >>ck_dbpub_ics_sql.tmp
			echo "spool off;" >>ck_dbpub_ics_sql.tmp
		elif [ $DB = iss ];then
			echo "spool $DBPUB_TMP_ISS_DATA$TABLENAME.tmp;" >>ck_dbpub_iss_sql.tmp
			echo "$SQLSTRING;" >>ck_dbpub_iss_sql.tmp
			echo "spool off;" >>ck_dbpub_iss_sql.tmp
		fi
	done<$1




# check the file 	
		if [ ! -r ck_dbpub_ics_sql.tmp ];then
			LM_DEBUG "the ics sql file is not built!!"
		fi
		if [ ! -r ck_dbpub_iss_sql.tmp ];then
			LM_DEBUG "the iss sql file is not bulit!!"
		fi



# build the files about ics and iss by sqlplus
sqlplus -S $CONNECTSTRING <<EOF
SET ECHO OFF;
SET FEEDBACK OFF;
SET HEADING OFF;
SET TERM OFF;
SET WRAP OFF;
@ck_dbpub_ics_sql.tmp;
@ck_dbpub_iss_sql.tmp;
exit;
EOF


# clear the split sql-file	
rm -f ck_dbpub_ics_sql.tmp
rm -f ck_dbpub_iss_sql.tmp

# get all file's checksum and bulid the second sql-file which used to insert into ckhealth_data 
	while read ITEM 
	do	
		DB=`echo $ITEM | awk -F "|" '{print $1}'`
		TABLENAME=`echo $ITEM | awk -F "|" '{print $2}'`

# check the split file is exsited and print the error table name and datebase name		
		if [ -z $DB ] || [ -z $TABLENAME ];then
			LM_DEBUG "The database name or table name is null"
			continue	
		fi	
		if [ $DB = ics ];then
			if [ -r $DBPUB_TMP_ICS_DATA$TABLENAME.tmp ];then
				CKSUM=`cksum $DBPUB_TMP_ICS_DATA$TABLENAME.tmp | awk '{print $1}'`
				rm -f $DBPUB_TMP_ICS_DATA$TABLENAME.tmp	
			else
				LM_DEBUG "The $TABLENAME data file is not exsited from $DB! Maybe the oracle is error!!"
				continue	
			fi
		elif [ $DB = iss ];then
			if [ -r $DBPUB_TMP_ISS_DATA$TABLENAME.tmp ];then
				CKSUM=`cksum $DBPUB_TMP_ISS_DATA$TABLENAME.tmp | awk '{print $1}'`
				rm -f $DBPUB_TMP_ISS_DATA$TABLENAME.tmp	
			else 
				LM_DEBUG "The $TABLENAME data file is not exsited from $DB! Maybe the oracle is error!!"
				continue
			fi
		else
			LM_DEBUG "The database name is not ics or iss!"
			continue	
		fi
# bulid the sql statement into a sql-file
		SQLSTRING="insert into ckhealth_data(plug_name, item_key, item_value) values('$DBPUB_NAME','$ITEM','$CKSUM');"
		echo $SQLSTRING >>$DBPUB_TMP_SQL
	done<$1

# delete cfg file
	rm -f $1
}












# excute the sql
excute_sql(){
	if [ ! -r $1 ];then
  		LM_ERROR "The sql file is not exist!"
	 	exit -1
	fi

sqlplus -S $2 <<EOF
	@$1;
	commit;
	exit;
EOF
# delete sql file	
	rm -f $1
}










main(){
	. ./ck_comm.sh
	
# check the connection!	
	if [ $? -eq 1 ];then
		LM_ERROR "Get db connection error!Gencipher fail!"
		exit -1
	fi

    cd $CKHEALTH_WORK_DIR

# check the work_dir	
	if [ $? -ne 0 ];then
		LM_ERROR "Can't cd WORK_DIR!"
		exit -1
	fi

# get the connection
	get_db_con ics

# get the ctf data	
	LM_INFO "Get ctf from database begin!!"
	get_ctf_from_db
	LM_INFO "Get ctf from database end!!"

# check the table data	
	LM_INFO "Build sql-file from cfg-file begin!!"
	build_sql $DBPUB_TMP_CFG
	LM_INFO "Build sql-file from cfg-file end!!"

# reget the connection,because the connection changed above!	
	LM_INFO "Insert ckhealth_data begin!!"
	excute_sql $DBPUB_TMP_SQL  $CONNECTSTRING | awk '/error|Error|ERROR|failed|Failed|FAILED|not exist|ORA/' >> $DBPUB_TMP_ERR
	while read line
		do
			LM_ERROR "$line"
		done<$DBPUB_TMP_ERR
	rm -f $DBPUB_TMP_ERR	
	LM_INFO "Insert ckhealth_data end!!"


}


main $@

定时查询数据库某些表count(*)定向到文件

################################################################################
#
#  Copyright (C) 2009
#
#  NAME: 
#               listenReject.sh
#  
#  DESCRIPTION:  
#               listen some table                
#
#  AUTHOR: 
#               cj.YangJun      
#
#  CHANGE LOG
#               
#
################################################################################


PRCLF="Log/rePRCLog.log"
CRPLF="Log/reCRPLog.log"
SRCLF="Log/reSRCLog.log"
CRSLF="Log/reCRSLog.log"


DBLOGIN="xxx"
DBPASSWD="xxx"
DBNAME="xxx"


# Get the sum of err from Datebase

doListen()
{
sqlplus -s $DBLOGIN/$DBPASSWD@$DBNAME <<EOF 
spool $PRCLF append;
select count(*) from ldst_log where inst_nm='recv' and file_nm like 'F%';
spool off;
spool $CRPLF append;
select count(*) from ldst_log where inst_nm='send' and file_nm like 'F%';
spool off;
spool $SRCLF append;
select count(*) from err_ciber_ric_cdr;
spool off;
spool $CRSLF append;
select count(*) from err_ciber_ioc_cdr;
spool off;
exit;
EOF
}

# Do it once per 30mins and record current time
main()
{
I=1
while [ 1 ];do
  date >>$PRCLF 
  date >>$CRPLF
  date >>$SRCLF
  date >>$CRSLF
  doListen  
  sleep 3600
done
}
main $@