DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, July 12, 2018

Goldengate: How To Handle drop a column when not using DDL replication?

How To Handle drop a column when not using DDL replication.

1. If the dropped column is a composite key column, make sure there are no open transactions on the affected source table between steps 2 & 6.

2. Issue LAG EXTRACT. When Extract's lag is 0... or... at EOF, stop Extract.

GGSCI (source.doy.com) > lag extract ext1
Sending GETLAG request to EXTRACT ext1...
No records yet processed.
At EOF, no more records to process.

GGSCI (source.doy.com) > stop extract ext1

3. Issue LAG EXTRACT for the pump. When the pump's lag is 0... or... at EOF... AND Extract's output RBA = pump's next RBA, stop the pump.

GGSCI (source.doy.com) > lag extract dpump1
GGSCI (source.doy.com) > stop extract dpump1

4. Issue LAG REPLICAT. When Replicat's lag is 0... or... at EOF... AND the pump's output RBA = Replicat's next RBA, stop Replicat.

GGSCI (target.doy.com) > lag replicat rep1
GGSCI (target.doy.com) > stop replicat rep1

5. Drop the column from both the source and target tables.

SQL> alter table sample drop (col3);
Table altered.
SQL> alter table sample drop (col3);
Table altered.

6. Issue ALTER EXTRACT for the primary Extract to switch to a new pump trail file, then start Extract.

GGSCI (source.doy.com) > start extract ext1

7. Issue ALTER EXTRACT for the pump to switch to a new remote trail file, then start the pump.

GGSCI (source.doy.com) > start extract dpump1

8. Alter the pump to start at the beginning of the new pump trail file, then start the pump.

GGSCI (source.doy.com) > alter extract <dpump1>, extseqno , extrba 0
GGSCI (source.doy.com) > start extract <dpump1>

9. Alter Replicat to start at the beginning of the new remote trail file, then start Replicat.

GGSCI (target.doy.com) > alter replicat <rep1>, extseqno , extrba 0
GGSCI (target.doy.com) > start replicat <rep1>

10. Confirm that the column was dropped from replication by looking at the Replicat report file.

GGSCI (target.doy.com) > view report rep1
..
map test.sample, target test.sample;
Using following columns in default map by name:
col1, col2, col4
Using the following key columns for target table test.sample: col1.
...

No comments: