Kevin Kempf's Blog

December 31, 2009

SSL & Servlet

Filed under: 11i, Techstack — kkempf @ 1:45 pm


I just realized that something which I had put in draft in September was still sitting in draft today… the information is still valid…

Pursuing the goal of moving my 11i Ebusiness Suite techstack forward, last weekend I cut over the application server to use SSL and Forms Servlet.  This has been a long time coming, but really it just ended up being a few edits to the context file, landing the certificates on the server and running autoconfig.  This information is based upon 123718.1 and it’s worth noting that forms socket won’t support Native Java with SSL (290807.1).


  • First, land the certificates into $COMN_TOP/admin/certs/apache (cp -R)
  • Context file edits
  • set the %s_url_protocol variable to https
  • set the %s_local_url_protocol variable to https
  • set the %s_webentryurlprotocol variable to https
  • set the %s_frmConnectMode variable to https
  • set the %s_webssl_port variable to the Apache SSL port required
  • set the %s_active_webport variable to the same value as that for the %s_webssl_port variable
  • set the %s_webport variable to the same value as that for the %s_webssl_port variable
    Note: prior to  TXK (FND) AutoConfig Template Rollup Patch F (3104607   December 2003) this value was set to the non-ssl Apache Port.

  1. set the %s_web_ssl_directory variable to point to the full directory path of the directory that is to contain the .crt and .key files that you are using for Apache eg <$COMMON_TOP>/admin/certs/apache
  2. set the %s_web_ssl_keyfile variable to point to the server.key file that you are using for Apache eg <$COMMON_TOP>/admin/certs/apache/ssl.key/server.key
  3. set the %s_web_ssl_certfile variable to point to the server.crt file that you are using for Apache eg <$COMMON_TOP>/admin/certs/apache/ssl.crt/server.crt
  4. set the %s_web_ssl_certfile variable to point to the ca-bundle.crt file that you are using for Apache eg <$COMMON_TOP>/admin/certs/apache/ssl.crt/ca-bundle.crt
  5. set %s_apps_portal_url variable to https
  6. set s_forms_servlet_serverurl to /forms/formservlet
  7. set s_forms_servlet_comment to <null>
  8. s_f60map, s_chronosURL, s_external_url to https from http

run AutoConfig

Forms Servlet

Context file changes:

  • <forms_connect oa_var=”s_frmConnectMode”>https</forms_connect>           or http
  • <server_url oa_var=”s_forms_servlet_serverurl”>/forms/formservlet</server_url>
  • <servlet_comment oa_var=”s_forms_servlet_comment”/>          or <servlet_comment oa_var=”s_forms_servlet_comment”></servlet_comment>

Run autoconfig


Filed under: 11g, PL/SQL — kkempf @ 11:51 am

Intermec 3400e

At a time which normally includes trips to the break room for Christmas cookies, half the staff being out on any given day and light traffic on the commute, I found myself exceptionally busy this week.  We’re in the midst of a regression cycle, and I’d revamped a mission critical PL/SQL package (4700 lines, to be exact!) which generates product and shipping labels (onto Intermec 3400e printers) for us in the manufacturing process.

Why did I have to rewrite it, and why now?  It turns out our labeling solution, Optio (commercial software which writes to label printers) is all but dead from a support standpoint.  In shopping new vendors, we were unimpressed and figured we could use an existing product we owned (Bartender).  In looking at the existing PL/SQL, I believe every rule of good programming was violated.  It’s like a case study in how to write unmaintainable code.  Seriously, not to harp on consultants writing terrible code, but were you even aware that PL/SQL supported goto syntax?  So effectively, we’re required to regression test this code as if it were new.

As it turns out, I had some logic bugs in the rewritten code (not a big surprise) which required a little attention; after fixing the issue, when called from a trigger, the code would always return ORA-6508 (PL/SQL: could not find program unit being called) and ORA-4068 (existing state of packages discarded).  I fought this issue for quite some time, before a real programmer suggested I might try:


Wow it worked like a charm.  I attribute this to my use of global variables, of which there are numerous documented notes and references (Tom Kyte even calls them evil!)  After I dumped them things worked, more or less, as expected, and I was able to resume holiday pace…

Incidentally, in the course of running down this error, using the exception block of PL/SQL, I found this nifty bit of code which is now my new standard for error handling (in this case, writing to an error table):

