Wednesday, October 31, 2012

Windows Development Reference

Windows Development Reference

Windows Sockets Error Codes


Security and Identity

Windows Web Services API

Getting Started with Oracle Business Intelligence Publisher

With Advanced Installation of OBIEE you get three HOME (top level directory)
3. ORACLE Application Server Home (HTTP Server and OC4J)
After installation, Login failed while accessing BI Publisher (earlier XML Publisher) using Administrator Account (Administrator/Administrator)
http://hostname.domainname:[http_port_of_oracle_application_server - default7777] /xmlpserver
Fix :
1. Open $ORACLE_BI_HOME/ xmlp/ XMLP/ Admin/ Configuration /xmlp-server-config.xml
2. Change entry like
<property name=”SECURITY_MODEL” value=”BI_SERVER“/>
<property name=”SECURITY_MODEL” value=”XDO“/>
3. Restart OC4J from 10g R3 Oracle Application Server ORACLE_HOME

opmnctl stopproc process-type=home
opmnctl startproc process-type=home

OBIEE performance tuning myth : BI Server logging

OBIEE performance tuning myth : BI Server logging
May 22nd, 2012 by Robin Moffatt

One of the frequent recommendations around performance in OBIEE that one hears is a blanket insistence on disabling the BI Server log. It is a line that is repeated by Oracle support, propogated in “Best Practice” guides, and repeated throughout blog posts on the subject. Antony Heljula did a talk on the subject at the recent RittmanMead BI Forum in Brighton, and I would like to echo and expand on it here.

The Myth:
If you are having performance problems in OBIEE, you should switch off BI Server logging

The arguments for:
Instinct would tell us that writing a log is going to take longer than not writing to a log
On a system with high user concurrency, we would expect to see contention for writing to the log file
Usage Tracking records report response times, so why do we also need the server logging
Log files will cause the disk to fill up, which left uncontrolled could cause system instability
The arguments against:
If you have performance problems in OBIEE, then you need logging in place to be able to trace and diagnose them. The BI Server log gives us vital information such as what physical SQL results from a logical query from the front end. If you turn off logging, you lose all visibility of query behaviour, timings, and row counts.
OBIEE writes lots of logs, more so now in 11g. Why only disable one of them? Why not all logs?
If a query takes 30 seconds to run, how much of that 30 seconds is actually going to be in log overhead? You disable logging and now your query runs in 29.999 seconds. It’s still slow, it’s still a performance problem – and now you don’t have the data available with which to diagnose the problem!
Usage Tracking doesn’t record the same level of detail around a query’s behaviour (response time profile, row counts) that the server log does.
By default, Usage Tracking chops off Logical SQL above 1024 characters in length.
Sometimes you need the log file to confirm that Usage Tracking is reporting correctly (especially in circumstances where report run times seem unusually high)
Error messages returned from the database are not captured in Usage Tracking
It Depends
To a point, I am being contrary in arguing this specific issue, but it is important with this and other broad-stroke pronouncements around performance that get regurgitating without context and caveats that they are understood. In particular, labelling it a “Best Practice” is a dangerous fallacy as it implies that it should be done without much further thought or consideration of its consequences.

If the NFR for a report’s performance is [sub]-second and it is not being met, then profiling of the end-to-end response time breakdown should be done, and it might be that it demonstrates that the logging is impeding performance. But the point is that it is proven rather than done blindly.

Further reading
Cary Millsap’s paper, Thinking Clearly About Performance, is an excellent starting point for developing an understanding of a logical and methodical approach to performance problem solving.

James Morle wrote an great blog post on the subject of “Best Practice” and why it is dangerous terminology, entitled “Right Practice”

OBIEE login takes forever. Logging in... please wait.....

When trying to login to BI server, we were getting a perpetual message :

Logging in... please wait.....

Select * from table_name

was running for over 5 mins. Asked DBAs to do their magic, and sure enough, everything was back to normal. But why in the name of god, login would hang up if database is preoccupied? Why can’t BI log you in and show a blank page in you’re My Folders?

Answer: Initialization blocks. Every time someone logs in, all session variables are populated, and till the results from BI comes back, BI would ask user to wait.
We checked the presentation server, BI server, memory, CPU, hard disk free space, temp files, you name it, all was good. Turns out, database server was the culprit. A simple query like :

Log files generated by OBIEE

OBIEE Logs are generated by the following components:

  1. BI Presentation Server
  1. BI Server
  1. BI Cluster Controller
  1. BI Javahost
  1. BI Scheduler
BI Presentation Server log can be located from C:\OracleBIData\web\log\sawlog0.log
This log is useful for Presentation Server login issues and answers/dashboard related issues.
You can increase the logging on this server to get a detailed logging to troubleshoot the SSO and integration issues by modifying the logconfig.xml file present in C:\OracleBIData\web\config folder

BI Server logs are NQServer.log and NQQuery.log present under C:\OracleBI\server\Log folder
NQServer.log consists of BI Server Start up issues and Subject areas loaded and the datasources connected
from within the RPD. If the NQSConfig.ini has errors or if RPD has version issues or if proper RPD is not loaded, the BI Server fails to start and this will be recorded in the NQServer.log
NQQuery.log consits of BI Server query operations. We can also check if the result is coming from cache or straight from database. We can get the actual time to execute the query. We can get the physical and logical query from this log.

BI Cluster Controller log can located from C:\OracleBI\server\Log folder. When the cluster is setup with OBIEE server nodes, the NQCluster.log file would provide the cluster nodes connectivity and status information. Along with the NQServer.log , NQCluster.log would be very useful for cluster related issues.

BI Javahost can be located from C:\OracleBIData\web\log\javahost\jhost0.log. This is used for identifying the issues with the charts and graphs generated by Java from Answers/Dashboard reports.

BI Scheduler can be located from C:\OracleBI\server\Log\NQScheduler.log. This is used for identifying the issues related to delivers/scheduler. To increase the logging on scheduler, set the debug flag to true in the scheduler job manager configuration window.

Sunday, October 21, 2012

Removing Suspended Service Instances - BizTalk 2009

' terminate.vbs
' Enter terminate.vbs with no arguments from a command prompt for usage
' This script needs to be run under a user account that is a member of the BizTalk Administrators
' group. This script needs to be run on a machine that is configured with BizTalk administration
' tools.
dim objBtsWmiNS, objMsg, svcinsts, inst, msg, ndx, size, savemessages
Dim aryClassIDs()
Dim aryTypeIDs()
Dim aryInstanceIDs()
Dim aryHostNames()
Dim aryObjQueues()
Dim aryHostBatchSize()
Dim strKey2Instance
Dim strQuery2Msg
maxBatchSize = 200 'Terminate in batches. Max supported batch size is 2K-1 (2047)
On Error Resume Next
Dim objArgs: Set objArgs = WScript.Arguments
If ( objArgs.Count = 0 OR objArgs.Count > 2) Then
     wscript.quit 0
End If
wmiQuery = ""
'ServiceStatus = 16 - 'Completed With Discarded Messages' in BizTalk Server 2004
'ServiceStatus = 32 - 'Suspended (not resumable)'
'ServiceStatus = 4 - 'Suspended (resumable)'
'ServiceClass = 64 - 'Routing Failure Report'
'ErrorId = "0xC0C01B4C" - is how 'Completed With Discarded Messages' are exposed in BizTalk Server 2009
If (objArgs(0) = "-Z" OR objArgs(0) = "-z") Then
     wmiQuery = "select * from MSBTS_serviceinstance where ServiceStatus=16"
End If
If (objArgs(0) = "-A" or objArgs(0) = "-a") Then
     wmiQuery = "select * from MSBTS_serviceinstance where ServiceStatus=4 OR ServiceStatus=32 OR ServiceStatus=16 OR ErrorId='0xC0C01B4C' OR ServiceClass=64"
End If
If (objArgs(0) = "-SR" or objArgs(0) = "-sr") Then
     wmiQuery = "select * from MSBTS_serviceinstance where ServiceStatus=4"
