Moving large databases without downtime requires log-based change data capture. OCI GoldenGate reads Oracle redo logs and replicates every committed transaction to the target in real time. When the target is current, cutover takes the time it takes to drain the remaining lag, which is typically seconds rather than hours. This post covers provisioning OCI GoldenGate with Terraform, preparing the source database, monitoring lag, and executing a clean cutover.

Step 1: Provision the GoldenGate Deployment

resource "oci_golden_gate_deployment" "production" {
  compartment_id          = var.compartment_id
  display_name            = "oracle-to-postgresql"
  license_model           = "LICENSE_INCLUDED"
  deployment_type         = "DATABASE_ORACLE"
  cpu_core_count          = 2
  is_auto_scaling_enabled = false
  subnet_id               = var.private_subnet_id
  nsg_ids                 = [var.goldengate_nsg_id]

  ogg_data {
    admin_username  = "ggadmin"
    admin_password  = var.gg_admin_password
    deployment_name = "ORACLE_TO_PG"
  }

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

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

Step 2: Register Database Connections

resource "oci_golden_gate_connection" "source_oracle" {
  compartment_id  = var.compartment_id
  display_name    = "source-oracle"
  connection_type = "ORACLE"
  technology_type = "ORACLE_DATABASE"
  username        = "gguser"
  password        = var.oracle_gg_password
  wallet          = var.oracle_wallet_base64
  subnet_id       = var.private_subnet_id
  nsg_ids         = [var.goldengate_nsg_id]
}

resource "oci_golden_gate_connection" "target_postgres" {
  compartment_id  = var.compartment_id
  display_name    = "target-postgresql"
  connection_type = "POSTGRESQL"
  technology_type = "POSTGRESQL"
  host            = var.pg_host
  port            = 5432
  database_name   = "analytics"
  username        = "gg_replicat"
  password        = var.pg_gg_password
  ssl_mode        = "VERIFY_CA"
  subnet_id       = var.private_subnet_id
}

Step 3: Prepare the Source Oracle Database

-- Enable supplemental logging at database level
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- Column-level supplemental logging for each replicated table
ALTER TABLE orders.order_header ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE orders.order_lines   ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- GoldenGate database user
CREATE USER gguser IDENTIFIED BY StrongPassw0rd;
GRANT CREATE SESSION, RESOURCE, SELECT ANY DICTIONARY TO gguser;
GRANT SELECT ANY TABLE, FLASHBACK ANY TABLE TO gguser;
GRANT EXECUTE ON DBMS_FLASHBACK TO gguser;
GRANT LOGMINING TO gguser;
GRANT SELECT ON SYS.V_$DATABASE TO gguser;
GRANT SELECT ON SYS.V_$LOG, SYS.V_$ARCHIVED_LOG TO gguser;

-- Enable GoldenGate replication parameter
ALTER SYSTEM SET enable_goldengate_replication = TRUE SCOPE=BOTH;

-- Confirm supplemental logging is active
SELECT log_mode, supplemental_log_data_min FROM v$database;

Step 4: Monitor Lag with Alarms

resource "oci_monitoring_alarm" "gg_lag_high" {
  compartment_id        = var.compartment_id
  display_name          = "goldengate-lag-high"
  is_enabled            = true
  metric_compartment_id = var.compartment_id
  namespace             = "oci_golden_gate"
  query                 = "ExtractLag[5m]{deploymentId = '${oci_golden_gate_deployment.production.id}'}.max() > 60"
  severity              = "WARNING"
  pending_duration      = "PT5M"
  destinations          = [var.ops_notification_topic_id]
  body                  = "GoldenGate lag above 60 seconds. Check network throughput and Extract performance."
}

resource "oci_monitoring_alarm" "gg_abended" {
  compartment_id        = var.compartment_id
  display_name          = "goldengate-extract-abended"
  is_enabled            = true
  metric_compartment_id = var.compartment_id
  namespace             = "oci_golden_gate"
  query                 = "ExtractStatus[5m]{deploymentId = '${oci_golden_gate_deployment.production.id}', status = 'ABENDED'}.max() > 0"
  severity              = "CRITICAL"
  pending_duration      = "PT2M"
  destinations          = [var.ops_notification_topic_id]
  body                  = "GoldenGate Extract has abended. Replication is stopped. Investigate immediately."
}

Step 5: Cutover Checklist

# Check deployment and process status
oci golden-gate deployment get \
  --deployment-id ${DEPLOYMENT_ID} \
  --query 'data.{state:"lifecycle-state", fqdn:fqdn}'

# Cutover steps:
# 1. Stop new writes to Oracle source (put app in maintenance mode)
# 2. Monitor lag until it drops to zero
#    Watch: oci monitoring get-metrics ... ExtractLag
# 3. Stop Extract and Replicat from GoldenGate Admin console
# 4. Verify row counts match
#    Oracle:     SELECT COUNT(*) FROM orders.order_header;
#    PostgreSQL: SELECT COUNT(*) FROM public.order_header;
# 5. Check sequence/identity values are ahead on PostgreSQL
# 6. Update application connection strings to PostgreSQL
# 7. Bring application out of maintenance mode
# 8. Verify application health against PostgreSQL
# 9. Keep GoldenGate running in a stopped state for 48 hours before decommissioning

Step 6: Monitor Lag via Python

import oci
from datetime import datetime, timezone, timedelta

def get_gg_lag(deployment_id: str, compartment_id: str) -> float:
    config  = oci.config.from_file()
    client  = oci.monitoring.MonitoringClient(config)
    now     = datetime.now(timezone.utc)

    resp = client.summarize_metrics_data(
        compartment_id=compartment_id,
        summarize_metrics_data_details=oci.monitoring.models.SummarizeMetricsDataDetails(
            namespace="oci_golden_gate",
            query=f"ExtractLag[5m]{{deploymentId = '{deployment_id}'}}.max()",
            start_time=(now - timedelta(minutes=10)).isoformat(),
            end_time=now.isoformat()
        )
    )

    items = resp.data
    if items and items[0].aggregated_datapoints:
        return items[0].aggregated_datapoints[-1].value
    return -1

lag = get_gg_lag(
    deployment_id="ocid1.ggdeployment.oc1..yourdeploymentocid",
    compartment_id="ocid1.compartment.oc1..yourcompartmentocid"
)
status = "READY" if 0 <= lag = 10 else "NO_DATA"
print(f"Lag: {lag:.1f}s | Status: {status}")

Operational Notes

Enable supplemental logging at the table level for the specific tables you are replicating, not globally at the database level. Database-level supplemental logging adds overhead to every redo log write for every table, even ones you are not replicating. Table-level logging scopes the overhead to only what GoldenGate needs.

Do not cut over until lag has been consistently below 10 seconds for at least 30 minutes under production load. A lag that spikes during peak hours means the GoldenGate deployment does not have enough CPU to keep up at peak throughput. Scale the deployment CPU count before scheduling the cutover window.

Regards,
Osama

#OCI #OracleCloud #GoldenGate #Replication #ZeroDowntime #Migration #Terraform #TechBlog #Oracle #DatabaseEngineering #CDC #ChangeDataCapture #OracleDatabase #PostgreSQL #OracleCloudInfrastructure #DataMigration #PlatformEngineering #OracleDBA #RealTimeData #CloudDatabase