Clone a pluggable Database with APEX and connect to ORDS 22.x

 This is an update to my older post Clone a pluggable Database with APEX and connect to ORDS, which is still valid for older ORDS-versions before 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 apexpdb2 from apexpdb1
file_name_convert = ('/apexpdb1/', '/apexpdb2/');

alter pluggable database apexpdb2 open;

sho pdbs

alter session set container=apexpdb2;

select * from apex_release;

Double check, that the new PDB has a valid APEX version installed. Add the newly created database in your $TNSADMIN/tnsnames.ora like

APEXPDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = apexpdb2)
    )
  )

So much for for the database. Now all we have to do is to tell ORDS about the 2nd PDB. There is a chapter in the documentation for this, so the setup is straight forward. For dealing with ORDS, some settings should be made in .bashrc

export ORDS_CONFIG=/u01/ords/config
export ORDS_HOME=/u01/ords-22-2
export PATH=$ORDS_HOME/bin:$PATH

Stop ORDS if it is already running We need to add the DB with the following if we want to use the interactive mode

ords install -i

At the end, ORDS should be running in standalone mode. If you don't like interactive installations, you can do the same with one (loooong) command. First we need a text file with the passwords for the admin and proxy user:

[oracle@orcldevvm-ol8 ~]$ cat password.txt 
oracle
oracle

Then run something like

ords install --db-pool apexpdb2 --db-hostname localhost --db-port 1521 --db-servicename apexpdb2 --feature-sdw true --feature-db-api true --feature-rest-enabled-sql true --admin-user sys --proxy-user --password-stdin < password.txt

Open the new URL with your browser, in my case:

vm:8080/ords/apexpdb2/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.

Run two different Apex versions on a single standalone ORDS

A single standalone ORDS can handle multiple database connections with Apex as described here. But without additional configuration, this can be problematic if not all Apex instances are on the same version. For example, in the Developer VM Project, we have two PDB's with APEX locally installed at version 21.5.

When upgrading one PDB to Apex 22.1, the last step according to the documentation is Copying the Images Directory. After that step, the images directory files are on version 22.1 whereas the unpatched 2nd PDB is still running 21.5, which will lead to the following error:

Error: There is a problem with your environment because the Application Express files are not up-to-date!

So as long as not all Apex environments on a given ORDS instance are updated within the same maintenance window, seperate images directories are needed for at least any Apex version running on that ORDS. An image prefix maps to a virtual directory, for more information see Image Prefix changes in Oracle Application Express 4.2.2.

I have found no solution to configure multiple virtual directories on for standalone ORDS. But even with only a single static files directory, several file versions can be put below that single root.

Just organize the files in separate directories per version like images/images-21-5. This can be configured per application under Shared Components/User Interface Attributes.

If you have more than one application on the Apex instance, the setting could be set at once for the whole instance with the reset_image_prefix.sql script, that comes with your Apex installation under utilities. Usage is described in the MOS-Note How to Switch APEX to Use Oracle APEX Static Resources on Content Delivery Network (CDN) (Doc ID 2817084.1).

This script also does some cleanup which takes some time, so according to this blog post this is not recommended for production.

begin 
        apex_instance_admin.set_parameter(
            p_parameter => 'IMAGE_PREFIX',
            p_value     => '/i/images-21-5' );
        
        commit;
  end;

Alternatively this can be done with the above listed procedure, that does not do the invalidation. But in my instance, the changes took not effect immediately. So probably the invalidation in the script is there for a reason.

Especially if your Apex application is used over public internet, you might want to get rid of the static files anyways and use the the Content Delivery Network instead as described in Announcing Oracle APEX Static Resources on Content Delivery Network.

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.