OBIEE Agents/Alert Subscription using Java Webservice


After long time I am publishing a interesting post. I could do that long time back but time was not permitting to do so.

So let me first explain the requirement : We had 10g for couple of years before we transitioned to OBIEE 11g. During migration process we lost Subscribers who had subscription to individual Agents/iBOTs. There are several reason why we had lost this, because one of our BI product Catalog is so huge with millions of tiny files (because it has not been cleaned for long time plus it had past 5 years of junk users/folders which never deleted or cleaned) moving those files with archive/un-archive and then OBIEE 10g to 11g upgrade was never successful. So we had to plan for either manually do Subscription for those Alerts or Automatically do that.

Manual was not an option as there were thousand of Alert subscribers who subscribed to individual agents. So that is why the solution come into play. I was never a Java coder but tried my best to code it in below phases:

  1. Install JDeveloper 11g software which will be our UI and java code compiler/execution interface
  2. Create a Application and then Create a Project.
  3. Under one Project add New -) Web Services -) Web Service Proxy
  4. Always select JAX-RPC Style coding
  5. Then Add your WSDL web link which is OBIEE WSDL URL (For e.g : if your OBIEE URL is : http://(host):9704/analytics/saw.dll?Dashboard – your WSDL URL will be http://(host):9704/analytics/saw.dll?wsdl
  6. Then Select the corresponding webservice. In this case we need “SAWSessionService” and “IBotService”

The step by step are in the Slideshow:

This slideshow requires JavaScript.

So finally you see how two Webservice has been added. Now you need the code which is AutoAlert.java and what it does step by step is :

  1. We had a lookup table with all username and password under csv file which is A4.csv here. This csv is has list of users for which we want the Alert subscription automatically for one specific Agents
  2. The code with parse the csv ,take user/password from each row
  3. It will create new OBIEE session for each row and then subscribe that user with specific agent in that OBIEE session itself
  4. Finally once you edit that Agent in OBIEE you will all users subscribed automatically.

For this java file you need to create it under Project as New -) Java -) Java Class and named it as AutoAlert.

5.png

Then paste the code as below: Also replace the OBIEE URL with your env’s URL.

++++++++++++++++++++++++++++++++++++++++++++++++++++

package oracle.bi.webservices;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.util.HashMap;

import webservices.bi.oracle.v6.Logon;
import webservices.bi.oracle.v6.LogonResult;
import webservices.bi.oracle.v6.SAWSessionServiceSoapPortClient;
import webservices.bi.oracle.v6.IBotServiceSoapPortClient;
import webservices.bi.oracle.v6.ItemInfo;
import webservices.bi.oracle.v6.Subscribe;
import webservices.bi.oracle.v6.SubscribeResult;

public class AutoAlert
{
public AutoAlert() {
super();
}

public static void main(String[] args) {
try
{
BufferedReader br = new BufferedReader(new FileReader(“D:\\A4.csv”));
String sCurrentLine;
String first = “Username is: “;
String second = “Password is: “;
HashMap data = new HashMap() ;
while ((sCurrentLine = br.readLine()) != null) {
String[] information = sCurrentLine.split(“,”);
String username = information[0];
String password = information[1];
System.out.println(“Username: ” +username + ” / ” + “Password: ” + password );
data.put(username,password);

Logon my_logon = new Logon();
LogonResult logResult = new LogonResult();
my_logon.setName(username);
my_logon.setPassword(password);
String log_sessid = new String();
SAWSessionServiceSoapPortClient new_soap_port_client = new SAWSessionServiceSoapPortClient();
new_soap_port_client.setEndpoint(“http://<obiee_host&gt;:9704/analytics/saw.dll?SoapImpl=nQSessionService”);
logResult = new_soap_port_client.logon(my_logon);
log_sessid = logResult.getSessionID();
System.out.println(“Logon Session ID: “+log_sessid);

IBotServiceSoapPortClient ibot_soap_access = new IBotServiceSoapPortClient();
ibot_soap_access.setEndpoint(“http://<obiee_host&gt;:9704/analytics/saw.dll?SoapImpl=ibotService”);
Subscribe sub=new Subscribe();
SubscribeResult subresult=new SubscribeResult();
sub.setPath(“/shared/Agents/Agency360 Alerts/Pace – Revenue Penetration Rank Decline”);
sub.setSessionID(log_sessid);
sub.setCustomizationXml(“”);
subresult = ibot_soap_access.subscribe(sub);
System.out.println(“Subscribing to Alert: “+sub.getPath());
}
}
catch (java.lang.Exception e) {
e.printStackTrace();
}
}
}

++++++++++++++++++++++++++++++++++++++++++++++++++++

Before you execute this 2 changes required to be done in OBIEE end:

  1. Login as Admin user and go to “Manage Catalog Groups” -) Under Presentation Admin add “Authenticated User” . (This is required because I am not using Impersonate method for my Java webservice call)
  2. Also under “Manage Privilege” add “Presentation Admin” under SOAP  option.

3. Now once you run the project you will see below : with each login each session has been initiated and subscribe to Agents: The failure below because user password was expired

WebService error 2

4. Also if step 1 and 2 is not configured proper way you will see “Insufficient Privilege” issue.

WebService error 3.jpg

WebService error 4

5. Now when this started executing just monitor the Session here and you will see Webservice call :

Manage Sessions while running Alerts Sunscription

6. Finally when everything is done. You will see users are subscribed to Agent as below:

8

7. If you want to run this Subscription for multiple Agents you just need to code such a way and change that Alert path in below method call :  sub.setPath(“/shared/Agents/Agency360 Alerts/Pace – Revenue Penetration Rank Decline”);

8. So you are all set and enjoy writing Java codes to do OBIEE stuff 😉 😀

OBIEE 11g Performance Challenge “Ultimate Tuning Guide”


The idea to create this new thread about OBIEE 11g Performance Tuning as it is a real challenge and very few people even the experts have limitation to cater all tuning aspects. This is only because a monster seating in front wheel which is driver of all stuff called “Weblogic” and magnitude of knowledge with evolve of Weblogic 11g has certain  limitations.

We are going to explore nuts and bolts of all possible tuning aspects which essentially can help a lot of people. The scope of this blog considers tuning OBIEE 11g(11.1.1.7 )Linux platform which is more stable and more reliable. We have huge # of customers accessing analytics everyday with high # of queries. The Customer base is huge each day 24×7 accessible across every part of the universe. The reports and pages rendering needs to run very fast without any performance concern at-least that is the expectation. Having this in mind we started tuning 11g platform and performed regression test 5 times in row. Below are the tuning what we did so far in different layers of the application. Overall it was a great testing effort conducted ! We did performance testing with 1000 concurrent user ramped up in the system within 1 minutes. We use performing testing tool to emulate user login using a software and browse through across different dashboard pages and the logout mimicking users activities.

Lets see what are the different layers of Performance tuning we have done on our platform:

