Brilliant performance articles by Cary Millsap

There is a LOT written about performance. And this post is now adding to it. Some of it’s excellent, some of it less so. But a lot of it starts from a point so far down the process that unless you know the first bit, you’re going to raring off and end up chasing your tail or p###ing in the wind…. (pardon my french). Without a well structured approach that you understand and always follow you’ll hit on solutions by luck only.

So seriously, unless you really know what you’re doing when it comes to computer systems and performance you should read these articles by Cary Millsap. They’re well written and easy to follow without losing any of their point, depth or value.
Heck, even if you do know what you’re doing then read them anyway and appreciate the clear and eloquent language.

• My Whole System Is Slow. Now What?
• Thinking Clearly About Performance
• The Most Common Performance Problem I See

Identify your OBIEE users by setting Client ID in Oracle connection

You get a call from your friendly DBA. He says the production database is up the spout, and it’s “that bee eye thingumy causing it”. What do you do now? All you’ve got to go on is a program name in the Oracle session tables of “nqsserver@MYSERVER (TNS V1-V3)” and the SQL the DBA sent you that if you’re lucky will look as presentable as this:

The username against the SQL is the generic User ID that you had created for connections to the database from OBIEE.

So you turn to Usage Tracking and discover that when that particular SQL ran there were twenty users all running reports. And not only that, but the SQL that’s recorded is the Logical SQL, not the physical SQL.

So how do you identify the originating report that spawned the SQL that broke the database that upset the DBA that phoned you? …

With a large hat-tip to Mark Rittman, here’s one thing you can do to help matters. Within the Connection Pool object in the RPD you can add statements to execute at the beginning of each connection. In this case, we can set the Client ID for the user running the request.

call dbms_session.set_identifier('VALUEOF(NQ_SESSION.USER)')

Now when you look at the queries from OBIEE running on the database you’ll see the Client ID column is populated :


This helps you trace SQL from the database back to the originating user.

My only question about this is with regards to connection pooling. The documentation states that the Execute on Connect is run “…each time a connection is made to the database.” – but if connection pooling is enabled then by definition the connection is re-used so the client ID will only be set for the first user into the connection pool. However this doesn’t seem to be the case as on the database I see different Client IDs against the same session.

How to resolve “[nQSError: 12002] Socket communication error at call=: (Number=-1) Unknown”

This error caught me out today. I was building a Linux VM to do some work on, and for the life of me couldn’t get the OBIEE Admin Tool to connect to the BI Server on the VM.

The error I got when trying to define a DSN on the Windows box was:

[nQSError: 12008] Unable to connect to port 9703 on machine 10.3.105.132
[nQSError: 12010] Communication error connecting to remote end point: address = 10.3.105.132; port = 9703.
[nQSError: 12002] Socket communication error at call=: (Number=-1) Unknown

This error means that the ODBC Driver for BI Server can’t communicate with the BI Server on port 9703.
99% of the time this question comes up on the forums it’s because the BI Server isn’t running, or the host is incorrect.

I validated the BI Server was running and listening on port 9703:

[oracle@RNMVM03 setup]$ netstat -a|grep 9703
tcp        0      0 *:9703                      *:*                         LISTEN

And I fired up Presentation Services and OC4J and successfully logged into Answers. So why couldn’t my Windows box connect?

I tried telnetting from my Windows box to the VM on port 9704 – the OC4J port. This worked, as did pinging it. So the network connectivity between the two was there. If I telnetted to port 9703 (BI Server) there was an eventual timeout.

The answer to the problem was that my Linux VM (OEL5.4) was running a firewall which I’d cleverly allowed 9704 on but not 9703. Disabling the firewall fixed the problem.

Hardening OAS

Oracle Application Server (OAS) is the Web and Application server typically deployed with OBIEE. There are several settings which by default may be viewed as security weaknesses. Whether realistically a target or not, it’s good practice to always be considering security and lock down your servers as much as reasonably possible. I adopt the default stance of having to find a reason to leave something less secure, rather than justify why it needs doing.

There are various tools and companies out there that will help you scan your deployments for weaknesses. In reading about this I found Nikto which runs on all platforms. In essence it takes a hostname and port and scans for known vulnerabilities in web servers (not just OAS).

Listed below are some of the simple things you can do to secure your default deployment of OAS.

Almost all of this is derived from the very excellent Securing Oracle Application Server by Caleb Sima

In the text below I refer to $OAS_HOME which may not be an actual environment variable, but is the home directory of your OAS installation.

Don’t forget to backup config files before you change them, and take backups of deleted files and directories.

After making the changes bounce OAS (opmnctl stopall; opmnctl startall).

As well as the specifics below you should always keep an eye on Oracle’s Critical Patch Updates.

Web server version and details

By default OAS will report its version in both HTTP headers and on error pages (such as those returned on a 404 Not Found which is easy to obtain by entering a non-existent URL):

Apply these two changes to $OAS_HOME/Apache/Apache/conf/httpd.conf:

1. Search for ServerSignature and change it from On to Off
   This removes the server information from error pages
   Ref: http://httpd.apache.org/docs/2.2/mod/core.html#serversignature
2. Add this on the next line:
   ServerTokens ProductOnly
   
   This removes some server version info from the HTTP header, and is the least possible data to reveal in Apache.
   Ref: http://httpd.apache.org/docs/2.2/mod/core.html#servertokens

After the changes have been made:

TRACE method

Read Apache Tips: Disable the HTTP TRACE method/ for information on how to see if HTTP TRACE is enabled. It is by default in OAS, and most security scanners will pick it up as a problem.

To disable it, add to $OAS_HOME/Apache/Apache/conf/httpd.conf:
TraceEnable Off

Default content

Most web and application servers come with default content such as example pages or “Welcome” pages, and OAS is no exception.
The reason for getting rid of this content is to give potential attackers one less thing to work with. Static content might give them information about software versions or paths. Dynamic content (JSPs etc) may be exploitable. Either way – what is to be gained from leaving it in place?

Apache default content

In $OAS_HOME/Apache/Apache:

mv htdocs/ htdocs.old
mkdir htdocs
vi htdocs/index.html
# enter:
<HTML><HEAD><TITLE>Nothing to see here</TITLE></HEAD><BODY>Nothing to see here, move along.</BODY></HTML>

---

rm $OAS_Home/Apache/Apache/icons/README
rm $OAS_Home/Apache/Apache/fcgi-bin/*

j2ee

cd $OAS_HOME/j2ee/home/default-web-app
rm -r WEB-INF/classes
rm -r examples/
echo "Nothing to see here" > index.html 

Pre-populated username in OAS login form

This could help an attacker as they are given a username to start trying to login as.

However, I can’t work out how to disable it. I opened a thread on OTN here:
http://forums.oracle.com/forums/thread.jspa?threadID=1010227&tstart=0

If you know, please leave a comment!

Weak ciphers / SSL version 2 supported

Disable the weak SSL ciphers & disable version 2 of the protocol

Add to httpd.conf after the TraceEnable statement from above:

SSLProtocol ALL -SSLv2
SSLCipherSuite HIGH:!SSLv2:!ADH:!aNULL:!eNULL:!NULL

Ref: http://httpd.apache.org/docs/2.0/mod/mod_ssl.html#sslciphersuite

Ref: http://adamyoung.net/Disable-SSLv2-System-Wide

Restarting OAS

When I started implementing this I used opmnctl restartproc, but found that HTTP_Server came back as status “Stop” or “Bounce”. I also got errors like: “time out while waiting for a managed process to restart”.

What I think happened was that the httpd (Apache) processes didn’t come down properly, and so couldn’t restart.

Therefore I resorted to opmnctl shutdown, then search for any remaining httpd processes (ps -ef|grep httpd) and kill any (kill -9 xxxx), and then restart OAS (opmnctl startall)