Oracle Cheatsheet: January 2017

Sunday, January 29, 2017

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.

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

Saturday, January 28, 2017

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

Friday, January 27, 2017

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 😃

Reissue the SHA1 certificate with SHA256
Update java to JDK7 with UnlimitedJCEPolicyJDK7
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

Thursday, January 19, 2017

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;

Wednesday, January 18, 2017

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