Installing APEX on Oracle Database Base Service

This is Part 3 of my  Oracle Database Developer OCI Environment series.

Download APEX

On the database machine, get the latest APEX release from https://www.oracle.com/tools/downloads/apex-downloads/

Download the ‘All Languages’ package or use wget, rename the apex folder to append the version number and create a symlink apex to that folder.

cd /u01
wget -P /tmp https://download.oracle.com/otn_software/apex/apex-latest.zip
unzip -d /tmp /tmp/apex-latest.zip
mv /tmp/apex apex-26-1
ln -sfn apex-26-1 apex

If ORDS is planned to run on a separate machine, repeat those steps on the ORDS machine, as the static files from the apex directory are also required on the ORDS machine.

Run APEX installer

From the apex directory on the database machine, run the APEX installer (change tablespaces according to your standards). The user SYS is mandatory, other users are not supported for running the APEX installation (https://apexapps.oracle.com/apex/r/apex_pm/ideas/details?idea=FR-3902&session=130781358621911).

cd /u01/apex

sql sys/<password>@orclpdb as sysdba @apexins sysaux sysaux temp /i/

or

export ORACLE_PDB_SID=orclpdb
sql / as sysdba @apexins sysaux sysaux temp /i/

Keep the session open. Check the APEX release

select * from apex_release;

Set the APEX Administrator password 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.

@apex_rest_config.sql

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

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

Install ORDS

First, we need to make some settings. Add the following to .bashrc and source it.

cat >> ~/.bashrc <<EOF
export _JAVA_OPTIONS="-Xms3000M -Xmx3000M"
export ORDS_CONFIG=/u01/ords-config
export ORDS_BASE=/u01/ords
export PATH=\$ORDS_BASE/bin:\$PATH
EOF
. ~/.bashrc    

Download the latest ORDS from https://download.oracle.com/otn_software/java/ords/ords-latest.zip and unzip the contents to your target directory (eg. /u01/ords-24-2 and reflect that in the $ORDS_BASE variable in .bashrc. Create the directories and optionally create a symlink:

cd /u01
mkdir ords-26-1
wget -P /tmp https://download.oracle.com/otn_software/java/ords/ords-latest.zip
unzip -d ords-26-1 /tmp/ords-latest.zip
ln -sfn ords-26-1 ords

Have your static document root folder at hand, or create a dummy document root and a log folder like

mkdir -p $ORDS_CONFIG/global/doc_root
mkdir -p $ORDS_CONFIG/log  

to avoid error messages because the document root folder does not exist.

ORDS requires at least JDK 17, but newer versions will also work. As opc user, install any JDK version 17 or higher eg.

sudo dnf in jdk-25-headless.x86_64

Optional: run ORDS installation without SYS

If you want to install ORDS without SYS AS SYSDBA, a user needs to be prepared for that task, see MOS note 2597045.1 or the ORDS Installation and Configuration Guide 1.4 ORDS Installer Privileges Script.

Create a user to install ORDS and run the script

grant connect, RESOURCE to ordsinstall identified by oracle;
@/u01/ords/scripts/installer/ords_installer_privileges.sql ordsinstall 

Mind the lines regarding APEX at the end of the script execution:

grant select on APEX_230200.WWV_FLOW_RT$MODULES to "ORDSINSTALL" with grant option;
grant select on APEX_230200.WWV_FLOW_POOL_CONFIG to "ORDSINSTALL" with grant option; 

As these explicit target the give APEX version, these need to be rerun with every new installed APEX version. But installation of a new APEX version requires SYSDBA rights anyways.

Run the ORDS installer

Either use the interactive installation or configure every parameter on the command line.

Interactive ORDS installation

run

ords install --interactive

The ORDS installation comes up with a menu

If ORDS should be installed without SYS, choose 1 and enter your given user from the previous chapter.

Select 3 and set a password for the ORDS_PUBLIC_USER .

Select 9 and set the APEX static resources location to something like /u01/apex/images/ .

Then continue by choosing A.

ORDS non-interactive silent installation

Alternatively, configure ORDS via command line parameters like shown below. Create a file with the passwords for the ordsinstall and ORDS_PUBLIC_USER passwords and pipe it into the command or if you do not want to use a password file, just enter the parameters when the installer starts. There will be no visible prompt, but it is there. Just type the password for SYS or ordsadmin, hit <Return>, enter the Password for apex_public_user, hit <Return> again and you are done. Some examples:

Basic connection with ORDS install user and interactive password entry

ords install --db-hostname localhost --db-port 1521 
    --db-servicename db26pdb.subxxxx.vcndb.oraclevcn.com --feature-sdw true 
    --log-folder $ORDS_CONFIG/log --admin-user ordsinstall --gateway-mode proxied 
    --    gateway-user APEX_PUBLIC_USER --proxy-user --password-stdin

Basic connection with SYS and password file

ords install --db-hostname localhost --db-port 1521 --db-servicename db26pdb.subxxxx.vcndb.oraevcn.com 
    --feature-sdw true --log-folder $ORDS_CONFIG/log --admin-user sys --gateway-mode proxied 
    --gateway-user APEX_PUBLIC_USER --proxy-user --password-stdin stdin < ~/password.txt

TNS connection with SYS and password file

ords --java-options "-DuseOracleHome=true" install --db-tns-dir $ORACLE_HOME/network/admin 
    --feature-sdw true --db-tns-alias orclpdb --admin-user sys --gateway-mode proxied 
    --gateway-user APEX_PUBLIC_USER --bequeath-connect --log-folder /u01/ords-config/log 
    --proxy-user --password-stdin < ~/password.txt

The non-interactive installation does not set the static resources location, so add this with

ords config set standalone.static.path /u01/apex/images/
ords config set standalone.static.context.path /i
ords config set jdbc.MaxLimit 30
ords config set jdbc.InitialLimit 20

If you plan to use the MongoDB-API, also run the following:

ords config set mongo.enabled true
ords config set mongo.access.log $ORDS_CONFIG/log
ords config set mongo.port 27017

Configuring ORDS via command line does not start ORDS afterward, so start ORDS with

ords serve

ORDS should come up without warnings.

Test ORDS connection

For a first try run the following on the same machine

curl -I http://localhost:8080/ords/

To test the connection from any other machine, we need to open the SQLNet port in the firewalls first.
As opc user run

sudo iptables -I INPUT 1 -p tcp --dport 8080 -j ACCEPT
sudo service iptables save
sudo iptables -S | grep 8080

Then we need to add the port to the Security List. As already done with port 1521 in the first chapter, navigate to your VCN and open the Default Security List. Under Ingress Rules, where you already have a rule for port 1521, create a similar rule for port 8080.

So the result should look as follows

Open http://<address>:8080 in a Browser, that should route to the ORDS landing page.

To test SQL Developer Web, we need at least one rest-enabled user. Connect to the orclpdb as SYS. For REST-enabling schemas, the executing user first needs an additional privilege, see MOS-Note 2863257.1

grant inherit privileges on user sys to ords_metadata;

Then run

BEGIN
    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                       p_schema => 'HR',
                       p_url_mapping_type => 'BASE_PATH',
                       p_url_mapping_pattern => 'hr',
                       p_auto_rest_auth => FALSE);
    commit;
