数据库同步

1.同步脚本
#!/bin/bash

CURRENT_DIR="`pwd`";
DB_CONFIGURE_FILE="${CURRENT_DIR}/DatabaseConfigure.properties";
LOG_DIR="${CURRENT_DIR}/log";
LOG_FILE="${LOG_DIR}/main.log";

EXP_PARA_OUTPUT_DATA_FILE="${CURRENT_DIR}/oradb.dmp";
EXP_PARA_OUTPUT_LOG_FILE="${CURRENT_DIR}/log/output.log";
IMP_PARA_OUTPUT_LOG_FILE="${CURRENT_DIR}/log/input.log";

BACKUP_DIR="${CURRENT_DIR}/backup";
DATAFILE_BACKUP_DIR="${BACKUP_DIR}/datafile";
LOG_BACKUP_DIR="${BACKUP_DIR}/log";

#没有找到数据库配置文件
EXIT_CONFIGURE_FILE_NOT_FOUND="The Database Configure File not Found,the Process will Exit."
#数据库配置文件配置项有误
EXIT_CONFIGURE_FILE_ERROR="The Database Configure File is Incorrect.";

#导出成功总数
EXP_SUCCESS_NUM_RECORD="0";
#导出失败总数
EXP_FAIL_NUM_RECORD="0";
#导入成功总数
IMP_SUCCESS_NUM_RECORD="0";
#导入失败总数
IMP_FAIL_NUM_RECORD="0";


#脚本退出接口
#输入参数：
# 1.exit_code:退出码
# 2.exit_message:退出消息
function exitScript()
{
exit_code=$1;
exit_message=$2;

[ "_${exit_code}" != "_0" ] && printMsg "[ERROR]${exit_message}";
[ "_${exit_code}" = "_0" ] && printMsg "[SUCCESSFUL]${exit_message}";

exit ${exit_code};
}

function printMsg()
{
message=$1;
echo "${message}" | tee -a ${LOG_FILE};
}

#函数功能：
# 1.创建日志目录和日志文件
# 2.读取数据库配置文件
# 3.检查导入数据库和导出数据库是否可以连接
function init()
{
[ ! -d ${LOG_DIR} ] && mkdir -p ${LOG_DIR};

[ ! -d ${BACKUP_DIR} ] && mkdir -p ${BACKUP_DIR};

[ ! -d ${DATAFILE_BACKUP_DIR} ] && mkdir -p ${DATAFILE_BACKUP_DIR};

[ ! -d ${LOG_BACKUP_DIR} ] && mkdir -p ${LOG_BACKUP_DIR};

#备份数据文件
[ -f ${EXP_PARA_OUTPUT_DATA_FILE} ] && mv ${EXP_PARA_OUTPUT_DATA_FILE} ${DATAFILE_BACKUP_DIR};

#备份日志文件
[ -f ${LOG_FILE} ] && mv ${LOG_FILE} ${LOG_BACKUP_DIR};
[ -f ${EXP_PARA_OUTPUT_LOG_FILE} ] && mv ${EXP_PARA_OUTPUT_LOG_FILE} ${LOG_BACKUP_DIR};
[ -f ${IMP_PARA_OUTPUT_LOG_FILE} ] && mv ${IMP_PARA_OUTPUT_LOG_FILE} ${LOG_BACKUP_DIR};

[ ! -f ${LOG_FILE} ] && touch ${LOG_FILE};

#将日志置空
echo "At `date '+%Y-%m-%d %H:%M:%S'` Begin to Export and Import Data." > ${LOG_FILE};

readConfig

#检查导出数据库是否可以连接
printMsg "Check the Connection of Database(${EXP_USERNAME}/${EXP_PASSWORD}@${EXP_DATABASE_IP}:${EXP_DATABASE_PROT}/${EXP_SID})";
result=`checkDatabaseConn ${EXP_USERNAME} ${EXP_PASSWORD} ${EXP_DATABASE_IP} ${EXP_DATABASE_PROT} ${EXP_SID}`;
if [ "_${result}" == "_0" ]
then
printMsg "Check the Connection successfully.";
else
exitScript 1 "Check the Connection failed,the Process will Exit.";
fi;

#检查导入数据库是否可以连接
printMsg "Check the Connection of Database(${IMP_USERNAME}/${IMP_PASSWORD}@${IMP_DATABASE_IP}:${IMP_DATABASE_PROT}/${IMP_SID})";
result=`checkDatabaseConn ${IMP_USERNAME} ${IMP_PASSWORD} ${IMP_DATABASE_IP} ${IMP_DATABASE_PROT} ${IMP_SID}`;
if [ "_${result}" == "_0" ]
then
printMsg "Check the Connection successfully.";
else
exitScript 1 "Check the Connection failed,the Process will Exit.";
fi;

printMsg "";
}

