Thinking Out Loud

May 4, 2014

GoldenGate 12c Features Found in 11.2.1.0.21 ???

Filed under: GoldenGate — mdinh @ 4:20 pm

I was curious if there was a way to add supplemental logging for ALL columns at the schema level.

Oracle GoldenGate Release Notes 12c (12.1.2) for Windows and UNIX April 2014

ADD SCHEMATRANDATA and ADD TRANDATA have been enhanced to support integrated Replicat for Oracle.

A new ALLCOLS option enables the unconditional logging of all of the key and non-key columns of a table.

Sounds great, but I am not on 12c and my curiosity got to me if this can be done for Oracle GoldenGate 11.2

Based on Oracle GoldenGate — Oracle RDBMS Server Recommended Patches (Doc ID 1557031.1), I had the following configurations set up

Oracle Linux Server release 6.5

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Patch  18095555     : applied on Sat Apr 26 10:29:11 PDT 2014
Patch  17478514     : applied on Sat Apr 26 10:27:19 PDT 2014
Patch description:  "Database Patch Set Update : 11.2.0.4.1 (17478514)"

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.21 18343248 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140404.1029_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr  4 2014 15:18:36

Here goes nothing!

oracle@arrow:san:/u01/app/ggs01
$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.21 18343248 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_140404.1029_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr  4 2014 15:18:36

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (arrow.localdomain) 1> dblogin userid ggs
Password:
Successfully logged into database.

GGSCI (arrow.localdomain) 2> info schematrandata soe

2014-05-04 08:27:00  INFO    OGG-01786  Schema level supplemental logging is disabled on schema SOE.
08:26:49 ARROW:(SYS@san):PRIMARY> select * from table(logmnr$always_suplog_columns('SOE','ADDRESSES')) order by intcol;

no rows selected

08:27:52 ARROW:(SYS@san):PRIMARY> 

GGSCI (arrow.localdomain) 3> add schematrandata soe

2014-05-04 08:28:12 INFO OGG-01788 SCHEMATRANDATA has been added on schema soe.

08:27:52 ARROW:(SYS@san):PRIMARY> r
  1* select * from table(logmnr$always_suplog_columns('SOE','ADDRESSES')) order by intcol

OWNER      TABLE_NAME COLUMN_NAME              INTCOL     SEGCOL    USERCOL
---------- ---------- -------------------- ---------- ---------- ----------
SOE        ADDRESSES  ADDRESS_ID                    1          1          1

08:28:15 ARROW:(SYS@san):PRIMARY> 

GGSCI (arrow.localdomain) 4> add schematrandata soe ALLCOLS

2014-05-04 08:28:26 INFO OGG-01788 SCHEMATRANDATA has been added on schema soe.

2014-05-04 08:28:26 INFO OGG-01977 SCHEMATRANDATA for all columns has been added on schema soe.

08:28:15 ARROW:(SYS@san):PRIMARY> r
  1* select * from table(logmnr$always_suplog_columns('SOE','ADDRESSES')) order by intcol

OWNER      TABLE_NAME COLUMN_NAME              INTCOL     SEGCOL    USERCOL
---------- ---------- -------------------- ---------- ---------- ----------
SOE        ADDRESSES  ADDRESS_ID                    1          1          1
SOE        ADDRESSES  CUSTOMER_ID                   2          2          2
SOE        ADDRESSES  DATE_CREATED                  3          3          3
SOE        ADDRESSES  HOUSE_NO_OR_NAME              4          4          4
SOE        ADDRESSES  STREET_NAME                   5          5          5
SOE        ADDRESSES  TOWN                          6          6          6
SOE        ADDRESSES  COUNTY                        7          7          7
SOE        ADDRESSES  COUNTRY                       8          8          8
SOE        ADDRESSES  POST_CODE                     9          9          9
SOE        ADDRESSES  ZIP_CODE                     10         10         10

10 rows selected.

08:28:32 ARROW:(SYS@san):PRIMARY>

GGSCI (arrow.localdomain) 5> exit

Verify supplemental logging for ALL COLS when new table is created.

08:28:32 ARROW:(SYS@san):PRIMARY> create table soe.t as select * from soe.addresses;

Table created.

08:28:48 ARROW:(SYS@san):PRIMARY> select * from table(logmnr$always_suplog_columns('SOE','ADDRESSES')) order by intcol;

