Kevin Kempf's Blog

September 30, 2009

Forms Designer – Old School (sans Windows)

Filed under: 11i, Forms, Linux — kkempf @ 2:50 pm

As stated previously, I don’t run a Windows desktop.  In response to a request from Oracle support to compile a form in Forms Builder, I had to reach back into the wayback machine and remember how to do it from the command prompt.  Just thought it was a fun diversion… of course, there was no difference in the compilation errors when I did it in Forms Builder than when I did it on the command line… but that’s besides the point.

In order to do this, you need to be able to project an X session onto your screen; it’s easy when you’re running native Linux, harder from Windows as you need some clumsy application running to manage it.  Basically, if you can type xclock on the Linux command line and get a clock on your screen, you’re good.  If not, Google search xhost+ and you will probably get to the answer in short order…

Alright, so here’s how it went.  First, to compile a form from the command line, ensure $AU_TOP/forms/US is in your path… or just be in that directory.  Then type:

$8.0.6_Home/bin/f60gen module=$AU_TOP/forms/US/PERWSSCP.fmb userid=APPS/password output_file=$PER_TOP/forms/US/PERWSSCP.fmx module_type=form batch=yes compile_all=special

Obviously, adjusting the paths and form names as required… Or you can do it from the GUI, just make sure $806_Home/bin is in your path (it should be) and type:

f60desm &

I know, it’s UGLY but it should come up looking like this:

Welcome Screen

Welcome Screen

Select a file and filter

Select a file and filter

Counter-intuitively, you can type the path of your form directly into the above form…

The eye-catching main screen

The eye-catching main screen

Now you click File->Connect to establish the database connection (as apps)

Database Connection Screen

Database Connection Screen

Finally, to compile, you highlight the form on the main page and click Program->Compile

Screenshot-Compile - PERWSSCP

In my case, you get a bunch of errors…


September 29, 2009

Using Custom Metrics in Enterprise Manager to Monitor Applications 11i (Part II)

Filed under: EM to monitor 11i, Enterprise Manager, Oracle — kkempf @ 3:26 pm

As I began to look at what was involved in monitoring custom metrics, I forgot how many steps it took to actually set up email notifications.   Therefore, I’m going to devote this Part II to notifications, and spell out some cool metrics in (yet) another future entry.  I should note before I begin here, that I’m not trying to be secretive about anything in the screen shots; if anything is blanked out (using the GIMP, by the way) in the images below, it’s most likely because it has personal or company information in it.

First step in receiving email notification is setting up an administrator account.  On the top right in EM, above the blue tabs, you need to click on Setup.Setup

Now click on Administrators on the bar on the left, and then the Create button.  This should get you to here:

Create an Administrator Account

Create an Administrator Account

Once your account is set up, you should confirm that your global SMTP settings are in place.  Click on Notification Methods on the bar on the left side of the screen

SMTP Setup

SMTP Setup

Note under Outgoing Mail (SMTP) Server, you need a fully qualified hostname or IP address of your SMTP server.  The Sender’s E-Mail Address is simply who will appear in the From field of the email you receive.

Now, log out and back in as the user you just created (I’m assuming this is you!).  Click on Preferences in the top right, and General in the bar on the left.  You should see the email address you just entered, as well as a preference for Message Format.  I recommend Short Format as most mobile devices don’t really have enough screen real estate to fit all the information it sends on Long Format.  While you’re here, you might as well click on TEST to ensure your SMTP and email settings are right – this should arrive in your Inbox in short order.

General Preferences

General Preferences

Next, lets set up the notification schedule.  On the left side, click on Schedule (you should already be under Preferences in the top right).  Select an administrator (you!) with the flashlight, and hit the Change button.    Since there is no schedule, the only button to hit is Define Schedule.   From here, you simply follow the bouncing ball, so to speak.  Rotation frequency is for shops where you may not be on the hook every week for after hours notifications or the like.  In my case, it’s a one-man operation, so I use a weekly rotation beginning “today”.  From the next screen, you need to fill in the hours you will be notified.  Again, in my case, it’s me or me, unless I’m on vacation.  So I just put in 12AM to 12AM and check Monday, Tuesday, Wednesday, Thursday , Friday, Saturday and Sunday, then hit the Batch Fill-In button.

schedule definition

Click finish and you’re almost there.  The last thing to configure is what you want to be notified about.  Again from the  Preferences selection in the top right, you now need to click on Rules.  The screen will look like this:

Notification Rules

You will notice that sysman owns the “canned” events, and you can choose to check Subscribe (Send E-mail) on any you wish.  In my case, I care about:

  • Agents Unreachable (indicative of host availability)
  • Database Availability and Critical States
  • Host Availability and Critical States
  • Listener Availability
  • User Defined Metrics

All of these are self explanatory, I believe, except the last.  In this case, I’ve created a category for my User Defined Metrics.  The easiest way to do this is select an existing category (Database Availability and Critical States works well) and click the Create Like button.  Under General call it User Defined Metrics.  Under Availability check what you’re interested in.  I’d recommend everything but blackout begin/end events:

Notification Rule2

From the Metrics tab is where you need to find your custom metrics.  Hit the Add button, then search (%User%) or click next (next, next…) for User-Defined Numeric Metric.

Notification Rule3

Once you add this, you can hit the Edit pencil on the right, and select exactly which User-Defined Metrics, on which database, you wish to monitor.  I’d also check Critical, Warning and Clear, just for good form.

Edit Metric

That’s a lot of ground covered!  At this point, you can test your User-Defined Metrics and ensure you receive notification when they fall out of tolerance.  Here’s a “gotcha”: as you add new User-Defined Metrics, they do not automatically get added to the selection in the Metrics screen, above.  Meaning you need to go back afterwards and manually add it/them to the already selected metrics, in order to be informed of a change in state on the new metric(s).

Next time: Some handy User-Defined Metrics with 11i, and how to monitor things at the OS level on the front end, such as the number of forms sessions and the availability of the Xvnc server (required for some reports)…

September 23, 2009

Using Custom Metrics in Enterprise Manager to Monitor Applications 11i (Part I)

Filed under: 11i, EM to monitor 11i, Enterprise Manager, Oracle — kkempf @ 9:26 am

Oracle calls me from time to time, and inevitably every few months I’m asked why I’m not using the Applications Management pack in Enterprise Manager to monitor my 11i environment.  I tell them that I have no compelling reason to pay for something which I feel I can code myself through user-defined metrics.  I’ve found these user-defined metrics to be one of the most powerful and flexible features in EM.

At present, I use 30 custom metrics to monitor what I’ve found to be critical pieces of the ERP in a production environment.  It would be a rather long blog entry to go through all of them, but for purposes of this entry, I’ll cover how to set up a custom metric and how to monitor the Standard Managers.

To begin, you can get to the custom metrics screen by navigating to a database target (in this case, the ERP production database) and scrolling all the way to the bottom.  Click User-Defined Metrics and you will get to a screen like this:

User-Defined Metrics Base Screen

User-Defined Metrics Base Screen

At this point, let’s assume there are no existing User-Defined Metrics, and you wish to create a metric which monitors the number of Standard Managers running.  Click on the create button in the top right, and you get a screen like this:

Screenshot-Oracle Enterprise Manager (KKEMPF) - Create User-Defined Metric - Mozilla Firefox

The bottom part of the screen looks like this:

Screenshot-Oracle Enterprise Manager (KKEMPF) - Create User-Defined Metric - Mozilla Firefox-1

It’s really rather self explanatory; you name your metric, and define whether the sql query you wrote will return number or character.  Single value versus column is well explained in the form.  After you enter your SQL:

select to_number(running_processes) from apps.fnd_concurrent_queues where concurrent_queue_name = ‘STANDARD’

and the apps username and password, you can hit the TEST button to confirm you receive expected results.  Finally, you define what the critical (and warning, if desired) thresholds are (in this case, I’m using 12 standard managers so anything less that this is a problem).  My alert message uses %Key% which simply means if this alert is triggered, it will tell me what the current number of standard managers is, and finally I tell it how frequently to poll.  That’s it!  Hit OK and it will show up on your user-defined metrics page now (takes a bit of time for the first query to show up on this screen, but it will eventually, or at least it will show a metric collection error if there is a problem).

