Home » Middleware » APEX » Installing APEX 4.2 and configuring Embedded PL/SQL Gateway (EPG)

Installing APEX 4.2 and configuring Embedded PL/SQL Gateway (EPG)

Posted on by

A standard Oracle 11.2.0.3 database installation comes bundled with Application Express (APEX) 3.2.1 by default.  I’m going to upgrade to the latest version of APEX (currently 4.2.2) and then configure the Embedded PL/SQL Gateway (EPG), which uses the Oracle XML DB HTTP components within the database itself, so I don’t need to run a separate HTTP server.

First off, download apex_4.2.2_en.zip from the following location and copy the zip file to your database server:
http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

Disable HTTP access and backup the existing binaries 

Connect as SYS and disable the Oracle XML DB HTTP server by temporarily setting the HTTP port to zero (if it’s already zero, then it’s not enabled):

sqlplus / as sysdba
SELECT dbms_xdb.gethttpport FROM dual;
EXEC dbms_xdb.sethttpport(0);

Backup and move the existing APEX binaries:

mv $ORACLE_HOME/apex $ORACLE_HOME/apex.3.2.1

Unzip the APEX 4.2.2 software and change directories ready for the install:

unzip /u01/app/oracle/software/apex_4.2.2_en.zip -d $ORACLE_HOME
cd $ORACLE_HOME/apex

Install APEX 4.2.2

Connect as SYS again, and create a new APEX tablespace (this is optional, but I prefer to keep things separate from the default SYSAUX tablespace):

sqlplus / as sysdba
CREATE TABLESPACE APEX DATAFILE '/u02/oradata/snap11g/apex_01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1000M LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Check which version is currently installed:

COL comp_name FOR A30
SELECT comp_name, version, status FROM dba_registry WHERE comp_id='APEX';

COMP_NAME                      VERSION                        STATUS
------------------------------ ------------------------------ -----------
Oracle Application Express     3.2.1.00.12                    VALID

Start the installation of 4.2.2:

@apexins APEX APEX TEMP /i/

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

apex_tbs – name of the tablespace for the APEX user.
apex_files_tbs – name of the tablespace for APEX files user.
temp_tbs – name of the temporary tablespace.
images – virtual directory for APEX images.  Define the virtual image directory as /i/ for future updates.

Once the installation has finished, reconnect and change the ADMIN account password:

sqlplus / as sysdba
@apxchpwd