  • Operating System(OS) / Hardware Level Tuning

    We have 3 Virtual servers in Horizontal cluster and each of them running only 1 instance. No Vertical clustering. All in Highly available platform with Fail-over and Load balancing capability and has future scalability opportunity. Note those servers are not Physical yet. If you are planning to do Physical that will give more dedicated power and Oracle recommend this but as of now if you plan to do with Virtual it will give you similar capability if you mastered it with proper level of tuning.
  • Assign Proper Hardware resource: Initially we had 3 CPU Cores but with the increase # of loads on CPU we increased that to 8 Virtual CPU’s to balance out the load.  We have Intel(R) Xeon(R) CPU E5-2637 v2 @ 3.50GHz and each server has 128 GB of RAM.
  • Change TCP Max Sync: When the server is heavily loaded or has many clients with bad connections with high latency, it can result in an increase in half-open connections which is called “tcp_max_syn_backlog” . To give CPU enough room to handle too many open files and to avoid OutofMemory error while creating new native threads we have tweaked couple of below parameter in /etc/security/limits.conf  file. Let root edit file to include below: Also check the parameter using ulimit -n and ulimit -u command in Linux.
    * soft nofile 131072
    * hard nofile 131072
    * soft nproc 131072
    * hard nproc 131072
  • Change TCP Fin Timeout: cat /proc/sys/net/ipv4/tcp_fin_timeout  , this will give you the existing #. If the # is 60 make it 30 . Reducing the value of this entry, TCP/IP can release closed connections faster, providing more resources for new connections.
  • You might thing of to change: /proc/sys/net/ipv4/tcp_keepalive_time to be less than 7200. Follow this to make both the changes on TCP level is updated in system:

http://tldp.org/LDP/solrhe/Securing-Optimizing-Linux-RH-Edition-v1.3/chap6sec75.html

  • I have face a  very weird scenarios where we had to touch Cisco router settings to Allow Urgent Flag(Disable Clear Urgent Flag) to have better communication between DB server and OBIEE servers. We had issue where Usage Tracking causing trouble to render one prompts .. ! Sounds weird ! yes its true , it was a connection issue between UT table in DB and UI causing trouble for one of the multi-select prompt never rending and hanging forever. Also I have seen issues where Scheduler process has severe intermittent connection issue  with DB (RCU Schemas). Both of them are addressed by this Cisco Firewall level changes below:
  • Cisco Router Config1 Cisco Router Config2 Cisco Router Config3 Cisco Router Config4
  • Middle Tier (Weblogic EM/Console) Layer Tuning

1)  In EM against data source Increase # of Connection Pool to 150. This includes all the data source added there as per below image. For us we haven’t touch default 50 and you might want to do that. Increasing this value could cause potential impact on Database as it will try to open that many open cursors and processes to the database server.

EMDataSourceConnections EMDataSourceConnections2

2) We have tuned 64 bit JVM’s. In JRockit JVM (R28.x), the heap grows faster than before. The JVM also ensures that the heap size grows up to the maximum Java heap size (-Xmx) before an OutOfMemory error is thrown.So we have tweaked some JVM related parameter under below file:  [MiddlewareHome]/user_projects/domains/bifoundation_domain/bin/setOBIDomainEnv.sh

JVM

3) Tune Analytics (WebLogic Server app plug-in) Connection pool:

There is a connection pool between WebLogic Server analytics app and OBIPS, and the default value is 128 inadequate for a large number of concurrent users which is typically expected in a BIEE system with high users concurrency. When the number of connections reaches the maximum limit, any new requests are kept waiting. Hence, it is recommended to increase this pool to 512 for your BIEE system to support more concurrent users.

