@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 $@
分享到:
相关推荐
叙述中国高等教育的主要问题并给出解决方案
some of the most talented UNIX professionals on the topic. I have enjoyed every minute of my association with these UNIX gurus and it has been my pleasure to have the opportunity to gain so much ...
Describing the UNIX System in a strictly linear fashion, without any forward references to terms that haven't been described yet, is nearly impossible (and would probably be boring). This chapter ...
needed and practical recipes that follow a problem-solution format, and help all Unix users regain some of the lost time spent creating and testing shell scripts. Most scripts in this book are POSIX ...
Some notes on machine learning algorithms, mostly in Matlab format.zip
It guides you on implementing some of the most common commands in Linux with recipes that handle any of the operations or properties related with files like searching and mining inside a file with ...
Shell脚本高级编程教程,希望对你有所帮助。 Example 10-23. Using continue N in an actual task: 1 # Albert Reiner gives an example of how to use "continue N": 2 # -------------------------------------...
It guides you on implementing some of the most common commands in Linux with recipes that handle any of the operations or properties related with files like searching and mining inside a file with ...
Some Solutions on QTP Common Issues Some Solutions on QTP Common Issues Some Solutions on QTP Common Issues Some Solutions on QTP Common Issues
Uniwin is a ssh/sftp based toolset to develop C/C++ systems on remote Unix servers (including all Unix systems supporting ssh and sftp) based on Windows clients. Uniwin is especially optimized for ...
It guides you on implementing some of the most common commands in Linux with recipes that handle any of the operations or properties related with files like searching and mining inside a file with ...
2nd Edition is filled with over 150 much-needed and practical recipes that follow a problem-solution format, and help all Unix users regain some of the lost time spent creating and testing shell ...
Some thoughts on differential equation notation - functional vs classical.pdf
Some Notes on Applied Mathematics for Machine Learning 这是一个对机器学习中的数学方法的介绍,供大家参考
集合了 所有的 Unix命令大全 登陆服务器时输入 公帐号 openlab-open123 telnet 192.168.0.23 自己帐号 sd08077-you0 ftp工具 192.168.0.202 tools-toolss 老师测评网址 http://172.16.0.198:8080/poll/ 各个 ...
GNU m4 also has built-in functions for including files, running shell commands, doing arithmetic, etc. GNU m4 is a macro processor in the sense that it copies its input to the output expanding ...
some software of my for java delepement some software of my for java delepement some software of my for java delepement
连分数中某些遍历和的比,廖灵敏,,本文研究与连分数相关的高斯动力系统中的遍历和的比的重分形分析。对任意的零一区间上的无理数x, 令x=[a1(x),a2(x),...]为它的连分数展�
My first public presentation on this subject was in a tutorial called “Minimal Perl for the Impatient” at the YAPC::Europe 2001 conference2 in Amsterdam, the Nether- lands. The eagerness with which ...