MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Oracle Apex 5’ Category

Upgrade APEX 4 to 5.1

with 2 comments

This blog post shows you how to upgrade APEX Version 4.0.2 on a default Oracle Database 11g XE instance to APEX 5.1.2. Oracle’s APEX t upgrade document was just a bit too short and missed some details. It divided into five parts. The first part confirms your starting point. The second part downloads and positions the extracted software. The third part installs APEX 5. The fourth part configures APEX 5. The fifth part shows you how to access and get to the APEX 5 home page.

Confirm APEX 4 Installation

  1. Verify the database version by connecting as the system user through SQL*Plus and running the following query: 

    SELECT banner
FROM   v$version
WHERE  banner LIKE 'Oracle Database%';

    It should return the following when you’re upgrading the Oracle Database 11g XE:

    BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
  2. Verify the APEX version by connecting as the system user through SQL*Plus and running the following query: 

    COLUMN version_no        FORMAT A16 HEADING "Version Number"
COLUMN api_compatibility FORMAT A16 HEADING "API|Compatibility"
COLUMN patch_applied     FORMAT A14 HEADING "Patch Applied"
SELECT *
FROM   apex_release;

    It should return the following when you start with the base Oracle Database 11g XE:

    		 API
Version Number	 Compatibility	  Patch Applied
---------------- ---------------- --------------
4.0.2.00.09      2010.05.13

  3. Verify the XML Database version by connecting as the system user through SQL*Plus and running the following query: 

    COLUMN comp_name FORMAT A20
COLUMN version   FORMAT A12
COLUMN status    FORMAT A8
SELECT comp_name
,      version
,      status
FROM   dba_registry
WHERE  comp_id = 'XDB';

    It should return the following when you’re upgrading the Oracle Database 11g XE:

    COMP_NAME            VERSION      STATUS
-------------------- ------------ --------
Oracle XML Database  11.2.0.2.0   VALID

  4. Verify the memory_target of the instance by connecting as the system user through SQL*Plus and running the following query. It should be no smaller than 300 MB. 

    show parameter memory_target

    It should return the following when you’re upgrading the Oracle Database 11g XE:

    NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 1G

  5. Oracle’s instructions qualify that APEX 5 will install into the APEX_050000 schema. Oracle creates the new APEX_050000 schema with a default of the sysaux and temp table space. You can verify these as the system user through SQL*Plus by running the following two queries. The first one checks for the tablespaces and the second for available space and auto extensibility. 

    COLUMN default_tablespace   FORMAT A22
COLUMN temporary_tablespace FORMAT A22
SELECT default_tablespace
,      temporary_tablespace
FROM   dba_users
WHERE  username = 'APEX_040000';

    It should return the following when you’re upgrading the Oracle Database 11g XE:

    DEFAULT_TABLESPACE     TEMPORARY_TABLESPACE
---------------------- ----------------------
SYSAUX                 TEMP

    COLUMN tablespace_name FORMAT A10        HEADING "Tablespace Name"
COLUMN file_name       FORMAT A38        HEADING "File Name"
COLUMN available_space FORMAT 999,999.00 HEADING "Available|Space MB"
COLUMN autoextensible  FORMAT A10        HEADING "Auto|Extensible"
SELECT   tablespace_name
,        file_name
,      ((maxbytes - bytes) / 1024) / 1024 AS available_space
,        autoextensible
FROM     dba_data_files
WHERE    tablespace_name IN ('SYSAUX','SYSTEM');

    It should return the following when you’re upgrading the Oracle Database 11g XE:

    Tablespace                                        Available   Auto
Name       File Name                              Space MB    Extensible
---------- -------------------------------------- ----------- ----------
SYSAUX     /u01/app/oracle/oradata/XE/sysaux.dbf    32,027.98 YES
SYSTEM     /u01/app/oracle/oradata/XE/system.dbf       200.00 YES

