Adding schemas to replication
Author: Bobby Curtis, MBA
Date: September 25, 2023
Updated: January 23, 2025
Organization: RheoData
Table of Contents
ADDING A SCHEMA/TABLE BETWEEN DATABASES
ADDING SCHEMA/TABLE TO REPLICATION
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.
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;
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.
Congratulations! You have successfully added a schema/table to your replication process.