Oracle ApEx Installation

Oracle ApEx Installation

Today I want to talk about the process of installing Oracle apex. Once in my company there was a need to create an interface for a small database. We were aware that in the future we would need integration with LDAP, the ability to easily manage user access to web browsing, a convenient tool for building websites and business analytics tools. Then I got acquainted with Oracle application express (apex). This is a powerful tool available for products such as Oracle Database 11g, 12c, which depending on the version can be very expensive. As is often the case, our desires far exceeded our capabilities.

In such cases, it is worth using the free version of the database management system Oracle database Express edition (abbreviated XE). Like any free version of a commercial product, XE ma has some limitations:

  • we can only install one instance on one server.;
  • we can install the system on a multiprocessor server, but we will only use 1CPU;
  • SZBD will have only 1GB RAM available;
  • the amount of user data is limited to 11GB (sometimes in the documentation we can find information about 4GB, but this is outdated data);
  • the built-in web server does not support HTTPS, so you need to use Apache / nginx.

These restrictions were not a problem for us. As a server we used VPs in Digital Ocean, 2GB RAM, we installed Centos 7.2.

Installing Oracle 11g XE

We install the necessary packages, add swap in the form of a file and make the appropriate changes in /etc/fstab:

yum update -y && yum install screen vim bash-completion wget telnet mailx unzip epel-release -y && yum install bc libaio flex -y
dd if=/dev/zero of=/var/swapfile bs=1M count=2048 && chmod 0600 /var/swapfile && mkswap /var/swapfile
echo "/var/swapfile swap swap defaults 0 0" >> /etc/fstab && swapon -a && swapon -s

The next step is optional. We can convert Centos to Oracle Linux and download a package that will change the kernel settings depending on Oracle’s recommendation.

curl -O https://linux.oracle.com/switch/centos2ol.sh && sh centos2ol.sh && yum distro-sync -y && yum install oracle-rdbms-server-11gR2-preinstall.x86_64 -y

To download the Oracle XE package (windows and Linux versions are available), you will need to register on the vendor’s website. It’s free and doesn’t oblige us to do anything. Download ZIP archive, unpack and install:

wget [[wkleić link do ściągnięcia]]
mv oracle-xe-11* oracle-xe-11.x86_64.rpm.zip
mkdir oracle-xe && mv oracle-xe-* oracle-xe/ && cd oracle-xe/
unzip oracle-xe-* && cd Disk1/
rpm -ivh oracle-xe-*

After the installation of the RPM package is complete, we enable Orcale-XE with the configure parameter. Using the window that appears, we define the parameters and complete the installation process. Alternatively, we can end the installation with a response file, where we enter the default ports and the SYS user password:

echo "ORACLE_HTTP_PORT=8080" > /root/oracle-xe/Disk1/response/xe.rsp
echo "ORACLE_LISTENER_PORT=1521" >> /root/oracle-xe/Disk1/response/xe.rsp
echo "ORACLE_PASSWORD=[[Bardzo tajne hasło]]" >> /root/oracle-xe/Disk1/response/xe.rsp
echo "ORACLE_CONFIRM_PASSWORD=[[Bardzo tajne hasło]]" >> /root/oracle-xe/Disk1/response/xe.rsp
echo "ORACLE_DBENABLE=y" >> /root/oracle-xe/Disk1/response/xe.rsp
/etc/init.d/oracle-xe configure responseFile=/root/oracle-xe/Disk1/response/xe.rsp

After the installation is completed using the file with the answers, it is important not to forget the password, keep it in a safe place and remove it from the file xe.rsp.

Application Express Installation

We need to upload the distribution file to the server. The file will also be available on the site after registration. Download and unpack:

cd ~
wget -O apex_5.zip [[tu wklejamy link do załadowania]]
mkdir apex5 && mv apex_5.zip apex5/ && cd apex5/ && unzip apex_5.zip

In File oracle_env.sh there are environment variables, necessary for the correct operation of the SQL * plus client. We add the initialization of this file to the bash configuration and apply these settings to the current environment:

echo ". /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh" >> /etc/bashrc
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

The next step is to update. We need the password that we used when we were working on the question-and-answer file. We introduce it to be able to connect to the database through the SYS user:

cd apex/ && sqlplus sys as sysdba

Using the SQL*Plus Console, we run the following scripts:

@apexins SYSAUX SYSAUX TEMP /i/
@apxldimg /root/apex5
@apxchpwd   [[enter]]
Enter the administrator's username [ADMIN] [[enter]]
User "ADMIN" exists.
Enter ADMIN's email [ADMIN] [[enter]]
Enter ADMIN's password []  [[wklejamy długie hasło]]
Changed password of instance administrator ADMIN.

Last script @apxchpwd changes the admin user password for application express.

The next step can be dangerous, so I recommend using it only in cases where it is necessary. This command allows you to remotely connect to a listener (using SQL clients or other software) that uses the port by default 1521:

EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

I recommend that you restrict connections to this port using the rule iptables.

When connecting to the listener over the internet, for example using SQL Developer, logins/passwords/queries are transmitted in an unsecured form. To prevent this, we need to use the SSH tunnel or VPN.

Using the SQL * plus console, we change the following parameters: the maximum number of sessions, processes, and more. Since these parameters are not dynamic, we will have to restart the entire database. Changing the mode Onlinelog per mode Archivelog, we will increase the limitation of the disk space available to store backups:

alter system set sessions=250 scope=spfile;
alter system set processes=200 scope=spfile;
shutdown normal
startup mount
alter database archivelog;
alter database open;

Creating backups

We store the copies on a separate volume. To have this opportunity, we use the block storage service on digital ocean. After connecting the new chapter to the VPS, we execute the following commands:

mkfs.ext4 -F /dev/disk/by-id/scsi-0DO_Volume_oracle-backups-volume
mkdir -p /opt/oracle-backups
echo "/dev/disk/by-id/scsi-0DO_Volume_oracle-backups-volume /opt/oracle-backups ext4 defaults,nofail,discard 0 0" | tee -a /etc/fstab
mount -a
chown oracle:dba /opt/oracle-backups

They build a file system, a folder for mounting the disk, defines the parameters of automatic mounting when loading into a file /etc/fstab and change folder and backup owners. Enable the SQL*Plus Console and change the backup parameters:

sqlplus sys as sysdba
alter system set DB_RECOVERY_FILE_DEST_SIZE = 20G;
alter system set DB_RECOVERY_FILE_DEST = '/opt/oracle-backups';

At the beginning, we will use the already installed script, which creates backups of the system, available for playback for 2 consecutive days. For the first time, we enable this script using the console, then we need to add it to the Oracle user’s cron:

su - oracle
bash /u01/app/oracle/product/11.2.0/xe/config/scripts/backup.sh

After making changes, restart the server. To turn on oracle-xe manually and turn this process on automatically loading, use the following commands:

systemctl start oracle-xe.service
systemctl enable oracle-xe.service

Web server settings

To encrypt the transmitted data, I use Nginx, the installation of which is very simple:

wget http://nginx.org/keys/nginx_signing.key
rpm --import nginx_signing.key
yum -y install nginx

We copy SSL certificates to the server and make changes to the configuration file of the web server. We can also create a new file in the format .conf in folder /etc/nginx/conf.d/. There must be the following lines that change the path to the certificates, pointing to the correct domain name:

Configuration for nginx:

server {
  listen 80;
  server_name example.com;
  rewrite ^(.*) https://$server_name$1 permanent;
}

server {
  listen 443 ssl;
  server_name example.com;
  resolver 8.8.8.8;
  ssl_stapling on;
  ssl on;
  ssl_certificate   /etc/nginx/keys/cert.crt;
  ssl_certificate_key /etc/nginx/keys/cert.key;
  ssl_dhparam /etc/pki/nginx/dhparam.pem;
  ssl_prefer_server_ciphers on;
  ssl_session_timeout 1d;
  ssl_session_cache shared:SSL:50m;
  ssl_session_tickets off;
  ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
  ssl_ciphers kEECDH+AES128:kEECDH:kEDH:-3DES:kRSA+AES128:kEDH+3DES:DES-CBC3-SHA:!RC4:!aNULL:!eNULL:!MD5:!EXPORT:!LOW:!SEED:!CAMELLIA:!IDEA:!PSK:!SRP:!SSLv2;

  location / {
    rewrite ^ "/app/f?p=101" permanent;
  }

  location /app {
    proxy_pass http://127.0.0.1:8080/apex;
    include /etc/nginx/reverse_proxy.conf;
  }

  location /i/ {
    proxy_pass     http://127.0.0.1:8080/i/;
    include /etc/nginx/reverse_proxy.conf;
  }
}

Contents of/etc/nginx / reverse_proxy.conf:

proxy_set_header  Host       $http_host;
proxy_set_header  X-Real-IP    $remote_addr;
proxy_set_header  X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_max_temp_file_size 0;

client_max_body_size    1000m;
client_body_buffer_size  128k;

proxy_connect_timeout   180;
proxy_send_timeout     180;
proxy_read_timeout     180;

proxy_buffer_size     4k;
proxy_buffers       4 32k;
proxy_busy_buffers_size  64k;
proxy_temp_file_write_size 64k;

Explanation: when going to the address http://example.com you will be redirected to http://example.com/app/f?p=101, this address will be processed by proxy_pass, which will redirect traffic to http://127.0.0.1:8080/apex with an indication of the appropriate web application number, in this case 101. Do not forget to check the configuration, turn on the web server and load it automatically at system startup.

nginx -t
systemctl start nginx
systemctl enable nginx

This completes the installation of Oracle application express.

Go to our cases Get a free quote