Vishal desai’s Oracle Blog

August 21, 2017

GoldenGate find size of large transaction from trail using logdump

Filed under: Golden Gate, Oracle Database — vishaldesai @ 7:12 pm

There are multiple ways to find size of large transaction but if you don’t have access to source database below is one way to find it using golden gate logdump utility.

 

	1. Using rba find end of transaction
	
	open ./dirdat/DB/GM000000234
	pos 51525307 <= start of large transaction
	usertoken on
	ggstoken detail
	detail on
	detail data
	Sfet
	
	End of Transaction found at RBA 103803427 
	
	2017/05/11 08:31:44.002.060 GGSUnifiedUpdate     Len    60 RBA 103803427 
	Name: OWNER.TABLE_NAME  (TDR Index: 10) 
	After  Image:                                             Partition 12   G  e   
	 0000 001c 0000 000a 0000 0000 0000 000f 8d74 0020 | .................t.   
	 000a ffff 0000 0000 0000 0000 0000 000a 0000 0000 | ....................  
	 0000 000f 8d74 0020 000a 0000 0000 0000 000f 8d74 | .....t. ...........t  
	Before Image          Len    32 (x00000020) 
	BeforeColumnLen     28 (x0000001c) 
	Column     0 (x0000), Len    10 (x000a)  
	 0000 0000 0000 000f 8d74                          | .........t  
	Column    32 (x0020), Len    10 (x000a)  
	 ffff 0000 0000 0000 0000                          | ..........  
	
	After Image           Len    28 (x0000001c) 
	Column     0 (x0000), Len    10 (x000a)  
	 0000 0000 0000 000f 8d74                          | .........t  
	Column    32 (x0020), Len    10 (x000a)  
	 0000 0000 0000 000f 8d74                          | .........t  
	  
	GGS tokens: 
	TokenID x52 'R' ORAROWID         Info x00  Length   20 
	 4141 416c 326c 4141 5441 4143 4650 2f41 4168 0001 | AAAl2lAATAACFP/AAh..  
	
	
	Make note of rba for end of large transaction
	
	2. Find number of rows in large transaction
	
	ghdr on
	ggstoken detail           
	--log to step1.txt           
	open ./dirdat/DB/GM000000234       
	pos 103803427   <= end of large transaction                   
	pos rev                      
	filter inc transind 0    
	n                                                        
	exit
	  
	Logdump 449 >Logdump 450 >Logdump 450 >Scanned     10000 records, RBA  102141625, 2017/05/11 08:31:44.002.030 
	Scanned     20000 records, RBA  100483423, 2017/05/11 08:31:44.004.204 
	Scanned     30000 records, RBA   98807289, 2017/05/11 08:31:44.003.072 
	Scanned     40000 records, RBA   97130025, 2017/05/11 08:31:44.004.990 
	Scanned     50000 records, RBA   95471275, 2017/05/11 08:31:44.012.356 
	Scanned     60000 records, RBA   93831447, 2017/05/11 08:31:44.018.615 
	Scanned     70000 records, RBA   92205107, 2017/05/11 08:31:44.012.011 
	Scanned     80000 records, RBA   90608113, 2017/05/11 08:31:44.012.912 
	Scanned     90000 records, RBA   88991547, 2017/05/11 08:31:44.013.783 
	Scanned    100000 records, RBA   87350643, 2017/05/11 08:31:44.014.443 
	Scanned    110000 records, RBA   85711735, 2017/05/11 08:31:44.011.865 
	Scanned    120000 records, RBA   84033389, 2017/05/11 08:31:44.014.733 
	Scanned    130000 records, RBA   82418681, 2017/05/11 08:31:44.014.494 
	Scanned    140000 records, RBA   80777203, 2017/05/11 08:31:44.014.346 
	Scanned    150000 records, RBA   79254179, 2017/05/11 08:31:44.016.142 
	Scanned    160000 records, RBA   77650931, 2017/05/11 08:31:44.024.699 
	Scanned    170000 records, RBA   76089431, 2017/05/11 08:31:43.999.983 
	Scanned    180000 records, RBA   74537491, 2017/05/11 08:31:44.012.628 
	Scanned    190000 records, RBA   73113183, 2017/05/11 08:31:44.014.765 
	Scanned    200000 records, RBA   71648821, 2017/05/11 08:31:44.014.572 
	Scanned    210000 records, RBA   70097055, 2017/05/11 08:31:44.016.426 
	Scanned    220000 records, RBA   68512477, 2017/05/11 08:31:44.013.233 
	Scanned    230000 records, RBA   66878817, 2017/05/11 08:31:44.011.231 
	Scanned    240000 records, RBA   65284733, 2017/05/11 08:31:44.016.270 
	Scanned    250000 records, RBA   63637763, 2017/05/11 08:31:44.013.952 
	Scanned    260000 records, RBA   62021021, 2017/05/11 08:31:44.011.614 
	Scanned    270000 records, RBA   60335507, 2017/05/11 08:31:44.004.826 
	Scanned    280000 records, RBA   58605025, 2017/05/11 08:31:44.006.868 
	Scanned    290000 records, RBA   56875905, 2017/05/11 08:31:44.004.518 
	Scanned    300000 records, RBA   55180519, 2017/05/11 08:31:44.003.255 
	Scanned    310000 records, RBA   53441671, 2017/05/11 08:31:43.999.957 
	Scanned    320000 records, RBA   51811087, 2017/05/11 08:31:43.999.864 
	___________________________________________________________________ 
	Hdr-Ind    :     E  (x45)     Partition  :     .  (x0c)  
	UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)  
	RecLength  :    60  (x003c)   IO Time    : 2017/05/11 08:31:43.999.864   
	IOType     :   134  (x86)     OrigNode   :   255  (xff) 
	TransInd   :     .  (x00)     FormatType :     R  (x52) 
	SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
	AuditRBA   :      15946       AuditPos   : 12222992 
	Continued  :     N  (x00)     RecCount   :     1  (x01) 
	
	2017/05/11 08:31:43.999.864 GGSUnifiedUpdate     Len    60 RBA 51525307 
	Name: OWNER.TABLE_NAME  (TDR Index: 10) 
	After  Image:                                             Partition 12   G  b   
	 0000 001c 0000 000a 0000 0000 0000 0014 8fa0 0020 | ...................   
	 000a ffff 0000 0000 0000 0000 0000 000a 0000 0000 | ....................  
	 0000 0014 8fa0 0020 000a 0000 0000 0000 0014 8fa0 | ....... ............  
	  
	GGS tokens: 
	TokenID x52 'R' ORAROWID         Info x00  Length   20 
	 4141 416c 326c 4141 4b41 4141 5839 4c41 4141 0001 | AAAl2lAAKAAAX9LAAA..  
	TokenID x4c 'L' LOGCSN           Info x00  Length   13 
	 3932 3834 3133 3937 3230 3334 33                  | 9284139720343  
	TokenID x36 '6' TRANID           Info x00  Length   11 
	 3136 2e34 2e31 3130 3730 35                       | 16.4.110705  
	TokenID x69 'i' ORATHREADID      Info x01  Length    2 
	 0002                                              | ..  
	   
	
	Filtering suppressed 322014 records 
	
	Filtering suppressed 322014 records
	
	3. Were there multiple tables modified in one large transaction?
	
	In step 1, make note of table name
	
	ghdr on
	ggstoken detail           
	open ./dirdat/DB/GM000000234       
	pos 103803427 <= end of large transaction           
	filter exclude filename OWNER.TABLE_NAME  
	pos rev         
	n  
	
	Logdump 457 >Scanned     10000 records, RBA  102141625, 2017/05/11 08:31:44.002.030 
	Scanned     20000 records, RBA  100483423, 2017/05/11 08:31:44.004.204 
	Scanned     30000 records, RBA   98807289, 2017/05/11 08:31:44.003.072 
	Scanned     40000 records, RBA   97130025, 2017/05/11 08:31:44.004.990 
	Scanned     50000 records, RBA   95471275, 2017/05/11 08:31:44.012.356 
	Scanned     60000 records, RBA   93831447, 2017/05/11 08:31:44.018.615 
	Scanned     70000 records, RBA   92205107, 2017/05/11 08:31:44.012.011 
	Scanned     80000 records, RBA   90608113, 2017/05/11 08:31:44.012.912 
	Scanned     90000 records, RBA   88991547, 2017/05/11 08:31:44.013.783 
	Scanned    100000 records, RBA   87350643, 2017/05/11 08:31:44.014.443 
	Scanned    110000 records, RBA   85711735, 2017/05/11 08:31:44.011.865 
	Scanned    120000 records, RBA   84033389, 2017/05/11 08:31:44.014.733 
	Scanned    130000 records, RBA   82418681, 2017/05/11 08:31:44.014.494 
	Scanned    140000 records, RBA   80777203, 2017/05/11 08:31:44.014.346 
	Scanned    150000 records, RBA   79254179, 2017/05/11 08:31:44.016.142 
	Scanned    160000 records, RBA   77650931, 2017/05/11 08:31:44.024.699 
	Scanned    170000 records, RBA   76089431, 2017/05/11 08:31:43.999.983 
	Scanned    180000 records, RBA   74537491, 2017/05/11 08:31:44.012.628 
	Scanned    190000 records, RBA   73113183, 2017/05/11 08:31:44.014.765 
	Scanned    200000 records, RBA   71648821, 2017/05/11 08:31:44.014.572 
	Scanned    210000 records, RBA   70097055, 2017/05/11 08:31:44.016.426 
	Scanned    220000 records, RBA   68512477, 2017/05/11 08:31:44.013.233 
	Scanned    230000 records, RBA   66878817, 2017/05/11 08:31:44.011.231 
	Scanned    240000 records, RBA   65284733, 2017/05/11 08:31:44.016.270 
	Scanned    250000 records, RBA   63637763, 2017/05/11 08:31:44.013.952 
	Scanned    260000 records, RBA   62021021, 2017/05/11 08:31:44.011.614 
	Scanned    270000 records, RBA   60335507, 2017/05/11 08:31:44.004.826 
	Scanned    280000 records, RBA   58605025, 2017/05/11 08:31:44.006.868 
	Scanned    290000 records, RBA   56875905, 2017/05/11 08:31:44.004.518 
	Scanned    300000 records, RBA   55180519, 2017/05/11 08:31:44.003.255 
	Scanned    310000 records, RBA   53441671, 2017/05/11 08:31:43.999.957 
	Scanned    320000 records, RBA   51811087, 2017/05/11 08:31:43.999.864 
	___________________________________________________________________ 
	Hdr-Ind    :     E  (x45)     Partition  :     .  (x0c)  
	UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)  
	RecLength  :   162  (x00a2)   IO Time    : 2017/05/11 08:31:34.999.737   
	IOType     :   134  (x86)     OrigNode   :   255  (xff) 
	TransInd   :     .  (x03)     FormatType :     R  (x52) 
	SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
	AuditRBA   :      15943       AuditPos   : 8348688 
	Continued  :     N  (x00)     RecCount   :     1  (x01) 
	
	2017/05/11 08:31:34.999.737 GGSUnifiedUpdate     Len   162 RBA 51525028 
	Name: GEM2.HEART_BEAT  (TDR Index: 11) 
	After  Image:                                             Partition 12   G  s   
	 0000 004f 0000 0005 0000 0001 3100 0100 1f00 0032 | ...O........1......2  
	 3031 372d 3035 2d31 313a 3130 3a33 303a 3335 2e37 | 017-05-11:10:30:35.7  
	 3235 3030 3330 3030 0002 001f 0000 3230 3137 2d30 | 25003000......2017-0  
	 352d 3131 3a31 303a 3330 3a33 352e 3732 3530 3033 | 5-11:10:30:35.725003  
	 3030 3000 0000 0500 0000 0131 0001 001f 0000 3230 | 000........1......20  
	 3137 2d30 352d 3131 3a31 303a 3331 3a33 352e 3739 | 17-05-11:10:31:35.79  
	 3035 3638 3030 3000 0200 1f00 0032 3031 372d 3035 | 0568000......2017-05  
	  
	GGS tokens: 
	TokenID x52 'R' ORAROWID         Info x00  Length   20 
	 4141 416e 3238 4141 4b41 4141 592b 2f41 4141 0001 | AAAn28AAKAAAY+/AAA..  
	TokenID x4c 'L' LOGCSN           Info x00  Length   13 
	 3932 3834 3133 3937 3230 3235 31                  | 9284139720251  
	TokenID x36 '6' TRANID           Info x00  Length   10 
	 3338 2e37 2e33 3833 3330                          | 38.7.38330  
	TokenID x69 'i' ORATHREADID      Info x01  Length    2 
	 0004                                              | ..  
	   
	
	Filtering suppressed 322015 records 
	 
	
	Rba returned by above should be less than start of large transaction.
	
	Further verify that rba is start of large transaction using rba returned by above step 3.
	
	open ./dirdat/DB/GM000000234
	pos <rba from step 3>
	usertoken on
	ggstoken detail
	detail on
	detail data
	n
	
	
	2017/05/11 08:31:34.999.737 GGSUnifiedUpdate     Len   162 RBA 51525028 
	Name: GEM2.HEART_BEAT  (TDR Index: 11) 
	After  Image:                                             Partition 12   G  s   
	 0000 004f 0000 0005 0000 0001 3100 0100 1f00 0032 | ...O........1......2  
	 3031 372d 3035 2d31 313a 3130 3a33 303a 3335 2e37 | 017-05-11:10:30:35.7  
	 3235 3030 3330 3030 0002 001f 0000 3230 3137 2d30 | 25003000......2017-0  
	 352d 3131 3a31 303a 3330 3a33 352e 3732 3530 3033 | 5-11:10:30:35.725003  
	 3030 3000 0000 0500 0000 0131 0001 001f 0000 3230 | 000........1......20  
	 3137 2d30 352d 3131 3a31 303a 3331 3a33 352e 3739 | 17-05-11:10:31:35.79  
	 3035 3638 3030 3000 0200 1f00 0032 3031 372d 3035 | 0568000......2017-05  
	Before Image          Len    83 (x00000053) 
	BeforeColumnLen     79 (x0000004f) 
	Column     0 (x0000), Len     5 (x0005)  
	 0000 0001 31                                      | ....1  
	Column     1 (x0001), Len    31 (x001f)  
	 0000 3230 3137 2d30 352d 3131 3a31 303a 3330 3a33 | ..2017-05-11:10:30:3  
	 352e 3732 3530 3033 3030 30                       | 5.725003000  
	Column     2 (x0002), Len    31 (x001f)  
	 0000 3230 3137 2d30 352d 3131 3a31 303a 3330 3a33 | ..2017-05-11:10:30:3  
	 352e 3732 3530 3033 3030 30                       | 5.725003000  
	
	After Image           Len    79 (x0000004f) 
	Column     0 (x0000), Len     5 (x0005)  
	 0000 0001 31                                      | ....1  
	Column     1 (x0001), Len    31 (x001f)  
	 0000 3230 3137 2d30 352d 3131 3a31 303a 3331 3a33 | ..2017-05-11:10:31:3  
	 352e 3739 3035 3638 3030 30                       | 5.790568000  
	Column     2 (x0002), Len    31 (x001f)  
	 0000 3230 3137 2d30 352d 3131 3a31 303a 3331 3a33 | ..2017-05-11:10:31:3  
	 352e 3739 3035 3638 3030 30                       | 5.790568000  
	  
	GGS tokens: 
	TokenID x52 'R' ORAROWID         Info x00  Length   20 
	 4141 416e 3238 4141 4b41 4141 592b 2f41 4141 0001 | AAAn28AAKAAAY+/AAA..  
	TokenID x4c 'L' LOGCSN           Info x00  Length   13 
	 3932 3834 3133 3937 3230 3235 31                  | 9284139720251  
	TokenID x36 '6' TRANID           Info x00  Length   10 
	 3338 2e37 2e33 3833 3330                          | 38.7.38330  
	TokenID x69 'i' ORATHREADID      Info x01  Length    2 
	 0004                                              | ..  
	   
	Logdump 465 >n
	
	2017/05/11 08:31:43.999.864 GGSUnifiedUpdate     Len    60 RBA 51525307 
	Name: OWNER.TABLE_NAME  (TDR Index: 10) 
	After  Image:                                             Partition 12   G  b   
	 0000 001c 0000 000a 0000 0000 0000 0014 8fa0 0020 | ...................   
	 000a ffff 0000 0000 0000 0000 0000 000a 0000 0000 | ....................  
	 0000 0014 8fa0 0020 000a 0000 0000 0000 0014 8fa0 | ....... ............  
	Before Image          Len    32 (x00000020) 
	BeforeColumnLen     28 (x0000001c) 
	Column     0 (x0000), Len    10 (x000a)  
	 0000 0000 0000 0014 8fa0                          | ..........  
	Column    32 (x0020), Len    10 (x000a)  
	 ffff 0000 0000 0000 0000                          | ..........  
	
	After Image           Len    28 (x0000001c) 
	Column     0 (x0000), Len    10 (x000a)  
	 0000 0000 0000 0014 8fa0                          | ..........  
	Column    32 (x0020), Len    10 (x000a)  
	 0000 0000 0000 0014 8fa0                          | ..........  
	  
	GGS tokens: 
	TokenID x52 'R' ORAROWID         Info x00  Length   20 
	 4141 416c 326c 4141 4b41 4141 5839 4c41 4141 0001 | AAAl2lAAKAAAX9LAAA..  
	TokenID x4c 'L' LOGCSN           Info x00  Length   13 
	 3932 3834 3133 3937 3230 3334 33                  | 9284139720343  
	TokenID x36 '6' TRANID           Info x00  Length   11 
	 3136 2e34 2e31 3130 3730 35                       | 16.4.110705  
	TokenID x69 'i' ORATHREADID      Info x01  Length    2 
	 0002                                              | ..  
