Kevin Kempf's Blog

August 20, 2018


Filed under: Uncategorized — kkempf @ 2:42 pm

EBS Table Inventory

For a variety of reasons, I took inventory of the largest objects (in this particular case, tables) in my R12.2 EBS environment.  I strongly suggest you do the same, upon occasion, as it’s somewhat enlightening.

 ,bytes/1024/1024/1024 gb
  segment_type = 'TABLE'
order by
  bytes/1024/1024/1024 desc

At the very top of my list was APPLSYS.FND_LOG_MESSAGES, checking in at 29.4GB.  I now had a target.

Read the directions

This is EBS, we can’t just arbitrarily start shooting and hope we don’t break anything.  There’s a number of MOS notes on support describing various aspects of purging this table.  Number one on the list was the concurrent request, “Purge Logs and Closed System Alerts”.  There were also some notes about a bug which prevented this CR from performing correctly, but they didn’t seem relevant to EBS 12.2

Back to my problem child

SQL> select count(*) from applsys.fnd_log_messages;


This jumps out immediately.

SQL> select owner, segment_name, bytes/1024/1024/1024 gb 
from dba_segments where segment_type = 'TABLE' 
and segment_name = 'FND_LOG_MESSAGES';

OWNER                     SEGMENT_NAME              GB
------------------------- ------------------------- ----------
APPLSYS                   FND_LOG_MESSAGES          29.4005127

I run Concurrent Request “Purge Logs and Closed System Alerts” every week, and the log shows it’s completing normal.  Why are 308 rows consuming so much space on disk?  Let’s assume that this table has had no attention since it was first installed about 14 years ago.  There were times when debug was on, to be sure, filling FND_LOG_MESSAGES faster than normal.  But after a quick check of Profile Option Values I see that at present, nobody has debug enabled.

Proving out the solution

Is it possible that 14 years of inserts and deletes could cause this table to have developed a bad case of high water mark 29 GB deep?

SQL> alter table applsys.fnd_log_messages enable row movement;

Table altered.

Elapsed: 00:00:00.08
SQL> alter table applsys.fnd_log_messages shrink space;

Table altered.

Elapsed: 00:56:47.99
SQL> select owner, segment_name, 
bytes/1024/1024/1024 gb from dba_segments 
where segment_type = 'TABLE' 
and segment_name = 'FND_LOG_MESSAGES';

------------ ---------------- ---------
Elapsed: 00:00:00.31

Not bad; we’re down from 29.4GB to 14.5GB without much effort.  Let’s go in for the kill

SQL> alter table applsys.fnd_log_messages move;

Table altered.

Elapsed: 00:01:30.32

SQL> select owner, segment_name, 
bytes/1024/1024/1024 gb from dba_segments 
where segment_type = 'TABLE' 
and segment_name = 'FND_LOG_MESSAGES';

------- ---------------- ----------
Elapsed: 00:00:00.13

Sweet.  29GB down to .0003GB.

Don’t forget that alter table xyz move renders indexes invalid.

set pagesize 0
'alter index '
|| owner
|| '.'
|| index_name
|| ' rebuild;'
status = 'UNUSABLE'

SQL> alter index applsys.fnd_log_messages_n4 rebuild;

Index altered.

SQL> alter index applsys.fnd_log_messages_n5 rebuild;

Index altered.

SQL> alter index applsys.fnd_log_messages_n7 rebuild;

Index altered.

SQL> alter index applsys.fnd_log_messages_n8 rebuild;

Index altered.

March 11, 2014

A Fun Diversion?

Filed under: Uncategorized — kkempf @ 11:01 am

What do others see on your resume?

I ran across a suggestion today to see what a recruiter sees on your resume. It’s kind of a fun exercise; just copy your resume to the clipboard and paste it in. It looks a LOT prettier on their website…

Here’s where mine ended up:

February 26, 2014

Controlling Data Guard Replication Lag

Filed under: Uncategorized — kkempf @ 12:35 pm


Tuning Standby Lag with Oracle Active Data Guard

We bought active data guard because of increasing reporting demands on our primary database in our ERP environment. While active data guard doesn’t play nicely with Apps 11i out of the box (it’s read-only, and just to establish a forms session you need to be able to write), it can fill a nice role offloading CPU load by serving up near-real time reports for specific (in our case bolt on) applications which only need to read tables.

What is Near-Real Time?

Once an SLA is established for the “oldest” data the report can return, you can tweak Oracle to honor it. In my case, the example below shows me going from no lag target to 300 seconds and finally to 600 seconds. Note that once you settle on a number, you’d be wise to add a “scope=both” to the end of the alter system.

$ sqlplus / as sysdba SQL*Plus: Release Production on Wed Feb 26 10:26:49 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
 Oracle Database 11g Enterprise Edition Release - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 SQL> show parameter archive_lag_target;

 ------------------------------------ ----------- ------------------------------
 archive_lag_target integer 0
 SQL> alter system set archive_lag_target = 300;
 System altered.
 SQL> alter system set archive_lag_target = 600;
 System altered.

Lag Behavior

I found it interesting to note that left to “its own devices” the pattern of archivelog ships (and therefore application on the other end)becomes an inverse function of how active your database is. In other words, the recency of your data at your standby is related to how fast you fill your online redo logs (which is also a function of how big they are), plus the odd twist of system-driven logfile switches. Lets say you had 200MB redo logs with a nearly idle system. Your lag can get huge if not tuned!

The graphic below captures the result of SQL:
select to_number(substr(value,instr(value,':',1,2)+1,length(value))) + 60 * to_number(substr(value,instr(value,':',1,1)+1,2)) seconds from v$dataguard_stats@apps_to_dataguard where name = 'apply lag';


The left part of the graphic (before 10:21) shows data transport left untuned. From 10:21 to 11:21 you can see where I had it set to 300 seconds.
From 11:21 onward it’s set to 600 seconds.

Near-Real Time on Steroids: Real Time Apply

Check your licensing, your mileage may vary. The easiest way to keep the standby up to date is to use real-time apply and standby logs. To create standby logs, you go to your standby and cancel recovery:

alter database recover managed standby database cancel;

Next, add your standby logs. They need to be the same size as the online redo logs on the primary. Make N+1 of them on the standby. The syntax looks like this:

alter database add standby logfile group 41 ('/usr/local/oracle/redo/log41b.dbf','/u04/appprod/proddata/log41a.dbf') size 100M;
alter database add standby logfile group 42 ('/usr/local/oracle/redo/log42b.dbf','/u04/appprod/proddata/log42a.dbf') size 100M;
alter database add standby logfile group 43 ('/usr/local/oracle/redo/log43b.dbf','/u04/appprod/proddata/log43a.dbf') size 100M;
alter database add standby logfile group 44 ('/usr/local/oracle/redo/log44b.dbf','/u04/appprod/proddata/log44a.dbf') size 100M;

Finally, restart your recovery with the real-time apply:

alter database recover managed standby database disconnect using current logfile;
Now your apply lag and transport lag drop to zero:


In the graph above, you can see the final version of data apply rates to the standby.

8:58a-10:21a: unmanaged apply rate (mostly happening when a log on the primary got full)

10:21a-11:21a: honoring the 300 second alter system set archive_lag_target=300;

11:21a-4:30p: honoring the 600 second alter system set archive_lag_target=600;

4:30p-end: real time apply started with standby logfile groups (effectively 0).

Thanks Roth!

Thanks Roth!

February 24, 2014

Oracle RDBMS tier is Virtualized

Filed under: Uncategorized — kkempf @ 2:24 pm



So we’ve been running non-Production versions of our Oracle 11i E-Business suite environment on VMWare since about 2006, and my Linux x86 PAE 11i front end on VMWare since at least 2008.  I never opened a ticket or had any issue with VMWare affecting a guest OS in any way, whether the kernel was Red Hat, Oracle Linux “Red Hat compatible” or even now the UEK.  Oracle protects themselves with this (Doc ID 249212.1):

 Oracle has not certified any of its products on VMware virtualized environments. Oracle Support will assist customers running Oracle products on VMware in the following manner: Oracle will only provide support for issues that either are known to occur on the native OS, or can be demonstrated not to be as a result of running on VMware.

The truth is, they try to scare you away from it, but it runs just fine, in my experience.  The big unknown was always the core database, running on 64-bit Linux.  It really does work hard, and has lots of moving parts.  As we began the project to convert it, we realized we had a lot of questions without firm answers, so we engaged a prominent 3rd party Oracle to VMWare integrator.  They did a great job managing the project, but to be honest, unbeknownst to us we’d already figured out most of the technical detail.

Project Flow

There was an extra wrinkle for our production go-live.  The server was physically moving from one data center to another one about 50 miles up the road.  We had good WAN links, but it still added some time.  While we considered leveraging dataguard to accomplish this, in the end we landed on an RMAN restore and Data Domain replication.

  • Create a VM with lots of CPUs and the same memory footprint as production.  Put in all the patches, updates, kernel parameters, and set up huge pages for the 64gb SGA.
  • Add disk from the SAN leveraging LVM and ext3.   We could have used ext4, but the Linux OS was Oracle Linux 5.10 (UEK) and ext3 felt more “tried and true”
  • Create a VMWare template at this point (this was actually done several times, after many, many OS tweaks)
  • Install the Oracle RDBMS software ( in my case) as well as deploying the EM agent
  • Use RMAN to bring the database to the new VM from the physical box.  In the case of test runs, this was an RMAN duplicate.  For the final, live production run it was a little trickier:
    • RMAN full backup of the RDBMS the night prior, so that full can get across the WAN and my incremental difference will be smaller
    • Shut down the 11i front end so users can’t get in
    • RMAN (hot) backup the RDBMS, shut it down
    • Wait for the Data Domain to replicate the RMAN backup and archivelogs to where the new server was
    • RMAN restore and recover the database
    • IP changes
      • Perform internal DNS changes to the new vlan
      • Bring up the 11i front end at the new location with a VEEAM restore
      • Re-IP the two machines, via /etc/hosts and /etc/sysconfig/network, as well as FND_NODES changes (see 751328.1)

Results & Advantages

Since the move to VMWare, we’ve had no issues whatsoever.  The database runs mostly in memory, and the users are none the wiser.  VMWare does bring some advantages to the table:

  • Unlike a physical machine, where I have to be in the data center at a console, or possibly over a DRAC card or the like, when I reboot my machine now, I can watch it in VCenter.
  • I don’t have to worry about drivers.  I had a serious issue when our oddball 10gb NICs decided to stop working after a yum update on the physical box during a reboot.
  • If the physical server (which in the case of the RDBMS is effectively the same thing as the ESX host, as the host is entirely devoted to running Oracle) running the RDBMS breaks, overheats, has a bad memory chip, burns up a CPU, or gets struck by lightening, we can shut it down cold and storage v-motion it to another host in minutes.
  • It’s a mainstream, mature product.  I did a lot of homework on this, and the general consensus was that Oracle VM wasn’t ready yet, but there’s LOTS of people running Oracle on VMWare.

December 31, 2013

2013 in review

Filed under: Uncategorized — kkempf @ 7:55 am

The stats helper monkeys prepared a 2013 annual report for this blog.   I guess I need to blog more.  Haven’t had as much time this year, nor relevant content.  Next year I have a few heavy hitters on the docket: leveraging active data guard, tearing down Discoverer 11g and replacing it with Oracle Apex (nearly done, by the way), virtualizing the main ERP Production database on VMWare, and (finally) an upgrade to R12.2.  While continuing to work on my MBA.  Should be fun, see you there!

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 49,000 times in 2013. If it were a concert at Sydney Opera House, it would take about 18 sold-out performances for that many people to see it.

Click here to see the complete report.

May 15, 2013

Best Oracle 11i Error Message Ever

Filed under: Uncategorized — kkempf @ 12:40 pm


March 21, 2013

When an Oracle Linux Clone Won’t Register…

Filed under: Uncategorized — Tags: — kkempf @ 8:09 am


Recently we used VMWare to clone an existing, registered Oracle Linux machine, and while it worked fine in general, it refused to register with ULN.  When I ran uln_register (this is an OL 6 machine) it said the machine was already registered.  This was after I regenerated the RHN UUID by using uuidgen and putting the resulting key in /etc/sysconfig/rhn/up2date. 

The resolution was to remove /etc/sysconfig/rhn/systemid and re-run uln_register.  Apparently this file also keeps some record of a machine’s “uniqueness” but isn’t well documented in the ULN FAQs.

January 3, 2013

A peek at 2013, and Windows 8

Filed under: Uncategorized — kkempf @ 11:05 am

Back in the saddle

