News

  1. Home
  2. All posts
All posts, Golden Gate, Oracle by Team Ajara

Oracle GoldenGate Scenario: Cascading replication

Cascading replication by using Oracle GoldenGate.

1. Scenario:

1.1.    Creating a Cascading Reporting Configuration

Oracle GoldenGate supports cascading synchronization, where Oracle GoldenGate propagates data changes from the source database to a second database, and then on to a third database. In this configuration:

 

 

Sno Config item Site1 Site2 site3 site4 site5
1 GoldenGate software location ogg1 ogg2 ogg3 ogg4 ogg5
3 GoldenGate database user ID/password gg1_owner gg2_owner gg3_owner gg4_owner gg4_owner
4 Manager port 7809 7810 7811 7812 7813
5 Database name US UK IND RUS AUS
6 Schema POS_US POS_UK POS_IND POS_RUS POS_AUS
7 extract name EXT_1 EXT_2
8 Extract trail filename ~/dirdat/L1 ~/dirdat/L2
9  Datapump name pump_1 PUMP_2
PUMP_3
PUMP_4
10 Remote trail filename  ~/dirdat/R1  ~/dirdat/R2  ~/dirdat/R3  ~/dirdat/R4
11 Replicat name REP_1 REP_2 REP_3 REP_4

 

 

 

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

 

add trandata POS_WEST.inventory cols (prod_category,qty_in_stock, last_dml)

add trandata POS_UK.inventory cols (prod_category,qty_in_stock, last_dml)

add trandata POS_IND.inventory cols (prod_category,qty_in_stock, last_dml)

add trandata POS_AUS.inventory cols (prod_category,qty_in_stock, last_dml)

add trandata POS_RUS.inventory cols (prod_category,qty_in_stock, last_dml)

 

Create the following objects on all databases (Site 1 and Site 2,Site 3,site 4 and site 5)

 

create user POS_WEST identified by POS_WEST default tablespace users temporary tablespace temp;

grant  connect,resource  to POS_WEST;

grant unlimited tablespace to POS_WEST;

 

create table POS_WEST.inventory

(prod_id number,

prod_category varchar2(20),

qty_in_stock number,

last_dml timestamp default systimestamp);

 

alter table POS_WEST.inventory add constraint pk_inventory primary key(prod_id);

——————————————————-

create user POS_UK identified by POS_UK default tablespace users temporary tablespace temp;

grant connect,resource  to POS_UK;

grant unlimited tablespace to POS_UK;

 

create table POS_UK.inventory

(prod_id number,

prod_category varchar2(20),

qty_in_stock number,

last_dml timestamp default systimestamp);

 

alter table POS_UK.inventory add constraint pk_inventory primary key(prod_id);

—————————————————————————————-

create user POS_IND identified by POS_IND default tablespace users temporary tablespace temp;

grant connect,resource  to POS_IND;

grant unlimited tablespace to POS_IND;

 

create table POS_IND.inventory

(prod_id number,

prod_category varchar2(20),

qty_in_stock number,

last_dml timestamp default systimestamp);

 

alter table POS_IND.inventory add constraint pk_inventory primary key(prod_id);

—————————————————————————————–

create user POS_AUS identified by POS_IND default tablespace users temporary tablespace temp;

grant connect,resource  to POS_AUS;

grant unlimited tablespace to POS_AUS;

 

create table POS_AUS.inventory

(prod_id number,

prod_category varchar2(20),

qty_in_stock number,

last_dml timestamp default systimestamp);

 

alter table POS_AUS.inventory add constraint pk_inventory primary key(prod_id);

———————————–

create user POS_RUS identified by POS_RUS default tablespace users temporary tablespace temp;

grant connect,resource to POS_RUS;

grant unlimited tablespace to POS_RUS;

 

create table POS_RUS.inventory

(prod_id number,

prod_category varchar2(20),

qty_in_stock number,

last_dml timestamp default systimestamp);

 

alter table POS_RUS.inventory add constraint pk_inventory primary key(prod_id);

