Install Oracle ApEx
Hello friends and colleagues. Once in a company there was a need to create a web interface for a small database. Already then there was an understanding that in the future integration with LDAP would be needed, the ability to flexibly control user access rights to view certain pages, a convenient designer for creating pages, and business intelligence tools. It was then that I met with Oracle Application Express (ApEx). This powerful tool is part of 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 the Oracle Database Express Edition (XE for short). The following restrictions are imposed on it:
- only 1 instance can be installed on one server;
- can be installed on a multiprocessor server, but only 1 CPU will be used;
- maximum 1Gb RAM is available for DBMS;
- the size of user data is limited to 11Gb (in some documents there is still a 4Gb limit, but this is already outdated information);
- HTTPS is not supported by the built-in web server, for this you have to use Apache / Nginx.
We were 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 - updating the built-in Application Express v.4 to the latest current version (at the time of this writing - 5.0.4).
Install Oracle 11g XE
Install the necessary packages, add swap as a file and make changes to / 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 -sThe next step is optional. You can convert Centos to Oracle Linux, then download a package that changes the kernel settings in accordance with the recommendations of Oracle, details can be read 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 -yTo download Oracle XE distribution ( available versions for Windows and Linux) need to register on the site of the vendor, it is free and no non-binding. 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 will start, which will ask questions interactively. An installation using the answer file is also available, in which we will add the default ports and password for the SYS account:
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.rspAfter installing using the answer file, be sure to save the password in a safe place and remove it from the xe.rsp file.
Install Application Express
You must download 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.zipThe oracle_env.sh file contains the environment variables necessary for the SQL * Plus client to work, add the initialization of this file to the bash parameters and apply it 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.shLet'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 sysdbaFrom the SQL * Plus console, 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 somewhat unsafe, use it only when it is really necessary: this command allows remote connections to the Listener (using SQL clients or various other software), which uses port 1521 by default:
EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);I recommend limiting connections to this port with the corresponding iptables rule.
When connecting to the Listener via the Internet, for example, using SQL Developer, logins / passwords / queries are transmitted in clear text. To avoid this, you must use an SSH tunnel or VPN.
From the SQL * Plus console, we will change other important parameters: the maximum number of sessions, processes, as these parameters are not dynamic, you 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
We put backups on a separate volume. To do this, we will use the Digital Ocean hosting service Volumes 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-backupsThese commands create the file system, a folder for mounting the disk, prescribe the auto-mount options when loading into the / etc / fstab file and change the owners of the backup folder. Launch the SQL * Plus console and change the backup options:
sqlplus sys as sysdba
alter system set DB_RECOVERY_FILE_DEST_SIZE = 20G;
alter system set DB_RECOVERY_FILE_DEST = '/opt/oracle-backups';To get started, we will use a predefined script that backs up with a recovery window of 2 days. The first launch we will carry out from the console, but in the future it will be necessary to add this script to the oracle user cron:
su - oracle
bash /u01/app/oracle/product/11.2.0/xe/config/scripts/backup.shAfter all the changes, you will need to restart the server. To start the oracle-xe service manually and enable it in startup, the following commands are useful:
systemctl start oracle-xe.service
systemctl enable oracle-xe.serviceWeb server setup
In order for all traffic to be 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 nginxThen you need to copy SSL certificates to the server and make changes to the configuration file of the web server, or create a new one with the extension .conf in the folder /etc/nginx/conf.d/. You need to add the following lines there, changing the path 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;
}
}
the contents of the file /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: the transition at http://example.com/ will be redirect to http://example.com/app/f?p=101 , this address will be processed by the directive proxy_pass, which redirects 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 turn it on at startup:
nginx -t
systemctl start nginx
systemctl enable nginxThis completes the installation of Oracle Application Express. In the next article, we will install a test web application and then do something truly useful.
Update In connection with the wishes, a description of the frontend that provides TLS encryption has been added to the article.
I will be sincerely glad to your comments, comments, questions.