#读取数据库配置文件
function readConfig()
{
[ ! -f ${DB_CONFIGURE_FILE} ] && exitScript 1 "${EXIT_CONFIGURE_FILE_NOT_FOUND}"

IMP_USERNAME="`grep 'IMP_USERNAME' ${DB_CONFIGURE_FILE} | awk -F"=" '{print $2}'`";
IMP_PASSWORD="`grep 'IMP_PASSWORD' ${DB_CONFIGURE_FILE} | awk -F"=" '{print $2}'`";
IMP_DATABASE_IP="`grep 'IMP_DATABASE_IP' ${DB_CONFIGURE_FILE} | awk -F"=" '{print $2}'`";
IMP_DATABASE_PROT="`grep 'IMP_DATABASE_PROT' ${DB_CONFIGURE_FILE} | awk -F"=" '{print $2}'`";
IMP_SID="`grep 'IMP_SID' ${DB_CONFIGURE_FILE} | awk -F'=' '{print $2}'`";

EXP_USERNAME="`grep 'EXP_USERNAME' ${DB_CONFIGURE_FILE} | awk -F"=" '{print $2}'`";
EXP_PASSWORD="`grep 'EXP_PASSWORD' ${DB_CONFIGURE_FILE} | awk -F"=" '{print $2}'`";
EXP_DATABASE_IP="`grep 'EXP_DATABASE_IP' ${DB_CONFIGURE_FILE} | awk -F"=" '{print $2}'`";
EXP_DATABASE_PROT="`grep 'EXP_DATABASE_PROT' ${DB_CONFIGURE_FILE} | awk -F"=" '{print $2}'`";
EXP_SID="`grep 'EXP_SID' ${DB_CONFIGURE_FILE} | awk -F"=" '{print $2}'`";

[ -z ${IMP_USERNAME} ] || [ -z ${IMP_PASSWORD} ] || [ -z ${IMP_DATABASE_IP} ] || [ -z ${IMP_DATABASE_PROT} ] || [ -z ${IMP_SID} ] || [ -z ${EXP_USERNAME} ] || [ -z {EXP_PASSWORD} ] || [ -z ${EXP_DATABASE_IP} ] || [ -z ${EXP_DATABASE_PROT} ] || [ -z ${EXP_SID} ] && exitScript 1 "${EXIT_CONFIGURE_FILE_ERROR}"

}
#检查使用指定的用户、密码是否可以登陆到指导的数据库
#输入参数：
# $1 登陆数据库的用户名
# $2 登陆密码
# $3 数据库所在主机的ip
# $4 数据库的监听端口
# $5 数据库实例名
#输出参数
# 返回1:数据库连接失败
# 返回0:数据库连接成功
function checkDatabaseConn()
{
USERNAME=$1;
PASSWORD=$2;
DATABASE_IP=$3;
DATABASE_PORT=$4;
DATABASE_SID=$5;

sqlplus "${USERNAME}/${PASSWORD}@${DATABASE_IP}:${DATABASE_PROT}/${DATABASE_SID}" <<EOF > /dev/null 2>&1

exit;
EOF
if [ $? != 0 ]
then
echo "1";
fi;

echo "0";
}

function expData()
{
USERNAME=$1;
PASSWORD=$2;
DATABASE_IP=$3;
DATABASE_PORT=$4;
DATABASE_SID=$5;

exp ${USERNAME}/${PASSWORD}@${DATABASE_IP}:${DATABASE_PORT}/${DATABASE_SID} file=${EXP_PARA_OUTPUT_DATA_FILE} log=${EXP_PARA_OUTPUT_LOG_FILE} owner=${USERNAME} buffer=4096000 feedback=10000 > /dev/null 2>&1

LAST_LINE="`tail -n 1 ${EXP_PARA_OUTPUT_LOG_FILE}`";
SUCCESS_FLAG="`echo "${LAST_LINE}" | grep " successfully"`";
if [ ! -n "${SUCCESS_FLAG}" ]
then
#导出失败，打印错误信息并退出。
exitScript 1 "${LAST_LINE},want know detailed information, please reading the output.log";
fi;

EXP_SUCCESS_NUM_RECORD="0";
for SINGLE_TABLE_NUM_SUCCESS in `grep "rows exported" ${EXP_PARA_OUTPUT_LOG_FILE} | awk '{print $1}'`
do
EXP_SUCCESS_NUM_RECORD=`expr ${EXP_SUCCESS_NUM_RECORD} + ${SINGLE_TABLE_NUM_SUCCESS}`;
done;

#导出成功

printMsg "${LAST_LINE} total ${EXP_SUCCESS_NUM_RECORD} records.";
}