So it’s been some time since I posted, and I figured I’d start off with low hanging fruit.  It’s not that I’ve given up on blogging, just haven’t had a lot to write about lately.

Suddenly… 2013!

It turns out, this year I should have plenty of fodder for the blog.  Among the items on the docket:

  • Replacing Highjump with Oracle Mobile Supply Chain in 11i
  • Upgrading 11i to r12
  • Migrating all EBusiness suites to a new colo facility
  • Moving all storage from Dell Equilogic to Nimble Storage

Windows 8

So over the course of the holidays, I had the opportunity to see Windows 8 first hand.  Like many of you, I suppose, I often become the defacto PC support person for the family.  It’s no mystery that I’m not a giant proponent of Micro$oft (I’m updating this blog entry from Ubuntu), but I do run Windows 7 and think it’s a solid OS.

I spent about an hour trying to figure out Windows 8.  It’s an hour I can’t get back.  In short, it’s the most useless upgrade/update I’ve ever seen in my life, and appears engineered for the ADD/Facebook generation, not people who have real work to do on their PCs.  I can say, with all confidence, that I will never install that OS on any PC I own.   Here’s the list of who I think probably likes Win 8:

  • Apple
  • M$ fanboys and girls
  • Me.  Because it means I can say “Sorry, I can’t help you, I haven’t installed Windows 8 and it just doesn’t make any sense to me”

So I have to ask, why?  It seems like M$ banked the future on the smallest segment of their markets:

  • If Microsoft had any presence in the smart phone market it might make sense, but Microsoft smart phone sales are virtually non-existent (2% last article I read). 
  • I might be inclined to use a touch screen at the airport for self-check in, but it’s not how I work.  What minute slice of the PC market is for touch screens?
  • I’ve never even seen a Microsoft tablet.  I assume they must exist.

I can say with some confidence, this OS will never get into the corporate world.  I’ve read articles comparing it to the Vista debacle, but this seems worse to me.  At least Vista ran and was usable if you backed it with the right hardware.  I left the Windows 8 experience with the hopes that I’d never have to interact with it again.


October 10, 2011

SELinux & RedHat Reboots

Filed under: Uncategorized — kkempf @ 8:15 am

PC Load letter

So I’m working a maintenance window yesterday which required a reboot of a RHEL5 production server which houses 4 non-11i Oracle databases.  It had been nearly a year since the last reboot (!) and I badly needed a kernel update.  After the reboot, I go to start the listener on my Kronos (timekeeping) database, and I get this:

$ lsnrctl start KRONOS 
lsnrctl: error while loading shared libraries: /u01/kronos/kronosdb/11.1.0/lib/ cannot restore segment prot after reloc: Permission denied

I have to confess, I’ve actually hit this before, but it had been a long time (at least a year) and I had to knock the cobwebs free to remember the solution. In short, SELinux doesn’t allow this shared library to be accessed, and this will stop your listener, sqlplus, webcache, or other executable from starting. The quick fix is rather simple: disable SELinux as follows:

# su - 
# getenforce 
# setenforce 0 
# exit 
$ lsnrctl start KRONOS 
LSNRCTL for Linux: Version - Production on 09-OCT-2011 10:23:19 Copyright (c) 1991, 2008, Oracle. All rights reserved. 
Starting /u01/kronos/kronosdb/11.1.0/bin/tnslsnr: please wait... 
The command completed successfully

The better answer?

If you check Doc ID 454196.1, you will see that Oracle has a few solutions for it, as even they recognize that disabling SELinux is bad policy. First, it appears there is a patch for RDBMS (9215184) and that the issue is resolved in For those of us who don’t consider an RDBMS upgrade a solution, apparently Red Hat has a bug filed for this. Interesting, as if you read it, you will see that Oracle built their shared library wrong, and RedHat had to essentially create a new SELinux rule for this in RHEL5.5:

Fixed in selinux-policy-2.4.6-256.el5
I believe this has missed RHEL5.4, so it will be in RHEL5.5

Note that this seems a little suspect, as I checked my release and it would appear to be fine:
# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.7 (Tikanga)

July 13, 2011

Things support does which makes my head want to explode

Filed under: Uncategorized — kkempf @ 3:32 pm

Sorry, this is a quick post from the “I’m speechless” category:

Didn't Kirk manage to kill some super advanced computer with a logical argument like this?

Older Posts »

Create a free website or blog at