最近客户异地新增了一套设备,并建了对应本地数据库,由于业务需要,需要对新库和老库上的业务进行同步处理,之前采用的是dblink方案,实际使用的网络环境没有配备专线,而是使用现有公司电信宽带资源,可想而知实时处理业务时网络成了最大瓶颈,超高的延迟影响业务正常进行。在和两地生产部门沟通后,反馈数据源每天的变化并不大,目标库上的业务处理可以接受这部分的延迟,于是我修改了原来的方案,改dblink为数据定时同步到本地。以下为具体步骤:
--远程
exp system/******@192.168.**.**:1521/AQ** file=/arch/qujin_data.dmp full=y
SYSTEM/******
#要对sh文件授权chmod u+x *.sh
--本地(曲靖),拆分成2个批处理文件,一个负责导出数据,一个负责把导出的数据传输到远程服务器
#!/bin/bash
if [ -f ~/.bash_profile ];
then
. ~/.bash_profile
fi
set timeout -1
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
exp system/****** file=/backup_rman/qujin_data.dmp full=y
cd /backup_rman
zip -r qujin_data.zip ./qujin_data.dmp
rm -rf qujin_data.dmp
#!/usr/local/bin/expect
set timeout -1
cd /backup_rman
spawn scp qujin_data.zip root@192.**.**.**:/arch/qujin_data.zip
#spawn scp test.txt root@192.**.**.**:/arch/test.txt
expect "*password:"
send "******\r"
expect eof
rm -rf qujin_data.zip
#添加任务
crontab -e
0 0 * * * /home/oracle/shscript/data_exp.sh
30 1 * * * /home/oracle/shscript/data_tran.sh
--689 vi data_imp.sh ,#要对sh文件授权chmod u+x *.sh
#!/bin/bash
if [ -f ~/.bash_profile ];
then
. ~/.bash_profile
fi
if [ ! -f "/arch/qujin_data.zip" ];then
echo "Warning:File[/arch/qujin_data.zip] not found."
exit
else
echo "Message:File[/arch/qujin_data.zip] found."
fi;
cd /arch
unzip -o qujin_data.zip
chown oracle:dba qujin_data.dmp
echo sqlplus...
sqlplus -s 'sys/******@aq*** as sysdba'<<EOF
set feed off;
set heading off;
set feedback off;
set verify off;
begin
for rec in(
select 'alter system kill session '''||sid||','||serial#||'''' sql_text
from v\$session t
where username in('SAA_MES','HIS_MES','SAA_INTERFACE','ANDON')) loop
EXECUTE IMMEDIATE rec.sql_text;
END LOOP;
END;
/
drop user SAA_MES cascade;
drop user HIS_MES cascade;
drop user SAA_INTERFACE cascade;
drop user ANDON cascade;
create user SAA_MES identified by **** default tablespace CGLMES;
ALTER USER SAA_MES QUOTA UNLIMITED ON ANDON;
ALTER USER SAA_MES QUOTA UNLIMITED ON MES;
ALTER USER SAA_MES QUOTA UNLIMITED ON CGLMES;
create user HIS_MES identified by **** default tablespace CGLMES;
ALTER USER HIS_MES QUOTA UNLIMITED ON ANDON;
ALTER USER HIS_MES QUOTA UNLIMITED ON MES;
ALTER USER HIS_MES QUOTA UNLIMITED ON CGLMES;
create user SAA_INTERFACE identified by **** default tablespace MES;
ALTER USER SAA_INTERFACE QUOTA UNLIMITED ON ANDON;
ALTER USER SAA_INTERFACE QUOTA UNLIMITED ON MES;
ALTER USER SAA_INTERFACE QUOTA UNLIMITED ON CGLMES;
create user ANDON identified by **** default tablespace ANDON;
ALTER USER ANDON QUOTA UNLIMITED ON ANDON;
ALTER USER ANDON QUOTA UNLIMITED ON MES;
ALTER USER ANDON QUOTA UNLIMITED ON CGLMES;
grant create session to SAA_MES;
grant create session to HIS_MES;
grant create session to SAA_INTERFACE;
grant create session to ANDON;
grant select any table to SAA_MES;
grant select any table to HIS_MES;
grant select any table to SAA_INTERFACE;
grant select any table to ANDON;
grant under any view to SAA_MES with admin option;
grant under any view to HIS_MES with admin option;
grant under any view to SAA_INTERFACE with admin option;
grant under any view to ANDON with admin option;
grant unlimited tablespace to SAA_MES with admin option;
grant unlimited tablespace to HIS_MES with admin option;
grant unlimited tablespace to SAA_INTERFACE with admin option;
grant unlimited tablespace to ANDON with admin option;
exit;
EOF
echo data_import...
imp system/abc123***@192.**.**.**/aqjdb file=/arch/qujin_data.dmp log=/arch/appdata_import1.log fromuser=SAA_MES touser=SAA_MES ignore=y
imp system/abc123***@192.**.**.**/aqjdb file=/arch/qujin_data.dmp log=/arch/appdata_import2.log fromuser=HIS_MES touser=HIS_MES ignore=y
imp system/abc123***@192.**.**.**/aqjdb file=/arch/qujin_data.dmp log=/arch/appdata_import3.log fromuser=SAA_INTERFACE touser=SAA_INTERFACE ignore=y
imp system/abc123***@192.**.**.**/aqjdb file=/arch/qujin_data.dmp log=/arch/appdata_import4.log fromuser=ANDON touser=ANDON ignore=y
rm -rf qujin_data.dmp
#rm -rf qujin_data.zip
/*
创建表空间
create tablespace MES datafile '/oracle/oradata/aqjdb/MES01.dbf' size 30720M;
alter tablespace MES add datafile '/oracle/oradata/aqjdb/MES02.dbf' size 30720M;
alter tablespace MES add datafile '/oracle/oradata/aqjdb/MES03.dbf' size 30720M;
alter tablespace MES add datafile '/oracle/oradata/aqjdb/MES04.dbf' size 30720M;
alter tablespace MES add datafile '/oracle/oradata/aqjdb/MES05.dbf' size 30720M;
create tablespace ANDON datafile '/oracle/oradata/aqjdb/ANDON01.dbf' size 30720M;
create tablespace CGLMES datafile '/oracle/oradata/aqjdb/CGLMES01.dbf' size 30720M;
alter tablespace CGLMES add datafile '/oracle/oradata/aqjdb/CGLMES02.dbf' size 30720M;
alter tablespace CGLMES add datafile '/oracle/oradata/aqjdb/CGLMES03.dbf' size 30720M;
*/
#添加任务
crontab -e
[oracle@mesoracle ~]$ crontab -e
0 16 * * * /arch/data_imp.sh
【完成】