—————————————————————————–

 

1 Source System

##################################

To configure the Manager process on the source

 

  1. On the source, configure the Manager process .

GGSCI>edit parma mgr

PORT 7809

USERID gg_owner,PASSWORD gg_owner

PURGEOLDEXTRACTS /u01/app/oracle/product/GG_BASE/GG_112101/dirdata/L1,

USECHECKPOINTS

 

To configure the primary Extract group on the source

##########################################################

  1. On the source, use the ADD EXTRACT command to create an Extract group ext_1.

GGSCI>add extract ext_1 tranlog begin now

==============================================

  1. On the source, use the ADD EXTTRAIL command to create a local trail.

 

ADD EXTTRAIL local_trail_1, EXTRACT ext_1

Use the EXTRACT argument to link this trail to the ext_1 Extract group.

 

GGSCI>add exttrail /u01/app/oracle/product/ogg/dirdat/L1 extract ext_1

EXTTRAIL added.

 

3.On the source, use the EDIT PARAMS command to create a parameter file for the ext_1 Extract group. Include the following parameters plus any others that apply to your database environment.

 

GGSCI> edit params ext_1

EXTRACT ext_1

USERID gg1_owner, PASSWORD gg1_owner

EXTTRAIL /u01/app/oracle/product/ogg/dirdat/L1

TRANLOGOPTIONS EXCLUDEUSER gg1_owner

TABLE POS_WEST.inventory;

 

To configure the data pump on the source

#################################################

  1. On the source, use the ADD EXTRACT command to create a data pump group.

ADD EXTRACT pump_1, EXTTRAILSOURCE local_trail_1, BEGIN time

Use EXTTRAILSOURCE as the data source option, and specify the name of the local trail.

 

GGSCI>add extract pump_1 exttrailsource /u01/app/oracle/product/ogg/dirdat/L1

  1. On the source, use the ADD RMTTRAIL command to specify a remote trail that will be created on the second system in the cascade.

ex:ADD RMTTRAIL remote_trail_1, EXTRACT pump_1

GGSCI>add rmttrail /u01/app/oracle/product/ogg2/dirdat/R1 extract pump_1

 

  1. On the source, use the EDIT PARAMS command to create a parameter file for the pump_1 data pump. Include the following parameters plus any others that apply to your database environment.

 

edit params pump_1

 

EXTRACT pump_1

USERID gg1_owner, PASSWORD gg1_owner

RMTHOST localhost, MGRPORT 7810, TCPBUFSIZE 100000

RMTTRAIL /u01/app/oracle/product/ogg2/dirdat/R1

PASSTHRU

TABLE POS_WEST.inventory;

 

Note:

To use PASSTHRU mode, the names of the source and target objects must be identical. No column mapping, filtering, SQLEXECfunctions, transformation, or other functions that require data manipulation can be specified in the parameter file. You can combine normal processing with pass-through processing by pairing PASSTHRU and NOPASSTHRU with different TABLE statements.

 

GGSCI (GGS-EAST.localdomain) 1> dblogin userid gg1_owner , password gg1_owner

Successfully logged into database.

 

GGSCI (GGS-EAST.localdomain) 2> add trandata POS_WEST.inventory cols (prod_category,qty_in_stock, last_dml)

 

Logging of supplemental redo data enabled for table POS_WEST.INVENTORY.

 

1.2.     Second System in the Cascade

################################################

Configure the Manager process, Replicat group, and data pump on the second system in the cascade.

To configure the Manager process on the second system

  1. On the second system, configure the Manager process

edit params mgr

 

PORT 7810

USERID gg2_owner,PASSWORD gg2_owner

PURGEOLDEXTRACTS /u01/app/oracle/product/GG_BASE/GG_112101/dirdata/L1, USECHECKPOINTS

 

  1. In the Manager Parameter file, use the PURGEOLDEXTRACTS parameter to control the purging of files from the trail.

 

