Blockchain tables are here in 19c!

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 Smile . 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.

6 comments

    1. 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.

  1. 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)

    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

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.