Oracle DBA

1. What is Conflict Detection and Resolution (CDR) in bidirectional replication?

Conflict Detection and Resolution (CDR) is a mechanism in Oracle GoldenGate to handle data conflicts that arise in bidirectional (active-active) replication setups, where the same table is being updated at multiple sites simultaneously.

🧠 Why is CDR needed?

In active-active replication, changes can occur on the same rows at both sites, leading to conflicting updates, inserts, or deletes. Without resolution, data divergence and corruption can occur.

⚙️ How CDR Works

GoldenGate supports two strategies:

• Conflict Detection: Detects when operations violate uniqueness or integrity constraints (e.g., duplicate PK).
• Conflict Resolution: Resolves these using predefined rules or custom user exit routines.

🔍 CDR Techniques

• Timestamp-based: Choose the latest change using last updated timestamps.
• Priority-based: Assign priority to one site over another.
• Custom logic: Use SQLEXEC or User Exits for application-specific logic.

📘 Configuration Example (Replicat param):

MAP hr.employees, TARGET hr.employees,
RESOLVECONFLICT (UPDATEROWEXISTS, (USEMAX (last_updated_date)));

✨ Best Practice: Always use conflict resolution when implementing active-active replication to ensure data integrity and avoid update loops.

---

2. How do you configure GoldenGate for Oracle CDB/PDB architecture?

GoldenGate fully supports Oracle Multitenant (CDB/PDB) beginning with Oracle 12c and higher.

🔧 Steps to Configure GoldenGate for CDB/PDB:

1. Create GoldenGate user in the PDB:
   • Must have required privileges within the PDB, not at CDB level.
2. Enable GoldenGate Replication in PDB:
   ALTER DATABASE ENABLE GOLDENGATE REPLICATION;
3. Use the correct TNS alias pointing to the PDB, not the CDB.
4. Register Extract/Replicat in the PDB context:
   dblogin useridalias ogg_user@pdb1
register extract ext1 database
5. Use Integrated Extract and Replicat, as they are more compatible with CDB/PDB model.
6. GoldenGate Home should be installed on the CDB host, but configured per PDB session.

---

3. Can you replicate sequences, LOBs, or BLOBs using GoldenGate? How?

Yes, Oracle GoldenGate can replicate LOBs, BLOBs, and sequences with specific configuration.

🔹 LOB/BLOB Replication:

LOBs require special parameters to ensure consistent replication.

• Use the following parameters in Extract and Replicat:
  FETCHLOBSONLY
LOBCHUNKSIZE 4096
• Considerations:
  • LOBs can be inline or out-of-line.
  • Integrated Extract handles LOBs more efficiently than Classic.

🔹 Sequence Replication:

Sequences are not transactional, so GoldenGate does not replicate sequences automatically.

• Solution Options:
  • Use manual synchronization via DDL scripts.
  • Switch to application-managed sequences (e.g., stored in a table).
  • Use GoldenGate DDL replication with custom scripts.

---

4. How do you secure GoldenGate trail files and communication?

GoldenGate provides multiple layers of data protection for both trail files and network communication.

🔐 Trail File Security

• File System Permissions: Secure trail directories with restricted OS-level access.
• Trail File Encryption:
  • Use ENCRYPTTRAIL parameter to encrypt trail files using AES.
  
  ENCRYPTTRAIL AES256

🌐 Network Communication Security

• TCP/IP Encryption:
  • Enable TLS/SSL between Extract, Data Pump, and Replicat.Requires certificates and parameter setup in GLOBALS and process config.
  
  ENCRYPT PASSWORD oracle ENCRYPTKEY default
• Credential Store:
  • Use CredentialStore instead of plain-text passwords.
  
  ADD CREDENTIALSTORE
ALTER CREDENTIALSTORE ADD USER ogg_user@db DOMAIN OracleCredentialStore PASSWORD oracle
• Oracle Wallet Integration: For advanced security compliance (especially FIPS).

🛡️ Security Best Practice: Always encrypt both trail files and network communication in regulated environments.

---

5. Explain how Oracle GoldenGate interacts with Oracle Data Guard or RAC environments.

🟢 With Oracle RAC (Real Application Clusters)

• GoldenGate is fully RAC-aware.
• Use a shared GoldenGate Home (or local on each node).
• Ensure trail files and checkpoints are placed on shared storage (ACFS or NFS).
• Configure THREADS parameter in Extract to capture redo from multiple threads:
  TRANLOGOPTIONS THREADS 2
• Register Extract with all redo threads (one per instance).
• Use SCAN listeners for TNS to maintain availability during failover.

🟢 With Oracle Data Guard

• Extract must run on the primary database because redo apply on standby does not support log mining.
• GoldenGate on Standby:
  • Possible only if Standby is open in read-only mode with real-time apply.
  • Still not recommended — primary is preferred for capture.
• For high availability:
  • Use Broker/Fast-Start Failover (FSFO) and script GoldenGate to resume replication after switchover/failover.

🔄 Best Practice for HA Setup:

• Use Data Guard for DR and GoldenGate for replication/migration.
• Combine both for zero-downtime upgrades or cross-platform migrations.