GoldenGate 23ai New Feature — Configuration Service
Tucked away amidst plethora of Oracle GoldenGate (OGG) 23ai new feature announcements, there is mention of a new service named Configuration Service. This is a brand new microservice which needs to be explicitly configured.
In this article I will share my experience in configuring (for lack of better word) the Configuration Service. When correctly configured, we can observe a new OS process, obviously named ConfigService.
-bash-4.4$ ps -ef|grep oggsw
oracle 31687 1 2 07:50 ? 00:08:48 /dbgg/oggsw/oggorasw/bin/ServiceManager --inventory '/dbgg/oggservdep/oggoraservdep/etc/conf'
oracle 31744 31687 1 07:50 ? 00:05:16 /dbgg/oggsw/oggorasw/bin/ConfigService -f /dbgg/oggservdep/oggoraservdep/var/lib/conf/ConfigService/ConfigService.json
oracle 32117 31687 0 07:53 ? 00:00:33 /dbgg/oggsw/oggorasw/bin/adminsrvr --config /dbgg/oggservdep/oggoraservdep/var/run/ogg23oracle-adminsrvr-config.dat
oracle 32155 31687 0 07:53 ? 00:02:52 /dbgg/oggsw/oggorasw/bin/distsrvr --config /dbgg/oggservdep/oggoraservdep/var/run/ogg23oracle-distsrvr-config.dat
oracle 32217 31687 0 07:53 ? 00:02:36 /dbgg/oggsw/oggorasw/bin/pmsrvr --config /dbgg/oggservdep/oggoraservdep/var/run/ogg23oracle-pmsrvr-config.dat
oracle 32265 31687 0 07:53 ? 00:00:34 /dbgg/oggsw/oggorasw/bin/recvsrvr --config /dbgg/oggservdep/oggoraservdep/var/run/ogg23oracle-recvsrvr-config.datWe will review contents of ConfigService.json later in the article. First let’s understand what this service does and why should we use it.
Quoting from the May 2024 release notes:
Configuration service to store critical configuration data
Oracle GoldenGate 23ai now supports a new Configuration Service for managing the configuration files of Oracle GoldenGate for high availability (HA) purposes. For more information, see Configuration Service.
Prior to OGG 23ai release, all configuration data was stored on a filesystem. If you have experience setting up Classic OGG with XAG (Oracle Grid Infrastructure Agent), you will recall selecting some directories to place on local (dirpcs), and some directories (dirprm etc.) on cluster filesystem for failover between nodes. Configuration Service provides a choice to store the configuration data either on a filesystem or a database.
To use Oracle database as backend for storing config data, Installer prompts for connection details.
As seen above a database username, password, connection string and table name is required to use Oracle database as backend.
Based on my testing following are the additional database requirements to successfully configure the service.
- Table must be created in locally managed tablespace.
- Database can be local or remote DB. I am assuming this DB doesn't necessarily have to be part of replication topology. I will update this post if my experience is contrary to this.
- User quota on the tablespace.
- Not stated in documentation but for obvious reasons, DB must be in archivelog mode. It’s no fun in having an unrecoverable database.
- Provide table name in format username.tablename. Although the screengrab above doesn’t illustrate this.
- DB user must have following privileges. Capture and Apply role can be skipped. In my case same database is part of replication setup as well.
GRANT CONNECT, RESOURCE to c##ggadmin;
GRANT OGG_CAPTURE to c##ggadmin;
GRANT OGG_APPLY to c##ggadmin;
ALTER USER c##ggadmin set CONTAINER_DATA = all CONTAINER = current;
grant connect,resource,create session, create view,alter system,
alter user to c##ggadmin;
grant SELECT ANY DICTIONARY to c##ggadmin;
-- execute_catalog_role for access to DBMS_XSTREAM_GG_ADM package
-- needed for trandata. Not required for Configuration Service
grant execute_Catalog_role to c##ggadmin;
GRANT CREATE any TABLE,ALTER any TABLE,DROP ANY TABLE to c##ggadmin;
GRANT CREATE any INDEX,ALTER any INDEX,DROP ANY INDEX to c##ggadmin;
GRANT CREATE any VIEW,DROP ANY VIEW to c##ggadmin;
GRANT CREATE any PROCEDURE,ALTER any PROCEDURE,DROP ANY PROCEDURE
to c##ggadmin;Without dictionary select privilege, installation fails with following error
2024-05-18T07:20:33.826Z | WARNING | OGG-00664: Error 942 on OCI call
[ORA] - ORA-00942: table or view "SYS"."V_$INSTANCE" does not exist7. Oracle DB version 23ai. At the time of writing this article, I didn’t find this specific requirement in the documentation. Any attempt to use 19c version fails at step of configuring “Configuration Service”. On screen error message is not very user friendly in debugging.
Any attempt to use 19c DB reports one of the following messages in installation error log (oggcaConfigAction[date].err)
2024-05-16T17:00:31.424Z | WARNING | OGG-00664: Error 933 on OCI call
[ORA] - ORA-00933: SQL command not properly ended
2024-05-16T17:23:34.107Z | WARNING | OGG-00664: Error 902 on OCI call
[ORA] - ORA-00902: invalid datatypeIf, like me, you also enjoy the peace of mind that comes with silent installs and deployments, you can access the response file here.
For silent deployment creation use this command. $OGG_HOME is the path of your OGG 23ai install location. Remember to replace IP address and password with actual value if using my response file.
$OGG_HOME/bin/oggca.sh -debug -silent -responseFile /home/oracle/oggcaora_ora23pdb.rspWhat happens after deployment wizard finishes creating a new deployment with Oracle DB as backend. for that lets look inside the database.
For this test, both Oracle database 23ai and OGG 23ai are installed on same machine along with MySQL and PostgreSQL.
Following objects are created for a brand new setup without any replication configured so far.
SQLcl: Release 24.1 Production on Sat May 18 14:42:25 2024
Copyright (c) 1982, 2024, Oracle. All rights reserved.
login.sql found in the CWD. DB access is restricted for login.sql.
Adjust the SQLPATH to include the path to enable full functionality.
USER is "C##GGADMIN"
NAME TYPE VALUE
------- ------ -----
db_name string FREE
Last Successful login time: Sat May 18 2024 14:42:26 +00:00
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
SQL> set sqlformat ansiconsole
SQL> select object_name,object_Type from dba_objects where owner='C##GGADMIN';
OBJECT_NAME OBJECT_TYPE
____________________________ ______________
ggs_backendtable_pk INDEX
SYS_IL0000071258C00008$$ INDEX
SYS_LOB0000071258C00008$$ LOB
ggs_backendtable_fk INDEX
GGS_BACKENDTABLE TABLEFrom the output we can see 1 table, 2 indexes, 1 LOB segment and 1 LOB index are created.
Let's look at structure of table GGS_BACKENDTABLE
SQL> info GGS_BACKENDTABLE
TABLE: GGS_BACKENDTABLE
LAST ANALYZED:2024-05-18 13:00:06.0
ROWS :48
SAMPLE SIZE :48
INMEMORY :DISABLED
COMMENTS :
Columns
NAME DATA TYPE NULL DEFAULT COMMENTS
*backendId RAW(16 BYTE) No
*id RAW(16 BYTE) No
parentId RAW(16 BYTE) No
added TIMESTAMP(6) WITH TIME ZONE No CURRENT_TIMESTAMP
modified TIMESTAMP(6) WITH TIME ZONE No CURRENT_TIMESTAMP
dataSchema VARCHAR2(64 BYTE) No
dataPatchBackendName VARCHAR2(64 BYTE) Yes
data JSON No
eTag RAW(32 BYTE) No
name VARCHAR2(512 BYTE) Yes
Indexes
INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS
_________________________________ _____________ _________ _________________ ______________________
C##GGADMIN.ggs_backendtable_fk NONUNIQUE VALID parentId, backendId
C##GGADMIN.ggs_backendtable_pk UNIQUE VALID id, backendId
References
TABLE_NAME CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE VALIDATED GENERATED
___________________ ______________________ ______________ __________ _________________ ____________ ____________
GGS_BACKENDTABLE ggs_backendtable_fk CASCADE ENABLED NOT DEFERRABLE VALIDATED USER NAME
Keep in mind certain words such as data, eTag, name and id are reserved SQL keywords. They are used in in the columns name for GGS_BACKENDTABLE. A closer look in DB audit logs/OGG install logs show that the backend table is created with quoted column identifiers. Thus, we must quote all column names exactly as shown above when selecting from the table. Refer to the example below.
SQL> /
select "etag","name","data","added" from GGS_BACKENDTABLE
*
ERROR at line 1:
ORA-00904: "etag": invalid identifier
Help: https://docs.oracle.com/error-help/db/ora-00904/
SQL> ed
Wrote file afiedt.buf
1* select "eTag","NAME","data","added" from GGS_BACKENDTABLE
SQL> /
select "eTag","NAME","data","added" from GGS_BACKENDTABLE
*
ERROR at line 1:
ORA-00904: "NAME": invalid identifier
Help: https://docs.oracle.com/error-help/db/ora-00904/
SQL> select "name","data","added" from GGS_BACKENDTABLE order by 1;
name data added
----------------------------------- -------------------------------------------------------------------------------- ----------------------------------------
/dbgg/oggdep/ogg23oracle/var/lib/cr {"$schema":"config:dataCollection","collectionSchema":"config:dataCollection"} 18-MAY-24 07.49.34.131926 AM +00:00
edential/secureStore/
/dbgg/oggservdep/oggoraservdep/var/ {"$schema":"config:dataCollection","collectionSchema":"config:dataCollection"} 18-MAY-24 07.49.12.612821 AM +00:00
lib/credential/secureStore/
GLOBALS {"$schema":"ogg:config","lines":["GGSCHEMA c##ggadmin"]} 18-MAY-24 07.49.39.510977 AM +00:00
.....
.....
.....
{{ogg:sca:service:all:roles}} {"$schema":"config:dataCollection","collectionSchema":"config:dataCollection"} 18-MAY-24 07.49.34.956256 AM +00:00
{"$schema":"ogg:secret","secret":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 18-MAY-24 07.49.12.558309 AM +00:00
{"$schema":"ogg:secret","secret":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 18-MAY-24 07.49.25.861095 AM +00:00
{"$schema":"ogg:secret","secret":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 18-MAY-24 07.49.34.988951 AM +00:00
{"connectionString":"localhost:1523/freepdb1","tableName":"c##ggadmin.ggs_backen 18-MAY-24 07.49.12.476629 AM +00:00
48 rows selected.
SQL>GLOBALS file is can be seen in third record. Looking at the last record (other records omitted for brevity), we can see that backend database connection info is stored as config info in the database itself. I am getting Inception vibes :-)
Let’s add a new EXTRACT and observe changes in the backend table. I have created a new DB connection alias and added one extract.
name data added
----------------------------------- -------------------------------------------------------------------------------- ----------------------------------------
afd15321-test {"$schema":"ogg:credentials","userid":"c##ggadmin@localhost:1523/freepdb1"} 18-MAY-24 03.35.30.156880 PM +00:00
HREXT.prm {"$schema":"ogg:config","lines":["EXTRACT hrext","USERIDALIAS test DOMAIN Oracle 18-MAY-24 04.10.39.264948 PM +00:00
HREXT {"credentials":{"domain":"OracleGoldenGate","alias":"test"},"status":"stopped"," 18-MAY-24 04.20.05.406873 PM +00:00
HREXT {"$schema":"checkpoint:base","header":{"version":3,"recordType":"Oracle IE Big S 18-MAY-24 04.20.05.425194 PM +00:00
EXTRACT:HREXT {"$schema":"internal:taskHistory","history":[{"started":{"sec":1716049348,"usec" 18-MAY-24 04.22.28.535122 PM +00:00
56 rows selected.
SQL> l
1* select "name","data","added" from GGS_BACKENDTABLE order by 3
SQL>We can see that one record is added for connection alias “test,” one record each for HREXT extract, HREXT.prm parameter file, checkpoint information and history information.
Pretty cool! All parameter, checkpoint, DB connection information is in the database.
To summarize, OGG 23ai now provides additional options to store configuration data either in filesystem or database. As per documentation it’s not possible to go from DB as backend to filesystem. Only Oracle DB can be used as the backend for now.
Information stored in DB can easily be replicated across region or geographical sites to ensure high availability and provide disaster recovery. Hence, this is going to be an extremly useful service.
Keep in mind that if you are using OGG23ai on OCI, there is no option to configure this or debug the settings as show above. This is a service layer option and end users need not bother about setting it up. This will be of primary use for non-OCI GG users for now. I expect OCI GG to automatically provide for this at some point.
In the next article we will see how multiple deployments can use the same backend table, thereby storing all configuration information in single database location.
What do you think of this new feature? Share your thoughts in comments and provide your feedback. As always thank you for reading. Happy learning.
Following information is provided for academic purposes only. Configuration Service details can be managed using REST API. We can observe that ConfigService.json file stores DB connection string, username, table name, wallet location, etc.
[oracle@gg23test ~]$ more /dbgg/oggservdep/oggoraservdep/var/lib/conf/
ConfigService/ConfigService.json
{
"$schema": "config:configurationFile",
"installationId": "699a4bd5-32f3-40ee-866a-0af9b6b1312a",
"backends": [
{
"$schema": "config:backend",
"id": "00000000-0000-0000-6557-000000000001",
"type": "Wallet",
"encrypted": true,
"configuration": {
"serialized": true,
"directory": "/dbgg/oggservdep/oggoraservdep/var/lib/conf/
ConfigService/ConfigService.wallet/",
"data": "xxxxxxx"
}
},
{
"$schema": "config:backend",
"id": "32f69a90-2a42-4913-8e23-3233c2972172",
"type": "Oracle",
"replaced": [
"f3ba9003-6e30-4b09-b653-9de00c43acdc"
],
"configuration": {
"connectionString": "localhost:1523/freepdb1",
"tableName": "c##ggadmin.ggs_backendtable",
"username": "C##ggadmin"
}
},
{
"$schema": "config:backend",
"id": "a1b341ba-2db1-49da-ba9f-c57fc5cdd55e",
"type": "Wallet",
"options": [
"duplicateNames"
],
"replacedBy": "e8d81a61-5e66-48f8-a8a3-e5ffd6dbffdf",
"encrypted": true,
"configuration": {
"directory": "${OGG_VAR_HOME}/lib/conf/ConfigService/"
}
},
{
"$schema": "config:backend",
"id": "e8d81a61-5e66-48f8-a8a3-e5ffd6dbffdf",
"type": "Oracle",
"options": [
"duplicateNames"
],
"replaced": [
"a1b341ba-2db1-49da-ba9f-c57fc5cdd55e"
],
"encrypted": true,
"configuration": {
"connectionString": "localhost:1523/freepdb1",
"tableName": "c##ggadmin.ggs_backendtable",
"username": "C##ggadmin"
}
},
{
"$schema": "config:backend",
"id": "f3ba9003-6e30-4b09-b653-9de00c43acdc",
"type": "Files",
"replacedBy": "32f69a90-2a42-4913-8e23-3233c2972172",
"configuration": {
"directory": "${OGG_VAR_HOME}/lib/conf/ConfigService/"
}
}
],
"mappings": [
{
"$schema": "config:mapping",
"id": "699a4bd5-32f3-40ee-866a-0af9b6b1312a",
"backends": [
{
"name": "backendSecrets",
"id": "00000000-0000-0000-6557-000000000001"
}
]
},
{
"backends": [
{
"name": "standard",
"id": "32f69a90-2a42-4913-8e23-3233c2972172"
},
{
"name": "secure",
"id": "e8d81a61-5e66-48f8-a8a3-e5ffd6dbffdf"
}
],
"$schema": "config:mapping",
"id": "00000000-0000-0000-458c-000000000001"
},
{
"backends": [
{
"name": "secure",
"id": "e8d81a61-5e66-48f8-a8a3-e5ffd6dbffdf"
},
{
"name": "standard",
"id": "32f69a90-2a42-4913-8e23-3233c2972172"
}
],
"$schema": "config:mapping",
"id": "56fa711c-abce-40cf-9388-3fae7362d28f"
}
]
}Environment details for this setup
1. OGG 23ai for Oracle database. Non-SSL setup (HTTP)
