Use SQLcl in Oracle Linux 8 on Windows Subsystem for Linux

There is an update to this post on my Blog.

---8<-----------------------------------------------------------------------------------

Windows Subsystem for Linux is a great way to have Linux functionality on Windows. For the first time usage, follow the Microsoft Documentation to install it. Oracle Linux 7.9 and 8.5 are available in the Microsoft Store as fully compatible, free RHEL/CentOS alternatives. And SQLcl can easily installed, as long as you have Java on the system.

It installed within minutes and can immediately be updated via sudo dnf update. But when I tried so install Java, I got a 

RPM: error: db5 error(-30969) from dbenv->open: BDB0091 DB_VERSION_MISMATCH: Database environment version mismatch

Looks like the RPM repository is defect. I found the solution on GitHub:

sudo -s
cd /var/lib/rpm
rpm --rebuilddb
dd if=/dev/zero of=__db.001 bs=1M count=1
dd if=/dev/zero of=__db.002 bs=1M count=1
dd if=/dev/zero of=__db.003 bs=1M count=1
rpm -qa rpm

Follow the postings on GitHub. There seem to be easier solutions, but I did not double check them, because the first one worked for me.

sudo dnf in java-11-openjdk.x86_64

Now JDK installs without problems, for SQLcl 22.1 at least JDK 11.x is mandatory.

Download latest SQLcl zip archive from oracle.com/sqlcl and copy the contents to any directory, like ~/bin/sqlcl

Add the path to the SQLcl binary to your .bashrc and source it. A cool feature of WLS is, that all your Windows drives are automatically mounted under /mnt/c, /mnt/d etc. So if you already have a network/admin directory under Windows, you can point your TNS_ADMIN to that location. 

export PATH=/home/arne/bin/sqlcl/bin:$PATH
export TNS_ADMIN=/mnt/d/Oracle/network/admin

This is what my .bashrc ends with.

That gives me access from Linux to all databases I have configured under Windows.

Install Synology Desktop Utilities on non-Debian Systems

Update: I have made a newer version of this post, including a Flatpak solution

Clone a pluggable Database with APEX and connect to ORDS

Update: this is still valid for ORDS-versions up to 21.x. With ORDS 22.x, this got a bit easier. I created a newer cookbook under Clone a pluggable Database with APEX and connect to ORDS 22.x.

---8<-----------------------------------------------------------------------------

Sometimes I quickly need another test/dev environment for Apex. There are several ways to get one: snapshot or clone the VM, create one with Liquibase or clone the PDB and link the ORDS to it as described below. This is meant as a short cookbook, find more detailed information on the sites from Jeff Smith or Oracle-Base.

Start by connecting to your CDB and create a clone of the PDB where Apex is installed.

create pluggable database orclpdb2 from orclpdb
file_name_convert = ('/orclpdb/', '/orclpdb2/');

alter pluggable database orclpdb2 open;

sho pdbs

alter session set container=orclpdb2;

select * from apex_release;

Double check, that the new PDB has a valid APEX version installed. So much for for the database, we are already done here.

Now all we have to do is to tell ORDS about the 2nd PDB. First we need to configure the DB with

java -jar ords.war setup --database orclpdb2

Finally we need to define an URL mapping where to find the newly added PDB:

java -jar ords.war map-url --type base-path /orclpdb2 orclpdb2

And that is all, restart your ORDS

ORDS should log the new connection. Open the new URL with your browser, in my case:

vm:8080/ords/orclpdb2/apex

The difference between the first and all other databases for an ORDS is, that the first one is always accessible without an additional base-path (like vm:8080/ords/apex) whereas the others always need that base-path. If you want a base-path also for the first database, see this posting from Jeff Smith. 

Apex for the second PDB should be available under that new URL.

And there is also a second Database Actions / SQLDeveloper Web under that new URL pattern.

This is done in less than 5 minutes and depending on your environment, this might be faster than cloning the complete VM. And dropping it only takes seconds, what also might be faster than restoring a snapshot of your VM.

Out-of-Place updating the Developer VM Oracle Database to 19.12

This is an update to my post on out-of-place patching to 19.11. The mechanism is identical, but I added a few steps, so better use this one.

In my last post "Enabling Blockchain Tables and SQL Table Macros with 19.10 Patch for the Developer VM Database", I described how to update the Database via OPatch. Now 19.11. and 19.12. do not have that much new features for developers, this post is only to show how to use out-of-place updating alternatively. Find more information on this on Mike Dietrich's Blog, I will only cover what I need to update the Developer VM.

Create a new directory for the new database home, in my case

mkdir /u01/app/oracle/product/19.12.0/dbhome_3

cd /u01/app/oracle/product/19.12.0/dbhome_3/
unzip ~/ddrive/Install/Linux-x64/LINUX.X64_193000_db_home.zip

As for the first installation, get the Oracle Database 19c 19.3.0 Base Release and unpack it to that directory. 

Create a separate directory for the patch like

mkdir /u01/app/oracle/patches/32904851

Get patch #32545013 for the 19.11 RU and unpack it to that directory. That needs at least OPatch 12.2.0.1.24. Get the latest OPatch via Patch 6880880 (which is .27 at the time of writing). 

mv OPatch OPatch.old
unzip ~/ddrive/Install/Linux-x64/opatch-12-2-0-1-27-p6880880_210000_Linux-x86-64.zip

Rename the OPatch directory to something like OPatch.old, create a new OPatch directory and unzip Patch 6880880 to that.

As we are running OEL 8.x, we need to give the installer a little hint

export CV_ASSUME_DISTID=OEL7.8

Now we can go to the newly created ORACLE_HOME and run the installer. Better unset all variables pointing to the old home.

unset ORABASE_EXEC
unset ORACLE_HOME
unset ORAHOME

cd /u01/app/oracle/product/19.12.0/dbhome_3
./runInstaller -applyRU /u01/app/oracle/patches/32545013/

This will apply the 19.12 RU during the install. If  you need any specific one-off patches, these can be applied using the "-applyOneOffs" option, see Mike Dietrich's Blog. Click through the installer and choose only to install the software without creating a new database.

After the installer has finished, modify /etc/oratab to point to the new ORACLE_HOME

Copy the contents from /u01/app/oracle/product/19.0.0/dbhome_1/dbs to /u01/app/oracle/product/19.12.0/dbhome_2/dbs . Also copy the contents of  /u01/app/oracle/product/19.0.0/network/admin to /u01/app/oracle/product/19.0.12/network/admin .

Change the ORACLE_HOME variable in .bashrc and /etc/oratab to point to the new home. 

Source .bashrc double check the environment variables. Start the listener and the database. Finally run the data patch.

cd $ORACLE_HOME/OPatch
./datapatch -verbose

As this is meant to be a VM for testing and development, it makes sense to also enable the optimizer patches. Mike Dietrich wrote some interesting articles on this, for more information, go directly to his Blog.

execute dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES');

For earlier versions, we would need an extra step. But on 19.12, all we need to enable the optimizer patches is to run rbms_optim_bundle.enable_optim_fixes. 

@?/rdbms/admin/dbmsoptim.sql
@?/rdbms/admin/prvtoptim.plb

If you get an error, because rdbms_optim_bundle is not valid, first run both above scripts and run enable_optim_fixes again.

Have fun with 19.12.

And if you are really sure, not to go back to the old version (better wait a few days), do not forget to run .../deinstall/deinstall from the old ORACLE_HOME.

How to upgrade Apex 20.2 to 21.2 and get Database Actions on-premise.

 This howto is based on my Oracle Developer VM project. To get from the previous Apex version 20.2 to 21.2 is a two step procedure. Install 21.1 over 20.2 and then add Patch Set Bundle 32598392 on top.

Double check the previous version. If you have followed my Oracle Developer VM series, it should be 20.2. Then

• Download Apex 21.1 and the PSU from apex.oracle.com
• Unpack apex_21.1.zip to a new directory
• Change to the new apex directory

According to the Oracle documentation, the database needs to be shut down, then the application server (ORDS in our case) needs also to be shut down, then the database can be restarted. As I use my VM as a single user, I leave the database running. According to Dimitri Gielis Blog, even the ORDS could be left running, but I shut down ORDS in this example.

Connect to the PDB as sys

@apexins.sql SYSAUX SYSAUX TEMP /i/

Run the Apex installer as like shown in the documentation.

After about 15 minutes that should be done.

[oracle@orcldevvm-ol8 apex]$ cd /u01/apex

[oracle@orcldevvm-ol8 apex]$ mv images images.20.2
[oracle@orcldevvm-ol8 apex]$ cp -R /u01/patches/apex/images/ .

As the documentation reads, we need to copy the images directory to the Apex installation directory. I prefer to keep the old data for a while, though this should not be necessary.

Check the Apex Version.

If you want to double-check, startup ORDS and login to the internal workspace to check the Apex version. It should read 21.1.0 now, so far, so good. Shutdown ORDS again.

The documentation recommends to clean up the old APEX schemas. So we need to get rid of the APEX_200200 schema.

alter session set "_oracle_script"=true;
drop user APEX_200200 cascade;

Because the Apex schemas are administrative users, this can only be run in administrative mode.

cd /u01/patches/
unzip ~/ddrive/Install/Generic/apex-21.2-p32598392_2110_Generic.zip
cd 32598392/

Now get Patch Set Bundle for Oracle APEX 21.1, unzip it and change to that directory.

Connect to the PDB and run the installation script catpatch.sql

It took only one and a half minute in my VM.

cp -Rf images/ /u01/apex/

Copy the images directory to the Apex home.

Again check the Apex version.

Get the latest ORDS software from the download site and unpack it to the ords installation directory (eg. /u01/ords). Go to that directory and according to the documentation, run the update with

 

java -jar ords.war install advanced

Enter the correct location of the configuration data from the previous installation, in my case /u01/ords/params/ . The rest is just entering passwords and go with the defaults.

After restarting the ORDS, the new version number should show up on the Apex Web-UI.

Also go to http://localhost:8080/ords/sql-developer and check that you now have the Database Actions instead of SQLDeveloper Web, which is basically the same but offers more functionality.

Out-of-Place updating the Developer VM Oracle Database to 19.11

I have made a newer version of this post covering 19.12. Though this post is still valid, I made some additional hints to the new one, so better go directly to Out-of-Place updating the Developer VM Oracle Database to 19.12.

---8<-----------------------------------------------------------------------------

In my last post "Enabling Blockchain Tables and SQL Table Macros with 19.10 Patch for the Developer VM Database", I described how to update the Database via OPatch. Now 19.11. does not have that much new features for developers, this post is only to show how to use out-of-place updating alternatively. Find more information on this on Mike Dietrich's Blog, I will only cover what I need to update the Developer VM.

Create a new directory for the new database home, in my case

mkdir /u01/app/oracle/product/19.11.0/dbhome_2

As for the first installation, get the Oracle Database 19c 19.3.0 Base Release and unpack it to that directory. Create a separate directory for the patch like

mkdir /u01/app/oracle/patches/32545013

Get patch #32545013 for the 19.11 RU and unpack it to that directory. That needs at least OPatch 12.2.0.1.24. Get the latest via Patch 6880880 (which is .25 at the time of writing). Rename the OPatch directory to something like OPatch.old, create a new OPatch directory and unzip Patch 6880880 to that.

As we are running OEL 8.x, we need to give the installer a little hint

export CV_ASSUME_DISTID=OEL7.8

Now we can go to the newly created ORACLE_HOME and run the installer. Better unset all variables pointing to the old home.

unset ORABASE_EXEC
unset ORACLE_HOME
unset ORAHOME

cd /u01/app/oracle/product/19.11.0/dbhome_2
./runInstaller -applyRU /u01/app/oracle/patches/32545013/

This will apply the 19.11 RU during the install. If  you need any specific one-off patches, these can be applied using the "-applyOneOffs" option, see Mike Dietrich's Blog. Click through the installer and choose only to install the software without creating a new database.

After the installer has finished, modify /etc/oratab to point to the new ORACLE_HOME

Copy the contents from /u01/app/oracle/product/19.0.0/dbhome_1/dbs to /u01/app/oracle/product/19.11.0/dbhome_2/dbs .

Change the ORACLE_HOME variable in .bashrc to the new home. Start a new terminal and double check the environment variables.

Start the listener and the database. Finally run the data patch.

cd $ORACLE_HOME/OPatch
./datapatch -verbose

Have fun with 19.11.

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.