End If
If (objArgs(0) = "-SNR" or objArgs(0) = "-snr") Then
     wmiQuery = "select * from MSBTS_serviceinstance where ServiceStatus=32"
End If
If (objArgs(0) = "-DIS" or objArgs(0) = "-dis") Then
     wmiQuery = "select * from MSBTS_serviceinstance where ServiceClass=32 AND ServiceStatus=8"
'ServiceClass = 32 'Isolated Adapter
'ServiceStatus = 8 'Dehydrated
End If
saveMessagesBeforeTermination = True
If ( objArgs.Count > 1) Then
     If (objArgs(1) = "-NOSAVE" OR objArgs(1) = "-nosave") Then
          saveMessagesBeforeTermination = False
          wscript.quit 0
     End If
End If
If(wmiQuery = "") Then
     wscript.quit 0
End If
wscript.echo "-->Connecting to BizTalk WMI namespace"
Set objBtsWmiNS = GetObject("WinMgmts:{impersonationLevel=impersonate, (security)}\\.\root\MicrosoftBizTalkServer")
If Err <> 0 Then
     wscript.quit 0
End If       
wscript.echo "-->Getting BizTalk host collection"
Set hosts = objBtsWmiNS.ExecQuery("select * from MSBTS_HostSetting")
If Err <> 0 Then
     wscript.quit 0
