#obihackers

There’s a #obihackers IRC channel on freenode, where a dozen or so of us have hung out for several years now. Chat is usually OBIEE, Oracle, ODI, and general geek out.

Bear in mind this is the equivalent of us hanging out in a bar; if you wanna shoot the shit with a geeky question about OBIEE go ahead, but if you’ve come to get help with your homework without even buying a round, you’ll probably get short shrift… ;-)

InfluxDB is a great time series database, that’s recently been rebranded as part of the “TICK” stack, including data collectors, visualisation, and ETL/Alerting. I’ve yet to really look at the other components, but InfluxDB alone works just great with my favourite visualisation/analysis tool for time series metrics, Grafana.

Getting data into InfluxDB is easy, with many tools supporting the native InfluxDB line input protocol, and those that don’t often supporting the carbon protocol (from Graphite), which InfluxDB also supports (along with others). So for collecting broad ranges of OS stats, for example, collectl via carbon and nmon via nmon2influxdb are both viable options.

Thanks to the power of twitter, I can look back on all the many and varied Full English breakfasts that I’ve (mostly) enjoyed:

👉🏻 https://twitter.com/search?q=rmoff%20%23fullenglish&src=typd

What makes a good Full English?

• Good ingredients, cooked well. Nothing worse than a limp pink sausage, as it were.
• Sausage, standard pork or Cumberland at most. Definitely no daft apricot and guava bean with a hint of foie gras nonsense. Must be cooked right well, crispy skin, almost burnt.
• Bacon, starting to crisp (but not deep fried and crisp-like as the Americans do it - it should bend if you pick it up)
• Black Pudding, seriously. Grim idea when you think about it (blood sausage) but so very tasty, and binds the other components together
• Fried eggs with runny yolks
• Scrambled eggs are making an appearance more recently too, after 30 years of not really caring for them. The dodgy “scrambled egg” that you get in some hotels I think straight from a packet mix are crap though and best avoided.
• Hash browns. Controversial, and often a bit greasy - but I’d never pass one by
• Fried slice - sadly all too often missing. I’ll forego hash browns if there’s a fried slice
• Tomatoes, but only if there’s more tomato than stalk as often happens
• Mushrooms, thicky chopped so there’s something to it - and certainly not the wussy sliced things that you get sitting in a pool of black greasy water at some fryup buffets
• Baked beans
• Good white toast
• HP Sauce - although I’ve realised recently that a really good fryup doesn’t actually need any sauce. But if it’s subpar and/or cheap ingrediants – gotta have HP sauce (and definitely not that awful “brown sauce” that you get in sachets and tastes grim).

It struck me today when I was writing my most recent blog over at Rittman Mead that I’ve been playing with visualising OBIEE metrics for years now.

• Back in 2009 I wrote about using something called JManage to pull metrics out of OBIEE 10g via JMX:

  

• Still with OBIEE 10g in 2011, I was using rrdtool and some horrible-looking tcl hacking to get the metrics out through jmx :

  

• 2014 brought with it DMS and my first forays with Graphite for storing & visualising data:

I will now be blogging mostly over at the venerable blog of my employer, Rittman Mead.

You can see my first posting here: Web Services in BI Publisher 11g.

Don’t entirely exclude this rnm1978 blog from your feeds, as I may still post more esoteric and random tidbits here.

 

---

 

This article has been superseded by a newer version: Instrumenting OBIEE Database Connections For Improved Performance Diagnostics

---

 

(Previously on this blog: 1, 2, 3…)

Summary

Instrument your code. Stop guessing. Make your DBA happy. Make your life as a BI Admin easier.

The Problem

OBIEE will typically connect to the database using a generic application account. (Hopefully, you’ll have isolated it to an account used only for this purpose - if you haven’t, you should.)

It’s not my fault really.

When running an installation, presented with the option of

• (a) do a bunch of stuff and wait to continue the install later or
• (b) tick a box and continue now

it’s a better man that I who would opt for option (a).

When I recently installed OBIEE 11g, I was prompted to get a script run as root to set up the inventory, or tick “Continue Installation with local inventory” to continue with the install.

Short note to record this, as Google drew no hits on it.

Windows XP machine with existing Oracle 11.1 client installation, all working fine.

Installed Oracle 11.2 XE, and started getting these errors: [sourcecode] C:\Windows\System32>tnsping DBNAME

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 26-SEP-2011 11:01:11

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files: C:\app\userid\product\11.1.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias Message 3513 not found; product=NETWORK; facility=TNS OK (20 msec) [/sourcecode]

I noticed in Ed Stevens’ blog posting here that some sourcecode he’d posted had certain lines highlighted.

Wordpress provides the sourcecode tag for marking up sourcecode in blog posts. For example:

cd /some/random/folder ls -l # do not run this next line!

is much better presented as: [sourcecode language=“bash”] cd /some/random/folder ls -l # do not run this next line! [/sourcecode] by wrapping it in [sourcecode] tags

I’ve known about the language=‘xx’ attribute that you can use with the tag, but Ed’s posting prompted me to check on the syntax and it turns out there a few tweaks one can use. Some of them are illustrated below. The list is taken from Wordpress’ Posting Source Code reference page.

• If you only read one blog post this month, read James Morle’s eloquent attack on the term “Best Practice”.
• I’m very excited to be joining RittmanMead next month! I’m looking forward to working with some of the industry’s most respected experts.

In Oracle 11g, the DBMS_STATS procedure GATHER_SCHEMA_STATS takes a parameter ‘options’ which defines the scope of the objects processed by the procedure call, as well as the action. It can be either GATHER or LIST (gather the stats, or list out the objects to be touched, respectively), and AUTO, STALE or EMPTY (defining the object selection to process).

• GATHER on its own will gather stats on all objects in the schema
• GATHER EMPTY / LIST EMPTY is self-explanatory - objects with no statistics.
• GATHER STALE / LIST STALE is pretty obvious too - objects that have stale statistics (i.e. have had 10% change to them since statistics were last gathered). NB this 10% can be changed at an object/schema/DB level.
• However, the documentation is ambiguous as to the precise function of GATHER AUTO / LIST AUTO.

There’s even a MOS note, “Differences between GATHER STALE and GATHER AUTO (Doc ID 228186.1)”, which strangely enough - given the precision of its title - doesn’t really explain the difference.

When using preupgrade to upgrade an existing Fedora 14 installation to Fedora 15, the following two errors were encountered:

• Failed to fetch release info
• No groups available in any repository

The box sits on a network behind a proxy out to the web.

The resolution was to make sure that environment variables http_proxy and https_proxy are set: [sourcecode language=“bash”] export http_proxy=http://user:password@proxyserver:port export https_proxy=http://user:password@proxyserver:port [/sourcecode] Make sure you do this from the user from which you run preupgrade.

Excel may send chills down the spine of us when we hear users talking about its [ab]use, but it has its place in the toolset. For my money, it is a very good tool for knocking out graphs which look decent. Of course, rrdtool is my geek tool of choice for dynamic long-term graphing, but when doing scratch PoC work, I normally fall back to Excel.

One thing which has frustrated me over time is, well, time, and Excel’s handling thereof. How many times (these puns are getting tiresome already) have you seen an axis like this and gnashed your teeth?

Here’s a quotation that I’ve just read and wanted to share. It is all part of a BAAG approach to troubleshooting problems, performance in particular.

From Greg Rahn (web|twitter) on oracle-l:

There are always exceptions, but exceptions can be justified and supported with data. Just beware of the the silver bullet syndrome…

The unfortunate part […] is that rarely anyone goes back and does the root cause analysis. It tends to fall into the bucket of “problem…solved”.

Blogging from Oracle itself about OBIEE has always been a bit sparse, certainly in comparison to that which there is for core RDBMS.

It’s good to see a new blog emerge in the last couple of months from OBI Product Assurance, including some nice ’n spicy detailed config/tuning info.

Find it here: http://blogs.oracle.com/pa/.

There’s a couple more OBI blogs from Oracle, but both are fairly stale:

• Implementing Oracle BI & EPM Solutions - Rob Reynolds
• OBIEE Ramblings
• Oracle BI Foundation (thanks Daan for pointing this one out)

OK, a bit tired on a Monday morning, and so a bit sarcastic.

I’ve not really fallen off my chair, but I am shocked. I honestly didn’t think it would happen.

Oracle have finally released OBI 11g for HP-UX Itanium:

In other news, patchset 10.1.3.4.2 for OBI 10g was released today, I wonder if/when we’ll get an HP-UX Itanium version? The download page has it conspicuous by its absence even from “Coming Soon”:

Have you defined CLIENT_ID in your OBIEE RPD yet? You really ought to.

As well as helping track down users of troublesome queries, it also tags dump files with the OBIEE user of an offending query should the worst occur:

For details, see:

• Identify your OBIEE users by setting Client ID in Oracle connection
• Instrumenting OBIEE for tracing Oracle DB calls

A new version of OBI 10g (remember that?) has just been released, the Oracle twitter machine announced:

Along with presumably a bunch of bugfixes, the release notes list new functionality in catalog manager:

Download 10.1.3.4.2 from here

Trying to connect to a repository in ODI using OCI. Target database is Oracle 11.1.0.7.

Throws this error: [sourcecode] com.sunopsis.sql.l: Oracle Data Integrator Timeout: connection with URL jdbc:oracle:oci8:@ODIPRD and user ODI_USER. at com.sunopsis.sql.SnpsConnection.a(SnpsConnection.java) at com.sunopsis.sql.SnpsConnection.t(SnpsConnection.java) at com.sunopsis.sql.SnpsConnection.connect(SnpsConnection.java) at com.sunopsis.tools.connection.DwgRepositoryConnectionsCreator.a(DwgRepositoryConnectionsCreator.java) at com.sunopsis.tools.connection.DwgRepositoryConnectionsCreator.a(DwgRepositoryConnectionsCreator.java) at com.sunopsis.graphical.l.oi.a(oi.java) […] [/sourcecode]

Normally this error would be caused by a misconfigured Oracle client. For example, a missing or incorrect tnsnames.ora entry. I validated these and got a successful response using tnsping.

July’s Critical Patch Update from Oracle includes CVE-2011-2241, which affects OBIEE versions 10.1.3.4.1 and 11.1.1.3. No details of the exploit other than it “allows remote attackers to affect availability via unknown vectors related to Analytics Server.”

It is categorised with a CVSS score of 5 (on a scale of 10), with no impact on Authentication, Confidentiality, or Integrity, and “Partial+” impact on Availability. So to a security-unqualified layman (me), it sounds like someone could remotely crash your NQSServer process, but not do any more damage than that.