OGG双向复制oracle数据库配置

标签: | 发表时间:2020-06-19 13:06 | 作者:
出处:https://www.cnblogs.com

 

Overview of an Active-Active Configuration

Oracle GoldenGate supports an active-active bi-directional configuration, where there are two systems with identical sets of data that can be changed by application users on either system. Oracle GoldenGate replicates transactional data changes from each database to the other to keep both sets of data current.

In a bi-directional configuration, there is a complete set of active Oracle GoldenGate processes on each system. Data captured by an Extract process on one system is propagated to the other system, where it is applied by a local Replicat process.

This configuration supports load sharing. It can be used for disaster tolerance if the business applications are identical on any two peers. Bidirectional synchronization is supported for all database types that are supported by Oracle GoldenGate.

Oracle GoldenGate supports active-active configurations for:

·      DB2 on z/OS, LUW, and IBM i

·      MySQL

·      Oracle

·      SQL Server

·      Teradata

Oracle GoldenGate supports DDL replication in an Oracle active-active configuration. DDL support is available for Oracle Database, MySQL, and Teradata databases.

 

Preventing Data Looping

In a bidirectional configuration, SQL changes that are replicated from one system to another must be prevented from being replicated back to the first system. Otherwise, it moves back and forth in an endless loop, as in this example:

  1. A user application updates a row on system A.
  2. Extract extracts the row on system A and sends it to system B.
  3. Replicat updates the row on system B.
  4. Extract extracts the row on system B and sends it back to system A.
  5. The row is applied on system A (for the second time).
  6. This loop continues endlessly.

To prevent data loopback, you may need to provide instructions that:

  • prevent the capture of SQL operations that are generated by Replicat, but enable the capture of SQL operations that are generated by business applications if they contain objects that are specified in the Extract parameter file.
  • identify local Replicat transactions, in order for the Extract process to ignore them.

 

Preventing the Capture of Replicat Transactions (Oracle)

To prevent the capture of SQL that is applied by Replicat to an Oracle database, there are different options depending on the Extract capture mode:

·      When Extract is in classic or integrated capture mode, use the TRANLOGOPTIONSparameter with the EXCLUDETAG tagoption. This parameter directs the Extract process to ignore transactions that are tagged with the specified redo tag.

·      When Extract is in classic capture mode, use the Extract TRANLOGOPTIONSparameter with the EXCLUDEUSERor EXCLUDEUSERIDoption to exclude the user name or ID that is used by Replicat to apply the DDL and DML transactions. Multiple EXCLUDEUSERstatements can be used. The specified user is subject to the rules of the GETREPLICATESor IGNOREREPLICATESparameter.

 

Identifying Replicat Transactions (Oracle)

There are multiple ways to identify Replicat transaction in an Oracle environment. When Replicat is in classic or integrated mode, you use the following parameters:

  • Use DBOPTIONS with the SETTAG option in the Replicat parameter file. Replicat tags the transactions being applied with the specified value, which identifies those transactions in the redo stream. The default SETTAG value is 00. Valid values are a single TAG value consisting of hexadecimal digits.
  • Use the TRANLOGOPTIONS parameter with the EXCLUDETAG option in the Extract parameter file. The logmining server associated with that Extract excludes redo that is tagged with the SETTAG value.

·      By using SETTAG and EXCLUDETAG you have more flexibility on how you configure replication between various databases.  For Oracle Database 12c container databases this is the only option to avoid data looping.

The following shows how SETTAG can be set in the Replicat parameter file:

dboptions settag 0935

 

The following shows how EXCLUDETAG can be set in the Extract parameter file:

tranlogoptions excludetag 0935

If you are excluding multiple tags, each must have a separate TRANLOGOPTIONS EXCLUDETAG statement specified.

You can also use the transaction name or userid of the Replicat user to identify Replicat transactions. You can choose which of these to ignore when you configure Extract.

 

Configuration for DDL replication

After Oracle GoldenGate 12.1.2, you no longer need to run the SQL scripts such as  marker_setup.sql and  ddl_setup.sql to set up the DDL replication. Using the GoldenGate Integrated Capture for Oracle Database, the native DDL replication is very easy to setup.

You need to use DDL INCLUDE to specify what DDL operations you need to capture. The following example includes ALL the DDL operations. To enable replication of the newly added tables, we can also add the  DDLOPTIONS ADDTRANDATA in the parameter file or run  ADD SCHEMATRANDATA in GGCSI.

ddl include all

ddloptions addtrandata, report

 

 

 

 

 

 

Creating an Active-Active configuration

 

 