END;
/

Klick on the ‘Go’ button below SQL Developer Web and login with hr. That should open the SDW homepage.

Navigate back to the ORDS Landing page or go directly to http://hostname:8080/ords/apex . Login to workspace INTERNAL with user ADMIN and the password you provided at installation. 

That should lead you to the APEX welcome page.

So APEX is up and running.

Use Oracle Autonomous Database SELECT AI feature with Mistral.ai

This Blog post shows how to use the Oracle 23ai SELECT SQL feature with Mistral.ai. Based on the blog-post Announcing Additional AI Providers for Oracle Autonomous Database Select AI, my colleague Michal Soszynski and me did some testing.

Let's do some preparations first. To work with the Mistral.ai API you need an API key, which requires an account and a subscription. For running a few tests, their free subscription is sufficient. 

To get the name of their actual model, just ask Mistral: what is the exact name of your model for use in API calls?

So it is mistral-large-latest. Now let us ask for the API endpoint: and what is the endpoint to use this model via api calls?

So it is https://api.mistral.ai/v1, we now have our basic parameters. In my case, Mistrala also offers a curl command to test the API, run it to check if your API key is valid

curl -X POST https://api.mistral.ai/v1/chat/completions \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer YOUR_MISTRAL_API_KEY" \
  -d '{
    "model": "mistral-large-latest",
    "messages": [
      {"role": "user", "content": "Hello, how are you?"}
    ]
  }'