function impData()
{
USERNAME=$1;
PASSWORD=$2;
DATABASE_IP=$3;
DATABASE_PORT=$4;
DATABASE_SID=$5;
FROM_USERNAME=$6;

sqlplus ${USERNAME}/${PASSWORD}@${DATABASE_IP}:${DATABASE_PORT}/${DATABASE_SID} <<EOF > /dev/null 2>&1
declare
--查询用户下所有外键约束名和相应的表名
cursor c_Constraints is
select t.table_name,t.constraint_name from user_constraints t where t.constraint_type='R';
--查询用户下的所有表名
cursor c_User_tables is
select t.table_name from user_tables t;
begin
----遍历游标 将用户下的所有外键约束设为失效的。
for c_Const in c_Constraints loop
execute immediate 'ALTER TABLE '|| c_Const.table_name||' DISABLE CONSTRAINT '||c_Const.constraint_name;
end loop;

----遍历游标，截断用户下的所有表中的数据
for c_table in c_User_tables loop
execute immediate 'truncate table '||c_table.table_name;
end loop;

----遍历游标 将用户下的所有外键约束设为有效的。
for c_Const in c_Constraints loop
execute immediate 'ALTER TABLE '|| c_Const.table_name||' ENABLE CONSTRAINT '||c_Const.constraint_name;
end loop;
end;
/
commit;

exit;
EOF

imp ${USERNAME}/${PASSWORD}@${DATABASE_IP}:${DATABASE_PORT}/${DATABASE_SID} file=${EXP_PARA_OUTPUT_DATA_FILE} log=${IMP_PARA_OUTPUT_LOG_FILE} fromuser=${FROM_USERNAME} touser=${USERNAME} buffer=2048000 commit=y ignore=y feedback=10000 > /dev/null 2>&1

IMP_SUCCESS_NUM_RECORD="0";
for SINGLE_TABLE_NUM_SUCCESS in `grep "rows imported" ${IMP_PARA_OUTPUT_LOG_FILE} | awk '{print $1}'`
do
IMP_SUCCESS_NUM_RECORD=`expr ${IMP_SUCCESS_NUM_RECORD} + ${SINGLE_TABLE_NUM_SUCCESS}`;
done;

#printMsg "The Import's Total Number is ${IMP_SUCCESS_NUM_RECORD}";

if [ ${EXP_SUCCESS_NUM_RECORD} -eq ${IMP_SUCCESS_NUM_RECORD} ]
then
printMsg "Import all data successfully,total ${IMP_SUCCESS_NUM_RECORD} records.";
else
IMP_FAIL_NUM_RECORD=`expr ${EXP_SUCCESS_NUM_RECORD} - ${IMP_SUCCESS_NUM_RECORD}`;
printMsg "Succeed numbers:${IMP_SUCCESS_NUM_RECORD},Failed numbers:${IMP_FAIL_NUM_RECORD},want be know detailed information, please reading the input.log";
fi;
}

function main()
{
init

printMsg "Export Data from Database(${EXP_DATABASE_IP})";
expData ${EXP_USERNAME} ${EXP_PASSWORD} ${EXP_DATABASE_IP} ${EXP_DATABASE_PROT} ${EXP_SID}

printMsg "Import Data to Database(${IMP_DATABASE_IP}) form Database(${EXP_DATABASE_IP})";
impData ${IMP_USERNAME} ${IMP_PASSWORD} ${IMP_DATABASE_IP} ${IMP_DATABASE_PROT} ${IMP_SID} ${EXP_USERNAME}

#echo "At `date '+%Y-%m-%d %H:%M:%S'` Complete Data Synchronization." > ${LOG_FILE};
}

main


2。配置文件
#数据导入数据库配置
IMP_USERNAME=wjh
IMP_PASSWORD=wjh
IMP_DATABASE_IP=10.38.132.81
IMP_DATABASE_PROT=1521
IMP_SID=ORACLE

#数据导出数据库配置
EXP_USERNAME=myvpn
EXP_PASSWORD=myvpnpwd
EXP_DATABASE_IP=10.38.202.147
EXP_DATABASE_PROT=1521
EXP_SID=sysdb