The figure shows Oracle GoldenGate Configuration for Active-active Synchronization. In below example, the configuration would be deployed between Oracle 12c pdbs. We will configure pdbsrc as primary system, pdbtgt as secondary system.

 

Prerequisites

Open Archive Log:

SQL>startup mount;

SQL>alter database archivelog;

SQL>alter database open;

 

Open supplemental log data and force logging

SQL>SELECT supplemental_log_data_min, force_logging FROM v$database;

SQL>alter database add supplemental log data;

SQL>alter database force logging;

SQL>ALTER SYSTEM switch logfile;

 

Enabling Oracle GoldenGate in the Database:

SQL> alter system set enable_goldengate_replication = true scope=both;   

 

Create OGG Users

SQL> startup

SQL> alter PLUGGABLE database all open;

SQL>create user c##oggadmin identified by oracle;

SQL>GRANT DBA to c##oggadmin container=all;

SQL> exec dbms_goldengate_auth.grant_admin_privilege('c##oggadmin',container=>'ALL');

SQL> alter session set container=pdbsrc;

SQL> create user west identified by oracle;

SQL> grant connect,resource to west;

SQL> alter user west quota unlimited on users;

SQL> alter session set container=pdbtgt;

SQL> create user east identified by oracle;

SQL> grant connect,resource to east;

SQL> alter user east quota unlimited on users;

 

 

Configuration from Primary System to Secondary System (pdbsrc to pdbtgt)

Configure the Extract Process (pdbsrc)

GGSCI (orcl) 59> view param etwest

extract etwest

userid c##oggadmin,password oracle

exttrail ./dirdat/ew

ddl include all

TranLogOptions Excludetag 00

sourcecatalog pdbsrc

table west.*;

 

GGSCI (orcl) 1> add extract etwest,integrated tranlog, begin now

GGSCI (orcl) 2> add exttrail ./dirdat/ew, extract etwest, megabytes 20

GGSCI (orcl) 5> dblogin userid c##oggadmin, password oracle

