• Arun Kumar

Oracle Golden Gate Schema Replication

In this article we will setup schema replication using golden gate. Schema replication means we will be replicating all the tables of a schema.


Scenario 1


Scenario: There is a new blank schema which is being created on source database. Client wants to setup replication for same schema on target server. Below are the client requirements:

  • All DML replications from source to target

  • All DDL replications from source to target

  • The source schema is E6P and target schema is also E6P

Let us analyze the scenario: As per the client requirement, it is a new schema which is being created on source. Hence, no initial load involved here. We will be directly setting up the change sync between source and target database.


Setup Schema Replication


Create E6P schema on both source and target server with below permissions

On proddb:
==========
Create user e6p identified by e6p;
Grant connect, resource to e6p;
Alter user e6p quota unlimited on users;

On devdb:
=========
Create user e6p identified by e6p;
Grant connect, resource to e6p;
Alter user e6p quota unlimited on users;

Till now we have always added table level supplemental logging. Now it’s time to add schema level supplemental logging

On proddb:
==========
GGSCI (ggprod) 2> add schematrandata e6p

INFO OGG-01788  SCHEMATRANDATA has been added on schema e6p.
INFO OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema e6p.

Add extract on source with below details

On proddb:
==========
GGSCI> ADD EXTRACT e6pex1, INTEGRATED TRANLOG, BEGIN NOW
EXTRACT (Integrated) added.

GGSCI> register extract e6pex1 database	 only needed in integrated capture mode

GGSCI> add exttrail /u01/app/oracle/product/gg/dirdat/e6, extract e6pex1

GGSCI> edit param e6pex1

EXTRACT e6pex1
USERID ogg, PASSWORD ogg
EXTTRAIL /u01/app/oracle/product/gg/dirdat/e6
DDL INCLUDE ALL
TABLE E6P.*;

Add data pump on source

On proddb:
==========
GGSCI> Add extract e6pdp1, EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/e6

GGSCI> Add rmttrail /u01/app/oracle/product/gg/dirdat/r6, extract e6pdp1

GGSCI> edit parame6pdp1

EXTRACT e6pdp1
USERID ogg, PASSWORD ogg
RMTHOST ggdev, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/r6
TABLE E6P.*;

Add replicat on target

On GGDEV:
=========
GGSCI>dbloginuseridogg, password ogg

GGSCI> add replicat e6drep1, integrated exttrail /u01/app/oracle/product/gg/dirdat/r6

GGSCI> edit param e6drep1

REPLICAT e6drep1
USERID ogg, PASSWORD ogg
ASSUMETARGETDEFS
MAP E6P.* TARGET E6P.*;

Start Extract, Pump and replicat

GGSCI> start e6pex1
GGSCI> start e6pdp1

GGSCI> start e6drep1


Test Schema Replication


Let us create table on source

CREATE TABLE profiles
(
pname VARCHAR2(32) PRIMARY KEY,
pemail VARCHAR2(64),
paddr VARCHAR2(128)
);

Check if table is replicated to E6P on target.


Let us insert some rows in table on source

INSERT INTO profilesVALUES ('Scott','scott@gmail.com','USA');
INSERT INTO profilesVALUES ('James','james@gmail.com','UK');

commit;

Check on target if the new rows are inserted or not.


Let us create an index on source table

SQL> CREATE INDEX pemail_idx ON profiles(pemail);

Check on target if the new index is created or not.


Let us create a cluster on source database

SQL> CREATE CLUSTER dept_no(deptno NUMBER);

Cluster created.

Connect to the target database and query the data dictionary view ‘DBA_CLUSTERS’


Let us create a sample procedure on source database

SQL> CREATE OR REPLACE PROCEDURE getemail(Ppname IN VARCHAR2,
Ppemail OUT VARCHAR2) IS
vpemail VARCHAR2(64);
BEGIN
SELECT pemail into vpemail
FROM profiles
WHERE pname =ppname;
END;
/

Procedure created.

Connect to the target database to verify it has been created successfully.


Let us grant permissions on PROFILES table to FOX user

SQL> GRANT SELECT ON PROFILES TO FOX;

Connect to target and verify if the permissions are granted or not.


IMPORTANT NOTES ON SCHEMA LEVEL DDL REPLICATION:

  • Make sure the permissions that source schema has, the same are given to target schema as well otherwise most of the DDL will fail.

  • In our example, both source and target schema names are same. If you have different source and target schema names, add below line in replicat to allow DDL mapping from source to target

DDLOPTIONS MAPSESSIONSCHEMA source_schema TARGET target_schema

DDLOPTIONS MAPSESSIONSCHEMA FOX TARGET TOM


Scenario 2


Scenario: There is an existing schema on source database. Client wants to setup replication for same schema on target server. Below are the client requirements:

  • All DML replications from source to target

  • All DDL replications from source to target

  • The source schema is HR and target schema is HRD

Let us analyze the scenario: As per the client requirement, it is an existing schema on source. First, we must perform an initial load and then continue with change sync. Below would be further approaches to resolve this:


Scenario 1: source schema of any size and you can stop changes to data on source:

  • Stop changes on source schema

  • Perform schema export / import using expdp/impdp

  • Configure golden gate change sync

  • Start changes on source schema

Scenario 2: source schema of small size < 100 GB and you cannot stop changes to data on source

  • Configure golden gate change sync but do not start replicat

  • Perform schema export / import using expdp/impdp (we are using database utility instead of GG Initial load)

  • Start replicat with HANDLECOLLISIONS parameter

Scenario 3: source schema of big size > 100 GB and you cannot stop changes to data on source:

  • Create target schema with source metadata only (DATAPUMP impdpover DBLINK content=METADATA_ONLY)

  • Disable constraints, triggers (if any) on target tables

  • Drop indexes on target tables

  • Configure Golden Gate change sync but do not start replicat

  • Configure Golden Gate initial load and start schema replication

  • Rebuild indexes on target tables

  • Enable constraints on target tables

  • Start replicat with HANDLECOLLISIONS parameter


Assignment


Perform schema replication using all the three scenarios mentioned above. You can create new schemas or try to replicate existing schemas.


Realtime Assignment


There is a new blank schema which is being created on source database. Client wants to setup replication for same schema on target server. Below are the client requirements:

  • All DML replications from source to target

  • All DDL replications from source to target

  • The source schema is IQP and target schema is IQD

  • All the target table names must prefix “P_”. For example, If source table is EMP, on target it must be P_EMP

  • No changes in the names of all other objects from source to target.

Recent Posts

See All

Install Oracle 12c Golden Gate on Oracle Linux

In this article we will look at the steps to install oracle 12c Golden Gate on Oracle Enterprise Linux 6.5. The steps involved are: Virtual Machine Setup Install Oracle 12c Database Install Oracle 12c

Single Table Replication – Same Schema

In this article we will be looking at single table replication using Oracle Golden Gate where the schema name is same on both source and target database. Create Sample Table Create Fox user on both pr

Single Table Replication – Different Schema

In this article we will be looking at single table replication using Oracle Golden Gate where the schema name is different on source and target but table structure remains same. Create Sample Table Cr

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis