Kevin Kempf's Blog

December 5, 2013

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.

About these ads

2 Comments

  1. Hi Kevin,

    We are facing the same issue. The analyst on a SR we opened pointed me to patch 17028253 which adds an IFILE option to the protocol.ora file. This could allow you to disable the tcp.validnode_checking by putting “tcp.validnode_checking=no” in the IFILE – it uses the last iteration of a duplicated parameter. The IFILE option also allows you to specify the list of allowed machines in a tcp.invited_nodes in the IFILE – just be sure to include the IP addresses from the one in protocol.ora as it will only use the last tcp.invited_nodes it finds.

    James Cornell
    DBA
    Helena Chemical Company

    Comment by James Cornell — January 8, 2014 @ 4:42 pm

    • Thanks for the information; that makes sense and provides a reasonable work around. I can say that I tried setting the profile option value SQLNet Access to value: ALLOW_ALL at the site level as I indicated the MOS note recommended and as far as I can tell it does absolutely nothing. One autoconfig later (thanks, adpatch!) and I had a restrictive protocol.ora file back in place. Not knowing about the patch (and also heavily restricted on applying anything into production since 11i is essentially desupported) I scripted up a crontab fix which isn’t as eloquent as what the patch delivers, but gets the job done:

      if [ -f $ORACLE_HOME/network/admin/$SID_HOST/protocol.ora ]
      then
      echo “protocol.ora file exists in $ORACLE_HOME/network/admin/$SID_HOST. Please remove and restart 8.0.6 listener” | mail -s “protocol.ora file exists” kkempf @ my.email
      fi

      Comment by kkempf — January 9, 2014 @ 8:01 am


RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 30 other followers