OCI GoldenGate Microservices Architecture is the cloud-native version of Oracle GoldenGate. It captures database changes from transaction logs in real time, transforms them, and delivers them to target databases or messaging systems. This post covers deploying a GoldenGate deployment with Terraform, configuring Oracle and Kafka connections, and creating a CDC pipeline for order data.

Step 1: IAM Policy and Deployment

resource "oci_identity_policy" "goldengate_policy" {
  compartment_id = var.compartment_id
  name           = "goldengate-policy"
  statements = [
    "Allow group DBAdmins to manage goldengate-family in compartment id COMPARTMENT_OCID",
    "Allow group DBAdmins to read secret-bundle in compartment id COMPARTMENT_OCID",
    "Allow service goldengate to read secret-bundle in compartment id COMPARTMENT_OCID"
  ]
}

resource "oci_golden_gate_deployment" "production_gg" {
  compartment_id          = var.compartment_id
  display_name            = "production-goldengate"
  license_model           = "LICENSE_INCLUDED"
  cpu_core_count          = 4
  is_auto_scaling_enabled = true
  deployment_type         = "DATABASE_ORACLE"
  fqdn                    = "goldengate.internal.example.com"

  subnet_id  = var.goldengate_subnet_id
  nsg_ids    = [var.goldengate_nsg_id]
  is_public  = false

  ogg_data {
    deployment_name          = "production-gg-deployment"
    admin_username           = "oggadmin"
    admin_password_secret_id = var.gg_admin_password_secret_id
  }

  defined_tags = { "Operations.Environment" = "production", "Operations.ManagedBy" = "terraform" }
}

output "deployment_url" { value = oci_golden_gate_deployment.production_gg.deployment_url }

Step 2: Database and Kafka Connections

resource "oci_golden_gate_connection" "source_oracle" {
  compartment_id     = var.compartment_id
  display_name       = "source-oracle-production"
  connection_type    = "ORACLE"
  technology_type    = "ORACLE_DATABASE"
  username           = "ggadmin"
  password_secret_id = var.source_db_gg_password_secret_id
  connection_string  = var.source_db_connection_string
  subnet_id          = var.goldengate_subnet_id
  nsg_ids            = [var.goldengate_nsg_id]
}

resource "oci_golden_gate_connection" "target_oracle" {
  compartment_id     = var.compartment_id
  display_name       = "target-oracle-reporting"
  connection_type    = "ORACLE"
  technology_type    = "ORACLE_DATABASE"
  username           = "ggadmin"
  password_secret_id = var.target_db_gg_password_secret_id
  connection_string  = var.target_db_connection_string
  subnet_id          = var.goldengate_subnet_id
}

resource "oci_golden_gate_connection" "kafka_target" {
  compartment_id     = var.compartment_id
  display_name       = "kafka-orders-topic"
  connection_type    = "KAFKA"
  technology_type    = "APACHE_KAFKA"
  username           = "gg-kafka-producer"
  password_secret_id = var.kafka_password_secret_id
  security_protocol  = "SASL_SSL"
  sasl_mechanism     = "PLAIN"
  subnet_id          = var.goldengate_subnet_id

  bootstrap_servers {
    host = "kafka-broker-1.internal.example.com"
    port = 9092
  }
  bootstrap_servers {
    host = "kafka-broker-2.internal.example.com"
    port = 9092
  }
}

resource "oci_golden_gate_connection_assignment" "source_to_gg" {
  connection_id = oci_golden_gate_connection.source_oracle.id
  deployment_id = oci_golden_gate_deployment.production_gg.id
}

resource "oci_golden_gate_connection_assignment" "target_to_gg" {
  connection_id = oci_golden_gate_connection.target_oracle.id
  deployment_id = oci_golden_gate_deployment.production_gg.id
}

Step 3: GoldenGate Deployment Alarm

resource "oci_monitoring_alarm" "gg_cpu_high" {
  compartment_id        = var.compartment_id
  display_name          = "goldengate-cpu-high"
  is_enabled            = true
  metric_compartment_id = var.compartment_id
  namespace             = "oci_goldengate"
  query                 = "CpuUtilization[5m]{deploymentId = 'GG_DEPLOYMENT_OCID'}.mean() > 80"
  severity              = "WARNING"
  pending_duration      = "PT10M"
  destinations          = [var.ops_notification_topic_id]
  body                  = "GoldenGate deployment CPU above 80%. Check Extract and Replicat lag. Consider increasing CPU allocation."
}

resource "oci_monitoring_alarm" "gg_lag_high" {
  compartment_id        = var.compartment_id
  display_name          = "goldengate-replication-lag"
  is_enabled            = true
  metric_compartment_id = var.compartment_id
  namespace             = "oci_goldengate"
  query                 = "ReplicationLag[5m]{deploymentId = 'GG_DEPLOYMENT_OCID'}.max() > 60"
  severity              = "CRITICAL"
  pending_duration      = "PT5M"
  destinations          = [var.ops_notification_topic_id]
  body                  = "GoldenGate replication lag exceeds 60 seconds. CDC pipeline may be falling behind. Investigate Extract or Replicat abend."
}

Operational Notes

GoldenGate requires Supplemental Logging enabled on the source Oracle database for every table being captured. Without it, the Extract process cannot reconstruct complete before and after images from the redo log. Enable it at the database level with ALTER DATABASE ADD SUPPLEMENTAL LOG DATA and at the table level with ALTER TABLE orders ADD SUPPLEMENTAL LOG DATA for each captured table.

The ggadmin user on the source database needs specific privileges: CONNECT, CREATE TABLE, SELECT ANY DICTIONARY, and the ability to manage supplemental logging. Use the Oracle-provided privilege scripts from the GoldenGate documentation rather than granting DBA directly, as the minimum privilege set is well-documented and substantially narrower than DBA.

Regards,
Osama

#OCI #OracleCloud #GoldenGate #CDC #Replication #Terraform #IaC #TechBlog #Oracle #DataEngineering #RealTime #OracleCloudInfrastructure #Kafka #ChangeDataCapture #DataPipeline #DatabaseReplication #GoldenGateMicroservices #ETL #DataIntegration #RealTimeAnalytics