Advertisements

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.

January 10, 2014

Golden Gate conditional processing using UPDATEDELETES, TOKENS and FILTER

Filed under: Golden Gate, Replication — vishaldesai @ 4:01 pm

Client had two databases (source and target) and wanted to replicate data as follows:

All transactions generated by OLTP user needs to be replicated (as is) in near real time to target database. ARCHIVER user runs batch operations and deletes data from source database but on Target database it should convert ARCHIVER deletes into updates and there should be column indicator on target database to identify records that were deleted by ARCHIVER user on source database. Eventually these records will be deleted from Target database once its consumed by queue processor.

Test Case:

For demo purpose my Target database is same as Source database.

Source Database Target Database
APPSCHEMA – stores application tables APPSCHEMA – stores application tables
OLTP – web user  
ARCHIVER – user for archiving old data using batch operations  

Scripts to setup demo:

Database schema and table setup:
================================

DROP USER oltp cascade;
DROP USER archiver cascade;
DROP USER appschema cascade;
DROP TABLE appschema.test;
DROP TABLE appschema.test1;

CREATE USER oltp IDENTIFIED BY oltp DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
CREATE USER archiver IDENTIFIED BY archiver DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
CREATE USER appschema IDENTIFIED BY appschema DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT CREATE session, RESOURCE, DBA TO oltp,archiver,appschema;
  
