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.