I have done parameter change in file :

[Middleware Home]/ user_projects/domains/bifoundation_domain/config/fmwconfig/biinstances/coreapplication/bridgeconfig.properties

Changed: oracle.bi.presentation.sawconnect.ConnectionPool.MaxConnections=777

4) EM STUCK THREAD PARAMETER CHANGES

This is very important as highlighted it in RED. It is also known as HOGGING Thread . In a high user volume testing environment seeing this STUCK Thread issue is pretty common in log files. We need some exceptional level of tuning to get rid of this STUCK thread issue.

WebLogic Server automatically detects when a thread in an execute queue becomes “stuck.” Because a stuck thread cannot complete its current work or accept new work, the server logs a message each time it diagnoses a stuck thread. A thread might get stuck due to various reasons.

For example: When large BI report is running and the time it takes to complete is say 800 seconds, then, as the default stuck thread timing is 600 seconds in WebLogic Server, the thread allocated for that query waits for 600 seconds and goes to stuck state.

As best practice we have changed basic Thread Parameters as below under Console. And this needs to be done all the Managed servers clustered participating nodes  including Admin server.

STUCK1 STUCK2

In our environment we have faced this issue multiple times and we took couple of thread dump when STUCK thread occurs and BI Server stops responding and stop accepting any new connections:

A typical thread dump has below details and observe that there are lot of TIMED WAITING events:

“[ACTIVE] ExecuteThread: ‘136’ for queue: ‘weblogic.kernel.Default (self-tuning)'” RUNNABLE native

java.net.SocketInputStream.socketRead0(Native Method)

java.net.SocketInputStream.read(SocketInputStream.java:129)

com.siebel.analytics.web.sawconnect.SAWConnection$NotifyInputStream.read(SAWConnection.java:165)

java.io.BufferedInputStream.fill(BufferedInputStream.java:218)

java.io.BufferedInputStream.read(BufferedInputStream.java:237)

com.siebel.analytics.web.sawconnect.sawprotocol.SAWProtocol.readInt(SAWProtocol.java:188)

————-

“Timer-1” waiting for lock java.util.TaskQueue@3b92d7d0 TIMED_WAITING

java.lang.Object.wait(Native Method)

java.util.TimerThread.mainLoop(Timer.java:509)

java.util.TimerThread.run(Timer.java:462)

“Timer-0” waiting for lock java.util.TaskQueue@49d85ab9 WAITING

java.lang.Object.wait(Native Method)

java.lang.Object.wait(Object.java:485)

java.util.TimerThread.mainLoop(Timer.java:483)

java.util.TimerThread.run(Timer.java:462)

“Signal Dispatcher” RUNNABLE

null

“Finalizer” waiting for lock java.lang.ref.ReferenceQueue$Lock@67646de5 WAITING

java.lang.Object.wait(Native Method)

java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:118)

java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:134)

java.lang.ref.Finalizer$FinalizerThread.run(Finalizer.java:159)

“Reference Handler” waiting for lock java.lang.ref.Reference$Lock@5178efd5 WAITING

java.lang.Object.wait(Native Method)

java.lang.Object.wait(Object.java:485)

java.lang.ref.Reference$ReferenceHandler.run(Reference.java:116)

“main” waiting for lock weblogic.t3.srvr.T3Srvr@4685b50e WAITING

———————-
“Timer-117” waiting for lock java.util.TaskQueue@46fbadd2 TIMED_WAITING

java.lang.Object.wait(Native Method)

java.util.TimerThread.mainLoop(Timer.java:509)

java.util.TimerThread.run(Timer.java:462)

“Timer-116” waiting for lock java.util.TaskQueue@3550da66 TIMED_WAITING

java.lang.Object.wait(Native Method)

java.util.TimerThread.mainLoop(Timer.java:509)

java.util.TimerThread.run(Timer.java:462)

“Timer-115” waiting for lock java.util.TaskQueue@4f3279e2 TIMED_WAITING

java.lang.Object.wait(Native Method)

java.util.TimerThread.mainLoop(Timer.java:509)

java.util.TimerThread.run(Timer.java:462)

“Timer-114” waiting for lock java.util.TaskQueue@7ae00d0c TIMED_WAITING

java.lang.Object.wait(Native Method)

java.util.TimerThread.mainLoop(Timer.java:509)

java.util.TimerThread.run(Timer.java:462)

“Timer-113” waiting for lock java.util.TaskQueue@b78cdda TIMED_WAITING

java.lang.Object.wait(Native Method)

java.util.TimerThread.mainLoop(Timer.java:509)

java.util.TimerThread.run(Timer.java:462)

“Timer-112” waiting for lock java.util.TaskQueue@2812a918 TIMED_WAITING

java.lang.Object.wait(Native Method)

java.util.TimerThread.mainLoop(Timer.java:509)

java.util.TimerThread.run(Timer.java:462)

“QuartzScheduler_BIPublisherScheduler-NON_CLUSTERED_MisfireHandler” TIMED_WAITING

————

See more on here if you are interested to trace that down:

https://blogs.oracle.com/WebLogicServer/entry/analyzing_a_stuck_weblogic_execute

A very useful way to find the STUCK Threads are: go to Console -) Environment -) Servers -) Click Each Managed server as bi_serverx -) Click Monitoring -) Threads .

