1,在目标机上建立Oracle DB Link:
A,在network/admin/tnsname.ora文件中加入源库的连接信息,如:
AAA=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.1.3)(PORT=1521))
(CONNECT_DATA=(SERVER=DEDICATED)
(SERVICE_NAME=AAA)
)
)
B,在目标机上用sqlplus user/pwd登录。
C,用如下命令建立DB Link:
createpublicdatabase link AAA_LINK connect to user identified by pwd using'AAA';
命令说明:
CREATE PUBLIC DATABASE LINK 数据库链接名 CONNECT TO 用户名 IDENTIFIED BY 密码 USING ‘本地配置的数据的实例名’;
如果建立成功,会提示:Database link created.
查看状态
select status from dba_objects where object_type='DATABASE LINK';
2,使用如下脚本,即可同步数据表:
#!/bin/sh
# to sync table A to BBB database from AAA databasesqlplus user/pwd@BBB<truncatetable A;
insert into A
select*from b_schema.A@AAA_LINK;#这里是指向要同步的来源表,表名必须是.@commit;
PS:需要DB支持Advanced replication功能,是否支持,可用如下SQL查看:
select*fromv$optionwherePARAMETER='Advanced replication';
如果是返回True就表示支持。