Next time: More metrics, and how to receive these user-defined metric alerts in e-mail…

September 14, 2009

JDK 1.6 on the Apps Tier

Filed under: Uncategorized — kkempf @ 11:30 am

With the native Java implementation completed, I’ve been able to finally catch up on some overdue updates to keep my techstack certified.  The first piece was to upgrade to Forms, and this past weekend I upgraded from JDK 1.4 to 1.6.   This is covered in Metalink note 401561.1; here’s the condensed version:

  1. Install the JDK in a common location on the host (/opt was a good location for me)
  2. Apply 2 interoperability patches via adpatch (5622511 and 5977502)
  3. Run the interactive script: $FND_TOP/bin/ -script=SetJDKCfg -force
    1. I ran the script with the “Run autoconfig when it’s done” option, though you could easily just do that manually after it’s done
    2. I had to use -force because the script thought I was already on jdk 1.6
      1. In my case, I have a symbolic link in /opt called jdk which was points to whichever version I wish to have active
      2. Prior to running this script, my symbolic link was pointed to 1.6; I suspect if it were still pointing at 1.4 I would not need -force
  4. Regenerate (force) Jar files via adadmin

I found 2 differences in the context file, between jdk1.4 and 1.6 (since I used the symbolic link described above, my java path was /opt/jdk regardless of which version I’m using so that didn’t change).  The jdk14 and jdk16 are just listed here for clarity.

  1. CLASSPATH: /opt/jdk14/lib/i18n.jar becomes /opt/jdk16/lib/dt.jar
  2. AF_CLASSPATH: /opt/jdk14/lib/tools.jar becomes /otp/jdk16/lib/dt.jar

September 9, 2009

11gR2 RMAN: You are too dumb to pick a compression algorithm

Filed under: 11g, Oracle, RMAN — kkempf @ 9:57 am

OK, that’s being a little harsh, but as of 11.2 RDBMS, the old configure compression algorithm parameter has been dumbed down.

If you don’t have Advanced Compression, you set it to BASIC.

If you do have Advanced Compression enabled, you can choose between HIGH, MEDIUM and LOW.

In reality, these terms map out as follows:

HIGH = unmodified BZIP2

What does this mean to the average person?

If you’re not using advanced compression, or are already using bzip2 and you wish to continue to use compression, simply do one of the following

  • configure compression algorithm basic;
  • configure compression algorithm clear;

If you are using advanced compression and have bzip2 or zlib set, you need to select a level before running  backup to avoid an ORA- error:

  • configure compression algorithm [high/medium/low];
  • configure compression algorithm clear;

September 8, 2009

Native Java Implementation

Filed under: 11i, Techstack — kkempf @ 8:54 pm


Only slightly behind the certification curve,  we’ve completed a cut over to native (Sun/Oracle?) Java last week.  I should explain that as a manufacturer, we have large percentage (40%?) of PCs which are “floor machines”.  This means that they are locked down tight, and basically the operators can only get into Oracle.  They are not local admins, and may not install software, and even if they could would likely cause problems with knowing whether to “trust” an install from an unknown source (or even a known source, they likely wouldn’t know who Sun is or whether to trust them!).   This provided some major obstacles, but in the end, after a year of good planning, it was virtually pain free.    Our implementation from a high level looked something like this:

  • Use Microsoft AD policy to push a public certificate used to sign the production Jar files
  • Use a login script to push Java 1.6 to all PC’s upon which Jinitiator was detected; capture install data to ensure we had 90-95% of PC’s installed
  • Created a “custom” Java install (and sign it with the private cert)  to put on the application server based upon Sun/Oracle Java 1.6
    • Pop up a DOS box to say “Installing Oracle Components”
    • Repackage the .exe as a self-extracting zip file, and naming it so that it looks like the “vanilla” Sun/Oracle Java; land it in $COMN_TOP/util/jinitiator (j2se16012.exe)
  • Perform the required patching and configuration on the application server; adadmin force sign of all Jar files
  • Monitor/assist with issues, especially VPN/remote users

