oracle 提供一种基于高级复制功能,实现两个数据库中的相同的表数据同步。
目前到ORACLE 11G 以后,以流复制为主,将高级复制迁移到流复制中。
在高级复制中,实现表与表之 间的同步,有同步模式与异步模式。
可以实现基于每秒钟传输同步或实时同步。
但考虑到数据量的大小及DDL操作,会造成锁表,死锁相关原因,建议采用异步模式同步,应用于现在的生产环境。
1、模型情况
2、 相关环境情况
JDCPRO 中的参数配置:
SQL> connect sys/oracle@jdcpro as sysdba;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as SYS
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
JDCPRO
DB_LINK 情况:
QL> select * from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
------------------------------ -------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- -----------
PUBLIC JDCAPP REPADMIN jdcapp 2014-9-22 1
SQL>
创建设一个针对于对方的DB_LINK ,
如在JDCPRO上,创建:DB_LINK名字是:jdcapp 数据
在JDCAPP上,创建设DB_LINK名字是:jdcpro
针对同一个用户:repadmin创建设。
考虑到repadmin的权限问题,一般给DBA 权限搞定。
监听情况(两台机器上都配置一样):
jdcpro =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.34.109)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
jdcapp =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.34.108)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
数据同步主体节点认为:jdcapp
数据传输到( 复制的节点)为:jdcpro
所以在复制节点上进行配置,将JDCAPP作为数据源进行。
SQL> select * from test.starmoon;
DEPTNO DNAME LOC
------ -------------- -------------
1 accounting new york
2 research dallas
SQL> create table test.ma01 as select * from test.dept;
Table created
添加一个主键,保持表的唯一性
SQL> alter table test.ma01 add (constraint pk_m1 primary key ((deptno)));
Table altered
SQL> truncate table test.ma01 ;
Table truncated
SQL> insert into test.ma01 select * from test.ma01@jdcapp;
2 rows inserted
SQL> commit;
Commit complete
在JDCPRO主机上,配置:
添加一个复制的组:mm
SQL> execute dbms_repcat.create_master_repgroup('mm');
PL/SQL procedure successfully completed
SQL> select gname,master status from dba_repgroup;
GNAME STATUS
------------------------------ ------
TEST_MG Y
REP_GROUP Y
APP Y
STAR Y
MM Y
添加一个MASTER定数据库,作为主体数据一部分:
SQL> execute dbms_repcat.create_master_repobject(sname => 'test',oname => 'ma01',type => 'table',use_existing_object => true,copy_rows => false,gname => 'mm');
PL/SQL procedure successfully completed
Sname: 复制的用户
Oname: 所复制的表
Type : 所复制的类型
Use_existing_object: 使用现有的对象
Copy_rows :如果原来有数据,是否进行同步。
Gname: 复制组名。
SQL> select sname,oname,status,gname from dba_repobject where gname='MM';
SNAME ONAME STATUS GNAME
------------------------------ ------------------------------ ---------- ------------------------------
TEST MA01 VALID MM
生成对数据对象复制的代码,存储过程等
SQL> execute dbms_repcat.generate_replication_support('test','ma01','table');
PL/SQL procedure successfully completed
SQL> select sname,oname,status,gname from dba_repobject where gname='MM';
SNAME ONAME STATUS GNAME
------------------------------ ------------------------------ ---------- ------------------------------
TEST MA01 VALID MM
TEST MA01$RP VALID MM
TEST MA01$RP VALID MM
SQL> select gname,master,status from dba_repgroup where gname='MM';
GNAME MASTER STATUS
------------------------------ ------ ---------
MM Y QUIESCED
然后再添加对数据源复制的对象,即:将JDCAPP的数据同步到JDC上,此处注意: master 参数为:jdcapp
SQL> execute dbms_repcat.add_master_database(gname => 'mm',master => 'jdcapp',use_existing_objects => true,copy_rows => false,propagation_mode => 'synchronous');
PL/SQL procedure successfully completed
定义两个之间的同步方式为:同步模式,也可以为异步模式。
SQL> select gname,dblink,masterdef,master from sys.dba_repsites where gname='MM';
GNAME DBLINK MASTERDEF MASTER
------------------------------ -------------------------------------------------------------------------------- --------- ------
MM JDCAPP N Y
MM JDCPRO Y Y
此处运行过程在:JDCpro主机上运行。
SQL> execute dbms_repcat.resume_master_activity('mm',true);
PL/SQL procedure successfully completed
SQL> select * from test.ma01;
DEPTNO DNAME LOC
------ -------------- -------------
1 accounting new york
2 research dallas
SQL> delete from test.ma01 where rownum<2;
1 row deleted
SQL> select * from test.ma01;
DEPTNO DNAME LOC
------ -------------- -------------
2 research dallas
SQL> commit;
Commit complete
SQL> select * from test.ma01;
DEPTNO DNAME LOC
------ -------------- -------------
2 research dallas
1 star dtee
SQL> connect test/test@jdcpro
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as test
SQL>
综合总结:
CENTOS 6.9 U盘安装,XMANAGER ,远程SSH2020-02-29
ORACLE 通过db_link 连接到POSTGRESQL配置2020-02-29
H3C 与CISCO 交换机链路绑定2015-05-17
全国机动车检测检验监督管理系统2015-04-18
oracle 高级复制在生产中的应用2014-09-23
云群集 HADOOP 概念与布曙2014-06-11