Posted by Slow down there….Take a breath. Because if you’ve read the title of this post and you’re already logging on to your 19c database, I need to you STOP. Before you go anywhere, please read this post first.
OK … lets proceed, now that you’re not going to end up with a table you can’t drop!
There’s been a lot of interest in blockchain technology and rather than having to go out and purchase a completely bespoke solution, there has naturally been a lot of interest in being able to simply utilise blockchain technology in the same place where your other business critical data is located, namely, an Oracle database!
That excitement has perhaps turned to disappointment as you’ve read that blockchain was planned for 20c, and since that release got shelved in the crazy year that 2020 was, it is now available in 21c, which might seem an eternity away for many customers still on earlier releases.
Compounding that is the tough decision point of whether to forgo blockchain in order to stay on the 19c version, which has the longest support timeframes, or having to jump to 21c to get blockchain but then perhaps be looking a more frequent upgrade cycle. For some customers, regular upgrades are no drama, but for others, it is can be tough to get approval for upgrades except when absolutely necessary.
But here is some good news! Blockchain tables have made their back into 19c, so you can have blockchain technology and still be on the long term support release!
As alluded to on the blockchain blog, release update 19.10 now contains support for blockchain tables, and the 19c documentation has been updated to reflect this.
Don’t panic if you have upgraded to 19.10 and you are seeing errors when trying to create a blockchain table. Here was my experience when I first tried to do it after upgrading to 19.10
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 30 22:34:49 2021 Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Last Successful login time: Sat Jan 23 2021 13:17:46 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.10.0.0.0
SQL> create blockchain table bc1 (d date, amt number)
2 no drop until 0 days idle
3 no delete until 31 days after insert
4 hashing using "sha2_512" version v1;
create blockchain table bc1 (d date, amt number)
*
ERROR at line 1:
ORA-00901: invalid CREATE command
The first thing you’ll need to do is set compatible to 19.10. After you’ve restarted, you’ll see this
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 19.10.0
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 30 22:34:49 2021 Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Last Successful login time: Sat Jan 23 2021 13:17:46 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.10.0.0.0
SQL> create blockchain table bc1 (d date, amt number)
2 no drop until 0 days idle
3 no delete until 31 days after insert
4 hashing using "sha2_512" version v1;
create blockchain table bc1 (d date, amt number)
*
ERROR at line 1:
ORA-00901: invalid CREATE command
Yeah, not our finest hour 
. We forgot a file in the patch release. So you also need to apply patch 32431413 as shown below.
[oracle@db192 32431413]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/19.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/19.2.0/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.23
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.2.0/dbhome_1/cfgtoollogs/opatch/opatch2021-01-30_22-35-53PM_1.log
Verifying environment and performing prerequisite checks...
--------------------------------------------------------------------------------
Start OOP by Prereq process.
Launch OOP...
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/19.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/19.2.0/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.23
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.2.0/dbhome_1/cfgtoollogs/opatch/opatch2021-01-30_22-36-42PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 32431413
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/19.2.0/dbhome_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '32431413' to OH '/u01/app/oracle/product/19.2.0/dbhome_1'
Patching component oracle.rdbms, 19.0.0.0.0...
Patching component oracle.rdbms.rsf, 19.0.0.0.0...
Patch 32431413 successfully applied.
Log file location: /u01/app/oracle/product/19.2.0/dbhome_1/cfgtoollogs/opatch/opatch2021-01-30_22-36-42PM_1.log
OPatch succeeded.
[oracle@db192 32431413]$
Finally, you’ll be good to go! Blockchain in 19c!
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 30 22:39:03 2021 Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 4294963952 bytes
Fixed Size 9143024 bytes
Variable Size 1392508928 bytes
Database Buffers 2885681152 bytes
Redo Buffers 7630848 bytes
Database mounted.
Database opened.
SQL> create blockchain table bc1 (d date, amt number)
2 no drop until 0 days idle
3 no delete until 31 days after insert
4 hashing using "sha2_512" version v1;
Table created.
Did you make it this far? Then please go back and read this post, so you know how to control blockchain tables in your development environment.
Footnote: Note that this is not a general comment that every time you apply a release update, you should change the compatible parameter. Mike Dietrich covers that nicely here.
Learning is not a spectator sport 
But as described in MOS 2610939.1 we should not set compatible to 19.10.0 so is this note now obsolete?
My general view is in alignment with the MOS note on compatible. I change it *only* when
a) I’ve upgraded to a major new version, and I’ve allowed enough for it the “bed down” at which point I’ll update because I’m at the point where I’ve decided I’m never going back to the old release
b) some particular feature/reason/etc compels me to.
Our aim is that (b) is never a circumstance that people should have to consider, but we are where are.
sorry for the other comment. my question should be. if we change compatible to 19.10.0 does expdp still works ( see mos 2610939.1)
Doesn’t look great 😦
[oracle@db192 ~]$ expdp scott/tiger@pdb1 dumpfile=scott.dmp directory=tmp
Export: Release 19.0.0.0.0 – Production on Fri Feb 5 07:41:43 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
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.
I will raise this internally