GGSCI (orcl as c##oggadmin@disdb/CDB$ROOT) 6> register extract etwest database container(pdbsrc)

GGSCI (orcl as c##oggadmin@disdb/CDB$ROOT) 7> add schematrandata pdbsrc.west

 

 

Configure the Data Pump Process (pdbsrc)

GGSCI (orcl) 60> view param pmwest

extract pmwest

passthru

rmthost orcl,mgrport 7909

rmttrail ./dirdat/rw

table pdbsrc.west.*;

 

GGSCI (orcl) 3> add extract pmwest, exttrailsource ./dirdat/ew

GGSCI (orcl) 4> add rmttrail ./dirdat/rw, extract pmwest, megabytes 20

 

Configure the Replicat Process (pdbtgt)

GGSCI (orcl) 61> view param rpeast

replicat rpeast                                             

userid c##oggadmin@pdbsrc, password oracle

discardfile rpeast.dsc, append, megabytes 10

ddl include all

map pdbtgt.east.*, target pdbsrc.west.*;

 

GGSCI (orcl) 8> add replicat rpeast, integrated ,exttrail ./dirdat/re

 

 

Configuration from Secondary System to Primary System (pdbtgt to pdbsrc)

  Configure the Extract Process (pdbtgt)

GGSCI (orcl) 135> view param eteast

extract eteast

userid c##oggadmin,password oracle

exttrail ./dirdat/ee

ddl include all

TranLogOptions Excludetag 00

sourcecatalog pdbtgt

table east.*;

 

GGSCI (orcl) 2> add extract eteast,integrated tranlog, begin now

GGSCI (orcl) 3> add exttrail ./dirdat/ee, extract eteast, megabytes 20

GGSCI (orcl) 6> dblogin userid c##oggadmin, password oracle

GGSCI (orcl as c##oggadmin@disdb/CDB$ROOT) 7> register extract eteast database container(pdbtgt)

GGSCI (orcl as c##oggadmin@disdb/CDB$ROOT) 8> add schematrandata pdbtgt.east

 

 

Configure the Data Pump Process (pdbtgt)

GGSCI (orcl) 136> view param pmeast

extract pmeast

passthru

rmthost orcl,mgrport 7809

rmttrail ./dirdat/re

table pdbtgt.east.*;

 

GGSCI (orcl) 1> add replicat rpwest, integrated ,exttrail ./dirdat/rw

GGSCI (orcl) 4> add extract pmeast, exttrailsource ./dirdat/ee

 

Configure the Replicat Process (pdbsrc)

GGSCI (orcl as c##oggadmin@disdb/CDB$ROOT) 138> view param rpwest

replicat rpwest                                            

userid c##oggadmin@pdbtgt, password oracle

discardfile rpwest.dsc, append, megabytes 10

ddl include all

map pdbsrc.west.*, target pdbtgt.east.*;

 

GGSCI (orcl) 5> add rmttrail ./dirdat/re, extract pmeast, megabytes 20

 

相关 [ogg 复制 oracle] 推荐:

OGG双向复制oracle数据库配置

- -
Oracle GoldenGate supports an active-active bi-directional configuration, where there are two systems with identical sets of data that can be changed by application users on either system.

oracle ogg goldengate 双活复制避免循环复制参数_ITPUB博客

- -
我简单的简绍一下goldengate的一些实用的、常用的参数. 一、双向复制避免数据循环复制的参数. 首先说明一下循环复制,官网上的描述:. 意译:主端对数据的修改,被应用到了备端. 但是备端在执行这个主端传递过来的数据改变时,又被备端的extract 进程.       扑获到,并且又反给主端. 然后主端又给备端,这样形成了循环复制,会一直循环下去.

使用OGG,两个Oracle库之间单向同步数据

- - CSDN博客数据库推荐文章
配置:源数据库100.100.100.21         实例名dbsid2.             目标数据库100.100.100.41       实例名db1. 实验目标:源数据库中的 scott用户 emp表同步到目标数据库 scott中的test表. 安装包为文件ogg112101_fbo_ggs_Linux_x64_ora10g_64bit.zip.

基于OGG的Oracle与Hadoop集群准实时同步介绍 - 偶素浅小浅 - 博客园

- -

利用ogg实现oracle到kafka的增量数据实时同步 | 伦少的博客

- -
ogg即Oracle GoldenGate是Oracle的同步工具,本文讲如何配置ogg以实现Oracle数据库增量数据实时同步到kafka中,其中同步消息格式为json. 下面是我的源端和目标端的一些配置信息:. 注意:源端和目标端的文件不一样,目标端需要下载Oracle GoldenGate for Big Data,源端需要下载Oracle GoldenGate for Oracle具体下载方法见最后的附录截图.

OGG工作原理 - 关系型数据库 - 亿速云

- -
发布时间:2020-08-07 04:46:09来源:ITPUB博客阅读:122作者:白盲丶栏目:. 一.GoldenGate介绍. OGG 是一种基于日志的结构化数据复制软件. OGG 能够实现大量交易数据的实时捕捉,变换和投递,实现源数据库与目标数据库的数据同步,保持最少10ms的数据延迟. (1).Trail文件的生成和删除.

Oracle 收购 Ksplice

- feng823 - LinuxTOY
实现无需重启即可为 Linux 内核打安全补丁的 Ksplice 被 Oracle 收购. 在被收购前, Ksplice 为 Fedora, Ubuntu 免费提供该功能,对于 RHEL 和 CentOS 则需要订阅其产品. Oracle 表示将把 Ksplice 带来的零宕机安全更新功能添加到 Oracle 产品订阅服务中,同时停止对其他企业级 Linux 发行版的支持,将 Oracle Unbreakable Linux 打造成唯一具备零宕机安全更新功能的企业级 Linux 发行版.

Linux Ksplice,MySQL and Oracle

- Syn - DBA Notes
Oracle 在 7 月份收购了 Ksplice. 使用了 Ksplice 的 Linux 系统,为 Kernel 打补丁无需重启动,做系统维护的朋友应该明白这是一个杀手级特性. 现在该产品已经合并到 Oracle Linux 中. 目前已经有超过 700 家客户,超过 10 万套系统使用了 Ksplice (不知道国内是否已经有用户了.

oracle license计算

- Fenng - eagle's home
Oracle license的计算是基于CPU core的. 用core的数目乘以一个系数core factor就可以得到所需的oracle license的数目. 对于不同的CPU,core factor是不一样的,可以从oracle提供的这张列表中查到 Oracle Processor Core Factor Table.

Oracle Exadata初探

- - 技术改变世界 创新驱动中国 - 《程序员》官网
在我们看来,它是一个把硬件和软件根据合理的配置整合在一起的 Oracle数据库(在本文编写时是11gR2版本)平台. Exadata数据库机器包含了存储子系统,在存储层上运行着研发的新软件,这使得研发人员可以做一些在其他平台上无法完成的事情. 实际上,Exadata一开始是以一个存储系统形式诞生的,如果你跟参与研发此产品的人交谈,你经常会听到他们称存储组件为Exadata或者是SAGE (Storage Appliance for Grid Environments,网格环境存储设备),这是该产品研发项目的代码名称.