Oracle 11g XE with Apex 5.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_5.1.2_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. Go to the /tmp/apex

cd /tmp/apex

16. Change MEMORY_TARGET

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

17. Upgrade the apex

@apexins.sql SYSAUX SYSAUX TEMP /i/

18. Configuring the EPG

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

19. Init an apex admin account

@apxchpwd.sql

20. Unlock a global access to the apex

EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

21. Drop the old apex scheme

drop user apex_040000 cascade;

22. Normalize the default group

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LOCK_TIME UNLIMITED;
ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME UNLIMITED;

23. Normalize ACL http://docs.oracle.com/database/apex-5.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_050100
  -- the "connect" privilege if APEX_050100 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_050100',
     'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
     'APEX_050100', 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_050100', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

24. Exit from Oracle db

EXIT

25. 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_5.1.2_en.zip

26. Route traffic from the 80 port to the 8080 port

sudo iptables -A PREROUTING -t nat -i eth0 -p tcp --dport 80 -j REDIRECT --to-port 8080
sudo service iptables save

27. Install stunnel

sudo yum install stunnel

28. Generate stunnel certificate

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

29. 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
[https]
accept = 443
connect = 8080
cert = /etc/stunnel/csr.pem
TIMEOUTclose = 0

30. 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

31. Run stunnel

sudo stunnel /etc/stunnel/stunnel.conf

32. Exit from the ssh

exit

P.S. List of the old apex schemes

select username from dba_users
where regexp_like(username,'(FLOWS|APEX)_\d{6}')
and username <> (select table_owner from all_synonyms
where synonym_name = 'WWV_FLOW'
and owner = 'PUBLIC');

P.P.S. Edit /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora to change the ip address.

P.P.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
chmod u+x backup.sh

crontab -e

0 0 * * * ~/backup.sh