Enabling Blockchain Tables and SQL Table Macros with Oracle 19.10 Patch

UPDATE: as 19.12. is available and out-of-place patching is the recommended way, skip the first part and follow this posting instead: Out-of-Place updating the Developer VM Oracle Database to 19.12

This posting is part of my Oracle Database Developer VM series and shows how to add SQL Table Macros and Blockchain tables to the Database by installing the 19.10 patch and a few additional steps. The solution is not based on my own work. A few of my colleagues worked it out, it is documented on Mike Dietrichs Blog. This post is meant to only extract what is needed for my DB VM and to have it all on one page. For more information go directly to Mikes article. 

Setup

First go to support.oracle.com and get the Database Release Update 19.10.0.0.210119 Patch 32218454. From the prerequisites in the README.html, we need Opatch version 12.2.0.1.23 for patching to 19.10. Checking the installed version:

[oracle@orcldevvm-ol8 ~]$ $ORACLE_HOME/OPatch/opatch version 

OPatch Version: 12.2.0.1.17

Our  19.3 standard installation only comes with 12.2.0.1.17. So we also need to download opatch from 6880880. Delete or rename the old OPatch directory and replace it with the contents of this archive and check again:

[oracle@orcldevvm-ol8 ~]$ $ORACLE_HOME/OPatch/opatch version

OPatch Version: 12.2.0.1.24

This is just following the steps from the patch README.html. Next we need to check the prerequisites:

[oracle@orcldevvm-ol8 32218454]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.24
Copyright (c) 2021, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.24
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2021-02-19_08-53-08AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

Looks good, so we can run opatch now. Shutdown the database as required:

[oracle@orcldevvm-ol8 32218454]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.24
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.24
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2021-02-19_08-56-24AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   32218454  

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.0.0/dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y

...

Patching component oracle.jdk, 1.8.0.201.0...
Patch 32218454 successfully applied.
Sub-set patch [29517242] has become inactive due to the application of a super-set patch [32218454].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2021-02-19_08-56-24AM_1.log

OPatch succeeded.

For the datapatch we need to start up the database again and as we are on a multi tenant architecture, the PDB needs also to be opened first:

[oracle@orcldevvm-ol8 32218454]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 19 09:13:24 2021
Version 19.10.0.0.0

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

SQL> Connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 6610222304 bytes
Fixed Size		    9149664 bytes
Variable Size		 2550136832 bytes
Database Buffers	 4043309056 bytes
Redo Buffers		    7626752 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

To finish up the 19.10 patch, we need to run the datapatch:

[oracle@orcldevvm-ol8 OPatch]$ cd $ORACLE_HOME/OPatch
[oracle@orcldevvm-ol8 OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.10.0.0.0 Production on Fri Feb 19 09:15:50 2021
Copyright (c) 2012, 2020, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_14976_2021_02_19_09_15_50/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
  No interim patches found

Current state of release update SQL patches:
  Binary registry:
    19.10.0.0.0 Release_Update 210108185017: Installed
  PDB CDB$ROOT:
    Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 18-AUG-20 10.27.40.949667 AM
  PDB ORCLPDB:
    Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 18-AUG-20 10.35.55.137144 AM
  PDB PDB$SEED:
    Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 18-AUG-20 10.35.55.137144 AM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT ORCLPDB
    No interim patches need to be rolled back
    Patch 32218454 (Database Release Update : 19.10.0.0.210119 (32218454)):
      Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.10.0.0.0 Release_Update 210108185017
    No interim patches need to be applied
  For the following PDBs: PDB$SEED
    No interim patches need to be rolled back
    Patch 32218454 (Database Release Update : 19.10.0.0.210119 (32218454)):
      Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.10.0.0.0 Release_Update 210108185017
    No interim patches need to be applied

Installing patches...
Patch installation complete.  Total patches installed: 3

Validating logfiles...done
Patch 32218454 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/32218454/24018797/32218454_apply_ORCL_CDBROOT_2021Feb19_09_16_38.log (no errors)
Patch 32218454 apply (pdb ORCLPDB): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/32218454/24018797/32218454_apply_ORCL_ORCLPDB_2021Feb19_09_27_46.log (no errors)
Patch 32218454 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/32218454/24018797/32218454_apply_ORCL_PDBSEED_2021Feb19_09_35_55.log (no errors)

Automatic recompilation incomplete; run utlrp.sql to revalidate.
  PDBs: ORCLPDB

SQL Patching tool complete on Fri Feb 19 09:41:24 2021

Looks good for now, but according to Mikes post ("Oracle Database 19.10.0 and Blockchain Tables and COMPATIBLE"), there is still some work to do. First we need patch 32431413 from MyOracle Support and unpack and apply this one too:

[oracle@orcldevvm-ol8 32431413]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.24
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.24
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2021-02-19_10-17-59AM_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.0.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.0.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.0.0/dbhome_1/cfgtoollogs/opatch/opatch2021-02-19_10-17-59AM_1.log

OPatch succeeded.

Now for the tricky part: to make Blockchain Tables work, compatible has to be set to 19.10.0. But this has some implications. Carefully read Mike's article to decide, if Blockchain Tables are worth it. For my development VM, I set the parameter, but took a VM snapshot before.

SQL> alter system set compatible='19.10.0' scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 6610222304 bytes
Fixed Size		    9149664 bytes
Variable Size		 2768240640 bytes
Database Buffers	 3825205248 bytes
Redo Buffers		    7626752 bytes
Database mounted.
Database opened.
SQL> sho parameter compatible

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
compatible			     string	 19.10.0
noncdb_compatible		     boolean	 FALSE

So much for the setup, now let's try it out.

SQL Table Macros

In 19.10, only the SQL Table Macros are available. This is not a post on SQL Macros, so I run just a very simple test

CREATE OR REPLACE FUNCTION EmpDept return varchar2 SQL_MACRO is
BEGIN
  RETURN q'{ 
        select  employee_id, first_name, last_name, email, phone_number, hire_date,
        job_id, salary, commission_pct, employees.manager_id emanager_id, department_name, 
        location_id, departments.manager_id dmanager_id
        from    employees, departments
        where employees.department_id = departments.department_id
    }';
END;
/

select * from EmpDept();

In SQLDeveloper, this should result in 

Blockchain Tables

As with SQL Table Macros, this is not a posting on Blockchain Tables, but just a simple test with 19.10. Run the following

CREATE blockchain TABLE Test_Blockchain 
( 
    id NUMBER, 
    content VARCHAR2(128)
) 
no drop until 0 days idle
no delete until 16 days after insert
hashing using "SHA2_512" version "v1";

insert into Test_Blockchain(id,content) values (1,'Oracle Blockchain Tables');

select * from Test_Blockchain;

truncate table Test_Blockchain;

That should give you

The error is as expected, because we configured the Blockchain table to not allow deletes before 16 days after insert. For more information on the parameters of the create Blockchain table statement, see also blockchain_table_clauses.

The Database Developer VM: installing Apex, ORDS 21.x und SQLDeveloper Web

This post is part of my Oracle Developer VM  Project series. It is not meant to replace the official documentation in any way and what I describe is no recommended configuration. It is only meant to be a guide, if you follow my DB Developer VM Project and want to add these services to your VM.

Compared to my other posts, this is a bunch of tools to be installed in one go. The reason is that if you want ORDS and Apex, then it is the best way to install Apex first. But it won't run without ORDS, so both should be installed in one go. And SQLDeveloper Web comes for free with ORDS. But despite the number of additional tools, this is one of the shorter posts of this series.

According to the documentation, memory_target needs to be set to at least 300M for Apex, but in my setup the database won't start with this amount of memory. I get the following error:

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 6304M

So first, we will set that parameter to the required value.

1 2	alter system set memory_target='6304M' scope=spfile; startup force;

This should give you the following: 

Download the Apex install bundle and unzip it (eg. to /u01/apex). In the unpacked folder run the installer

sqlplus sys/your_password@orclpdb as sysdba @apexins sysaux sysaux temp /i/

Leave the session open and create the Apex Admin user by running @apxchpwd.sql.

Unlock the APEX_PUBLIC_USER and change his password, which has been randomly set at installation

alter user apex_public_user identified by oracle account unlock;

Still in the same session, run @apex_rest_config.sql and set both passwords for  the APEX_LISTENER user and the APEX_REST_PUBLIC_USER user.

To use web services and for sending email, network services need to be enabled. Copy the following from the documentation and run it:

1 2 3 4 5 6 7

BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => '*', ace => xs$ace_type(privilege_list => xs$name_list('connect'), principal_name => 'APEX_200200', principal_type => xs_acl.ptype_db)); END;

So much for the Apex part. Download the latest ORDS from https://www.oracle.com/database/technologies/appdev/rest-data-services-downloads.html and unzip the contents to your target directory (eg. /u01/ords).

Go to the directory where you have unzipped ORDS and run

java -jar ords.war

ORDS will notice, that this is a first time run and will ask for some configuration parameters. In most cases, you can just accept the defaults (except for the passwords, of course). If you have followed my guide so far, the only parameters, that need to be set, are:

• location to store configuration data => /u01/ords/params/
• database service name: orclpdb
• Enter the APEX static resources location:/u01/apex/images/

After the setup, ORDS will keep running in standalone mode. We are not done yet, but give it a try to check if everything went fine so far. Open Firefox and enter http://localhost:8080, that should give you the Apex login page (as this is the first call, it will take a few seconds).

Following the documentation, we need to set maxFormContentSize on startup by running 

java -Dorg.eclipse.jetty.server.Request.maxFormContentSize=3000000 -jar ords.war

I created a runords.sh with this line in the same directory as ords.war for starting ORDS. Looking at the ORDS terminal window, you will see the following warnings:

Though this is not a production environment, I prefer to configure the system to run without these warnings. At least it makes it easier to notice, if something really goes wrong. So I set these parameters to the recommended values from the documentation.

Go to /u01/ords/params/ords/ and edit defaults.xml. Add the following lines before the closing </properties>:

<entry key="jdbc.MaxLimit">20</entry>
<entry key="jdbc.InitialLimit">10</entry> 

I also got the following error:

INFO Disabling document root because the specified folder does not exist: /u01/ords/params/ords/standalone/doc_root

To get rid of this INFO either create that directory or edit /u01/ords/params/ords/standalone/standalone.properties and set standalone.doc.root to an existing folder.

A few errors I ran into were all like

WARNING The pool named: |apex|al| is invalid and will be ignored: The username or password for the connection pool named |apex|al|, are invalid, expired, or the account is locked

Probably I messed up my passwords when running the installer. This can be fixed by setting the passwords for the following users (and unlocking them, just in case):

1 2 3 4 5 6 7 8

alter user ORDS_PUBLIC_USER account unlock; alter user APEX_PUBLIC_USER account unlock; alter user APEX_LISTENER account unlock; alter user APEX_REST_PUBLIC_USER account unlock; alter user ORDS_PUBLIC_USER identified by oracle; alter user APEX_PUBLIC_USER identified by oracle; alter user APEX_LISTENER identified by oracle; alter user APEX_REST_PUBLIC_USER identified by oracle;

Now ORDS should come up with any errors.

Just to double check that everything is there, call http://localhost:8080/ords/sql-developer

Of course, you need a rest enabled schema to log in. That's it, everything is running.