insert into error_log(error_date
 ,dbms_utility.format_error_stack || ' ' ||

What I’m really raving about here is the dbms_utility call.  It made it crystal clear what my error was, and if you’re not familiar with it I suggest you try it:

dbms_utility.format_error_stack || ' ' || dbms_utility.format_call_stack

December 16, 2009

Whats in your dbs directory?

Filed under: Uncategorized — kkempf @ 2:55 pm

I ran into an issue regarding SMR files in an 11g home, and I thought it might be interesting to take a moment and catalog what might be in a $ORACLE_HOME/dbs directory.  I believe this is almost the proverbial “junk drawer” folder of the software install, though it certainly contains some absolutely critical files.  They tend to be rather disparate, however, so I thought I would spell them out here.


The human-readable and human-editable, clear text version of the parameter (or initialization) file for the database.  Well this one is obvious, it’s been living here ever since I started working with Oracle databases 9 years ago.

initSID.ora ifiles

In my case, I like ifiles for things which are instance specific.  So if I have 3 “like” instances, they can all use virtually the same init file, and have custom ifiles to accommodate them.  In the case of an ERP, the initfile is so standardized by running the database autoconfig, I find this is useful for things which are local customizations unrelated to the ERP, such as dataguard parameters.


The binary version of the parameter (or initialization) file for the database, introduced in 9i which allows far more dynamic parameter changes to Oracle.

hc_SID.dat files

Healthcheck files, which seem to exist only to confound your efforts in enterprise manager.

Password files

For sys priveliges and remote password logins

lkSID files

Instance lock file.  It’s created at the first DB startup, opened when the DB is started, and closed when the DB is shut down.

snapcf_SID.f files

Snapshot controlfile

dr1SID.dat files

Dataguard broker configuration file

Upgrade logs, random sql scripts, backup copies of init files

This list, I’m sure, will be wide and varied, though none of this really belongs in the DBS directory…

Upgrading RDBMS 11.2 to Timezone Version 11

Filed under: 11g, Oracle — kkempf @ 10:15 am

After completing a slew of 10.2 to 11.2 RDBMS upgrades recently, I went back and did as the pre-upgrade analyzer (utlu1112.sql) suggested and updated the Timezone Version from 10 to 11 (per doc 944122.1).  The main take away from this is that Oracle has changed and to some degree simplified this process (at least for Version 11).  It still requires taking the database down several times, which is more than annoying.  I figured it may be easiest to just show my runtime log here, with commands I executed in bold.  I performed all of these upgrades on RHEL5.4 x86_64.

** EDIT ** there is a new doc now, 977512.1, which adds a few steps to prepare the upgrade, though the substance is still the same I recommend you review it thoroughly before proceeding!

As a (perhaps not-so) interesting side-note, is still only available for download on OTN on Sun & Linux platforms.

$ sqlplus / as sysdba
SQL*Plus: Release Production on Tue Dec 15 11:06:31 2009
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release - 64bit Production
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area  167014400 bytes
Fixed Size                  2211528 bytes
Variable Size             113246520 bytes
Database Buffers           46137344 bytes
Redo Buffers                5419008 bytes
Database mounted.
Database opened.
SQL> set serveroutput on
SQL> exec dbms_dst.begin_upgrade(11);
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  167014400 bytes
Fixed Size                  2211528 bytes
Variable Size             113246520 bytes
Database Buffers           46137344 bytes
Redo Buffers                5419008 bytes
Database mounted.
Database opened.

SQL> select property_name, substr(property_value, 1, 30) value from database_properties where property_name like 'DST_%' order by property_name;

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
SQL> select owner, table_name, upgrade_in_progress from all_tstz_tables;
OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYS                            AQ$_AQ_PROP_TABLE_S            NO
SYS                            TSM_DST$                       NO
SYS                            SCHEDULER_FILEWATCHER_QT       NO
SYS                            SCHEDULER$_STEP_STATE          NO
SYS                            SCHEDULER$_JOB_DESTINATIONS    NO
SYS                            SCHEDULER$_EVENT_LOG           NO
SYS                            KET$_CLIENT_TASKS              NO
SYS                            SCHEDULER$_JOB                 NO
SYS                            SCHEDULER$_FILEWATCHER_HISTORY NO
SYS                            WRI$_ALERT_OUTSTANDING         NO
SYS                            AQ$_SUBSCRIBER_TABLE           NO
OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYS                            AQ$_SCHEDULER_FILEWATCHER_QT_L NO
SYS                            AQ$_KUPC$DATAPUMP_QUETAB_L     NO
SYS                            ALERT_QT                       NO
SYS                            AQ$_AQ$_MEM_MC_L               NO
SYS                            SCHEDULER$_WINDOW              NO
SYS                            SCHEDULER$_EVENT_QTAB          NO
SYS                            WRI$_OPTSTAT_HISTHEAD_HISTORY  NO
SYS                            AQ$_SCHEDULER$_REMDB_JOBQTAB_S NO
SYS                            FGR$_FILE_GROUP_VERSIONS       NO
SYS                            OPTSTAT_USER_PREFS$            NO
SYS                            KET$_CLIENT_CONFIG             NO
OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYS                            AQ$_AQ$_MEM_MC_S               NO
SYS                            SCHEDULER$_WINDOW_DETAILS      NO
SYS                            SCHEDULER$_REMDB_JOBQTAB       NO
SYS                            SCHEDULER$_LIGHTWEIGHT_JOB     NO
SYS                            SCHEDULER$_GLOBAL_ATTRIBUTE    NO
SYS                            SCHEDULER$_FILE_WATCHER        NO
SYS                            WRI$_OPTSTAT_IND_HISTORY       NO
SYS                            AQ$_SCHEDULER_FILEWATCHER_QT_S NO
SYS                            SCHEDULER$_JOB_RUN_DETAILS     NO
SYS                            WRI$_OPTSTAT_OPR               NO
SYS                            AQ$_SYS$SERVICE_METRICS_TAB_L  NO
OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYS                            FGR$_FILE_GROUP_FILES          NO
SYS                            TSM_SRC$                       NO
SYS                            SCHEDULER$_REMOTE_JOB_STATE    NO
SYS                            WRI$_OPTSTAT_HISTGRM_HISTORY   NO
SYS                            WRI$_OPTSTAT_AUX_HISTORY       NO
SYS                            REG$                           NO
SYS                            AQ$_SYS$SERVICE_METRICS_TAB_S  NO
SYS                            FGR$_FILE_GROUPS               NO
SYS                            AQ$_ALERT_QT_S                 NO
SYS                            WRR$_REPLAY_DIVERGENCE         NO
SYS                            SCHEDULER$_SCHEDULE            NO
OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYS                            WRI$_OPTSTAT_TAB_HISTORY       NO
SYS                            WRI$_ALERT_HISTORY             NO
SYS                            AQ$_SCHEDULER$_REMDB_JOBQTAB_L NO
SYS                            AQ$_SCHEDULER$_EVENT_QTAB_S    NO
SYS                            AQ$_KUPC$DATAPUMP_QUETAB_S     NO
SYS                            OPTSTAT_HIST_CONTROL$          NO
SYS                            KET$_AUTOTASK_STATUS           NO
SYS                            AQ$_AQ_PROP_TABLE_L            NO
SYS                            AQ$_ALERT_QT_L                 NO
SYS                            SCHEDULER$_FILEWATCHER_RESEND  NO
SYS                            AQ$_SCHEDULER$_EVENT_QTAB_L    NO
OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
DBSNMP                         MGMT_DB_FEATURE_LOG            NO
WMSYS                          WM$VERSIONED_TABLES            NO
WMSYS                          AQ$_WM$EVENT_QUEUE_TABLE_S     NO
WMSYS                          AQ$_WM$EVENT_QUEUE_TABLE_L     NO
SYSMAN                         MGMT_PROV_NET_CONFIG           YES
SYSMAN                         MGMT_PROV_IP_RANGE             YES
SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_L       YES
SYSMAN                         AQ$_MGMT_LOADER_QTABLE_S       YES
SYSMAN                         AQ$_MGMT_LOADER_QTABLE_L       YES
SYSMAN                         MGMT_PROV_BOOTSERVER           YES
OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_S       YES
SYSMAN                         MGMT_PROV_STAGING_DIRS         YES
SYSMAN                         MGMT_PROV_OPERATION            YES
SYSMAN                         MGMT_PROV_ASSIGNMENT           YES
SYSMAN                         MGMT_CONFIG_ACTIVITIES         YES
SYSMAN                         MGMT_PROV_CLUSTER_NODES        YES
SYSMAN                         MGMT_PROV_RPM_REP              YES
SYSMAN                         MGMT_PROV_DEFAULT_IMAGE        YES
74 rows selected.
SQL> set serveroutput on;
SQL> declare
num_of_failures number;

Number of failures: 0
Number of failures: 0
Number of failures: 0
Number of failures: 0
Number of failures: 0
Number of failures: 0
Number of failures: 0
Number of failures: 0
Number of failures: 0
Number of failures: 0
Number of failures: 0
Number of failures: 0
Number of failures: 0
Number of failures: 0
Number of failures: 0
An upgrade window has been successfully ended.
PL/SQL procedure successfully completed.
SQL> select * from all_tstz_tables where upgrade_in_progress = 'YES';
no rows selected
SQL> select property_name, substr(property_value, 1, 30) value from database_properties where property_name like 'DST_%'
order by property_name;
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------

December 15, 2009

More to upgrades

Filed under: 10g, 11g, Oracle — kkempf @ 9:38 am

With the end of Premier support for RDBMS 10.2 coming in July of next year (Officially from Oracle here), I started doing some more 10g to 11g migrations.  6 upgrades later, I am happy to say that it’s been successful across the board.  In the interest of brevity, I’m going to skip all the detailed checks outlined in 837570.1 and cut to the chase:  Make sure you have a backup before you start the upgrade.   The rest of this assumes a vanilla database, meaning you don’t have any of the issues outlined in 836570.1.  Like you don’t have duplicate items in SYS and SYSTEM schemas, you are smart enough to already have a SYSAUX tablespace, you understand what the new connect role is, you don’t have TIMESTAMP WITH TIMEZONE Datatypes in the database, etc, etc, etc.

  1. Install 11g in a new home
  2. Run the 11g_home/rdbms/admin/utlu112i.sql script against your 10gR2 database and see what it says.   In my case:
    1. Archivelog formats now require %r in them
    2. user/background/core dump destination parameters are depricated (I simply removed them and went with the new defaults)
    3. minimum sga_target of 700M & minimum java_pool_size of 128M (I allowed this for the upgrade, then cut it back)
    4. Empty the recycle bin: purge dba_recyclebin
    5. exec dbms_stats.gather_schema_stats vs. SYS and SYSMAN schemas
  3. Run 10g_home/rdbms/admin/utlrp to recompile invalids
  4. Shutdown the 10g instance
  5. Source the new Oracle Home so $ORACLE_HOME points to the 11.2 home
  6. Copy the new/modified initSID.ora file to the 11g_home/dbs directory; copy the listener.ora and tnsnames.ora file from the 10g home to the 11g_home/network/admin directory
  7. startup upgrade pfile=initSID.ora (11g version)
  8. Run 11g_home/rdbms/admin/catupgrd.sql  (catalog upgrade, this script takes quite a while to finish, about 1.5 hours on a wimpy 2 CPU VM)
  9. Run 11g_home/rdbms/admin/utlu112s.sql (the doc incorrectly identifies this as utlu111s.sql)
  10. Run 11g_home/rdbms/admin/utlrp.sql (recompile)
  11. Check your compatible parameter (in my case I simply changed it from to and restart the database if required
  12. Start your listener
Oracle Database 11.2 Post-Upgrade Status Tool           12-15-2009 09:34:18
Component                                Status         Version  HH:MM:SS
Oracle Server
.                                         VALID  00:31:18
JServer JAVA Virtual Machine
.                                         VALID  00:11:33
Oracle Workspace Manager
.                                         VALID  00:01:00
OLAP Analytic Workspace
.                                    OPTION OFF  00:00:00
OLAP Catalog
.                                    OPTION OFF  00:00:00
.                                    OPTION OFF  00:00:00
Oracle Enterprise Manager
.                                         VALID  00:12:34
Oracle XDK
.                                         VALID  00:01:29
Oracle Text
.                                         VALID  00:01:17
Oracle XML Database
.                                         VALID  00:04:45
Oracle Database Java Packages
.                                         VALID  00:00:33
Oracle Multimedia
.                                         VALID  00:04:37
.                                    OPTION OFF  00:00:00
Oracle Expression Filter
.                                         VALID  00:00:21
Oracle Rule Manager
.                                         VALID  00:00:11
Gathering Statistics
.                                                                00:15:12
Total Upgrade Time: 01:24:57

December 7, 2009

Write to a socket from PL/SQL

Filed under: 10g, 11g, Linux, Oracle — kkempf @ 4:32 pm

During a code clean-up, I had to figure out how to send a sequence number to a dumb (are there any other kind?) Windows application server over a tcp socket.  This sequence number will effectively act as a trigger for the app server to go print a label (with data referenced by this sequence number).  The Linux guy showed me how to create a testing socket on my Linux desktop so I could verify it was working, and I thought it was interesting enough to pass along.  Really the only variables are the remote host IP/name ( in my case), the remote port, and what you’re writing:

 bt_conn               utl_tcp.connection;
 retval                binary_integer;
 l_sequence            varchar2(30) := 'Sequence Number 101021';
 bt_conn := utl_tcp.open_connection(remote_host => ''
                                   ,remote_port => 5000
                                   ,tx_timeout => 1
 retval := utl_tcp.write_line(bt_conn,l_sequence)
 when others then
  raise_application_error(-20101, sqlerrm)

On my Linux box, I just opened a terminal and ran netcat, then ran the PL/SQL from another terminal session:

netcat listening on port 5000

December 3, 2009

Discoverer 10g with Ebuisness Suite 11i (CP6 available)

Filed under: 11i, Discoverer, Linux — kkempf @ 10:44 am

I’m still running Discoverer 10g…we’re not sophisticated enough or rich enough to justify OBIEE yet.  I noticed in Note 237607.1 that Discoverer 10g CP6 (8746296) was available for Windows, Solaris and HP-UX a few weeks back; now they’ve got the Linux x86 version available.   It’s as simple as OPatch gets: download it, unzip it, stop discoverer, run opatch, start discoverer.

Version before and after the patch was:; apparently this doesn’t change with CP’s

I looked up the Statement of Direction for Oracle Discoverer 10g, as it’s always been a bit of a red-headed stepchild in the Oracle inventory (more so with the advent of OBIEE).  Looks like 10gR2 (10.1.2) loses premier support in 1 year, and extended support in 2 years.  Not sure if that means it will be kept on life support on an exception basis for Ebusiness Suite users, or if we’ll see a cert for Discoverer 11g with 11i.

Incidentally, about a year ago we moved from desktop-based Discoverer 4i to 100% web based use of Discoverer 10g.  From an administration standpoint, it’s a dream.  We intentionally decided not to let users have the 10g Discoverer plus desktop client due to the hassles of patches, tnsnames changes, etc, and the result is that I only think about Discoverer a few times a year when various patchsets come out.

Discoverer 10g with 11i

December 2, 2009

Performance or Security?

Filed under: Uncategorized — kkempf @ 5:02 pm

Fast or Safe?

I’m a regular reader of Steven Chan’s blog, and he had a good posting of an Open World presentation about 11i/R12 performance tuning.  It referenced note 244040.1 “Recommended Performance Patches for Oracle E-Business Suite”.  This document is a patchwork (no pun intended!) of patch names which seems to be a  little more haphazard than the typical Oracle document.  Nonetheless, I went through and (tried) to determine which patches were applicable to my RDBMS ( backend of 11i.  I found 9 patches when I included their section on Advanced Compression.

A brief aside, their list for Advanced Compression included my “showstopper” bug fix from last summer.  That’s in and of itself good, except I wouldn’t call a patch which prevents the corruption of your standby database to be a recommended performance patch…

So I began to use opatch to apply the patches (8364676  8508056  8834636  8409203  8580883  8876094  8447623  8599477  8983266) to my TEST environment (Linux x86-64), and suprise!, all but 4 conflict with PSU 1009.

So, to paraphrase:

  1. Oracle recommends you apply security patches (CPUs/PSUs)
  2. Oracle recommends you apply performance patches
  3. Oracle security patches are inherently at conflict with the performance patches

Incidentally, I’m aware that in theory, you can contact support and get patch merges, but I’m not going down that road every quarter!

Blog at