CREATE TABLE appschema.test
(
   id      NUMBER PRIMARY KEY,
   name    VARCHAR2(20),
   company VARCHAR2(20)
);

  
CREATE TABLE appschema.test1
(
   id          NUMBER PRIMARY KEY,
   name        VARCHAR2(20),
   company     VARCHAR2(20),
   deleted_row VARCHAR(20)
);

Source:
=======

--Add Extract
dblogin  userid ggadmin@DBTNS, password pass1234
ADD EXTRACT e_test, TRANLOG, BEGIN NOW
ADD EXTTRAIL ./dirdat/et, EXTRACT e_test

--Extract parameter file
edit params e_test

EXTRACT e_test
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_HOME = "/opt/oracle/app/11.2.0.3")
SETENV (ORACLE_SID = "DBTNS")
DBOPTIONS
USERID ggadmin, PASSWORD pass1234
EXTTRAIL ./dirdat/et
TABLE appschema.test,TOKEN(TK_DBUSER = @GETENV ("TRANSACTION" , "USERNAME"));

--Generate definition file
edit params defgen1

defsfile /opt/oracle/app/gghome/dirdef/test.def
USERID ggadmin@DBTNS, PASSWORD pass1234
table appschema.test;

defgen paramfile /opt/oracle/app/gghome/dirprm/defgen1.prm

