Handle Oracle DDL add a Column When not using DDL replication
Description:-
There will be NO data loss occur for below activity , because Extract will automatically pick up from where it left off.
Following DDL require downtime for Extract and Replicat.
Step 1:- If the newly added column is a composite key column, make sure there are no open transactions on the affected source table.
Step 2:- Issue LAG EXTRACT. When Extract’s lag is 0… or… at EOF, stop Extract.
GGSCI (gg-11.2.com as gguser@source) 44> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPUMP1 00:00:00 00:00:02
EXTRACT RUNNING EXT1 00:00:00 00:00:04
GGSCI (gg-11.2.com as gguser@source) 45> lag extract ext1
Sending GETLAG request to EXTRACT EXT1 …
No records yet processed.
At EOF, no more records to process.
GGSCI (gg-11.2.com as gguser@source) 46> stop extract ext1
Sending STOP request to EXTRACT EXT1 …
Request processed.
Step 3:-Issue LAG REPLICAT. When Replicat’s lag is 0… or… at EOF…
GGSCI (gg-12.2.com as gguser@source) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:00
GGSCI (gg-12.2.com as gguser@source) 4> lag replicat rep1
Sending GETLAG request to REPLICAT REP1 …
Last record lag 343,307 seconds.
At EOF, no more records to process.
GGSCI (gg-12.2.com as gguser@source) 5> stop replicat rep1
Sending STOP request to REPLICAT REP1 …
Request processed.
Step 4:- Add the column to both the source and target tables.
[oracle@gg-11 ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 13 18:40:38 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn sample/sample
Connected.
SQL> show user
USER is “SAMPLE”
SQL> desc tt
Name Null? Type
—————————————– ——– —————————-
ID NUMBER(10)
SQL> alter table tt add (name varchar2(10));
Table altered.
Target Side:
[oracle@gg-12 ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 13 18:41:20 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> conn sample/sample
Connected.
SQL> show user
USER is “SAMPLE”
SQL> desc tt
Name Null? Type
—————————————– ——– —————————-
ID NUMBER(10)
SQL> alter table tt add (name varchar2(10));
Table altered.
Step 5:- Start Extract:
GGSCI (gg-11.2.com as gguser@source) 49> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPUMP1 00:00:00 00:00:05
EXTRACT STOPPED EXT1 00:00:00 00:00:07
GGSCI (gg-11.2.com as gguser@source) 50> start extract ext1
Sending START request to MANAGER …
EXTRACT EXT1 starting
GGSCI (gg-11.2.com as gguser@source) 51> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPUMP1 00:00:00 00:00:02
EXTRACT RUNNING EXT1 00:10:18 00:00:02
Step 6:- start Replicat:
GGSCI (gg-12.2.com as gguser@source) 8> start replicat rep1
Sending START request to MANAGER …
REPLICAT REP1 starting
GGSCI (gg-12.2.com as gguser@source) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:02
Step 7:- Now insert the record into newly added column in source side.
SQL> insert into tt values (10,’SAM’);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tt where name=’SAM’;
ID NAME
———- ———-
10 SAM
Step 8:- Target side check whether the above record are replicated or not
SQL> select * from tt where name=’SAM’;
ID NAME
———- ———-
10 SAM
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
