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

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: