oracle异地数据库定时同步

最新推荐文章于 2023-08-28 16:53:26 发布

慢工也能出细活

最新推荐文章于 2023-08-28 16:53:26 发布

阅读量624

点赞数

分类专栏： oracle管理文章标签： oracle数据同步

本文链接：https://blog.csdn.net/bendywu/article/details/97779760

版权

oracle管理专栏收录该内容

15 篇文章 2 订阅

订阅专栏

最近客户异地新增了一套设备，并建了对应本地数据库，由于业务需要，需要对新库和老库上的业务进行同步处理，之前采用的是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

【完成】