Latest Posts

Install and Configure Oracle Application Express with Oracle REST Data Services and Apache Tomcat

In this article I will show you how you can install and configure Oracle Application Express (APEX) and the Oracle REST Data Service (ORDS), which is running on an Apache Tomcat application server. In previous installations I used the Oracle HTTP server and the extenstion mod_plsq. But this does not work anymore with the newest Oracle HTTP server version. In the Oracle Application Express Installation Guide is written:

mod_plsql is deprecated as of Oracle HTTP Server 12c (12.1.3). For more information, please see My Oracle Support Note 1576588.1. Oracle recommends using Oracle REST Data Services instead.

Oracle REST Data Service is the future – so let’s go to the future.

Installation Steps

  1. Installation and Configuration Oracle Application Express 5.1.1
  2. Installation and Configuration Apache Tomcat 8.5.14
  3. Installation and Configuration Oracle Rest Data Service ORDS 3.0.9

My new architecture what I want to build looks like as described on this picture.  

Source: http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-deploy-installation-1878444.html

My Environment
  • Red Hat Enterprise Linux Server Release 7.3 – server hostname is neuendorf.jurasuedfuss.coom
  • Oracle Database 12c Standard Edition Release 12.2.0.1.0 – database service name is APEXORDS.jurasuedfuss.com
  • OS Firewall is open for Port 8080
OS Users

I work with two OS users to separate RDBMS and application tasks:

  • oracle – Oracle RDBMS / Listener / Oracle Application Express
  • tomcat – Apache Tomcat / ORDS / Java Development Kit JDK 1.8
Directories
  • Oracle Software: /u01/app/oracle
  • Apache Tomcat / ORDS / JDK: /u01/app/tomcat

1. Installation and Configuration Oracle Application Express 5.1.1 – OS User: oracle

I have downloaded Oracle Application Express here: http://www.oracle.com/technetwork/developer-tools/apex/downloads/download-085147.html . The extracted software is located on the server in  the /tmp directory. For the APEX data I have created a new tablespace called APEX too.

Go to the software location:

Login into the database as SYSDBA:

Execute the installation script:

Set password for ADMIN user / Workspace INTERNAL:

Configure database RESTful services – the passwords for the new created users APEX_LISTENER and APEX_REST_PUBLIC_USER will be used later for the ORDS setup:

Set password for the APEX_PUBLIC_USER and unlock the account:

To avoid the password expiration for the APEX_PUBLIC_USER, I have created a  new profile especially for this user with unlimited password lifetime:

Allow other hosts than the localhost to use the Oracle Application Express installation:

2. Installation and Configuration Apache Tomcat 8.5.14 – OS User: tomcat

Create directory :

Go to directory and extract software  form /tmp:

Two new directories for Apache Tomcat and JDK are created:

To simplify the management with the Apache Tomcat application server, I have added environment variables to the .bash_profile:

Startup Tomcat – after the re-login as OS user tomcat the application server can be started by using the environment variable $CATALINA_HOME.

Verify on command line level if tomcat has started, for example with CURL – HTTP 200 means that the response is OK:

Browser Verification – http://neuendorf.jurasuedfuss.com:

 

Shutdown Tomcat:

Start- / Stop Runlevel Script:

To automate the start/stop – we use a runlevel script. The script has to be created as OS user root. In one of the first lines, I have set a sleep command to be sure that the database is available before the application server starts.

Content:

Add the script to the runlevel environment level 3 and 5:

3. Installation and Configuration Oracle Rest Data Service ORDS 3.0.9 – OS User: tomcat

Create ORDS installation directory:

Extract ORDS from /tmp directory:

Verify directory content:

Create ORDS directory and edit the database configuration template – set hostname and database service name:

Content of my ords_params.properties file:

Set Oracle REST Database Service ORDS configuration directory:

Install Oracle REST Database Service ORDS:

You will be asked for a new password for the database user ORDS_PUBLIC_USER (user will be created by this script), for the already existing users APEX_LISTENER and APEX_REST_PUBLIC_USER and for the SYS password. For ORDS metadata, I have created a new tablespace called ORDS.

The values for database server, hostname and the service name are taken from the configuration file ords_params.properties. Do not start ORDS in standalone mode at the end of the configuration.

You can see the encrypted passwords and the selected tablespaces in the configuration file ords_params.properties:

Optimize the database connection settings – add these lines to the apex.xml configuration file:

Prepare the application server directory for the Oracle Application Express images

Copy the ORDS application ords.war to the Apache Tomcat:

Startup the application server:

Login into  Oracle Application Express – add /ords at the end of the application server URL like http://neuendorf.jurasuedfuss.com:8080/ords:

That’s it – have fun with the Oracle Application Express, Oracle REST Data Services and Tomcat :-). In the next post I will show you how you can secure your installation by change to https/SSL.

Manuals:

Installing Application Express and Configuring Oracle REST Data Services

Tomcat Web Application Deployment

Oracle Application Express Installation Overview

DATABASE PATCH SET UPDATE 12.1.0.2.170117 apply fails – catconInit failed, exiting

Last weekend was patchday. The goal was to apply the patch 24732082 (DATABASE PATCH SET UPDATE 12.1.0.2.170117) to a 12.1.0.2 database on AIX. The OPatch precheck returned no error and OPatch apply was ok. The problem was the post step, the datapatch command failed with the message catconInit failed, exiting.

The solution was described in this My Oracle Support Note: Datapatch fails with „catconInit: database is not open on the default instance“ (Doc ID 2003488.1)

In the glogin.sql file  located in ORACLE_HOME/sqlplus/admin were two lines:

After I have commented out these lines, everything runs ok.

Summary: Two small lines, a big impact.

Oracle Data Pump with the 12.2.0.1.0 Instant Client for Linux x86-64

From the Oracle Database 12c Release 2 (12.2) New Features Guide:

Adding Oracle Data Pump and SQL*Loader Utilities to Instant Client

This feature adds SQL*Loader, expdp, impdp, exp, and imp to the tools for instant client.

Now you can run these utilities on machines that do not have a complete Oracle Database installation.

The newest release of the Oracle Instant Client for Linux x86-64 has an additional package called Tools. This package contains Data Pump, SQL*Loader and the Workload Replay Client for Real Aplication Testing. The good old import export tools is included too. This is very nice.

For example, if you want to load application log files with SQL*Loader into the database which are located on a separate server, there is no need for the client installation anymore. Or if your developers want to export data with Data Pump, all they need is the Instant Client now.

Just install – or better say unzip – the Instant Client basic package and the tools. Set some variables and go for it. The Instant Client packages are available as zip and as rpm for Unix systems. The rpm method requires root access to install.

Sizes of the Zip-Files

  • Instant Client Package – Basic:  66 Megabyte
  • Instant Client Package – Tools:   1 Megabyte

Zip Download URLs

SQL*Plus® User’s Guide and Reference

https://docs.oracle.com/database/122/SQPUG/SQL-Plus-instant-client.htm#SQPUG157

Instant Client Directory Content

Example for the Instant Client directory content on a Oracle Linux 7.2 server – Basic and Tools package installed.

Let’s Data Pump

Set the environment variables. TNS_ADMIN is where my tnsnames.ora / sqlnet.ora are located.

Start a Data Pump Export

Permission denied

If you get a permission denied error, change the file properties.

Operating System Availability of the Tools Package

At the moment – 22th of March 2017 – the tools packages is only available for these operating systems:

  • Microsoft Windows (x64)
  • Linux x86-64
  • Solaris Operating System (SPARC 64-bit)

I hope the AIX release will coming soon 🙂