Oracle Cheatsheet: February 2017

Sunday, February 19, 2017

Font APEX Icons

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

Monday, February 13, 2017

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.

Sunday, February 12, 2017

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.

Wednesday, February 8, 2017

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;