Oracle GoldenGate - Adding schemas to replication

Oracle GoldenGate - Adding schemas to replication

Adding schemas to replication

 

Author: Bobby Curtis, MBA

 

Date: September 25, 2023

 

Updated: January 23, 2025

 

Organization: RheoData

Table of Contents

INTRODUCTION:

ADDING A SCHEMA/TABLE BETWEEN DATABASES

ADDING SCHEMA/TABLE TO REPLICATION

CONCLUSION:

 


 

Introduction:

After Oracle GoldenGate is configured and data is flowing, there are times where additional schemas need to be added to the replication stream.  In order to do this, an administrator needs to remember that replication has to stay moving while adding these schemas.  The process seems to be simple but may still cause issues if not done correctly.  In this document, we will take a look at how to add a schema to existing replication.

 

The easiest way of thinking of adding a schema to replication is to define the replication pipeline as an equation – one-side has to equal the other side in one direction:

 

A = A

 

Based on this, we can quickly add a schema and enabled bi-direction replication for that schema.

Adding a Schema/Table between databases

 

1.     Identifying a schema is as simple as just saying, this schema needs to be replicated.  At the same time, we need to know what tables are in the schema to be replicated.  The following query will provide a list of tables within a schema:

 

SQL> select object_name from dba_objects where owner in (‘<schema>’);

2.     With the schema identified, we then need to pull the metadata for the schema. 

 

$ export ORACLE_HOME=$ORACLE_HOME

$ export ORACLE_SID=$ORACLE_SID

$ $ORACLE_HOME/bin/expdp oggadmin/<pwd>@$ORACLE_SID parfile=expdp.par

Parfile:

DIRECTORY=DATAPUMP_DIR

DUMPFILE=”GG_METADATA%U.dmp”

LOGFILE=”GG_METADATA.log”

JOB_NAME=’EXPDB_GG’

CONTENT=METADATA_ONLY

PARALLEL=6

FILESIZE=5g

FLASHBACK_SCN=<min_scn>

 

3.     After pulling the metadata, the associated dump file needs to be moved to the target system

 

$ scp ./GG_METADATA*.dmp oracle@<host>:/tmp

Note: You can pull the metadata with the import datapump (impdp) and a database link to the source system to minimize the file storage.

 

4.     Once the dump file with the metadata is on the target system, it will need to be imported to the target database.  This will setup the schema with all the metadata needed for the schema.

 

$ export ORACLE_HOME=$ORACLE_HOME

$ export ORACLE_SID=$ORACLE_SID

$ $ORACLE_HOME/bin/impdp oggadmin/<pwd>@$ORACLE_SID parfile=expdp.par

Parfile:

DIRECTORY=DATAPUMP_DIR

DUMPFILE=”GG_METADATA%U.dmp”

LOGFILE=”GG_METADATA.log”

LOGTIME=ALL

JOB_NAME=’IMPDB_GG’

STATUS=120

STREAMS_CONFIGURATION=N

TABLE_EXISTS_ACTION=REPLACE

SKIP_UNUSABLE_INDEXES=Y (optional)

 

5.     Adjust sequences as needed.

 

·      Bi-Directional Setup should have an Odd/Even sequence pattern.

·      Multi-Master Setup should have a site-number pattern.

o   example: Dublin = Site 1, Raleigh = Site 2, Shanghi = Site 3. 

§  Then each sequence should be incremented by site number.

 


 

6.     With the target metadata imported, all the referential integrity within the schema needs to be disabled.  This is done in order to speed up the data loading process.

 

Note: The below script will print out an ALTER TABLE statement for each referential integrity associated with the schema.  These statements will need to be ran from a SQL prompt of SQL Developer.

 

set linesize 180

set pagesize 50000

select 'ALTER TABLE '|| owner ||'.'|| table_name ||' [DISABLE | ENABLE] CONSTRAINT '|| constraint_name ||';'

from all_constraints