I should be very clear and say that the self-extracting executable, vb scripting, and policy/active directory pushes were done by competent systems/desktop support folks.  I could not have done this piece on my own, and basically just provided the adkeystore.dat file (which contained the private key).  Oracle has default passwords on the certificates in this keystore; you can find it on Metalink.

As far as problems after cutover, there were a few minor issues, but probably no more than 5 problems.  For the most part, people came back from lunch, logged back in and didn’t even realize they were using Java now instead of Jinitiator.

11.2 Install

Filed under: 11g, advanced compression, Oracle, RMAN — kkempf @ 12:10 pm

I bit the bullet and installed 11.2 on my Enterprise Manager/RMAN repository database last week.  It’s a small vm, used mostly by only me, so I figured why not?

First impressions were a little mixed.  I really thought the installer looked great.  It’s come a long way, from when I first started using it (8i).  I wish I’d taken some screen shots, but basically it had a consistent look on all pages, and a nice “progress bar” as you were answering the questions before installing.  In addition, when it was checking pre-requisites, it had the ability to do a “fix it” for kernel parameters which was kind of convenient.  I did have to use yum to install a few packages (don’t know if I had to, but the point is the installer asked for them):

  • elfutils-libelf-devel
  • unixODBC
  • unixODBC-devel

I always do a manual upgrade.  Just too old school to trust the DBUA, so when I ran the pre-requisite script against my 11.1 environment, here’s what it had to say:

SQL> @utlu112i
Oracle Database 11.2 Pre-Upgrade Information Tool    09-03-2009 10:31:53
--> name:          OEMREP
--> version:
--> compatible:    11.1.0
--> blocksize:     8192
--> platform:      Linux x86 64-bit
--> timezone file: V10
Tablespaces: [make adjustments in the current environment]
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 576 MB
.... AUTOEXTEND additional space required: 86 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 129 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 576 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
--> MGMT_TABLESPACE tablespace is adequate for the upgrade.
.... minimum required size: 2932 MB
Flashback: OFF
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
-- No update parameter changes are required.
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
-- No renamed parameters found. No changes are required.
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
-- No obsolete parameters found. No changes are required
Components: [The following database components will be upgraded or installed]
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
Miscellaneous Warnings
WARNING: --> Database is using a timezone file older than version 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains schemas with stale optimizer statistics.
.... Refer to the Upgrade Guide for instructions to update
.... schema statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
....   SYS
WARNING: --> log_archive_format must be updated.
.... As of 10.1, log_archive_format requires a %r format qualifier
.... be present in its format string.  Your current setting is:
.... log_archive_format='OEMREP%T%R__%s.arc'.
.... Archive Logging is currently ON, and failure to add the %r to the
.... format string will prevent the upgraded database from starting up.
WARNING:--> recycle bin in use.
.... Your recycle bin is turned on and it contains
.... 203 object(s).  It is REQUIRED
.... that the recycle bin is empty prior to upgrading
.... your database.
.... The command:  PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.

In short: empty your recycle bin, and %r is now a required part of the archivelog format.  No problem, made the minor changes and proceeded with the manual upgrade.  It didn’t complain again, until I was done and wanted to connect to RMAN and upgrade the catalog.

First problem: ZLIB is apparently no longer a valid compression algorithm.  Opened an SR to figure out what happened to it, as it was the only piece of advanced compression that worked.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on oem_disk_backup channel at 09/03/2009 11:51:40
ORA-19962: compression algorithm ZLIB is not valid with release

Next problem: upgrading the recovery catalog failed.  I can only describe my repository as vanilla, but when I tried upgrade catalog I received this error:

[oracle@zso-orabkp-01 env]$ rman catalog  rcat_user/pw@oemrep

Recovery Manager: Release - Production on Thu  Sep 3 12:06:43 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates.   All rights reserved.

connected to recovery catalog database
PL/SQL package  RCAT_USER.DBMS_RCVCAT version in RCVCAT database is not
recovery catalog is partially upgraded to

RMAN> upgrade  catalog;

recovery catalog owner is RCAT_USER
enter UPGRADE CATALOG command  again to confirm catalog upgrade

RMAN> upgrade catalog;

error creating  remove_null_db_unique_name_rows_from_node
RMAN-00571:  ===========================================================
RMAN-00569:  =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:  ===========================================================
RMAN-06004: ORACLE  error from recovery catalog database: ORA-02292: integrity constraint
(RCAT_USER.TSATT_F2) violated - child record found

I opened an SR on this issue, looks like a bug.  In the end my solution was to drop catalog and reuild it from scratch, then re-register all the databases.  Once that was done, everything was fine and I’ve been running on 11.2 ever since.   I’m still going over what else may be of benefit in this release, but at least I can say I’m running 11.2 in a production instance!

September 4, 2009

You can keep My Oracle Support (MOS)… I’ll take Metalink

Filed under: Oracle, Support, Uncategorized — kkempf @ 8:52 am

Apparently, I’m not the only one who dislikes the new “My Oracle Support” (MOS) website in comparison to the “old” Metalink.   I stumbled across this article in Computer World, which is interesting, but the most damning criticism I found was on one of Oracle’s own “internal but customer facing” type blogs here.  Check out the comments, they’re not too endearing.   Perhaps the most telling, however, is the poll, which shows 2/3s of users prefer the old Metalink, among other things.  Finally, here’s a forum link if you just can’t get enough.

Last year at Open World, Oracle was pushing MOS pretty hard, and although I don’t remember the exact public availability date, it had been out prior to Open World.  So the point is that after almost a year of improvements, most customers don’t want it.

To reiterate, here’s my major concerns with MOS:

  • Flash is cumbersome, proprietary, slow and buggy (especially on a linux desktop!)
  • Due to an acknowledged (6 months ago) Firefox bug, I can’t upload files via MOS (I have to log in to classic Metalink to do this)
    • SR Symptoms: When I try to attach/upload a file in the “new” metalink, it fails with some error I can’t even read because it’s cut out of the box in the pop up.
    • SR Response: I got the answer from our Metalink admin team : ” on trying to reproduce this getting the symptons associated with MOS bugs 7515007, 7660458. Please use classic UI or wait for SCM 3.1 where the upload is fixed. “
  • Oracle Configuration Manager (OCM) is a great idea, if only it worked
    • In my experience, even when I map an SR with a system, the analyst still asks the same inane questions about the system
    • When I open an SR with a OCM profile, the analyst still asks the same inane questions about product versioning
    • The success rate and recency of information OCM pushes to Oracle from my hosts is inconsistent despite having configured them all the same way
  • If I enter a doc id in the knowledge search, it’s never the first result.  It should always be the first result.  Maybe even in a boxed off area before the first result.  Instead, I get notes and documents referencing the one I’m looking for.

I don’t know if this is the elephant in the room that Oracle doesn’t want to talk about, but they’re going to have a lot of unhappy Oracle professionals if they force MOS on us at it exists now.

September 2, 2009

RDBMS 11gR2 Released

Filed under: 11i, advanced compression, Oracle — kkempf @ 8:33 am

I happened to see something about 11gR2 which linked me to this page.  I had expected the release to be in conjunction with Open World in October , but I was surprised to see it now available for download.  As a brief aside, I can see they’ll be promoting this instead in San Francisco.  When I was there in 2001, they were promoting Sun and EMC.  Then in 2006 they were promoting AMD and trying to kill Red Hat.  In 2008 it was HP Exadata.  Well they certainly spread the love.

I found it on edelivery and  OTN for linux x86 and linux x86-64.  I found it odd that I get spam email from Oracle on things completely irrelevant to me (such as Metalink 3 downtime) but I didn’t get a heads up on 11gR2.

You can browse the new features here. Unsurprisingly, Oracle says that is now the terminal 11.1 release, and updates will be delivered through PSU’s from this point forward.  Based on that information, and Oracle Lifetime Support policy, that means that the Premier support life for the major current RDBMS versions looks like this:

10.2 July 2010 (1 Year extension)
11.1  August 2012
11.2  September 2014

If you spend any time on their promotional page, you’ll find there is an “eduquiz” on 11gR2 features.  Since it boasts claims like 100% improvement in vague categories like productivity, it must have been done by a marketing department.  The one I found especially entertaining was in regards to Advanced Compression, so I added an answer which I think they missed in their quiz based on my implementation:


Create a free website or blog at