NOTE: The password must contain at least one punctuation character: (!”#$%&()“*+,-/:;?_).

Check the registry again:

COL comp_name FOR A30
SELECT comp_name, version, status FROM dba_registry WHERE comp_id='APEX';

COMP_NAME                      VERSION                        STATUS
------------------------------ ------------------------------ -----------
Oracle Application Express     4.2.2.00.11                    VALID

Run the Embedded PL/SQL Gateway configuration (EPG)

@apex_epg_config.sql /u01/app/oracle/product/11.2.0.3

Update the APEX images with those from the new release:

@apxldimg.sql /u01/app/oracle/product/11.2.0.3

NOTE: This step isn’t necessary if you ran the apex_epg_config.sql script above, as it will have already done this for you.  If you didn’t run the EPG script above, because you’ve upgraded from an install where EPG was already configured, then you do need to run this.

Make sure that the following accounts are unlocked:

ALTER USER anonymous ACCOUNT UNLOCK;
ALTER USER xdb ACCOUNT UNLOCK;
ALTER USER apex_public_user ACCOUNT UNLOCK;
ALTER USER flows_files ACCOUNT UNLOCK;

Configure database parameters for APEX

Check that the JOB_QUEUE_PROCESSES parameter is set to at least 20:

SHOW PARAMETER job_queue_processes
ALTER system SET job_queue_processes=20 scope=both;

For a small group of concurrent users, Oracle recommends a value of 5 for SHARED_SERVERS:

SHOW PARAMETER shared_servers
ALTER system SET shared_servers=5 scope=both;

Enable network services (ACL) and XML DB HTTP server

Re enable the Oracle XML DB HTTP Server port (8082):

EXEC dbms_xdb.sethttpport(8082);

Enable remote HTTP connections (optional):

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.

By default, the ability to interact with network services is disabled in Oracle Database 11g. Therefore, you must use the DBMS_NETWORK_ACL_ADMIN package to grant connect privileges to any host for the APEX_040200 database 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;

Finally, login and check everything is working…

Administration Services login page (used for managing the APEX instance): http://linux03.vbox:8082/apex/apex_admin

NOTE: You’ll be prompted to change the ADMIN password the first time you logon.

Username: ADMIN
Password: *****

Workspace login page: http://linux03.vbox:8082/apex

Workspace: INTERNAL
Username: ADMIN
Password: *****

Once you’re logged in, you’ll see something like this…

Removing previous versions of APEX

If later on you decide to clean-up and remove older versions of APEX, you can run the following SQL to identify such schemas:

SELECT username
FROM dba_users
WHERE (username LIKE 'FLOWS_%' OR username LIKE 'APEX_%')
AND username NOT IN (
SELECT 'FLOWS_FILES'
FROM dual
UNION
SELECT 'APEX_PUBLIC_USER' FROM dual
UNION
SELECT schema
FROM dba_registry
WHERE comp_id = 'APEX');

…and then drop the schema(s), with the cascade option:

DROP USER APEX_030200 CASCADE;

References:

Oracle Application Express Installation Guide Release 4.2

27 thoughts on “Installing APEX 4.2 and configuring Embedded PL/SQL Gateway (EPG)”

  1. Nice document. Can I have your email address. I want your help if I ran into any issues with apex installation and in learning…

    Reply

  3. This is one of the best installation tips I ever read. And I tried it more then twice with oracle and other tips on different server systems. There is no missing, all statements are fully functionable and the XDB part is a mistery for me always….
    Therefore: thanks a lot, perfect job

    Berrnd

    Reply

      • Confusing part for Windows were @apex_epg_config and @apxldimg.sql:
        Unzipped apex to c:TEMP on the database server and did the following.
        @apex_epg_config C:TEMP
        @apxldimg.sql C:TEMP
        Also having problems getting the Apex admin screen. Could be firewall problems.
        Is port 8082 significant for the Embedded PL/SQL Gateway configuration or can I use 8080?

        Reply

        • Thanks for the feedback Pat. You can use 8080 yes, I just chose 8082 in this example as I already had services running on port 8080.

          If you’re still having problem with the APEX admin screen, I would take a look at this Oracle documentation first to begin with just to make sure the installation was successful, and images loaded correctly.

          Cheers,
          Garth

          Reply

  4. Nice and clear installation instructions. Can you please add instructions on how to install Apex Listener 2.0.1 on standalone and on Glassfish?

    Thanks

    Reply

  5. Hi Garth,

    I’ve installed as per instructions but I can only get page with “Internet Explorer cannot display the webpage”… is there anything I might be missing ? Could be anything related to secuty/firewall ? I’ve basically used HTTP port 48112 which is the one setup when I list the listener services (lsnrctl services)… I’ve also tried just the default 8080 but no lucky as well… btw, I installed APEX on the db server and I’m trying to access remotely from my machine.
    Any thoughts ?
    Thanks in advance for your help !

    Regards,
    -Felipe

    Reply

    • Hi Felipe,

      First off, I would make sure that port 48112 is accessible via your firewall(s)…make sure iptables in Linux isn’t blocking the port, and that your remote server is indeed listening on that port: netstat -na|grep LISTEN|grep 48112

      Presumably running SELECT dbms_xdb.gethttpport FROM dual; returns port 48112?

      Make sure the ACL settings have been configured, as per the “Enable network services (ACL) and XML DB HTTP server” section above. Also check that the XDB, ANONYMOUS, APEX_PUBLIC_USER accounts are unlocked as this has caused me problems in the past.

      If you’re still having no joy, check that the APEX component is indeed valid:

      SELECT comp_name, version, status FROM dba_registry WHERE comp_id='APEX';

COMP_NAME                      VERSION                        STATUS
------------------------------ ------------------------------ -----------
Oracle Application Express     4.2.2.00.11                    VALID

      …and it might be worth looking at the output of @?/rdbms/admin/epgstat.sql in case something DAD wise isn’t quite right.

      Hope this helps…

      Cheers,
      Garth

      Reply

      • Thanks Garth.

        I had checked all that and everything seems fine/correct. I had installed it once before as well…
        I also had an Oracle SR opened to help me with that and we were not having any luck…

        Buuuut, “maybe luckily”, the sever/db were rebooted over this past wkd for maintenance and now it’s working… 🙂

        Thanks again for your help !

        Regards,
        -Felipe

        Reply

      • hi Gartcould you please help. i have followed all the instructions installing apec 4.2 on our 10g database. installation has neen successfull. i also have xml db installed and up and running. also have Tomcat 6 installed . but still cannot connect to apex using the url duggested. what is missing.
        important to mention that i coyld not execute EXEC dbms_xdb.setListenerLocalAccess(l_access => FALSE); simply because this package does not seem to have procedure setListenerLocalAccess at all. since i think this may be the cause of my failure in accessing apex url i wonder why the package does not even have the procedure required

        Reply

  6. Thank you for your post. Very fast tutorial on setuping the Oracle Apex. A good article to avoid complex oracle documentations.

    Reply

  9. Awesome post! These appear to be the most complete instructions for this task available. I’m part way through this task on my own, and am eager to implement some of your suggestions along the way. Thanks again!

    Reply

  10. Installing Apex 4.2 with Oracle 11gR2 was a long frustrating process partially because the documentation is incomplete. Thanks to everyone out there in forums and blogs, I managed to get it done. I’m using EPG. What made the big difference for me was running this script:
    $ORACLE_HOME/rdbms/admin/epgstat.sql
    It showed ANONYMOUS access as FALSE. I ran the script from this page:
    https://community.oracle.com/thread/2344127?tstart=0
    which fixed the problem that epgstat.sql found and set user ANONYMOUS access to TRUE.

    Some other things I did that may have helped:
    – Changed ANONYMOUS password to NULL: alter user ANONYMOUS identified by NULL;

    – It’s hard to find error messages with Apex. To get error messages:
    execute dbms_epg.set_global_attribute(‘log-level’, 7);
    Then look for logs in your Oracle trace directory that start with _s . Or grep for epg or apex in that directory and you’ll see some errors.

    – Switching to brand new port. I made up 8083 as I wasn’t sure some settings somewhere were still stuck for 8080. Setting it back and forth to 0 and back to 8083 seems to help.
    EXEC dbms_xdb.sethttpport(8083);
    EXEC dbms_xdb.sethttpport(0);
    EXEC dbms_xdb.sethttpport(8083);

    – 8083 never showed up in netstat but it doesn’t seem to make a difference

    – Went into OEM and edited acls:
    Log into OEM
    select schema
    select resources
    select images and click edit
    select the security tab
    click edit (the file you should be editing is /sys/acls/ro_anonymous_acl.xml)
    ensure you have the following:
    principal: ANONYMOUS
    privilege: read-contents
    granted: ticked
    click apply
    alter user xdb identified by xdb;

    – Clear the Firefox browser cache; restarting the browser in Safe Mode with plugins off

    – APEX_HOME is the directory many of the apex scripts want. It’s the PARENT directory of the apex directory – the one that it’s unzipped into. In my 11gR2 installation its the same as ORACLE_HOME .

    – With Apex 4.2 there is no need to enter anything into listener.ora or tnsnames.ora if you’re using the PL/SQL gateway (EPG). The listener will show the epg port in it’s status if it’s working. Check the listener.log for connections.

    I’m putting all this here hoping it helps someone else and they won’t have to spend as much time as I did on this.

    Reply

    • Thanks for taking the time to share this feedback Nancy, much appreciated! I too found the process frustrating and missing steps in the documentation, hence my post about the process, hopefully this will be of use to others – thanks again.

      Reply

  11. Get a little hint from this site and I did get solve my problem about to install Oracle Application Express.
    Thanks :-)))
    /Hiep

    Reply

  12. Great work Garth, happily your page turns up high in the Google ranking!
    I followed you instructions word to word on a W7 box and it all installed without a single glitch.

    Reply

Leave a comment

Your email address will not be published. Required fields are marked *