Below are 3 important parameters which tell you the server health plus Hogging thread count . If you see Hogging thread count is potentially high then you are in trouble with STUCK Thread . This below snapshot is very ideal managed server nodes with no Hogging/Stuck thread at all.

Hogger1

However this is most notorious STUCK thread issue . See the Count of thread waiting here. You must be in trouble in such scenarios:

Console

Memory consumption will tell you no issue with CPU / RAM . See this for 3 clustered nodes. Each has 128 GB memory though we are using hardly 15% of it and CPU is 50% free almost every time. So its not an Hardware bottleneck for sure. Network bottleneck ??? May be ! right now in our optical fiber network channel I am assuming its not an issue.

Hogger2

When you see Hogging thread count exceeds 20+ you will get below kind of error in dashboard page for all sessions already In and no new sessions will be able to open connection and will throw login error:

Hogger3

Now see the way you can get details of which request is Hogging . It will not tell you exact dashboard and report name but will tell you which application part/URI request at very high level. When more and more such request piled up in Middle-tier it will results a unresponsive server and application will not be able to respond which potentially leads to server crash.

Usage Tracking Hogging

More such requests you can find here . All became STUCK or HOGGING thread .

Hogger4

See JDBC Data source performance as well:

JDBC Connections usages 0 JDBC Connections usages 1Below shows 2 nodes of 4 node Exadata RAC performance so STUCK Thread is not a bottleneck due to Database and very less usage/load has been observed in DB nodes.

Exadata

HOW TO TRACE STUCK THREAD and SEE where is the bottleneck:

Always leverage EM to see the performance in sessions plus performance in GET and POST request.

server loads3

Use WLS EM to find out the load of various components:

WLS Load

Usage EM Agency Peak Load Agency Peak Load-2 EM Usage - Metric Palette

Try to use Performance URL like below to get the online details during the regression test execution  http://<server:port>/analytics/saw.dll?Perfmon

Load this , analyzed this to get some alarming #’s which will tell you precisely where the problem lies.

Perfmon - Query parameters

Check this link which will tell you how a standard Perfmon will be looks like: RATE-After Final Perf Test

But at  this point you have to understand which parameter is bottleneck and its not an easy job!

Note that, so far whatever tuning we did, it was not enough for the excellent performing environment.

So we did couple of other level of tunings in RPD layer and BI Config layer so that we can best use of the change we did in Weblogic layer.

  • OBIEE (RPD/Catalog/Config file) Level Tuning

     1) BI Server Config (The Blue one below is very important to get rid of THREAD STUCK issue)

[SERVER]

READ_ONLY_MODE = NO; # This is for both online & offline – This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control
MAX_SESSION_LIMIT = 2000;
MAX_REQUEST_PER_SESSION_LIMIT = 5000;
SERVER_THREAD_RANGE = 100-1000; # 40-100 was default
SERVER_THREAD_STACK_SIZE = 1 MB; # default is 256 KB (32 BIT mode), 1 MB (64 BIT mode), 0 for default
DB_GATEWAY_THREAD_RANGE = 40-1000; #40-200 was default
DB_GATEWAY_THREAD_STACK_SIZE = 1 MB; # default is 256 KB (32 BIT mode), 1 MB (64 BIT mode), 0 for default
HTTP_CLIENT_THREAD_RANGE = 0 – 100;
HTTP_CLIENT_THREAD_STACK_SIZE = 1 MB; # default is 256 KB (32 BIT mode), 1 MB (64 BIT mode), 0 for default
MAX_EXPANDED_SUBQUERY_PREDICATES = 8192; # default is 8192
MAX_QUERY_PLAN_CACHE_ENTRIES = 5000; # default is 1024
MAX_QUERY_PLAN_CACHE_ENTRY_SIZE = 1 MB; # default is 256 KB,(32 BIT mode), 1 MB (64 BIT mode), 0 for default
MAX_DRILLDOWN_INFO_CACHE_ENTRIES = 1024; # default is 1024
MAX_DRILLDOWN_QUERY_CACHE_ENTRIES = 1024; # default is 1024
INIT_BLOCK_CACHE_ENTRIES = 500; # default is 20

GLOBAL_CACHE_STORAGE_PATH = “/obiagency360/Shared/Cache” 2 GB; # This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control
MAX_GLOBAL_CACHE_ENTRIES = 1000;
CACHE_POLL_SECONDS = 300;
CLUSTER_AWARE_CACHE_LOGGING = NO;

 2) BI Presentation Server Config

<ThreadPoolDefaults>
<ChartThreadPool>
<MinThreads>100</MinThreads>
<MaxThreads>400</MaxThreads>
<MaxQueue>2048</MaxQueue>
</ChartThreadPool>
</ThreadPoolDefaults>

<Cache>

<ACLs>
<Enabled>false</Enabled>
</ACLs>

<CatalogXml>
<!– Remove from the cache everything older than N minutes –>
<MaxAgeMinutes>240</MaxAgeMinutes>
<MaxLastAccessedSeconds>14400</MaxLastAccessedSeconds>
</CatalogXml>
<Query>
<MaxEntries>5000</MaxEntries>
<!– AbsoluteMaxEntries is the enforced maximum number of entries. When this maximum is reached –>
<!– subsequent queries will fail until the maximum is no longer exceeded. –>
<AbsoluteMaxEntries>20000</AbsoluteMaxEntries>
<!– CruiseEntries is amount of entries the OracleBI Presentation server tries to maintain in its cache. –>
<CruiseEntries>3000</CruiseEntries>
<!– Forces the cache to attempt to remove an old entry when MaxEntries is exceeded. –>
<ForceLRU>true</ForceLRU>
</Query>
</Cache>

 3) Chart config [INSTANCE_HOME/config/OracleBIJavaHostComponent/coreapplication_obijh1/config.xml]

<JobManager>
<MinThreads>100</MinThreads>
<MaxThreads>200</MaxThreads>
<MaxPendingJobs>200</MaxPendingJobs>
</JobManager>

 4) RPD (Repository) Init block config

  • As we know that Starting point of OBIEE Authentication is “Authentication Init block”. We have found minor lags on query performance because it was taking more than expected time and ~7-8 seconds. Since our Init block query is heavy with almost 20 joins the concurrent user login could cause a issue for us. So we moved the entire query to Database procedure , use TABLE functions and pass the :USER to query only and get the variables populated by the returned value of the functions. We have seen 50% performance improvement in this process as it was leveraging the DB cache feature a great extent
  • During 10g to 11g upgrade RPD connection pools are not changed. However best practice is to separate connection pools for init block and connection pools used for Datawarehouse query. So we have segregated one connection pool to two and then assign 30% of Datawarehouse connection pool to Init block totaling ~ 760 connections to 1 RPD spread across 5 databases.
  • Removed unnecessary init block queries. Cleaned up errors in Init block query and removed unused variables plus connection pools
  • Apply patch at server level for “ADF Warning” which will reduce ADF warning in logs
  • Make LOGLEVEL for users not more than 0.
  • Web/Front Layer Tuning

    Bandwidth Savings: 1) Enabling HTTP compression can have a dramatic improvement on the latency of responses. By compressing static files and dynamic application responses, it will significantly reduce the remote (high latency) user response time.
    2) Improves request/response latency: Caching makes it possible to suppress the payload of the HTTP reply using the 304 status code. Minimizing round trips over the Web to re-validate cached items can make a huge difference in browser page load times.

  • This uses a web accelerator mechanism to compress HTTP files (static and dynamic content) and add caching mechanism to help rendering the UI faster. Also in URI and Content compressor the packets transmits the web reduced to 10x in number of bytes and increase the usability of network traffic and faster hand-off between clients and server request.  See this and learn more about HTTP Compression:

https://support.f5.com/kb/en-us/products/wa/manuals/product/wa_implementations_11_0_0/3.htm

Attached below which will tell how much % we gain due to HTTP Compression and we are avoiding misuse of network bandwidth.

HTTP Compress

Also it is advised not to clear browser cache each time after closing and reopening of browser. Make below changes in browser settings:

Increase the cache size to 1024 MB
o Firefox: Enter “about:config” as the url and change:  browser.cache.disk.capacity to 1024000 ,  browser.cache.disk.max_entry_size to -1
o IE: Set “Disk space to use” to 1024 under Internet Options -> Browsing history -> Settings

More details of issues can be found on deciphering below Log files as additional measure of Performance tuning…

  • Presentation Services Log (saw.log).
  • BIServer Log (nqquery.log, nqserver.log).
  • Scheduler Log (nqscheduler.log).
  • JavaHost Log (jh.log).
  • Cluster Controller Log (nqcluster.log).
  • WLS Managed Servers Log (AdminServer-diagnostic.log, AdminServer.log,
    bi_server1.log, bi_server1-diagnostic.log).

I hope this will help a lot and enjoy performance tuning 🙂 😀

You can refer Vishal’s blog which has handful of tuning details specially on DB config parameters and how to tweak them  :

http://obiee-oracledb.blogspot.com/2012/05/obiee-performance-imporvement-tips.html

Another very useful information available here on Performance topic: http://www.clearpeaks.com/blog/oracle-bi-ee-11g/obiee-11g-tuning-and-performance-monitoring

 

OBIEE 11g Services Start-up Guide for Unix Platform


Below are the step by step guide to do clean start-up and shut-down of OBIEE 11g services. As we all know 11g is beast compared to its ancestor so we need to be careful doing the things in the right way.

Here I have demonstrated the steps how to do that in Linux/Unix platform and the idea is to kick-off below commands from the user having admin roles and in my case dba groups .This user is same I have used to do installation. If you have any other local user better to avoid it for services start-up as this could cause several locks into Java OPSS security files and will be causing problem to start Admin and Managed server of weblogic in bootstrap process.

In below steps I am running all the processes in nohup mode in background of unix session so that it will continue to run even if you have exit from the current running session.

1) Start-up Admin Server:

[orabi@obiappl11g-xxx ~]$ nohup sh /u00/app/MiddlewareHome/user_projects/domains/bifoundation_domain/bin/startWebLogic.sh -Dweblogic.management.username=weblogic -Dweblogic.management.password=xxx > wls_start.log &

Find the below lines in log files which will make sure clear start of Admin Server.
<Apr 28, 2014 10:05:56 PM CDT> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to RUNNING>
<Apr 28, 2014 10:05:56 PM CDT> <Notice> <WebLogicServer> <BEA-000360> <Server started in RUNNING mode>

2)  Start-up Node Manager:

[orabi@obiappl11g-xxx ~]$ nohup sh /u00/app/MiddlewareHome/wlserver_10.3/server/bin/startNodeManager.sh > nodem_start.log &

Find the below lines in log files to confirm start-up:

<Apr 28, 2014 10:07:03 PM> <INFO> <Secure socket listener started on port 9556>
Apr 28, 2014 10:07:03 PM weblogic.nodemanager.server.SSLListener run
INFO: Secure socket listener started on port 9556

3) Start-up Managed Server:

[orabi@obiappl11g-xxx ~]$ nohup sh /u00/app/MiddlewareHome/user_projects/domains/bifoundation_domain/bin/startManagedWebLogic.sh bi_server1 http://obiappl11g-xxx:7001 > start_bi_server1.log &

Look for below lines in log files to confirm Managed Server start-up:

WebLogic Managed Server “bi_server1” for domain “bifoundation_domain” running in Production Mode>
<Apr 28, 2014 10:53:14 PM CDT> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to RUNNING>
<Apr 28, 2014 10:53:14 PM CDT> <Notice> <WebLogicServer> <BEA-000360> <Server started in RUNNING mode>

Note: In above steps http://obiappl11g-xxx:7001 is my Admin URL where  obiappl11g-xxx is server name.

Make sure you have added WLS_USER and WLS_PWD in below file before start-up so you don’t need to pass that in command line parameters:

/u00/app/MiddlewareHome/user_projects/domains/bifoundation_domain/bin/startManagedWebLogic.sh

You can start-up this from WLS Console or Fusion Middleware Control also.

4) Start OPMN managed services:

[orabi@obiappl11g-xxx ~]$ /u00/app/MiddlewareHome/instances/instance1/bin/opmnctl startall

enter and wait until command prompt comeback

Check the status:

[orabi@obiappl11g-xxx ~]$ /u00/app/MiddlewareHome/instances/instance1/bin/opmnctl status

It should be look like this:

Processes in Instance: instance1
———————————+——————–+———+———
ias-component                    | process-type       |     pid | status
———————————+——————–+———+———
coreapplication_obiccs1          | OracleBIClusterCo~ |    5467 | Alive
coreapplication_obisch1          | OracleBIScheduler~ |    5801 | Alive
coreapplication_obijh1           | OracleBIJavaHostC~ |    5465 | Alive
coreapplication_obips1           | OracleBIPresentat~ |    5463 | Alive
coreapplication_obis1            | OracleBIServerCom~ |    5464 | Alive

Now Voila…. After all services started up in good health 🙂

11g

 

Note: You can see background processes from top -u serid> command:

[orabi@obiappl11g-xxx ~]$ top -u orabi

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 5065 orabi     20   0 5983m 1.1g  42m S  1.0  2.3   2:20.89 java
 7117 orabi     20   0 19452 1352  948 R  0.7  0.0   0:00.19 top
 2643 orabi     20   0 6100m 1.2g  48m S  0.3  2.6   3:01.12 java
 2841 orabi     20   0 2095m 129m  19m S  0.3  0.3   0:05.34 java
 5464 orabi     20   0 4538m 121m  47m S  0.3  0.3   0:04.26 nqsserver
 5465 orabi     20   0 3980m 232m  16m S  0.3  0.5   0:07.90 java
 2448 orabi     20   0  105m 1936 1492 S  0.0  0.0   0:00.24 bash
 2578 orabi     20   0  103m 1432 1120 S  0.0  0.0   0:00.03 sh
 2809 orabi     20   0  103m 1364 1108 S  0.0  0.0   0:00.01 sh
 5011 orabi     20   0  103m 1264 1092 S  0.0  0.0   0:00.00 sh
 5012 orabi     20   0  103m 1436 1120 S  0.0  0.0   0:00.02 startWebLogic.s
 5440 orabi     20   0 68328 8424 5900 S  0.0  0.0   0:00.00 opmn
 5441 orabi     20   0 1757m  17m  10m S  0.0  0.0   0:06.68 opmn
 5463 orabi     20   0 4172m 271m  72m S  0.0  0.6   0:03.49 sawserver
 5467 orabi     20   0 1481m  24m  15m S  0.0  0.1   0:00.70 nqsclustercontr
 5801 orabi     20   0 1909m  78m  51m S  0.0  0.2   0:01.66 nqscheduler

or else you can see them from running job list:

[orabi@obiappl11g-xxx ~]$ jobs
[1]   Running                 nohup sh /u00/app/MiddlewareHome/user_projects/domains/bifoundation_domain/bin/startWebLogic.sh -Dweblogic.management.username=weblogic -Dweblogic.management.password=xxx> wls_start.log &
[2]-  Running                 nohup sh /u00/app/MiddlewareHome/wlserver_10.3/server/bin/startNodeManager.sh > nodem_start.log &
[3]+  Running                 nohup sh /u00/app/MiddlewareHome/user_projects/domains/bifoundation_domain/bin/startManagedWebLogic.sh bi_server1 http://obiappl11g-t1.gain.tcprod.local:7001 > start_bi_server1.log &

Make sure you follow the same rule during shutdown…

Leave it for you guys to explore how to do that 😉 😛

OBIEE 11g – orthodox De-install Process


Most of the common question is how to Install OBIEE 11g(11.1.1.5) but a bit un-common question is how to De-install until and unless you are facing problem with current installation process . Here are the steps at high level (not screenshot by screenshot) which will perform the orthodox De-install process. As the selection is quite straightforward hence I am skipping the screenshot’s part. There are several blog where you can find the screenshot to help you out.

Briefly, I had the full installation with Weblogic , RCU , Oracle in some path in Windows Server(2008,R2, 64 bit) and after De-installation I want to continue using the same path so all the De-install process should be very clean so that it will not leave any footprints of previous installation in any of the places for a successful installation once again.

Here you go …

  • Stop all running processes for Admin and Managed servers including BI components services. Stop all the processes, if any, created during service process creation for e.g. Windows Process.
  • Remove the Weblogic Server using the Un-installation wizard under Control Panel.
  • Perform De-install from Oracle Business Intelligence -) De-install option of Program menu
  • Drop the RCU schemas using the RCU batch file -) Drop option
  • Manually remove the RCU Tablespace DBF file (to have a clean new RCU schema) . In my case I didn’t remove the DBF file as the Database server is under DBA’s control .Hence, to be safer side I re-create the RCU during the fresh installation process under different tablespace. This will make sure nothing is going to messed up with your previous installation … See below image:
  • De-install Middleware Oracle home (traverse to ..\[MIDDLEWARE_HOME]\Oracle_BI\oui\bin\ and execute below from command line: setup.exe -deinstall)
  • De-install the Middleware common Oracle Home  (traverse to ..\[MIDDLEWARE_HOME]\oracle_common\oui\bin\ and execute below from command line: setup.exe -deinstall –jreLoc  ..\[MIDDLEWARE_HOME]\Oracle_BI1\jdk)
  • Remove the Middleware Home directory with all contents forcefully,if reqd.
  • Remove the environment variable pointing to earlier locations and the relevant paths if any. This is Important!
  • Re-boot the entire server.
  • Delete the Oracle common keys , if any, from registry. This is Important ! Most of the common problem lies here . So in my case I follow the process of finding my Middleware Home path from Registry and delete all relevant Keys and values . Be careful while playing with registry 🙂
  • Remove the windows service process key from registry , if any
  • As in my case I don’t need to De-install my Oracle Client/DB Home hence I skip the process as I going to re-use the same Oracle Home in the next installation time.So if you are going to do the same probably you can skip De-installing your Oracle Client/DB home.

Thats it , Welldone! until you have performed the clean installation once again …..:)

OBIEE 11g Graphical Color Scheme like 10g


Salut à tous…. Joyeux Noël…. Wish you Merry X-Mas and good time with your near and dear ones … So I am back here with some new geeks … 🙂 Excuse me if somebody already know it !… and please let other to learn …. !

OBIEE 11g Graph colors are quite jazzy comparing to 10g… this is one of the recent feedback we received from clients in our post up-gradation review of codes .So one of the obvious requirement came into picture is that, how do we retain the 1og Graphical color schemes in 11g. This could be easily done by playing across the XML codes for color definition .

Lets do it …

As you might already be aware of that 10g Color contents (hex-codes) definition are stored in the palette.cxml file. So what you need to do is only copying the color Hexcodes from 10g files and put those under the file located : “..\Middleware\Oracle_BI1\bifoundation\web\msgdb\s_blafp\viewui\chart\dvt-graph-skin.xml” having the below sample syntax  (do the same for other style folders like s_FusionFX  if you need it)

So below content copied to the above xml and put this between the first and last tag of  SeriesSlice :

<SeriesItems>
<Series id=”0″ color=”#A4D5F9″ borderColor=”#A4D5F9″/>
<Series id=”1″ color=”#5592D0″ borderColor=”#5592D0″/>
<Series id=”2″ color=”#EFD084″ borderColor=”#EFD084″/>
<Series id=”3″ color=”#687FA1″ borderColor=”#687FA1″/>
<Series id=”4″ color=”#CFC4E5″ borderColor=”#CFC4E5″/>
<Series id=”5″ color=”#8D9EB7″ borderColor=”#8D9EB7″/>
<Series id=”6″ color=”#5898AA” borderColor=”#5898AA”/>
<Series id=”7″ color=”#CEE4EA” borderColor=”#CEE4EA”/>
<Series id=”8″ color=”#90B5D1″ borderColor=”#90B5D1″/>
<Series id=”9″ color=”#F6E795″ borderColor=”#F6E795″/>
<Series id=”10″ color=”#C7C1C1″ borderColor=”#C7C1C1″/>
<Series id=”11″ color=”#85A8EE” borderColor=”#85A8EE”/>
</SeriesItems>

Make sure all the services restarted before the change to be effective.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

See below difference before applying the XML(11g Original) and after applying (aligned with 10g color).

N.B:- It has been observed that though the 10g color scheme applied for 11g but it is not at per with existing 10g colors and there are slight deviations in terms of colors which is not eye-catching but somehow not matching exactly due to the difference of the rendering engine of 10g and 11g.

A worth to note is that, in our implementation we have seen that in several charts the color change is not propagated and hence it seems that the color scheme is not applied properly as per the defined XML. However if you edit the chart and remove any measure and then drag the same measure again we see that the color starts changing as per XML . The behavior seems to be unpredictable for us. Assumption is that ,it might happened due to some internal cache specially for the chart engine here.  But the problem is not observed for any new reports created in answers in 11g and it starts reflecting the new colors scheme from very beginning.

OBIEE 11g LDAP with HTTPS – SSL Setup


Hypertext Transfer Protocol Secure (HTTPS) is a combination of the Hypertext Transfer Protocol (HTTP) with SSL protocol to provide encrypted communication and secure identification of a network web server. HTTPS connections are often used for payment transactions on the World Wide Web and for sensitive transactions in corporate information systems.

The purpose of the thread is to establish the connection between web via OBIEE11g front-end and the weblogic app server using  HTTPS protocol . The first part of the below thread describes it. The second part of the thread describes how to enable the communication between the OBIEE components,Weblogic Admin and Managed servers via secure SSL protocol. This will ensure the communication using the SSL Certificates.

In my case it is presumed that OBIEE11g Repository already configured with LDAP server based authentication.

Part One – Configuration under Weblogic Admin


Rest of the settings described as below:

1) Login to Weblogic Administration Console .

2) Click on Environments -) Servers -) AdminServer (admin) -) General tab

3) Click Lock and Edit from the left pane.

4) Check the ‘SSL Listen Port Enabled’ as 7002 (this is not default SSL port enabled for all browsers , So please check yours ad modify based on that)

This will ensure that you will be able to access the URL using 7002 port using https://

5) Check also ‘Listen Port Enabled’ if you also want to access BI URL using http://

6) Save the configuration

7) Activate the changes from left pane

8 ) Change the StartStopServices.cmd file from : “<MiddleWare Home>\instances\instance1\bifoundation\OracleBIApplication\coreapplication” as below for below parameters only:

set BI_URL=https://%wls.host%:%wls.mgd.port%/analytics

set wls.mgd.port=7002

9) Restart the Weblogic Servers(Admin/Managed) and BI Servers components

10) Accept the exception in browser when it prompts for it and continue accessing BI URL in secure HTTPS protocol

(Note that once this has been made as https:// you have to access OFWM EM Control page and Weblogic Console page also in https:// going forward)

Part Two – Configuration under OFMW Enterprise Manager

1) Navigate to “<OFMW Home>\user_projects\domains\bifoundation_domain\bin” and take backup of startManagedWebLogic.cmd

2) Edit and locate section with below content::

set JAVA_OPTIONS=-Dweblogic.security.SSL.trustedCAKeyStore=”<OFMW Home>\wlserver_10.3\server\lib\cacerts”

3) Replace the above with below:: (Kindly note that you have to change the OFMW Home path as applicable to your environment)

set JAVA_OPTIONS= -Djavax.net.ssl.trustStore=”<OFMW Home>\wlserver_10.3\server\lib\DemoTrust.jks” -Djavax.net.ssl.trustStorePassword=”

4) Restart all the services of Weblogic (Admin/Managed/opmnctl/Node Manager/Process Manager)

5) Now in the next step System MBean browser will be configured to enable SSL across all BI components

6) Login OFMW Control Enterprise Manager page

7) Invoke the Lock of BIDomain.

8 ) Now we have to Generates the certificates required as a prerequisite for enabling SSL, using the specified passphrase to protect both certificate stores and private keys.This enables internal https calls to the web server. The certificate type (pem or der) must be explicitly stated.

Hence navigate to oracle.biee.admin –> bifoundation_domain –> BIDomain.BIInstance.SecurityConfiguration and click on the BIDomain.BIInstance.SecurityConfiguration MBean.Click on the operation tab click on “generateSSLCertificates”.

9) Enter the details asked for: For my case I have included below:

Passphrase  : dxp12345

webServerCACertificatePath : \wlserver_10.3\server\lib\CertGenCA.der

certificateEncoding is: der

10) Now click on Invoke

11) Return to the path specified in step 7 and click on simpleCommit.

12) Now click on attributes tab of the step 8 and click on ‘SSLEnabled’ .Change the value to True from False and click on Apply.

13) Repeat step 7 to lock and perform step 11 for simpleCommit.

14) Repeat Step 4

15) Return to Step 8 and click on “runSSLReport” ,Invoke it and find the output as below to ensure correct SSL communication across all BI components:

That’s All for today …. See you next time … 🙂 stay tuned !

Bypass Username and Password on Weblogic server Startup


It’s always annoying to put Weblogic server username and password on server start-up and shutdown. It would be nice if the process could be automated. There are two different approach for this. Making this as Windows Service and set it as ‘Automatic’ in Startup Type .Other controllable approach is to manipulate the StartStopservices.cmd file of weblogic to pass the weblogic user name and password variable with a hard-coded predefined value .

To adopt the second approach take the backup of file “StartStopServices.cmd” located in below path:

.\\[MIDDLEWARE_HOME]\instances\instance1\bifoundation\OracleBIApplication\coreapplication

and add the below strings to pass the value of the variables: -DWLS_USER=weblogic -DWLS_PW=weblogic#1

(here “weblogic#1” is my password)

Save the file and start the BI Services. It will take the username and password automatically reading from the file and trigger the start-up activities …

This will save some keystrokes at-least 🙂