We have upgraded Oracle Apex in the previous blog and next have to plan for below things
- Apex static images update for upgraded version
- ORDS Upgrade to stable version
Prerequisites
- Downloads the upgraded apex version in oracle RDS ( Apex 21.1.2 )
- Download latest stable version of ORDS 19.4
- Make sure you have already configured Oracle Client 19C and Java 1.8 is installed
Apex Static Image Upgrade :
Before upgrade apex images , we will receive below error while login apex workspace page
- Rename the existing apex folder from D:\apex to D:\apex_5.1.4
- Unzip the apex_21.1_en.zip folder into apex folder
- Go to apex utilities folder
- Stop the ORDS running service
Connect Oracle 19C in SQL Plus
C:\Windows\system>D:
D:\>cd apex
D:\apex>cd utilities
D:\apex\utilities>
D:\apex\utilities>sqlplus dataadmin@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dataadmin.cwt1dgu2zxag.ap-southeast-1.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Enter Password:
SQL> @reset_image_prefix.sql
Enter the Application Express image prefix [/i/] https://static.oracle.com/cdn/apex/21.1.2/
...Changing Application Express image prefix
NEW_IMAGE_PREFIX
------------------------------------------
https://static.oracle.com/cdn/apex/21.1.2/
PL/SQL procedure successfully completed.
...Recreate APEX global
PL/SQL procedure successfully completed.
Commit complete.
Image Prefix update complete
Disconnected from Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
D:\apex\utilities>
Once its completed , we will able to browse the apex pages without any issues
ORDS Upgrade Paths
Step 1 :
Rename existing ords folder as ords_3.0.13 . As mentioned in prerequisites , unzip the downloaded ords-19.4.6.142.1859 into ords folder
Step 2 :
Copy the D:/apex/images folder into D:/ords/
Step 3:
Uninstall the existing ORDS version from Oracle RDS ,
D:\ords>java -jar ords.war uninstall Specify the database connection type to use. Enter number for [1] Basic [2] TNS [3] Custom URL [1]: Enter the name of the database server [localhost]:dataadmin.cwt1dgu2zxag.ap-southeast-1.rds.amazonaws.com Enter the database listen port [1521]: Enter 1 to specify the database service name, or 2 to specify the database SID [1]: Enter the database service name:ORCL Requires to login with administrator privileges to verify Oracle REST Data Services schema. Enter the administrator username:dataadmin Enter the database password for dataadmin: Confirm password: Connecting to database user: dataadmin url: jdbc:oracle:thin:@//dataadmin.cwt1dgu2zxag.ap-southeast-1.rds.amazonaws.com:1521/ORCL Retrieving information. Uninstalling Oracle REST Data Services ... Log file written to C:\Users\datablogsadmin\ords_uninstall_core_2022-03-16_133300_00152.log Completed uninstall for Oracle REST Data Services. Elapsed time: 00:00:07.840Step 4 :
Set Config Directory
java -jar ords.war configdir D:\config
Step 5 :
Update passwords and unlock below accounts in Oracle RDS
- APEX_PUBLIC_USER
- APEX_LISTENER
- APEX_REST_PUBLIC_USER
Connect Oracle 19C in SQL Plus
D:\apex\utilities>sqlplus dataadmin@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dataadmin.cwt1dgu2zxag.ap-southeast-1.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Enter Password:
SQL>alter user APEX_PUBLIC_USER account unlock identified by Admin123;
SQL>exec rdsadmin.rdsadmin_run_apex_rest_config('Admin123','Admin123');
SQL>ALTER USER APEX_REST_PUBLIC_USER account unlock IDENTIFIED BY Admin123;
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_OBJECTS', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_ROLE_PRIVS', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_TAB_COLUMNS', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('USER_CONS_COLUMNS', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('USER_CONSTRAINTS', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('USER_OBJECTS', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('USER_PROCEDURES', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('USER_TAB_COLUMNS', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('USER_TABLES', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('USER_VIEWS', 'DATAADMIN', 'SELECT', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('WPIUTL', 'DATAADMIN', 'EXECUTE', true);
SQL>exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_SESSION', 'DATAADMIN', 'EXECUTE', true);
SQL>EXEC rdsadmin.rdsadmin_util.grant_apex_admin_role;
SQL>grant APEX_ADMINISTRATOR_ROLE to DATAADMIN;
Once its executed , proceed the ORDS InstallationInstall the required database naming configuration setup for ORDS , By default it will install as apex
D:\ords>java -jar ords.war setup --database dataadmin Specify the database connection type to use. Enter number for [1] Basic [2] TNS [3] Custom URL [1]: Enter the name of the database server [localhost]:dataadmin.cwt1dgu2zxag.ap-southeast-1.rds.amazonaws.com Enter the database listen port [1521]: Enter 1 to specify the database service name, or 2 to specify the database SID [1]: Enter the database service name:ORCL Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:1 Enter the database password for ORDS_PUBLIC_USER: Confirm password: Requires to login with administrator privileges to verify Oracle REST Data Services schema. Enter the administrator username:dataadmin Enter the database password for dataadmin: Confirm password: Connecting to database user: dataadmin url: jdbc:oracle:thin:@//dataadmin.cwt1dgu2zxag.ap-southeast-1.rds.amazonaws.com:1521/ORCL Retrieving information. Enter the default tablespace for ORDS_METADATA [SYSAUX]: Enter the temporary tablespace for ORDS_METADATA [TEMP]: Enter the default tablespace for ORDS_PUBLIC_USER [USERS]: Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]: Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step. If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]: Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]: Enter the database password for APEX_PUBLIC_USER: Confirm password: Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]: Enter the database password for APEX_LISTENER: Confirm password: Enter the database password for APEX_REST_PUBLIC_USER: Confirm password: Enter a number to select a feature to enable [1] SQL Developer Web [2] REST Enabled SQL [3] None [1]:1 2022-03-16T05:35:15.021Z INFO reloaded pools: [|dataadmin||, |dataadmin|al|, |dataadmin|rt|, |dataadmin|pu|] Installing Oracle REST Data Services version 19.4.6.r1421859 ... Log file written to C:\Users\datablogsadmin\ords_install_core_2022-03-16_133515_00223.log ... Verified database prerequisites ... Created Oracle REST Data Services proxy user ... Created Oracle REST Data Services schema ... Granted privileges to Oracle REST Data Services ... Created Oracle REST Data Services database objects ... Log file written to C:\Users\datablogsadmin\ords_install_datamodel_2022-03-16_133527_00932.log ... Log file written to C:\Users\datablogsadmin\ords_install_apex_2022-03-16_133530_00191.log Completed installation for Oracle REST Data Services version 19.4.6.r1421859. Elapsed time: 00:00:17.247
Step 7 :
Once installed ORDS , if you get any errors on ORDS users . Run below command in SQL PLUS
ALTER USER ORDS_PUBLIC_USER account unlock IDENTIFIED BY Admin123;
ALTER USER ORDS_METADATA account unlock IDENTIFIED BY Admin123;
Step 8 :
Start the ORDS Service
java -jar ords.war
Step 9 :
If you need to map your ords link with multiple schemas , its must have URL mapping . To avoid confusion to access multiple workspaces
D:\ords>java -jar ords.war map-url --workspace-id ss --type base-path /datablogs01 dataadmin
Its all completed , if you have any issues and doubts please contact us
Authors : 👷 Krishna and 👷 Selvackp