Oracle 11g XE with Apex 18.1 on the AWS EC2

1. Creation a VPC

http://docs.aws.amazon.com/AmazonVPC/latest/UserGuide/VPC_Scenario1.html

2. Make a SSH connection

http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/AccessingInstancesLinux.html

3. Update the operating system

sudo yum update

4. Create a swap file

SWAP = 2*RAM; SWAP>=2G

sudo dd if=/dev/zero of=/swapfile bs=1K count=2M
sudo mkswap /swapfile
sudo chmod 600 /swapfile
sudo swapon /swapfile

5. Add a swap entry to the fstab and edit the tmpfs entry

sudo vim /etc/fstab

/swapfile   swap        swap    defaults        0   0
tmpfs /dev/shm tmpfs size=1G 0 0

6. Go to the /tmp

cd /tmp

7. Download oracle xe

http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

8. Download Apex

http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

9. Unpack files

unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip
unzip apex_18.1_en.zip

10. Oracle XE installation

cd Disk1
sudo rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm
sudo /etc/init.d/oracle-xe configure

11. Add an environment to the .bashrc

vim ~/.bashrc

. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

12. Add user to dba

sudo usermod -a -G dba ec2-user

13. Exit ssh

exit

14. Make a ssh connection

15. Remove old apex

cd /u01/app/oracle/product/11.2.0/xe/apex
sqlplus /nolog
CONNECT SYS as SYSDBA
@apxremov.sql
exit

16. Go to the /tmp/apex

cd /tmp/apex

17. Change MEMORY_TARGET

sqlplus /nolog
CONNECT SYS as SYSDBA
ALTER SYSTEM SET MEMORY_TARGET='1G' SCOPE=spfile;
SHUTDOWN
STARTUP

18. Install the apex

@apexins.sql SYSAUX SYSAUX TEMP /i/

19. Configuring the EPG

sqlplus /nolog
CONNECT SYS as SYSDBA
@apex_epg_config.sql /tmp

20. Init an apex admin account

@apxchpwd.sql

21. Normalize the default group

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

22. Normalize ACL https://docs.oracle.com/database/apex-18.1/HTMIG/enabling-network-services-in-Oracle-db11g-or-later.htm#HTMIG29162

DECLARE
  ACL_PATH  VARCHAR2(4000);
BEGIN
  -- Look for the ACL currently assigned to '*' and give APEX_180100
  -- the "connect" privilege if APEX_180100 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_180100',
     'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
     'APEX_180100', 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_180100', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

23. Exit from Oracle db

EXIT

24. Clean up the installation files

cd /tmp
rm -rf apex
rm -rf Disk1
rm oracle-xe-11.2.0-1.0.x86_64.rpm.zip
rm apex_18.1_en.zip

25. Install stunnel

sudo yum install stunnel

26. Generate stunnel certificate

cd /etc/stunnel
sudo openssl req -new -out csr.pem -keyout csr.pem -nodes -x509 -days 365

27. Configure localhost port 2525 for the SMTP and route traffic for https

sudo vim /etc/stunnel/stunnel.conf

fips = no
[smtp-tls-wrapper]
accept = 2525
client = yes
connect = email-smtp.us-east-1.amazonaws.com:465
delay = yes
cert = /etc/stunnel/csr.pem

28. Add the line to the end of the file to start stunnel after the boot

sudo vim /etc/rc.local

stunnel /etc/stunnel/stunnel.conf

29. Run stunnel

sudo stunnel /etc/stunnel/stunnel.conf

30. Install apache

sudo yum install httpd24 mod24_ssl

31. Get a certificate from the cloudflare

sudo vim /etc/pki/tls/certs/www.crt
sudo vim /etc/pki/tls/private/www.key

32. Add default vhost

sudo vim /etc/httpd/conf.d/vhosts.conf

<VirtualHost *:80>
ServerName ide.w--w--w.com
Redirect permanent / https://ide.w--w--w.com
</VirtualHost>
<VirtualHost *:443>
Redirect permanent / /apex
ServerName ide.w--w--w.com
SSLEngine on
SSLCertificateFile /etc/pki/tls/certs/www.crt
SSLCertificateKeyFile /etc/pki/tls/private/www.key

RewriteCond %{QUERY_STRING} (.*)(?:^|&)CKEditor=(.*)$
RewriteCond %1%2 (^|&)([^&].*|$)
RewriteRule ^(/apex/f)$ $1?%2 [R=302,L]
RewriteCond %{QUERY_STRING} (.*)(?:^|&)CKEditorFuncNum=(.*)$
RewriteCond %1,%2 (^|&)([^&].*|$)
RewriteRule ^(/apex/f)$ $1?%2 [R=302,L]
RewriteCond %{QUERY_STRING} (.*)(?:^|&)langCode=(.*)$
RewriteCond %1,%2 (^|&)([^&].*|$)
RewriteRule ^(/apex/f)$ $1?%2 [R=302,L]

</VirtualHost>
<VirtualHost *:80>
ServerName pills.komplemed.ru
Redirect permanent / https://pills.komplemed.ru
</VirtualHost>
<VirtualHost *:443>
ServerName pills.komplemed.ru
SSLEngine on
SSLCertificateFile /etc/pki/tls/certs/www.crt
SSLCertificateKeyFile /etc/pki/tls/private/www.key
RewriteCond %{QUERY_STRING} !^p=102 [NC]
RewriteRule ^/apex/f / [R=301,L]
RewriteRule ^/$ apex/f?p=102 [R=302,L]
</VirtualHost>
<VirtualHost *:80>
ServerName day.w--w--w.com
Redirect permanent / https://day.w--w--w.com
</VirtualHost>
<VirtualHost *:443>
ServerName day.w--w--w.com
SSLEngine on
SSLCertificateFile /etc/pki/tls/certs/www.crt
SSLCertificateKeyFile /etc/pki/tls/private/www.key
RewriteCond %{QUERY_STRING} !^p=100 [NC]
RewriteRule ^/apex/f / [R=301,L]
RewriteRule ^/$ apex/f?p=100 [R=302,L]
</VirtualHost>
<VirtualHost *:80 *:443>
ServerName default
ServerAlias *
Redirect permanent / /apex
</VirtualHost>
ProxyRequests Off
ProxyPreserveHost On
ProxyPass /apex http://127.0.0.1:8080/apex
ProxyPassReverse /apex http://127.0.0.1:8080/apex
ProxyPass /i http://127.0.0.1:8080/i
ProxyPassReverse /i http://127.0.0.1:8080/i
SetEnv force-proxy-request-1.0 1
SetEnv proxy-nokeepalive 1
KeepAlive Off

33. Obtain the boilerplate config

cd /etc/httpd/conf.d
sudo wget https://raw.githubusercontent.com/h5bp/server-configs-apache/master/dist/.htaccess
sudo mv .htaccess h5bp.conf
cd ~

34. Start an Apache

sudo service httpd start
sudo chkconfig httpd on

35. Exit from the ssh

exit

P.S Backup

cd ~
aws configure
sqlplus /nolog
CONNECT SYS as SYSDBA
CREATE DIRECTORY TMP AS '/tmp';
EXIT

vim backup.sh

#!/bin/bash
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
expdp \"sys/<password> as sysdba\" NOLOGFILE=YES DIRECTORY=tmp FULL=YES
gzip -c /tmp/expdat.dmp > ~/expdat.dmp.gz
sudo rm /tmp/expdat.dmp
aws s3 mv expdat.dmp.gz s3://backup.w--w--w.com/expdat.dmp.gz
expdp \"sys/<password> as sysdba\" NOLOGFILE=YES DIRECTORY=tmp SCHEMAS=medicine DUMPFILE=medicine.dmp
gzip -c /tmp/medicine.dmp > ~/medicine.dmp.gz
sudo rm /tmp/medicine.dmp
aws s3 mv medicine.dmp.gz s3://backup.w--w--w.com/medicine.dmp.gz
expdp \"sys/<password> as sysdba\" NOLOGFILE=YES DIRECTORY=tmp SCHEMAS=w2bw2bw DUMPFILE=w2bw2bw.dmp
gzip -c /tmp/w2bw2bw.dmp > ~/w2bw2bw.dmp.gz
sudo rm /tmp/w2bw2bw.dmp
aws s3 mv w2bw2bw.dmp.gz s3://backup.w--w--w.com/w2bw2bw.dmp.gz
expdp \"sys/<password> as sysdba\" NOLOGFILE=YES DIRECTORY=tmp SCHEMAS=diary DUMPFILE=diary.dmp
gzip -c /tmp/diary.dmp > ~/diary.dmp.gz
sudo rm /tmp/diary.dmp
aws s3 mv diary.dmp.gz s3://backup.w--w--w.com/diary.dmp.gz
chmod u+x backup.sh

crontab -e

0 0 * * * ~/backup.sh

P.P.S. Grant execute SYS.DBMS_CRYPTO

GRANT EXECUTE ON SYS.DBMS_CRYPTO TO MEDICINE;