This blog has moved

This blog has moved, to here: http://rnm1978.wordpress.com/

OBIEE performance monitoring and alerting with jManage

See post on my new blog here: http://rnm1978.wordpress.com/2009/07/29/oracle-bi-management-jmanage/

Moved to Wordpress

Blogger's rubbish editing interface has made me jump ship to Wordpress.

Check out my new blog here and please update to my new RSS feed

I've yet to work out how to mimic the "Followers" feature of Blogger which I quite liked, anyone know how? I'm hosting on the free Wordpress blog so suspect I'll be limited by functionality.

How to find out what web application server is in use

If, for some reason, you need to check what web application server is in use for Presentation Services (as this chap needed to), you can use an add-in for FireFox called HttpFox to inspect the HTTP headers.

1. Install HttpFox (and obviously Firefox if you don't have it already!)
2. Open the HttpFox window (Tools -> HttpFox -> Toggle HttpFox)
3. Click the Start button in the HttpFox window
4. Navigate to your OBIEE home page
5. Click the Stop button in the HttpFox window
6. Click on the first entry in the list, URL should be http://yourserver:7777/analytics/saw.dll?Dashboard
7. In the right-hand pane of the Headers tab you should see Server listed. In this instance, it's Oracle-Application-Server-10g/10.1.3.1.0 Oracle-HTTP-Server

Maker's Schedule, Manager's Schedule

I found this post very interesting: Paul Graham : Maker's Schedule, Manager's Schedule (originally found here)

It was one of those mini-revelations when I found something that greatly resonated and explained an inexplicable frustration I find in the workplace sometimes.

I wonder how applicable it can be to a large corporation though, rather than a start-up where it's given that people are allowed to be bolshy with their meetings? ;-)

ORA-00922: missing or invalid option

We routinely change Oracle passwords as part of security best-practice, I keep hitting this and keep forgetting why! :-)

ALTER USER DAC_REPO IDENTIFIED BY 1KoBe3RH REPLACE YlR94tqp
Error report:
SQL Error: ORA-00922: missing or invalid option
00922. 00000 - "missing or invalid option"
*Cause:
*Action:

Someone better qualified than me can explain why but I suspect it's the leading number in the new password. Quoting the passwords then works fine:

ALTER USER DAC_REPO IDENTIFIED BY "1KoBe3RH" REPLACE "YlR94tqp"

ALTER USER DAC_REPO succeeded.

OBIEE Windows PerfMon counters

Yet another way to access the BI Management data discussed here - through Windows' PerfMon tool.

This will only work for installations where your OBIEE server is running on Windows. You should be able to run PerfMon locally or remotely. Standard practise would be not to run it locally on a Production machine :-)

To run PerfMon go to Start->Run and enter perfmon, or navigate Start -> Settings -> Control Panel -> Administrative Tools -> Performance

By default a handful of metrics about your local machine are displayed:

Right click and Add Counters:
If you want to monitor a remote installation of OBIEE (on Windows only, remember) then enter the network name eg \\MYREMOTESERVER in 'Select counters from Computer:', otherwise set this to 'Use local computer counters'.

Then click on Performance object dropdown, and you should see a long list of Oracle BI performance objects:
Pick one of these and a list of counters within the object will be listed. You can add all, some or just one of these.


By default the Performance Counters are installed I think, but both NQSServer.exe and sawserver.exe have commandline options for reinstalling them (or uninstalling, if you want to):

NQSServer.exe /installperf
NQSServer.exe /uninstallperf

sawserver.exe /installperf
sawserver.exe /uninstallperf

PerfMon is documented well elsewhere on the web so I won't say much more other than that you can use it interactively or logging to file. The latter would be very useful for trending of performance data, you could even go full circle and analyse it with Answers :-)

Final thought is that exposing the data this way is very helpful for Systems Management, as you now have the option of using MOM/SCOM, etc to monitor and alert on your BI servers.

I would imagine some or all of the above functionality is also available through the BI Management Pack for Enterprise Manager, but this is another way to skin the cat.

Metalink 3 - Do You Mean ... ?

One of my little gripes with Metalink is its purporting to be helpful when it's blatantly not. Here's one:
Now which is more likely, on Metalink 3; that I'm searching for sawserver (integral component to OBIEE), or sqlserver?!

sawserver logging configuration - logconfig.xml

The configuration of how Presentation Services (sawserver) does its logging is in the file web/config/logconfig.xml (same directory as instanceconfig.xml).

It's all nice and XML'd:

Logging Detail
Change the numerical values in the FilterRecord entries to alter the detail level of the logging. Lower means less detail, higher means more.

Be aware that your log files can grow very rapidly if you set the logging too high, and unless you're troubleshooting then leave them at the defaults.

Logging Configuration
You can change various things like how many log files are written, to what size, and also the format of the log entries:
This configuration is in the Writer definition, in the case of format set fmtName="short"
Being able to write a single-line entry is very useful in the case of monitoring software (eg. OpenView) which can't parse multiple line log entries.

sawserver.out.log and saw[x].log
sawserver.out.log is the stdout logging from presentation services. In Unix this is captured to sawserver.out.log, whereas in Windows I don't think it's captured.
saw[x].log is the file logging from presentation services

The level of information for both files is defined in logconfig.xml:

<filterrecord writerclassgroup="Cout" path = "saw" information="31" warning="41" error="41" security="41">
<filterrecord writerclassgroup="File" path = "saw" information="31" warning="100" error="100" security="41">

So by default you'll more detail in your saw[x].log (writerClassGroup="File") than sawserver.out.log (the redirected stdout, writerClassGroup="Cout"). In sawserver.out.log you'll get the same Information and Security type messages as saw[x].log, but fewer (only those of greater severity) Warning and Error type messages.

More detail
See Presentation Services Administration Guide "Using the Oracle BI Presentation Services Logging Facility" for more very detailed information.

psservice - Windows command line goodness!

Our main servers are Unix and I'm as happy as a pig in muck at the command line, so when I'm working on Windows (where I've got a test OBIEE install) I like to stick with the CLI where possible.

PSService is one of those tools that I instinctively reach for without realising it. Combined with Launchy, it's even better.

Simply put, you can control windows services from the command line.

So to restart Presentation Services, instead of

Start -> Settings -> Control Panel -> Administrative Tools -> Services, scroll down, find service, right click, select restart,

I do

alt-space -> cmd [tab] psservice restart sawsvc

You can query service statuses, and for multiple services at a time:

C:\Windows\System32>psservice query "Oracle BI"
PsService v2.21 - Service information and configuration utility
Copyright (C) 2001-2006 Mark Russinovich
Sysinternals - www.sysinternals.com

SERVICE_NAME: Oracle BI Cluster Controller
DISPLAY_NAME: Oracle BI Cluster Controller
(null)
TYPE              : 10 WIN32_OWN_PROCESS
STATE             : 1  STOPPED
                    (NOT_STOPPABLE,NOT_PAUSABLE,IGNORES_SHUTDOWN)
WIN32_EXIT_CODE   : 1077 (0x435)
SERVICE_EXIT_CODE : 0  (0x0)
CHECKPOINT        : 0x0
WAIT_HINT         : 0x0

SERVICE_NAME: Oracle BI Scheduler
DISPLAY_NAME: Oracle BI Scheduler
(null)
TYPE              : 10 WIN32_OWN_PROCESS
STATE             : 1  STOPPED
                    (NOT_STOPPABLE,NOT_PAUSABLE,IGNORES_SHUTDOWN)
WIN32_EXIT_CODE   : 1077 (0x435)
SERVICE_EXIT_CODE : 0  (0x0)
CHECKPOINT        : 0x0
WAIT_HINT         : 0x0

SERVICE_NAME: Oracle BI Server
DISPLAY_NAME: Oracle BI Server
(null)
TYPE              : 10 WIN32_OWN_PROCESS
STATE             : 4  RUNNING
                    (STOPPABLE,NOT_PAUSABLE,ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE   : 0  (0x0)
SERVICE_EXIT_CODE : 0  (0x0)
CHECKPOINT        : 0x0
WAIT_HINT         : 0x0

SERVICE_NAME: sawjavahostsvc
DISPLAY_NAME: Oracle BI Java Host
(null)
TYPE              : 10 WIN32_OWN_PROCESS
STATE             : 4  RUNNING
                    (STOPPABLE,NOT_PAUSABLE,ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE   : 0  (0x0)
SERVICE_EXIT_CODE : 0  (0x0)
CHECKPOINT        : 0x0
WAIT_HINT         : 0x0

SERVICE_NAME: sawsvc
DISPLAY_NAME: Oracle BI Presentation Server
(null)
TYPE              : 10 WIN32_OWN_PROCESS
STATE             : 4  RUNNING
                    (STOPPABLE,NOT_PAUSABLE,ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE   : 0  (0x0)
SERVICE_EXIT_CODE : 0  (0x0)
CHECKPOINT        : 0x0
WAIT_HINT         : 0x0

PSService can be run remotely too, so you could use it for remote administration of your servers.

You can get full syntax from psservice -help

Services can be referenced by either their name (sawsvc) or display name (Oracle BI Presentation Server)

BTW if you like this kind of thing and work with Windows rather than Unix, check out Microsoft's PowerShell - it's one of the few things I've missed since moving back to working primarily with Unix! :-D (I don't mean that in a Microsoft-bashing way, just that it's really satisfying to work with)

[Edit]
You can also use the builtin net command for controlling services, but it's not as functional as psservice
[/edit]

sawserver charts crash

By a strange co-incidence after following this thread on OTN forums about a BI crash and struggling to understand the actual problem, I think I've encountered it myself!

I've got a test install of OBIEE running on my Windows XP laptop, and whilst building a report in Answers got this:

The "Error signature" (brings to mind fountain pens and blotting pads) was:

szAppName : sawserver.exe szAppVer : 10.1.3.4 szModName : kernel32.dll
szModVer : 5.1.2600.3119 offset : 000097a3

Going to the sawserver log at c:\OracleBIData\web\log\sawlog0.log disappointingly showed no error entries :(

A suggestion here of a bug but it relates to pivot tables, which I wasn't doing.

[update]
I've been able to reproduce this error. It's on a report with a table and chart. It works fine with the original dataset of 97 rows returned, but when I remove a filter and 22,000 rows are returned sawserver barfs when rendering it.

I've increased the logging level in sawserver, but still can't see anything helpful in the log.

It's definitely something to do with the chart, because I've now whittled the report down to just the chart and the large dataset query, and it crashes PS every time it's run.

I've upped the javahost logging (see [OracleBI]\web\javahost\config\logconfig.txt), nothing to be seen there. I dug around in CORDA, the chart generation software, but failed to get any logs out.

The only bit of firm diagnostic is that when a chart is successfully rendered there are two files created in C:\WINDOWS\TEMP:
Charts_150001_63825.javahost.in
Charts_150001_63825.javahost.out
(where 150001_63825 varies). The .in file is XML, I think as sent to Corda for rendering. The .out I binary and I assume is the flash file.
When I run my dodgy report, neither of these two files gets created. For successful charts the javahost log shows:

23-Jul-2009 23:30:33 ChartRpcCall processMessage
INFO: Saved request to C:\WINDOWS\TEMP\Charts_150001_63825.javahost.in. Saving response to C:\WINDOWS\TEMP\Charts_150001_63825.javahost.out

In the javahost log is a WARNING: Unexpected exception. Connection will be closed message, presumably from sawserver barfing out and dropping the connection.

So if the sawserver "sends" graphs to corda via javahost, and javahost isn't logging the request -and it's sawserver process that's crashing- the fault's presumably somewhere in sawserver, my guess is when it tries to render a chart with too many data points?
[/update]

Mark Rittman's OBIEE repository for DAC

Mark Rittman has an excellent article about querying the DAC repository database tables, including a downloadable RPD file. Being new to working with RPDs I thought it would be good practise to explore this as well as hopefully get some useful information about our current ETL deployment.

I downloaded the RPD to c:\OracleBI\server\Repository and opened it up in the Admin tool (Administrator/Administrator).
First off I changed the connection pool to point to my DAC repository database, having setup a TNS entry for it first.

Then I had to rename the physical schema from DAC to DAC_REPO, and moved S_ETL_DAY from S_NQ_ACCT to DAC_REPO -- both of these are just how our DBs laid out, YMMV

To test the connectivity I did Update Row Count over one of the tables, and got

There was an error while updating row count for "ORCL".."DAC_REPO"."S_ETL_DAY":
[nQSError: 17001] Oracle Error code:942, message: ORA-00942: table or view does not exist ...

I'd already checked where the tables did reside through SQL Developer:
Since my connection was defined with a user other than the schema owner (DAC_REPO), I figured it was maybe not qualifying the table name, so found this in the connection pool settings:
I could then update the row counts, with the following exceptions which are columns defined in the RPD but not present in my DAC repository schema (v7.9.5.1):
W_ETL_FOLDER.PRIORITY, W_ETL_RUN_STEP.GROUP_NAME and W_ETL_RUN_STEP.RUN_MODE

I deleted these from the Physical layer, hoping that it would warn me if they're used in the BMM or Presentation layer. It didn't - but running a Global Consistency Check warned me that Run Mode is used in the Task Runs logical table, so I deleted it from there too.

I saved the RPD and change my NQServer.config to point to it:
Starting up BI Server I got an error:

2009-07-23 11:47:45 [nQSError: 26011] File C:\OracleBI\server\Config\NQSConfig.INI Near Line 30, near : Syntax error [nQSError: 26012] .

So I guess it's not happy with the spaces in the filename. I changed it to

Star = "DAC Analysis.rpd", DEFAULT;

and it starts up fine. I then got an error trying to log into Answers:

Error connecting to the Oracle BI Server: The specified ODBC DSN is referencing a subject area that no longer exists within the Oracle BI Repository.

State: 08004. Code: 10018. [NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused. [nQSError: 43037] Invalid catalog, Sample Sales, specified. (08004)

(I like the fact that Presentation Services parses the ODBC error into something more intelligable. I'd have eventually figured it out from the ODBC error, but being told up front what the problem is doesn't happen enough with computer errors:) )

This happened because in my system DSN (pointed to in the Presentation Services instanceconfig.xml) I had checked the "Change default catalog to" box, and it was trying to find SampleSales in the repository when it didn't exist.
Unticking this box finally let me log in

On a specific point, the "# Succesful" and "# Failed" measures in Task Runs refer to the number of rows, not number of tasks as it could be interpreted.


The RPD is described by Mark Rittman as:

[...] no means finished or error tested, but if you want to see how I put the example together, feel free to download it and see how it’s put together.[...]

and this is a fair description of it. It's a great starting point which has done a lot of the hard work, and it is very useful as a head-start for understanding the DAC repository tables.
However, it would be wrong to think of it as an out-the-box solution for super-fancy reporting against the DAC. Realistically you still need to understand the tables and data that you're analysing otherwise you'll come up with misleading or plain wrong reports.
But that said, if you have a DAC deployment that you maybe want to do some serious performance work with and want a way to visualise what's going on in your batch, this is a great starting place.

Oracle BI Management / Systems Management MBeans

Part of looking at the various gubbins inside OBIEE led me to realise that the Oracle BI Management application drives quite a few things. It exposes MBeans (Management Beans, a java term), accessible through jmx.
In the installation of OBIEE this component is referred to as "Systems Management".

The MBeans give us real-time performance information, along with access to all the configuration options that are normally done through config files (instanceconfig.xml etc).
Bear in mind if using it for updating configuration instead of through the files you don't get any backup created, so for that reason alone I would suggest it should only be used for reading current values.


They can be accessed directly through jconsole, a java GUI distributed with java jdks, or oc4j (Oracle Containers For Java) which you'll either be running directly or as part of OAS.

@lex has details here about accessing through oc4j/OAS.
It's worth noting if you have done separate BI Server and Presentation Services installations then on your PS server you'll already have an application server running, but on your BI Server you might need to start oc4j (on unix: obiee/oc4j_bi/bin $nohup oc4j -start &)

The MBeans are used as a source for both perfmon and the BI Management Pack in EM.

OTN forum rant

I read and post a bit on the OBIEE and OBIA OTN forums. The noise ratio isn't too bad, but a few things really get my goat:

1. Not responding to answers!
   If I've gone out of my way to help, or try to help, at least have the courtesy to acknowledge it, and ideally mark as Helpful or Correct as appropriate.
   Even a simple "thanks." would do. It's just good manners.
   It also helps people who come along afterwards as you get the completed picture rather than half a question/answer thread.
   Forums Etiquette
   
   
2. Questions like "how do i install obiee will it work on my amiga". R.T.F.M. has never been more appropriate!
   

I'm sure there are more, but that's my steam vented for the moment :-)

And on a lighter note: Top 10 funniest OBIEE topics

JConsole / JMX - followup

A few points to add to my previous posting on JConsole:

• As well as performance data, you have access to configuration data. Be aware that it is read-write! So whilst it might be a nice alternative to digging around for your instanceconfig.xml etc, you should be careful
• If you have your BI Server and Presentation Services deployed on separate servers then you will only get MBeans for the relevant service:

• If you want to view the values of the BI Server MBeans and your Presentation Services server is not on the same box then you have to use JConsole/JMX, as Performance Monitor will not have access to the values:

[Edit]
An alternative to jconsole is to access the Oracle BI Management MBeans through oc4j. @lex has details here
[/edit]

OBIEE admin tools & hacks

As a kid I loved the idea of lego where you can disassemble and reassemble something from the ground up. As soon as I got my hands on a computer it was the same. You can have your Acorn Archimedes with its games, where do I find the sprites and sound files behind it? Likewise Microsoft Word, let me at the VBA underneath to hack it around and see what else it can do.

With that in mind I've enjoyed discovering bits of the "underbelly" of OBIEE from manuals, blogs and a few SRs I've raised.

Here's my list so far, please feel free to add to it in the comments and correct any errors or missing credits :)

Oracle BI Management data
This server-based performance and diagnostic data can be access through at least two methods:

perfmon
Credit: http://obiee101.blogspot.com/2009/07/obiee-perfmon-performance-monitor.html

Go to http://[server]:[port]/analytics/saw.dll?perfmon and you'll get the Performance Monitor. This is the same data that's used by the BI Management Pack and is accessible through the jmx agent

oc4j MBeans
An alternative to jconsole is to access the Oracle BI Management MBeans through oc4j. @lex has details here

Windows perfmon
More details in separate post here

jmxagent / jconsole
The same data as through perfmon above, full details here: jmx agent
sawping
Credit: http://tipsonobiee.blogspot.com/2009/07/sawping.html

C:\OracleBI\web\bin>sawping.exe -help
sawping [-p port] [-s host] [-v (verbose mode)] [-q (quiet mode)] [-h]

C:\OracleBI\web\bin>sawping.exe -s myPSserver.company.net -v
Server alive and well

NB not got this working yet on unix:

obiee/web/bin64 $./sawping64
/usr/lib/hpux64/dld.so: Unable to find library 'libsawcomm643r.so'.

Found in [OracleBI Home]/web/bin (or Bin64)

nqcmd
Great little command line tool. We're using it for some crude load testing (get a bunch of logical-SQL statements and use unix scripting to fire them in parallel at the BI Server) as well as monitoring of the BI Server ("pinging" it regularly with some small logical-SQL to make sure it responds correctly)
Found in [OracleBI Home]/server/Bin (or Bin64)

UnixChk
(UNIX only, duh)
What it says on the tin - validates your Unix environment is suitable for OBIEE

Usage: UnixChk.sh [-b] [-s | ]

diagcap
Support asked me to run this, you'll find it in [OBIEE home]/server/Bin

Usage: ./diagcap.sh -d [-p] [-h]
-d : directory that info will be stored
The directory must be empty
-p : absolute path to Oracle BI root directory
May be omitted if the SAROOTDIR
environment variable is set
-h : show this help message

It collects all the config files and log files from your environment into a TAR archive, which you can then send to support.

tusc
Not OBIEE as such, but interesting for the truly nosy. This is a unix (HP) tool which you use to invoke a program and then get low-level diagnostics on what it's up to. Most of the output may be gobbledegook to all but the hardcore programmer, but it does sometimes pick out if a library file is missing etc.
For example to invoke nqqserver in run-sa.sh:

tusc -vfe ${ANA_BIN_DIR}/nqsserver 2>&1 &

Administration Tool
Maybe not so unknown, but has front-end for monitoring clusters which I liked

Other stuff
This is a list of binaries that might or might not be of interest and that I plan to have a play with at some point

NB obiee/setup/sa-init64.sh and common.sh scripts might need running first to set the environment variables correctly.

obiee/web/bin

• cryptotools - used for generating keys for authentication between obiee/delivers/xmlp
• sawmigrate - for migrating from older versions of OBIEE?

obiee/server/Bin

• equalizerpds - For equalising RPDs?! Reference
• ErrorMsgCheck (directory) - not sure
  
• nqerrormsgcompiler - not sure
  

$nqerrormsgcompiler
ErrorMessageCompiler [

] []
Exiting

• nqlogviewer - For parsing BI server logs. Can be useful for clustered instances?
  
• nqschangepassword - For changing of RPD user passwords
  
• nqscripthostexec - not sure
  

$nqscripthostexec
Usage: nqscripthostexec Memory fault(coredump)

• nqsshutdown - To shut down a BI Server via DSN reference (see here)
  
• nqsudmlcli - something to do with UDML?
  
• nqudmlexec - executing UDML against the RPD?
• nqudmlgen - generating UDML to run against the RPD?
• openssl - OpenSSL command line
  
• sametaexport - Oracle Database Metadata Generator. See Oracle Business Intelligence Server Administration Guide and Venkat's blog
  
• saschinvoke - invoke Delivers jobs & iBots (see here)
  
• schconfig - Scheduler [Delivers] configuration program
• schshutdown - Shutdown Scheduler remotely?
  

In Googling these various binaries I found an excellent PDF from Andreas Nobbmann's presentation in Brighton earlier this year: Scripting OBIEE - Is UDML and XML all you need?

Another useful page is here: http://gerardnico.com/wiki/dat/obiee/executable_files/start

JConsole / JMX

[edit] See this post too [/edit]
On an OBIEE server run

nohup obiee/systemsmanagement/runagent.sh &

and then run jconsole (make sure you've set the DISPLAY first if you're running it from UNIX).
NB: if you don't have jconsole in your path you can search for it:

$whereis jconsole
jconsole: /opt/java1.5/bin/jconsole /opt/java6/bin/jconsole

You should find it under your java/bin directory

You should get this kind of connection dialog:

Click connect, and the console will launch. From here click on the MBeans tab, where you've got access to performance and configuration data


You can connect to the agent remotely too, but to do this you need to amend runagent.sh script:

On the java_cmd line replace
-Dcom.sun.management.jmxremote
with
-Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=9980 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false

See here for more information on configuring jmx



The BI Management Pack uses the agent for collecting its data. The data also looks like it's accessible at http://[server]:[port]/analytics/saw.dll?perfmon (hat-tip: John Minkjan)

nqcmd and [nQSError: 27005] Unresolved column

I'm working on a scripted load test for OBIEE using nqcmd to run reports multiple times. I hit this interesting issue.

Cut and pasting the logical SQL that Presentation Services sends to BI Server from Manage Sessions -> Statement, I kept getting this error when I ran it through nqcmd:

[10058][State: S1000] [NQODBC] [SQL_STATE: S1000] [nQSError: 10058] A general error has occurred.
[nQSError: 27005] Unresolved column: "Natural Account (COA)"."Account Parent1 Code".
Statement preparation failed

Eventually I tracked it down after looking at the logical SQL behind the report in Answers itself and comparing it to what I'd copied out of the Session statement:

SELECT "Natural Account (COA)"."Account Parent1 Code" saw_0, "Natural Account (COA)"."Account Parent5 Code"  [...]
SELECT "Natural Account  (COA)"."Account Parent1 Code" saw_0, "Natural Account  (COA)"."Account Parent5 Cod [...]



The "Natual Account (COA)" prefix had two spaces in it, but because I was copying it out of a web browser where multiple spaces are rendered as single unless escaped, the supposedly verbatim copy wasn't such.

More details of the load testing script to follow...

Custom HTTP error page in OBIEE / OAS

It's possible to change the error pages served up by OAS/Apache by using the ErrorDocument directive. This is widely documented.

However, to get this to take effect in an oc4j application (such as analytics) you need to change mod_oc4j.conf too.
(I found this out from this post here)

Take backups of httpd.conf and mod_oc4j.conf, and then edit them as follows:

In httpd.conf add:
ErrorDocument 500 /500.html
where /500.html is a relative path to your custom document

In mod_oc4j.conf add to the end of the file:
Oc4jUseOHSErrors on

This will make any HTTP 500 (Internal Server error) errors show the page 500.html, instead of the default Apache one.

Don't forget to bounce OHS after making this change:
opmnctl restartproc ias-component=HTTP_Server

New releases

New releases this week - 10.1.3.4.1 of OBIEE, and 7.9.6 of OBIA.

After a bit of scrabbling around found:

"The 10.1.3.4.1 release of the Oracle Business Intelligence Enterprise Edition introduces no new features."

Though there are some new bits and pieces for Publisher

For 7.9.6 OBIA, there's no New Features document :(
By piecing together the "What's New in This Release" for each document you can build up a picture (eg in the Upgrade Guide there's reference to changes for the doc relating to a new version of Informatica), but it would be nice to have it all in one place:

• Informatica PowerCenter version 8.6 HF 4
  
• DAC changes?
• Updated BAW schema
• Importantly for our site, support for BAW on Oracle 11g
  

for more info see a posting here

OBIEE and F5 BIG-IP

We've got a setup of two OAS/Presentation Services boxes and two BI Server boxes, with load balancing/failover throughout.
The Load Balancing of the web requests is being done through a separate bit of kit, an F5 BIG-IP load balancer. This directs the requests at the two OAS servers.

The problem we have is that by default OAS serves HTTP on port 7777, but the F5 is using port 80. A request for our load balanced URL: http://bi.mycompany.com/analytics/ barfs out with

Internet Explorer cannot display the webpage

Most likely causes:
-You are not connected to the Internet.
-The website is encountering problems.
-There might be a typing error in the address.

or in FireFox:

Failed to Connect The connection was refused when attempting to contact bi.mycompany.com:7777. Though the site seems valid, the browser was unable to establish a connection.

Using the excellent HttpFox add-in for Firefox I could see the HTTP requests/responses:

1. http://bi.mycompany.com/analytics/ goes via the loadbalancer on the default HTTP port 80 to OAS
2. OAS responds with HTTP/1.1 302 Moved Temporarily to http://bi.mycompany.com:7777/analytics/saw.dll?Dashboard
3. The web client requests this URL (http://bi.mycompany.com:7777/analytics/saw.dll?Dashboard) from the load balancer but because it's port 7777 F5 rejects the request (NS_ERROR_CONNECTION_REFUSED)
   

We could also just use the direct URL http://bi.mycompany.com/analytics/saw.dll?Dashboard but this is hardly user friendly (and also means that if they typo when entering it they'll get an unhelpful error as above)

Looking at the httpd.conf for Apache to find the port config made me think of the UseCanonicalName setting which I also encountered recently. This setting is to do with how Apache deals with the server name in the URL being requested and the hostname of the server configured in Apache.
When I got the behaviour described above UseCanonicalName was set to Off, which I think means Apache does not rewrite the URL at all, so the redirect was to http://bi.mycompany.com:7777/analytics/saw.dll?Dashboard which is the F5 Load Balancer address.
If I changed UseCanonicalName to On then the F5 load balancing starts to work, as this happens instead:

1. http://bi.mycompany.com/analytics/ goes via the loadbalancer on the default HTTP port 80 to OAS
2. OAS responds with HTTP/1.1 302 Moved Temporarily to http://oasserver_1.mycompany.com:7777/analytics/saw.dll?Dashboard

i.e. the request goes directly to one of the load balanced servers, and correctly on port 7777.
The disadvantage of this is that the URL used by the web client then becomes http://oasserver_1.mycompany.com which means the user is no longer hitting the load balancer so any failover wouldn't get picked up. It also means that users might start bookmarking OAS servers directly instead of the load balancer, again meaning that they don't hit the load balancer so a server failover wouldn't get picked up.


Eventually I got this resolved, with a bit of help from a very helpful chap at Oracle. By changing the httpd.conf to set Port 80, when Apache rewrites URLs it now uses Port 80.
Listen remains as 7777.
Traffic from web client now hits the LB on port 80, which forwards to 7777 on one of the OAS servers, which if necessary rewrite the URL and use port 80 in the rewrite.
Because Listen remains as 7777 there is no need to run Apache as root.
You can also set ServerName to the load balancer address (bi.mycompany.com) and UseCanonicalName to On. If you do this then I don't think it's possible to access web pages on a specific OAS server (eg oasserver_1) because entering http://oasserver_1.mycompany.com:7777/analytics just redirects to bi.mycompany.com/analytics.

Ref: Deploying F5 with Oracle Application Server 10g
Ref: Oracle HTTP Server - Port setting
Ref: Metalink 301755.1 - What Is the Difference Between Port & Listen In Httpd.Conf

OAS makes you log in twice

A very minor irritation, but an irritation nonetheless, is when I go to Application Server Control in OAS I have to login twice.

Reading around I found this is an Apache feature, and is actually designed behaviour.

For reasons I've not explored our servers have several different hostnames which resolve to the same IP, e.g.:
myserver
myserver-app
myserver-data

When you request a page from Apache using a hostname other than that configured as ServerName in Apache's httpd.conf, it redirects you to the version of the page using the ServerName.

If I go to http://myserver.company.fqdn.net:7777/em/ I get the login page as expected. Having typed the password I get sent to the login page again - but this time at http://myserver-app.company.fqdn.net:7777/em/, and this ties in with httpd.conf in which ServerName is myserver-app.

Per the Apache FAQ this can be resolved in several ways. ServerName can either be altered (not sure if this would impact other functions), or change UseCanonicalName from On to Off. Obviously you could also just login at a URL which corresponded with the ServerName in httpd.conf :-)

Ref: Apache UseCanonicalName documentation

Google and Korean OTN forums

Why does Google more often than not return the korean (kr) version of OTN forums for a match, but not english?

If I search Google for "Send notification:" "oracle.ons.Notification" the top hit is for kr.forums.oracle.com/forums/thread.jspa?threadID=650662

If you strip the kr from the URL you get this, which makes more sense given the locality when doing the search in Google.

Only mildly irritating, but odd behaviour nonetheless

OAS bug 7132128 - Send notification: oracle.ons.Notification

I noticed that the j2ee server.log file was filling up with these entries:

oracle.ons.Notification@afba5d
09/04/02 10:15:12.207 Send notification:
oracle.ons.Notification@17ca0f5
09/04/02 10:15:42.217 Send notification:
oracle.ons.Notification@1a28842
09/04/02 10:16:12.227 Send notification:
oracle.ons.Notification@5144d5
09/04/02 10:16:42.237 Send notification:
oracle.ons.Notification@19078ed
09/04/02 10:17:12.247 Send notification:
oracle.ons.Notification@fcc268
09/04/02 10:17:42.257 Send notification:
oracle.ons.Notification@16df388

A quick google turned up this page in which bug 7132128 ("OC4J EMITS "SEND NOTIFICATION" MESSAGES THAT FILL SERVER.LOG") is identified.

The server.log is currently only 6MB and given the size of the server it will be a while before it causes us problems, but it's worth being aware of.

sawserver won't start up - resolved

(See here and here for history)

I edited the shell script which is eventually called by run-saw.sh to start the sawserver, (OracleBI)/setup/sawserver.sh, to use trus:
Comment out the final line:

$SASAWSERVER

and insert as a new line:

tusc -fepan -o /tmp/sawserver_tusc.out $SASAWSERVER

The output of trus ended with this:

open("/app/oracle/product/10.2.0/lib/libstd_v2.so.1", O_RDONLY|0x800, 0) ......... ERR#2 ENOENT
open("/app/oracle/product/obiee/server/Bin64/libstd_v2.so.1", O_RDONLY|0x800, 0) . ERR#2 ENOENT
open("/app/oracle/product/obiee/web/bin64/libstd_v2.so.1", O_RDONLY|0x800, 0) .... ERR#2 ENOENT
open("/app/oracle/product/obiee/odbc/lib64/libstd_v2.so.1", O_RDONLY|0x800, 0) ... ERR#2 ENOENT
open("/usr/lib/libstd_v2.so.1", O_RDONLY|0x800, 0) ............................... ERR#2 ENOENT
open("/lib/libstd_v2.so.1", O_RDONLY|0x800, 0) ................................... ERR#2 ENOENT
open("/app/oracle/product/10.2.0/lib/libstd_v2.so.1", O_RDONLY|0x800, 0) ......... ERR#2 ENOENT
open("/opt/aCC/lib/hpux64/libstd_v2.so.1", O_RDONLY|0x800, 0) .................... ERR#2 ENOENT

which showed sawserver failing to find libstd_v2.so.1.
The helpful chap on my SR at Oracle said this should be in /usr/lib/hpux64 (which it is) and so to add this to the SHLIB_PATH variable:

export SHLIB_PATH=/usr/lib/hpux64

If SHLIB_PATH already has a value then use the syntax ${SHLIB_PATH}:/usr/lib/hpux64 or use this anyway and set +u to stop the shell barfing on the unknown variable

Having updated SHLIB_PATH sawserver now starts up fine on this server now! w00t!

But -- why did this just start happening now?? The same servers with the same obiee installation and same obiee configuration worked without the SHLIB_PATH being set last week, so why do they need it now?

Troubleshooting an HPUX program

In investigating the problems with sawserver I was pointed towards a tool called tusc (which appears to be an HP version of truss).

You can use it to invoke a program, and get out a bunch of debug information including system calls.

You run it like this:

$tusc -fep /app/oracle/product/obiee/web/bin64/sawserver64

As a beginner when it comes to hardcore *nix I can only look at this and take pot shots at what's going on, but with Google by my side I'm interested in the last lines of the output:

open("/app/oracle/product/10.2.0/lib/libstd_v2.so.1", O_RDONLY|0x800, 0) ......... ERR#2 ENOENT
open("/app/oracle/product/obiee/server/Bin64/libstd_v2.so.1", O_RDONLY|0x800, 0) . ERR#2 ENOENT
open("/app/oracle/product/obiee/web/bin64/libstd_v2.so.1", O_RDONLY|0x800, 0) .... ERR#2 ENOENT
open("/app/oracle/product/obiee/odbc/lib64/libstd_v2.so.1", O_RDONLY|0x800, 0) ... ERR#2 ENOENT
open("/usr/lib/libstd_v2.so.1", O_RDONLY|0x800, 0) ............................... ERR#2 ENOENT
open("/lib/libstd_v2.so.1", O_RDONLY|0x800, 0) ................................... ERR#2 ENOENT
open("/app/oracle/product/10.2.0/lib/libstd_v2.so.1", O_RDONLY|0x800, 0) ......... ERR#2 ENOENT
open("/opt/aCC/lib/hpux64/libstd_v2.so.1", O_RDONLY|0x800, 0) .................... ERR#2 ENOENT

This is the last output before the process hangs, and ENOENT is a file or directory not found error. It looks like it's scanning different folders for the file

This makes me think there's something up with the path/load library environment variables (but why on all our servers and only now??).

sawserver won't start (analytics: Servlet error java.net.ConnectException: Connection refused (errno:239))

We're getting this error in the Presentation Services plug-in [analytics].
Log file: /j2ee/home/application-deployments/analytics/home_default_group_1/application.log

09/03/30 13:16:38.75 analytics: Servlet error
java.net.ConnectException: Connection refused (errno:239)
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
at java.net.Socket.connect(Socket.java:517)
at java.net.Socket.connect(Socket.java:467)
at java.net.Socket.(Socket.java:364)
at java.net.Socket.(Socket.java:178)
at com.siebel.analytics.web.sawconnect.ConnectionPoolSocketFactoryImpl.createSocket(ConnectionPoolSocketFactoryImpl.java:63)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.siebel.analytics.web.sawconnect.ConnectionPoolSocketFactoryImpl.createSocket(ConnectionPoolSocketFactoryImpl.java:70)
at com.siebel.analytics.web.sawconnect.ConnectionPool.createNewConnection(ConnectionPool.java:314)
at com.siebel.analytics.web.sawconnect.ConnectionPool.getConnection(ConnectionPool.java:133)
at com.siebel.analytics.web.SAWBridge.processRequest(SAWBridge.java:299)
at com.siebel.analytics.web.SAWBridge.doGet(SAWBridge.java:325)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.3.0) ].server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.3.0) ].server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:3
70)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.3.0) ].server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.3.0) ].server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.3.0) ].server.http.AJPRequestHandler.run(AJPRequestHandler.java:302)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.3.0) ].server.http.AJPRequestHandler.run(AJPRequestHandler.java:190)
at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:239)
at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:34)
at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:880)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.3.0) ].util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.
java:303)
at java.lang.Thread.run(Thread.java:595)

The relevant bit of the stacktrace looks like "com.siebel.analytics.web.sawconnect.ConnectionPoolSocketFactoryImpl.createSocket", i.e. it's trying to connect to SAW.

I checked if Presentation Services is running:

ps -ef|grep saw

which it was. However, looking in the /web/log folder the sawserver.out.log file is zero bytes!

I stopped all services including OAS and restarted them:
- OAS comes up fine
- Javahost starts fine
- Presentation services process starts but no log file generated
When I try to connect to Analytics I get 500 Server Error and "analytics: Servlet error
java.net.ConnectException: Connection refused (errno:239)" error logged in the analytics Presentation Services plug in log file

Looking at this logically, sawserver is the problem. It's not starting up - there's no log and the port 9710 doesn't get opened up by it.
The strangest thing at this point is that there is no log - there's normally be at least a "... starting up" type entry, even if nothing else.
Even after increasing the log levels (/web/config/logconfig.xml) to minute levels (100 for each), there is still nothing logged.

On a dev box on which nothing's changed recently (and which sawserver was running without complaint) I did

run-saw.sh stop

(...wait for a while...)

run-saw.sh start64

and sawserver didn't come up! This to me points the finger towards the server

This has now gone to Oracle as an SR, as something is clearly up with sawserver :-(

Update: running gpm (glance for x-windows) I found this:
sawserver64 being reported as "Blocked On" "Other" for 100% of the time.
Not sure what that translates to in real money yet though.

Update: solution here!

Bug in Clustered Publisher Scheduler - ClusterManager: detected 1 failed or restarted instances

Follow on from setting up Publisher in a clustered environment, I've found a nasty little bug in the scheduling element of Publisher, Quartz.

Looking at the oc4j log file /opmn/logs/default_group~home~default_group~1.log I can see OC4J starting up, and then a whole load of repeated messages:

09/03/30 11:28:43 Oracle Containers for J2EE 10g (10.1.3.3.0) initialized
- ClusterManager: detected 1 failed or restarted instances.
- ClusterManager: Scanning for instance "myserver.fqdn.company.net1238408921404"'s failed in-progress jobs.
- ClusterManager: detected 1 failed or restarted instances.
- ClusterManager: Scanning for instance "myserver.fqdn.company.net1238408921404"'s failed in-progress jobs.
- ClusterManager: detected 1 failed or restarted instances.
- ClusterManager: Scanning for instance "myserver.fqdn.company.net1238408921404"'s failed in-progress jobs.
- ClusterManager: detected 1 failed or restarted instances.
- ClusterManager: Scanning for instance "myserver.fqdn.company.net1238408921404"'s failed in-progress jobs.
- ClusterManager: detected 1 failed or restarted instances.
- ClusterManager: Scanning for instance "myserver.fqdn.company.net1238408921404"'s failed in-progress jobs.
- ClusterManager: detected 1 failed or restarted instances.
- ClusterManager: Scanning for instance "myserver.fqdn.company.net1238408921404"'s failed in-progress jobs.
- ClusterManager: detected 1 failed or restarted instances.
- ClusterManager: Scanning for instance "myserver.fqdn.company.net1238408921404"'s failed in-progress jobs.
- ClusterManager: detected 1 failed or restarted instances.
- ClusterManager: Scanning for instance "myserver.fqdn.company.net1238408921404"'s failed in-progress jobs.
[... repeated for 38MB worth ]

Metalink to the rescue .... a search for "Search: ClusterManager: Scanning for instance" throws up doc 739623.1 - Repeated Error Appears In Log File - ClusterManager: detected 1 failed or restarted instances which details the problem and references bug # 7264646.

This is a bug in Quartz (the Publisher scheduling tool), which has been fixed in 1.5.2 (the version that's included with Publisher is 1.5.1).

On my installation quartz was located in /j2ee/home/applications/xmlpserver/xmlpserver/WEB-INF/lib

Implenting the fix described on Metalink doc 739623.1 solved the problem.

ODI Server install - missing odiparams.sh file

I'm installing ODI agent on our database server using OUI. I selected the "Server" option at install time to get the Agent only, but looking in oracledi/bin odiparams.sh is missing:

$ls -l *.sh
-rwxrwxrwx   1 odiadm     dba            685 Nov 21 15:58 agent.sh
-rwxrwxrwx   1 odiadm     dba            908 Nov 21 15:58 agentscheduler.sh
-rwxrwxrwx   1 odiadm     dba            707 Nov 21 15:58 agentstop.sh
-rwxrwxrwx   1 odiadm     dba            941 Nov 21 15:58 agentweb.sh
-rwxrwxrwx   1 odiadm     dba            724 Nov 21 15:58 jython.sh

My understanding was that odiparams.sh was necessary, and looking at the code for agent.sh it must be as it includes:

. $ODI_HOME/bin/odiparams.sh

Checking the manual it's possible to install ODI simply by unzipping the installation folder, so I copied the rest of the bin directoy from the original installation.

I then edited the odiparams.sh as required, and the agent started fine.

Remove windows line feed characters in vi

If you work with a file in Windows and Unix at some point you might end up with windows line feed characters in your Unix file. It'll look like this:

one line of text ^M
next line ^M
and next line with more ^M

To remove the ^M character, load the file into vi on unix and enter as a line command the following:

:1,$s/^M//

but instead of typing ^M do Ctrl-V Ctrl-M to get the charaters

Alternatively, load the file in Windows into Notepad++ and use Format -> Convert to UNIX format, then FTP the file back to Unix

ORA-12537 / ORA-12518 [Informatica DAC error CMN_1022]

We're getting problems with an instance of Informatica / out-of-the-box OBIA on a new set of servers. When we run the execution plan we get this error soon after starting:

MAPPING> DBG_21075 Connecting to database [TNSENTRY], user [MYUSER]
MAPPING> CMN_1761 Timestamp Event: [Tue Mar 24 18:56:33 2009]
MAPPING> CMN_1022 Database driver error...
CMN_1022 [
Database driver error...
Function Name : Logon
ORA-12537: TNS:connection closed

Database driver error...
Function Name : Connect
Database Error: Failed to connect to database using user [MYUSER] and connection string [TNSENTRY].]

MAPPING> CMN_1761 Timestamp Event: [Tue Mar 24 18:56:33 2009]
MAPPING> CMN_1076 ERROR creating database connection.

One or two tasks using the DataWarehouse connection succeed, and then the rest fail with the above error.

That one or two tasks succeed proves that the connection string is specified correctly, plus I'd expect to see an auth error if our username/pw was incorrect. We've verified the Physical Data Source in DAC, but stupidly in Informatica (Workflow Manager - Connections - Relational) there's no "test connection".

Both of the errors, Informatica's CMN_1022 and Oracle's ORA-12537, are generic "somat's bust" ones, neither providing a clue to what the problem is.

Metalink 3 has several entries for CMN_1022 but they just point to configuration/installation errors with the database connectivity.

There's a matching article on OTN Forums but without a definitive solution

In DAC Physical Data Sources the Max Num Connections is 10. The OTN forum posting refers to performace so guessing maybe Oracle wasn't happy with the # of concurrent connections I changed it to 1, but the problem remained.

This is on Informatica 8.1.1, Oracle client 10.2.0 and Oracle DB 11.1.0.7.

Our DBA had a look and validated all the connectivity, and also granted the user DBA just to make sure it wasn't a priviledges issue.

I turned on tracing in the sqlclient (add trace_level_client=16 to the sqlnet.ora in $TNS_ADMIN) and got this rather helpful output:

***********************************************************************
Fatal NI connect error 12537, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.company.com)(PORT=1521))(CONNECT_DATA=(SID=TNSENTRY)(SERVER=DEDICATED)(CID=(PROGRAM=pmdtm)(HOST=
apphost)(USER=unixuser))))

VERSION INFORMATION:
TNS for HPUX: Version 10.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for HPUX: Version 10.2.0.1.0 - Production
Time: 25-MAR-2009 11:09:46
Tracing to file: /app/oracle/product/informatica/server/bin/cli_2844.trc
Tns error struct:
ns main err code: 12537
TNS-12537: TNS:connection closed
ns secondary err code: 12560
nt main err code: 507
TNS-00507: Connection closed
nt secondary err code: 0
nt OS err code: 0

and delving into the guts of the .trc file found:

(11) [25-MAR-2009 11:09:46:011] nsprecv: reading from transport...
(11) [25-MAR-2009 11:09:46:011] nttrd: entry
(11) [25-MAR-2009 11:09:46:100] nttrd: exit
(11) [25-MAR-2009 11:09:46:100] ntt2err: entry
(11) [25-MAR-2009 11:09:46:100] ntt2err: Read unexpected EOF ERROR on 38
(11) [25-MAR-2009 11:09:46:100] ntt2err: exit
(11) [25-MAR-2009 11:09:46:100] nsprecv: error exit
(11) [25-MAR-2009 11:09:46:100] nserror: entry
(11) [25-MAR-2009 11:09:46:101] nserror: nsres: id=0, op=68, ns=12537, ns2=12560; nt[0]=507, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0

So maybe it's the DB server that's not playing ball? I'm guessing the "Read unexpected EOF ERROR on 38" might be relevant.

Taking the opportunity to learn a bit more about Oracle connectivity, I had a look at Oracle® Database Net Services Administrator's Guide 10g Release 2 (10.2) - Troubleshooting Oracle Net Services. This details setting up logs and traces, and points to Trace Assistant, trcasst. Running it on one of the trace files from a failed connection reported this:

///////////////////////////////////////////////////////////////
Error found. Error Stack follows for thread #: 11
              id:0
  Operation code:68
      NS Error 1:12537
      NS Error 2:12560
NT Generic Error:507
  Protocol Error:0
        OS Error:0
 NS & NT Errors Translation
12537, 00000 "TNS:connection closed"
 // *Cause: "End of file" condition has been reached; partner has disconnected.
 // *Action: None needed; this is an information message.
/
12560, 00000 "TNS:protocol adapter error"
 // *Cause: A generic protocol adapter error occurred.
 // *Action: Check addresses used for proper protocol specification. Before
 // reporting this error, look at the error stack and check for lower level
 // transport errors.For further details, turn on tracing and reexecute the
 // operation. Turn off tracing when the operation is complete.
/
00507, 00000 "Connection closed"
 // *Cause: Normal "end of file" condition has been reached; partner has
 // disconnected.
 // *Action: None needed; this is an information message.
/
///////////////////////////////////////////////////////////////

which is the same error as I found in the trace file but with each code explained.

We tested different permutations of servers:

Inf server A / 10g client -> DB Server A (11g) -> Fails
Inf server A / 10g client -> DB Server Y (11g) -> Success
Inf server B / 10g client -> DB Server B (11g) -> Success
Inf server A / 10g client -> DB Server Z (10g) -> Success
Inf server C / 11g client -> DB Server C (11g) -> Success
Inf Server C / 11g client -> DB Server A (11g) -> Success

So now we have three identical setups (same informatica/oracle client/oracle DB), two of which work, one fails - when run against Server A.

Our DBA ran a trace on the listener on Server A and picked up this error:

TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
HPUX Error: 32: Broken pipe

which points to a possible OS issue.

Ref: Oracle® Database Installation Guide 11g Release 1 (11.1) for HP-UX - 2.7 Configure Kernel Parameters
Ref: Metalink article 550859.1 - TROUBLESHOOTING GUIDE TNS-12518 TNS listener could not hand off client connection

The UNIX team checked the kernel settings between DB Server A and DB Server Y, but found no differences (in particular they checked maxuprc and nproc).

This problem eventually got resolved after two actions:

1) Database server was restarted
2) Oracle PROCESSES was increased from 200 to 500

We suspect the restart fixed the problem as one of the UNIX guys spotted some "performance funnies" (technical term ;-) ) on the box prior to the restart.

Which jdbc driver to use

In setting the scheduler in Publisher I discovered a useful difference in jdbc drivers.
Our repository is on Oracle 11g.
According to the manual oracle.jdbc.driver.OracleDriver should be used, but previous installations have used oracle.bi.jdbc.AnaJdbcDriver so I tried this too.

In experimenting with both I found you get more useful feedback from the second one. Here's the same problem reported by both drivers:

· Exception [TOPLINK-4002] (Oracle TopLink - 11g Release 1 (11.1.1.0.0) (Build 080319)): oracle.toplink.exceptions.DatabaseException Internal Exception: java.sql.SQLException: ORA-28000: the account is locked Error Code: 28000

· Exception [TOPLINK-4021] (Oracle TopLink - 11g Release 1 (11.1.1.0.0) (Build 080319)): oracle.toplink.exceptions.DatabaseException Exception Description: Unable to acquire a connection from driver [oracle.bi.jdbc.AnaJdbcDriver], user [OBIEE_PUBL_SCHED] and URL [jdbc:oracle:thin:@dbserver.company.com:1521:ORACLESID]. Verify that you have set the expected driver class and URL. Check your login, persistence.xml or sessions.xml resource. The jdbc.driver property should be set to a class that is compatible with your database platform Internal Exception: java.sql.SQLException: ORA-28000: the account is locked Error Code: 28000

As you can see with the highlighting that I've added the second driver gives you the really useful stuff - which ID and server it's trying to connect to.

Obviously I can check what's been configured to trace back which ID and server should be being used - but it's always useful to get confirmation of what it's actually doing just to rule out me having been stupid and typed the wrong options :)