Here is a demo to modify/add columns in a table in oracle goldengate configuration.
In this demo, I used both source and target in the same database.
Database name:- ggtest1
Step 1:- Preparing the Environment
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
In this demo, I used both source and target in the same database.
Database name:- ggtest1
Step 1:- Preparing the Environment
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> alter database force logging;
SQL>
SQL> alter database add supplemental log data;
SQL> select force_logging, supplemental_log_data_min from v$database;
FORCE_LOGGING SUPPLEME
--------------------------------------- --------
YES YES
created goldengate user ogguser
SQL> create user ogguser identified by oracle default tablespace goldengate;
User created.
SQL> grant connect, resource, dba, unlimited tablespace to ogguser;
Grant succeeded.
SQL>ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;SQL>show parameter ENABLE_GOLDENGATE_REPLICATION
SQL>EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'ogguser', privilege_type=>'CAPTURE , grant_optional_privileges=> '*');
Create source/target user in the same database and create sample table and insert records for testing.
creating Source username US01
SQL> CREATE USER us01 IDENTIFIED BY us01;
SQL> GRANT CONNECT,RESOURCE,DBA TO us01;
create table tb1(id number primary key, name varchar2(20));
insert into tb1 values(1, 'DEEKSHI');
insert into tbl values(2, 'PRITHVI');
creating target schema DEMO
SQL> CREATE USER demo IDENTIFIED BY demo;
SQL> GRANT CONNECT,RESOURCE,DBA TO demo;
create table tb1(id number primary key, name varchar2(20));
insert into tb1 values(1, 'DEEKSHI');
insert into tbl values(2, 'PRITHVI');
From Goldengate home(GGHOME) run the below scripts as sysdba
[oracle@rac1 Desktop]$ cd /home/oracle/gghome/
[oracle@rac1 gghome]$ sqlplus / as sysdba
SQL> @marker_setup
SQL> @ddl_setup
SQL > @role_setup
GRANT GGS_GGSUSER_ROLE TO
where is the user assigned to the GoldenGate processes.
SQL> grant ggs_ggsuser_role to ogguser;
SQL> @ddl_enable
Step 2:- Configuring Goldengate environment
GGSCI (rac1.localdomain) 1> view params mgr
PORT 7809
GGSCI (rac1.localdomain) 2> start mgr
GGSCI (rac1.localdomain) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (rac1.localdomain) 7> edit params ex1
EXTRACT ex1
userid gguser@ggtest1, password gguser
TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 4194304
DISCARDFILE /home/oracle/gghome/dirrpt/ex1.dsc, APPEND, MEGABYTES 50
EXTTRAIL /home/oracle/gghome/dirdat/ex
TABLE us01.*;
GGSCI (rac1.localdomain) 10> edit params rep1
REPLICAT rep1
USERID gguser@ggtest1, PASSWORD gguser
ASSUMETARGETDEFS
DISCARDFILE /home/oracle/gghome/dirrpt/rep1.dsc, APPEND, MEGABYTES 90
INSERTALLRECORDS
HANDLECOLLISIONS
MAP us01.*, TARGET demo.*;
GGSCI (rac1.localdomain) 13> edit params ./GLOBALS
GGSCHEMA OGGUSER
CHECKPOINTTABLE OGGUSER.CHECKPOINTTABLE
GGSCI (rac1.localdomain) 14> view params mgr
PORT 7809
GGSCI (rac1.localdomain) 15> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (rac1.localdomain) 21> add extract ex1, tranlog, threads 1, begin now
EXTRACT added.
GGSCI (rac1.localdomain) 22> add exttrail /home/oracle/gghome/dirdat/ex, EXTRACT ex1, MEGABYTES 500
EXTTRAIL added.
GGSCI (rac1.localdomain) 23> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EX1 00:00:00 00:00:29
GGSCI (rac1.localdomain) 26> dblogin userid ogguser, password oracle
Successfully logged into database.
GGSCI (rac1.localdomain as ogguser@ggtest1) 27> add checkpointtable ogguser.checkpointtable
Successfully created checkpoint table ogguser.checkpointtable.
GGSCI (rac1.localdomain as ogguser@ggtest1) 28> add trandata us01.*
2018-12-21 00:56:49 INFO OGG-15131 Logging of supplemental redo log data is already enabled for table US01.TB1.
2018-12-21 00:56:49 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table US01.TB1.
GGSCI (rac1.localdomain as ogguser@ggtest1) 29> info trandata us01.*
Logging of supplemental redo log data is enabled for table US01.TB1.
Columns supplementally logged for table US01.TB1: "ID".
Prepared CSN for table US01.TB1: 2538827
GGSCI (rac1.localdomain as ogguser@ggtest1) 30> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EX1 00:00:00 00:03:10
GGSCI (rac1.localdomain as ogguser@ggtest1) 31> add replicat rep1 exttrail /home/oracle/gghome/dirdat/ex, checkpointtable ogguser.checkpointtable
REPLICAT added.
GGSCI (rac1.localdomain as ogguser@ggtest1) 32> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EX1 00:00:00 00:03:56
REPLICAT STOPPED REP1 00:00:00 00:00:04
GGSCI (rac1.localdomain as ogguser@ggtest1) 33> start ex1
Sending START request to MANAGER ...
EXTRACT EX1 starting
GGSCI (rac1.localdomain as ogguser@ggtest1) 34> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STARTING EX1 00:00:00 00:04:10
REPLICAT STOPPED REP1 00:00:00 00:00:18
GGSCI (rac1.localdomain as ogguser@ggtest1) 36> info ex1, detail
EXTRACT EX1 Last Started 2018-12-21 00:58 Status RUNNING
Checkpoint Lag 00:04:13 (updated 00:00:05 ago)
Process ID 26709
Log Read Checkpoint Oracle Redo Logs
2018-12-21 00:54:39 Thread 1, Seqno 54, RBA 30467088
SCN 0.0 (0)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
/home/oracle/gghome/dirdat/ex 0 1428 500 EXTTRAIL
Extract Source Begin End
+DATA/GGTEST1/ONLINELOG/group_3.311.995089343 2018-12-21 00:54 2018-12-21 00:54
Not Available * Initialized * 2018-12-21 00:54
Not Available * Initialized * 2018-12-21 00:54
Not Available * Initialized * 2018-12-21 00:54
Current directory /home/oracle/gghome
Report file /home/oracle/gghome/dirrpt/EX1.rpt
Parameter file /home/oracle/gghome/dirprm/ex1.prm
Checkpoint file /home/oracle/gghome/dirchk/EX1.cpe
Process file /home/oracle/gghome/dirpcs/EX1.pce
Error log /home/oracle/gghome/ggserr.log
GGSCI (rac1.localdomain as ogguser@ggtest1) 37> info ex1
EXTRACT EX1 Last Started 2018-12-21 00:58 Status RUNNING
Checkpoint Lag 00:04:13 (updated 00:00:09 ago)
Process ID 26709
Log Read Checkpoint Oracle Redo Logs
2018-12-21 00:54:39 Thread 1, Seqno 54, RBA 30467088
SCN 0.0 (0)
GGSCI (rac1.localdomain as ogguser@ggtest1) 38> info ex1
EXTRACT EX1 Last Started 2018-12-21 00:58 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Process ID 26709
Log Read Checkpoint Oracle Redo Logs
2018-12-21 00:58:51 Thread 1, Seqno 54, RBA 31052800
SCN 0.2539046 (2539046)
GGSCI (rac1.localdomain as ogguser@ggtest1) 39> start rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (rac1.localdomain as ogguser@ggtest1) 40> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EX1 00:00:00 00:00:04
REPLICAT STARTING REP1 00:00:00 00:00:47
GGSCI (rac1.localdomain as ogguser@ggtest1) 42> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EX1 00:00:00 00:00:08
REPLICAT RUNNING REP1 00:00:00 00:00:01
GGSCI (rac1.localdomain as ogguser@ggtest1) 43> info rep1, detail
REPLICAT REP1 Last Started 2018-12-21 00:59 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Process ID 26857
Log Read Checkpoint File /home/oracle/gghome/dirdat/ex000000000
First Record RBA 0
Current Log BSN value: (no data)
Last Committed Transaction CSN value: (no data)
Extract Source Begin End
/home/oracle/gghome/dirdat/ex000000000 * Initialized * First Record
/home/oracle/gghome/dirdat/ex000000000 * Initialized * First Record
Current directory /home/oracle/gghome
Report file /home/oracle/gghome/dirrpt/REP1.rpt
Parameter file /home/oracle/gghome/dirprm/rep1.prm
Checkpoint file /home/oracle/gghome/dirchk/REP1.cpr
Checkpoint table ogguser.checkpointtable
Process file /home/oracle/gghome/dirpcs/REP1.pcr
Error log /home/oracle/gghome/ggserr.log
Step 3: - Check Lag and stop the extract/replicat
GGSCI (rac1.localdomain as ogguser@ggtest1) 45> lag extract ex1
Sending GETLAG request to EXTRACT EX1 ...
Last record lag 2 seconds.
At EOF, no more records to process
GGSCI (rac1.localdomain as ogguser@ggtest1) 47> stop extract ex1
Sending STOP request to EXTRACT EX1 ...
Request processed.
GGSCI (rac1.localdomain as ogguser@ggtest1) 48> lag replicat rep1
Sending GETLAG request to REPLICAT REP1 ...
Last record lag 7 seconds.
At EOF, no more records to process
GGSCI (rac1.localdomain as ogguser@ggtest1) 49> stop replicat rep1
Sending STOP request to REPLICAT REP1 ...
Request processed.
GGSCI (rac1.localdomain as ogguser@ggtest1) 50> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EX1 00:00:00 00:00:16
REPLICAT STOPPED REP1 00:00:00 00:00:02
Step 4:- Check table is accessed by any users using below query.
SQL> SELECT SID, OBJECT, OWNER FROM GV$ACCESS WHERE OBJECT='TB1' AND OWNER='US01'
SQL> /
no rows selected
SQL> SELECT SID, OBJECT, OWNER FROM GV$ACCESS WHERE OBJECT='TB1' AND OWNER='DEMO';
no rows selected
also If the newly added column is a composite key column, make sure there are no open transactions on the affected source table.
Step 5: -Perform the table modifications on the source and target databases/schema.
SQL> conn us01/us01
Connected.
SQL> show user
USER is "US01"
SQL> desc TB1
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(20)
SQL> alter table tb1 add ( DEPT varchar2(20));
Table altered.
SQL> DESC TB1
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(20)
DEPT VARCHAR2(20)
SQL> select * from tb1;
ID NAME DEPT
---------- -------------------- --------------------
3 RAMESH
1 DEEKSHI
2 PRITHVI
3 rows selected.
on demo schema
SQL> conn demo/demo
Connected.
SQL> show user
USER is "DEMO"
SQL> DESC TB1
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(20)
SQL> alter table tb1 add (dept varchar2(20));
Table altered.
SQL> desc TB1
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(20)
DEPT VARCHAR2(20)
SQL> select * from tb1;
ID NAME DEPT
---------- -------------------- --------------------
1 DEEKSHI
2 PRITHVI
3 RAMESH
Step 6:- Start extract and replicat
GGSCI (rac1.localdomain as ogguser@ggtest1) 52> start ex1
Sending START request to MANAGER ...
EXTRACT EX1 starting
GGSCI (rac1.localdomain as ogguser@ggtest1) 53> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EX1 00:00:00 00:00:02
REPLICAT STOPPED REP1 00:00:00 00:03:45
GGSCI (rac1.localdomain as ogguser@ggtest1) 54> start replicat rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (rac1.localdomain as ogguser@ggtest1) 55> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EX1 00:00:00 00:00:05
REPLICAT RUNNING REP1 00:00:00 00:00:00
GGSCI (rac1.localdomain as ogguser@ggtest1) 56> info all, all processes
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EX1 00:00:00 00:00:00
REPLICAT RUNNING REP1 00:00:00 00:00:05
GGSCI (rac1.localdomain as ogguser@ggtest1) 57>
Final Step:- Verify the changes on both source and target schemas by update/insert statements.
SQL> update tb1 set dept='IT' ;
3 rows updated.
SQL> commit;
Commit complete.
SQL> select * from tb1;
ID NAME DEPT
---------- -------------------- --------------------
3 RAMESH IT
1 DEEKSHI IT
2 PRITHVI IT
3 rows selected.
SQL> insert into tb1 values (4,'VIDHYA','MEDICAL');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM TB1;
ID NAME DEPT
---------- -------------------- --------------------
3 RAMESH IT
4 VIDHYA MEDICAL
1 DEEKSHI IT
2 PRITHVI IT
4 rows selected.
SQL> SELECT * FROM TB1 ORDER BY ID;
ID NAME DEPT
---------- -------------------- --------------------
1 DEEKSHI IT
2 PRITHVI IT
3 RAMESH IT
4 VIDHYA MEDICAL
4 rows selected.
on DEMO schema side.
SQL> conn demo/demo
Connected.
SQL> show user
USER is "DEMO"
after update statement demo schema output as below.
SQL> SELECT * FROM TB1 ORDER BY ID;
ID NAME DEPT
---------- -------------------- --------------------
1 DEEKSHI IT
2 PRITHVI IT
3 RAMESH IT
after insert result on demo schema.
SQL> SELECT * FROM TB1 ORDER BY ID;
ID NAME DEPT
---------- -------------------- --------------------
1 DEEKSHI IT
2 PRITHVI IT
3 RAMESH IT
4 VIDHYA MEDICAL
SQL>
hope, this helps.
References:-
https://docs.oracle.com/goldengate/1212/gg-winux/GITER/ogg_wu_admin_tera.htm#GITER269
http://www.oracledbwr.com/goldengate/how-to-handle-oracle-ddl-add-a-column-when-not-using-ddl-replication/
No comments:
Post a Comment