Kevin Kempf's Blog

December 31, 2013

2013 in review

Filed under: Uncategorized — kkempf @ 7:55 am

The WordPress.com 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.

Advertisements

December 5, 2013

Cleaning up after Autoconfig

Filed under: 11i, Linux — kkempf @ 12:01 pm

shell

This post could also be called “writing a tiny shell script to notify me of a difference in a config file”.  Short and sweet 2nd post today, I love shell scripting!

Whenever I run autoconfig, it reverts back a value for my Mobile Supply Chain dispatcher port to a value stored in the database somewhere which is wrong.  If I dig into the log, it’s complaining that the port I want it to change to is in use.  Duh, it’s running.  Except it complains when it’s not running also.  The dispatcher is downright critical to getting into Mobile Supply Chain, and Oracle Support is being their usual helpful self.  Except now as of Dec 1, new for 11i: extra ignore!

Let’s assume you have a “good” version of a config file sitting next to one autoconfig wants to keep changing.  This script will email you the differences if they exist.  It assumes you have sendmail setup on your linux host.

cat checkmwacfg.sh

MWACHECK=`diff --brief $MWA_TOP/secure/mwa.cfg $MWA_TOP/secure/mwa.cfg.good|grep -woF differ`
if [ $MWACHECK ]
then
  diff $MWA_TOP/secure/mwa.cfg $MWA_TOP/secure/mwa.cfg.good | mail -s "$MWA_TOP/secure/mwa.cfg file is different" kempf@myemailaddress.com
fi

Crontab it up and you’re done:

# email me bad MWA config file in $MWA_TOP/secure once a day
0 16 * * * /(path to script)/checkmwacfg.sh

Good old ADI

Filed under: 11i — kkempf @ 9:42 am

Applications Desktop Integrator

I can’t really defend the fact that we’re still using ADI, running on 11i.  Some lines of business are resistant to change.  I recently ran into an issue after upgrading to RDBMS 11.2.0.4 and pushing the techstack to Autoconfig U.  It was a bugger, because accounting couldn’t publish their Financial Statements (FSG’s), and to be honest, while the truth resided on Oracle’s Support Site (or MOS), it wasn’t easy to find.

What is ADI?

For those who are blissfully unaware of this tool, it’s essentially a desktop Excel “plug-in” which connects to the database and the 8.0.6 techstack via the RDBMS listener or 8.0.6 listener on the applications tier (depending on what they’re doing).  In a word, it allows accountants to do their business in Excel, where they prefer to work, and then push data to the EBS.  It’s technically only certified for Windows XP, and Oracle has said it’s dead, to be replaced by Web ADI for uploading journal entries and Oracle Report Manager for publishing.  It seems accountants tend to like things just like they were in 1970, so that’s where we’re at.

New Security

As a part of moving to 11.2.0.4, some part of the applications tier packages (I heavily suspect Autoconfig U, but there were other patches), Oracle implemented a “security feature” which in effect just tightened down the 8.0.6 listener.  Generically, you can find the problem description in MOS note 291897.1.  What really happens is that after autoconfig, a protocol.ora file gets landed in your 8.0.6 $TNS_ADMIN directory.  This protocol.ora file has 2 lines (or at least mine did) generated by autoconfig:

tcp.validnode_checking = yes
tcp.invited_nodes = (<database node IP>, <apps tier IP>, <apps tier IP>)

Behavior

As a result of this change, and a regression test “miss”, accounting could log into ADI and post journals, but when they went to publish reports they received this awesome Oracle message:

adi error

I’m typing it out so google can crawl it: An error occurred while attempting to establish an Applications File Server connection.  There may be a network configuration problem, or the TNS listener may not be running.

The Quick Fix

# pound out the 2 lines in protocol.ora, and restart the 8.0.6 listener with adalnctl.sh stop/start.

The Better Fix

Log into 11i as system administrator and change profile option value SQLNet Access to value: ALLOW_ALL at the site level.  In theory this lets you survive an autoconfig run.

The Most Correct Fix (Grudgingly Admitted)

Figure out who needs access, and add them via OAM.  See MOS 281758.1 for details, look under Managed SQL*Net Access from Hosts

Soapbox

Why does Oracle wait 10 years and then change something like this in the terminal release?  Because they can.  While I admit it would be ideal to name every PC from which an accountant could log in to publish a report, the truth is I don’t know when they might get a new machine, or who might be doing it from home over VPN, etc.  It’s a good idea, but somewhat impossible to administer, and I’d venture to guess irrelevant for most installations.  Why not add the feature, but by default DISABLE it?  Just saying.

Create a free website or blog at WordPress.com.