As sys grant the execution right to your user:

grant EXECUTE on C##CLOUD$SERVICE.DBMS_CLOUD to scott;
grant EXECUTE on C##CLOUD$SERVICE.DBMS_CLOUD_AI to scott;

Also allow network access with an ACL

BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'api.mistral.ai',
    ace  => xs$ace_type(privilege_list => xs$name_list('http'),
                principal_name => 'SCOTT',
                principal_type => xs_acl.ptype_db));
END;

Continue as your user, create a credential for access to Mistral.

BEGIN
  DBMS_CLOUD.create_credential(
    credential_name => 'MISTRAL_CRED',
    username    => 'YOUR_MISTRAL_USERNAME',
    password    => 'YOUR_MISTRAL_API_KEY');
END; 

Give your user a few tables to test with, like Employees and Departmens from the HR demo schema or use whatever you have. Then create a profile for Mistral.AI, which is OpenAI compatible. But as it is compatible, but it is not OpenAI, we need the to extra parameters model and provider_endpoint. Luckily, we already asked Mistral.ai for them so we can use them in our create_profile call.

BEGIN
  DBMS_CLOUD_AI.create_profile(
    'MISTRAL',
    '{"credential_name": "MISTRAL_CRED",
      "provider": "openai",
      "object_list": [
        {"owner": "SCOTT", "name": "EMPLOYEES"},
        {"owner": "SCOTT", "name": "DEPARTMENTS"}      ],
      "model" : "mistral-large-2407",
      "provider_endpoint" : "api.mistral.ai/v1/chat"}'
    );
END;

Enable that profile for use with SELECT AI

EXEC DBMS_CLOUD_AI.set_profile('MISTRAL');

That’s it, start playing and don’t be ashamed of typos, eg. 

select ai how many employees ae working per derpartment;
select ai showsql how many employees ae working per derpartment;

Or try something in your language:

select ai welcher beruf verdient im Schnitt am wenigsten;

Have fun!

Updating SQLcl, setting up the environment: Oracle Database Developer OCI Environment Part 2

This is part 2 of my Oracle Database Developer OCI Environment series. It contains just a few steps to make the environment a bit more useable.

Update sqlcl on OCI

SQLcl is the modern replacement for SQL*Plus, it is called via sql instead of sqlplus and it comes with the newer Oracle database Releases. Let’s see what we have included:

The Base Database service comes with the SQLcl version that comes with the database. So in this case, Oracle 23.7 comes with SQLcl 24.3.2. In most cases, this will absolutely be fine. But as a developer, you might want to get your hands on the latest and greatest features. At the time of writing, SQLcl 25.1.1 is available, so let’s see how to get an update.

Exit the oracle session back to the opc user, so we have sudo access. Seach the yum repository for sqlcl

sudo dnf se sqlcl

There is a match, so install it

sudo dnf in sqlcl

Wait for the installer to complete

Switch back to the oracle user and check sqlcl. So we have a new sqlcl und /usr/bin/sql, which is version 25.1.0.101. That is close to the latest version 25.1.113 available on oracle.com/sqlcl . That should be close enough for normal usage.

But for getting into latest features, I recommend to always use the latest version of SQLcl directly from it’s homepage under https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip

or get it via wget

cd /u01
wget -P /tmp https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip
unzip -d /tmp /tmp/sqlcl-latest.zip
mv /tmp/sqlcl /u01/sqlcl-25-1-1
ln -sfn sqlcl-25-1-1 sqlcl

Doing so, when a new version arrives or you need to go back to an older one, just change the sqlcl link. Add it to the Path at your own risk.

echo export PATH=/u01/sqlcl/bin:\$PATH >> ~/.bashrc
~/.bashrc

Optional: configure SQLcl Syntax Highlighting

