12c Oracle Application Express - APEX

This is a guide/document on how to install APEX 4.2.2 using Apex Listener 2.0 installed on Weblogic 12c.

## All the work is being done on a single server on AWS as it houses the database and weblogic server software ##

## It assumes the s/w is already downloaded - /u01/app/oracle/product/11.2.0 ##

Installing APEX 4.2 


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

Login to EC2 IP as oracle and set environment setdb_apexdb.sh
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/product/11.2.0/apex_4.2.2.zip -d $ORACLE_HOME
cd $ORACLE_HOME/apex


Install APEX 4.2.2


Connect as SYS again, and create a new APEXTBS tablespace :

sqlplus / as sysdba
CREATE TABLESPACE APEXTBS DATAFILE '/oracledb/oradata/EC2OEM/EC2OEM/apextbs01.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 APEXTBS APEXTBS 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

<< add it to keypass >>

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

Update the APEX images with those from the new release:

@apxldimg.sql /u01/app/oracle/product/11.2.0/db_1
 

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
NAME TYPE VALUE
------------------------------------
job_queue_processes integer 1000

 

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

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://EC2 IP:8082/apex/apex_admin

## This won't work as I believe the ports (8082) need to be opened up ##

 

Creating an Apex_domain in WebLogic 12c

It is best to create another domain on the OEM server and not mix up the existing OMS with an Apex install:

Launch X-windows on your PC and obtain your IP adress
Login to EC2 IP as oracle and set environment setdb_oms.sh
cd $WLS_HOME/common/bin
./config.sh

Once the GUI starts, respond as follows:

  • Welcome
    • Create a new WebLogic domain
    • Next
  • Select Domain Source
    • Generate a domain configured automatically to support the following products
      • Selecting the ‘Oracle Enterprise Manager’ component here, which will automatically select ‘Oracle JRF’ (Java Required Files), although it’s by no means compulsory.
      • Leave everything else unchecked.
    • Next
  • Specify Domain Name and Location
    • Domain name: Apex_domain
    • Domain location: /u01/app/oracle/Middleware/gc_inst/user_projects/domains
    • Application location: /u01/app/oracle/Middleware/user_projects/applications
    • Next
  • Configure Administrator User Name and Password
    • Name: weblogic
    • User password: ***********
    • Confirm user password: **********
    • Description: This user is the default administrator.
    • Next
  • Configure Server Start Mode and JDK
    • WebLogice Domain Startup Mode: Development Mode (unless this is for a Production environment)
    • Available JDKs: Sun SDK 1.6.0_24 (this will vary depending on your installation)
    • Next
  • Select Optional Configuration
    • Tick ‘Administration Server’
    • Tick ‘Managed Servers, Clusters and Machines’
    • Next
  • Configure the Administration Server
    • Name: AdminServer
    • Listen address: EC2 IP
    • Listen port: 7001
    • Tick ‘SSL enabled’
    • SSL listen port: 7002
    • Next
  • Configure Managed Servers
    • Add
      • Name: ManagedServer_1
      • Listen address: EC2 IP
      • Listen port: 7010
      • Tick ‘SSL enabled’
      • SSL listen port: 7510
    • Next
  • Configure Clusters
    • Next (we’re not clustering here)
  • Configure Machines
    • Select the ‘Unix Machine’ tab
    • Add
      • Name: EC2
      • Node manager listen address: EC2 IP
      • Node manager listen port: 5556
    • Next
  • Assign Servers to Machines
    • Assign Server AdminServer to Machine EC2
    • Assign Server ManagedServer_1 to Machine EC2
    • Next
  • Configuration Summary
    • Check the details
    • Create
    • Done

Start your Admin Server

Now you can startup your Admin Server and tail the log file to check everything starts as expected:

nohup $MW_HOME/gc_inst/user_projects/domains/Apex_domain/startWebLogic.sh > /dev/null 2>&1 &
sleep 10
tail -f $MW_HOME/gc_inst/user_projects/domains/Apex_domain/servers/AdminServer/logs/AdminServer.log

