Oracle GoldenGate Microservices have a new approach when deleting an Integrated Extract process, it also executes UNREGISTER EXTRACT automatically from the database. But not in all situations, that’s why we need to have some caution.
# Oracle Database 12.2.0.1.0 (Non-CDB)
# Oracle GoldenGate Microservices 21.11.0.0.0
Let’s see how it works, starting with login into OGG Service Manager through adminclient:
$OGG_HOME/bin/adminclient
Oracle GoldenGate Administration Client for Oracle
Version 21.11.0.0.0 OGGCORE_21.11.0.0.0OGGRU_PLATFORMS_230714.2015
Copyright (C) 1995, 2023, Oracle and/or its affiliates. All rights reserved.
Oracle Linux 7, x64, 64bit (optimized) on Jul 14 2023 21:58:01
Operating system character set identified as UTF-8.
OGG (not connected) 1> connect http://<host>:<port> deployment dp_src_OGG1001 user oggadmin password welcome1
Add three new Extracts:
OGG> add extract e_ggbr01, tranlog, begin now
2024-05-08T17:20:11Z INFO OGG-08100 Integrated Extract added.
OGG> add extract e_ggbr02, tranlog, begin now
2024-05-08T17:20:11Z INFO OGG-08100 Integrated Extract added.
OGG> add extract e_ggbr03, tranlog, begin now
2024-05-08T17:20:11Z INFO OGG-08100 Integrated Extract added.
In addition, in OGG Microservices, even if I try to create a Classic Extract on the Oracle Database, it will automatically create an Integrated Extract, check the “Integrated Extract added” even using only TRANLOG option.
Then, setup the Extract parameters, to avoid the error “OGG-12029 The item type file with the name ‘E_xxx.prm’ does not exist.“, which is new in OGG Microservices, to check if the parameter file exists for each created process.
EXTRACT E_GGBR01
USERIDALIAS oggconnect_12c
EXTTRAIL g1
TABLE GGBR_TREINAMENTOS_SRC.DADOS_E;
EXTRACT E_GGBR02
USERIDALIAS oggconnect_12c
EXTTRAIL g2
TABLE GGBR_TREINAMENTOS_SRC.DADOS_E;
EXTRACT E_GGBR03
USERIDALIAS oggconnect_12c
EXTTRAIL g3
TABLE GGBR_TREINAMENTOS_SRC.DADOS_E;
OGG> info all
Program Status Group Type Lag at Chkpt Time Since Chkpt
ADMINSRVR RUNNING
DISTSRVR RUNNING
PMSRVR RUNNING
RECVSRVR RUNNING
EXTRACT STOPPED E_GGBR01 INTEGRATED 00:00:00 00:00:12
EXTRACT STOPPED E_GGBR02 INTEGRATED 00:00:00 00:00:32
EXTRACT STOPPED E_GGBR03 INTEGRATED 00:00:00 00:00:57
Now, let’s register these Extracts into Oracle Database:
OGG> dblogin useridalias oggconnect_12c
Successfully logged into database.
OGG (oggconnect_12c@GOLDEN12) > register extract e_ggbr01 database
2024-05-08T17:46:17Z INFO OGG-02003 Extract group E_GGBR01 successfully registered with database at SCN 1697573.
OGG (oggconnect_12c@GOLDEN12) > register extract e_ggbr02 database
2024-05-08T17:50:14Z INFO OGG-02003 Extract group E_GGBR02 successfully registered with database at SCN 1710204.
OGG (oggconnect_12c@GOLDEN12) > register extract e_ggbr03 database
2024-05-08T17:51:00Z INFO OGG-02003 Extract group E_GGBR03 successfully registered with database at SCN 1712401.
Next, after registering Extracts in the database, let’s check the database DBA_CAPTURE view, to confirm if registration is fine:
SQL> select capture_name, status from dba_capture;
CAPTURE_NAME STATUS
--------------------- ---------------
OGG$CAP_E_GGBR01 ENABLED
OGG$CAP_E_GGBR02 ENABLED
OGG$CAP_E_GGBR03 ENABLED
All good! Great! Now, let’s try three different examples to remove the Integrated Extract process registered in the database.
# 01. Delete Integrated Extract ( after execute dblogin )
OGG (oggconnect_12c@GOLDEN12) > delete extract e_ggbr01
2024-05-08T17:59:33Z INFO OGG-01750 Successfully unregistered Extract group E_GGBR01 from database.
2024-05-08T17:59:33Z INFO OGG-08100 Extract group E_GGBR01 deleted.
OGG> info all
Program Status Group Type Lag at Chkpt Time Since Chkpt
ADMINSRVR RUNNING
DISTSRVR RUNNING
PMSRVR RUNNING
RECVSRVR RUNNING
EXTRACT STOPPED E_GGBR02 INTEGRATED 00:00:00 00:01:28
EXTRACT STOPPED E_GGBR03 INTEGRATED 00:00:00 00:02:01
Please note that before the deletion of the extract, it was automatically unregistered from the database. Additionally, to confirm, kindly check DBA_CAPTURE from the database side.
SQL> select capture_name, status from dba_capture;
CAPTURE_NAME STATUS
--------------------- ---------------
OGG$CAP_E_GGBR02 ENABLED
OGG$CAP_E_GGBR03 ENABLED
Integrated extract E_GGBR01 is not there.
Now, let’s try next example:
# 2. Delete Integrated Extract – without dblogin (exit an reconnect to the Service Manager)
OGG> delete extract E_GGBR02
2024-05-08T18:06:33Z INFO OGG-01750 Successfully unregistered Extract group E_GGBR02 from database.
2024-05-08T18:06:33Z INFO OGG-08100 Extract group E_GGBR02 deleted.
OGG> info all
Program Status Group Type Lag at Chkpt Time Since Chkpt
ADMINSRVR RUNNING
DISTSRVR RUNNING
PMSRVR RUNNING
RECVSRVR RUNNING
EXTRACT STOPPED E_GGBR03 INTEGRATED 00:00:00 00:03:16
Same behaviour, before deleting the Extract, it was automatically unregistered from the database.
Please double-check the database DBA_CAPTURE for confirmation.”
SQL> select capture_name, status from dba_capture;
CAPTURE_NAME STATUS
--------------------- ---------------
OGG$CAP_E_GGBR03 ENABLED
# 3. Delete Integrated Extract – after executing dblogin, but having some issues connecting to the database when the command is issued.
To simulate this scenario, I intentionally create a database issue, making it impossible to connect to the database. There are various methods to achieve this; for this demonstration, I’ve changed the db_recovery_file_dest_size to 1M to force an archiver error.
SQL> alter system set db_recovery_file_dest_size=1m;
System altered.
SQL> alter system switch logfile; <<< run until db freeze
If we perform dblogin once again, the following archive error will happen:
OGG> dblogin useridalias oggconnect_12c
2024-05-08T18:27:40Z ERROR OGG-08110 Login failed. OCI Error ORA (status = 257-ORA-00257: Archiver error. Connect AS SYSDBA only until resolved.
)
In this scenario, when deleting the Integrated Extract process, it will be removed from the Service Manager, but it will remain registered in the database. Let’s take a look:
OGG> delete extract e_ggbr03
2024-05-08T18:27:46Z ERROR OGG-08110 Login failed. OCI Error ORA (status = 257-ORA-00257: Archiver error. Connect AS SYSDBA only until resolved.
)
2024-05-08T18:27:46Z ERROR OGG-08267 A source database connection is required when unregistering or deleting an extract in an upstream configuration.
2024-05-08T18:27:46Z INFO OGG-08100 Extract group E_GGBR03 deleted. <<<<<<<<<<<<<<<<<<<<<<<<
OGG> info all
Program Status Group Type Lag at Chkpt Time Since Chkpt
ADMINSRVR RUNNING
DISTSRVR RUNNING
PMSRVR RUNNING
RECVSRVR RUNNING
OGG (oggconnect_12c@GOLDEN12) >
No Extract process is found in OGG Service Manager anymore, but, when checking the database DBA_CAPTURE view, the process is still registered.
SQL> select capture_name, status from dba_capture;
CAPTURE_NAME STATUS
--------------------- ---------------
OGG$CAP_E_GGBR03 ENABLED
In summary, it appears that when attempting to delete an Integrated Extract process where there is a database connection issue, the error ERROR OGG-08267 is fired, and the Extract process is deleted but is not unregistered from the database. However, based on my understanding, it should not be deleted, as the error message states, “A source database connection is required when unregistering or deleting an extract…“
** This scenario happened only for Non-CDB database, when tested for a CDB it worked fine.
Thanks.
Gilson Martins.
