Posted by Just a quick blog post today showing how easy it was for me to upgrade from ORDS 22.1 to the latest ORDS 22.4.
It was just a case of downloading the software, and running it in “install” mode whilst pointing to the existing config directory. (Obviously, for environments more serious than my local PC, I would be having a good read of the docs to ensure I’m doing everything I need to)
C:\Users\hamcdc>type x:\bin\ords.bat
cd /d c:\oracle\ords.221\bin
ords.exe --config c:\oracle\ords.conf serve
C:\>cd \oracle\ords.224\bin
C:\oracle\ords.224\bin>ords.exe --config c:\oracle\ords.conf install
ORDS: Release 22.4 Production on Fri. Jan. 06 10:00:22 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
/C:/oracle/ords.conf/
Oracle REST Data Services - Interactive Install
Enter a number to select the type of installation
[1] Install or upgrade ORDS in the database only
[2] Create or update a database pool and install/upgrade ORDS in the database
[3] Create or update a database pool only
Choose [1]:
Enter a number to select the database pool to use or specify the database connection
[1] default jdbc:oracle:thin:@//localhost:1519/pdb1
[2] Specify the database connection
Choose [1]:
Provide database user name with administrator privileges.
Enter the administrator username: sys
Enter the database password for SYS AS SYSDBA:
Connecting to database user: SYS AS SYSDBA url: jdbc:oracle:thin:@//localhost:1519/pdb1
Retrieving information.
Connecting to database user: ORDS_PUBLIC_USER url: jdbc:oracle:thin:@//localhost:1519/pdb1
2023-01-06T10:00:38.270Z INFO Upgrading Oracle REST Data Services schema 22.1.0.r1051723 to version 22.4.0.r3401044 in PDB1
------------------------------------------------------------
Date : 06 Jan. 2023 10:00:38
Release : Oracle REST Data Services 22.4.0.r3401044
Type : ORDS Upgrade from 22.1.0.r1051723 to 22.4.0.r3401044
Database : Oracle Database 19c Enterprise Edition
DB Version : 19.16.0.0.0
------------------------------------------------------------
Container Name: PDB1
------------------------------------------------------------
[*** Info: Upgrade intialize ]
[*** script: ords_alter_session_script.sql]
PL/SQL procedure successfully completed.
[*** script: ords_init_upgrade.sql]
Session altered.
PL/SQL procedure successfully completed.
[*** script: ords_schema_version.sql]
Session altered.
PL/SQL procedure successfully completed.
Commit complete.
[*** Info: Upgrade version: 22.1.1.154 teamPath: upgrade/22.1.1.154/core/ ]
[*** script: upg_revoke_inherit_priv.sql]
REVOKE INHERIT PRIVILEGES ON USER ORDS_PUBLIC_USER FROM PUBLIC
PL/SQL procedure successfully completed.
[*** script: ords_validate.sql]
Procedure VALIDATE_ORDS compiled
[*** script: upg_ords_database_objects.sql]
PL/SQL procedure successfully completed.
[*** script: oauth_internal.pls]
Package OAUTH_INTERNAL compiled
[*** script: oauth.pls]
Package OAUTH compiled
[*** script: ords.pls]
Package ORDS compiled
[*** script: ords_admin.pls]
Package ORDS_ADMIN compiled
[*** script: ords_services.pls]
Package ORDS_SERVICES compiled
[*** script: ords_services_internal.pls]
Package ORDS_SERVICES_INTERNAL compiled
[*** script: oauth_internal.plb]
Package Body OAUTH_INTERNAL compiled
[*** script: oauth.plb]
Package Body OAUTH compiled
[*** script: ords.plb]
Package Body ORDS compiled
[*** script: ords_admin.plb]
Package Body ORDS_ADMIN compiled
[*** script: ords_export.plb]
Package Body ORDS_EXPORT compiled
[*** script: ords_internal.plb]
Package Body ORDS_INTERNAL compiled
[*** script: ords_security.plb]
Package Body ORDS_SECURITY compiled
[*** script: ords_services.plb]
Package Body ORDS_SERVICES compiled
[*** script: ords_services_internal.plb]
Package Body ORDS_SERVICES_INTERNAL compiled
[*** Info: Upgrade version: 22.1.1.154 teamPath: upgrade/22.1.1.154/apex/ ]
[*** script: ords_apex_repair.plb]
Package Body ORDS_APEX_REPAIR compiled
[*** script: ords_apex_repair_internal.plb]
Package Body ORDS_APEX_REPAIR_INTERNAL compiled
[*** Info: Upgrade version: 22.1.1.154 teamPath: upgrade/22.1.1.154/scheduler/ ]
[*** script: ords_sdw_sched_jobs.pls]
Package ORDS_SDW_SCHED_JOBS compiled
[*** script: ords_sdw_sched_rprt.pls]
Package ORDS_SDW_SCHED_RPRT compiled
[*** script: ords_sdw_sched_jobs.plb]
Package Body ORDS_SDW_SCHED_JOBS compiled
[*** script: ords_sdw_sched_rprt.plb]
Package Body ORDS_SDW_SCHED_RPRT compiled
[*** script: ords_sdw_scheduler.plb]
Package Body ORDS_SDW_SCHEDULER compiled
[*** script: ords_version.sql]
Session altered.
PL/SQL procedure successfully completed.
[*** Info: Upgrade version: 22.2.0.155 teamPath: upgrade/22.2.0.155/core/ ]
[*** script: upg_ords_database_objects.sql]
PL/SQL procedure successfully completed.
[*** script: upg_ords_triggers.sql]
Trigger SEC_OBJECT_ALLOW_LIST_TRG compiled
[*** script: ords_runtime_privs.sql]
PL/SQL procedure successfully completed.
[*** script: ords_schema_mapping.sql]
INFO: Configuring ORDS_PUBLIC_USER to map APEX Workspaces and ORDS schemas
Session altered.
Configuring APEX and ORDS schemas for url mapping
Made APEX_PUBLIC_USER proxiable from ORDS_PUBLIC_USER
Made APEX_REST_PUBLIC_USER proxiable from ORDS_PUBLIC_USER
APEX_POOL_CONFIG Synonym exists
Created ORDS_METADATA.APEX_WWV_FLOW_POOL_CONFIG as view over
APEX_220200.APEX_POOL_CONFIG
PL/SQL procedure successfully completed.
INFO: Completed configuring ORDS_PUBLIC_USER to map APEX Workspaces and ORDS Schemas
Session altered.
[*** script: ords_admin.pls]
Package ORDS_ADMIN compiled
[*** script: ords_internal.pls]
Package ORDS_INTERNAL compiled
[*** script: ords_services_internal.pls]
Package ORDS_SERVICES_INTERNAL compiled
[*** script: ords_util.pls]
Package ORDS_UTIL compiled
[*** script: ords_admin.plb]
Package Body ORDS_ADMIN compiled
[*** script: ords_internal.plb]
Package Body ORDS_INTERNAL compiled
[*** script: ords_services_internal.plb]
Package Body ORDS_SERVICES_INTERNAL compiled
[*** script: ords_util.plb]
Package Body ORDS_UTIL compiled
[*** script: ords_validate.sql]
Procedure VALIDATE_ORDS compiled
[*** Info: Upgrade version: 22.2.0.155 teamPath: upgrade/22.2.0.155/scheduler/ ]
[*** script: ords_sdw_scheduler.pls]
Package ORDS_SDW_SCHEDULER compiled
[*** script: ords_sdw_sched_jobs.pls]
Package ORDS_SDW_SCHED_JOBS compiled
[*** script: ords_sdw_sched_rprt.pls]
Package ORDS_SDW_SCHED_RPRT compiled
[*** script: ords_sdw_scheduler.plb]
Package Body ORDS_SDW_SCHEDULER compiled
[*** script: ords_sdw_sched_jobs.plb]
Package Body ORDS_SDW_SCHED_JOBS compiled
[*** script: ords_sdw_sched_rprt.plb]
Package Body ORDS_SDW_SCHED_RPRT compiled
[*** script: ords_version.sql]
Session altered.
PL/SQL procedure successfully completed.
[*** Info: Upgrade version: 22.3.0.273 teamPath: upgrade/22.3.0.273/core/ ]
[*** script: add_grant_privs.sql]
GRANT READ ON SYS.DBA_USERS TO ORDS_METADATA
PL/SQL procedure successfully completed.
[*** script: ords_util.pls]
Package ORDS_UTIL compiled
[*** script: ords_admin.pls]
Package ORDS_ADMIN compiled
[*** script: ords_internal.pls]
Package ORDS_INTERNAL compiled
[*** script: ords_util.plb]
Package Body ORDS_UTIL compiled
[*** script: ords_admin.plb]
Package Body ORDS_ADMIN compiled
[*** script: ords_internal.plb]
Package Body ORDS_INTERNAL compiled
[*** script: upg_properties.sql]
PL/SQL procedure successfully completed.
[*** Info: Upgrade version: 22.3.0.273 teamPath: upgrade/22.3.0.273/scheduler/ ]
[*** script: ords_sdw_scheduler.pls]
Package ORDS_SDW_SCHEDULER compiled
[*** script: ords_sdw_sched_jobs.pls]
Package ORDS_SDW_SCHED_JOBS compiled
[*** script: ords_sdw_sched_rprt.pls]
Package ORDS_SDW_SCHED_RPRT compiled
[*** script: ords_sdw_scheduler.plb]
Package Body ORDS_SDW_SCHEDULER compiled
[*** script: ords_sdw_sched_jobs.plb]
Package Body ORDS_SDW_SCHED_JOBS compiled
[*** script: ords_sdw_sched_rprt.plb]
Package Body ORDS_SDW_SCHED_RPRT compiled
[*** Info: Upgrade version: 22.3.0.273 teamPath: upgrade/22.3.0.273/apex/ ]
[*** script: ords_apex_repair_internal.pls]
Package ORDS_APEX_REPAIR_INTERNAL compiled
[*** script: ords_apex_repair_internal.plb]
Package Body ORDS_APEX_REPAIR_INTERNAL compiled
[*** script: ords_apex_repair.plb]
Package Body ORDS_APEX_REPAIR compiled
[*** script: allow_apex_entry_points.sql]
Found APEX 22.2.1. Migrating APEX entry points to ORDS PL/SQL Procedure Gateway
Allow List
PL/SQL procedure successfully completed.
[*** script: ords_version.sql]
Session altered.
PL/SQL procedure successfully completed.
[*** Info: Upgrade version: 22.4.0.320 teamPath: upgrade/22.4.0.320/core/ ]
[*** script: upg_ords_database_objects.sql]
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
[*** script: oauth_internal.pls]
Package OAUTH_INTERNAL compiled
[*** script: oauth.pls]
Package OAUTH compiled
[*** script: ords_internal.pls]
Package ORDS_INTERNAL compiled
[*** script: ords.pls]
Package ORDS compiled
[*** script: ords_export.pls]
Package ORDS_EXPORT compiled
[*** script: ords_util.pls]
Package ORDS_UTIL compiled
[*** script: oauth_internal.plb]
Package Body OAUTH_INTERNAL compiled
[*** script: oauth.plb]
Package Body OAUTH compiled
[*** script: ords_internal.plb]
Package Body ORDS_INTERNAL compiled
[*** script: ords.plb]
Package Body ORDS compiled
[*** script: ords_export.plb]
Package Body ORDS_EXPORT compiled
[*** script: ords_util.plb]
Package Body ORDS_UTIL compiled
[*** script: ords_validate.sql]
Procedure VALIDATE_ORDS compiled
[*** script: upg_ords_pub_synonyms_grants.sql]
PL/SQL procedure successfully completed.
[*** Info: Upgrade version: 22.4.0.320 teamPath: upgrade/22.4.0.320/apex/ ]
[*** script: ords_apex_repair_internal.plb]
Package Body ORDS_APEX_REPAIR_INTERNAL compiled
[*** script: ords_apex_sso.pls]
Package ORDS_APEX_SSO compiled
[*** script: ords_apex_sso.plb]
Package Body ORDS_APEX_SSO compiled
[*** Info: Upgrade version: 22.4.0.320 teamPath: upgrade/22.4.0.320/scheduler/ ]
[*** script: sch_grant_privs.sql]
INFO: Grant object and system privileges to ORDS owner
PL/SQL procedure successfully completed.
INFO: Completed granting object and system privileges for scheduler team
[*** script: ords_version.sql]
Session altered.
PL/SQL procedure successfully completed.
[*** Info: Upgrade status ]
[*** script: ords_schema_mapping.sql]
INFO: Configuring ORDS_PUBLIC_USER to map APEX Workspaces and ORDS schemas
Session altered.
Configuring APEX and ORDS schemas for url mapping
Made APEX_PUBLIC_USER proxiable from ORDS_PUBLIC_USER
Made APEX_REST_PUBLIC_USER proxiable from ORDS_PUBLIC_USER
APEX_POOL_CONFIG Synonym exists
Created ORDS_METADATA.APEX_WWV_FLOW_POOL_CONFIG as view over
APEX_220200.APEX_POOL_CONFIG
PL/SQL procedure successfully completed.
INFO: Completed configuring ORDS_PUBLIC_USER to map APEX Workspaces and ORDS Schemas
Session altered.
[*** script: ords_migrate_grant_priv.sql]
INFO: Verify if Application Express exists to setup the migration privileges for
ORDS.
INFO: Completed setting up the APEX REST migration privileges for ORDS.
PL/SQL procedure successfully completed.
Session altered.
[*** script: ords_version.sql]
Session altered.
PL/SQL procedure successfully completed.
[*** script: ords_completed_upgrade.sql]
Session altered.
INFO: 18:00:48 Validating objects for Oracle REST Data Services.
VALIDATION: 18:00:48 Starting validation for schema: ORDS_METADATA
VALIDATION: 18:00:48 Validating objects
VALIDATION: 18:00:50 INFO: VIEW USER_ORDS_SERVICES2 is not in the validation
list.
VALIDATION: 18:00:50 Validating roles granted to ORDS_METADATA and
ORDS_PUBLIC_USER
VALIDATION: 18:00:50 Validating ORDS Public Synonyms
alter public synonym "DBA_ORDS_SCHEMAS" compile
Public Synonym DBA_ORDS_SCHEMAS compiled.
alter public synonym "ORDS" compile
Public Synonym ORDS compiled.
alter public synonym "ORDS_ADMIN" compile
Public Synonym ORDS_ADMIN compiled.
alter public synonym "ORDS_OPER" compile
Public Synonym ORDS_OPER compiled.
alter public synonym "ORDS_SERVICES" compile
Public Synonym ORDS_SERVICES compiled.
alter public synonym "USER_ORDS_APPROVALS" compile
Public Synonym USER_ORDS_APPROVALS compiled.
alter public synonym "USER_ORDS_CLIENTS" compile
Public Synonym USER_ORDS_CLIENTS compiled.
alter public synonym "USER_ORDS_CLIENT_PRIVILEGES" compile
Public Synonym USER_ORDS_CLIENT_PRIVILEGES compiled.
alter public synonym "USER_ORDS_CLIENT_ROLES" compile
Public Synonym USER_ORDS_CLIENT_ROLES compiled.
alter public synonym "USER_ORDS_ENABLED_OBJECTS" compile
Public Synonym USER_ORDS_ENABLED_OBJECTS compiled.
alter public synonym "USER_ORDS_HANDLERS" compile
Public Synonym USER_ORDS_HANDLERS compiled.
alter public synonym "USER_ORDS_MODULES" compile
Public Synonym USER_ORDS_MODULES compiled.
alter public synonym "USER_ORDS_OBJECTS" compile
Public Synonym USER_ORDS_OBJECTS compiled.
alter public synonym "USER_ORDS_OBJ_MEMBERS" compile
Public Synonym USER_ORDS_OBJ_MEMBERS compiled.
alter public synonym "USER_ORDS_PARAMETERS" compile
Public Synonym USER_ORDS_PARAMETERS compiled.
alter public synonym "USER_ORDS_PENDING_APPROVALS" compile
Public Synonym USER_ORDS_PENDING_APPROVALS compiled.
alter public synonym "USER_ORDS_PRIVILEGES" compile
Public Synonym USER_ORDS_PRIVILEGES compiled.
alter public synonym "USER_ORDS_PRIVILEGE_MAPPINGS" compile
Public Synonym USER_ORDS_PRIVILEGE_MAPPINGS compiled.
alter public synonym "USER_ORDS_PRIVILEGE_MODULES" compile
Public Synonym USER_ORDS_PRIVILEGE_MODULES compiled.
alter public synonym "USER_ORDS_PRIVILEGE_ROLES" compile
Public Synonym USER_ORDS_PRIVILEGE_ROLES compiled.
alter public synonym "USER_ORDS_PROPERTIES" compile
Public Synonym USER_ORDS_PROPERTIES compiled.
alter public synonym "USER_ORDS_ROLES" compile
Public Synonym USER_ORDS_ROLES compiled.
alter public synonym "USER_ORDS_SCHEMAS" compile
Public Synonym USER_ORDS_SCHEMAS compiled.
alter public synonym "USER_ORDS_SERVICES" compile
Public Synonym USER_ORDS_SERVICES compiled.
alter public synonym "USER_ORDS_TEMPLATES" compile
Public Synonym USER_ORDS_TEMPLATES compiled.
VALIDATION: 18:00:50 Total objects: 317, invalid objects: 0, missing objects: 0
VALIDATION: 18:00:50 88 INDEX
VALIDATION: 18:00:50 1 JOB
VALIDATION: 18:00:50 3 LOB
VALIDATION: 18:00:50 21 PACKAGE
VALIDATION: 18:00:50 20 PACKAGE BODY
VALIDATION: 18:00:50 1 PROCEDURE
VALIDATION: 18:00:50 57 PUBLIC SYNONYM
VALIDATION: 18:00:50 1 SEQUENCE
VALIDATION: 18:00:50 31 TABLE
VALIDATION: 18:00:50 31 TRIGGER
VALIDATION: 18:00:50 20 TYPE
VALIDATION: 18:00:50 6 TYPE BODY
VALIDATION: 18:00:50 37 VIEW
VALIDATION: 18:00:50 Validation completed.
INFO: 18:00:50 Completed validating objects for Oracle REST Data Services.
PL/SQL procedure successfully completed.
Commit complete.
[*** script: ords_alter_session_script.sql]
PL/SQL procedure successfully completed.
2023-01-06T10:00:50.924Z INFO Completed upgrade for Oracle REST Data Services version 22.4.0.r3401044. Elapsed time: 00:00:12
.588
[*** Info: Upgrade complete ]
[*** Info: Completed upgrade for Oracle REST Data Services version 22.4.0.r3401044. Elapsed time: 00:00:12.588 ]
C:\oracle\ords.224\bin>
And voila! A new version of ORDS is good to go!
The motivation for the post is pretty simple. I think we need a more flexible definition of “Oracle upgrade” in that a lot of people tend to treat upgrades of all Oracle software as the same. I.T departments often discuss “Oracle Database Upgrades” in the same fashion in that they refer to (say) “Oracle SQLcl Upgrade” or “ORDS upgrade” etc. That mindset gets people bogged down with old versions of software when they don’t need to.
Yes, you definitely need to do some rigorous testing when moving from (say) Oracle 11g database to Oracle 19c, and yes, that testing may take time and money. But I think it’s not a smart practice to then say “Yeah, we’re only going to upgrade ORDS every 3 years”, when as you can see from above, the upgrade effort is trivial. I fired up APEX and called a REST service – they both worked, so that’s good enough for me. If I hit some niche issue later on, I’ll react and fix it, but I’d rather do that than stay stuck on old versions forever.
Even in the worst case, if you have a issue after upgrade that justifies a downgrade, just a few commands in SQLcl lets you get the modules, schemas, and everything else you need to reload your definitions into a clean install of the old version.
SQL> rest modules
NAME PREFIX STATUS ITEMS_PER_PAGE
---- ------ --------- --------------
mod1 /mod1/ PUBLISHED 0
SQL> rest schemas
PARSING_SCHEMA PATTERN STATUS
-------------- ------- -------
ASKTOM asktom ENABLED
SCOTT scott ENABLED
Of course, you might be thinking “If it ain’t broke, then don’t fix it” as a justification for not upgrading ORDS (or any other Oracle software you’re running). But I’ve got one word for you – Security. There’s plenty of nasty people out there constantly looking for holes to poke in Java libraries, and we’ve got plenty of people inside Oracle making sure no holes can be poked :-). But those fixes come with the new versions of the products, just like they come with Release Updates for the database.
And I lied 🙂 I’ve got two words for you, the second one being Support. If you do have a problem with your ORDS software (or any other Oracle software), there is a very good chance the first thing you’ll be asked when you log your SR is “Does the problem still occur on the current version of the product?” You’ll be in a much better place to get rapid and effective support if you’re already on this version.
Lots of our software is now “Unzip and go!” (eg SQLcl, SQLDeveloper, Instant client) etc. We’ve done that to make installation and upgrade of those products super easy. You should take advantage of that.
Learning is not a spectator sport 