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
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.