Upgrading Oracle APEX 3.2.1.00.10 to 4.2.6.00.03 version

This article shows how to upgrade from APEX version  3.2.1.00.10 to 4.2.6.00.03 on Oracle Database 11g Release 11.2.0.1.0.
First of all why we need to upgrade, the default version provided by Oracle doesn't support few of the features which is later version they improved the PDF printing options , Mobile application builder and many more.

The details on the version changes can be retrieved from the below link:

• http://en.wikipedia.org/wiki/Oracle_Application_Express

By upgrading the APEX version we are not upgrading the Oracle database, this will remain as it is but all the latest options to build an small application can be done having more features on upgrading the application side. This is not make any significance of performance improvement lastly this supports HTML 5 too and the basic themes are also present, where as we can also configure it. 

By default APEX version 3  comes when we install Oracle database from 10g onwards.  Once we have it installed we need to unlock the "anonymous " user and check if our HTTP port by executing the below SQL :
 

SQL> select dbms_xdb.getHTTPPort from dual;
                      

Select all Open in new window

Now if we see any result then fine else we can set the HTTP port by executing the below code:
 

EXEC dbms_xdb.sethttpport(8082);
                      

Select all Open in new window

Now if we open the URL on our local we will see the below screen for login:

Initially the screen looks as like above. To check the version details we can login to our database to verify :

Login as sys user and check:

Connected to Oracle Database 11g Release 11.2.0.1.0 
                      Connected as SYS
                      SQL> SELECT comp_name, version, status FROM dba_registry WHERE comp_id='APEX';
                      
                      COMP_NAME                        VERSION          STATUS
                      ------------------------------------------------------------------------
                      Oracle Application Express      3.2.1.00.10        VALID
                      

Select all Open in new window

Check the port:
 

SQL> select dbms_xdb.getHTTPPort from dual;
                      
                      GETHTTPPORT
                      -----------
                             8082
                      

Select all Open in new window

Go to the URL ( http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html)marked as yellow on the above screen and download the Oracle Apex version as marked Oracle Application Express 4.2.6 - All languages.
 
To download you have to provide your Oracle register username and password on URL as shown below:
Once you successfully logged in the download will automatically started. The downloaded file was present on my desktop location : C:\Users\sloba\Downloads\apex_4.2.6.zip

Now unzip it and move all the files under: C:\Users\sloba\Downloads\apex_4.2.6 \apex to your Oracle root folder where you will see one folder called APEX:  E:\app\sloba\product\11.2.0\dbhome_1\apex
 
Do a copy and replace for all the files but before doing this please take a backup of the existing binaries. I took the entire APEX folder as a backup to be on a safe side.

Before we start the installation we have to disable the HTTP access. 

Connected to Oracle Database 11g Release 11.2.0.1.0 
                      Connected as SYS
                      
                      SQL> EXEC dbms_xdb.sethttpport(0);
                      PL/SQL procedure successfully completed
                      SQL>  
                      

Select all Open in new window

I created a separate table space for my APEX application:

CREATE TABLESPACE APEX DATAFILE 'E:\app\sloba\oradata\db11gr2\apex_01.dbf'
                      SIZE 200M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1000M LOGGING
                      EXTENT MANAGEMENT LOCAL
                      SEGMENT SPACE MANAGEMENT AUTO; 
                      

Select all Open in new window

You can ignore the above step if you want to use APEX on any specific table space already created for your schema. to start the installation we have to execute the script apexins that is present under APEX folder:

Directory of E:\app\sloba\product\11.2.0\dbhome_1\apex
                      
                      01/20/2015  05:38 PM    <DIR>          .
                      01/20/2015  05:38 PM    <DIR>          ..
                      03/02/2013  07:19 AM             5,882 apexins.sql
                      

Select all Open in new window

To execute this script we need to pass some parameters: 

@apexins <apex_tbs> <apex_files_tbs> <temp_tbs> <images>
                      

Select all Open in new window

Details on the parameter are as below: 

• apex_tbs – Tablespace name for the APEX user, for us it will be .” APEX”
• apex_files_tbs – name of the tablespace for APEX files user, we are going to use the same as above “APEX”.
• temp_tbs – Temporary tablespace name which is TEMP for us.
• images – This is the virtual directory for the APEX images. It is defined as /i/  

Now login to cmd and go to APEX folder under Oracle Home:  E:\app\ sloba \product\11.2.0\dbhome_1\apex . The login as sys user as like below:

E:\app\sloba\product\11.2.0\dbhome_1\apex>sqlplus
                      SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 20 18:00:43 2015
                      Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                      
                      Enter user-name: sys as sysdba
                      Enter password:
                      
                      Connected to:
                      Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
                      
                      SQL>
                      

Select all Open in new window

Now start the installation:

SQL> @apexins APEX APEX TEMP /i/
                      

Select all Open in new window

Once the installation is done you will get a message:

Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
                      

Select all Open in new window

Now change the ADMIN password by running apxchpwd.sql. Login to Oracle as SYS user and run the script to change the password :

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 20 18:24:47 2015
                      
                      Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                      
                      Enter user-name: sys as sysdba
                      Enter password:
                      
                      Connected to:
                      Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
                      
                      SQL>@apxchpwd
                      

Select all Open in new window

Make sure you follow this set of rules to create a new password for ADMIN user:

* Password must contain at least 6 characters.
                      * Password must contain at least one numeric character (0123456789).
                      * Password must contain at least one punctuation character
                      (!"#$%&()``*+,-/:;?_).
                      * Password must contain at least one upper-case alphabetic character.
                      * Password must not contain username.
                      

Select all Open in new window

Once used the below rule and create a new password we will get the below message:

SQL> @apxchpwd
                      Enter a value below for the password for the Application Express ADMIN user.
                      
                      Enter a password for the ADMIN user              []
                      
                      Session altered.
                      
                      ...changing password for ADMIN
                      
                      PL/SQL procedure successfully completed.
                      

Select all Open in new window

Now let us check the APEX version:

SQL> SELECT comp_name, version, status FROM dba_registry WHERE comp_id='APEX';
                      
                             COMP_NAME                      VERSION          STATUS
                      ------------------------------ ----------------------------------
                      Oracle Application Express         4.2.6.00.03         VALID
                      

Select all Open in new window

From the above you can see that the version is changed from 3.2.1.00.10 to 4.2.6.00.03 and the status says it’s valid. Now run the Embedded PL/SQL Gateway configuration by executing the below scripts under SYS user:

SQL> @apex_epg_config.sql  E:\app\sloba\product\11.2.0\dbhome_1
                      SQL> @apxldimg.sql E:\app\sloba\product\11.2.0\dbhome_1
                      

Select all Open in new window

Now unlock the below users on Oracle using SYS user:

Connected to Oracle Database 11g Release 11.2.0.1.0 
                      Connected as SYS
                      
                      SQL> 
                      SQL> ALTER USER anonymous ACCOUNT UNLOCK;
                      
                      User altered
                      
                      SQL> ALTER USER xdb ACCOUNT UNLOCK;
                      
                      User altered
                      
                      SQL> ALTER USER apex_public_user ACCOUNT UNLOCK;
                      
                      User altered
                      
                      SQL> ALTER USER flows_files ACCOUNT UNLOCK;
                      
                      User altered
                      
                      SQL>
                      

Select all Open in new window

Configure the database parameters for using APEX:
 

• Check the JOB_QUEUE_PROCESSES parameter
  
  • The minimum value should be set to 20 if you see more then leave it as is; do not configure.
  • To display we can use SHOW PARAMETER job_queue_processes
  • To set the parameter we can use the below command:
    
    • ALTER system SET job_queue_processes=20 scope=both;
• Check the SHARED_SERVERS parameter
  
  • The minimum recommendation for SHARED_SERVERS is 5 for a small concurrent users
  • To display we can use SHOW PARAMETER shared_servers
  • To set the parameter we can use the below command:
    
    • ALTER system SET shared_servers=5 scope=both;
• Now we can set the HTTP port which we were using earlier i.e. 8082
  
  • To set we have to execute :
    
    • EXEC dbms_xdb.sethttpport(8082);
• Now we can enable the remote HTTP connection (optional)
  
  • To execute we have to use the below command:
    
    • EXEC dbms_xdb.setListenerLocalAccess(l_access => FALSE);
    • If l_access is set to TRUE, setListenerLocalAccess allows access to the XML DB HTTP server on the localhost only.
    • If l_access is set to FALSE, setListenerLocalAccess allows access to the XML DB HTTP server on both the localhost and non-localhost interfaces i.e. remote connections.

To grant connect privilages to any host to connect to access APEX_040200 database user we have to use “DBMS_NETWORK_ACL_ADMIN” package .

Execute the below script under sys user:

DECLARE
                              acl_path VARCHAR2(4000);
                      BEGIN
                              -- Look for the ACL currently assigned to '*' and give APEX_040200
                              -- the "connect" privilege if APEX_040200
                              -- 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_040200'
                                                                       ,'connect') IS NULL
                              THEN
                                      dbms_network_acl_admin.add_privilege(acl_path
                                                                          ,'APEX_040200'
                                                                          ,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_040200'
                                                                       ,TRUE
                                                                       ,'connect');
                                      dbms_network_acl_admin.assign_acl('power_users.xml'
                                                                       ,'*');
                      END;
                      / 
                      COMMIT;
                      /
                      

Select all Open in new window

Execution:

Connected to Oracle Database 11g Release 11.2.0.1.0 
                      Connected as SYS
                      
                      SQL> 
                      SQL> DECLARE
                        2  ACL_PATH VARCHAR2(4000);
                        3  BEGIN
                        4  -- Look for the ACL currently assigned to '*' and give APEX_040200
                        5  -- the "connect" privilege if APEX_040200
                        6  -- does not have the privilege yet.
                        7  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
                        8  WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
                        9  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040200',
                       10  'connect') IS NULL THEN
                       11  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
                       12  'APEX_040200', TRUE, 'connect');
                       13  END IF;
                       14  EXCEPTION
                       15  -- When no ACL has been assigned to '*'.
                       16  WHEN NO_DATA_FOUND THEN
                       17  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
                       18  'ACL that lets power users to connect to everywhere',
                       19  'APEX_040200', TRUE, 'connect');
                       20  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
                       21  END;
                       22  /
                      
                      PL/SQL procedure successfully completed
                      
                      SQL> COMMIT;
                      
                      Commit complete
                      
                      SQL>
                      

Select all Open in new window

Go the browser and check if we are able to open the application or not:   http://localhost:8082/apex/apex_admin

Log in to the application:
When you first login reset the password:
Once you change the password you can login to the application:


You can see that we have upgraded to APEX 4.2 version.