Spotted this when trawling through My Oracle Support. It’s pretty common knowledge anyway amongst people already familiar with hacking around with OBIEE, but worth recording for people coming along to it new.
Doc ID 1068266.1 states:
UDML is not supported in OBI 10g.
in 11g, XUDML (the Oracle BI Server XML API) will be fully supported and documented.
I’ve been playing around with SQL Tuning Sets, and was trying to clear up my mess.
To list all the tuning sets:
SET WRAP OFF SET LINE 140 COL NAME FOR A15 COL DESCRIPTION FOR A50 WRAPPED select name,created,last_modified,statement_count,description from DBA_SQLSET
NAME CREATED LAST_MODI STATEMENT_COUNT DESCRIPTION --------------- --------- --------- --------------- ---------------------------------------------------------------------------------------- sts_test_02 09-MAR-10 09-MAR-10 1 Test run 1 sts_test_01 12-FEB-10 12-FEB-10 1 an old STS test test test
To delete a tuning set:
BEGIN DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'sts_test_01'); END;
But you may hit this message:
ORA-13757: “SQL Tuning Set” “string” owned by user “string” is active.
Cause: The user attempted to update an active SQL Tuning Set.
Action: Remove all reference to the SQL Tuning Set and retry the operation.
For example: ORA-13757: “SQL Tuning Set” “sts_test_01” owned by user “badger” is active.
To look up why the STS is considered active, check the SQL Tuning Information Views, in this case DBA_SQLSET_REFERENCES
SET WRAP OFF SET LINE 140 COL NAME FOR A15 COL DESCRIPTION FOR A50 WRAPPED select description, created, owner from DBA_SQLSET_REFERENCES where sqlset_name = 'sts_test_01';
which in my case showed this:
DESCRIPTION CREATED OWNER -------------------------------------------------- --------- ------------------------------ created by: SQL Tuning Advisor - task: RNM_TT 12-FEB-10 badger
So we check for this on DBA_ADVISOR_TASKS:
SET WRAP OFF SET LINE 140 COL NAME FOR A15 COL OWNER FOR A10 COL DESCRIPTION FOR A50 WRAPPED select owner,description, created,last_modified from DBA_ADVISOR_TASKS where task_name = 'RNM_TT'
and it shows this:
OWNER DESCRIPTION CREATED LAST_MODI ---------- -------------------------------------------------- --------- --------- badger SQL Advisor - sts_test_01 12-FEB-10 12-FEB-10
So now we know it’s a stale SQL Tuning Advisor task that uses the SQL Tuning Set, and I definitely want to delete it:
BEGIN DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'RNM_TT'); END;
and then I can delete my original SQL Tuning Set:
BEGIN DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'sts_test_01'); END;
All done 🙂
OBIEE’s Systems Management functionality exposes performance counters and the application’s configuration options through Java MBeans and optionally a protocol called JMX.
It’s extremely useful, and is documented pretty widely :
In this article I’m going to discuss the use of JMX to access these counters remotely, and a possible security issue that’s present in the BI Management Pack manual. The BI Management Pack is an add-on to Oracle’s Enterprise Manager / Grid Control for managing OBIEE, see Mark Rittman’s excellent guide on Oracle’s website.
To access Systems Management data remotely you need to start the JMX agent, having configured it for remote access first. However, if you are lazy, and/or follow the configuration in the BI Management Pack manual, and set com.sun.management.jmxremote.authenticate=false anyone can update your OBIEE configuration if they have network access to your server and a client for JMX (such as jconsole, part of standard java distribution) and time to guess the port number. This is not cool. Ever played with AUTHENTICATION=BYPASS_NQS?
The latest Java documentation (now with an Oracle logo!) does address this:
Caution – This configuration is insecure. Any remote user who knows (or guesses) your JMX port number and host name will be able to monitor and control your Java application and platform. While it may be acceptable for development, it is not recommended for production systems.
To be clear – if you’re not running the JMX Agent, this is all irrelevant. It’s only if you’re running it and haven’t thought through the consequences of the configuration.
One way to secure the JMX agent is to use password authentication. The other is to set up SSL. The following demonstrates how to enable password authentication.
Please note – the following covers how to password-protect the JMX agent. It isn’t making it bullet-proof, and there’s no reason why a dictionary attack against it wouldn’t work as there’s no lockout. This also means it’s a good reason not to use a default username from the config files. Note also the following warning in the Java documentation: (if anyone can translate it into english I’d be grateful 😉 )
“WARNING: A potential security issue has been identified with password authentication for JMX remote connectors when the client obtains the remote connector from an insecure RMI registry (the default). If an attacker starts a bogus RMI registry on the target server before the legitmate one is started, then the attacker can steal clients’ passwords.”
To enable password authentication you need to edit three files.
The first file to edit is the agent script, runagent.sh. You’ll find this in $ORACLEBI_HOME/systemsmanagement.
By default, the file looks like this:
#!/bin/sh
# this is a template of runagent.sh to be used on Unix.
# The installer will fill in JAVA_HOME, SAROOTDIR, and SATEMPDIR
export JAVA_HOME=/usr/java/jdk1.6.0_17
export SAROOTDIR=/app/oracle/product/obiee
export SADATADIR=/data
export SATEMPDIR=/data/tmp
export UNIXPERFDIR=${SATEMPDIR}
java_cmd="${JAVA_HOME}/bin/java -Djava.library.path=${SAROOTDIR}/server/Bin -Dcom.sun.management.jmxremote -classpath analytics-jmx.jar:lib/xmlparserv2.jar oracle.bi.analytics.management.StandardConsoleAgent"
${java_cmd}
To enable remote access to the JMX agent you change the java_cmd to the following:
java_cmd="${JAVA_HOME}/bin/java -Djava.library.path=${SAROOTDIR}/server/Bin -Dcom.sun.management.jmxremote -Dcom.sun.man
agement.jmxremote.port=9980 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -
classpath analytics-jmx.jar:lib/xmlparserv2.jar oracle.bi.analytics.management.StandardConsoleAgent"
Note that jmxremote.authenticate is set to false. To secure the JMX agent you change it to true:
java_cmd="${JAVA_HOME}/bin/java -Djava.library.path=${SAROOTDIR}/server/Bin -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=9980 -Dcom.sun.management.jmxremote.authenticate=true -classpath analytics-jmx.jar:lib/xmlparserv2.jar oracle.bi.analytics.management.StandardConsoleAgent"
Now note what JAVA_HOME is set to in the runagent.sh file (in the above example it’s /usr/java/jdk1.6.0_17). Navigate to this directory, and then to jre/lib/management. You should see these four files:
jmxremote.access jmxremote.password.template management.properties snmp.acl.template
Create a copy of jmxremote.password.template to a file called jmxremote.password. Open the file and you’ll see two default users (or “roles”) as the documentation calls them.
$cp jmxremote.password.template jmxremote.password $vi jmxremote.password
# # Following are two commented-out entries. The "measureRole" role has # password "QED". The "controlRole" role has password "R&D". # # monitorRole QED # controlRole R&D
We’ll come back to this file in a moment. Now open jmxremote.access and you’ll see the access rights for the users (“roles”) in the password file are defined here:
# "readonly" grants access to read attributes of MBeans. # For monitoring, this means that a remote client in this # role can read measurements but cannot perform any action # that changes the environment of the running program. # "readwrite" grants access to read and write attributes of MBeans, # to invoke operations on them, and to create or remove them. # This access should be granted to only trusted clients, # since they can potentially interfere with the smooth # operation of a running program
So, now decide how you want to regulate access. I would strongly recommend that the only access available through remote JMX is readonly. Read/Write access to configuration needs to be through one auditable route, and I’d suggest this isn’t the best one. If that’s how we’re going to configure it, we set the files up like this:
(delete or comment out everything in the files first, having taken a backup first)
jmxremote.password:
jmxobiee S3cur3Passw0rd
jmxremote.access
jmxobiee readonly
Finally, secure access to the password file so that it’s only readable by the application owner ID:
chmod 600 jmxremote.password
Now, go back to $ORACLEBI_HOME/systemsmanagement, and start the JMX agent:
nohup ./runagent &
(the nohup and & make it run in the background so it doesn’t quit when you exit your session)
Having started your agent, you can go to JConsole and login to it remotely.
See the document here for the full details of securing JMX, including use of SSL and alternative password file locations.
JConsole should be in your PATH, so enter JConsole from Start -> Run (Windows), or alternatively find it in the bin directory of your JAVA home directory (Windows/Linux/Unix).
To see the OBIEE counters click on MBeans tab :
and then expand the “Oracle BI Management” folder:
You’ll notice if you’re connected as a readonly user and try to change any values you get an error:
When OBIEE is running you get some very detailed performance counters:
(If you only see Configuration folders within BI then it’s because OBIEE isn’t running 🙂 )
One nice thing you can do is see a graph of the metrics, by clicking on Attributes in the left tree, and then double-clicking on the number you want to graph in the right pane:
I find the possibilities of the JMX interface to BI counters very interesting, and am surprised there is so little discussed about it. Maybe everyone else is merrily using it and feels no need to brag 😉
The counters in particular that BI Server exposes gives a peek under the covers of an application that has no detailing logging other than NQQuery.log. Using these counters through JMX we can look at things such as the current state of a connection pool, or the BI Server Cache.
Does anyone know of a freeware tool for collecting data from JMX? I know I could use the BI Management Pack but we don’t have it. JConsole or JManage give visualisation of the data realtime, but the latter is very rough around the edges.
We hit a very interesting problem in our Production environment recently. We’d made no changes for a long time to the configuration, but all of a sudden users were on the phone complaining. They could login to BI from EBS but after logging in the next link they clicked took them to the OBIEE “You are not logged in” screen.
Our users login to EBS R12 and then using EBS authentication log in to OBIEE (10.1.3.4). Our OBIEE is deployed on OAS, load balanced across two servers by an F5 BIG-IP hardware load balancer.
In the OBIEE NQServer.log we started to see a lot of these errors around the time users started complaining:
[nQSError: 13011] Query for Initialization Block 'EBS Security Context' has failed.
[nQSError: 23006] The session variable, NQ_SESSION.ACF, has no value definition.
The EBS/BI authentication configuration was not done by me, and the theory of it was one of the things on my to-do list to understand but as is the way had never quite got around to it. Here was a good reason to learn very quickly! This posting by Gerard Braat is fantastic and brought me up to speed quickly. There’s also a doc on My Oracle Support, 552735.1, and some more info from Gareth Roberts on the OTN forum here.
We stopped Presentation Services on one of the servers, and suddenly users could use the system again. If we reversed the stopped/started servers, users could use the system. With one Presentation Services server running, the system was fine. With both up, users got “You are not logged in”. What did this demonstrate? That on their own, there was nothing wrong with our Presentation Services instances.
We soon suspected the load balancer. The load balancer sets a cookie on each user’s web browser at the initial connection as they connect to BI. The cookie is used in each subsequent connection to define which application server the user should be routed to. This is because Presentation Services cannot maintain state across instances and so the user must always come through to the same application server that they initially connected to (and therefore authenticated on).
What had happened was that the Load Balancer was issuing cookies with an expiry date already in the past (the clock was set incorrectly on it *facepalm*). This meant that the initial connection from EBS to BI was successful, because authentication was done as expected. But – the next time the client came back to the BI server for a new or updated report, they hit the Load Balancer and since the cookie holding the BI app server affinity was invalid (it had already expired) the Load Balancer sends them to any BI app server. If it’s not the one that they authenticated against then BI tries to authenticate them again, but they don’t have the acf URL string (which comes through in the initial EBS click through to BI), and hence the “The session variable, NQ_SESSION.ACF, has no value definition.” error in the NQServer.log and “You are not logged in” error shown to the user.
As soon as the date was fixed on the load balancer cookies were served properly, we brought up both Presentation Services, and everything worked again. Phew.
Footnote: I cannot recommend this tool highly enough : Fiddler2. It makes tracing HTTP traffic, request headers, cookies, etc, a piece of cake (cookie?).
A new document has been published by Oracle, discussing ways of improving performance for OBIA 7.9.6 and 7.9.6.1. Its primary focus is around improving ETL performance. There’s some very interesting content including hardware sizing recommendations, and I’d strongly recommend anyone working with OBIA reads it.
It’s called “Oracle Business Intelligence Applications Version 7.9.6 Performance Recommendations” and is available on My Oracle Support through Doc ID 870314.1
Low-born clods of brute earth
Latest Tunes
rmoff 