Create the directory /home/oracle/sql and inside a file login.sql with the following content (source: https://www.thatjeffsmith.com/archive/2023/08/your-ideal-oracle-database-command-line-experience):

mkdir ~/sql
cat >> ~/sql/login.sql <<EOF
SET statusbar ON
SET statusbar ADD editmode 
SET statusbar ADD txn
SET statusbar ADD timing
SET highlighting ON
SET highlighting keyword foreground green
SET highlighting identifier foreground white
SET highlighting string foreground yellow
SET highlighting NUMBER foreground cyan
SET highlighting comment background white
SET highlighting comment foreground black
alias cls=clear screen; 
EOF

Check highlightning with some sql

Optional: TNSNAMES

To use TNSNAMES, add ORCLPDB to tnsnames.ora

lsnrctl stop
echo "export TNS_ADMIN=$ORACLE_HOME/network/admin" >> ~/.bashrc
. ~/.bashrc
cat >> $TNS_ADMIN/tnsnames.ora <<EOF
ORCLPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclpdb.subxxxxxxxx.vcndb.oraclevcn.com)
    )
  )
EOF
lsnrctl start
tnsping orclpdb1

Optional: Demo Schema

To have a schema to test ORDS Database Actions, we will use the HR-Schema from Oracle.

Optional: to have some sample data to test with, the required scripts to setup the HR-schema can be found on GitHub under https://github.com/oracle-samples/db-sample-schemas. For easy setup, you could use hr_quick_start.sql from https://github.com/connormcd/misc-scripts.

mkdir git
cd git
git clone https://github.com/oracle-samples/db-sample-schemas
git clone https://github.com/connormcd/misc-scripts
cd misc-scripts
sql / as sysdba
alter session set container=orclpdb;
@hr_quick_start_23.sql
@sh_quick_start_23.sql

Do not forget to set a password for both schemas if needed.

Running the Oracle 23ai Autonomous Database Free Container Image

 With the Oracle Database 23ai comes a new deployment option, the free Autonomous Database Free Container Image. It gives developers the option to develop with an Autonomous Database even when not connected to the internet, eg. when travelling. This post shows how to set it up on Oracle Linux 9.4, in my case it is running in a VirtualBox VM on my Windows desktop PC. For details, see the official documentation, this is only meant as a quick runthrough.

Following the documentation, start with

podman machine init 
podman machine set --cpus 4 --memory 8192 
podman machine start 

Maybe it is just my system, but when running podman machine init I get the error qemu-system-x86_64: executable file not found in $PATH. A post on forums.oracle.com described the following solution, which worked on my system:

sudo ln -s /usr/libexec/qemu-kvm /usr/bin/qemu-system-x86_64

The podman command should run now, so follow the steps from the documentation.

podman pull container-registry.oracle.com/database/adb-free:latest-23ai
podman images container-registry.oracle.com/database/adb-free:latest-23ai

That image should show up on your system:

It is important to choose a password which is compliant with the images policy "The ADMIN_PASSWORD must be between 12 and 30 characters long and must include at least one uppercase letter, one lowercase letter, and one numeric".

Choosing a too simple password results in a runtime error, check with podman logs adb-free.

Then start a container with that image, in my case an ATP instance. 

podman run -d \
-p 1521:1522 \
-p 1522:1522 \
-p 8443:8443 \
-p 27017:27017 \
-e WORKLOAD_TYPE='ATP' \
-e WALLET_PASSWORD=*** \
-e ADMIN_PASSWORD=*** \
--cap-add SYS_ADMIN \
--device /dev/fuse \
--name adb-free \
container-registry.oracle.com/database/adb-free:latest-23ai

If nothing went wrong, you should see the container running.

So let's try some stuff. First open Database Actions by browsing https://localhost:8443/ords/sql-developer (and accept the self signed certificate), login with admin and your super secure password from above.

Also APEX works ootb, just login again with your password from above.

To access the ADB from outside with your favorite tooling, copy the wallet from to image to a folder. For SQLDeveloper, we need it as a zip.

