Installing SSL enabled APEX 4.1 with development environment and APEX Listener on Weblogic and standalone

This article presents a way to set SSL enabled APEX 4.1 on weblogic using APEX listener. The final part shows the process to configure standalone APEX listener and the use of Grizzly in it.

This post is divided in 4 sections

1. Installing APEX schema and related DB components

2. Installing APEX listener

3. Time to configure the listener

4. APEX Listener in standalone mode

Installing APEX schema and related DB components

1. Make sure that the SHARED_POOL_SIZE is 100 MB. The method to set this depends on whether you are using a server parameter file (spfile) or a initialization parameter file

2. Free disk space requirement if English only installation is used, is 300MB. Tablespace in which we want to install the APEX schema should have 125 MB of free space. We will pass this tablespace as an argument while running apexins script. Free system tablespace should be 75MB. Each additional language requires 60MB of additional tablespace

Use the following query for finding this out. I am installing my APEX in SYSAUX schema (See step 7) so I have that in my where clause

SELECT t.TABLESPACE, t.totalspace AS " Totalspace(MB)",
       ROUND ((t.totalspace - fs.freespace), 2) AS "Used Space(MB)",
       fs.freespace AS "Freespace(MB)",
       ROUND (((t.totalspace - fs.freespace) / t.totalspace) * 100,
              2
             ) AS "% Used",
       ROUND ((fs.freespace / t.totalspace) * 100, 2) AS "% Free"
  FROM (SELECT   ROUND (SUM (d.BYTES) / (1024 * 1024)) AS totalspace,
                 d.tablespace_name TABLESPACE
            FROM dba_data_files d
        GROUP BY d.tablespace_name) t,
       (SELECT   ROUND (SUM (f.BYTES) / (1024 * 1024)) AS freespace,
                 f.tablespace_name TABLESPACE
            FROM dba_free_space f
        GROUP BY f.tablespace_name) fs
WHERE t.TABLESPACE = fs.TABLESPACE AND t.TABLESPACE IN ('SYSAUX', 'SYSTEM')
/

3. Oracle XML DB should be installed. If you have not explicitly removed it, it should be there. Installer does a prerequisite test, so if it is not there then installer will exit. XML DB installation creates a user called ANONYMOUS. This user is required by APEX. In fact, if we want to access some custom procedure via http request, we have to grant execute on that procedure to ANONYMOUS

4. Run the following query to get the version of PLSQL Web Toolkit. This version should be greater than 10.1.2.0.6

select owa_util.get_version from dual;

5. Stop the listener. One way or the other, ensure that no one is accessing the database during installation time.

6. Disable Password complexity rules for DEFAULT profile. This is disabled by default

7. Run the following command in sqlplus after switching to the directory in which apex_4.1_en.zip has been unzipped.

@apexins tablespace_apex tablespace_files tablespace_temp images

Eg: @apexins SYSAUX SYSAUX TEMP /i/

Installation log is generated in the directory from which the installation was started. Search this file for possible errors. The format of the log file name is installYYYY-MM-DD_HH24-MI-SS.log

In case of successful installation you should be able to find the below message in the installation log

8. Run the following select statement. The status should be valid

SELECT STATUS FROM DBA_REGISTRY WHERE COMP_ID = ‘APEX’;

9. Change the password for the ADMIN account. We will use this to login in to the APEX workspace using admin account

    Change the directory in which you have unzipped the installer, connect using sys and enter the following command

     @apxchpwd

10. Restart your listener which you had stopped before the start of the installation to stop incoming requests to the database

11. Unlock APEX_PUBLIC_USER and change its password

alter user APEX_PUBLIC_USER account unlock;

alter user APEX_PUBLIC_USER identified by new_password;

12. Run the following commands. This is to ensure that the password of APEX_PUBLIC_USER does not expire after every few days

CREATE PROFILE
   apex_public_user_profile
LIMIT
   PASSWORD_LIFE_TIME UNLIMITED;
ALTER USER apex_public_user PROFILE apex_public_user_profile;

13. Enable Network Services in Oracle Database 11g

      This is required for

a. Sending outbound mail in Oracle Application Express. Users can call methods from the APEX_MAIL package, but issues arise when sending outbound email.

b. Using Web services in Oracle Application Express.

c. PDF/report printing.

DECLARE
   acl_path   VARCHAR2 (4000);
   acl_id     RAW (16);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_040100
   -- the "connect" privilege if APEX_040100 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;
-- Before checking the privilege, ensure that the ACL is valid
   -- (for example, does not contain stale references to dropped users).
   -- If it does, the following exception will be raised:
   --
   -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040100'
   -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
   --
   SELECT sys_op_r2o (EXTRACTVALUE (p.res, '/Resource/XMLRef'))
     INTO acl_id
     FROM xdb.xdb$acl a, path_view p
    WHERE EXTRACTVALUE (p.res, '/Resource/XMLRef') = REF (a)
      AND EQUALS_PATH (p.res, acl_path) = 1;
   DBMS_XDBZ.validateacl (acl_id);
   IF dbms_network_acl_admin.check_privilege (acl_path,
                                              'APEX_040100',
                                              'connect'
                                             ) IS NULL
   THEN
      dbms_network_acl_admin.add_privilege (acl_path,
                                            'APEX_040100',
                                            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_040100',
                        TRUE,
                        'connect'
                       );
      dbms_network_acl_admin.assign_acl ('power_users.xml', '*');
END;

 
Installing APEX listener
Oracle APEX listener is a J2EE web archive(war) and gives an alternative environment other than OAS+modplsql. It can be used in combination with any of the following 3 webservers or can be used in standalone mode
Oracle WebLogic Server 11g Release 1 (10.3.3) or higher
Oracle GlassFish Server Release 3 or higher
Oracle Containers for J2EE Release 10.1.3.4 or higher
Before APEX listener, the only 2 possibilities were OAS+modplsql or built in gateway which used XML DB HTTP server. The inbuild server is not suggested for large application so the OAS+modplsql was the hobson’s choice for applications with considerable load.
Apex listener gives the flexibility to use weblogic as the webserver. Before APEX listener, if we wanted to use weblogic, we had to use a weblogic envelope around the server which already had APEX running. This basically meant that we had to use either weblogic+OAS or weblogic+XML DB HTTP server. Independent use of weblogic was not possible. This process of using weblogic with internal XML DB HTTP server is described in the following link
http://christopherbeck.wordpress.com/2008/09/15/weblogic-server-and-apex/
Following are the requirements of APEX listener
a.    Oracle Database (Enterprise Edition, Standard Edition or Standard Edition One) release 10.2.0.3 or higher, or Oracle Database 10g Release 2 Express Edition or higher.
b.    Java 6 Update 20 JDK or higher.
c.    Java Servlet Specification 2.3 or higher.
1.    Go to the <directory in which you unzipped  apex_4.1_en.zip>\apex\images
For example
cd D:\ApexInstallation\apex\images
I had unzipped apex_4.1_en.zip in D:\ApexInstallation


Run the following command.
jar -cvf0 D:\ApexInstallation\i.war .


The little dot at the end of the command is because we want to include all files in the images directory in the i.war package so don’t miss it.

This command will generate i.war file in D:\ApexInstallation directory. We will have to upload it on weblogic at a later step
2.    Create a weblogic domain.
If you do not want your passwords to be transmitted over the network in plain text then select the ssl option while creating the domain


3.    Install apex.war in the domain which you created and select your managed server when the wizard asks you to associate a server with apex.war. You will find apex.war in the directory in which you have unzipped apex listener.
Select the following security model for apex.war. Wizard will let you select this when you install apex.war in your domain
Custom Roles: Use roles that are defined in the Administration Console; use policies that are defined in the deployment descriptor.Keep the other default selected options
Install i.war. Use the following security model for i.war
DD Only: Use only roles and policies that are defined in the deployment Descriptors
4.    Start the node manager
Open command prompt
Goto <BEAHome>\wlserver_10.3\server\bin
And then run
startNodeManager




5.    Login to the admin consolehttps://localhost:7002/console
Goto Machines under Environment in Domain Structure on the left side of the page
Create a new machine


6.    Go to Servers under Environment in Domain Structure and associate the newly created machine with the managed server

7.    If the managed server is not started then click on the Control tab on the same window, select the managed server and click the Start button




8.    Configuring users
Click on security realms in Domain Structure on the left side. Click on myrealm
Click on Users and Groups and create 2 new users (adminlistener & managerlistener). Managerlistener will be used to access the listener status page and adminlistener will be used to access the listener admin page. The respective powers will be assigned to these users by assigning roles to them




9.    Select Roles and Policies under Security Realms and then select Realm Roles
Under Deployments, select apex and then click on Roles

On the Stand-Alone Web Application Scoped Roles table, click New.Enter the name as Admin and the role mapper as XACMLRoleMapper
10.   
        a.    Click the newly created Admin role.
        b.    The Edit Stand-Alone Web Application Scoped Roles page displays.
        c.    Click on Add Conditions.
        d.    Select  User from the predicate list
        e.    In the User argument name, put adminlistener and click Add and then Finish. After addition, my page looks  like the following

        f.    Repeat the same process to add managerlistener to a different role called Manager
        g.    After addition, the page should look like the following



Time to configure the listener


1.    Go to http://<host>:<port>/apex/listenerConfigure
        Eg: https://localhost:7503/apex/listenerConfigure
Note that we are using 7503 as the port. This is the ssl enabled port of the managed server. See the 3rd screenshot in step 2 under Installing APEX Listener. We had selected the managed server while installing apex.war and i.war so these applications are accessible on port 7503
       The world is yours, configure APEX_PUBLIC_USER and anything else you like
 

Use your admin listener account here
adminlistener
url: https://localhost:7503/apex/listenerAdmin

Other important urls arehttp://<host>:<port>/apex/listenerConfigurehttp://<host>:<port>/apex/listenerStatushttps://localhost:7503/apex/resourceTemplates/
Use managerlistener for http://<host>:<port>/apex/listenerStatus


2.    After the listener config is complete, we can access our apex console using the following url

APEX Listener in standalone mode

The best part of the standalone mode is that it does not need an external webserver. The web archive, apex.war, hosts itself on port 8080. Now obviously, every application needs a web server on which it can be hosted. APEX Listener uses Grizzly to build scalable webserver. More info on Grizzly can be found at

http://grizzly.java.net/

HTTP server API of Grizzly can be found at

http://grizzly.java.net/nonav/docs/2.0/apidocs/org/glassfish/grizzly/http/server/HttpServer.html

Grizzly can be embedded with other applications to provide HTTP services.

https://blogs.oracle.com/oleksiys/entry/grizzly_2_0_httpserver_api

Now let me put a small demo of Grizzly and APEX Listener in action by enabling standalone APEX Listener

1. If you have anything running on port 8080 then switch it off. I have the express edition of Oracle 10G database running on my machine. XML HTTP Server is bundled with express edition and this server runs on port 8080. In fact, this server is used to service HTTP requests for built in apex of express edition.

I switched it off by logging in as sys and running the following command

EXEC DBMS_XDB.SETHTTPPORT(0);

2. APEX Listener works with some of the later releases of JDK 1.6.xxx. JDK 1.7 and later is best for APEX Listener

3. Get into <JDK_Home>\bin directory and run the following command

java –jar <Directory in which APEX Listener is extracted>\apex.war

4. My XML HTTP server was up on port 8080 so I got a few errors. I closed my command prompt and stopped XML HTTP server using the command

EXEC DBMS_XDB.SETHTTPPORT(0);

and reran the command

java –jar <Directory in which APEX Listener is extracted>\apex.war

The result was right in front of me . Note that all the links mentioned above such as

http://<host>:<port>/apex/listenerConfigure

work on the newer port(8080) as well