Interesting change in ALTER EXTRACT command

Today, while helping a customer, we had to rebuild an extract.  The integrated extract that we rebuilt was stuck in a loop and displaying it was an initial load extract from the HTML5 page (AdminService).  The adminclient (cmd line), said it was working fine.  After the customer rebooted the their GoldenGate Service (GGS) environment, the integrated extract was still having problems.  

At this point, we executed an INFO EXTRACT <extract>, DETAIL and retrieved the sequence number (EXTSEQ) and the relative byte address (EXTRBA).  With this information in hand, we removed the extract and the associated parameter file.  After all, we were going to rebuilt the extract.

The customer the rebuilt the extract using Microsoft VSCode with the RESTful plug-in (makes it really easy and scriptable).  With the integrated extract rebuilt, we attempted to ALTER EXTRACT from the admin service, but there is no options (image 1).

Image 1: alter extract no seqno/rba option

This lead us to look at altering the extract from AdminClient within GGS.  The alter extract command that we use was:

adminclient> alter extract <extract>, extseq <num>, extrba <num>

This command caused a syntax error. For anyone and myself, doing GoldenGate for better part of 15 plus years, this was odd.  So, we went and looked up the documentation on ALTER EXTRACT in 19c (here).  The command syntax for ALTER EXTRACT for Admin Client is clearly the same as any veteran to Oracle GoldenGate would remember.

Admin Client Syntax (19c):

ALTER EXTRACT group-name
[, BEGIN (NOW | yyyy-mm-dd[ hh:mi:[ss[.cccccc]]]} |
EXTSEQNO sequence-number 
[, EXTRBA archive-offset-number] [, ADD_EXTRACT_attribute] | 
SCN value]
[, DESC
[, UPGRADE INTEGRATED TRANLOG]
[, DOWNGRADE INTEGRATED TRANLOG [THREADS number]]
[, THREAD number]

[, ETROLLOVER]
[, ENCRYPTIONPROFILE encryption-profile-name ]
[CRITICAL [ YES | NO ]
[PROFILE profile-name 
| [AUTOSTART [ YES | NO ]
[DELAY delay-number]
[AUTORESTART [ YES | NO ]| 
[RETRIES retries-number ]|
[WAITSECONDS wait-number ]|
[RESETSECONDS reset-number ]|
[DISABLEONFAILURE [ YES | NO ] ] ]
]

Then we realized or remembered that we were using GoldenGate Service (GGS) and there might have been a few things different from the 19c release to the 21c release. After all, Oracle GoldenGate Service (GGS) is running on 21c.  This prompted me to look at the 21c docs and I was sure it didn’t change; users needed a way to position an extract after rebuilding.  The documentation for 21c (here) provide what the syntax is for ALTER EXTRACT in 21c – there are minor differences:

Admin Client Syntax (21c):

ALTER EXTRACT group-name
[, BEGIN {NOW | yyyy-mm-dd[ hh:mi:[ss[.cccccc]]]} |
[, EXTRBA archive-offset-number] [, ADD_EXTRACT_attribute] | 
SCN value]
[, DESC
[, THREAD number]
[, ETROLLOVER]
[, ENCRYPTIONPROFILE encryption-profile-name ]
[CRITICAL [ YES | NO ]
[PROFILE profile-name 
| [AUTOSTART [ YES | NO ]
[DELAY delay-number]
[AUTORESTART [ YES | NO ]| 
[RETRIES retries-number ]|
[WAITSECONDS wait-number ]|
[RESETSECONDS reset-number ]|
[DISABLEONFAILURE [ YES | NO ] ] ]
]
[, LOGNUM lognum]
[, LOGPOS logpos]

What this meant for the customer, is that the extract could be rebuilt; however, to find the correct position to start the extract from we needed to know how to get the correct System Change Number (SCN) or the correct Relative Byte Address (RBA).  

In discussions with the customer, we decided that it was best to use a known System Change Number (SCN).  With the information we had, the customer knew we could go back an hour.  From here, we used an old post which I wrote in 2014 on how to convert a timestamp to SCN (here).  After the retrieving the SCN, we start the rebuilt extract as follows:

adminclient> alter extract <extract>, scn <scn>

The extract started successfully, remained on the correct trail file (we were over 660ish files) and captured data as expected.

Lesson learned here was, between versions Oracle likes to change things and we need to keep up.  At the same time, we wish subtle differences like this do not get over looked in the release notes.

Please follow and like: