Why upgrade?
Because Oracle APEX is constantly adding new features and the new versions include all those bug fixes, so unless you have paid support, you can only fix those bugs via upgrade.
But I can follow Oracle's Documentation on how to upgrade...
Yes, but OXAR is not a vanilla installation of Oracle APEX.
And one of the upsides of using OXAR is that every installation is pretty much the same because the installation process eliminated the choice of directories, options, etc..
This guide works for APEX 19.1, as a matter of fact I upgraded to 19.1 using this guideline I had written a few weeks ago.
So in reality (at least in my head) the vanilla OXAR is more vanilla than a vanilla Oracle installation. That's vanilla3 !
- wait, wha?? - Yes, just keep reading..
Before you start, check the space available on SYSTEM
Who would've thought that SYSTEM (it's datafile to be precise) is limited to 600MB! (Remember we're talking in the context of OXAR).
So you're pretty close to fill it up, might as well increase it because 18.2 or 19.1 create a lot of objects and you can run out of space in the middle.. In this guide I recommend 800MB which is enough space with some room left.
SQL> CONNECT SYS as SYSDBA
Enter password: {SYS_password}
SQL> alter database datafile '/u01/app/oracle/oradata/XE/system.dbf' AUTOEXTEND ON MAXSIZE 800M;
Steps I followed
Notes:
- The following OS commands must be run as root.
- Use SQLPlus to connect.
- I use dropbox but you can use whatever you prefer.
- In this example I had APEX 5.1.4 and ORDS 3.0.9 OXAR Installation
- You want to upgrade ORDS before APEX as it's an APEX 18.2 requirement to have ORDS 3.0.12 and above (see my other BLOG).
pm2 stop node4ords
systemctl stop tomcat@oxar
cd ~
wget https://www.dropbox.com/s/{secret_link}/apex_18.2.zip
unzip apex_18.2.zip
cd apex
sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: {SYS_password}
SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/
sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: {SYS_password}
SQL> @apex_epg_config.sql /root
SQL> exit
sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: {SYS_password}
SQL> @apxchpwd.sql
SQL> exit
If your OXAR configuration enabled the run of ACL (via OOS_ORACLE_ACL_APEX_ALL_YN = Y in the config.properties of your oxar installation) Note: set to Y by default then run the following:
sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: {SYS_password}
DECLARE
ACL_PATH VARCHAR2(4000);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_180200
-- the "connect" privilege if APEX_180200 does not have the privilege yet.
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_180200',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_180200', TRUE, 'connect');
END IF;
EXCEPTION
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_180200', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;
As root run the following commands:
cd /ords/apex_images
unalias cp
cp -r /root/apex/images/* .
systemctl start tomcat@oxar
pm2 start node4ords
Last step is to cleanup:
cd ~
rm -rf apex_18.2.zip apex
You're done!
Notes:
Please backup prior you make any changes, and test, test and more test
More notes...
I realized that after the APEX upgrade ORDS was not behaving and throwing errors like objects not initialized on the browsers console..
This is how I fixed it:
cd /var/lib/tomcat/webapps
java -jar ords.war validate --database apex
(You will need to provide SYS password.)
Let me know if you have any questions.
Thanks
Gaspar
ps. Latest updates to this Blog will be on Github: