Pythian Blog: Technical Track

Truncates and sequences replication in Oracle GoldenGate

by Gleb Otochkin on Jul 15, 2016 12:00:00 AM

We use the terms DDL and DML in our DBA life with Oracle all the time. The first stands for Data Definition Language and it is about Data Definition changes. The latter is about manipulation with your data itself, and stands for Data Manipulation Language. When we speak about replication of our data by replication tools between Oracle databases, we generally either enable DDL, work only replicating DML, or do it for both together. In general, I would recommend replicating both DML and DDL just in case, to prevent the replication to be broken in case of unexpected structural changes in the replicated schemas. But in some cases you do not want to replicate all DDL or any DDL at all for certain reasons. I will discuss a couple of operations which are handled slightly different from pure DDL/DML changes in GoldenGate.

The first of them is truncate operation. In Oracle it is definitely DDL and you can see that.

[code lang="sql"] orcl> select object_id,data_object_id,last_ddl_time from dba_objects where object_name='EMP_TEST'; OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIME ---------------- ---------------- ----------------- 148769 148769 06/24/16 16:07:04 orcl> truncate table emp_test; Table truncated. orcl> select object_id,data_object_id,last_ddl_time from dba_objects where object_name='EMP_TEST'; OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIME ---------------- ---------------- ----------------- 148769 148770 06/24/16 16:15:52 orcl> [/code] It is clear that the object gets the new data_object_id and last_ddl_time shows new time.

There is a general assumption that you need to enable a DDL support to replicate truncates. But is this true? In reality you can replicate truncates (with some limitations) without full DDL support, and I want to show you how it can be done. What you need to do is setup a parameter GETTRUNCATES. Let's setup it on extract and see how it works.

Here is my extract parameter file:

[code lang="text"] [oracle@bigdatalite oggora]$ cat dirprm/trext.prm extract trext userid ogg, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT --RMTHOSTOPTIONS RMTHOST bigdatalite, MGRPORT 7849 RMTTRAIL ./dirdat/tr, format release 11.2 GETTRUNCATES TABLE trsource.*; [/code] We don't have DDL support and if we try to add a column on the source and put a value to that column our replicat on other side will be abended. [code lang="sql"] orcl> alter table trsource.emp add col1 varchar2(10) ; Table altered. orcl> update trsource.emp set col1='Test1' where empno=7499; 1 row updated. orcl> commit; [/code] And on the target side: [code lang="text"] 2016-06-27 13:51:47 INFO OGG-01021 Oracle GoldenGate Delivery for Oracle, trrep.prm: Command received from GGSCI: STATS. 2016-06-27 13:57:37 ERROR OGG-01161 Oracle GoldenGate Delivery for Oracle, trrep.prm: Bad column index (8) specified for table TRSOURCE.EMP, max columns = 8. 2016-06-27 13:57:37 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, trrep.prm: PROCESS ABENDING. [/code] You are going to get similar errors for other DDL operations but not for truncates. [code lang="sql"] orcl> truncate table trsource.emp; Table truncated. orcl> [/code] [code lang="text"] GGSCI (bigdatalite.localdomain) 1> send trext, stats Sending STATS request to EXTRACT TREXT ... Start of Statistics at 2016-06-27 14:05:24. Output to ./dirdat/tr: Extracting from TRSOURCE.EMP to TRSOURCE.EMP: *** Total statistics since 2016-06-27 14:05:07 *** Total inserts 0.00 Total updates 0.00 Total deletes 0.00 Total truncates 1.00 Total discards 0.00 Total operations 1.00 [/code]

You can see that we have captured the truncate by our extract. Even our DDL support is disabled. What we need is to set up the same parameter GETTRUNCATES on replicat side. Why do we need to set it up explicitly? Because the default behaviour and parameter for GoldenGate is "IGNORETRUNCATES" for all processes. As result, the truncates will be applied to the target system.

We are setting our parameter on replicat side and see the result:

[code lang="text"] [oracle@bigdatalite ogg11ora]$ cat dirprm/trrep.prm replicat trrep --trace DISCARDFILE ./dirdsc/discard.out, append userid ogg@test, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT assumetargetdefs --DDL include all GETTRUNCATES map trsource.emp, target trdest.emp; [oracle@bigdatalite ogg11ora]$ GGSCI (bigdatalite.localdomain) 4> send trrep, stats Sending STATS request to REPLICAT TRREP ... Start of Statistics at 2016-06-27 14:08:40. Replicating from TRSOURCE.EMP to TRDEST.EMP: *** Total statistics since 2016-06-27 14:08:25 *** Total inserts 0.00 Total updates 0.00 Total deletes 0.00 Total truncates 1.00 Total discards 0.00 Total operations 1.00 [/code] [code lang="sql"] test> select count(*) from trdest.emp; COUNT(*) ---------------- 0 test> [/code]

We don't need full DDL support if we want to replicate truncates only. Sometimes it may help us when we have workflow including truncates, but we don't want to replicate all DDL commands for some reasons. Just keep in mind that it works with some limitations. You cannot replicate by using "truncate partition" for Oracle. It will require full DDL support.

The second thing I want to discuss in this topic is support for sequences values replication. Sometimes people assume that it requires DDL support, but this is not true. As matter of fact replicating of sequences values doesn't require you to enable DDL support for your replication. Of course, you need full DDL replication support to replicate CREATE, ALTER, DROP, RENAME for sequences, but the values are replicated as DML.