…wait for a couple of minutes and look out for the following to ensure the Admin Server starts up correctly:

<BEA-000360> <Server started in RUNNING mode>

Then you can test connectivity to the Administration Console using the following URL: http://EC2 IP:7001/console

Start your Node Manager

Start the Node Manager process (doing so allows you to start Managed Server using the Administration Console):

nohup $WLS_HOME/server/bin/startNodeManager.sh > /dev/null 2>&1 &
sleep 2
cat $WLS_HOME/common/nodemanager/nodemanager.log

Start your Managed Server

Now the Managed Server can then be started from within the Administration Console above (Environment > Servers > Control):

## on EC2 IP there wasn't sufficient memory to start ManagedServer_1 so I left it down ##

You can start it manually by running the following:

nohup $MW_HOME/gc_inst/user_projects/domains/Apex_domain/bin/startManagedWebLogic.sh ManagedServer_1 > /dev/null 2>&1 &
sleep 5
tail -f $MW_HOME/gc_inst/user_projects/domains/Apex_domain/servers/ManagedServer_1/logs/ManagedServer_1.log

The Managed Server will take several minutes start, unlike the Admin Server, but again, look out for the following message to ensure it starts up correctly:

<BEA-000360> <Server started in RUNNING mode>

Installing and configuring the APEX Listener 2.0.1 on WebLogic 12c

 

The Oracle APEX Listener is a Java based alternative to using Oracle HTTP Server (OHS) with mod_plsql.  Alternative options to deploying the APEX Listener on WebLogic would be to use Oracle Glassfish Server or the standalone mode.  The use of HTTPS isn’t supported using standalone mode though, and in the real world should only be used for development purposes.  WebLogic and Glassfish are much more scalable, flexible and secure options…

Unzip the APEX software

First of all, you need both the APEX Listener, and the APEX 4.2.2 software present on your WebLogic server (APEX 4.2.2 binaries are required for the images):

Login to EC2 IP as oracle and set environment setdb_apexdb.sh
cd /u01/app/oracle/product/11.2.0
unzip apex_listener.2.0.2.133.14.47.zip -d /u01/app/oracle/product/11.2.0/apex_listener

Configure the APEX Listener

By default, the APEX Listener configuration files are stored in /tmp/apex, which obviously isn’t a good place to keep them.  Running the following command will specify an alternative location to store the configuration:

oracle 11gR2 Cloud OEM DB RDBMS: java -jar apex.war configdir /u01/app/oracle/product/11.2.0/apex_listener
Aug 20, 2013 11:09:53 AM oracle.dbtools.common.config.cmds.ConfigDir execute
INFO: Set config.dir to /u01/app/oracle/product/11.2.0/apex_listener in: /u01/app/oracle/product/11.2.0/apex_listener/apex.war

 

it’s a good idea to make sure the APEX_PUBLIC_USER account (or whichever user account you specified above) is unlocked and matches the password supplied above, otherwise you’ll hit “503 – Service Unavailable” messages when trying to access APEX later on:

 

SELECT username, account_status FROM dba_users WHERE username = 'APEX_PUBLIC_USER';
ALTER USER apex_public_user identified by ******** ACCOUNT UNLOCK;

 

Next, we configure the database connection details appropriate to where APEX 4.2.2 was installed: -check the APEX_PUBLIC_USER password on the EC2OEM database-


 

oracle 11gR2 Cloud OEM DB RDBMS: java -jar apex.war configdir /u01/app/oracle/product/11.2.0/apex_listener
Aug 20, 2013 11:09:53 AM oracle.dbtools.common.config.cmds.ConfigDir execute
INFO: Set config.dir to /u01/app/oracle/product/11.2.0/apex_listener in: /u01/app/oracle/product/11.2.0/apex_listener/apex.war
oracle 11gR2 Cloud OEM DB RDBMS: java -jar apex.war setup
Aug 20, 2013 11:10:09 AM oracle.dbtools.common.config.file.ConfigurationFolder logConfigFolder
INFO: Using configuration folder: /u01/app/oracle/product/11.2.0/apex_listener/apex
Enter the name of the database server [localhost]:EC2 IP
Enter the database listen port [1521]:1521
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:1
Enter the database service name:APEXDB.COM
Enter the database user name [APEX_PUBLIC_USER]:APEX_PUBLIC_USER
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to enter passwords for the RESTful Services database users (APEX_LISTENER,APEX_REST_PUBLIC_USER), 2 to use the same password as used for APEX_PUBLIC_USER or, 3 to skip this step [1]:2
Aug 20, 2013 11:11:55 AM oracle.dbtools.common.config.file.ConfigurationFiles update
INFO: Updated configurations: defaults, apex, apex_al, apex_rt

Create a Web Archive (WAR) file for the APEX images

oracle 11gR2 Cloud OEM DB RDBMS: java -jar apex.war static /u01/app/oracle/product/11.2.0/apex/images
WAR Generation complete
WAR location : /u01/app/oracle/product/11.2.0/apex_listener/i.war
Context path : /i
Static resources : /u01/app/oracle/product/11.2.0/apex/images

NOTE: This WAR file contains only references to the image files on disk, it doesn’t contain the images themselves, so don’t be tempted to remove the APEX images directory or rename it afterwards

 

Deploy the WAR files

 

With the Admin Server up and running, log into the WebLogic Administration Console:

 

http://EC2 IP:7001/console

 

NOTE: If you’re running in Production mode, you’ll need to click the Lock & Edit button before continuing.

 

First of all we’ll deploy the apex.war file…

 

Select Deployments from underneath the Domain Structure, then click Install.

 

 

Navigate to /u01/app/oracle/product/11.2.0/apex_listener and select the apex.war file created earlier, click Next.

 

 

Leave the Install this deployment as an application option select and click Next.

 

 

Select the target server(s)/cluster(s) for deployment of the application and click Next - It is best to deploy into a ManagedServer but AdminServer was all that was up

 

 

Make sure the deployment is named ‘apex’ and use the following security model:

 

Custom Roles: Use roles that are defined in the Administration Console; use policies that are defined in the deployment descriptor

 

Leave everything else as defaults. then click Next.

 

 

Finally, select No I will review the configuration later and click Finish.

 

 

You’ll be returned to the Deployments page at this point.

 

Now repeat the process above, but this time deploy the i.war in /u01/app/oracle/product/11.2.0/apex_listener file, ensuring the deployment name is left as ‘i’.

Make sure you activate changes:

Configure WebLogic to deal with HTTP Basic Authentication 

WebLogic will attempt to intercept all HTTP Basic Authentication requests by default, so this needs to be disabled before the APEX Listener works correctly:

export DOMAIN_HOME=/u01/app/oracle/Middleware/gc_inst/user_projects/domains/Apex_domain
cd $DOMAIN_HOME/config
cp config.xml config.xml.bak
vi config.xml

Add the following element:

<enforce-valid-basic-auth-credentials>false</enforce-valid-basic-auth-credentials>

…into the config.xml file before closing tag </security-configuration>:

<security-configuration>
...
<enforce-valid-basic-auth-credentials>false</enforce-valid-basic-auth-credentials>
</security-configuration>

Save the changes, and then restart WebLogic:

$DOMAIN_HOME/bin/stopWebLogic.sh
nohup $MW_HOME/gc_inst/user_projects/domains/Apex_domain/startWebLogic.sh > /dev/null 2>&1 &
nohup $MW_HOME/gc_inst/user_projects/domains/Apex_domain/bin/startManagedWebLogic.sh ManagedServer_1 > /dev/null 2>&1 &

Check the deployments and test APEX connectivity

Once both WAR files have been deployed, and the WebLogic servers restarted as above, log back into the console and check that the deployments are active and healthy:

If they aren’t active, then start them by clicking on Start and Servicing all requests.

Finally, test connectivity using the new APEX Listener deployment to your APEX installation and you should get to the usual APEX login prompt:

 

http://EC2 IP:7001/apex

 

Workspace - INTERNAL

Username - Admin

Password - **********

NOTE: On first login you will be prompted to change the password.