Sunday, November 22, 2020

My steps to upgrade to Oracle APEX 20.2, ORDS 20.3 and AOP 20.3

In the last month, the three major components of an Oracle APEX environment got new versions:
- on October 29, 2020, Oracle REST Data Services (ORDS) 20.3 
- on November 15, 2020, APEX Office Print (AOP) 20.3 

In general, the releases are as follows (based on findings from 2018 onwards):
- APEX has two releases a year, around March/April and one around September/October.
- ORDS brings out one release per quarter, but from time to time they might skip a quarter.
- AOP has three major releases per year and some smaller releases in between.

Do we upgrade our production environment with every new release? 
- APEX: yes, we upgrade typically once the patchset bundle is available, so every 6 months
- ORDS: no, we upgrade ORDS once a year, unless we need a specific feature
- AOP: yes, at least the major releases are followed for the on-premises AOP release. AOP Cloud always has the latest release by default.

When I look at our customers, many don't upgrade that fast. The bigger the corporation the slower the uptake, going from every 4 years to yearly upgrades.

This weekend we upgraded an environment to bring everything to the latest release. Here's a breakdown of my tasks and some tips.

1. Download all the latest software

- Go to the website Oracle Application Express (APEX) 20.2, click the download link, once you log in the download starts.  If you want to be on the latest patch set, then also click the link to download Patch Set Bundle for Oracle APEX 20.2  (32006852). You will need to have a valid support contract with Oracle in order to download the patchset bundle.

- Go to the website Oracle REST Data Services (ORDS) 20.3, click the download link, once you log in the download starts. 

- Go to the website APEX Office Print (AOP) 20.3, login (or signup if you don't have an account yet), go to the downloads section, and download the on-premise version of Linux or Windows.

2. Prepare installs

- Copy all the zip files from step 1 to the server(s). In our case, all of the above software is running on the same server, but many people have separate servers for the DB (where APEX is), ORDS, and AOP.

- Prepare the directories and unzip

- I typically export all Workspaces and APEX apps before doing the upgrade, in the case of APEX 20.1, and store them somewhere. This way I always have a copy of the app in that APEX version. Here's the command to do so:

/usr/bin/java oracle.apex.APEXExport -db localhost:1521/APEX_PDB -user xxx -password xxx -expWorkspace > workspaces.txt

/usr/bin/java oracle.apex.APEXExport -db localhost:1521/APEX_PDB -user xxx -password xxx -instance > applications.txt

3. Install Oracle APEX

Just as with the upgrade to Oracle APEX 20.1 I blogged about previously, I followed the same steps to maximize uptime during the APEX upgrade.

During the first two phases, the Oracle APEX apps were still running:

SQL> @apexins1.sql sysaux sysaux temp /i/

SQL> @apexins2.sql sysaux sysaux temp /i/

The first two phases took about 7 minutes.

After phase 2, I stopped ORDS as specified in the doc, but which is not necessary anymore as ORDS is smart and knows APEX is upgrading and automatically suspends activity by itself. 
(As I wanted to upgrade ORDS too I stopped it - read more in ORDS install about that)

SQL> @apexins3.sql sysaux sysaux temp /i/

It completed in about 3.30 minutes.

At the same time, phase 3 was running, I copied the images folder.
Note: you can also use the CDN by running @reset_image_prefix.sql after phase 3 and specifying:

With the above steps, the APEX 20.2 install completed. Now, I applied the patchset bundle, which took about 1 minute.

SQL> @catpatch

Finally, I ran the following command to allow APEX apps to access web services:

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

That was pretty much it to upgrade our Oracle APEX 20.1 to 20.2 release.

4. Install ORDS

Every pluggable database and APEX version has its own ORDS. Whenever I upgrade ORDS, I unzip it in a new directory. I copy the config of the previous ORDS version (the whole ords directory) in the new directory where I unzipped ORDS. In my case, I unzipped ORDS 20.3 in /u01/apex202/ords.
Next to the config, I also copy the start and stop scripts and create a logs directory.

Next, I run the ORDS command to set the config directory in the new ords.war file.

java -jar ords.war configdir /u01/apex202/ords

To upgrade ORDS I run: 

java -jar ords.war 

This will connect to the database, identify the meta-data, upgrade it, and run ORDS.
Once it's complete, I stop ORDS and run it with the script, so proper logging is done. 
Note, in this environment we run ORDS in standalone mode.

Now, with the install of ORDS, I made a mistake... I thought to be smart, and upgrade ORDS at the same time when APEX was doing the final install step (see in APEX, when I stopped ORDS).
This wasn't so smart to do! It looks like ORDS is looking at the APEX version to generate some repository views. It messed up things, as ORDS saw still APEX 20.1 as the flip of APEX versions was still going on. Anyway, I got an error. So I decided to wait until the APEX upgrade was finished, then I ran the ORDS command again and it completed fine.  All things were validated ok, start ORDS and we were up-and-running in APEX 20.2 and ORDS 20.3.  Tested the apps, all ok... but for one domain, after a few minutes, I saw an error in the ORDS logs:

2020-11-21T11:19:38.182Z INFO        <CaNhATfOdgVPIOr6aSAtzw> GET /ords/f?p=XXXX:LOGIN:0 403
ProcedureForbiddenException [statusCode=403, logLevel=INFO, reasons=[Access to the procedure named: f is denied. ]]

I still can't explain why it happened. I solved it by running ORDS validate again and restarting ORDS.

java -jar ords.war validate

My guess is, that the connection pool was messed up by running ORDS while APEX was not finished yet installing. So my recommendation is to always install APEX first and only once that is complete upgrade ORDS (if you want to do it at the same moment), or the other way around, install ORDS first and once complete upgrade APEX.

5. Install AOP

Upgrading AOP is the most simple of the three components, as it doesn't have a repository. Unzip the download in a new directory, copy the config (aop_config.json) of the running AOP to the new folder and activate AOP:

./APEXOfficePrintLinux64 -a

Stop the old version of AOP, start the new version of AOP, and done.

Happy upgrading!


Marko Gorički said...

If you are on DB version 12 or more you don't need to run the ACL script. It should be done with APEX installation automatically (see coreins3.sql).

Anonymous said...

Don't you need to move your license file as well (to the new instance of AOP)?

Dimitri Gielis said...

I would reactivate the AOP Server. (use -a)

Unknown said...

I will use the -a. So there is no code (PL/SQL, APEX, etc.) that needs manual updating?

carlino said...

Hi, when I execute:


This page isn’t working right now10.48.41.10
didn’t send any data

I've already review some config files, parameters bt i can´t to pen the APEX on that web.

any idea?

Thanks in advanced!