OWNER      TABLE_NAME COLUMN_NAME              INTCOL     SEGCOL    USERCOL
---------- ---------- -------------------- ---------- ---------- ----------
SOE        ADDRESSES  ADDRESS_ID                    1          1          1
SOE        ADDRESSES  CUSTOMER_ID                   2          2          2
SOE        ADDRESSES  DATE_CREATED                  3          3          3
SOE        ADDRESSES  HOUSE_NO_OR_NAME              4          4          4
SOE        ADDRESSES  STREET_NAME                   5          5          5
SOE        ADDRESSES  TOWN                          6          6          6
SOE        ADDRESSES  COUNTY                        7          7          7
SOE        ADDRESSES  COUNTRY                       8          8          8
SOE        ADDRESSES  POST_CODE                     9          9          9
SOE        ADDRESSES  ZIP_CODE                     10         10         10

10 rows selected.

08:29:10 ARROW:(SYS@san):PRIMARY> select * from table(logmnr$always_suplog_columns('SOE','T')) order by intcol;

OWNER      TABLE_NAME COLUMN_NAME              INTCOL     SEGCOL    USERCOL
---------- ---------- -------------------- ---------- ---------- ----------
SOE        T          ADDRESS_ID                    1          1          1
SOE        T          CUSTOMER_ID                   2          2          2
SOE        T          DATE_CREATED                  3          3          3
SOE        T          HOUSE_NO_OR_NAME              4          4          4
SOE        T          STREET_NAME                   5          5          5
SOE        T          TOWN                          6          6          6
SOE        T          COUNTY                        7          7          7
SOE        T          COUNTRY                       8          8          8
SOE        T          POST_CODE                     9          9          9
SOE        T          ZIP_CODE                     10         10         10

10 rows selected.

08:29:37 ARROW:(SYS@san):PRIMARY>

Snipit from ggserr.log

oracle@arrow:san:/u01/app/ggs01
$ cat ggserr.log
2014-05-04 08:26:45  INFO    OGG-00948  Oracle GoldenGate Manager for Oracle, mgr.prm:  Lag for REPLICAT RLAS_SAN is 00:00:00 (checkpoint updated 89:00:47 ago).
2014-05-04 08:26:45  INFO    OGG-00948  Oracle GoldenGate Manager for Oracle, mgr.prm:  Lag for EXTRACT ESAN is 00:00:00 (checkpoint updated 00:00:03 ago).
2014-05-04 08:26:45  INFO    OGG-00948  Oracle GoldenGate Manager for Oracle, mgr.prm:  Lag for EXTRACT PSAN_LAS is 00:00:00 (checkpoint updated 89:00:47 ago).
2014-05-04 08:27:00  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): info schematrandata soe.
2014-05-04 08:27:00  INFO    OGG-01786  Oracle GoldenGate Command Interpreter for Oracle:  Schema level supplemental logging is disabled on schema SOE.
2014-05-04 08:28:12  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): add schematrandata soe.
2014-05-04 08:28:12  INFO    OGG-01788  Oracle GoldenGate Command Interpreter for Oracle:  SCHEMATRANDATA has been added on schema soe.
2014-05-04 08:28:26  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): add schematrandata soe ALLCOLS.
2014-05-04 08:28:26  INFO    OGG-01788  Oracle GoldenGate Command Interpreter for Oracle:  SCHEMATRANDATA has been added on schema soe.
2014-05-04 08:28:26  INFO    OGG-01977  Oracle GoldenGate Command Interpreter for Oracle:  SCHEMATRANDATA for all columns has been added on schema soe.
2014-05-04 08:28:51  INFO    OGG-01487  Oracle GoldenGate Capture for Oracle, esan.prm:  DDL found, operation [create table soe.t as select * from soe.addresses  (size 50)], start SCN [642971], commit SCN [642994] instance [san (1)], DDL seqno [2806], marker seqno [2806].
2014-05-04 08:28:51  INFO    OGG-00487  Oracle GoldenGate Capture for Oracle, esan.prm:  DDL operation included [INCLUDE ALL], optype [CREATE], objtype [TABLE], objowner [SOE], objname [T].
2014-05-04 08:28:51  INFO    OGG-00497  Oracle GoldenGate Capture for Oracle, esan.prm:  Writing DDL operation to extract trail file.
oracle@arrow:san:/u01/app/ggs01
$

For GoldenGate version 11.2.1.0.21, ADD SCHEMATRANDATA ALLCOLS works and ADD TRANDATA ALLCOLS does not work.

If ADD SCHEMATRANDATA ALLCOLS does not work for versions less than 11.2.1.0.21, then try EXECUTE DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(‘SOE’,’ALL’);

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 294 other followers

%d bloggers like this: