<

Upgrading Oracle APEX 3.2.1.00.10 to 4.2.6.00.03 version

Published on
7,899 Points
4,899 Views
Last Modified:
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:
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;

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);

Open in new window

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

PIC-1.pngInitially 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

Open in new window


Check the port:
 
SQL> select dbms_xdb.getHTTPPort from dual;

GETHTTPPORT
-----------
       8082

Open in new window



PIC-2.pngGo 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:
PIC-3.pngOnce 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>  

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; 

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

Open in new window

To execute this script we need to pass some parameters: 

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

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>

Open in new window


Now start the installation:
SQL> @apexins APEX APEX TEMP /i/

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

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

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.

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.

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

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

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>

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;
/

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>

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

pic-4.pngLog in to the application:
pic-5.pngWhen you first login reset the password:
pic-6.pngOnce you change the password you can login to the application:

pic-7.png
You can see that we have upgraded to APEX 4.2 version. 
0
Comment
Author:Swadhin Ray
0 Comments

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Join & Write a Comment

This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month