To enable the replication of sequences you need to create a special user on source and target databases, add the user to the GGSCHEMA parameter to your .GLOBALS file, and run one script to create all necessary procedures in the newly created schema. Let's have a closer look. I have a user OGG I am using for connection and I plan to use the same user for sequence support.

Here is my .GLOBALS file:

[code lang="text"] [oracle@bigdatalite oggora]$ cat GLOBALS GGSCHEMA OGG [/code] The same I have on the target side: [code lang="text"] [oracle@bigdatalite ogg11ora]$ cat GLOBALS GGSCHEMA OGG [/code] I ran the script sequence.sql on both sides. [code lang="sql"] orcl> @sequence.sql Please enter the name of a schema for the GoldenGate database objects: ogg Setting schema name to OGG UPDATE_SEQUENCE STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors GETSEQFLUSH Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors SEQTRACE Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors REPLICATE_SEQUENCE STATUS: Line/pos Error ---------------------------------------- ----------------------------------------------------------------- No errors No errors STATUS OF SEQUENCE SUPPORT -------------------------------------------------------------- SUCCESSFUL installation of Oracle Sequence Replication support orcl> [/code]

And on the source side add primary key supplemental logging to the sys.seq$ table:

[code lang="sql"] orcl> ALTER TABLE sys.seq$ ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; Table altered. orcl> [/code] You may have a look to the procedures created by the scripts: SEQTRACE GETSEQFLUSH REPLICATESEQUENCE UPDATESEQUENCE These procedures enable interface to flush, update and replicate the sequences.

Now we are creating a sequence on the source and target with the same parameters.

[code lang="sql"] orcl> create sequence trsource.empno_seq start with 8100; Sequence created. orcl> [/code] Adding parameter SEQUENCE to our parameter file for extract: [code lang="text"] [oracle@bigdatalite oggora]$ cat dirprm/trext.prm extract trext userid ogg, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT --RMTHOSTOPTIONS RMTHOST bigdatalite, MGRPORT 7849 RMTTRAIL ./dirdat/tr, format release 11.2 --DDL include objname trsource.* GETTRUNCATES SEQUENCE tsource.*; TABLE trsource.*; [oracle@bigdatalite oggora]$ [/code]

On the target we are creating the same sequence:

[code lang="sql"] test> create sequence trdest.empno_seq start with 8100; Sequence created. test> [/code] [code lang="text"] [oracle@bigdatalite ogg11ora]$ cat dirprm/trrep.prm replicat trrep --trace DISCARDFILE ./dirdsc/discard.out, append userid ogg@test, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT assumetargetdefs --DDL include all GETTRUNCATES map trsource., target trdest.; [/code]

I made our sequences a bit different on purpose. Our source sequence had a slightly bigger current value than target:

[code lang="sql"] orcl> select trsource.empno_seq.currval from dual; CURRVAL ---------------- 8102 orcl> test> select trdest.empno_seq.currval from dual; CURRVAL ---------------- 8100 test> [/code]

What we need is to run command FLUSH SEQUENCE on our extract side:

[code lang="text"] GGSCI (bigdatalite.localdomain) 9> dblogin userid ogg@orcl,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, BLOWFISH, ENCRYPTKEY DEFAULT Successfully logged into database. GGSCI (bigdatalite.localdomain as ogg@orcl) 3> flush sequence trsource.empno_seq Successfully flushed 1 sequence(s) trsource.empno_seq. GGSCI (bigdatalite.localdomain as ogg@orcl) 4> [/code] And on target we can see: [code lang="sql"] test> select * from dba_sequences where SEQUENCE_NAME='EMPNO_SEQ'; SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------------------------ ------------------------------ ---------------- ---------------- ---------------- - - ---------------- ---------------- TRDEST EMPNO_SEQ 1 9999999999999999 1 N N 20 8143 test> select last_number from dba_sequences where SEQUENCE_NAME='EMPNO_SEQ'; LAST_NUMBER ---------------- 8143 test> [/code] The last number for the sequence on the target has been increased to 8143 when on the source we have only cache was flushed and we got 8123 as a last number for the sequence: [code lang="sql"] orcl> select last_number from dba_sequences where SEQUENCE_NAME='EMPNO_SEQ'; LAST_NUMBER ---------------- 8123 orcl> [/code] Let's try to get new values for the sequence. [code lang="sql"] orcl> select trsource.empno_seq.nextval from dual; NEXTVAL ---------------- 8104 orcl> select trsource.empno_seq.nextval from dual; NEXTVAL ---------------- 8105 [/code]

We continue to increase values on the source, and as soon as we crossed max number for the source (8123) we got new value on the target:

[code lang="sql"] orcl> select trsource.empno_seq.nextval from dual; NEXTVAL ---------------- 8119 ........ NEXTVAL ---------------- 8124 orcl> test> select last_number from dba_sequences where SEQUENCE_NAME='EMPNO_SEQ'; LAST_NUMBER ---------------- 8144 test> [/code] And the statistics on the target will be shown as updates: [code lang="text"] GGSCI (bigdatalite.localdomain) 1> send trrep, stats Sending STATS request to REPLICAT TRREP ... Start of Statistics at 2016-06-29 13:20:36. Replicating from TRSOURCE.EMPNO_SEQ to TRDEST.EMPNO_SEQ: *** Total statistics since 2016-06-29 13:10:52 *** Total updates 4.00 Total discards 0.00 Total operations 4.00 [/code]

We can see that the two operations are a bit different from all other standard DDL and DML in Oracle GoldenGate. I hope this small piece of information may help you in your implementation, or help to support your GoldenGate environment.

Stay tuned and keep your eyes on Pythian blog.

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.