podman cp adb-free:/u01/app/oracle/wallets/tls_wallet ~/tls_wallet
zip adb-free-wallet.zip tls_wallet/*

Use that wallet zip file in your tool of choice, in my case VSCodium with the Oracle SQL Developer Extension for VSCode.

Welcome to the Oracle 23ai Autonomous Database Free Container Image.

As long as your podman is ready to run and you respect the password compliancy, this is setup within minutes.

Oracle on Windows: out-of-place Patching with Virtual Account

This guide shows a way, how a Gold Image can be created from an Oracle Home on Windows and how that can be used to patch other Oracle Homes. This worked for me, but is not meant to be the only, best or officially recommended Oracle way.

Prepare the image source home

Say we have an unpatched 19.3 installed in c:\oracle\product\19.0.0\dbhome_1 with a database ORCL (non-PDB), installed with the option to use a Virtual Account, which is the default from the Oracle installer.

So the owner for the Oracle software home and datafiles has been created as ORA_OraDB19Home1_SVCACCTS by the installer.

Next we set up a new Oracle home and patch it, to make it the source of our gold image in c:\oracle\product\19.0.0\dbhome_2.

There is no standard oraenv.sh under Windows. As some tasks have to be done from the old and some from the new home, it helps to create two different oraenv scripts, eg. old-oraenv.cmd and new-oraenv.cmd , like in my environment:

old-oraenv.cmd

set ORACLE_BASE=C:\Oracle

set ORACLE_HOME=%ORACLE_BASE%\Product\19.0.0\dbhome_1
set PATH=%ORACLE_HOME%\bin;%PATH%
set PATH=%ORACLE_HOME%\perl\bin;%PATH%
set PATH=%ORACLE_HOME%\OPatch\;%PATH%
set PATH=%ORACLE_HOME%\jdk\bin\;%PATH%

new-oraenv.cmd

set ORACLE_BASE=C:\Oracle
set OLDORA_HOME=%ORACLE_BASE%\Product\19.0.0\dbhome_1
set ORACLE_HOME=%ORACLE_BASE%\Product\19.21\dbhome_3
set PATH=%ORACLE_HOME%\bin;%PATH%
set PATH=%ORACLE_HOME%\perl\bin;%PATH%
set PATH=%ORACLE_HOME%\OPatch\;%PATH%
set PATH=%ORACLE_HOME%\jdk\bin\;%PATH%

Run your oraenv.cmd, unpack the software (you can use tar xfz under Windows for unzip) and run setup.bat, see the Database Installation Guide 19c for Microsoft Windows. 

C:\app>oraenv.cmd

C:\app>mkdir %ORACLE_HOME%
C:\app>cd %ORACLE_HOME%
C:\app\oracle\product\19.0.0\dbhome_2>tar xfz z:\Install\Windows\WINDOWS.X64_193000_db_home.zip
C:\app\oracle\product\19.0.0\dbhome_2>setup.bat 

We only need to setup the software only and this is a single instance installation. Under Linux, we would just apply the RU and one-offs from the setup, but the options '-applyRU 'and '-applyOneOffs' are not available under Windows (see the comments on https://mikedietrichde.com/2022/05/17/simple-database-installation-with-applyru-and-applyoneoffs/, search for Windows).

Update opatch by following Doc ID 274526.1 from Oracle Support, in this case using opatch 12.2.0.1.41.

For the Gold Image, we will patch the 19.3 dbhome_2 up to 19.21 plus two one-off patches for the JVM. So download and unpack the patches to a given directory, in this example c:\app\oracle\patches. The patch numbers are 35638318, 35648110 and 35681552, these have to be applied manually by running opatch apply in every patch directory, starting with 35681552 which is the Bundle Patch 19.21.

Now the dbhome_2 is on 19.21 with two additional one-off patches and will serve as source for our Gold Image.

Create the Gold Image

Next, from the patched Oracle Home, we create a Gold Image following the documentation.

setup.exe -createGoldImage -destinationLocation c:\oracle\images 

Wait for the installer to finish, then you should have the newly created image in the directory stated by -destinationLocation. The dbhome_2 can be deleted with Oracle's deinstall program.

Out-of-place patching using the Gold Image

We now have an unpatched Oracle 19.3 home in dbhome_1 with the database ORCL and a gold image with version version 19.21 and two one-off patches.

For out-of-place patching, we need to create a new Oracle Home, where we unpack the Gold Image. 

C:\app>new-oraenv.cmd
C:\app>mkdir %ORACLE_HOME%
C:\app>cd %ORACLE_HOME%
C:\app\oracle\product\19.21\dbhome_3>tar xfz c:\app\oracle\images\db_home_2024-02-21_05-21-14PM.zip
C:\app\oracle\product\19.21\dbhome_3>setup

Run setup, choose software only and accept the default virtual account. If setup.exe crashes with a Java EXCEPTION_ACCESS_VIOLATION, follow MOS note 2772729.1.

rem --- Copy spfile and pwdfile to new ORACLE_HOME
copy %OLDORA_HOME%\database\SPFILEORCL.ORA %ORACLE_HOME%\database
copy %OLDORA_HOME%\database\PWDorcl.ora    %ORACLE_HOME%\database

Shutdown the database first, then copy the spfile and pwdfile to the new dbhome_3

The setup has created a new user ORA_OraDB19Home2_SVCACCTS and granted full control for the oradata directory, so the Oracle software from the new dbhome_2 can take over the files from the old dbhome_1.

On Unix/Linux systems, we would already be done, but on Windows we have services which need to be migrated to the new Oracle Home.

In the registry, we see that some services are linked to a given Oracle Home, so these need to be recreated.

set OLDORA_HOME=%ORACLE_BASE%\product\19.0.0\dbhome_1

rem --- use oradim to delete Oracle services ---
oradim -DELETE -SID orcl

For easier scripting, we point OLDORA_HOME to the Oracle home running the old version.

First delete the instance services with oradim, which will remove OracleServiceORCL, OracleVssWriterORCL and OracleJobSchedulerORCL. 

The Oracle Home specific services will remain. These will be deleted, when deleting the dbhome_1. 

rem --- stop remaining Oracle services ---

sc stop OracleOraDB19Home1MTSRecoveryService
sc config OracleOraDB19Home1MTSRecoveryService start= disabled
sc stop OracleOraDB19Home1TNSListener

sc config OracleOraDB19Home1TNSListener start=disabled

But as one advantage of out-of-place patching is, that you can go back to the old version, you probably want to keep them for some time, so we will just disable them.

The remaining services should now be stopped and disabled.

rem --- create new services for new Oracle home

oradim -NEW -SID orcl -STARTMODE auto -SRVCSTART system -SPFILE -PFILE %ORACLE_HOME%\database\SPFILEORCL.ORA

Then use oradim again to create the instance specific services

It recreates the OracleServiceorcl, OracleVssWriterorcl and OracleJobSchedulerorcl for the new Oracle home.

Double checking from the Registry, that we are now running on the patched dbhome_3

Also from SQLPlus, everything looks fine.

rem --- recreate remaining services
%ORACLE_HOME%\bin\oramtsctl -new
%ORACLE_HOME%\bin\lsnrctl start
sc config "OracleOraDB19Home3TNSListener" start= auto

The formerly disabled services from the old home need to be recreated for the new home. Starting the Listener automatically creates a new Service for the new home. That should be configured to start automatically.

Now we have migrated all services to the new Oracle home.

rem --- start the database and run datapatch
rem --- not needed with service auto start --- @echo startup | sqlplus / as sysdba
rem --- @echo alter pluggable database all open | sqlplus / as sysdba
rem --- @echo show pdbs | sqlplus / as sysdba

@echo @?/rdbms/admin/utlrp.sql | sqlplus / as sysdba
%ORACLE_HOME%\OPatch\datapatch

Before running the datapatch, make sure that all pds are open if already running on Mutlitenant architecture. When the open state has been saved by using ALTER PLUGGABLE DATABASE ... SAVE STATE, that step can be skipped. Here we open all PDB's before running the datapatch 

The database software and data dictionary should now be on the new version.

rem --- apply optimizer fixes for every PDB
@echo @?/rdbms/admin/dbmsoptim.sql | sqlplus / as sysdba
@echo @?/rdbms/admin/prvtoptim.plb | sqlplus / as sysdba
@echo execute dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES'); | sqlplus / as sysdba

Last step is enabling the optimizer fixes for the new version. That's it, we are running on 19.21 after out-of-place patching.

Finally, after some time, you might want to delete the old 19.3 Oracle home dbhome_1. Run %ORACLE_HOME%\deinstall\deinstall.bat to clean up the old installation.

After the deinstallation of the old home, only the patched 19.21 services from dbhome_3 will remain.

The complete scripting can be found on GitLab.