Oracle 23ai Blockchain Tables and GoldenGate Replication

Blockchain tables was a feature introduced in Oracle 21c and back ported to certain releases in Oracle 19c.  

It involves the use crypto-secure methods to help protect data from tampering or deletion by both external hackers and also rogue or compromised insiders. 

Blockchain tables insert-only restrictions prevent updates or deletions (even by DBAs) and cryptographic hash chains enable verification of table data. 

Blockchain tables provide a tamper-proof built-in audit trail or journal and these capabilities can be used in applications related to financial ledgers, payments history, regulatory compliance tracking, legal logs, and any data where tampering or deletion could result in significant legal, reputation, or financial consequences. 

Oracle Database 23ai provides support for logical replication of blockchain tables via Oracle GoldenGate as well as introduces the ability to add/drop columns without impacting cryptographic hash chaining.

In an earlier release “V1” blockchain table, adding new columns or dropping existing columns was not supported. 

In Oracle Database 23ai we have the option of using the original “V1” version, or the new “V2” version, which supports additional functionality.

Let us look at an example of GoldenGate replication using 23ai Blockchain tables.

Create the Blockchain table on 23ai source and target databases.

[oracle@perth system]$ sqlplus scott/Oracle_4U@perth:1521/ORCLPDB1

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Jan 6 04:29:33 2025 

Version 23.6.0.24.10 

Copyright (c) 1982, 2024, Oracle.  All rights reserved. 

Last Successful login time: Wed Dec 18 2024 05:23:51 +00:00 

Connected to: 

Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Limited Availability 

Version 23.6.0.24.10 
 

SQL> create blockchain table  scott.myobjects_blockchain 
     (object_id number, object_name varchar2(100), owner varchar2(30)) 
     no drop until 1 days idle 
     no delete until 30 days after insert 
     hashing using "SHA2_512" version "v2";  

Table created. 

[oracle@perth system]$ sqlplus scott/Oracle_4U@perth:1521/ORCLPDB2

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Jan 6 04:29:33 2025 

Version 23.6.0.24.10 

Copyright (c) 1982, 2024, Oracle.  All rights reserved. 

Last Successful login time: Wed Dec 18 2024 05:23:51 +00:00 

Connected to: 

Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Limited Availability 

Version 23.6.0.24.10 
 

SQL> create blockchain table  scott.myobjects_blockchain 
     (object_id number, object_name varchar2(100), owner varchar2(30)) 
     no drop until 1 days idle 
     no delete until 30 days after insert 
     hashing using "SHA2_512" version "v2";  

Table created. 

View the GoldenGate Extract and process running in the GoldenGate 23ai environment.

Note the additional (block chain metadata) columns which are present in the MYOBJECTS_BLOCKCHAIN table.

INSERT rows into the source Blockchain table.

Note the number of rows processed by the GoldenGate 23ai Extract and Replicat processes.

Also note the additional metadata columns in the blockchain tables which are also part of the GoldenGate replication.

SQL> insert into  myobjects_blockchain 
     select object_id,object_name,owner from dba_objects; 

70931 rows created. 

SQL> commit;   

Commit complete. 

Alter the blockchain and add an additional column.

INSERT some more rows into the blockchain table including the newly added column.

Note the Replicat has also applied these changes as well involving the newly added column.

SQL> alter table  scott.myobjects_blockchain 
     add object_type varchar2(100); 

Table altered. 

SQL> insert into scott.myobjects_blockchain 
     select object_id,object_name,owner,object_type 
     from dba_objects 
     where owner='SYSTEM'; 

89 rows created.  

SQL> commit; 

Commit complete. 

In Oracle Database 23ai, we can now add as well as drop columns from a blockchain table.

But operations like DELETE as well as TRUNCATE are not permitted on the blockchain table.

SQL> alter table scott.myobjects_blockchain drop column object_type; 

 Table altered. 


SQL> truncate table  scott.myobjects_blockchain; 

truncate table  scott.myobjects_blockchain 
                   * 
ERROR at line 1: 

ORA-05715: operation not allowed on the blockchain or immutable table 

Help: https://docs.oracle.com/error-help/db/ora-05715


SQL> delete scott.myobjects_blockchain; 

delete scott.myobjects_blockchain 
             * 

ERROR at line 1: 

ORA-05715: operation not allowed on the blockchain or immutable table 

Help: https://docs.oracle.com/error-help/db/ora-05715/