Oracle GoldenGate is a powerful replication tool that enables real-time data replication across heterogeneous environments. With the release of Oracle 23ai, enhancements in data replication and dictionary views provide more robust insights into replication readiness. One such critical view is DBA_GOLDENGATE_SUPPORT_MODE, which helps Database Administrators (DBAs) determine whether a table is supported for replication and its corresponding mode.
What is DBA_GOLDENGATE_SUPPORT_MODE?
The DBA_GOLDENGATE_SUPPORT_MODE view provides metadata about tables and their compatibility with Oracle GoldenGate. It helps identify whether a table is fully supported, has limitations, or is not supported for replication.
Key Columns in DBA_GOLDENGATE_SUPPORT_MODE
- OWNER VARCHAR2(128): The schema that owns the table.
- OBJECT_NAME VARCHAR2(128): The name of the table or object.
- SUPPORT_MODE VARCHAR2(6): Indicates the level of support for GoldenGate replication. Possible values include:
- FULL – A capture process can capture changes made to all of the columns in the table
- ID KEY – A capture process can capture changes made to the key columns and any other columns in the table supported by the capture process, except for LOB, LONG, LONG RAW, and XMLType columns.
- PLSQL – A capture process can capture changes made to the table as long as Procedural Supplemental Logging is enabled on the source database. Such tables include hierarchy-enabled tables, AQ queue tables, and tables containing columns of type MDSYS.SDO_TOPO_GEOMETRY, MDSYS.SDO_GEORASTER, or MDSYS.SDO_RDF_TRIPLE_S.
- NONE – A capture process cannot capture changes made to any columns in the table because the table is not supported for replication.
- EXPLANATION VARCHAR2(4000): Reason the table does not have FULL capture process support. This column is populated only when both of the following conditions are met:
- The value of the COMPATIBLE initialization parameter is 20.0 or higher
- The value of the SUPPORT_MODE column is ID KEY, INTERNAL, PLSQL, or NONE
How to Use DBA_GOLDENGATE_SUPPORT_MODE in Oracle 23ai
It is assumed that the environment is setup for Golden Gate replication. E.g.
show parameter ENABLE_GOLDENGATE_REPLICATION
-- ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH SID='*';To check the replication support status of tables in a database, you can execute the following query:
SELECT OWNER, object_NAME, SUPPORT_MODE
FROM DBA_GOLDENGATE_SUPPORT_MODE
ORDER BY OWNER, object_NAME;This query returns a list of all tables and their replication support status.
Test Cases
Test Case 1: Verifying GoldenGate Support Mode in Oracle 23ai
Let’s go through a simple test case where we create a table, check its support mode, and analyze the output.
Step 1: Create a Sample Table
CREATE TABLE test_table (
id NUMBER PRIMARY KEY,
name VARCHAR2(100),
created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
data JSON -- New JSON datatype in Oracle 23ai
);Step 2: Check the GoldenGate Support Mode
select owner, object_name, support_mode
from DBA_GOLDENGATE_SUPPORT_MODE
where object_name like 'TEST_TABLE';
OWNER OBJECT_NAME SUPPOR
--------------------------------------
RADU TEST_TABLE FULL Step 3: Interpret the Output
- If the SUPPORT_MODE is SUPPORTED, the table can be fully replicated using GoldenGate.
- If it is not SUPPORTED, check Oracle’s documentation for potential limitations.
Test Case 2: Verifying GoldenGate Support Mode in Oracle 23ai – AQ Table
Let’s go through a simple test case where we create a table, check its support mode, and analyze the output.
Step 1: Create a Sample AQ Table
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE(
queue_table => 'my_queue_table',
queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE', -- Message type
multiple_consumers => TRUE
);
END;
/Step 2: Check the GoldenGate Support Mode
select owner, object_name, support_mode, explanation
from DBA_GOLDENGATE_SUPPORT_MODE
where object_name like 'MY_QUEUE_TABLE';
WNER OBJECT_NAME SUPPORT_MODE EXPLANATION
---------- -------------------- -------------------- --------------------
RADU MY_QUEUE_TABLE PLSQL Table is an Advanced
Queue Table Step 3: Interpret the Output
- If the SUPPORT_MODE is SUPPORTED, the table can be fully replicated using GoldenGate.
- If it is not SUPPORTED, check Oracle’s documentation for potential limitations.
Conclusion
With Oracle 23ai, the DBA_GOLDENGATE_SUPPORT_MODE view has been enhanced to provide better insights into table replication compatibility. By leveraging this view, DBAs can proactively identify and address potential replication challenges, ensuring smooth and efficient data synchronization across environments.
Would you like assistance in analyzing the replication readiness of your database? Let me know in the comments!