where owner in (

select owner

from dba_tables

where owner in (‘<schema>’)

and constraint_type in ('R')

and status = 'DISABLED'

order by 1;

Adding Schema/Table to replication

With the metadata moved to the target system, this schema/table can be added to replication.  To do this, we need to take an initial load approach before merging it into the normal change data capture (CDC) processes.  This section will show the steps to complete this task.

1.     Stop CDC extract

adminclient> stop extract <extract>

Example:

Adminclient> stop extract EXTEU

2.     Edit CDC extract and add schema/table

adminclient> edit params <extract>

TABLE <schema>.*;

Example:

Adminclient> edit params EXTEU

TABLE <schema>.*;


 

3.     Capture System Change Number (SCN) (optional)

4.     Create an initial load extract using the table as the source

adminclient> add extract <extract>, sourceistable

adminclient> add extfile IL, extract <extract>, megabytes 1024

Example:

Adminclient> add extract AM2EUIL, sourceistable

Adminclient> add extfile IL, extract AM2EUIL, megabytes 1024

Adminclient> start extract AM2EUIL;

5.     Create a dedicated distribution path for extfile file

*Add distro path through GUI

6.     Create an initial load replicat that will read extfile file

adminclient> add replicat <replicat>, extfile IL, nodbcheckpoint

Example:

Adminclient> add replicat AM2EMIL, extfile IL, nodbcheckpoint

7.     Start initial load replicat

adminclient > start replicat <replicat>

Example:

Adminclient> start replicat AM2EMIL

8.     Check for end-of-file on initial load replicat

Adminclient > send replicat <replicat>, logend

Example:

Adminclient> send replicat AM2EUIL, logend

9.     Stop Initial Load extract and replicat

adminclient> stop extract <extract>

adminclient> stop replicat <replicat>

Example:

Adminclient> stop extract AM2EU (in AM)

Adminclient> stop replicat AM2EU (in EU)


 

10.  Stop CDC replicat

adminclient> stop replicat <replicat>

Example:

Adminclient> stop replicat REPAM

11.  Edit CDC replicat and add schema/table

adminclient> edit params <replicat>

MAP <schema>.*, TARGET <schema>.*;

12.  Start CDC replicat

adminclient> start replicat <replicat>

Example:

Adminclient> start replicat REPAM

13.  Check statistics on CDC replicat

adminclient> stats replicat <replicat>

Example:

Adminclient> stats replicat REPAM

14.  Remove the initial load extract and replicat

adminclient> delete extract <extract>

adminclient> delete replicat <replicat>

Example:

adminclient> delete extract AM2EU (in AM)

adminclient> delete replicat AM2EU (in EU)

 

At this point, the new table should be replicating data. The statistics command should show you the number of inserts, updates and deletes that are being processed.

Conclusion:

Congratulations! You have successfully added a schema/table to your replication process.

    • Related Articles

    • Oracle GoldenGate - Adding schemas/tables to replication - simplified

      Adding schemas/tables to replication - simplified Author: Bobby Curtis, MBA Date: September 27, 2023 Updated: January 23, 2025 Organization: RheoData Table of Contents INTRODUCTION: ADDING SCHEMA/TABLE TO REPLICATION (SIMPLIFIED) CONCLUSION: ...
    • Oracle GoldenGate - Adding schemas/tables to replication - DDL

      Adding schemas/tables to replication - DDL Author: Bobby Curtis, MBA Date: October 1, 2023 Updated: January 23, 2025 Organization: RheoData Table of Contents INTRODUCTION: ADDING SCHEMA/TABLE TO REPLICATION (DDL) CONCLUSION: REFERENCE: Introduction: ...
    • Oracle GoldenGate - Exception Handling

      Oracle GoldenGate Exception Handling Author: Bobby Curtis, MBA Date: October 3, 2023 Updated: January 23, 2025 Organization: RheoData Table of Contents INTRODUCTION: EXCEPTION TABLE(S) INFO Exception Table Exception Macro Update Replicat CONCLUSION: ...
    • Oracle GoldenGate - Installing Oracle GoldenGate binaries

      Installing Oracle GoldenGate binaries Author: Bobby Curtis, MBA Date: August 4, 2023 Organization: RheoData Table of Contents INTRODUCTION: Prerequisites: STEP 1: DOWNLOAD ORACLE GOLDENGATE 21.10 STEP 2: TRANSFER INSTALLATION FILES STEP 3: EXTRACT ...
    • Oracle GoldenGate - Backup Oracle GoldenGate Deployments

      Backup Oracle GoldenGate Deployments Author: Bobby Curtis, MBA Date: January 23, 2025 Updated: October 13, 2023 Organization: RheoData Table of Contents INTRODUCTION: Assumption: STEP 1: ACCESS THE ORACLE GOLDENGATE SERVER STEP 2: IDENTIFY THE ...