GGSCI>add trandata POS_UK.inventory cols (prod_category,qty_in_stock, last_dml)

 

To configure the Replicat group on the second system

################################################################

  1. Create a Replicat checkpoint table
  2. On the second system, use the ADD REPLICAT command to create a Replicat group. For documentation purposes, this group is called rep_1.

 

Use the EXTTRAIL option to link the rep_1 group to the remote trail remote_trail_1 that is on the local system.

###GGSCI>add replicat rep_1 exttrail /u01/app/oracle/product/ogg2/dirdat/R1 CHECKPOINTTABLE gg2_owner.chkpttable

GGSCI>add replicat rep_1 exttrail /u01/app/oracle/product/ogg2/dirdat/R1

REPLICAT added.

 

  1. On the second system, use the EDIT PARAMS command to create a parameter file for the Replicat group. Include the following parameters plus any others that apply to your database environment.

edit params rep_1

REPLICAT rep_1

ASSUMETARGETDEFS

USERID gg2_owner@UK, PASSWORD gg2_owner

DISCARDFILE /u01/app/oracle/product/ogg2/discard_1.txt, append,

MAP POS_WEST.inventory, TARGET POS_UK.inventory;

 

To configure an Extract group on the second system

###############################################################

  1. use the ADD EXTRACT command to create an Extract group. For documentation purposes, this group is calledext_2
  2. GGSCI> add extract ext_2 tranlog begin now
  1. On the second system, use the ADD EXTTRAIL command to specify a local trail that will be created on the third system.
  2. ex:ADD EXTTRAIL local_trail_2, EXTRACT ext_2

Use 9the EXTRACT argument to link this local trail to the ext_2 Extract group.

GGSCI> add exttrail /u01/app/oracle/product/ogg2/dirdat/L2 extract ext_2

  1. On the second system, use the EDIT PARAMS command to create a parameter file for the ext_2 Extract group.

EXTRACT ext_2

USERID gg2_owner@UK, PASSWORD gg2_owner

EXTTRAIL /u01/app/oracle/product/ogg2/dirdat/L2

TRANLOGOPTIONS EXCLUDEUSER gg2_owner

TABLE POS_UK.inventory;

 

Note:

If replicating DDL operations, IGNOREAPPLOPS, GETREPLICATES functionality is controlled by the DDLOPTIONS parameter.

To configure the data pump on the second system

##################################################################

  1. On the second system, use the ADD EXTRACT command to create a data pump group. For documentation purposes, this group is called pump_2.
  2. ADD EXTRACT pump_2, EXTTRAILSOURCE local_trail_2, BEGIN time

Use EXTTRAILSOURCE as the data source option, and specify the name of the local trail.

 

GGSCI>add extract pump_2 exttrailsource /u01/app/oracle/product/ogg2/dirdat/L2

 

  1. On the second system, use the ADD RMTTRAIL command to specify a remote trail that will be created on the third system in the cascade.
  2. ADD RMTTRAIL remote_trail_2, EXTRACT pump_2

GGSCI>add rmttrail /u01/app/oracle/product/ogg3/dirdat/R2 extract pump_2

Use the EXTRACT argument to link the remote trail to the pump_2 data pump group. The linked data pump writes to this trail.

 

  1. On the second system, use the EDIT PARAMS command to create a parameter file for the pump_2 data pump. Include the following parameters plus any others that apply to your database environment.

edit params pump_2

EXTRACT pump_2

USERID gg2_owner@UK, PASSWORD gg2_owner

RMTHOST localhost, MGRPORT 7811, TCPBUFSIZE 100000

RMTTRAIL /u01/app/oracle/product/ogg3/dirdat/R2

PASSTHRU

TABLE POS_UK.inventory;

add extract pump_3 exttrailsource /u01/app/oracle/product/ogg2/dirdat/L2

add rmttrail /u01/app/oracle/product/ogg4/dirdat/R3 extract pump_3

 

EXTRACT pump_3

USERID gg2_owner@UK, PASSWORD gg2_owner

