Install Oracle ApEx

Hello friends and colleagues. Once in the company there was a need to create a web interface for a small database. Even then, there was an understanding that in the future, integration with LDAP, the ability to flexibly manage user access rights to browse certain pages, a convenient designer for creating pages, and business intelligence tools would be needed. That's when I met Oracle Application Express (ApEx). This powerful tool is included in products such as Oracle Database 11g, 12c, which, depending on the edition used, can cost a lot. As often happens, desires exceeded the possibilities ...

For such cases, Oracle has a free version of Oracle Database Express Edition Database (XE for short). The following restrictions apply:

• only 1 instance can be installed on one server;
• can be installed on a multiprocessor server, but only 1 CPU will be used;
• A maximum of 1Gb RAM is available for the DBMS;
• The size of user data is limited to 11Gb (some documents still contain a 4Gb limit, but this is already outdated information);
• HTTPS is not supported by the embedded web server, for this it is necessary to use Apache / Nginx.

We are quite satisfied with these restrictions. For the server, we took VPS to Digital Ocean with 2Gb RAM, installed Centos 7.2. The next chapter will describe the installation of Oracle XE, and then update the embedded Application Express v.4 to the latest actual version (as of this writing, 5.0.4).

Install Oracle 11g XE

Install the necessary packages, add the swap as a file and make 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. You can convert Centos to Oracle Linux, then download the package that changes the kernel settings in accordance with the recommendations of Oracle, details can be found here .

 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 distribution (versions are available for Windows and Linux), registration on the vendor’s website is required, it is free and does not oblige to anything. Download the zip-archive, unpack it and install:

 wget [[    ]] 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 installing the RPM package, you need to run oracle-xe with the configure parameter — in this case, the setup completion wizard starts, asking questions in interactive mode. Installation is also available using an answer file, into which we will enter the default ports and the SYS account 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=[[  ]]" >> /root/oracle-xe/Disk1/response/xe.rsp echo "ORACLE_CONFIRM_PASSWORD=[[ ]]" >> /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 installing using the answer file, be sure to keep the password in a safe place and remove it from the xe.rsp file.

Installing Application Express

It is necessary to upload the distribution kit to the server, which is also available after registering on the site. Download and unpack:

 cd ~ wget -O apex_5.zip [[    ]] mkdir apex5 && mv apex_5.zip apex5/ && cd apex5/ && unzip apex_5.zip 

The oracle_env.sh file contains the environment variables necessary for the operation of the SQL * Plus client, we add initialization of this file to the bash parameters and apply 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 

Let's start the update. Prepare the password that was used in the answer file and enter it - this is necessary to connect to the database under the SYS account:

 cd apex/ && sqlplus sys as sysdba 

From the SQL * Plus console you need to 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 [] [[     ]] Changed password of instance administrator ADMIN. 

The latest @apxchpwd script changes the admin account password for Application Express. The next step is somehow not secure, use it only when it is really necessary: ​​this command allows remote connections to the Listener (using SQL clients or various other software) using the default port 1521:

 EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE); 

I recommend limiting connections to this port with the appropriate iptables rule.
When connecting to Listener via the Internet, for example, using SQL Developer, logins / passwords / requests are transmitted in clear text. To avoid this, you need to use SSH-tunnel or VPN.

From the SQL * Plus console, we will change other important parameters: the maximum number of sessions, processes, since these parameters are not dynamic, you will need to restart the database. We will also switch from Onlinelog mode to Archivelog mode and increase the limit of disk space available for 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; 

Backup

Backups we put on a separate volume. To do this, we will use the Digital Ocean hosting Volumes service and after connecting the new section to the VPS, we will 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 

These commands create a file system, a folder for mounting the disk, set the automount parameters when loading into the / etc / fstab file and change the owners of the folder for backups. Run the SQL * Plus console and change the backup settings:

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

To begin with, we will use the pre-installed backup script with the recovery window in 2 days. We will start the first run from the console, but in the future it will be necessary to add this script to the cron of the oracle user:

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

After all the changes you will need to restart the server. To start the oracle-xe service manually and turn on autoload, the following commands are useful:

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

Web server setup

To ensure that all traffic is encrypted, I decided to use Nginx.
Its installation is extremely simple:

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

Then you need to copy the SSL certificates to the server and make changes to the configuration file of the web server, or create a new one with the .conf extension in the /etc/nginx/conf.d/ folder. There you need to add the following lines, changing the paths to the certificates and specifying the appropriate domain name:

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

 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; 

Explanations: when going to http://example.com/, it will be redirected to http://example.com/app/f?p=101 , this address will be processed by the proxy_pass directive, which will redirect traffic to http: //127.0 .0.1: 8080 / apex with the corresponding web application number, in this case 101.
Do not forget to check the configuration, then start the web server and enable it in autoload:

 nginx -t systemctl start nginx systemctl enable nginx 

This completes the installation of Oracle Application Express. In the next article, we will install a test web application, and then do something really useful.

Update. In connection with the wishes, the frontend description has been added to the article, which provides TLS encryption.

I will be sincerely glad to your comments, comments, questions.