Some My Shell on Unix in 2009

@Author:cjcj  [e-mail] <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 $@