RMTHOST localhost, MGRPORT 7812, TCPBUFSIZE 100000

RMTTRAIL /u01/app/oracle/product/ogg4/dirdat/R3

PASSTHRU

TABLE POS_UK.inventory;

add extract pump_4 exttrailsource /u01/app/oracle/product/ogg2/dirdat/L2

add rmttrail /u01/app/oracle/product/ogg5/dirdat/R4 extract pump_4

 

EXTRACT pump_4

USERID gg2_owner@UK, PASSWORD gg2_owner

RMTHOST localhost, MGRPORT 7813, TCPBUFSIZE 100000

RMTTRAIL /u01/app/oracle/product/ogg5/dirdat/R4

PASSTHRU

TABLE POS_UK.inventory;

 

Note:

To use PASSTHRU mode, the names of the source and target objects must be identical. No column mapping, filtering, SQLEXECfunctions, transformation, or other functions that require data manipulation can be specified in the parameter file. You can combine normal processing with pass-through processing by pairing PASSTHRU and NOPASSTHRU with different TABLE statements.

 

1.3.     Third System in the Cascade

Configure the Manager process and Replicat group on the third system in the cascade.

To configure the Manager process

###########################################

  1. On the third system, configure the Manager process

PORT 7811

USERID gg3_owner,PASSWORD gg3_owner

 

  1. In the Manager parameter file, use the PURGEOLDEXTRACTS parameter to control the purging of files from the trail.

To configure the Replicat group

###########################################

  1. On the third system, create a Replicat checkpoint table
  2. On the third system, use the ADD REPLICAT command to create a Replicat group rep_2.

Use the EXTTRAIL option to link the rep_2 group to the remote_trail_2 trail.

GGSCI>add replicat rep_2 exttrail /u01/app/oracle/product/ogg3/dirdat/R2

On the third system, use the EDIT PARAMS command to create a parameter file for the Replicat group. Include the following parameters plus any others that apply to your database environment. For possible additional required parameters, see the Oracle GoldenGate installation and setup guide for your database.

Replicat group:

REPLICAT rep_2

ASSUMETARGETDEFS

USERID gg3_owner, PASSWORD gg3_owner

DISCARDFILE /u01/app/oracle/product/ogg3/discard_2.txt, append,

MAP POS_UK.inventory, TARGET POS_IND.inventory;

 

1.4.    Fourth System in the Cascade

SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Configure the Manager Process and Replicat group on the fourth system in the cascade.

To configure the Manager process

###########################################

  1. On the fourth system, configure the Manager process

PORT 7812

USERID gg4_owner@AUS,PASSWORD gg4_owner

 

  1. In the Manager Parameter file, use the PURGEOLDEXTRACTS parameter to control the purging of files from the trail.

To configure the Replicat group

###########################################

  1. On the fourth system, create a Replicat checkpoint table (unless using Oracle integrated Replicat).
  2. On the fourth system, use the ADD REPLICAT command to create a Replicat group. For documentation purposes, this group is called rep_2.

 

Use the EXTTRAIL option to link the rep_2 group to the remote_trail_2 trail.

GGSCI>add replicat rep_3 exttrail /u01/app/oracle/product/ogg4/dirdat/R3

add trandata POS_AUS.inventory cols (prod_category,qty_in_stock, last_dml)

  1. On the fourth system, use the EDIT PARAMS command to create a parameter file for the Replicat group.

 

— Identify the Replicat group:

REPLICAT rep_3

ASSUMETARGETDEFS

USERID gg4_owner@AUS, PASSWORD gg4_owner

DISCARDFILE /u01/app/oracle/product/ogg4/discard_4.txt, append,

MAP POS_UK.inventory, TARGET POS_AUS.inventory;

 

1.5.     Fifth System in the Cascade

##############################################

Configure the Manager Process and Replicat group on the fifth system in the cascade.

To configure the Manager process

###########################################

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

 

  1. On the fifth system, configure the Manager process