End If       
hostCount = hosts.count
ReDim aryHostNames(hostCount - 1)
ReDim aryObjQueues(hostCount - 1)
ReDim aryHostBatchSize(hostCount - 1)
wscript.echo "-->Retrieve BizTalk host names and loading host queues"
ndx = 0
For Each host in hosts
     wscript.echo "Found host " & host.Properties_("Name")
     aryHostNames(ndx) = host.Properties_("Name")
     Set aryObjQueues(ndx) = objBtsWmiNS.Get("MSBTS_HostQueue.HostName=""" & aryHostNames(ndx) & """")
     If Err <> 0 Then
          wscript.quit 0
     End If       
     ndx = ndx + 1
wscript.echo "-->Getting collection of service instances"
Set svcinsts = objBtsWmiNS.ExecQuery(wmiQuery)
ReDim aryClassIDs(hostCount, maxBatchSize-1)
ReDim aryTypeIDs(hostCount, maxBatchSize-1)
ReDim aryInstanceIDs(hostCount, maxBatchSize-1)
'Iterate through instances and save them in host-specific arrays.
'Terminate instances from host-specific array when array gets to a maxBatchSize
wscript.echo "-->Start iterating service instances"
totalCount = 0
saveMessages = saveMessagesBeforeTermination
For Each inst in svcinsts
     saveMessagesBeforeTermination = saveMessages
     wscript.echo "Found suspended instance """ & inst.Properties_("ServiceName") & """ on host " & inst.Properties_("HostName")
     'Resolve host index
     For hostIdx = 0 To hostCount-1
          If aryHostNames(hostIdx) = inst.Properties_("HostName") Then
               Exit For
          End If
    '16 is an internal service class that cannot be terminated
     If 16 = inst.Properties_("ServiceClass") Then
          wscript.echo "Skipping BizTalk internal service instances (they cannot be terminated anyway)"
          '64 is a routing failure report and doesn't have messages that can be saved
          If 64 = inst.Properties_("ServiceClass") Or 16 = inst.Properties_("ServiceClass") Then
               saveMessagesBeforeTermination = False
          End If
          errorCountSavingMessages = 0
          If saveMessagesBeforeTermination Then
               strQuery2Msg = "select * from MSBTS_MessageInstance where ServiceInstanceID=""" & inst.Properties_("InstanceId") & """"
               Set msgInsts = objBtsWmiNS.ExecQuery(strQuery2Msg)
               For Each msg in msgInsts
                    msg.SaveToFile "C:\Temp"
                  If Err <> 0 Then
                       wscript.echo "Failed to save MSBTS_MessageInstance"
                       wscript.echo Err.Description & Err.Number
                       errorCountSavingMessages = errorCountSavingMessages + 1
                         wscript.echo "Saved message " & msg.Properties_("MessageInstanceID")
                  End If       
         End If
         If 0 = errorCountSavingMessages Then 'Only terminate when we had no problems saving messages
               aryClassIDs(hostIdx, aryHostBatchSize(hostIdx)) = inst.Properties_("ServiceClassId")
               aryTypeIDs(hostIdx, aryHostBatchSize(hostIdx)) = inst.Properties_("ServiceTypeId")
               aryInstanceIDs(hostIdx, aryHostBatchSize(hostIdx)) = inst.Properties_("InstanceId")
               aryHostBatchSize(hostIdx) = aryHostBatchSize(hostIdx) + 1 'Keep track of newly added instace for that host
               wscript.echo "Skipping the instance since couldn't save its messages"
          End If
          totalCount = totalCount + 1
          If(aryHostBatchSize(hostIdx) = maxBatchSize) Then
               TerminateAccumulatedInstacesForHost hostIdx
          End If
     End If
' Delete whatever is left
For hostIdx = 0 To hostCount-1
     If aryHostBatchSize(hostIdx) > 0 Then
          TerminateAccumulatedInstacesForHost hostIdx
     End If
wscript.echo "SUCCESS> " & totalCount & " instances were found and attempted to be terminated"
Sub     TerminateAccumulatedInstacesForHost(hostIdx)
     wscript.echo "Sending termination request for host " & aryHostNames(hostIdx) & " service instances"
     Dim aryClassIDs4Host()
     Dim aryTypeIDs4Host()
     Dim aryInstanceIDs4Host()
     ReDim aryClassIDs4Host(aryHostBatchSize(hostIdx)-1)
     ReDim aryTypeIDs4Host(aryHostBatchSize(hostIdx)-1)
     ReDim aryInstanceIDs4Host(aryHostBatchSize(hostIdx)-1)
     For i = 0 to aryHostBatchSize(hostIdx)-1
          aryClassIDs4Host(i) = aryClassIDs(hostIdx, i)
          aryTypeIDs4Host(i) = aryTypeIDs(hostIdx, i)
          aryInstanceIDs4Host(i) = aryInstanceIDs(hostIdx, i)
     aryObjQueues(hostIdx).TerminateServiceInstancesByID aryClassIDs4Host, aryTypeIDs4Host, aryInstanceIDs4Host
     aryHostBatchSize(hostIdx) = 0
End Sub
'This subroutine deals with all errors using the WbemScripting object. 
'Error descriptions are returned to the user by printing to the console.
Sub CheckWMIError()
     If Err <> 0 Then
          On Error Resume Next
          Dim strErrDesc: strErrDesc = Err.Description
          Dim ErrNum: ErrNum = Err.Number
          Dim WMIError : Set WMIError = CreateObject("WbemScripting.SwbemLastError")
          If (TypeName(WMIError) = "Empty" ) Then
               wscript.echo strErrDesc & " (HRESULT: " & Hex(ErrNum) & ")."
               wscript.echo WMIError.Description & "(HRESULT: " & Hex(ErrNum) & ")."
               Set WMIError = nothing
          End If
          'wscript.quit 0
     End If
End Sub
Sub PrintUsage()
     wscript.echo "Usage:"
     wscript.echo "cscript Terminate.vbs < -Z | -A | -DIS | -SR | -SNR > [-nosave]"
     wscript.echo "  -Z terminates all ""Zombie"" instances (e.g. completed with discarded messages)"
     wscript.echo "  -A terminates all suspended and zombie instances as well as all routing failure reports"
     wscript.echo "  -SR terminates suspended resumable instances only"
     wscript.echo "  -SNR terminates suspended non-resumable instances only"
     wscript.echo "  -DIS terminates all dehydrated 'isolated adapter' instances"
     wscript.echo "  -nosave terminates instances without saving messages they reference"
     wscript.echo "  Default action is to save instances to the C:\Temp folder on the local computer"
     wscript.echo "  Ensure that the C:\Temp folder exists before running terminate if you want to save instances"
     wscript.echo "  Example: cscript Terminate.vbs -z -nosave"
End Sub