@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 $@