Preparation
As a preparation I recommend several steps and checks to prepare the upgrade.
- Make sure you have a working database backup and just in case a DBA at hand who would be able to restore your database, tablespace or schemas.
- Workspace clean up – delete applications that are not needed anymore. Especially copies of others application, that were created to test a specific feature or do a proof of concept. Be careful whether you also want to run any included deinstallation scripts. For an Apex update in general you don’t want to delete the connected tables.
- Software Download – Apex 5.1 can be downloaded from the OTN download page: www.oracle.com/technetwork/developer-tools/apex/downloads/index.html
- Backup Application – Export the application including all private saved reports
- Export supporting objects like Theme, Static Application files
- Backup Image folder – if your image folder is /i/ then make a copy and rename the copy to /i50/
- Check the Apex 5.1 known issues page: http://www.oracle.com/technetwork/developer-tools/apex/downloads/apex-51-known-issues-3408261.html
Apart from downloding the new Apex version, none of these steps is really required. But it gives a nice and cozy fealing to be sure to be able to go back.
In Apex 5.1 some features are deprecated and some options did change. It is possible to prepare your application to anticipate the effects of the upgrade. I will cover this in a separate blog post. More importantly read through the “changed behaviour”, “deprecated” and “desupported” sections of the installation manual (https://docs.oracle.com/database/apex-5.1/HTMRN/toc.htm#HTMRN-GUID-8BDFB50B-4EC6-4051-A9B6-7D5805E00C4E ).
Here are some things to consider already in Apex 5.0.
- Apex.Server plugin
- does not return an jqXHR object anymore
- async option deprecated
- htmldb_getobject desupported => replace with apex.server
- old Apex themes deprecated
- check for CANCEL or BACK or PREVIOUS buttons (page redirect) with execute validations = YES. These will do client side validation in Apex 5.1. If that is not wanted, change it to NO.
- jsTRee plugin deprecated
- classic reports
- hidden column type => hidden column
- no enterable fields!
- file browse storage => switch from WWV_FLOW_FILES to APEX_APPLICATION_TEMP_FILES
- desupported attributes or types
- page: body header, include standard javascript and css
- region: svg charts, simple chart, classic tree
- button: pre text, post text
- item: start and stop grid layout, file browse storage type
- Conditions deprecated: text= value, text != value, text is (not) contained in item
- No more: Save state before branching
- apex_plsql_job package desupported
- check if you reference the internal hidden fields (renamed in Apex 5.1): pPageChecksum => pPageItemsProtected, p_md5_checksum=>pPageItemsRowVersion
- date picker (classic) deprecated
- several updated javascript libraries
Decide about the upgrade path
Now consider whether you want to do a traditional upgrade (all steps in one go) or if you want to minimize the application downtime (several steps, not for CDB$ROOT installations). Or as oracle calls it: “maximize application uptime”.
To minimize downtime read this chapter in the documentation: https://docs.oracle.com/database/apex-5.1/HTMRN/toc.htm#HTMRN-GUID-411DE0D8-59E1-4267-8751-FD56305ABE4E
Upgrade in one go
The only step needed to do:
Create database schemas and database objects (tables, packages) and do the application migrations.
@apexins.sql tablespace_apex tablespace_files tablespace_temp images
Upgrade with maximum Uptime
Instead of running a single script, we can do the upgrade in several steps. Only during the 3rd step, the end users need to be disconnected. This third step took only 1.01 seconds on my system.
The upgrade of an Application Express instance runs in four phases:
- Create database schemas and database objects (tables, packages).
This essentially creates the Apex_051000 schema.
-> no influence on running sessions
- Migrate application metadata.
This copies the repository application data from Apex_050000 into Apex_051000.
To help with that some upgrade triggers were previously installed.
-> developers can’t work anymore
- Migrate data that runtime applications modify and switch to the new version.
-> downtime for all (developers and end users)
- Migrate additional log and summary data (this step starts automatically)
-> no influence on running sessions
But we need zero downtime – is it possible?
I’m convinced it is possible to reach a downtimeless application upgrade using the EBR (edition based redefinition) feature of the oracle database. I have extensive knowledge using EBR even inside Apex. However so far I didn’t have the time to do a proof of concept (POC) for the upgrade. Also this would be an unsupported action (currently). The change would include tweaking serveral non editioned objects (public synonyms, session contexts, registry data) in such a way that they show up differently when used inside an edition.
If any Germany based customer or the Oracle Apex Team itself is interested how to do this and is willing to pay for the time I need to invest in this, then please contact me.
do the upgrade
Unzip the Apex_5.1.zip file into an appropriate folder. And navigate to the apex_5.1/apex folder
If you decide for the “Maximum Uptime” upgrade path, then three scripts need to run. And ORDS needs to be stopped for script 3. To run the scripts we need to know the tablespace names and the image path.
Find the tablespace
The documentation gives examples useing the SYSAUX tablespace. I do not recommend that. Apex should have its own tablespace.
select username, default_tablespace, temporary_tablespace, profile, oracle_maintained
from dba_users
where regexp_like(username,'^(APEX_|ORDS_)');
This shows only the default setting. We can reuse the same tablespace. But it is also possible to install apex 5.1 into a new tablespace. If you want to do that, then this new tablespace needs to be created first.
Sometimes we want to see if the data is really in this default tablespace. Here is a select that will show the data distribution and also how much space is used.
select owner as schema, tablespace_name as data_tbs, nvl(segment_type,' - total -') segment_type, round(sum(bytes)/1024/1024,2) size_in_MB
from dba_extents
where regexp_like(owner,'^(APEX_|ORDS_)')
group by owner, tablespace_name, rollup(segment_type)
;
Example result
SCHEMA DATA_TBS SEGMENT_TYPE SIZE_IN_MB
APEX_050000 APEX INDEX 239,69
APEX_050000 APEX TABLE 267,19
APEX_050000 APEX LOBINDEX 12,75
APEX_050000 APEX LOBSEGMENT 240,31
APEX_050000 APEX - total - 759,94
ORDS_METADATA ORDS INDEX 4,5
ORDS_METADATA ORDS TABLE 1,63
ORDS_METADATA ORDS LOBINDEX 0,19
ORDS_METADATA ORDS LOBSEGMENT 0,38
ORDS_METADATA ORDS - total - 6,69
This is a example of one of my apex environments. As you can see there is only one Apex tablespace “APEX” used. Approximatly the same amount of data is in tables, in indexes and in LOBs. LOBSEGMENT indicates that there had been some wwv_flow_files activities going on.
Run the scripts
The scripts are located in the apex subfolder. For example D:/product/apex/apex_5.1/apex.
Navigate to that folder and start an sqlplus session with sys as sysdba. If you are in a CDB/PDB environment connect to the PDB not to CDB$ROOT. To connect to the PDB, the service name needs to be provided. Because of that also a running listener and a matching tnsnames.ora file is needed.
Assuming the following settings:
- tablespace for apex and apex files: APEX
- temp tablespace: TEMP
- image directory: /i/
Then those 3 scripts need to run.
@apexins1.sql APEX APEX TEMP /i/
@apexins2.sql APEX APEX TEMP /i/
stop ORDS
@apexins3.sql APEX APEX TEMP /i/
restart ORDS
Phase 4 is automatically started by running a dbms_scheduler job.
Check privs and synonyms
Sometimes we give extra access from the Apex Schema to our own custom schema. For example in one Application I extended the Apex Feedback functionality and did use the view APEX_TEAM_FEEDBACK. Such changes need to be moved to the new APEX_050100 schema.
ACLs
Here is just how to check if the Apex 5.0 schema has any network ACLs set.
SELECT *
FROM DBA_HOST_ACES xe
where principal = 'APEX_050000';
I created a script to duplicate all ACEs that exists for APEX_050000 to APEX_050100.
The script is shown and explained in a separate blog post.
Grants
Here is just how to check if the Apex 5.0 schema has any objects granted to other schemas.
select *
from all_tab_privs
where grantor like 'APEX\_%' escape '\'
and grantee not in ('PUBLIC','SYS','SYSTEM');
It will show objects like tables, views and packages that have privileges granted directly.
To see only the missing grants you can run the following statement. If it returns no rows, then you are fine.
select GRANTEE , TABLE_SCHEMA , TABLE_NAME , PRIVILEGE , GRANTABLE ,HIERARCHY
from all_tab_privs
where grantor in ('APEX_050000','APEX_050100')
and not regexp_like (grantee,'^(APEX_|ORDS_|SYSTEM$|PUBLIC$|SYS$)')
group by GRANTEE , TABLE_SCHEMA , TABLE_NAME , PRIVILEGE , GRANTABLE ,HIERARCHY
having count(*) = 1 and min(grantor) = 'APEX_050000';
Those grants probably need to be copied to the new apex_50100 schema.
Search Source code
You should search the complete application, if there are any references to APEX_050000. This should be done after the migration.
downgrade to 5.0
The section how to downgrade an application back to 5.0 is currently missing from the documentation. Here is a official blog post how to do it: http://jastraub.blogspot.de/2017/01/ooops-i-did-it-again.html
This is the sql script that Jason Straub published to do the downgrade:
alter session set current_schema = SYS;
@wwv_flow_val.sql
@wwv_flow_val.plb
begin
dbms_utility.compile_schema('APEX_050000');
end;
/
set define '^'
@validate_apex x x APEX_050000
begin
for i in ( select owner, trigger_name
from sys.dba_triggers
where owner = 'APEX_050000'
and trigger_name like 'WWV_FLOW_UPGRADE_%'
order by 1 )
loop
sys.dbms_output.put_line('Dropping trigger '||i.owner||'.'||i.trigger_name);
execute immediate 'drop trigger '||i.owner||'.'||i.trigger_name;
end loop;
end;
/
ALTER SESSION SET CURRENT_SCHEMA = APEX_050000;
exec apex_050000.wwv_flow_upgrade.switch_schemas('APEX_050100','APEX_050000');
ALTER SESSION SET CURRENT_SCHEMA = SYS;
drop context sys.APEX$SESSION;
create context sys.APEX$SESSION using APEX_050000.WWV_FLOW_SESSION_CONTEXT;
declare
l_apex_version varchar2(30);
begin
l_apex_version := apex_050000.wwv_flows_release;
dbms_registry.downgrading('APEX','Oracle Application Express','validate_apex','APEX_050000');
dbms_registry.downgraded('APEX',l_apex_version);
validate_apex;
end;
/
select * from dba_tablespaces ;
select username, default_tablespace, temporary_tablespace, profile, oracle_maintained
from dba_users
where regexp_like(username,'^(APEX_|ORDS_)');
select owner as schema, tablespace_name as data_tablespace, nvl(segment_type,' - total -') segment_type, round(sum(bytes)/1024/1024,2) size_in_MB
from dba_extents
where regexp_like(owner,'^(APEX_|ORDS_)')
group by owner, tablespace_name, rollup(segment_type)
;
Fairly easy, once we know what to do. In the future I might use this session storage for other things too.
svenweller 