Oracle DBA

1. How do you configure a unidirectional replication using GoldenGate?

Unidirectional replication moves data from a source database to a target database in one direction only. Here’s the step-by-step configuration process:

✅ Source Side Configuration:

1. Enable Supplemental Logging:
   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE FORCE LOGGING;
2. Create GoldenGate user and grant necessary privileges.
3. Configure Manager Process:
   GGSCI> EDIT PARAMS MGR
PORT 7809
4. Create Extract Process:
   GGSCI> ADD EXTRACT ext1, INTEGRATED TRANLOG, BEGIN NOW
GGSCI> ADD EXTTRAIL ./dirdat/lt, EXTRACT ext1
5. Edit Extract Parameter File:
   GGSCI> EDIT PARAMS ext1
EXTRACT ext1 USERID ggate_user, PASSWORD oracle
EXTTRAIL ./dirdat/lt
TABLE hr.*;
6. (Optional) Add Data Pump Process:
   GGSCI> ADD EXTRACT dpump1, EXTTRAILSOURCE ./dirdat/lt
GGSCI> ADD RMTTRAIL ./dirdat/rt, EXTRACT dpump1, TARGETHOST target, MGRPORT 7809

✅ Target Side Configuration:

1. Configure Manager Process:
   GGSCI> EDIT PARAMS MGR
PORT 7809
2. Create Replicat Process:
   GGSCI> ADD REPLICAT rep1, INTEGRATED, EXTTRAIL ./dirdat/rt
3. Edit Replicat Parameter File:
   GGSCI> EDIT PARAMS rep1
REPLICAT rep1
USERID ggate_user, PASSWORD oracle
MAP hr.*, TARGET hr.*;
4. Start All Processes:
   GGSCI> START EXTRACT ext1
GGSCI> START EXTRACT dpump1
GGSCI> START REPLICAT rep1

2. What are the steps involved in setting up Integrated Extract?

Integrated Extract is tightly coupled with Oracle’s LogMiner infrastructure. It offers better performance and supports complex data types and CDB/PDB.

🔹 Steps:

1. Enable Database Logging and Supplemental Logs:
   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
EXEC DBMS_CAPTURE_ADM.ENABLE_AUTO_SUPPLEMENTAL_LOGGING;
2. Prepare Database for Integrated Extract:
   EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('GGATE_USER');
3. Register Extract with Database: bashCopyEditGGSCI> REGISTER EXTRACT ext1 DATABASE
4. Create Integrated Extract:
   GGSCI> ADD EXTRACT ext1, INTEGRATED TRANLOG, BEGIN NOW
GGSCI> ADD EXTTRAIL ./dirdat/lt, EXTRACT ext1
5. Configure Parameter File:
   EXTRACT ext1
USERID ggate_user, PASSWORD oracle
EXTTRAIL ./dirdat/lt
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 500, parallelism 4)
TABLE hr.*;
6. Start Extract Process:
   GGSCI> START EXTRACT ext1

3. How do you add and register Extract in Integrated Mode?

This is essential to use the Integrated Capture mechanism.

🔹 Steps to Add and Register:

1. Add Integrated Extract Process:
   GGSCI> ADD EXTRACT ext1, INTEGRATED TRANLOG, BEGIN NOW
2. Add Trail:
   GGSCI> ADD EXTTRAIL ./dirdat/lt, EXTRACT ext1
3. Register Extract with Database LogMiner:
   GGSCI> REGISTER EXTRACT ext1 DATABASE
4. Verify Registration:
   GGSCI> INFO EXTRACT ext1, DETAIL
5. Create Parameter File:
   GGSCI> EDIT PARAMS ext1
EXTRACT ext1
USERID ggate_user, PASSWORD oracle
EXTTRAIL ./dirdat/lt TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 300, parallelism 4)
TABLE hr.*;

4. How do you ensure data consistency during initial load and replication start?

Maintaining data consistency during replication setup is critical to prevent data divergence.

🔹 Approach:

1. Use Flashback SCN or Timestamp:
   • Capture a consistent point-in-time SCN or timestamp.
     SELECT CURRENT_SCN FROM V$DATABASE;
2. Export Data at SCN:
   • Use Data Pump or GoldenGate INITIALLOAD utility to export data using that SCN.
     expdp ... flashback_scn=123456789
3. Start Extract at the same SCN:
   GGSCI> ADD EXTRACT ext1, INTEGRATED TRANLOG, BEGIN SCN 123456789
4. Delay Replicat Until Load Completes:
   • Use HANDLECOLLISIONS or hold Replicat until bulk data is loaded.
5. Start Replicat after Load:
   • Once data is loaded to target, Replicat applies trail files from same SCN onward.