Download APEX 5

  1. Download the APEX software from the Oracle web site. Assuming you download the software as the student user, you can save it in your Downloads directory.

    You should open a Terminal session and connect as the oracle user. If you’ve setup your instance correctly, you will need to first become the root user and then the oracle user. As the oracle user, you source the Oracle environment and copy the apex_x.x.x.zip file from the ~student/Downloads directory to the /u01/app/oracle directory.

  1. You copy the file from the student user’s Downloads directory with the following command: 

    cp /home/student/Downloads/apex_x.x.x.zip /u01/app/oracle

  1. You unzip the copied apex_x.x.x.zip file (version 5.1.2 in this example) with the following command, and it will create a new apex directory as a subdirectory of the /u01/app/oracle directory. 

    unzip apex_5.1.2.zip

Install APEX 5

  1. You should query the dba_users view to check the status of the apex_public_user and anonymous user accounts, like this: 

    COLUMN username       FORMAT A18 HEADING "User Name"
COLUMN account_status FORMAT A10 HEADING "Account|Status"
SELECT   username
,        account_status
FROM     dba_users
WHERE    username IN ('APEX_PUBLIC_USER','ANONYMOUS');

    It should return the following when you’re upgrading the Oracle Database 11g XE but the anonymous user name may be open if you’ve previously unlocked it:

    		   Account
User Name	   Status
------------------ ----------
APEX_PUBLIC_USER   LOCKED
ANONYMOUS	   LOCKED

  2. You can unlock the apex_public_user and anonymous accounts with the following statements: 

    ALTER USER apex_public_user ACCOUNT UNLOCK;
ALTER USER anonymous ACCOUNT UNLOCK;

  3. The installation uses the flows_files schema, which should be installed. You can verify the default and temporary tablespaces with the following query: 

    COLUMN default_tablespace   FORMAT A22
COLUMN temporary_tablespace FORMAT A22
SELECT default_tablespace
,      temporary_tablespace
FROM   dba_users
WHERE  username = 'FLOWS_FILES';

    DEFAULT_TABLESPACE     TEMPORARY_TABLESPACE
---------------------- ----------------------
SYSAUX		       TEMP

  4. Open a Terminal session, connect as the oracle user, source the Oracle environment file, and change your active directory to the /u01/app/oracle/apex directory, and open a SQL*Plus connection as the sys user. You need superuser privileges, which means you need to connect to the Oracle database with the “sys as sysdba” syntax. 

    sqlplus sys as sysdba

    You can now install APEX 5.x.x by calling the following script with four parameters:

    @apexins.sql SYSAUX SYSAUX TEMP /i/

    It will take a couple minutes for the installation script to succeed. You will know that it is completed when you see the following message:

    Thank you for installing Oracle Application Express 5.1.2.00.09
 
Oracle Application Express is installed in the APEX_050100 schema.
 
The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
http://host:port/apex/apex_admin     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex/apex_admin     (Oracle REST Data Services)
 
The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
http://host:port/apex     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex     (Oracle REST Data Services)
 
PL/SQL procedure successfully completed.
 
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
  5. After installing APEX 5, you can re-verify the APEX version by connecting as the system user through SQL*Plus and running the following query: 

    COLUMN version_no        FORMAT A16 HEADING "Version Number"
COLUMN api_compatibility FORMAT A16 HEADING "API|Compatibility"
COLUMN patch_applied     FORMAT A14 HEADING "Patch Applied"
SELECT *
FROM   apex_release;

    It should return the following after upgrading with APEX 5:

    		 API
Version Number	 Compatibility	  Patch Applied
---------------- ---------------- --------------
5.1.2.00.09	 2016.08.24	  APPLIED

Configure APEX 5

  1. At this point, you need to set the internal password, which you can do by navigating to the /u01/app/oracle/apex directory. In that directory, you should open a SQL*Plus session as the sys user with the “sys as sysdba” privilege. Run the following script to set the APEX Administrator’s credentials: 

    @apxchpwd.SQL

    It will prompt you for parameters, like so:

    ================================================================================
