Introduction
Oracle Blockchain Table was initially introduced in release version 20c (preview only) and later in 21c, but the feature has been backported and is now available in version 19c as well. Blockchain tables are insert-only tables that provide a highly tamper-resistant persistence option. The rows are chained by storing the previous row’s hash in the current row to verify that the data has not been modified. To learn more about blockchain tables and their benefits have a look at this blog post. Today, we will discuss the prerequisites and considerations to use blockchain tables in RU 19.10 and 19.11.
RU 19.10
1. Apply Release Update (RU) 19.10
Your 19c database must be at least at release level 19.10, which was released on January 19, 2021. Oracle always recommends to apply the newest release update, which is 19.11 at the time of writing this blog.
In the Oracle Cloud, use the Cloud Tooling to patch your database as described in a previous blog. For on-premises, Download RU 19.10.0.0.210119 (Patch 32218454) to patch your databases. As always, out-of-place patching is recommended for your production systems. Here I’ll continue with in-place patching for the demo purpose. Unzip, and apply the patch to the database home. You need opatch version 12.2.0.1.23 (Patch 6880880) or later:
$ORACLE_HOME/OPatch/opatch version
SQL> shutdown immediate
lsnrctl stop
cd <PATCH_TOP_DIR>/32218454
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
$ORACLE_HOME/OPatch/opatch apply -local
lsnrctl start
SQL> startup
SQL> alter pluggable database all open;
$ORACLE_HOME/OPatch/datapatch -prereq
$ORACLE_HOME/OPatch/datapatch -verbose
SQL> SELECT * FROM cdb_registry_sqlpatch;
2. Apply Patch 32431413
To use blockchain tables, the interim (one-off) patch 32431413 must be applied on top of RU 19.10. Download patch 32431413, unzip, and apply it to your database home as usual:
SQL> shutdown immediate
lsnrctl stop
cd <PATCH_TOP_DIR>/32431413
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
$ORACLE_HOME/OPatch/opatch apply -local
lsnrctl start
SQL> startup
To check that the patch has been applied:
$ORACLE_HOME/OPatch/opatch lsinventory | grep 32431413
SQL> SELECT xmltransform(dbms_qopatch.is_patch_installed('32431413'), dbms_qopatch.get_opatch_xslt) FROM dual;
In the Oracle Cloud, you can apply RU 19.10 and the interim patch in one step automatically by using Custom Database Software Images as discussed in a previous blog.
3. COMPATIBLE=19.10.0
The default value for COMPATIBLE in 19c databases is 19.0.0.
SQL> SELECT value FROM v$parameter WHERE name = 'compatible';
VALUE
--------------------------------------------------------------------------------
19.0.0
Now, if you try to create a blockchain table, you’ll hit the following error:
ORA-05728: COMPATIBLE needs to be 19.10.0.0.0 or higher to use blockchain table
Hence, you’ll need to set COMPATIBLE to at least 19.10.0 to be able to create the blockchain table.
But, would this change have any implications??? Yes, especially if you are using Multitenant. Read the blog post Should you change COMPATIBLE when you apply an RU? by my dear colleague Mike Dietrich before you apply any changes.
SQL> alter system set compatible='19.10.0' scope=spfile;
System altered.
SQL> shutdown immediate
SQL> startup
Create Blockchain Table
Now, the create blockchain table succeeds:
SQL> CREATE BLOCKCHAIN TABLE bank_ledger (bank VARCHAR2(128), d_date DATE, d_amount NUMBER)
NO DROP UNTIL 25 DAYS IDLE
NO DELETE UNTIL 31 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1";
Table created.
Use “NO DROP UNTIL 0 DAYS IDLE” if you are just testing and want to drop the table immediately after inserting some data.
Data Pump Export
According to Bug 30828205 (EXPDP IS FAILING WITH ORA-39021 WHEN COMPATIBLE IS SET AS 19.4.0) Data Pump Export would fail:
expdp system@pdb1910 directory=DATA_PUMP_DIR schemas=myuser
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39021: Database compatibility version 19.10.0.0.0 is not supported.
The workaround is really simple, just add version=19 (=19.0.0.0.0) to the export command:
expdp system@pdb1910 directory=DATA_PUMP_DIR schemas=myuser version=19
The one-off patch should be available in 19.11 So let’s check!
RU 19.11
Release Update (RU) 19.11 was released on April 20, 2021. Download RU 19.11 (Patch 32545013) and apply it as usual. You need opatch version 12.2.0.1.24 or later.
Let’s check whether the patches for blockchain table (32431413) and the data pump export (30828205) are already included in the 19.11 RU:
$ORACLE_HOME/OPatch/opatch lsinventory | grep '32431413\|30828205'
Yes. They are included. So everything should work without any issues.
We still need to set up compatible to at least 19.10 to use blockchain tables:
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 19:30:44 2021
Version 19.11.0.0.0
SQL> alter system set compatible='19.10.0' scope=spfile;
System altered.
SQL> shutdown immediate
SQL> startup
Create the table:
SQL> CREATE BLOCKCHAIN TABLE bank_ledger (bank VARCHAR2(128), d_date DATE, d_amount NUMBER)
NO DROP UNTIL 25 DAYS IDLE
NO DELETE UNTIL 31 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1";
Table created.
Works!
And Data Pump Export?
expdp system@pdb1910 directory=DATA_PUMP_DIR schemas=myuser
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Apr 21 19:40:10 2021 elapsed 0 00:00:27
Works fine too in 19.11.
Happy Patching!
Conclusion
The Blockchain Table feature is available for Standard and Enterprise Edition databases on-premises and in the cloud for no additional cost. The feature is available in version 21c and has been backported to 19c. The 19c database must be at least at RU 19.10 and having the on-off patch 32431413 applied. RU 19.11 already includes the patches and everything works just fine. The COMPATIBLE parameter must be set to at least 19.10.0.
Database Heartbeat 