Enable HTTP/2 for Tomcat 9

It is easier than I thought.

First, ensure you are using APR/native connector. You can download tomcat native from here. Put the tcnative-1.dll in the tomcat lib directory.

Second, change protocol to org.apache.coyote.http11.Http11AprProtocol and add the UpgradeProtocol tag.

$CATALINA_HOME\conf\server.xml

<Connector port="8443"
  protocol="org.apache.coyote.http11.Http11AprProtocol"
  maxThreads="150" SSLEnabled="true">
    <UpgradeProtocol overheadDataThreshold="0" compression="on" className="org.apache.coyote.http2.Http2Protocol" />
    <SSLHostConfig honorCipherOrder="false">
        <Certificate certificateKeyFile="conf/ca.key"
          certificateFile="conf/ca.crt"
          type="RSA" />
    </SSLHostConfig>
</Connector>

Restart tomcat and we are done.

Check if HTTP/2 is enabled
https://tools.keycdn.com/http2-test

Enable Oracle 12c Unified Auditing - Pure Mode

Why we want Pure Mode?
The first is the audit trails are no longer written to their traditional pre-12c audit locations. Auditing is consolidated into the Unified Audit views and stored using Oracle SecureFiles. Oracle Secured Files use a proprietary format which means that Unified Audit logs cannot be viewed using editors such vi and may preclude or affect the use of third party logging solutions such as Splunk or HP ArcSight.

Operations done by SYS are also recorded.

Unified Auditing comes standard with Oracle Enterprise Edition; no additional license is required. It is installed by default, but not fully enabled by default.

In Command window

rename %ORACLE_HOME%/bin/orauniaud12.dll.dbl file %ORACLE_HOME%/bin/orauniaud12.dll

In sqlplus as SYSDBA

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

In Command window as Administrator

sc stop OracleService<sid>
sc start OracleService<sid>

Now pure mode unified auditing is enabled. Let's check.
In sqlplus as SYSDBA

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 9 01:03:46 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditing options

SQL> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

VALUE
----------------------------------------------------------------
TRUE

Check out the audit output

SELECT * FROM unified_audit_trail
ORDER BY event_timestamp DESC;

For performance reason, you may want to use queued-write method
In sqlplus as SYSDBA

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_property(
audit_trail_type           => DBMS_AUDIT_MGMT.audit_trail_unified,
audit_trail_property       => DBMS_AUDIT_MGMT.audit_trail_write_mode, 
audit_trail_property_value => DBMS_AUDIT_MGMT.audit_trail_queued_write
);
END;
/

Check configuration

SELECT * FROM dba_audit_mgmt_config_params
order by audit_trail, parameter_name;

Check what is being auditing out of the box

SELECT * FROM audit_unified_policies
ORDER BY policy_name,
         audit_option;

SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES;

Setup how many days of audit records we want to keep

BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type     => DBMS_AUDIT_MGMT.audit_trail_unified,
last_archive_time    => SYSTIMESTAMP-90
);
END;
/

Check archive setting

SELECT audit_trail,
last_archive_ts
FROM   dba_audit_mgmt_last_arch_ts;

We have to run this for the very first time

BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.audit_trail_all,
DEFAULT_CLEANUP_INTERVAL => 24 /*hours*/
);
END;
/

Let's purge manually

SELECT COUNT(*) FROM unified_audit_trail;
BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type        => DBMS_AUDIT_MGMT.audit_trail_unified,
use_last_arch_timestamp => TRUE);
END;
/
SELECT COUNT(*) FROM unified_audit_trail;

Let's automate this purging process by setting up scheduled job

DBMS_SCHEDULER.create_job (
job_name        => 'audit_last_archive_time',
job_type        => 'PLSQL_BLOCK',
job_action      => 'DECLARE
l_days NUMBER := 90;
BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.audit_trail_unified, TRUNC(SYSTIMESTAMP)-l_days);
  DBMS_AUDIT_MGMT.clean_audit_trail(
    audit_trail_type        => DBMS_AUDIT_MGMT.audit_trail_unified,
    use_last_arch_timestamp => TRUE);
END;',
start_date      => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=1; byminute=0; bysecond=0;',
end_date        => NULL,
enabled         => TRUE,
comments        => 'Automatically set audit last archive time.');
END;
/

SSL Reverse Proxy using nginx without using Oracle Wallet

In my previous post, I mentioned that we can use stunnel to get around using https in oracle utl_http call. Today I ran into this 404 not found problem and there is no solution.

I am forced to switch to nginx. Setup was extremely easy. Most likely I am going to use nginx in the future.

With this setup, I can issue http://localhost:8103 and nginx will load balance between https://web1.remote.com:8443 and https://web2.remote.com:8443

Bonus is I don’t need to worry about oracle wallet anymore. It is a nightmare to maintain, especially internal hostname with https.

nginx.conf

worker_processes  1;
pid        logs/nginx.pid;

events {
    worker_connections  1024;
}

http {
    include       mime.types;
    default_type  application/octet-stream;
    sendfile      on;
    keepalive_timeout 65;

upstream tomcathosts {
      server web1.remote.com:8443;
      server web2.remote.com:8443;
  }

server {
    listen 8103;
    server_name  localhost;
    location / {
        root /;
        proxy_connect_timeout       600;
        proxy_send_timeout          600;
        proxy_read_timeout          600;
        send_timeout                600;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-Host $host:$server_port;
        proxy_set_header X-Forwarded-Server $host;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_pass https://tomcathosts;
    }
  }
}

After everything is working, I use nssm to make nginx a window service.

P.S. If you don't have two upstream servers for load balancing, remove the upstream section and put the upstream server hostname directly in proxy_pass

Enable/Disable Archive Log Mode

Verify database log mode

sqlplus / as sysdba
archive log list

Non-RAC database

Enable archive log

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

Disable archive log

shutdown immediate;
startup mount;
alter database noarchivelog;
alter database open;

RAC database

Enable archive log

srvctl stop database -d orcl
srvctl start database -d orcl -o mount
 
sqlplus / as sysdba
alter database archivelog;
EXIT;

srvctl stop database -d orcl
srvctl start database -d orcl 

Disable archive log

srvctl stop database -d orcl
srvctl start database -d orcl -o mount
 
sqlplus / as sysdba
alter database noarchivelog;
EXIT;

srvctl stop database -d orcl
srvctl start database -d orcl 

Dump windows scheduled tasks

From time to time, peoples asked me what is scheduled on the server.

Human readable format

schtasks /query /v /fo LIST

CSV

schtasks /query /v /fo CSV > tasks.csv

Increase the Size of an AWS EBS Cloud Volume Attached to a Linux Machine

1. Stop the instance
2. Make a snapshot of the volume that we want to increase
3. Write down the attachment information
   

   Attachment information: i-f0cacda3 (PROD):/dev/sdf1 (attached)
   
4. Detach the volume
5. Create a new volume with larger size, pick the same zone, pick the snapshot that we create in Step #2
6. Attach the new volume to the instance with the same attachment information (see Step #3)
7. Start the instance
8. ssh into the instance as usual
9. df -h should still show the old size even we are using a larger volume
10. Run resize2fs /dev/sdf1 to increase the size to match the larger volume
11. Reboot (optional)

Update ESXi Embedded Host Client

1. ssh into the ESXi host as root

2. Goto ESXi Embedded Host Client Site and copy the vib link.

3. install the vib

The time and date of this login have been sent to the system logs.

VMware offers supported, powerful system administration tools.  Please
see www.vmware.com/go/sysadmintools for details.

The ESXi Shell can be disabled by an administrative user. See the
vSphere Security documentation for more information.
[root@shuttle:~] esxcli software vib install -v http://download3.vmware.com/software/vmw-tools/esxui/esxui-signed-6360286.vib
Installation Result
Message: Operation finished successfully.
Reboot Required: false
VIBs Installed: VMware_bootbank_esx-ui_1.23.0-6360286
VIBs Removed: VMware_bootbank_esx-ui_1.8.0-4516221
VIBs Skipped: 
[root@shuttle:~]

iReport Builder and SVN keywords

In iReport Builder, there is no comment field to store SVN keywords.

As a workaround, I add a new "No Data" band, set the"print when expression" to "new Boolean (false)" and then I put a static field in the band.

REM SVN Revision Information: DO NOT REMOVE
REM $Revision: 2593 $
REM $Author: pchiu $
REM $Date: 2017-08-28 19:39:41 -0400 (Mon, 28 Aug 2017) $

This works as long as I don't need to display anything when the report is empty.

zfs replace faulty disk

The disk already crashed. We have already pull it out and replace it with a good one. Now, we just need to fix zpool setup.

First we put the disk offline.

# zpool offline opt2 14610995796361758680
# zpool status -v opt2
  pool: opt2
 state: DEGRADED
status: One or more devices has been taken offline by the administrator.
        Sufficient replicas exist for the pool to continue functioning in a
        degraded state.
action: Online the device using 'zpool online' or replace the device with
        'zpool replace'.
  scan: scrub repaired 0 in 21h9m with 0 errors on Tue Jul 25 00:42:02 2017
config:

        NAME                      STATE     READ WRITE CKSUM
        opt2                      DEGRADED     0     0     0
          raidz1-0                DEGRADED     0     0     0
            da3                   ONLINE       0     0     0
            da4                   ONLINE       0     0     0
            14610995796361758680  OFFLINE      0     0     0  was /dev/da5

And then we replace it with the new device

# zpool replace opt2 da5 /dev/da5
# zpool status -v opt2
  pool: opt2
 state: DEGRADED
status: One or more devices is currently being resilvered.  The pool will
        continue to function, possibly in a degraded state.
action: Wait for the resilver to complete.
  scan: resilver in progress since Sun Aug 27 11:21:53 2017
        28.5G scanned out of 4.77T at 96.7M/s, 14h18m to go
        9.50G resilvered, 0.58% done
config:

        NAME                        STATE     READ WRITE CKSUM
        opt2                        DEGRADED     0     0     0
          raidz1-0                  DEGRADED     0     0     0
            da3                     ONLINE       0     0     0
            da4                     ONLINE       0     0     0
            replacing-2             OFFLINE      0     0     0
              14610995796361758680  OFFLINE      0     0     0  was /dev/da5/old
              da5                   ONLINE       0     0     0  (resilvering)

errors: No known data errors

How to get A+ on SSL Labs running Tomcat

In my previous posting, we brought our tomcat server to Grade A on SSL Labs.
However, to archive A+, it requires more.

You need to enable HSTS. You can following this post to enable HSTS.

One problem I encountered with SSL Labs is the SNI. If we run the test under the default domain, HSTS test works. But if we run it under other domains, HSTS test will fail. So you end up with Grade A instead of A+.

So make sure you test it using your default domain.

ORDS Install/Upgrade (ORDS Versions 3.0 to 21.4)

Install

If your ords directory is e:\ords, use e:\ below. Do not include \ords

Stop Tomat

copy ords.war apex.war
rmdir $CATALINA_HOME/webapps/apex
copy apex.war $CATALINA_HOME/webapps/
java -jar apex.war configdir e:\
java -jar apex.war

Validate ORDS installation

java -jar apex.war validate

Run this if validation fails

java -jar apex.war schema

Configure Multiple Databases
Do not use workspace-id when adding APEX

java -jar apex.war setup --database dev
java -jar apex.war map-url --type base-path /dev dev

Start Tomcat

Upgrade

Stop Tomat

copy ords.war apex.war
rmdir $CATALINA_HOME/webapps/apex
copy apex.war $CATALINA_HOME/webapps/
java -jar apex.war configdir e:\

java -jar apex.war schema

Start Tomcat

SSL Reverse Proxy using stunnel without using Oracle Wallet

EDIT: Using nginx is a better solution.

Making https call from oracle is a nightmare. As a minimum, you need to import the site certificate into Oracle wallet.

However, if there is any problem with the certificate, it is next to impossible to debug.
Oracle XE doesn't come with Oracle wallet, utl_http may not support SNI, all depends on the oracle database version, etc...

As a workaround, use stunnel to listen on localhost and let it talk to the remote site via https.

In this example, it is listening on 8103 protocol http and talk to remote site via 8443 protocol https.
doing http://127.0.0.1:8103 just like https://web1.remote.com:8443

stunnel.conf

[web1]
client = yes
accept = 127.0.0.1:8103
connect = web1.remote.com:8443
CAfile = ca-certs.pem

Oracle APEX dynamic style base on domain name

Add a new process with the following PL/SQL code to the login page Before Header Section.

Pre-Rendering->Before Header->Processes

DECLARE
  v_http_host varchar2(1000);
BEGIN 
  v_http_host := lower(owa_util.get_cgi_env('HTTP_HOST'));

  if v_http_host = 'domain1.com' or v_http_host like '%.domain1.com' then
    apex_theme.set_session_style(p_theme_number=> 242, p_name => 'my_style1');
  elsif v_http_host = 'domain2.com' or v_http_host like '%.domain2.com' then
    apex_theme.set_session_style(p_theme_number=> 242, p_name => 'my_style2');
  else 
    apex_theme.set_session_style(p_theme_number=> 242, p_name => 'my_style1');
  end if;
END;

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">

Tomcat 9 JDK8 server.xml for HTTPS with SNI Support

This configure file is case sensitive.
SSLCertificateKeyFile is the private key.

<Connector port="443" protocol="org.apache.coyote.http11.Http11NioProtocol" 
  SSLEnabled="true" scheme="https" secure="true"
  maxHttpHeaderSize="32767"
  maxThreads="150" 
  URIEncoding="UTF-8"
  compression="on"
  defaultSSLHostConfigName="cmeportal.oneconceptonline.ca">
<SSLHostConfig hostName="cmeportal.oneconceptonline.ca"
  honorCipherOrder="true"
  disableSessionTickets="true"
  ciphers="TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384,
TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256,
TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384,
TLS_ECDH_ECDSA_WITH_AES_256_GCM_SHA384,
TLS_DHE_DSS_WITH_AES_256_GCM_SHA384,
TLS_ECDH_RSA_WITH_AES_256_GCM_SHA384,
TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256,
TLS_ECDH_ECDSA_WITH_AES_128_GCM_SHA256,
TLS_ECDH_RSA_WITH_AES_128_GCM_SHA256,
TLS_DHE_DSS_WITH_AES_128_GCM_SHA256,
TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384,
TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA384,
TLS_ECDH_RSA_WITH_AES_256_CBC_SHA384,
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384,
TLS_DHE_DSS_WITH_AES_256_CBC_SHA256,
TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA,
TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA,
TLS_ECDH_RSA_WITH_AES_256_CBC_SHA,
TLS_DHE_DSS_WITH_AES_256_CBC_SHA,
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256,
TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256,
TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256,
TLS_DHE_DSS_WITH_AES_128_CBC_SHA256,
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA,
TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA,
TLS_ECDH_RSA_WITH_AES_128_CBC_SHA,
TLS_DHE_DSS_WITH_AES_128_CBC_SHA,
TLS_ECDHE_ECDSA_WITH_RC4_128_SHA,
TLS_ECDH_ECDSA_WITH_RC4_128_SHA,
TLS_ECDH_RSA_WITH_RC4_128_SHA,
TLS_EMPTY_RENEGOTIATION_INFO_SCSVF"
>
<Certificate 
     CertificateFile="${catalina.home}\conf\ssl\cmeportal.oneconceptonline.ca.crt"
     CertificateKeyFile="${catalina.home}\conf\ssl\cmeportal.oneconceptonline.ca.pem"
     CertificateChainFile="${catalina.home}\conf\ssl\gd_bundle-g2-g1.crt"
     type="RSA" />
</SSLHostConfig>
<SSLHostConfig hostName="*.conceptdynamics.zone"
  honorCipherOrder="true"
  disableSessionTickets="true"
  ciphers="TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384,
TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256,
TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384,
TLS_ECDH_ECDSA_WITH_AES_256_GCM_SHA384,
TLS_DHE_DSS_WITH_AES_256_GCM_SHA384,
TLS_ECDH_RSA_WITH_AES_256_GCM_SHA384,
TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256,
TLS_ECDH_ECDSA_WITH_AES_128_GCM_SHA256,
TLS_ECDH_RSA_WITH_AES_128_GCM_SHA256,
TLS_DHE_DSS_WITH_AES_128_GCM_SHA256,
TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384,
TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA384,
TLS_ECDH_RSA_WITH_AES_256_CBC_SHA384,
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384,
TLS_DHE_DSS_WITH_AES_256_CBC_SHA256,
TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA,
TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA,
TLS_ECDH_RSA_WITH_AES_256_CBC_SHA,
TLS_DHE_DSS_WITH_AES_256_CBC_SHA,
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256,
TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256,
TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256,
TLS_DHE_DSS_WITH_AES_128_CBC_SHA256,
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA,
TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA,
TLS_ECDH_RSA_WITH_AES_128_CBC_SHA,
TLS_DHE_DSS_WITH_AES_128_CBC_SHA,
TLS_ECDHE_ECDSA_WITH_RC4_128_SHA,
TLS_ECDH_ECDSA_WITH_RC4_128_SHA,
TLS_ECDH_RSA_WITH_RC4_128_SHA,
TLS_EMPTY_RENEGOTIATION_INFO_SCSVF"
>
<Certificate 
     CertificateFile="${catalina.home}\conf\ssl\conceptdynamics.zone.crt"
     CertificateKeyFile="${catalina.home}\conf\ssl\conceptdynamics.zone.pem"
     CertificateChainFile="${catalina.home}\conf\ssl\gd_bundle-g2-g1.crt"
     type="RSA" />
</SSLHostConfig>
</Connector>

SSL Certificate with OpenSSL

Generate CSR

openssl req -new -newkey rsa:2048 -nodes -keyout jes.jmetrics.com.pem -out jes.jmetrics.com.csr

Install the Certificate

SSLCertificateFile jes.jmetrics.com.crt
SSLCertificateKeyFile jes.jmetrics.com.pem
SSLCertificateChainFile rapidssl.crt

View Public Key

openssl x509 -text -in jes.jmetrics.com.crt

View Private Key

openssl rsa -text -in jes.jmetrics.com.pem

Show SHA1 Fingerprint

openssl x509 -noout -fingerprint -sha1 -inform pem -in jes.jmetrics.com.crt

Export Private Key from Java KeyStore

keytool -importkeystore -srckeystore jes.jmetrics.com.keystore -srcstoretype jks -destkeystore jes.jmetrics.com.p12 -deststoretype PKCS12 -srcalias jes.jmetrics.com
openssl pkcs12 -in jes.jmetrics.com.p12 -nocerts -nodes

Now, we can copy and paste the private key on the screen into the PEM file.

SSL Certificate with Java Keystore

Generate CSR

keytool -genkeypair -alias cftc-csid.ca -keyalg RSA -keysize 2048 -validity 365 -keypass xxxx -keystore keystore.jks -storepass xxxx
keytool -certreq -alias cftc-csid.ca -file cftc-csid.ca.csr -keystore keystore.jks -storepass xxxx

Import the Certificate from CA

keytool -import -trustcacerts -alias root -file cftc-csid_ca.ca-bundle  -keystore keystore.jks -storepass xxxx
keytool -import -trustcacerts -alias cftc-csid.ca -file cftc-csid_ca.crt  -keystore keystore.jks -storepass xxxx

List all keys

keytool -v -list -keystore keystore.jks -storepass xxxx

Delete a key

keytool -delete -keystore keystore.jks -storepass xxxx

Font APEX Icons

https://apex.oracle.com/pls/apex/f?p=42:4000

Code Signing for Oracle 10g Forms Server

Code Signing Certificate

1. Build the keystore for Signing
The keystore is used for signing jar files. It can be installed on any computer, it is not necessary to put it on the Forms server.

1.1 Generate CSR

"C:\Program Files\Java\jdk1.8.0_152\bin\keytool" -genkey -alias codesigncert -keyalg RSA -keysize 2048 -keystore E:\codesigning\codesignstore -storepass QbnaJN3sfj3MNU -dname "CN=xxx,O=xxx,L=Toronto,ST=Ontario,C=CA"

1.2 Export CSR

"C:\Program Files\Java\jdk1.8.0_152\bin\keytool" -certreq -v -alias codesigncert -file E:\codesigning\\xxx_codesigning.csr -keystore E:\codesigning\codesignstore -storepass QbnaJN3sfj3MNU

1.3 Install the certificate from godaddy

"C:\Program Files\Java\jdk1.8.0_152\bin\keytool" -importcert -file F:\Oracle\Middleware\fr_inst\config\c1d2569cebbe31da-SHA2.pem -keystore F:\Oracle\Middleware\fr_inst\config\codesignstore -keystorepassword Welcome1 -alias codesigncert 

At this point the code signing certificate is properly installed on our keystore and it is ready to sign our own jar files.

2. Signing jar files
In F:\Oracle\Middleware\FR_home\forms\java directory, that is where all the jar files reside. Most of them are provided by oracle and we DO NOT NEED to sign them. They are already signed by Oracle.

We have two custom jar files, my_icons.jar and jacob.jar. These are the only two files that need to be signed.

2.1 Clean up the jar file
Open the jar file using 7-zip. Remove these files in the META-INF sub-directory.

META-INF/*.SF META-INF/*.DSA META-INF/*.RSA META-INF/*.MF

2.2 Add permission to jar files
Create a text file add.txt like this, make sure you have a CR at the end of the file.

Codebase: * 
Permissions: all-permissions
Application-Name: OracleForms

And we run the following to add the permission to the jar files.

"C:\Program Files\Java\jdk1.8.0_152\bin\jar" ufm F:\Oracle\Middleware\FR_home\forms\java\my_icons.jar F:\Oracle\Middleware\FR_home\forms\java\add.txt
"C:\Program Files\Java\jdk1.8.0_152\bin\jar" ufm F:\Oracle\Middleware\FR_home\forms\java\jacob.jar F:\Oracle\Middleware\FR_home\forms\java\add.txt

2.3 Sign the jar files

"C:\Program Files\Java\jdk1.8.0_152\bin\jarsigner" -verbose -keystore F:\Oracle\Middleware\fr_inst\config\codesignstore -storepass Welcome1 -tsa http://tsa.starfieldtech.com/ F:\Oracle\Middleware\FR_home\forms\java\jacob.jar codesigncert
"C:\Program Files\Java\jdk1.8.0_152\bin\jarsigner" -verbose -keystore F:\Oracle\Middleware\fr_inst\config\codesignstore -storepass Welcome1 -tsa http://tsa.starfieldtech.com/ F:\Oracle\Middleware\FR_home\forms\java\my_icons.jar codesigncert

2.4 Verify the jar files (optional)

"C:\Program Files\Java\jdk1.8.0_152\bin\jarsigner" -verify -verbose -certs F:\Oracle\Middleware\FR_home\forms\java\jacob.jar
"C:\Program Files\Java\jdk1.8.0_152\bin\keytool" -printcert -jarfile F:\Oracle\Middleware\FR_home\forms\java\jacob.jar

At this stage, the jar files are properly signed. We can use these jar files on any 10g Forms server. It is not tied to the domain or URL.

The signed jar files are good for life.

Upgrade Oracle Linux

Check version

$ cat /etc/oracle-release
Oracle Linux Server release 6.2 
$ lsb_release -d
Description:    Oracle Linux Server release 6.2

Perform upgrade

# cd /etc/yum.repos.d/
# mv public-yum-ol6.repo public-yum-ol6.repo.old
# wget http://public-yum.oracle.com/public-yum-ol6.repo
# yum upgrade

French characters and CLOB

I stored the import file in BLOB and then convert the BLOB to CLOB and process it.

However, today file include a french letter and throw off everything.

I am using something like this to convert the BLOB to CLOB.

dbms_lob.converttoclob(dest_lob     => l_clob
,src_blob     => p_blob
,amount       => dbms_lob.lobmaxsize
,dest_offset  => l_dest_offsset
,src_offset   => l_src_offsset
,blob_csid    => dbms_lob.default_csid
,lang_context => l_lang_context
,warning      => l_warning);

The problem here is the blob_csid is wrong for the file.To determine the correct blob_csid, you can first save the file to the file system and run

C:\>lcsscan file=dent_47268_cexp.txt

Language and Character Set File Scanner v2.0
(c) Copyright 2003, 2004 Oracle Corporation.  All rights reserved.

dent_47268_cexp.txt:    FRENCH  WE8ISO8859P1;

Once I determined the correct language, I change my function to this and everything is fine again.

dbms_lob.converttoclob(dest_lob     => l_clob
,src_blob     => p_blob
,amount       => dbms_lob.lobmaxsize
,dest_offset  => l_dest_offsset
,src_offset   => l_src_offsset
,blob_csid    => NLS_CHARSET_ID ('WE8ISO8859P1'),
,lang_context => l_lang_context
,warning      => l_warning);

Amazon EC2 snapshot/rman/datapump 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-11ba0cå5d vol-3cba0c70" -k 3  -c $SCRIPT_HOME/cron-primer.sh

sleep 60

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

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

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

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_export.sh

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

. $ORACLE_HOME/bin/oracle_env.sh

cd $SAN_FLASH_AREA

rm -f $SAN_FLASH_AREA/cftc.zip
rm -f $SAN_FLASH_AREA/oraxe*.dmp $SAN_FLASH_AREA/cftc*.dmp
rm -f $SAN_FLASH_AREA/oracle_export.log
rm -f /tmp/cftc.zip.*

$ORACLE_HOME/bin/expdp userid=system/xxxxxxx parfile=$SCRIPT_HOME/oracle_export.par

$SCRIPT_HOME/apex_export.sh

7za a -pxxxxxx -bd -y -tzip -mem=AES256 -w/tmp $SAN_FLASH_AREA/cftc.zip $SAN_FLASH_AREA/cftc*.dmp $SAN_FLASH_AREA/oracle_export.log /u01/JasperReportsIntegration/reports/ $SAN_FLASH_AREA/f*.sql $SAN_FLASH_AREA/w*.sql

s3cmd put /u01/app/oracle/dpdump/cftc.zip s3://cftc-datapump-backup

rm -f $SAN_FLASH_AREA/oraxe*.dmp $SAN_FLASH_AREA/cftc*.dmp
rm -f /tmp/cftc.zip.*

Transfer openbsd from one disk to another

Print out the current partition setup and size. This setup is very important.

pf:~$ df -h |sort
Filesystem     Size    Used   Avail Capacity  Mounted on
/dev/sd0a     1008M   55.2M    903M     6%    /
/dev/sd0d      7.9G    4.0K    7.5G     0%    /tmp
/dev/sd0e     78.7G   20.6M   74.8G     0%    /var
/dev/sd0f      3.9G    1.0G    2.7G    27%    /usr
/dev/sd0g     1001M    2.0K    951M     0%    /usr/X11R6
/dev/sd0h      9.8G   63.9M    9.3G     1%    /usr/local
/dev/sd0i      2.0G    2.0K    1.9G     0%    /usr/src
/dev/sd0j      2.0G    2.0K    1.9G     0%    /usr/obj
/dev/sd0k      1.7T   30.4M    1.6T     0%    /home

Assume the current drive is sd0 and the new drive is sd1.

# fdisk -i sd1
Do you wish to write new MBR and partition table? [n] y
Writing MBR at offset 0.

Repeat adding all partitions (a, d, e, f, g, h, i, j, k)

pf:~# disklabel -E sd1
Label editor (enter '?' for help at any prompt)

> a a
offset: [226468288] 
size: [3680555777] 
FS type: [4.2BSD] 
Rounding offset to bsize (128 sectors): 226468352
Rounding size to bsize (128 sectors): 3680555648
(...)

> w
> q

Repeat formatting all partitions

pf:~# newfs sd1k      
/dev/rsd1k: 1797146.2MB in 3680555648 sectors of 512 bytes
551 cylinder groups of 3266.88MB, 52270 blocks, 104704 inodes each

# shutdown -h now
Shutdown NOW!

Now boot your system in single-user mode and clone the data from the current drive to the new drive

boot> boot -s
(...)
Enter pathname of shell or RETURN for sh:
# mount_mfs -s 4096m swap /tmp

Repeat the following for each partition

# mount /dev/sd1a /mnt
# cd /mnt
# dump -0f - /dev/sd0a  | restore -rf -
# cd /
# umount /mnt

If dump failed because of drive problem, try using tar instead

# mount /dev/sd1a /mnt
# tar cpXf - / | tar xpf - -C /mnt

Test boot the new drive

# reboot
boot> boot hd1a:/bsd -s
(...)
Enter pathname of shell or RETURN for sh: 
#

Boot your system in single-user mode using the original drive and install boot loader on the new drive

boot> boot -s
(...)
Enter pathname of shell or RETURN for sh:
# mount /dev/sd0f /usr
# mount /dev/sd1a /mnt
# installboot -r /mnt sd1 /usr/mdec/biosboot /usr/mdec/boot

Fix duid in /etc/fstab

pf:root# disklabel sd1
# /dev/rsd1c:
type: SCSI
disk: SCSI disk
label: ST2000VN000-1H31
duid: 85d0b835a275e8ee
flags:
bytes/sector: 512
sectors/track: 63
tracks/cylinder: 255
sectors/cylinder: 16065
cylinders: 243201
total sectors: 3907029168
boundstart: 64
boundend: 3907024065
drivedata: 0 

16 partitions:
#                size           offset  fstype [fsize bsize  cpg]
a:          2104448               64  4.2BSD   2048 16384    1 # /
c:       3907029168                0  unused                   
d:         16771840          2104512  4.2BSD   2048 16384    1 # /tmp
e:        167766784         27262304  4.2BSD   2048 16384    1 # /var
f:          8385952         18876352  4.2BSD   2048 16384    1 # /usr
g:          2088448        195029088  4.2BSD   2048 16384    1 # /usr/X11R6
h:         20964832        197117536  4.2BSD   2048 16384    1 # /usr/local
i:          4192960        218082368  4.2BSD   2048 16384    1 # /usr/src
j:          4192960        222275328  4.2BSD   2048 16384    1 # /usr/obj
k:       3680555648        226468352  4.2BSD   8192 65536    1 # /home

Edit /mnt/etc/fstab, replace the original duid with the one we found above

pf:root# cat /etc/fstab 
85d0b835a275e8ee.b none swap sw
85d0b835a275e8ee.a / ffs rw 1 1
85d0b835a275e8ee.k /home ffs rw,nodev,nosuid 1 2
85d0b835a275e8ee.d /tmp ffs rw,nodev,nosuid 1 2
85d0b835a275e8ee.f /usr ffs rw,nodev 1 2
85d0b835a275e8ee.g /usr/X11R6 ffs rw,nodev 1 2
85d0b835a275e8ee.h /usr/local ffs rw,nodev 1 2
85d0b835a275e8ee.j /usr/obj ffs rw,nodev,nosuid 1 2
85d0b835a275e8ee.i /usr/src ffs rw,nodev,nosuid 1 2
85d0b835a275e8ee.e /var ffs rw,nodev,nosuid 1 2

All done. Power off the system, remove the original drive and boot again from the new drive.

Viewing APEX Mail Log and Queue

Send email via APEX using email template

DECLARE
    l_id NUMBER;
BEGIN   
    APEX_UTIL.set_workspace (p_workspace => 'YOUR WORKSPACE NAME HERE');

    l_id :=
        APEX_MAIL.SEND (p_to                   => 'pchiu@xxxxxxx.com',
                        p_from                 => 'no-reply@xxxxxxx.com',
                        p_application_id       => 110,
                        p_template_static_id   => 'BUYBACK_LOA_YEAREND',
                        p_placeholders         => '{' || '}');

    APEX_MAIL.PUSH_QUEUE;
    COMMIT;
END;
/

First you need to change security group to your workspace name before running the query.

BEGIN   
    APEX_UTIL.set_workspace (p_workspace => 'YOUR WORKSPACE NAME HERE');
END;
/

SELECT * from APEX_MAIL_LOG ORDER BY LAST_UPDATED_ON DESC;

SELECT * from APEX_MAIL_QUEUE ORDER BY LAST_UPDATED_ON DESC;

JasperReportsIntegration fonts issue

Out of the box, ireports on the server can only render a few fonts, e.g. Deja Vu, SansSerif. Anything beyond that will give you a nasty "JRFontNotFoundException" error at runtime.

To fix this, you need to install the fonts on the server side.

1. Add all the fonts required to iReports, Tools->Options->Fonts->Install Font
2. Check Embedded font in PDF
3. On the same screen, highlight all the newly added fonts, hit "Export as extension", give it a name like "apex-fonts.jar".
4. Copy this apex-fonts.jar file to this server location
   /usr/local/tomcat/webapps/JasperReportsIntegration/WEB-INF/lib
5. Restart Tomcat

Add Google Font to CKEditor

Add two new fonts (Reenie Beanie, Open Sans) to C:\tomcat\webapps\i\libraries\ckeditor\3.6.4\config.js

CKEDITOR.editorConfig = function( config )
{
 // Define changes to default configuration here. For example:
 // config.language = 'fr';
 // config.uiColor = '#AADC6E';
 config.contentsCss = 'https://fonts.googleapis.com/css?family=Reenie+Beanie|Open+Sans';
    //the next line add the new font to the combobox in CKEditor
    config.font_names = 'Reenie Beanie/Reenie Beanie;Open Sans/Open Sans;' + config.font_names;
};

Add this to the html header section

<head>
<link href="https://fonts.googleapis.com/css?family=Reenie+Beanie|Open+Sans" rel="stylesheet" type="text/css">
</head>

Add this to the css style in your content

style="font-family: 'Reenie Beanie';"

Handling XML

This is how you import the XML from file system into oracle table as a xmltype field.

INSERT INTO pc_xmltable (my_id, my_xml)
VALUES (2, xmltype (BFILENAME ('ECHELON_DIR', 'xml.txt'), NLS_CHARSET_ID ('AL32UTF8')));

These are some examples as to how you can extract records from the XML

SELECT xt.*
FROM pc_xmltable pc,
XMLTABLE ('/PaymentOutput/DocumentInfo' PASSING pc.my_xml COLUMNS filecreationdate VARCHAR2 (20) PATH 'FileCreationDate') xt
WHERE pc.my_id = 2;

SELECT xt.*
FROM pc_xmltable pc,
XMLTABLE (
'/PaymentOutput/EOBDetails/SBClaimPredLines'
PASSING pc.my_xml
COLUMNS claimlinesequencenumber NUMBER (10) PATH 'ClaimLineSequenceNumber',
submittedclaimline VARCHAR2 (20) PATH 'SubmittedClaimLine',
certificatenumber VARCHAR2 (20) PATH 'CertificateNumber',
dateofservice DATE PATH 'DateOfService',
totalsubmittedamount NUMBER (14, 4) PATH 'TotalSubmittedAmount',
totalamountpaid NUMBER (14, 4) PATH 'TotalAmountPaid') xt
WHERE pc.my_id = 2;

OpenBSD equal-cost multipath routing

Enable multipath in /etc/sysctl.conf

net.inet.ip.multipath=1         # 1=Enable IP multipath routing
net.inet6.ip6.multipath=1       # 1=Enable IPv6 multipath routing

Check gateway

# netstat -rnf inet | grep default 

Add routing if it is missing

# /sbin/route delete default 108.162.159.65   ;/sbin/route add -mpath default 108.162.159.65  

PPPOE Control

# pppctl /var/tmp/pppoe      

Tracking FreeBSD security branch using svnup

cat /usr/local/etc/svnup.conf

# $FreeBSD$
#
# Default configuration options for svnup.conf
#


[defaults]
work_directory=/tmp/svnup
#host=svn.freebsd.org
#host=svn0.us-west.freebsd.org
host=svn0.us-east.freebsd.org
protocol=https
verbosity=1
trim_tree=0
extra_files=0
target=/usr/src

[release]
branch=base/release/11.0.0

[security]
branch=base/releng/11.0

[stable]
branch=base/stable/11

[current]
branch=base/head

[ports]
branch=ports/head
target=/usr/ports

Update Source

# svnup security

Update Port

# svnup ports

Installation

# cd /usr/src
# make buildworld
# make buildkernel KERNCONF=AMD64
# make installkernel KERNCONF=AMD64
# reboot

After Reboot

# cd /usr/src
# make installworld
# mergemaster
# reboot

Oracle 10g Forms and Reports Server Installation that work for Java 7 and 8

1. as_windows_x86_forms_reports_101202.zip
Use COMPATIBILITY MODE XP SP2 and run as Administrator

2. Patch #5983622
Use COMPATIBILITY MODE XP SP2 and run as Administrator

3. Patch #6640838
Use COMPATIBILITY MODE XP SP2 and run as Administrator
Use custom install, expand all nodes and ensure every node is checked.

4. Patch #6880880
unzip and overwrite the opatch directory in Oracle AS

5. Patch #9593176
Run this in command window as Administrator. If opatch fails, just copy the file manually to oracle AS.

set OPATCH_PLATFORM_ID=215
set ORACLE_HOME=E:\Oracle\MiddleTier
set JAVA_HOME=C:\Program Files\Java\jdk1.7.0_76\
E:\Oracle\MiddleTier\OPatch\opatch apply -jdk C:\ProgrA~1\Java\JDK17~1.0_7\bin\

6. Patch #11710576
Run this in command window as Administrator. If opatch fails, just copy the file manually to oracle AS.

set OPATCH_PLATFORM_ID=215
set ORACLE_HOME=E:\Oracle\MiddleTier
set JAVA_HOME=C:\Program Files\Java\jdk1.7.0_76\
E:\Oracle\MiddleTier\OPatch\opatch apply -jdk C:\ProgrA~1\Java\JDK17~1.0_7\bin\

7. Patch #13685884
Run this in command window as Administrator. If opatch fails, just copy the file manually to oracle AS.

set OPATCH_PLATFORM_ID=215
set ORACLE_HOME=E:\Oracle\MiddleTier
set JAVA_HOME=C:\Program Files\Java\jdk1.7.0_76\
E:\Oracle\MiddleTier\OPatch\opatch apply -jdk C:\ProgrA~1\Java\JDK17~1.0_7\bin\

8. Patch #13095466 (Bundle Patch on top of 10.1.2.3.1)
Run this in command window as Administrator. If opatch fails, just copy the file manually to oracle AS.

set OPATCH_PLATFORM_ID=215
set ORACLE_HOME=E:\Oracle\MiddleTier
set JAVA_HOME=C:\Program Files\Java\jdk1.7.0_76\
E:\Oracle\MiddleTier\OPatch\opatch apply -jdk C:\ProgrA~1\Java\JDK17~1.0_7\bin\

webutil
Follow all the steps in this metalink document (Doc ID 566628.1)


formsweb.cfg

JRE 7 & 8

jpi_classid=clsid:CAFEEFAC-0017-0000-FFFF-ABCDEFFEDCBA
jpi_mimetype=application/x-java-applet;version=1.7
jpi_codebase=http://java.sun.com/update/1.7.0/jinstall-7u17-windows-i586.cab#Version=1,70,17
jpi_download_page=http://www.oracle.com/technetwork/java/javase/downloads

JRE 6

jpi_classid=clsid:CAFEEFAC-0016-0000-FFFF-ABCDEFFEDCBA
jpi_mimetype=application/x-java-applet;version=1.6
jpi_codebase=http://java.sun.com/update/1.6.0/jinstall-6-windows-i586.cab
jpi_download_page=http://www.oracle.com/technetwork/java/javase/downloads

Clone Production database to Demo

1. Stop DEMO database

set ORACLE_SID=demo
set ORACLE_HOME=C:\app\oracle\product\11.2.0\dbhome_1
C:\app\oracle\product\11.2.0\dbhome_1\BIN\sqlplus / as sysdba
shutdown abort;

2. Purge DEMO database

del /F /Q /S C:\app\oracle\fast_recovery_area\demo\
del /F /Q /S C:\app\oracle\admin\demo\
del /F /Q /S F:\oradata\demo\
del /F /Q /S E:\fast_recovery_area\demo\
del /F /Q /S F:\fast_recovery_area\demo\

mkdir C:\app\oracle\fast_recovery_area\demo
mkdir C:\app\oracle\admin
mkdir C:\app\oracle\admin\demo
mkdir C:\app\oracle\admin\demo\adump
mkdir C:\app\oracle\admin\demo\bdump
mkdir C:\app\oracle\admin\demo\dpdump
mkdir C:\app\oracle\admin\demo\pfile
mkdir F:\oradata\demo
mkdir F:\oradata\demo\datafile
mkdir E:\fast_recovery_area\demo
mkdir E:\fast_recovery_area\demo\archivelog
mkdir F:\fast_recovery_area\demo
mkdir F:\fast_recovery_area\demo\archivelog

3. Create Password file - SYS password needs to be the same on both databases

cd /D C:\app\oracle\product\11.2.0\dbhome_1\database
ORAPWD FILE=PWDdemo.ora ENTRIES=10

4. Listener.ora
Add a static entry for demo

5. Fix E:\periodic\oracle_rman_clone.rcv
Run this against Production database to get a list of latest datafiles

SELECT 'SET NEWNAME FOR DATAFILE ' || file# || ' TO ''' || REPLACE (name, 'E:\ORADATA\PROD', 'F:\ORADATA\DEMO') || ''';' sqlline
  FROM v$datafile
 ORDER BY file#;

Refresh the datafile list in oracle_rman_clone.rcv, and we need to fix the archivelog sequence # base on Production RMAN output

#
# SVN Revision Information:
# $Revision: 2021 $
# $Author: pchiu $
# $Date: 2016-12-10 00:45:01 -0500 (Sat, 10 Dec 2016) $
#
#
#  SELECT 'SET NEWNAME FOR DATAFILE ' || file# || ' TO ''' || REPLACE (name, 'E:\ORADATA\PROD', 'F:\ORADATA\DEMO') || ''';' sqlline
#    FROM v$datafile
#ORDER BY file#;
#
run {  
SET NEWNAME FOR DATAFILE 1 TO 'F:\ORADATA\DEMO\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO 'F:\ORADATA\DEMO\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 3 TO 'F:\ORADATA\DEMO\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'F:\ORADATA\DEMO\USERS01.DBF';
SET NEWNAME FOR DATAFILE 5 TO 'F:\ORADATA\DEMO\EXAMPLE01.DBF';
SET NEWNAME FOR DATAFILE 6 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_BHWQS94L_.DBF';
SET NEWNAME FOR DATAFILE 7 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_HCS_STAG_BHWQVY73_.DBF';
SET NEWNAME FOR DATAFILE 8 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_HCS_STAG_BHWQX19T_.DBF';
SET NEWNAME FOR DATAFILE 9 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_DATA_BHWQXTD2_.DBF';
SET NEWNAME FOR DATAFILE 10 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_APEX_DAT_BHWR0HHJ_.DBF';
SET NEWNAME FOR DATAFILE 11 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_INDE_BHWR18JT_.DBF';
SET NEWNAME FOR DATAFILE 12 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_BT_DATA_BHWR2CLM_.DBF';
SET NEWNAME FOR DATAFILE 13 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_HIST_BHWR2GNF_.DBF';
SET NEWNAME FOR DATAFILE 14 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_BHWR37PG_.DBF';
SET NEWNAME FOR DATAFILE 15 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_LECXE_DA_BHWR5WSR_.DBF';
SET NEWNAME FOR DATAFILE 16 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_LECXE_LO_BHWR6CVX_.DBF';
SET NEWNAME FOR DATAFILE 17 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_BHWR7GY4_.DBF';
SET NEWNAME FOR DATAFILE 18 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_BHWR9J1Q_.DBF';
SET NEWNAME FOR DATAFILE 19 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_BQOXCZNQ_.DBF';
SET NEWNAME FOR DATAFILE 20 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_C2OH4RCN_.DBF';
SET NEWNAME FOR DATAFILE 21 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_CB028886_.DBF';
SET NEWNAME FOR DATAFILE 22 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_CF0WL1ML_.DBF';
SET NEWNAME FOR DATAFILE 23 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_CNCN5RBS_.DBF';
SET NEWNAME FOR DATAFILE 24 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_CQ5ZWVP8_.DBF';
SET NEWNAME FOR DATAFILE 25 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_INDE_CT7MCBWW_.DBF';
SET NEWNAME FOR DATAFILE 26 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_CT7MN0X5_.DBF';
SET NEWNAME FOR DATAFILE 27 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_CXRZ4VD6_.DBF';
SET NEWNAME FOR DATAFILE 28 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_D14533D2_.DBF';
SET NEWNAME FOR DATAFILE 29 TO 'F:\ORADATA\DEMO\DATAFILE\O1_MF_ACC_LOB_D4KCSQ85_.DBF';
SET NEWNAME FOR TEMPFILE 1 TO 'F:\oradata\demo\temp01.dbf';
DUPLICATE TARGET DATABASE TO 'DEMO'
FROM ACTIVE DATABASE
SPFILE 
PARAMETER_VALUE_CONVERT 'PROD','DEMO'
SET DB_CREATE_FILE_DEST 'F:\ORADATA\DEMO'
SET dispatchers '(PROTOCOL=TCP) (SERVICE=demoXDB)'
SET control_files 'F:\ORADATA\DEMO\CONTROL01.CTL'
SET LOG_ARCHIVE_DEST_1 'location=E:\fast_recovery_area\demo\archivelog'
SET LOG_ARCHIVE_DEST_10 'location=USE_DB_RECOVERY_FILE_DEST'
NOFILENAMECHECK
OPEN RESTRICTED
SKIP READONLY;
}

6. Clone database
Startup database

set ORACLE_SID=demo
set ORACLE_HOME=C:\app\oracle\product\11.2.0\dbhome_1
C:\app\oracle\product\11.2.0\dbhome_1\BIN\sqlplus / as sysdba
STARTUP NOMOUNT PFILE='C:\app\oracle\product\11.2.0\dbhome_1\database\initdemo.ora';

C:\app\oracle\product\11.2.0\dbhome_1\database\initdemo.ora

DB_NAME=demo

Start cloning

C:\app\oracle\product\11.2.0\dbhome_1\bin\rman target sys/xxxxx@prod auxiliary sys/xxxxx@demo CMDFILE E:\periodic\oracle_rman_clone.rcv LOG F:\oracle_backup\dmp\oracle_rman_clone_test.log

7. Post cloning

C:\app\oracle\product\11.2.0\dbhome_1\BIN\sqlplus / as sysdba
ALTER SYSTEM SET DB_CREATE_FILE_DEST='F:\ORADATA'

C:\app\oracle\product\11.2.0\dbhome_1\BIN\sqlplus accdb/xxxxx@benecaid_demo
BEGIN
  migration_pkg.migration_demo;
END;
/
@E:\periodic\demo_patch01.sql
-- @E:\periodic\demo_patch02.sql

8. Refresh all Oracle Forms, Oracle Reports and Jasper Reports from Production

Tomcat 7 JDK7 server.xml for HTTPS

This configure setup is case sensitive.
SSLCertificateKeyFile is the private key.

<Connector protocol="org.apache.coyote.http11.Http11AprProtocol"
     port="8443" maxThreads="200"
     maxHttpHeaderSize="32767" 
     URIEncoding="UTF-8"
     useServerCipherSuitesOrder="true"
     compression="on"
     enableLookups="false" disableUploadTimeout="true"
     scheme="https" secure="true" SSLEnabled="true"
     SSLCertificateFile="${catalina.home}\conf\ssl\server.crt"
     SSLCertificateKeyFile="${catalina.home}\conf\ssl\server.pem"
     SSLCertificateChainFile="${catalina.home}\conf\ssl\rapidssl.crt"
     clientAuth="false" sslProtocol="TLS"
ciphers="TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384, 
TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256, 
TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384, 
TLS_ECDH_ECDSA_WITH_AES_256_GCM_SHA384, 
TLS_ECDH_RSA_WITH_AES_256_GCM_SHA384, 
TLS_DHE_DSS_WITH_AES_256_GCM_SHA384, 
TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256, 
TLS_ECDH_ECDSA_WITH_AES_128_GCM_SHA256, 
TLS_ECDH_RSA_WITH_AES_128_GCM_SHA256, 
TLS_DHE_DSS_WITH_AES_128_GCM_SHA256, 
TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384, 
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384, 
TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA384, 
TLS_ECDH_RSA_WITH_AES_256_CBC_SHA384, 
TLS_DHE_DSS_WITH_AES_256_CBC_SHA256, 
TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA, 
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA, 
TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA, 
TLS_ECDH_RSA_WITH_AES_256_CBC_SHA, 
TLS_DHE_DSS_WITH_AES_256_CBC_SHA, 
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256, 
TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256, 
TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256, 
TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256, 
TLS_DHE_DSS_WITH_AES_128_CBC_SHA256, 
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA, 
TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA, 
TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA, 
TLS_ECDH_RSA_WITH_AES_128_CBC_SHA, 
TLS_DHE_DSS_WITH_AES_128_CBC_SHA, 
TLS_ECDHE_ECDSA_WITH_RC4_128_SHA, 
TLS_ECDH_ECDSA_WITH_RC4_128_SHA, 
TLS_ECDH_RSA_WITH_RC4_128_SHA, 
TLS_ECDHE_ECDSA_WITH_3DES_EDE_CBC_SHA, 
TLS_ECDHE_RSA_WITH_3DES_EDE_CBC_SHA, 
TLS_ECDH_ECDSA_WITH_3DES_EDE_CBC_SHA, 
TLS_ECDH_RSA_WITH_3DES_EDE_CBC_SHA, 
TLS_RSA_WITH_AES_128_CBC_SHA,
TLS_RSA_WITH_AES_256_GCM_SHA384, 
TLS_RSA_WITH_AES_128_GCM_SHA256,
TLS_EMPTY_RENEGOTIATION_INFO_SCSVF"
/>

APEX application passed Ministry of Health security check

One of the APEX applications I am working on just passed the government security audit with flying color. It is a portal where the cardholders can check out their medical claims history, payment etc. The database has the cardholder personal information, address, date of birth and claims history.

Needless to say, it must passed government security check before they let you open it up to the public. The last thing they want is on the 630 headline news like yahoo 😅

Here is want I have done.

1. Salted Password

In the worst case scenario, whoever has the login data still need year over year to brute-force the password.

2. APEX-SERT

Our application has 200+ pages, the first time I ran APEX-SERT, OMG, I was SHOCKED. There are over 2000 actionable items to fix. It took me over a week to clean up all the mess.

3. SQL Injection

All records more than one rows are displayed via Interactive report or classic report. They can add filters via the build-in UI, I don't take parameters directly. By design, I am pretty much immune from this problem.

4. SSL Server Test

First test our score was C, not good. We fixed the followings and end up with an A 😃

1. Reissue the SHA1 certificate with SHA256
2. Update java to JDK7 with UnlimitedJCEPolicyJDK7
3. Update tomcat to v7 and customize the server.xml

There are probably a few more minor items but I can't recall now.

Just want to say big thank you to

Oracle APEX team

APEX-SERT team

Defuse security who wrote the salted password article

QUALYS SSL Labs for their free SSL test site

Script to create schema and grant proper security from schema owner

Assume the schema is ACCDB and the new schema is ACCDBP1.

This script will create the new schema, setup synonym and grant security to it.

CREATE USER accdbp1 IDENTIFIED BY passwordxyz
 DEFAULT TABLESPACE ACC_DATA
 TEMPORARY TABLESPACE TEMP
 QUOTA UNLIMITED ON ACC_DATA;

 GRANT "CONNECT" TO accdbp1;

ALTER USER accdbp1 DEFAULT ROLE "CONNECT";

 SELECT 'DROP SYNONYM accdbp1.'
 || object_name
 || ';'
 || CHR (13)
 || CHR (10)
 || 'Create synonym accdbp1.'
 || object_name
 || ' for accdb.'
 || object_name
 || ';'
 || CHR (13)
 || CHR (10)
 || CASE
 WHEN object_type IN ('TABLE', 'VIEW')
 THEN
 'grant select,update,insert,delete on accdb.' || object_name || ' to accdbp1;'
 WHEN object_type IN ('SEQUENCE')
 THEN
 'grant select on accdb.' || object_name || ' to accdbp1;'
 WHEN object_type IN ('PROCEDURE', 'PACKAGE', 'FUNCTION')
 THEN
 'grant execute on accdb.' || object_name || ' to accdbp1;'
 END
 syn_sql
 FROM all_objects
 WHERE object_type IN ('TABLE',
 'VIEW',
 'SEQUENCE',
 'PROCEDURE',
 'PACKAGE',
 'FUNCTION')
 AND object_name NOT LIKE 'SEB%'
 AND object_name NOT LIKE '%\_H' ESCAPE '\'
 AND object_name NOT LIKE '%\_AUDIT' ESCAPE '\'
 AND ( (object_type = 'TABLE' AND data_object_id IS NOT NULL) OR object_type <> 'TABLE')
 AND owner = 'ACCDB'
ORDER BY object_type, object_name;

SQLcl language setting in Windows environment

I have a PL/SQL procedure doing data dump to a file running it through sqlplus. It was working fine until I switch sqlplus to sqlcl and all numeric fields ended up like 123,45 instead of 123.45.

After googling, I found that it is a java issue and there is a setting for it.

I add the following line to sql.bat and that solved the problem.

set JAVA_TOOL_OPTIONS=-Duser.language=en -Duser.region=US -Dfile.encoding=UTF-8