Steps to reposition an oracle goldengate extract to a previous SCN
Situation:
There may be a situation in your DBA career when we play also the role of GG admin, where the application team missed a table from replication list or
the extract process cant be started at all for some reason when the initial seed was taken.
GG administration says the extract should be kicked before the initial seed is taken, you can setup the replicat at a later stage.
Reason... the replicat can use the extract files to perform the data sync even starting late, all we need is disk space to secure the extract files for the duration
we arent kicking off the replicat.
But here, we arent even having extract setup. But then how we perform the replication?
We have our DB in Force Logging, Archivelog mode. We have all the archivelogs for the duration we need in disk and is accessible for the extract process to consume
and generate the extract files. So we are going to try using the same.
Initial SEED SCN: 459732
Steps Summary:
1. DB preperation
2. Add credentials
3. Setup Extract
4. Reposition the extract and start
Readings:
https://k21academy.com/oracle-goldengate-12c/goldengate-upgrade-classic-capture-to-integrated-capture/
https://docs.oracle.com/en/cloud/paas/goldengate-cloud/gwuad/restarting-primary-extract-system-failure-or-corruption.html
https://blogs.oracle.com/dataintegration/post/alter-integrated-extract-to-previous-scn
Actual Steps:
1. DB preperation for second setup of Golden Extract
Follow the below link for an idea on how to setup remote extract:
https://oracledbaplanner.blogspot.com/2023/08/oracle-goldengate-setup-for-remote.html
There are 2 major steps in DB preperation for EXTRACT.
A. DB preperation overall for the extracts
B. Extract specific preperation such as user account
Here in our procedure we will have a seperate user managing the second extract.
To reduce the complexities, I have kept Database Vault in disabled state until the second extract is kicked off.
Note I still grant all the Database Vault related permissions to ggadmin2 user.
CREATE TABLESPACE GG_DATA2 DATAFILE '/oradata/GGSRC04T/gg_data2_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M;
-- as dbv_owner user -- vault is disabled, so sys is good enough
CREATE USER ggadmin2 IDENTIFIED BY "ggadmin2" DEFAULT TABLESPACE GG_DATA2 TEMPORARY TABLESPACE TEMP;
grant connect to ggadmin2;
alter user ggadmin2 quota unlimited on GG_DATA2;
grant DV_GOLDENGATE_ADMIN to ggadmin2; -- this is needed otherwise OGG-08221 error will occur when you try start the GG extract
-- other permission as sys
GRANT ALTER SYSTEM TO ggadmin2;
GRANT DBA TO ggadmin2;
GRANT CREATE SESSION TO ggadmin2;
GRANT ALTER ANY TABLE TO ggadmin2;
GRANT RESOURCE TO ggadmin2;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin2');
Actual output:
2. Add credential for GG extract using cURL or admin client web UI (BUI)
adminclient:
alter credentialstore add user ggadmin@192.168.194.10:1521/ggsrc04t alias ggsrc04t PASSWORD ggadmin;
but it will give invalid usn/psw error.
> BUI based action is complete and tested as well.
3. Setup the GG extract using admin client from GG machine (remote extract)
credetnial creation -> DB login -> necessary table setup -> Add Extract -> Add Extract Trail file -> Register the db
export OGG_HOME=/u01/app/oracle/product/21.3.0/ogg_home_1
export PATH=$PATH:$OGG_HOME/bin
adminclient
connect http://127.0.0.1:9011 deployment oggdep01 user ggsca password ggsca
DBLOGIN USERIDALIAS ggsrc04t_2
ADD SCHEMATRANDATA encvault_test
ADD HEARTBEATTABLE
ADD CHECKPOINTTABLE ggadmin2.ggs_checkpoint
add extract rxt4, TRANLOG, begin NOW
-- now add param file
cp /oggdata/oggdep01/etc/conf/ogg/rxt2.prm /oggdata/oggdep01/etc/conf/ogg/rxt4.prm
-- update the below settings properly
EXTRACT rxt4
USERIDALIAS ggsrc04t DOMAIN OracleGoldenGate
EXTTRAIL src/r4
[oracle@vcentos79-oracle-sa1 ~]$ cat /oggdata/oggdep01/etc/conf/ogg/rxt4.prm
EXTRACT rxt4
USERIDALIAS ggsrc04t_2 DOMAIN OracleGoldenGate
EXTTRAIL src/r4
DDL INCLUDE MAPPED
TABLE encvault_test.*;
[oracle@vcentos79-oracle-sa1 ~]$
ADD EXTTRAIL src/r4 EXTRACT rxt4
info extract rxt4 detail
exit
adminclient
connect http://127.0.0.1:9011 deployment oggdep01 user ggsca password ggsca
DBLOGIN USERIDALIAS ggsrc04t_2
register extract rxt4 database
Actual output:
4. Start the GG extract after repositioning it
select name, thread#, sequence# from v$archived_log
where 459732 between first_change# and next_change#;
info extract rxt4 detail
ALTER EXTRACT rxt4 EXTSEQNO 14 >>>>>>>>>> not an option
ALTER EXTRACT rxt4 EXTRBA 0 >>>>
ALTER EXTRACT rxt4 ETROLLOVER
info extract rxt4 detail
START EXTRACT rxt4
Actual output:
Try again:
OK, but does repositioning really work?
Range of SCNs: 1088486 -> 1106087
Try reposition to: 1100000
So the reposition really works from the server's first scn not before. How we make the GG learn the first scn (old) is something to give it a try later.
YouTube Video:
No comments:
Post a Comment