Copy /opt/oracle/app/gghome/dirdef/test.def to target server.

Target:
=======

--Add Replicat
dblogin  userid ggadmin@DBTNS, password pass1234
ADD CHECKPOINTTABLE ggadmin.CHKPTAB
ADD REPLICAT r_test, EXTTRAIL ./dirdat/et CHECKPOINTTABLE ggadmin.CHKPTAB

--Replicat parameter file
edit params r_test

REPLICAT r_test
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_HOME = "/opt/oracle/app/11.2.0.3")
SETENV (ORACLE_SID = "DBTNS")
SOURCEDEFS /opt/oracle/app/gghome/dirdef/test.def
USERID ggadmin, PASSWORD pass1234
ALLOWDUPTARGETMAP
GETINSERTS
GETUPDATES
GETDELETES
MAP appschema.test, TARGET appschema.test1, COLMAP (USEDEFAULTS, deleted_row = " "), FILTER ( @STRFIND(@TOKEN("TK_DBUSER"),"OLTP") > 0);
IGNOREINSERTS
IGNOREUPDATES
GETDELETES
UPDATEDELETES
MAP appschema.test, TARGET appschema.test1, COLMAP (USEDEFAULTS, deleted_row = "ARCHIVER"), FILTER ( @STRFIND(@TOKEN("TK_DBUSER"),"ARCHIVER") > 0);

Basically we are telling Golden Gate to replicate transactions as is when TOKEN value has OLTP stored in it and convert deletes into updates when TOKEN value has ARCHIVER stored in it.

Testing Transactions:

-- Populate Data and verify INSERTS are replicated to Target

SQL> conn oltp/oltp
Connected.
SQL> insert into appschema.test values (20,'VISHAL','ORA');

1 row created.

SQL> insert into appschema.test values (21,'ERIC','ORA');

1 row created.

SQL> insert into appschema.test values (22,'PHIL','ORA');

1 row created.

SQL> insert into appschema.test values (23,'GARY','ORA');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from appschema.test;

        ID NAME                 COMPANY
---------- -------------------- --------------------
        20 VISHAL               ORA
        21 ERIC                 ORA
        22 PHIL                 ORA
        23 GARY                 ORA

SQL> select * from appschema.test1;

        ID NAME                 COMPANY              DELETED_ROW
---------- -------------------- -------------------- --------------------
        20 VISHAL               ORA
        21 ERIC                 ORA
        22 PHIL                 ORA
        23 GARY                 ORA

-- Run DELETE as OLTP user

SQL> conn oltp/oltp
Connected.
SQL> delete from appschema.test where id=20;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from appschema.test  order by id;

        ID NAME                 COMPANY
---------- -------------------- --------------------
        21 ERIC                 ORA
        22 PHIL                 ORA
        23 GARY                 ORA

SQL> select * from appschema.test1 order by id;

        ID NAME                 COMPANY              DELETED_ROW
---------- -------------------- -------------------- --------------------
        21 ERIC                 ORA
        22 PHIL                 ORA
        23 GARY                 ORA

-- Run DELETE as ARCHIVER user

SQL> conn archiver/archiver
Connected.
SQL> delete from appschema.test where id=22;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from appschema.test order by id;

        ID NAME                 COMPANY
---------- -------------------- --------------------
        21 ERIC                 ORA
        23 GARY                 ORA

SQL> select * from appschema.test1 order by id;

        ID NAME                 COMPANY              DELETED_ROW
---------- -------------------- -------------------- --------------------
        21 ERIC                 ORA
        22 PHIL                 ORA                  ARCHIVER
        23 GARY                 ORA

That concludes the demo.

P.S. FILTER (@TOKEN("TK_DBUSER")="OLTP") did not work for some reason. Probably Token was storing blank padding’s. I will update this blog when I have time to verify that. 🙂

February 24, 2011

Golden Gate Bidirectional Replication Oracle to SQL Server example

Filed under: Golden Gate, Replication — vishaldesai @ 3:37 am

In this blog I will try to show how to setup golden gate heterogenous bidirectional replication (Oracle 11g <-> SQL Server 2008).

Software Setup

Virtual Box 1: OEL 6.0, Oracle 11g, Golden Gate 11, Oracle Instance oralin11g
Virtual Box 2: Windows 2008 Server, SQL Server 2008, Golden Gate 11, SQL Server Instance MSSQLSERVER

Pre-requisites

Install golden gate and create sub directories.
Create golden gate schema in Oracle Database and grant required permissions.
Turn on database into archive log mode and supplemental logging.

Note: Below configuration is not from production environment. In actual setup you have to consider lot of other things such as performance, conflict resolution, test insert/update/delete, testing actual application workflows etc.

Configuration

-SQL Server Table structure.

--Run following in SQL Server Management studio.

USE [HR]
GO

/****** Object:  Table [dbo].[emp1]    Script Date: 02/22/2011 16:27:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [hruser].[emp1](
    [id] [smallint] NOT NULL,
    [fname] varchar(50) NOT NULL,
    [lname] varchar(50) NOT NULL,
 CONSTRAINT [PK_emp1] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-- Oracle Table structure

SQL> create table vdesai.emp1
(id number not null,
fname varchar2(50),
lname varchar2(50)) tablespace users;

Table created.

– Create ODBC connection as per documentation (Reference e17805 Page 23). I have created ODBC connection named ggsqlds. Verify ODBC connection using golden gate.

GGSCI (WIN-NE9G39A65OR) 2> dblogin sourcedb ggsqlds , userid sa , password vishal
Successfully logged into database.

GGSCI (WIN-NE9G39A65OR) 3> add trandata hruser.emp1

Logging of supplemental log data is enabled for table hruser.emp1

– Generate defination files for SQL Server 2008 & Oracle.

GGSCI (WIN-NE9G39A65OR) 8> edit params defgen

defsfile C:\app\Administrator\ggssql\dirdef\emp1.def
sourcedb ggsqlds, userid sa, password vishal
table hruser.emp1;

C:\app\Administrator\ggssql>defgen paramfile C:\app\Administrator\ggssql\dirprm\defgen.prm

C:\app\Administrator\ggssql>defgen paramfile C:\app\Administrator\ggssql\dirprm\defgen.prm

***********************************************************************
         Oracle GoldenGate Table Definition Generator for ODBC
                     Version 11.1.1.0.0 Build 078
 Windows x64 (optimized), Microsoft SQL Server on Jul 28 2010 15:01:58

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

                    Starting at 2011-02-22 16:44:51
***********************************************************************

Operating System Version:
Microsoft Windows Server 2008 R2 , on x64
Version 6.1 (Build 7600: )

Process id: 1284

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile C:\app\Administrator\ggssql\dirdef\emp1.def
sourcedb ggsqlds, userid sa, password *********
table hruser.emp1;
Retrieving definition for HRUSER.EMP1

Definitions generated for 1 tables in C:\app\Administrator\ggssql\dirdef\emp1.def

GGSCI (linux6) 9> edit params defgen

defsfile /app/oracle/ggs/dirdef/emp1.def
USERID ggs_owner, PASSWORD vishal
table vdesai.emp1;

$ defgen paramfile /app/oracle/ggs/dirprm/defgen.prm

***********************************************************************
        Oracle GoldenGate Table Definition Generator for Oracle
                     Version 11.1.1.0.0 Build 078
   Linux, x64, 64bit (optimized), Oracle 11 on Jul 28 2010 13:15:49

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

                    Starting at 2011-02-23 19:54:24
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Wed Feb 2 18:40:23 EST 2011, Release 2.6.32-100.28.5.el6.x86_64
Node: linux6
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 2927

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile /app/oracle/ggs/dirdef/emp1.def
USERID ggs_owner, PASSWORD ******
table vdesai.emp1;
Retrieving definition for VDESAI.EMP1

2011-02-23 19:54:31  WARNING OGG-00869  No unique key is defined for table EMP1. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Definitions generated for 1 tables in /app/oracle/ggs/dirdef/emp1.def

– Copy linux emp1.def to windows c:\app\Administrator\ggssql\dirdef\emp1.def. Copy windows emp1.def to linux /app/oracle/ggs/dirdef/emp1.def. Make sure not to overwrite same file on source and target.

– For SQL Server 2008 set database option and take full backup

exec sp_dboption “HR”, “trunc. log on chkpt.”, “false”
Take full backup of database

– Setup Manager & Extract on Virtual box 2 (MSQSQLSERVER)

GGSCI (WIN-NE9G39A65OR) 3> edit param ./globals

MGRSERVNAME MGRSQLSERVER

GGSCI (WIN-NE9G39A65OR) 2> edit param mgr

PORT 7809

GGSCI (WIN-NE9G39A65OR) 9> ADD EXTRACT sqlext, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (WIN-NE9G39A65OR) 10> ADD EXTTRAIL C:\app\Administrator\ggssql\dirdat\lt,EXTRACT sqlext
EXTTRAIL added.

GGSCI (WIN-NE9G39A65OR) 11> ADD EXTRACT sqldpump, EXTTRAILSOURCE C:\app\Administrator\ggssql\dirdat\lt
EXTRACT added.

GGSCI (WIN-NE9G39A65OR) 12> edit params sqlext

EXTRACT sqlext
sourcedb ggsqlds, userid sa, password vishal
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
EXTTRAIL C:\app\Administrator\ggssql\dirdat\lt
TABLE hruser.emp1;

GGSCI (WIN-NE9G39A65OR) 13> ADD RMTTRAIL /app/oracle/ggs/dirdat/rt, EXTRACT sqldpump
RMTTRAIL added.

GGSCI (WIN-NE9G39A65OR) 14> EDIT PARAMS sqldpump

EXTRACT sqldpump
sourcedb ggsqlds, userid sa, password vishal
RMTHOST 192.168.56.101, MGRPORT 7809
RMTTRAIL /app/oracle/ggs/dirdat/rt
PASSTHRU
TABLE hruser.emp1;

– Setup Replicat on Virtual Box 1 (oralin11g)

GGSCI (linux6) 10> ADD REPLICAT orarep, EXTTRAIL /app/oracle/ggs/dirdat/rt
REPLICAT added.

GGSCI (linux6) 11> edit params orarep

REPLICAT orarep
sourcedefs /app/oracle/ggs/dirdef/emp1.def
USERID ggs_owner, PASSWORD vishal
MAP hruser.emp1, TARGET vdesai.emp1 ;

-Setup Extract on Virtual Box 1 (oralin11g)

GGSCI (linux6) 12> ADD EXTRACT oraext, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (linux6) 13> ADD EXTTRAIL /app/oracle/ggs/dirdat/lt, EXTRACT oraext
EXTTRAIL added.

GGSCI (linux6) 14> ADD EXTRACT oradpump, EXTTRAILSOURCE /app/oracle/ggs/dirdat/ltEXTRACT added.

GGSCI (linux6) 15> edit params oraext

EXTRACT oraext
USERID ggs_owner, PASSWORD vishal
TRANLOGOPTIONS EXCLUDEUSER ggs_owner
EXTTRAIL /app/oracle/ggs/dirdat/lt
TABLE vdesai.emp1 KEYCOLS ID;

GGSCI (linux6) 16> ADD RMTTRAIL C:\app\Administrator\ggssql\dirdat\rt, EXTRACT oradpump
RMTTRAIL added.

GGSCI (linux6) 17> EDIT PARAMS oradpump

EXTRACT oradpump
userid ggs_owner, password vishal
RMTHOST 192.168.56.103, MGRPORT 7809
RMTTRAIL C:\app\Administrator\ggssql\dirdat\rt
PASSTHRU
TABLE vdesai.emp1;

– Create checkpoint table on Virtual Box 2 (MSSQLSERVER) and setup Replicat.

GGSCI (WIN-NE9G39A65OR) 10> dblogin sourcedb ggsqlds, userid sa, password vishal
Successfully logged into database.

GGSCI (WIN-NE9G39A65OR) 11> ADD CHECKPOINTTABLE hruser.chkptab

Successfully created checkpoint table HRUSER.CHKPTAB.

GGSCI (WIN-NE9G39A65OR) 15> ADD REPLICAT sqlrep, EXTTRAIL C:\app\Administrator\ggssql\dirdat\rt CHECKPOINTTABLE HRUSER.CHKPTAB
REPLICAT added.

GGSCI (WIN-NE9G39A65OR) 16> edit params sqlrep

REPLICAT sqlrep
sourcedefs C:\app\Administrator\ggssql\dirdef\emp1.def
sourcedb ggsqlds, userid sa, password vishal
MAP vdesai.emp1, TARGET hruser.emp1;

– Start Extract, pump and Replicat processes.

GGSCI (WIN-NE9G39A65OR) 17> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     SQLDPUMP    00:00:00      00:06:38
EXTRACT     STOPPED     SQLEXT      00:00:00      00:07:10
REPLICAT    STOPPED     SQLREP      00:00:00      00:00:59

GGSCI (WIN-NE9G39A65OR) 18> start extract sqlext

Sending START request to MANAGER ('MGRSQLSERVER') ...
EXTRACT SQLEXT starting

GGSCI (WIN-NE9G39A65OR) 19> start extract sqldpump

Sending START request to MANAGER ('MGRSQLSERVER') ...
EXTRACT SQLDPUMP starting

GGSCI (WIN-NE9G39A65OR) 20> start replicat sqlrep

Sending START request to MANAGER ('MGRSQLSERVER') ...
REPLICAT SQLREP starting

GGSCI (WIN-NE9G39A65OR) 21> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     SQLDPUMP    00:00:00      00:07:02
EXTRACT     RUNNING     SQLEXT      00:00:00      00:00:05
REPLICAT    RUNNING     SQLREP      00:00:00      00:01:24

GGSCI (linux6) 21> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     ORADPUMP    00:00:00      00:03:59
EXTRACT     STOPPED     ORAEXT      00:00:00      00:04:22
REPLICAT    STOPPED     ORAREP      00:00:00      00:05:08    

GGSCI (linux6) 22> start extract oraext

Sending START request to MANAGER ...
EXTRACT ORAEXT starting

GGSCI (linux6) 23> start extract oradpump

Sending START request to MANAGER ...
EXTRACT ORADPUMP starting

GGSCI (linux6) 24> start replicat orarep

Sending START request to MANAGER ...
REPLICAT ORAREP starting

GGSCI (linux6) 25> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     ORADPUMP    00:00:00      00:04:15
EXTRACT     RUNNING     ORAEXT      00:00:00      00:00:01
REPLICAT    RUNNING     ORAREP      00:00:00      00:05:24    

– Testing

-- SQL Server

begin tran
insert into hruser.emp1 values (1,'Vishal','Desai')
commit tran

-- Oracle

SQL> select * from vdesai.emp1;

    ID FNAME        LNAME
---------- -------------------- --------------------
     1 Vishal        Desai

SQL> insert into vdesai.emp1 values (11,'John','Mako');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from vdesai.emp1;

    ID FNAME        LNAME
---------- -------------------- --------------------
    11 John         Mako
     1 Vishal        Desai

- SQL Server from SQL Management Studio.

select * from hruser.emp1

id    fname    lname
1    Vishal    Desai
11    John    Mako

Golden Gate Bidirectional Replication Oracle to Oracle example

Filed under: Golden Gate, Replication — vishaldesai @ 1:28 am

In this blog I will try to show how to setup golden gate homogeneous bidirectional replication (Oracle <-> Oracle).

Software Setup:

Virtual Box 1: OEL 6.0, Oracle 11g, Golden Gate 11, Oracle Instance oralin11g
Virtual Box 2: Windows 2008 Server, Oracle 11g, Golden Gate 11, Oracle Instance orawin11g

Pre-requisites:

Install golden gate and create sub directories.
Create golden gate schema in Oracle Database and grant required permissions.
Turn on database into archive log mode and supplemental logging.

Note: Below configuration is not from production environement. In actual setup you have to consider lot of other things such as performance, conflict resolution, test insert/update/delete, testing actual application workflows etc.

Configuration:

Setup Extract and dump trail on Virtual Box 1 (oralin11g):

GGSCI (linux6) 1> ADD EXTRACT ext1, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (linux6) 2> ADD EXTTRAIL /app/oracle/ggs/dirdat/lt, EXTRACT ext1
EXTTRAIL added.

GGSCI (linux6) 3> ADD EXTRACT dpump, EXTTRAILSOURCE /app/oracle/ggs/dirdat/lt
EXTRACT added.

GGSCI (linux6) 4> EDIT PARAMS ext1

EXTRACT ext1
USERID ggs_owner, PASSWORD vishal
TRANLOGOPTIONS EXCLUDEUSER ggs_owner
EXTTRAIL /app/oracle/ggs/dirdat/lt
TABLE vdesai.emp;

GGSCI (linux6) 5> ADD RMTTRAIL C:\app\oracle\ggsoracle\dirdat\rt, EXTRACT dpump
RMTTRAIL added.

GGSCI (linux6) 6> EDIT PARAMS dpump

EXTRACT dpump
USERID ggs_owner, PASSWORD vishal
RMTHOST 192.168.56.103, MGRPORT 7809
RMTTRAIL C:\app\oracle\ggsoracle\dirdat\rt
PASSTHRU
TABLE vdesai.emp;

Setup Replicat trail on Virtual Box 2 (orawin11g):

GGSCI (WIN-NE9G39A65OR) 1> ADD REPLICAT rep1, EXTTRAIL C:\app\oracle\ggsoracle\dirdat\rt CHECKPOINTTABLE GGS_OWNER.CHKPTAB
REPLICAT added.

GGSCI (WIN-NE9G39A65OR) 2> EDIT PARAMS rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD vishal
MAP vdesai.emp, TARGET vdesai.emp;

Setup Extract and dump trail on Virtual Box 2 (orawin11g)

GGSCI (WIN-NE9G39A65OR) 3> ADD EXTRACT ext2, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (WIN-NE9G39A65OR) 4> ADD EXTTRAIL C:\app\oracle\ggsoracle\dirdat\lt, EXTRACT ext2
EXTTRAIL added.

GGSCI (WIN-NE9G39A65OR) 5> ADD EXTRACT dpump, EXTTRAILSOURCE C:\app\oracle\ggsoracle\dirdat\lt
EXTRACT added.

GGSCI (WIN-NE9G39A65OR) 6> EDIT PARAMS ext2

EXTRACT ext2
USERID ggs_owner, PASSWORD vishal
TRANLOGOPTIONS EXCLUDEUSER ggs_owner
EXTTRAIL C:\app\oracle\ggsoracle\dirdat\lt
TABLE vdesai.emp;

GGSCI (WIN-NE9G39A65OR) 7> ADD RMTTRAIL /app/oracle/ggs/dirdat/rt, EXTRACT dpump

RMTTRAIL added.

GGSCI (WIN-NE9G39A65OR) 8> EDIT PARAMS dpump

EXTRACT dpump
USERID ggs_owner, PASSWORD vishal
RMTHOST 192.168.56.101, MGRPORT 7809
RMTTRAIL /app/oracle/ggs/dirdat/rt
PASSTHRU
TABLE vdesai.emp;

Setup Replicat trail on Virtual Box 1 (oralin11g):

GGSCI (linux6) 7> ADD REPLICAT rep2, EXTTRAIL /app/oracle/ggs/dirdat/rt CHECKPOINTTABLE GGS_OWNER.CHKPTAB
REPLICAT added.

GGSCI (linux6) 8> EDIT PARAMS rep2

REPLICAT rep2
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD vishal
MAP vdesai.emp, TARGET vdesai.emp;

Start Extract & Replicate processes on Virtual Box 1 (oralin11g):

GGSCI (linux6) 46> INFO ALL

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     DPUMP       00:00:00      00:00:47
EXTRACT     STOPPED     EXT1        00:00:00      00:00:49
REPLICAT    STOPPED     REP2        00:00:00      00:00:45    

GGSCI (linux6) 47> START EXTRACT ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting

GGSCI (linux6) 48> START EXTRACT dpump

Sending START request to MANAGER ...
EXTRACT DPUMP starting

GGSCI (linux6) 49> START REPLICAT rep2

Sending START request to MANAGER ...
REPLICAT REP2 starting

GGSCI (linux6) 50> INFO ALL

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP       00:00:00      00:00:02
EXTRACT     RUNNING     EXT1        00:00:00      00:00:08
REPLICAT    RUNNING     REP2        00:00:00      00:00:03    

Start Extract and Replicat processes on Virtual Box 2 (orawin11g):

GGSCI (WIN-NE9G39A65OR) 34> INFO ALL

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     DPUMP       00:00:00      00:01:38
EXTRACT     STOPPED     EXT2        00:00:00      00:01:40
REPLICAT    STOPPED     REP1        00:00:00      00:01:41

GGSCI (WIN-NE9G39A65OR) 35> START EXTRACT EXT2

Sending START request to MANAGER ...
EXTRACT EXT2 starting

GGSCI (WIN-NE9G39A65OR) 36> START EXTRACT dpump

Sending START request to MANAGER ...
EXTRACT DPUMP starting

GGSCI (WIN-NE9G39A65OR) 37> START REPLICAT rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (WIN-NE9G39A65OR) 38> INFO ALL

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP       00:00:00      00:00:04
EXTRACT     RUNNING     EXT2        00:00:00      00:00:07
REPLICAT    RUNNING     REP1        00:00:00      00:00:07

Testing:

-- Insert into orawin11g database

SQL> select * from vdesai.emp;

no rows selected

SQL> insert into vdesai.emp values (1,'Vishal','Desai');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from vdesai.emp;

    ID FNAME    LNAME
---------- ------------ ------------
     1 Vishal    Desai

-- Verify record on oralin11g database

SQL> select * from vdesai.emp;

        ID FNAME        LNAME
---------- ------------ ------------
         1 Vishal       Desai

-- Insert into oralin11g database

SQL> select * from vdesai.emp;

        ID FNAME        LNAME
---------- ------------ ------------
         1 Vishal       Desai

SQL>
SQL> insert into vdesai.emp values (2,'Bob','Peterson');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from vdesai.emp;

        ID FNAME        LNAME
---------- ------------ ------------
         2 Bob          Peterson
         1 Vishal       Desai

-- Verify record on orawin11g database

SQL> select * from vdesai.emp; 

    ID FNAME    LNAME
---------- ------------ ------------
     2 Bob        Peterson
     1 Vishal    Desai

Oracle Golden Gate

Filed under: Golden Gate, Replication — vishaldesai @ 12:44 am

Lot of people have already blogged about Oracle Golden Gate so in this blog I will just give pointers to those links.

High level overview of Oracle Golden Gate

http://sai-oracle.blogspot.com/2010/09/what-is-oracle-goldengate.html

Quick overview and demos to setup Golden Gate

http://gavinsoorma.com/2010/02/oracle-goldengate-an-introduction/ (To start with and there are various other tutorials on Gavin’s blog)

Troubleshooting

http://deciphercorp.wordpress.com/2010/08/05/using-oracle-goldengates-logdump-utility/

While testing golden gate I ran into lot of issues and logdump utility helped me a lot to read trail files and fix issues.

Performance & Exception Handler

http://www.oracle11ggotchas.com/articles/DefiningMultipleReplicatstoIncreaseGoldenGatePerformance.htm

http://www.oracle11ggotchas.com/articles/CreatingaGoldenGateExceptionHandlertotrapandlogOracleErrors.htm

Extract Internals

http://www.pythian.com/news/7225/oracle-goldengate-extract-internals-part-i/

http://www.pythian.com/news/7459/oracle-goldengate-extract-internals-part-ii/

http://www.pythian.com/news/7617/oracle-goldengate-extract-internals-part-iii/

Oracle Golden Gate by example

http://apex.oracle.com/pls/apex/f?p=44785:24:2096163966240098::::P24_CONTENT_ID,P24_PREV_PAGE:5153,24

Upcoming Golden Gate Books

https://www.packtpub.com/oracle-goldengate-11g-implementers-guide/book

http://apress.com/book/view/9781430235668

Important Metalink notes and Documentation

1194273.1 – How to de normalize data using Golden Gate

1099523.1 – TimesTen replication using Golden Gate

http://download.oracle.com/docs/cd/E18101_01/index.htm

Create a free website or blog at WordPress.com.