1、首先保证目标数据库所在服务器可以连接到源数据库
2、在目标数据库中创建dblink。
①创建连接源数据库的别名
在目标数据库的 tnsnames.ora文件中添加连接源数据库的别名,在文件中添加如下语句即可。
自定义的连接的别名 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 源数据库的IP)(PORT = 源数据库的端口号))
)
(CONNECT_DATA =
(SERVICE_NAME = 源数据库的实例名称)
)
)
例子:
test=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.4.37)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
②创建dblink
在目标数据库中执行如下sql:
create public database link 自定义连接的名称 connect to 源数据库用户名 identified by "源数据库用户密码" using '创建的连接别名';
3、dblink创建完成以后,就是定时同步的问题。
在linux系统中,我们可以编写shell文件来实现定时执行。
我创建了两个文件。一个是merge.sh 一个是merge.sql
merge.sh内容如下:
#!/bin/sh
today=`date +%Y-%m-%d-%H`
log_file=log${today}.txt
ORACLE_HOME=/oracle/product/11.2.0/dbhome_1 export ORACLE_HOME
$ORACLE_HOME/bin/sqlplus app/app@172.18.133.10/bdjzpt @/oracle/merge/merge.sql >>/oracle/merge/${log_file} 2>&1
merge.sql内容如下:
Create global temporary table temp6 on commit preserve rows as select * from METEO_DATA_COLLECT@BRANCH A where A.METEO_DATA_COLLECT_ID not in (select METEO_DATA_COLLECT_ID from METEO_DATA_COLLECT);
Create global temporary table temp2 on commit preserve rows as select * from REAL_FLOOD_RESERVOIR@BRANCH A where A.REAL_FLOOD_RESERVOIR_ID not in (select REAL_FLOOD_RESERVOIR_ID from REAL_FLOOD_RESERVOIR);
Create global temporary table temp3 on commit preserve rows as select * from EARTHQUAKE@BRANCH A where A.EARTHQUAKE_ID not in (select EARTHQUAKE_ID from EARTHQUAKE);
Create global temporary table temp4 on commit preserve rows as select * from DISASTERWARNING@BRANCH A where A.DISASTER_WARNING_ID not in (select DISASTER_WARNING_ID from DISASTERWARNING);
Create global temporary table temp5 on commit preserve rows as select * from JZ_PUBLICSIGN@BRANCH A where A.JZ_PUBLICSIGN_ID not in (select JZ_PUBLICSIGN_ID from JZ_PUBLICSIGN);
insert into METEO_DATA_COLLECT m (M.METEO_DATA_COLLECT_ID,M.FILE_BINARY,M.FILE_NAME,M.DATA_TYPE,M.ORIG_FILE_PATH,M.DATA_INTERVAL,M.UPDATE_TIME,M.CREATE_TIME,M.DESCR)select T.METEO_DATA_COLLECT_ID,T.FILE_BINARY,T.FILE_NAME,T.DATA_TYPE,T.ORIG_FILE_PATH,T.DATA_INTERVAL,T.UPDATE_TIME,T.CREATE_TIME,T.DESCR from temp6 T;
insert into REAL_FLOOD_RESERVOIR select * from temp2;
insert into EARTHQUAKE select * from temp3;
insert into DISASTERWARNING select * from temp4;
insert into JZ_PUBLICSIGN select * from temp5;
truncate table temp6;
truncate table temp2;
truncate table temp3;
truncate table temp4;
truncate table temp5;
drop table temp6;
drop table temp2;
drop table temp3;
drop table temp4;
drop table temp5;
COMMIT;
exit
关于同步的sql语句的补充:可以使用merge语句进行插入,但是我的字段中含有大字段类型(blob等),所有使用上述语句。先建临时表再插入在删除临时表的方案。
关于这两个文件中内容的解释,不懂的请自行百度
4、linux中的定时任务自定百度创建,嘻嘻