Burn VMware vSphere Hypervisor 6.5 Image to USB

1. Download the VMware image
2. Download UNetbootin
3. Burn the image to USB

Backup Oracle database Data Definition to SVN

These scripts will export all database data definitions to individual text file and then commit to SVN.
Schedule a daily task to run this get_ddl_master.bat is like taking a daily backup of the database data definition.
You can use TortoiseSVN to check out the change over the time.

get_ddl_master.bat
This script will generate all the ddl statement, call sql.exe to dump the definitions and commit them to SVN.

@echo off
cd /D c:\WebAdmin
REM echo SET HEADING OFF       > get_ddl_all.sql
REM echo SET ECHO OFF        >> get_ddl_all.sql
REM echo SET PAGES 999        >> get_ddl_all.sql
REM echo SET LONG 90000     >> get_ddl_all.sql
REM echo SET TRIMSPOOL ON     >> get_ddl_all.sql
REM echo SET PAGESIZE 50000  >> get_ddl_all.sql
REM echo SET VERIFY OFF      >> get_ddl_all.sql
REM echo SET FEEDBACK OFF    >> get_ddl_all.sql
echo SET TERMOUT OFF     >> c:\WebAdmin\get_ddl_all.sql
"C:\Program Files\sqldeveloper\sqldeveloper\bin\sql.exe" sys/xxxx@berry.xxxx.local:1521:paris as sysdba @get_ddl_master.sql
echo exit; >> c:\WebAdmin\get_ddl_all.sql
"C:\Program Files\sqldeveloper\sqldeveloper\bin\sql.exe" sys/xxxx@berry.xxxx.local:1521:paris as sysdba @get_ddl_all.sql
REM
cd /D c:\WebAdmin\paris
svn cleanup c:\WebAdmin\paris
svn add * --force
svn -m "Nightly Backup" commit --username=chiup --password=xxxx --non-interactive
svn update

get_ddl_master.sql
This sql will generate all ddl statements. You should change the filter conditions to suit your needs.

SET HEADING OFF
SET ECHO OFF
SET PAGES 999
SET LONG 90000
set LINES 300
SET TRIMSPOOL ON
SET PAGESIZE 50000
SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF
cd c:\webAdmin
spool get_ddl_all.sql append
SELECT 'spool c:\webadmin\paris\' || OWNER || '.' || OBJECT_TYPE || '.' || OBJECT_NAME ||'.sql;'||
       chr(13)||chr(10)||
       'ddl '||owner||'.'||object_name||';'
sql_cmd
FROM ALL_OBJECTS
WHERE OBJECT_TYPE IN ('TABLE','PROCEDURE','FUNCTION','TRIGGER','PACKAGE','VIEW') 
AND OWNER NOT IN ('SYS','SYSTEM','SYSMAN','OUTLN','PORTAL','XDB','LBACSYS','OLAPSYS','WIRELESS','CTXSYS','EXFSYS')
order by owner, object_type, object_name;
spool off
exit

Redirect HTTP to HTTPS on Tomcat

Add this to the end of
$CATALINA_HOME\conf\web.xml

<security-constraint>
<web-resource-collection>
<web-resource-name>Entire Application</web-resource-name>
<url-pattern>/*</url-pattern>
</web-resource-collection>
<user-data-constraint>
<transport-guarantee>CONFIDENTIAL</transport-guarantee>
</user-data-constraint>
</security-constraint>

Enable HTTP Strict Transport Security (HSTS) in Tomcat

Add these two sections to web.xml in the conf directory. This is one of the conditions to get to A+ in Qualyst SSL Server Test.

It allows web servers to declare that web browsers (or other complying user agents) should only interact with it using secure HTTPS connections, and never via the insecure HTTP protocol.

One of the nice features is that, if the client issue http://abcdef.com, it will be redirected to https://abcdef.com.

$CATALINA_HOME\conf\web.xml

      <filter>
        <filter-name>httpHeaderSecurity</filter-name>
        <filter-class>org.apache.catalina.filters.HttpHeaderSecurityFilter</filter-class>
        <async-supported>true</async-supported>
        <init-param>
            <param-name>antiClickJackingOption</param-name>
            <param-value>SAMEORIGIN</param-value>
        </init-param>
        <init-param>
            <param-name>hstsMaxAgeSeconds</param-name>
            <param-value>31536000</param-value>
        </init-param>
        <init-param>
            <param-name>hstsIncludeSubDomains</param-name>
            <param-value>true</param-value>
        </init-param>
    </filter>

  <!-- The mapping for the HTTP header security Filter -->
    <filter-mapping>
        <filter-name>httpHeaderSecurity</filter-name>
        <url-pattern>/*</url-pattern>
        <dispatcher>REQUEST</dispatcher>
    </filter-mapping>

Restore Oracle database from RMAN backup

In SQL*Plus, sqlplus / as sysdba

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 200G SID='*';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/disk1/flash_recovery_area' SID='*';

In RMAN, rman target /
DBID is the number from the source database

rman target /
SET DBID 2659406081;
startup nomount;
RESTORE SPFILE TO PFILE '/u01/app/oracle/initorcl.ora' FROM AUTOBACKUP;
SHUTDOWN ABORT;

STARTUP FORCE NOMOUNT PFILE='/u01/app/oracle/initorcl.ora';
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;
catalog recovery area;

In SQL*Plus, sqlplus / as sysdba

create spfile from pfile='/u01/app/oracle/initorcl.ora';
alter database rename file 'C:\oradb\oradata\orcl\redo01.log' to 'C:\oradb\oradata\orcl\xredo01.log';
alter database rename file 'C:\oradb\oradata\orcl\redo02.log' to 'C:\oradb\oradata\orcl\xredo02.log';
alter database rename file 'C:\oradb\oradata\orcl\redo03.log' to 'C:\oradb\oradata\orcl\xredo03.log';

Back to RMAN, rman target /
Perform a SET UNTIL to limit recovery to the end of the archived redo logs. Note that recovery stops with an error if no SET UNTIL is specified.

run {
set until time "to_date('2017-03-30 13:00:00', 'yyyy-mm-dd hh24:mi:ss')";
restore database;
recover database;
}
ALTER DATABASE OPEN RESETLOGS;

Oracle 10g XE customization

In SQL*Plus, sqlplus / as sysdba

alter system set processes=300 scope=spfile;
alter system set open_cursors= 300;
alter system set job_queue_processes=20;
alter system set SHARED_SERVERS = 5;
alter system set memory_max_target=1G scope=spfile;
alter system set memory_target=1G scope=spfile;
alter system set sga_max_size=600M scope=spfile;

alter system set DB_CREATE_FILE_DEST = '/u01/app/oracle/oradata';
EXEC DBMS_XDB.SETHTTPPORT(0);
EXEC DBMS_XDB.SETLISTENERLOCALACCESS(TRUE);

Check the parameters

select * from v$resource_limit;

Amazon EC2 customization

TimeZone

ln -sf /usr/share/zoneinfo/Canada/Eastern /etc/localtime

Edit /etc/sysconfig/clock

ZONE="Canada/Eastern"

iptables
Amazon Linux would not let me bind port < 1024, so we have to use iptables to rediect 80 to 8080 and 443 to 8443. First, create the rules and save them to this file,iptables.dump

# Generated by iptables-save v1.4.18 on Fri Mar 31 03:06:30 2017

*filter

:INPUT ACCEPT [97187:40139959]

:FORWARD ACCEPT [0:0]

:OUTPUT ACCEPT [121395:162261294]

-A INPUT -i eth0 -p tcp -m tcp --dport 80 -j ACCEPT

-A INPUT -i eth0 -p tcp -m tcp --dport 8080 -j ACCEPT

-A INPUT -i eth0 -p tcp -m tcp --dport 443 -j ACCEPT

-A INPUT -i eth0 -p tcp -m tcp --dport 8443 -j ACCEPT

COMMIT

# Completed on Fri Mar 31 03:06:30 2017

# Generated by iptables-save v1.4.18 on Fri Mar 31 03:06:30 2017

*nat

:PREROUTING ACCEPT [1072:59746]

:INPUT ACCEPT [1601:90978]

:OUTPUT ACCEPT [8974:551695]

:POSTROUTING ACCEPT [8974:551695]

-A PREROUTING -i eth0 -p tcp -m tcp --dport 80 -j REDIRECT --to-ports 8080

-A PREROUTING -i eth0 -p tcp -m tcp --dport 443 -j REDIRECT --to-ports 8443

COMMIT

# Completed on Fri Mar 31 03:06:30 2017

Next, load the rules into iptables

iptables-restore < iptables.dump 

Save the rules

service iptables save

In the default configuration, stopping or restarting the iptables service will discard the running configuration. This behavior can be changed by setting IPTABLES_SAVE_ON_STOP="yes" or IPTABLES_SAVE_ON_RESTART="yes" in /etc/sysconfig/iptables-config. If these values are set, the affected files are:
/etc/sysconfig/iptables

2-factor Authenication usiing Google Authenticator

Download the OATH-toolkit

$ http://download.savannah.gnu.org/releases/oath-toolkit/
$ tar -zxvf oath-toolkit-1.12.5.tar.gz
$ ./configure
$ make

Assume the the secret key is JBSWY3DPEHPK3PXP. Run the command on linux will give you the code. Google uses 30 seconds time step and 16 chars base32 secret key.

$ ./oathtool --totp -s 30s -b JBSWY3DPEHPK3PXP
431234

Finally, we need to generate the QR code for Google Authenticator

https://dan.hersam.com/tools/gen-qr-code.html

Fire up Google Authenticator or 1Password, scan the QR code on the screen and you are all set.

Amazon EC2 Oracle RMAN backup to S3

$cat oracle_backup.sh

#!/bin/bash

SCRIPT_HOME=/u01/app/oracle/scripts
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
LOCAL_FLASH_AREA=/u01/app/oracle/fast_recovery_area/XE

. $ORACLE_HOME/bin/oracle_env.sh

rm -f $SCRIPT_HOME/oracle_backup.log

$ORACLE_HOME/bin/rman target / cmdfile $SCRIPT_HOME/oracle_backup.rcv log $SCRIPT_HOME/oracle_backup.log
s3cmd --no-progress sync --delete-removed $LOCAL_FLASH_AREA s3://cftc-rman-backup/
s3cmd put $SCRIPT_HOME/oracle_backup.log s3://cftc-rman-backup

$ cat oracle_backup.rcv

list db_unique_name all;
show all;
RUN {
  crosscheck backup;
  crosscheck copy;
  delete noprompt expired archivelog all;
  delete noprompt expired backup;
  delete noprompt obsolete device type disk;
}
RUN {
  sql "alter system archive log current";
  RECOVER COPY OF DATABASE WITH TAG 'ORAXE_INC_UPD'
    UNTIL TIME 'SYSDATE - 7';
  BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'ORAXE_INC_UPD'
    DATABASE;
  sql "alter system switch logfile";
}
exit;

Amazon EC2 Oracle Snapshot Backup

The idea is first put oracle database in backup mode, start the ec2 snapshot process. At this point, we can put the database back to normal already.
I add 60 seconds wait to be on the safe side.

$ cat ebs_volume_snapshot.sh

#!/bin/bash
SCRIPT_HOME=/u01/app/oracle/scripts
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe

sqlplus -S / as sysdba << ABC1
alter database begin backup;
exit;
ABC1

$SCRIPT_HOME/ec2-automate-backup.sh -v "vol-11ba0c5d vol-3cba0c70" -k 3  -c $SCRIPT_HOME/cron-primer.sh

sleep 60

sqlplus -S / as sysdba << ABC2
alter database end backup;
exit;
ABC2

$ cat cron-primer.sh

#!/bin/bash -
# EC2_HOME required for EC2 API Tools
export EC2_HOME=/opt/aws/apitools/ec2
# JAVA_HOME required for EC2 API Tools
export JAVA_HOME=/usr/lib/jvm/jre
export PATH=/bin:/opt/aws/bin/
export AWS_ACCESS_KEY=XXXXXXXXXXXXX
export AWS_SECRET_KEY=YYYYYYYYYYYYYYYYYYYYYY

You can find the ec2-automate-backup script here.

JasperReportsIntegration on windows Tomcat 8 & 9 High CPU usage

After deployment of the war file, edit the following file and change Context attribute reloadable from true to false.
This fix the CPU spike every 12 seconds issue on windows box.

C:\tomcat9\webapps\JasperReportsIntegration\META-INF\context.xml

<Context path="/JasperReportsIntegration" debug="5" reloadable="false"
         crossContext="true">