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.
(Previously on this blog: 1, 2, 3…)
Instrument your code.
Stop guessing.
Make your DBA happy.
Make your life as a BI Admin easier.
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.)
The problem is that you lose a lot of visibility of work both up and down the stack.
We know SID 491 is causing a problem, but how do we identify the originating OBIEE user?
You could start piecing together Usage Tracking and NQQuery.log files, but it’s hardly convenient or instantaneous is it?
By taking advantage of native Oracle procedures, we can instrument our OBIEE code to pass through lots of valuable information:
Now we can see which OBIEE user fired the query resulting in SID 491, and not only the user, but the dashboard and request name they are running.
This works in both OBIEE 10g and 11g.
See my previous post here for further background, and discussion of the procedures used.
In essence, we harness internal OBIEE session variables which hold the user ID, name, dashboard and report name. We put a set of database calls on the connection pool(s) associated with query requests.
We have to do a bit of trickery to work around two issues.
Firstly, the variables may not be set (you may not have saved your new request yet, or may be running it outside of a dashboard). To get around this, we create two dummy session variables with the same names, and populate them with dummy init blocks.
Secondly, there is a limitation to the number of characters that can be passed through, and so we manipulate the string if necessary to use the right-most characters.
Create two init block/session variable pairs:
Session Variables
Initialisation Blocks
Be sure to use a connection pool which isn’t used for queries.
Load up your RPD. If you haven’t already, create a new connection pool that is just for these init blocks. It can be to any database – in the examples below it’s an Oracle one, but any that supports selecting from a dummy table like DUAL in Oracle.
Go to Manage -> Variables, click on Session -> Initialisation Blocks. Right click in the section to the right, and select New Initialization Block.
Call the init block Dummy_SawSrcPath_InitBlock, and then click on “Edit Data Source”
Set the Data Source Type to Database, and the init string to
select '[unsaved request]' from dual
Click on Browse to set the Connection Pool used. The connection pool should be one exclusively for init blocks (not the same you use for queries). If you try to use the same connection pool as for queries, you’ll most likely get an error when you logon.
Once you’ve set the connection pool, click on Test – you should get a result as shown:
If the Test doesn’t succeed then you need to fix the problem before you continue.
Assuming it’s worked, click OK to return to the Init Block creation window. We now want to define the dummy variable, so to do so click on “Edit Data Target”:
Click on New to create a new variable, and give it the name SAW_SRC_PATH. Make sure you get the name exactly correct, no typos.
Give it a default initializer, and then click OK.
Make sure your init block setup now looks like this:
Click on Test, and expect to get this returned:
Assuming it works, then click OK to save the new Init Block and Variable.
Repeat as above to create an init block/variable pair for SAW_DASHBOARD, looking like this:
When you’ve finished, you should have two init block/variables pairs set up like this:
Session Variables
Initialisation Blocks
Add these three SQL statements to the “Execute before query” of “Connection Scripts” of each Connection Pool which is used for queries.
Do not add them to ones which are used for init blocks / authentication etc.
call dbms_application_info.set_client_info(client_info=>'VALUEOF(NQ_SESSION.DISPLAYNAME)')
call dbms_session.set_identifier('VALUEOF(NQ_SESSION.USER)')
call dbms_application_info.set_module(module_name=>'OBIEE: ' || case when length('VALUEOF(NQ_SESSION.SAW_DASHBOARD)')<40 then 'VALUEOF(NQ_SESSION.SAW_DASHBOARD)' else '...' || substr('VALUEOF(NQ_SESSION.SAW_DASHBOARD)',-37) end,action_name=>case when length('VALUEOF(NQ_SESSION.SAW_SRC_PATH)')<31 then 'VALUEOF(NQ_SESSION.SAW_SRC_PATH)' else '...' || substr('VALUEOF(NQ_SESSION.SAW_SRC_PATH)',-28) end);
This sets values as follows:
NB CLIENT_IDENTIFIER and CLIENT_INFO have a larger capacity so could be used if you want to view more of the report/dashboard detail:
V$SESSION column Max value length MODULE 47 ACTION 31 CLIENT_INFO 63 CLIENT_IDENTIFIER 63
Reference:
DBMS_APPLICATION_INFO
DBMS_SESSION
If you’re currently logged into Answers, logout and log back in. This is necessary for the dummy session variables to populate.
Run this sql*plus SQL script below to look at any existing OBIEE queries running on the database.
set linesize 170 col program for a30 col client_info for a20 col client_identifier for a18 col module for a47 col action for a31 SELECT SID,PROGRAM, CLIENT_IDENTIFIER, CLIENT_INFO, MODULE, ACTION FROM V$SESSION WHERE LOWER(PROGRAM) LIKE 'nqsserver%';
Now login to Answers, and run an existing report, or create a new one. When you re-run the SQL script you should see your session now listed:
If for some strange reason you don’t love sql*plus, you can obviously use the above SQL in any other SQL client. Or, you can fire up Enterprise Manager and see the same set of information:
(run at a different time from the SQL above, so different report and dashboard names)
It’s occurred to me that by parsing in user-provided values to a string that’s executed on the database, there could be the potential for a breach through SQL Injection via a maliciously named report or dashboard.
I’ve not been able to find a report name which does cause trouble, but I have never tried exploiting SQL injection before.
It is another good reason to make sure that you’re using a DB account solely created for reporting queries from OBIEE, because then its privileges can be greatly restricted. This isn’t an excuse not to test for SQL Injection, but a reminder of why good practices such as granting of least privileges exist.
[nQSError: 17001] Oracle Error code: 911, message: ORA-00911: invalid character at OCI call OCIStmtExecute
State: HY000. Code: 10058. [NQODBC
It’s not my fault really.
When running an installation, presented with the option of
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.
Not having root privileges on the machine, not really understanding the purpose of OUI inventory on a single installation machine, and mostly just being a bit lazy, I obviously went for the path of least resistance.
Lazy people click here
The installation proceeded with no further issues, and I spent a couple of happy hours tinkering with the freshly installed OBI 11g and EM.
But… you cross the gods of OUI’s inventory at your peril…
When I tried to run the Upgrade Assistant, got an error:
/app/oracle/product/fmw_home/Oracle_BI1/bin $./ua Oracle Fusion Middleware Upgrade Assistant 11.1.1.5.0 Log file is located at: /app/oracle/product/fmw_home/Oracle_BI1/upgrade/logs/ua2011-10-05-08-53-26AM.log UPGAST-00055: error reading the Oracle Universal Installer inventory The inventory pointer location /etc/oraInst.loc is either not readable or does not exist
Sure enough, the file doesn’t exist:
$ls -l /etc/oraInst.loc ls: /etc/oraInst.loc: No such file or directory
Fortunately it appears you can make amends with OUI easily, by running as root the script which you were originally asked to run:
[root@server]/ $/app/oracle/product/oraInventory/createCentralInventory.sh Setting the inventory to /app/oracle/product/oraInventory Setting the group name to biadmin Creating the Oracle inventory pointer file (/etc/oraInst.loc) Changing permissions of /app/oracle/product/oraInventory to 770. Changing groupname of /app/oracle/product/oraInventory to biadmin. The execution of the script is complete
This creates /etc/oraInst.loc, which simply points to the original local inventory which was created:
$cat /etc/oraInst.loc inventory_loc=/app/oracle/product/oraInventory inst_group=biadmin
After this the Upgrade Assistant fired up just fine.
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:
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:
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:
Change notes for the OBI 11g certification doc
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:
And the culprit is ...
For details, see:
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.
Patches 11833743 and 11833750 for 10.1.3.4.1 and 11.1.1.3 respectively.
I noticed on Nico’s wiki (which is amazing by the way, it has so much information in it) a bunch of additional parameters for nqcmd other than those which are displayed in the default helptext (nqcmd -h).
These are the additional ones:
-b<super batch file name> -w<# wait seconds> -c<# cancel interval seconds> -n<# number of loops> -r<# number of requests per shared session> -t<# number of threads> -T (a flag to turn on time statistics) -SmartDiff (a flag to enable SmartDiff tags in output) -P<the percent of statements to disable cache hit> -impersonate <the impersonate username> -runas <the runas username>
Most parameters don’t appear to work in default call of nqcmd in 10g and 11g, throwing a Argument error near: error.
-b<super batch file name> -w<# wait seconds> -c<# cancel interval seconds> -n<# number of loops> -r<# number of requests per shared session> -t<# number of threads> -P<the percent of statements to disable cache hit> -SmartDiff (a flag to enable SmartDiff tags in output)
I wonder if there’s an Open Sesame type flag that needs to be used to enable these parameters by support. Or maybe they don’t even exist.
This leaves this handful of additional parameters which do work (/don’t throw an error) in the default invocation of nqcmd:
-T (a flag to turn on time statistics) -impersonate <the impersonate username> -runas <the runas username>
Oracle Support directed me to the documentation (Table 14-1), but this covers the standard parameters, not these extra ones.
Oracle Support also pointed out that undocumented parameters are not supported except under direct instruction
The -T flag looks very useful for performance testing purposes, as it appends this information to the output from nqcmd:
Clock time: batch start: 15:44:32.000 Query from: 15:44:32.000 to: 15:44:59.000 Row count: 0 total: 27 prepare: 1 execute: 26 fetch: 0 Cumulative time(seconds): Batch elapsed: 26 Query total: 27 prepare: 1, execute: 26, fetch: 0, query count: 1, cumulative rows: 0
I’m intrigued to know where Nico got his list from (he couldn’t remember when I asked him :-)). Has anyone else come across these and/or know what they do and how to invoke them? Stuff like SmartDiff sounds tantalisingly interesting.
Low-born clods of brute earth
Latest Tunes
rmoff 