Configuring Oracle GoldenGate DDL Replication Privileges for Zero‑Downtime Migration

Direct answer

You do not need to grant ALTER ANY TABLE as a broad, standalone privilege to the GoldenGate user. The supported path is to grant the DDL-related system privileges individually and, more importantly, use Oracle's role-based grant procedure in DBMS_GOLDENGATE_AUTH (GRANT_ADMIN_PRIVILEGE), which gives the GoldenGate admin user the capture/apply rights it needs — including DDL handling — without handing it a generic DBA-style grant. If your security policy still forbids the ANY-object privileges, the fallback is to disable DDL replication and apply DDL manually during a frozen-change window; that trade-off is described below.

Minimal privilege set

Assuming GoldenGate 12c or later (integrated capture/apply), create a dedicated user such as GGADMIN and:

CREATE USER ggadmin IDENTIFIED BY <strong_password> DEFAULT TABLESPACE gg_ts QUOTA UNLIMITED ON gg_ts;
GRANT CREATE SESSION TO ggadmin;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('GGADMIN');

GRANT_ADMIN_PRIVILEGE grants the capture/apply privileges through Oracle-managed roles rather than direct ANY grants, which is the least-privilege-friendly option Oracle documents. If you instead grant privileges directly, limit them to the DDL operations you actually replicate, e.g.:

GRANT ALTER ANY TABLE, CREATE ANY TABLE, DROP ANY TABLE,
      ALTER ANY INDEX, CREATE ANY INDEX, DROP ANY INDEX,
      ALTER ANY PROCEDURE, EXECUTE ANY PROCEDURE TO ggadmin;

Do not grant DBA, UNLIMITED TABLESPACE at the database level, or EXECUTE ANY PROCEDURE unless procedure DDL is genuinely in scope. Enable supplemental logging before starting the extract:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-- in GGSCI, per schema:
ADD SCHEMATRANDATA hr

Then configure DDL INCLUDE ALL (or scope it with DDL INCLUDE OBJNAME "hr.*") in both extract and replicat parameter files so only the application schema's DDL flows.

Audit and compliance impact of ALTER ANY TABLE

Granting ALTER ANY TABLE directly lets the account modify any table in any schema, which auditors typically flag because it bypasses schema ownership boundaries. Mitigations that are usually accepted: (1) use DBMS_GOLDENGATE_AUTH role-based grants instead of direct ANY grants; (2) enable unified auditing policies on the GGADMIN account so every DDL it issues is captured in UNIFIED_AUDIT_TRAIL; (3) scope DDL replication with DDL INCLUDE/EXCLUDE filters so the account's effective reach matches the migration scope; (4) expire or lock the account after cutover. The exact acceptability depends on your compliance framework (SOX, PCI, internal policy), so confirm with your security team — this is the one detail that can change the recommendation.

Risks of manual DDL application instead

If you withhold DDL privileges and apply changes by hand after cutover:

• Schema divergence window: any DDL on the source between freeze and cutover must be replayed manually in the exact order; a missed ALTER causes replicat abends on the next DML touching that object.
• Bidirectional conflict risk: in active-active, DDL applied on one side only can break DML on the other side immediately.
• Operational burden: you need a hard change freeze on the application schema, which is often the very thing zero-downtime migration is trying to avoid.

For a small OLTP app with a controlled release process, a documented change freeze plus manual DDL is defensible; for anything with frequent schema changes, replicate DDL with scoped privileges.

Verification

-- confirm grants
SELECT privilege FROM dba_sys_privs WHERE grantee = 'GGADMIN';
-- issue a test DDL on source, then in GGSCI:
INFO EXTRACT ext1, SHOWCH
INFO REPLICAT rep1, SHOWCH

Confirm lag is near zero and the test object exists on the target. Check ggserr.log for DDL capture errors if the object does not appear.