- While doing pre things to upgrade database to 12c we came to know that we need to upgrade Apex component also which we can do out of side of upgrade window it will reduce significantly upgrade time.
- When we ran pre upgrade information utility we will get below information from script output.
- By performing this apex upgrade ahead of time or out of maintenance window database upgrade you could save significant of upgrade times.
- In my case I was planned to do upgrade oracle database to 12.1.0.2 from 11.2.0.4 . during the Pre requisites I did Apex upgrade to save timing .
################################
Needs to Upgrade APEX MANUALLY :-
################################
INFORMATION: --> Oracle Application Express (APEX) can be
manually upgraded prior to database upgrade
APEX is currently at version 3.2.1.00.12 and will need to be
upgraded to APEX version 4.2.5 in the new release.
Note 1: To reduce database upgrade time, APEX can be manually
upgraded outside of and prior to database upgrade.
Note 2: See MOS Note 1088970.1 for information on APEX
installation upgrades.
Check Your current Version:-
################################
Needs to Upgrade APEX MANUALLY:-
################################
INFORMATION: --> Oracle Application Express (APEX) can be
manually upgraded prior to database upgrade
APEX is currently at version 3.2.1.00.12 and will need to be
upgraded to APEX version 4.2.5 in the new release.
Note 1: To reduce database upgrade time, APEX can be manually
upgraded outside of and prior to database upgrade.
Note 2: See MOS Note 1088970.1 for information on APEX
installation upgrades.
Check Your Version:-
COMP_NAME STATUS VERSION
---------------------------------------- ------------ ------------------------------
JServer JAVA Virtual Machine VALID 11.2.0.4.0
OLAP Analytic Workspace VALID 11.2.0.4.0
OLAP Catalog VALID 11.2.0.4.0
OWB VALID 11.2.0.4.0
Oracle Application Express VALID 3.2.1.00.12
Oracle Database Catalog Views VALID 11.2.0.4.0
Oracle Database Java Packages VALID 11.2.0.4.0
Oracle Database Packages and Types VALID 11.2.0.4.0
Oracle Expression Filter VALID 11.2.0.4.0
Oracle Multimedia VALID 11.2.0.4.0
Oracle OLAP API VALID 11.2.0.4.0
Oracle Rules Manager VALID 11.2.0.4.0
Oracle Text VALID 11.2.0.4.0
Oracle Workspace Manager VALID 11.2.0.4.0
Oracle XDK VALID 11.2.0.4.0
Oracle XML Database VALID 11.2.0.4.0
Spatial VALID 11.2.0.4.0
17 rows selected.
====> Determine the CURRENT APEX Installation Type <====
There are two types of APEX installations: development and runtime only. To determine which type is currently installed, run the following query:
set time on timing on echo on
spool apex_installation_type.log
select count(*) from FLOWS_010500.WWV_FLOWS where id = 4000;
select count(*) from FLOWS_010600.WWV_FLOWS where id = 4000;
select count(*) from FLOWS_020000.WWV_FLOWS where id = 4000;
select count(*) from FLOWS_020100.WWV_FLOWS where id = 4000;
select count(*) from FLOWS_020200.WWV_FLOWS where id = 4000;
select count(*) from FLOWS_030000.WWV_FLOWS where id = 4000;
select count(*) from FLOWS_030100.WWV_FLOWS where id = 4000;
select count(*) from APEX_030200.WWV_FLOWS where id = 4000;
select count(*) from APEX_040000.WWV_FLOWS where id = 4000;
select count(*) from APEX_040100.WWV_FLOWS where id = 4000;
select count(*) from APEX_040200.WWV_FLOWS where id = 4000;
spool off;
10:07:13 SQL> select count(*) from APEX_030200.WWV_FLOWS where id = 4000;
COUNT(*)
----------
1
SQL> select username,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username ='APEX_030200';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEMPORARY_TABLESPACE
------------------------------
APEX_030200 SYSAUX
TEMP
SQL> select status from dba_registry where comp_id='APEX';
STATUS
--------------------------------------------
VALID
If the query returns 0 then you'll need to run apxrtins.sql
If the query returns 1 then you'll need to execute apexins.sql
So i have Apex 3.2 installed since that schema is there.
Step 2) DOWNLOAD required APEX from ORACLE WEBSITE
Step 3) Move that download software to required database server where you want to upgrade apex
Step 4) Execute below command
-- Rename currently installed APEX folder to Apex 3.2 under ORACLE_HOME
cd $ORACLE_HOME
mv apex apex_3.2
-- Unzip the download soft under ORACLE_HOME.
-- Unzip the download soft under ORACLE_HOME.
unzip apex_4.2.5_en.zip -d /u00/app/oracle/product/11.2.0.4/DB
Step 5) Start Apex Upgrade
-- @apexins.sql tablespace_apex tablespace_files tablespace_temp images
cd $ORACLE_HOME/apex
SQL> connect / as sysdba
Connected.
SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/
-- in my case its using SYSAUX tablespace.
-- timing for: Complete Installation
Elapsed: 00:19:52.10
in my case its taken around 20 Mins to complete but its all depends on environment some times it might take around 45 mins to 1 hrs.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From other window you can verify the Status while upgrading happens :-
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
col STATUS for a15
col VERSION for a20
col COMP_ID for a15
select comp_id,version,status from dba_registry where comp_id='APEX';
COMP_ID VERSION STATUS
--------------- -------------------- ---------------
APEX 3.2.1.00.12 UPGRADING
##########################
verify the installation
##########################
I always verify my APEX installation after a upgrade by querying dba_registry:
select comp_id,version,status from dba_registry where comp_id='APEX';
col STATUS for a15
col VERSION for a20
col COMP_ID for a15
select comp_id,version,status from dba_registry where comp_id='APEX';SQL>
COMP_ID VERSION STATUS
--------------- -------------------- ---------------
APEX 4.2.5.00.08 VALID
set line 200;
set pagesize 9999;
col COMP_ID format a15;
col COMP_NAME format a50;
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry
/
COMP_ID COMP_NAME VERSION STATUS
--------------- -------------------------------------------------- ------------------------------ --------------------------------------------
APEX Oracle Application Express 4.2.5.00.08 VALID
OWB OWB 11.2.0.4.0 VALID
AMD OLAP Catalog 11.2.0.4.0 VALID
SDO Spatial 11.2.0.4.0 VALID
ORDIM Oracle Multimedia 11.2.0.4.0 VALID
XDB Oracle XML Database 11.2.0.4.0 VALID
CONTEXT Oracle Text 11.2.0.4.0 VALID
EXF Oracle Expression Filter 11.2.0.4.0 VALID
RUL Oracle Rules Manager 11.2.0.4.0 VALID
OWM Oracle Workspace Manager 11.2.0.4.0 VALID
CATALOG Oracle Database Catalog Views 11.2.0.4.0 VALID
CATPROC Oracle Database Packages and Types 11.2.0.4.0 VALID
JAVAVM JServer JAVA Virtual Machine 11.2.0.4.0 VALID
XML Oracle XDK 11.2.0.4.0 VALID
CATJAVA Oracle Database Java Packages 11.2.0.4.0 VALID
APS OLAP Analytic Workspace 11.2.0.4.0 VALID
XOQ Oracle OLAP API 11.2.0.4.0 VALID
17 rows selected.
Apex Upgrade Completed. Happy Apex Upgrading.
Note: Above views are expressed completely on my view please make sure before proceeding by testing on your testing environments.
Thank you very much! the explanation is excellent and very usefull
ReplyDelete