Oracle Golden Gate Schema Replication

Replicate entire schemas efficiently with GoldenGate.

Oracle Golden Gate Schema Replication means we will be replicating all the tables of a schema. Here we will be looking at two scenarios: replication for new schema and replication for an existing schema

Golden Gate New Schema Replication
Golden Gate Existing Schema Replication

Golden Gate New Schema Replication

Imagine 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

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 and check if table is replicated to E6P on target

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

Let us insert some rows on source and you should see same row replicated on target

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

commit;

Let us create an index on source and you should see same replicated on target

SQL> CREATE INDEX pemail_idx ON profiles(pemail);

Let us create a cluster on source and you should see same replicated on target

SQL> CREATE CLUSTER dept_no(deptno NUMBER);

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;

Golden Gate Existing Schema Replication

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.