This script can be used to change the password of an Application Express
instance administrator. If the user does not yet exist, a user record will be
created.
================================================================================
Enter the administrator's username [ADMIN] ADMIN
User "ADMIN" exists.
Enter ADMIN's email [ADMIN] mclaughlinm@byui.edu
Enter ADMIN's password [] 
Changed password of instance administrator ADMIN.

  2. The next task requires you to run the apex_epg_config.sql script with one directory parameter. You should be connect to the sys user with the “sys as sysdba” privilege: 

    @apex_epg_config.SQL /u01/app/oracle

    It will take a couple minutes to complete this script, and when it is complete it displays:

    . Loading images directory: /u01/app/oracle/apex/images

  3. While the default port for XML DB is 8080, you should confirm it with this query: 

    SELECT dbms_xdb.gethttpport
FROM   dual;

    It should return the following:

    GETHTTPPORT
-----------
       8080

Connect to and use APEX 5

  • You can type the following URL into your local browser to get to the APEX 5 Administration page: 

    http://localhost:8080/apex/apex_admin

    It should display the following login. The password is the one you entered when you ran the apxchpwd.sql script in the configuration section of this post.

    Oracle12cInstall04
    After you enter proper credentials, click the Sign in to Administration button to proceed. It should display the following APEX 5 home page.

    Oracle12cInstall04
    You can now work in APEX 5 Administration and setup a individual workspaces.

Cleanup APEX 4

Migrating functionality to APEX 5 is possible but reworking the existing design in the context of new features is better. After you have migrated your applications and upgraded your production instance, you can drop the APEX_040000 user/schema and remove any APEX 4 workspaces. This segment of shows you how to remove an APEX 4 workspace and drop the APEX_040000 user/schema.

  1. The following anonymous PL/SQL block will remove an APEX 4 workspace from a user schema. It’s designed for you to run it inside the target schema but you can change it to run it as the system user against multiple schemas. 

    DECLARE
  /* Cursor for all APEX 4 tables and sequences. */
  CURSOR c IS
    SELECT   uo.object_type
    ,        uo.object_name
    FROM     user_objects uo
    WHERE    uo.object_name IN
               ('DEPT'
               ,'APEX$_WS_WEBPG_SECTION_HISTORY'
               ,'APEX$_ACL'
               ,'APEX$_WS_WEBPG_SECTIONS'
               ,'APEX$_WS_ROWS'
               ,'EMP'
               ,'APEX$_WS_FILES'
               ,'APEX$_WS_TAGS'
               ,'APEX$_WS_LINKS'
               ,'APEX$_WS_NOTES'
               ,'DEMO_USERS'
               ,'DEMO_CUSTOMERS'
               ,'DEMO_ORDERS'
               ,'DEMO_PRODUCT_INFO'
               ,'DEMO_ORDER_ITEMS'
               ,'DEMO_STATES'
               ,'APEX$_WS_HISTORY'
               ,'DEMO_USERS_SEQ'
               ,'DEMO_PROD_SEQ'
               ,'DEMO_ORD_SEQ'
               ,'DEMO_ORDER_ITEMS_SEQ'
               ,'DEMO_CUST_SEQ'
               ,'CUSTOM_HASH'
               ,'CUSTOM_AUTH')
    ORDER BY  uo.object_type DESC;
BEGIN
  FOR i IN c LOOP
    IF i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS';
    ELSE
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;
    END IF;
  END LOOP;
END;
/

  2. You can then connect as the sys user with the “sys as sysdba” privilege and drop the APEX_040000 user/schema, like this: 

    DROP USER apex_040000 CASCADE;

Written by maclochlainn

July 23rd, 2017 at 12:41 am

Posted in Apex,Apex 4,Apex 5,Oracle,Oracle 11g,Oracle APEX,Oracle Apex 4,Oracle Apex 5

Tagged with ,