This example shows how to setup a bi-directional replication between Oracle Database.
The setup is as follows:
WEST Database Setup
The database is ogg11r2. WEST and OGGUSER account are created as follows:
- There are two database EAST and WEST.
- There are extract, pump and replicat setup on EAST and WEST DB side:
WEST: exwest, dpwest, rewest
EAST: exeast, dpeast, reeast
- WEAST-> eswest -> dpwest -> reeast (ogguser) -> EAST -> exeast -> dpeast -> rewest (ogguser)-> WEST
WEST Database Setup
The database is ogg11r2. WEST and OGGUSER account are created as follows:
Then login to WEST DB to create tables:
connect west/Welcome1@ogg11r2 SQL> connect west/Welcome1@ogg11r2 Connected. SQL> set echo on SQL> @objects.sql
SQL> connect system/oracle@ogg11r2 Connected. create user west identified by Welcome1 default tablespace 2 oggdata temporary tablespace temp; User created. SQL> grant connect, resource to west; Grant succeeded. create user ogguser identified by Welcome1 default 2 tablespace oggdata temporary tablespace temp; User created. SQL> grant dba to ogguser; Grant succeeded.
The objects.sql is shown as follows:
------------------------------------------------------------------------ -- DISCLAIMER: -- This script is provided for educational purposes only. It is NOT -- supported by Oracle World Wide Technical Support. -- The script has been tested and appears to work as intended. -- You should always run new scripts on a test instance initially. -- ------------------------------------------------------------------------ CREATE TABLE ACCOUNT ( ACCT_ID NUMBER(12) NOT NULL, CUST_ID NUMBER(12) NOT NULL, ACCOUNT_TYPE VARCHAR2(12 BYTE) NOT NULL, ACCOUNT_NUMBER VARCHAR2(12 BYTE) NOT NULL, ACCOUNT_BALANCE NUMBER(12,2) NOT NULL, ACCOUNT_OPENED DATE NOT NULL, ACCOUNT_BRANCH_ID VARCHAR2(20 BYTE) NOT NULL, UPDATE_TS TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL ) TABLESPACE OGGDATA PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 256K NEXT 256K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; CREATE TABLE BRANCH ( BRANCH_ID VARCHAR2(20 BYTE) NOT NULL, BRANCH_STREET_TYPE VARCHAR2(20 BYTE) NOT NULL, BRANCH_STREET_NAME VARCHAR2(255 BYTE) NOT NULL, BRANCH_STREET_NUMBER VARCHAR2(100 BYTE) NOT NULL, BRANCH_LEVEL INTEGER, BRANCH_CITY VARCHAR2(100 BYTE) NOT NULL, BRANCH_STATE VARCHAR2(100 BYTE) NOT NULL, BRANCH_COUNTRY VARCHAR2(100 BYTE) NOT NULL, BRANCH_SIZE VARCHAR2(10 BYTE) DEFAULT 'SMALL' NOT NULL, BRANCH_OPENED DATE NOT NULL, ZIP_CODE VARCHAR2(10 BYTE) NOT NULL, BRANCH_NAME VARCHAR2(60 BYTE) NOT NULL, UPDATE_TS TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL ) TABLESPACE OGGDATA PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 256K NEXT 256K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; CREATE TABLE CUSTOMER ( CUST_ID NUMBER(12) NOT NULL, NAME VARCHAR2(50 BYTE) NOT NULL, MIDDLE_NAME VARCHAR2(50 BYTE), SURNAME VARCHAR2(50 BYTE) NOT NULL, FULL_NAME VARCHAR2(255 BYTE) NOT NULL, DOB DATE NOT NULL, NATIONALITY VARCHAR2(50 BYTE) NOT NULL, EMAIL VARCHAR2(255 BYTE), UPDATE_TS TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL ) TABLESPACE OGGDATA PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 256K NEXT 256K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; CREATE UNIQUE INDEX ACCOUNT_PK ON ACCOUNT (CUST_ID, ACCT_ID) LOGGING TABLESPACE OGGDATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 256K NEXT 256K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; CREATE UNIQUE INDEX BRANCH_PK ON BRANCH (BRANCH_ID) LOGGING TABLESPACE OGGDATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 256K NEXT 256K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; CREATE UNIQUE INDEX CUST_PK ON CUSTOMER (CUST_ID) LOGGING TABLESPACE OGGDATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 256K NEXT 256K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; ALTER TABLE ACCOUNT ADD ( CONSTRAINT ACCOUNT_PK PRIMARY KEY (CUST_ID, ACCT_ID) USING INDEX ACCOUNT_PK); ALTER TABLE BRANCH ADD ( CONSTRAINT BRANCH_PK PRIMARY KEY (BRANCH_ID) USING INDEX BRANCH_PK); ALTER TABLE CUSTOMER ADD ( CONSTRAINT CUST_PK PRIMARY KEY (CUST_ID) USING INDEX CUST_PK);
EAST DB Setup
The database is dwnstr. WEST and OGGUSER account are created and then create the tables in EAST account. The steps are shown as follows:
The database is dwnstr. WEST and OGGUSER account are created and then create the tables in EAST account. The steps are shown as follows:
GoldenGate Setup on the WEST DB Side
SQL> connect system/oracle@dwnstr Connected. create user east identified by Welcome1 default tablespace 2 oggdata temporary tablespace temp; User created. SQL> grant connect, resource to east; Grant succeeded. create user ogguser identified by Welcome1 default 2 tablespace oggdata temporary tablespace temp; User created. SQL> grant dba to ogguser; Grant succeeded. SQL> connect east/Welcome1@dwnstr Connected. SQL> set echo on SQL> @objects.sql
The parameter files are defined as follows:
Extract exwest SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1") SETENV(ORACLE_SID = "ogg11r2") ExtTrail ./dirdat/ew UserID ogguser@ogg11r2, Password Welcome1 StatOptions ResetReportStats ReportCount Every 10 Records TranLogOptions ExcludeUser ogguser Table WEST.*;
Extract dpwest RmtHost ogg_target, MgrPort 7909, Compress RmtTrail ./dirdat/pe Passthru Table WEST.*;
Replicat rewest SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1") SETENV(ORACLE_SID = "ogg11r2") AssumeTargetDefs DiscardFile ./dirrpt/rewest.dsc, Purge UserID ogguser@ogg11r2, Password Welcome1 Map EAST.*, Target WEST.*;
Create the extract, pump and replicat groups:
GGSCI> dblogin userid ogguser@ogg11r2, password Welcome1 GGSCI> add checkpointtable ggs_chkpt GGSCI> add trandata west.* GGSCI> Add Extract exwest, tranLog, begin now EXTRACT added. GGSCI> Add ExtTrail ./dirdat/ew, Extract exwest, megabytes 10 EXTTRAIL added. GGSCI> Add Extract dpwest, ExtTrailSource ./dirdat/ew EXTRACT added. GGSCI> Add RmtTrail ./dirdat/pe, Extract dpwest,megabytes 10 RMTTRAIL added. GGSCI> Add Replicat rewest, ExtTrail ./dirdat/pw REPLICAT added.
Oracle GoldenGate Setup on EAST Database Side.
The parameter files for extract, pump and replicat are defined as follows:
The parameter files for extract, pump and replicat are defined as follows:
Extract exeast SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1") SETENV(ORACLE_SID = "dwnstr") ExtTrail ./dirdat/ee UserID ogguser@dwnstr, Password Welcome1 StatOptions ResetReportStats ReportCount Every 10 Records TranLogOptions ExcludeUser ogguser Table EAST.*;
Extract dpeast SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1") SETENV(ORACLE_SID = "dwnstr") RmtHost ogg_source, MgrPort 7809, Compress RmtTrail ./dirdat/pw Passthru Table east.*;
Replicat reeast AssumeTargetDefs DiscardFile ./dirrpt/reeast.dsc, Purge UserID ogguser@dwnstr, Password Welcome1 Map west.*, Target east.*;
Create the extract, pump and replicat group.
GGSCI> dblogin userid ogguser@dwnstr, password Welcome1 GGSCI> add checkpointtable ggs_chkpt GGSCI> add trandata east.* GGSCI> Add Extract exeast, TranLog, Begin Now EXTRACT added. GGSCI> Add ExtTrail ./dirdat/ee, Extract exeast, Megabytes 10 EXTTRAIL added. GGSCI> Add Extract dpeast, ExtTrailSource ./dirdat/ee EXTRACT added. GGSCI> Add RmtTrail ./dirdat/pw, Extract dpeast, megabytes 10 RMTTRAIL added. GGSCI> Add Replicat reeast, ExtTrail ./dirdat/pe REPLICAT added.
Start all the groups and then let's perform some replication with account_west.sql (180), branch_east.sql (5) , customer_east.sql (100), account_east.sql (180) , branch_east.sql (5) , customer_west.sql (100) . OGG will sync-up the two DB no matter the SQL transaction run on WEST or ESAT DB.
SQL> connect west/Welcome1@ogg11r2 SQL> @account_west.sql SQL> select count(*) from account; COUNT(*) ---------- 180 sqlplus east/Welcome1@dwnstr SQL> select count(*) from account; COUNT(*) ---------- 180 SQL> @branch_east.sql .. Commit complete. SQL> connect west/Welcome1@ogg11r2 Connected. SQL> select count(*) from branch; COUNT(*) ---------- 5
DDL Replication