Oracle DBA

In Oracle environments where DDL replication (i.e., replicating schema changes automatically) is not enabled, handling a DDL change like adding a column in a replicated environment requires coordination and care. If not done properly, it may lead to data inconsistency or replication failures. In this blog post, we walk through a safe procedure using GoldenGate (or similar replication tools) to alter table structure, with an example using odba as the schema owner.

Scenario & Considerations

• You have a source and target database under replication (for example using Oracle GoldenGate).
• You want to add a new column to a table in both source and target, but you are not using DDL replication.
• The goal is to ensure that:
  1. No data is lost.
  2. The replication continues correctly.
  3. The new column is correctly synchronized from source to target.

Some DDL changes can be more complex (e.g. altering primary key, changing datatypes). This article focuses on the “add column” use case, which is one of the safer DDL changes when managed properly.

Step-by-Step Procedure (using odba schema)

Below is a step-by-step method that you can adapt. Replace odba with your schema name, TT with your table name, and adjust for your environment.

Step 1: Prepare & Validate Replicator Lag

Before making any change:

• Ensure that the EXTRACT process on the source has no lag (i.e., caught up with all transactions).
• Ensure that the REPLICAT on the target has also no lag.

From GGSCI (on source):

GGSCI> info all

Check extract lag:

GGSCI> lag extract ext1
-- Wait until it shows “At EOF” or lag = 0

Once lag is zero / at EOF:

GGSCI> stop extract ext1

Do similar on the target side (replicat):

On target:

GGSCI> info all
GGSCI> lag replicat rep1
-- Wait for lag = 0 or at EOF
GGSCI> stop replicat rep1

This ensures no in-flight changes are pending.

Step 2: Execute the DDL on Source and Target

Once the replication processes are stopped:

• On source database, as odba: ALTER TABLE TT ADD (new_col VARCHAR2(100));
• On target database, also as odba: ALTER TABLE TT ADD (new_col VARCHAR2(100));

It is crucial that the DDL is identical (same column name, datatype, nullability, default if any, etc.).

Step 3: Restart Extract and Replicat

After the DDL is applied on both sides:

• On source: GGSCI> start extract ext1
• On target: GGSCI> start replicat rep1

Check status:

GGSCI> info all

Now the replication should resume, and new DML operations can include the new column.

Step 4: Validate Insertion & Replication

To test:

• On source (as odba): INSERT INTO TT (id, new_col) VALUES (10, 'TESTVAL'); COMMIT;
• On target, check: SELECT id, new_col FROM TT WHERE id = 10;

You should see:

ID   NEW_COL
10   TESTVAL

This confirms that the new column and data change flowed correctly across replication.

Example Session (odba & TT)

Here’s a simulated session with odba user and TT table:

-- On source GGSCI:
GGSCI> info all
GGSCI> lag extract ext1
  -- At EOF
GGSCI> stop extract ext1

-- On target GGSCI:
GGSCI> info all
GGSCI> lag replicat rep1
  -- At EOF
GGSCI> stop replicat rep1

-- On source DB (as odba):
SQL> ALTER TABLE TT ADD (new_col VARCHAR2(100));

-- On target DB (as odba):
SQL> ALTER TABLE TT ADD (new_col VARCHAR2(100));

-- Restart on source:
GGSCI> start extract ext1

-- Restart on target:
GGSCI> start replicat rep1

-- On source, insert:
SQL> INSERT INTO TT (id, new_col) VALUES (20, 'HELLO');
SQL> COMMIT;

-- On target, verify:
SQL> SELECT id, new_col FROM TT WHERE id = 20;
-- Expect: 20 | HELLO

Important Notes & Caveats

• If the new column is part of a composite key (or affects primary key/index), additional care is required—ensure there are no open transactions on the source table before making the change.
• The replication tool must be compatible with structural changes; some tools may require additional configuration for schema changes.
• Always test this procedure in a nonproduction / staging environment before applying it in production.
• Ensure you have backups or a fallback plan, just in case something unexpected occurs during DDL or replication restart.
• Monitor replication lag and error logs after restart to catch any anomalies early.

By following this disciplined process, DBAs can safely introduce a new column in a replicated environment even when DDL replication is disabled. The key is to quiesce replication, apply identical DDL on both sides, and then resume replication, verifying that data continues to flow as expected.