数据库同步

junge8618

浏览: 121049 次
性别:
来自: 邵阳

最近访客更多访客>>

pp_dadi

shilianjun

supersyd

willow_stream

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

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

分享到：