PORT 7813

USERID gg5_owner@RUS,PASSWORD gg5_owner

  1. In the Manager parameter file, use the PURGEOLDEXTRACTS parameter to control the purging of files from the trail.

To configure the Replicat group

  1. On the fifth system, create a Replicat checkpoint table (unless using Oracle integrated Replicat).
  2. On the fifth system, use the ADD REPLICAT command to create a Replicat group. For documentation purposes, this group is called rep_2.

 

Use the EXTTRAIL option to link the rep_2 group to the remote_trail_2 trail.

GGSCI>add replicat rep_4 exttrail /u01/app/oracle/product/ogg5/dirdat/R4

add trandata POS_RUS.inventory cols (prod_category,qty_in_stock, last_dml)

On the fifth system, use the EDIT PARAMS command to create a parameter file for the Replicat group.

— Identify the Replicat group:

REPLICAT rep_4

ASSUMETARGETDEFS

USERID gg5_owner@RUS, PASSWORD gg5_owner

DISCARDFILE /u01/app/oracle/product/ogg5/discard_4.txt, append,

MAP POS_UK.inventory, TARGET POS_RUS.inventory;

—————————————————————————————————————————–

insert into epm_WEST.inventory values (104444,’DVD’,10,sysdate);

insert into epm_WEST.inventory values (10100,’DVD’,10,sysdate);

insert into epm_WEST.inventory values (10101,’DVD’,10,sysdate);

insert into POS_WEST.inventory values (2,’DVD’,10,sysdate);

insert into POS_WEST.inventory values (3,’DVD’,10,sysdate);

insert into POS_WEST.inventory values (5,’DVD’,10,sysdate);

insert into POS_WEST.inventory values (9,’DVD’,10,sysdate);

select * from POS_WEST.inventory;

select * from POS_UK.inventory;

select * from POS_IND.inventory;

select * from POS_RUS.inventory;

select * from POS_AUS.inventory;

 

Errors:

Error

GGSCI (GGS-EAST.localdomain) 22> start extract ext_1

Sending START request to MANAGER …

ERROR: opening port for MGR MGR (Connection refused).

————————————————————————————————-

GGSCI (GGS-EAST.localdomain) 4> stop mgr !

Sending STOP request to MANAGER …

ERROR: opening port for MGR MGR (Connection refused).

 

Solution: Verify ip address ,if eth0/eth1 is not up , then make it up

Example:

Ifconfig eth1 192.168.1.2 netmask 255.255.255.0 up

—————————————————————————————————–

 

ERROR

GGSCI (GGS-EAST.localdomain) 15> add trandata POS_WEST.inventory cols (prod_category,qty_in_stock, last_dml)

 

2016-03-13 17:27:47  WARNING OGG-00706  Failed to add supplemental log group on table POS_WEST.INVENTORY

due to ORA-01031: insufficient privileges SQL ALTER TABLE “POS_WEST”.”INVENTORY” ADD SUPPLEMENTAL LOG GROUP

“GGS_74619” (“PROD_ID”,”PROD_CATEGORY”,”QTY_IN_STOCK”,”LAST_DML”) ALWAYS  /* GOLDENGATE_DDL_REPLICATION */.

 

Solution: Add privilege

Grant alter any table to <GG_OWNER>;

——————————————————————————————————–

 

GGSCI (ol6-GG-sys1.localdomain) 6> add trandata POS_IND.inventory cols (prod_category,qty_in_stock, last_dml)

 

2016-03-14 16:51:00  WARNING OGG-00706  Failed to add supplemental log group on table POS_IND.INVENTORY due to ORA-01031: insufficient privileges SQL ALTER TABLE “POS_IND”.”INVENTORY” ADD SUPPLEMENTAL LOG GROUP “GGS_74574” (“PROD_ID”,”PROD_CATEGORY”,”QTY_IN_STOCK”,”LAST_DML”) ALWAYS  /* GOLDENGATE_DDL_REPLICATION */.

 

See Also: