Tag Archives: apex

Upgrade Oracle APEX from 4.2.4 to 4.2.5

Database, System Admin, , , , ,

Introduction

Oracle recently released version 4.2.5 of its Application Express (APEX) software.  This post is to document how to upgrade Oracle APEX from 4.2.4 to 4.2.5.

The Oracle APEX installation to be upgraded has the following specification:

  1. The operating system of the server is CentOS 6.5.
  2. The Oracle Database version is Oracle XE 11gR2.
  3. Oracle APEX is served using the APEX Listener, via GlassFish 4.

Download and read the Patch Set Notes

First things first, read up on the patch set notes here.

Download and unzip the Patch Set

Next, the patch set is downloaded via Oracle Support.

The downloaded patch set is then extracted.

[oracle@server ~]$ unzip p17966818_425_Generic.zip

Backup database

The database is backed up as a precaution if the patch is unsuccessful.

I prefer a cold and consistent backup to ease the recovery process.

[oracle@server ~]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Apr 25 09:38:09 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: XE (DBID=2709648321)
using target database control file instead of recovery catalog

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

RMAN> backup database plus archivelog;

RMAN> alter database open;

database opened
RMAN> exit

Recovery Manager complete.

Prevent access to the APEX instance

No users should be using the APEX installation while it is being upgraded.  Access to the instance should be limited.  In this particular case, since APEX is served by the APEX Listener which is hosted by a GlassFish instance, user access is prevented by shutting down the GlassFish instance.

[oracle@server ~]$ sudo service glassfish4 stop
Shutting down glassfish4: Waiting for the domain to stop ...
Command stop-domain executed successfully.
                                                           [  OK  ]

The above command is used to control the GlassFish installation only if the corresponding service init script has already been correctly created.  An example on how this is done can be found here.

Apply the patch set

Now, set the current directory to the patch directory, and apply the patch.

[oracle@server ~]$ cd patch
[oracle@server patch]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Apr 25 09:38:45 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> @apxpatch.sql

The patch will now be applied, and the process will take a few minutes.

Copy patch directory contents into APEX directory

After the patch process is completed, it is time to copy the contents of the patch directory into the original APEX installation directory.

[oracle@server patch]$ cp -R * /path/to/apex/

This will include the images directory as well, and if the path to the APEX directory is the same one used to originally create the images WAR file, the WAR file need not be recreated.

Restart GlassFish

The final step is to restart the GlassFish instance.

[oracle@server ~]$ sudo service glassfish4 start
Starting glassfish4: Waiting for domain1 to start .....................................................
Successfully started the domain : domain1
domain  Location: /home/gfish/glassfish4/glassfish/domains/domain1
Log File: /home/gfish/glassfish4/glassfish/domains/domain1/logs/server.log
Admin Port: 4848
Command start-domain executed successfully.
                                                           [  OK  ]

Now the APEX installation is successfully updated to version 4.2.5, and is ready to be accessed.

Upgrade Oracle APEX from 4.2.3 to 4.2.4

Database, System Admin, , , , ,

Introduction

Oracle recently released version 4.2.4 of its Application Express (APEX) software.  This post is to document how to upgrade Oracle APEX from 4.2.3 to 4.2.4.

The Oracle APEX installation to be upgraded has the following specification:

  1. The operating system of the server is CentOS 6.5.
  2. The Oracle Database version is Oracle XE 11gR2.
  3. Oracle APEX is served using the APEX Listener, via GlassFish 4.

Download and read the Patch Set Notes

First things first, read up on the patch set notes here.

Download and unzip the Patch Set

Next, the patch set is downloaded via Oracle Support.

The downloaded patch set is then extracted.

[oracle@server ~]$ unzip p17607802_424_Generic.zip

Backup database

The database is backed up as a precaution if the patch is unsuccessful.

I prefer a cold and consistent backup to ease the recovery process.

[oracle@server ~]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jan 7 12:33:41 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: XE (DBID=2709648321)
using target database control file instead of recovery catalog

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

RMAN> backup database plus archivelog;

RMAN> alter database open;

database opened
RMAN> exit

Recovery Manager complete.

Prevent access to the APEX instance

No users should be using the APEX installation while it is being upgraded.  Access to the instance should be limited.  In this particular case, since APEX is served by the APEX Listener which is hosted by a GlassFish instance, user access is prevented by shutting down the GlassFish instance.

[oracle@server ~]$ sudo service glassfish4 stop
Shutting down glassfish4: Waiting for the domain to stop ...
Command stop-domain executed successfully.
                                                           [  OK  ]

The above command is used to control the GlassFish installation only if the corresponding service init script has already been correctly created.  An example on how this is done can be found here.

Apply the patch set

Now, set the current directory to the patch directory, and apply the patch.

[oracle@server ~]$ cd patch
[oracle@server patch]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 7 16:07:39 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> @apxpatch.sql

The patch will now be applied, and the process will take a few minutes.

Copy patch directory contents into APEX directory

After the patch process is completed, it is time to copy the contents of the patch directory into the original APEX installation directory.

[oracle@server patch]$ cp -R * /path/to/apex/

This will include the images directory as well, and if the path to the APEX directory is the same one used to originally create the images WAR file, the WAR file need not be recreated.

Restart GlassFish

The final step is to restart the GlassFish instance.

[oracle@server ~]$ sudo service glassfish4 start
Starting glassfish4: Waiting for domain1 to start .....................................................
Successfully started the domain : domain1
domain  Location: /home/gfish/glassfish4/glassfish/domains/domain1
Log File: /home/gfish/glassfish4/glassfish/domains/domain1/logs/server.log
Admin Port: 4848
Command start-domain executed successfully.
                                                           [  OK  ]

Now the APEX installation is successfully updated to version 4.2.4, and is ready to be accessed.

Move Oracle APEX on Oracle XE 11gR2 from EPG to APEX Listener

Database, System Admin, , , ,

I have been playing with Oracle Application Express (APEX) to make a few applications, but I have never been bothered to switch from the default Embedded PL/SQL Gateway (EPG).  However, after becoming a little bit familiar with APEX and its different configurations, I became interested to explore other ways of deploying APEX.

The purpose of this post is to document the switch-over of my APEX deployment from EPG to APEX Listener.

The OS of the server is CentOS 6.4.  I installed Oracle Express Edition (XE) 11gR2, with which APEX 4.0 is included.  I then upgraded APEX to 4.2.3, and installed GlassFish 4 Open Source Edition on the server.

Next, configure the APEX_PUBLIC_USER user.

 SQL> alter user apex_public_user identified by password account unlock;

After that it is time to disable EPG.

SQL> exec dbms_xdb.sethttpport(0);

Next, go into the APEX unzipped directory and run the configure restful services script.

$ cd apex_install_directory
$ sqlplus / as sysdba
SQL> @apex_rest_config
Enter a password for the APEX_LISTENER user              []
Enter a password for the APEX_REST_PUBLIC_USER user              []

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

...create APEX_LISTENER user

PL/SQL procedure successfully completed.

User created.

...create APEX_REST_PUBLIC_USER user

User created.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Session altered.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Synonym created.

Session altered.

PL/SQL procedure successfully completed.

SQL>

Then, grant connect privileges to the APEX_040200 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;

Download and unzip APEX Listener.

Configure APEX Listener.

# java -jar apex.war
This Listener instance has not yet been configured.
Please complete the following prompts

Enter the location to store configuration data:/root/apex_listener/
Oct 15, 2013 1:31:28 AM oracle.dbtools.common.config.cmds.ConfigDir execute
INFO: Set config.dir to /root/apex_listener/ in: /root/apex_listener/apex.war
Oct 15, 2013 1:31:29 AM oracle.dbtools.common.config.file.ConfigurationFolder logConfigFolder
INFO: Using configuration folder: /root/apex_listener/apex
Enter the name of the database server [localhost]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:2
Enter the database SID [xe]:
Enter the database user name [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]:1
Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:
Oct 15, 2013 1:32:38 AM oracle.dbtools.common.config.file.ConfigurationFiles update
INFO: Updated configurations: defaults, apex, apex_al, apex_rt
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2
#

Configure administrator for APEX Listener

# java -jar apex.war user adminlistener "Listener Administrator"
Oct 15, 2013 1:36:58 AM oracle.dbtools.common.config.file.ConfigurationFolder logConfigFolder
INFO: Using configuration folder: /root/apex_listener/apex
Enter a password for user adminlistener:
Confirm password for user adminlistener:
Oct 15, 2013 1:37:14 AM oracle.dbtools.standalone.ModifyUser execute
INFO: Created user: adminlistener in file: /root/apex_listener/apex/credentials

Create war for static images

# java -jar apex.war static /u01/app/oracle/apex/images
WAR Generation complete
 WAR location     : /root/apex_listener/i.war
 Context path     : /i
 Static resources : /u01/app/oracle/apex/images
Ensure the static resources are available at path: /u01/app/oracle/apex/images

Deploy on GlassFish.

Using nice URLs for APEX

System Admin, ,

So, I have installed the OS to my server, and subsequently installed the latest Oracle Express Database, and now I can start making applications using Oracle Application Express (APEX).

I can access the APEX login page by typing in the following address:

http://host.domain.com:8080/apex/

Notice the ugly :8080 after the hostname? That is because the APEX installation on Oracle XE defaults to using the Embedded PL/SQL Gateway (EPG) and EPG listens to port 8080 by default.

I want to change the URL to a ‘nicer-looking’ one. One way of doing this is to configure EPG to listen to port 80.  This is especially fine if APEX is the sole purpose of the server. However, since the server will be used for other purposes, this is undesirable because EPG cannot serve as a regular webserver, and therefore cannot replace Apache.

Another way to give APEX a nice URL is to use Apache’s reverse proxy feature.  The users will not directly access APEX via EPG but will connect to Apache, which in turn will connect to APEX.

First, set up the proxy directives in apache conf:

<IfModule mod_proxy.c>
#ProxyRequests should be turned off to disable forward proxy
ProxyRequests Off
<Proxy *>
    Order allow,deny
    Allow from all
</Proxy>
</IfModule>

Then put in the actual proxy configuration for APEX:

NameVirtualHost *:80

<VirtualHost *:80>
    DocumentRoot "/var/www/html"
    ServerName host.domain.com
</VirtualHost>

<VirtualHost *:80>
    DocumentRoot "/var/www/html"
    ServerName apex.domain.com

    Redirect    /       http://apex.domain.com/apex/

    <Location /apex/>
        ProxyPass               http://localhost:8080/apex/
        ProxyPassReverse        http://localhost:8080/apex/
    </Location>

    <Location /i/>
        ProxyPass               http://localhost:8080/i/
        ProxyPassReverse        http://localhost:8080/i/
    </Location>
</VirtualHost>

Now the browser will show the APEX login page when you go to apex.domain.com.