Vishal desai’s Oracle Blog

November 6, 2015

SCHEMATRANDATA, NOVALIDATE Primary Key and Supplemental logging

Filed under: Golden Gate — vishaldesai @ 10:16 pm

We had SCHEMATRANDATA enabled in our golden gate configuration and in recent release developer decided to enable novalidate constraint as we were just converting from regular index to hash partitioned index so their thought was that there is no pointing in validating existing records again. But as constraint was enabled with NOVALIDATE option, due to SCHEMATRANDATA, supplemental logging was enabled on all the columns on some active tables. As a result update on single column captured all columns in golden gate trail and caused lag on replicat side as single column updates got converted to multi column updates.

Golden Gate configuration:

GGSCI > info schematrandata DEAL

2015-11-06 20:01:56  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema DEAL.

2015-11-06 20:01:56  INFO    OGG-01980  Schema level supplemental logging is enabled on schema DEAL for all scheduling columns.

Test Case:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
SQL>DROP TABLE DEAL.t;

Table dropped.

SQL>
SQL>CREATE TABLE DEAL.t
    AS
       SELECT *
         FROM dba_objects
        WHERE object_id IS NOT NULL;

Table created.

SQL>
SQL>ALTER TABLE DEAL.t MODIFY object_id NOT NULL;

Table altered.

SQL>
SQL>SELECT * FROM TABLE (logmnr$always_suplog_columns ('DEAL', 'T'));

OWNER                          TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------
DEAL                           T                              OWNER
DEAL                           T                              OBJECT_TYPE
DEAL                           T                              TEMPORARY
DEAL                           T                              SECONDARY
DEAL                           T                              OBJECT_NAME
DEAL                           T                              NAMESPACE
DEAL                           T                              OBJECT_ID
DEAL                           T                              DATA_OBJECT_ID
DEAL                           T                              LAST_DDL_TIME
DEAL                           T                              SUBOBJECT_NAME
DEAL                           T                              CREATED
DEAL                           T                              TIMESTAMP
DEAL                           T                              STATUS
DEAL                           T                              GENERATED
DEAL                           T                              EDITION_NAME

15 rows selected.

SQL>
SQL>CREATE UNIQUE INDEX DEAL.tpk  ON DEAL.t (object_id);

Index created.

SQL>ALTER TABLE DEAL.t ADD CONSTRAINT tpk PRIMARY KEY(object_id) USING INDEX;

Table altered.

SQL>
SQL>SELECT * FROM TABLE (logmnr$always_suplog_columns ('DEAL', 'T'));

OWNER                          TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------
DEAL                           T                              OBJECT_ID

SQL>
SQL>ALTER TABLE DEAL.t DISABLE  CONSTRAINT tpk;

Table altered.

SQL>
SQL>SELECT * FROM TABLE (logmnr$always_suplog_columns ('DEAL', 'T'));

OWNER                          TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------
DEAL                           T                              OBJECT_ID

SQL>DROP INDEX DEAL.tpk;

Index dropped.

SQL>
SQL>SELECT * FROM TABLE (logmnr$always_suplog_columns ('DEAL', 'T'));

OWNER                          TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------
DEAL                           T                              OWNER
DEAL                           T                              OBJECT_TYPE
DEAL                           T                              TEMPORARY
DEAL                           T                              SECONDARY
DEAL                           T                              OBJECT_NAME
DEAL                           T                              NAMESPACE
DEAL                           T                              OBJECT_ID
DEAL                           T                              DATA_OBJECT_ID
DEAL                           T                              LAST_DDL_TIME
DEAL                           T                              SUBOBJECT_NAME
DEAL                           T                              CREATED
DEAL                           T                              TIMESTAMP
DEAL                           T                              STATUS
DEAL                           T                              GENERATED
DEAL                           T                              EDITION_NAME

15 rows selected.

SQL>CREATE UNIQUE INDEX DEAL.tpk  ON DEAL.t (object_id) global PARTITION BY HASH (object_id)
  2    (PARTITION p1  ,  PARTITION p2);

Index created.

SQL>SELECT * FROM TABLE (logmnr$always_suplog_columns ('DEAL', 'T'));

OWNER                          TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
DEAL                           T                              OBJECT_ID

SQL>ALTER TABLE DEAL.t ENABLE NOVALIDATE CONSTRAINT tpk USING INDEX;

Table altered.

SQL>SELECT * FROM TABLE (logmnr$always_suplog_columns ('DEAL', 'T'));

OWNER                          TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ -------------------------
DEAL                           T                              OWNER
DEAL                           T                              OBJECT_TYPE
DEAL                           T                              TEMPORARY
DEAL                           T                              SECONDARY
DEAL                           T                              OBJECT_NAME
DEAL                           T                              NAMESPACE
DEAL                           T                              OBJECT_ID
DEAL                           T                              DATA_OBJECT_ID
DEAL                           T                              LAST_DDL_TIME
DEAL                           T                              SUBOBJECT_NAME
DEAL                           T                              CREATED
DEAL                           T                              TIMESTAMP
DEAL                           T                              STATUS
DEAL                           T                              GENERATED
DEAL                           T                              EDITION_NAME

15 rows selected.

GGSCI > info trandata deal.t

2015-11-06 20:12:13  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema DEAL.

2015-11-06 20:12:13  INFO    OGG-01980  Schema level supplemental logging is enabled on schema DEAL for all scheduling columns.

Logging of supplemental redo log data is enabled for table DEAL.T.

Columns supplementally logged for table DEAL.T: CREATED, DATA_OBJECT_ID, EDITION_NAME, GENERATED, LAST_DDL_TIME, NAMESPACE, OBJECT_ID, OBJECT_NAME, OBJECT_TYPE, OWNER, SECONDARY, STATUS, SUBOBJECT_NAME, TEMPORARY, TIMESTAMP.


SQL>ALTER TABLE DEAL.T  ENABLE  CONSTRAINT tpk USING INDEX;

Table altered.

SQL>SELECT * FROM TABLE (logmnr$always_suplog_columns ('DEAL', 'T'));

OWNER                          TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ -------------------------
DEAL                           T                              OBJECT_ID

GGSCI > info trandata deal.t

2015-11-06 20:12:39  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema DEAL.

2015-11-06 20:12:39  INFO    OGG-01980  Schema level supplemental logging is enabled on schema DEAL for all scheduling columns.

Logging of supplemental redo log data is enabled for table DEAL.T.

Columns supplementally logged for table DEAL.T: OBJECT_ID.
Advertisements

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

Create a free website or blog at WordPress.com.

%d bloggers like this: