OBIEE 12c: uploadRpd Failed: Failure in trying to acquire lock. Check bi-lcm-logs or diagnostics.


If you see below error after uploading the 12c RPD make sure you have right parameter passed at the command line .

uploadRpd Failed: Failure in trying to acquire lock. Check bi-lcm-logs or diagnostics. Error Desc Code: DESC_CODE_SERVER_EXCEPTION

This typically happens if the instances are wrong which mean -SI parameter in uploadrpd utility . Please make sure you double check and correct this .

In my case ssi is the default -SI argument value at the command line and you should be able to use cd /u00/app/Oracle_Home/user_projects/domains/bi/bidata/service_instances/ssi/metadata/datamodel/customizations to land to right path where ssi is my -SI argument.

So double check and this might be your issue and once you right service instances you should be good unless some other issues.

Also non comprehensive bi-lcm-logs tells to lookup under managed server log path and see the error description in details under file bi-lcm-rest.log.x at : /u00/app/Oracle_Home/user_projects/domains/bi/servers/bi_server1/logs

De-install OBIEE 12c in <1 minute


Rather going to GUI or achieving this executing bunch of Unix commands I will show you how to do this in 1 minute and possibly less than 1 minute 🙂

My VM Host is Linux and I have SYSDBA access to DB : 11.2.0.4 (minimal required for 12c)

Step 1 : Stop all running processes 

Go to BITools location :  /u00/app/Oracle_Home/user_projects/domains/bi/bitools/bin

Then ./stop.sh   # this will stop all processes associated with BI

Step 2:  DROP RCU Schema  — Needs SYSDBA privilege or ask DBA

Select * from System.SCHEMA_VERSION_REGISTRY where MRC_NAME='<add DB PREFIX>’;   — Check DB Prefix where RCU is installed

delete from System.SCHEMA_VERSION_REGISTRY where MRC_NAME='<add DB PREFIX>’;

Commit;

Locate all RCU schemas and Perform DROP schema operation . Typically it is 9 RCU schemas.  For my case its below:

drop-schema

Step 3: remove the app folder :

in my case :   rm -rf  /u00/app

You can achieve this above by writing a Unix script combining all steps and just press one button .

This steps applicable for Linux env but for Windows you might need to clear extra stuffs sitting inside Registry.

If you don’t have direct access to database then for Step 2) you can user deinstall utility to drop the repository automatically and let the scripts do that internally.

 

 

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 12c “A new era of Oracle Analytics” – The New features


OBIEE 12c has been launched with couple of Brand new exciting features and with couple of major enhancements from its predecessor. The enhancement has been made in Visualizations, Platform Architecture, Installation and Configuration, Migration and deployment. Also major enhancement made on the directory structures and no more complicated lengthy path of Weblogic directories and its more clean and pristine.

Lets isolate each areas and see what are the major enhancements:

  1. Installation And Configurations
  2. Multi-tenancy (MSI) and Pluggable BI
  3. BI Application Archive(BAR)
  4. OBIEE Server level enhancements – In-memory analytics and Advanced analytics
  5. Data Mashups for Answers
  6. Visual Analyzer
  7. Better Mobile Analytics

I am going to touch the UI enhancement first on Visual Analyzer (as I know this is going to be very attractive features and major highlight of this release).

A) VISUAL ANALYZER

  • VA is a very strong web based tool has new generation Answers capability for self-service analysis.
  • VA is quick data exploration and data discovery tool which can build rich dashboards on-the-fly in very quick turnaround time. Rich analysis possible with drag and drop configuration
  • It has very easy and swift transitioning palette and quick slice and dice features.
  • Very simple user friendly input which can create very rich graphics with different options of exploring the data, discovering the patterns and seeing trends.
  • Provide highly interactive experience with intuitive filtering and cross visualization brushing and highlighting
  • Visual Analyzer is not intended to be a standalone reporting tool. Visual Analyzer makes use of the existing Oracle BI EE
    repository, which means that all your Oracle BI EE 11g data sources can be leveraged to allow users to explore existing data warehouse content.
  • Visual Analyzer uses Presentation Services data models as a content source, relies on the catalog to store content, uses the search infrastructure to find content and relies on a common security model for managing roles and permissions.
  • VA use rich modern java-script library and rendered through HTML5
  • Using VA you can create advanced graph, blend of color , size , shape , palette using various co-ordinate systems

VA01 VA02 VA03

You can modify and manipulate data model on-the-fly

VA04 VA05VA Integrates any kind of data source, it is powerful tool to load and model data. You can smartly blend the personal and external data.

It has a nice global searching capabilities on any measure/object name like Qlikview.

VA06

B) DATA MASHUP

You can upload your own datafile like xls data , match with existing OBIEE model and generate on the fly customized reporting. Internally its logical SQL extension to directly call external data. Just in time modelling with measures and attributes, mashup across enterprise and user defined data.

Data mashup also include OBIEE embedded data store for user uploaded files.

A simple MASHUP Architecture like below :

VA18

VA07 VA08 VA09 VA10 VA11Quick reporting by picking up right metrics and showing data and Easy content search

VA12VA13 VA14 VA15 VA16 VA17

C) Advanced Analytics Capability

Predictive analysis is more tightly integrated, enabling customers to more easily forecast future conditions based on existing data points, group elements that are statistically similar, and expose outliers.  BI 12c includes the ability to run the free Oracle R distribution on BI Server, and extend existing analytics with custom R scripts, which can point to any engine (R, Oracle Database, Spark, etc.) without needing to change the BI RPD to deliver results.

Misc01

D) In-Memory Analysis

Misc02 Misc03

E) 11g to 12c UPGRADE – Pluggable Model and BAR/BVL

Oracle BI Baseline Validation Tool – Complete New Capability
• A tool that enables the creation of a baseline set of data that is then able to be compared with data from another (or the same) system that may be the result of an upgrade, patch or migration, or any change
• A way to perform regression testing on any two compatible Oracle BIEE environments
–Works within and across 11g and 12c versions
• A way to instill confidence when performing an upgrade, migration or significant change to an existing environment

Misc04The baseline validation tool interfacing both 11g(Pre-Upgrade) and 12c (Post-Upgrade) which will give the capability of easy testing plus validating data set, Logical query pre and post upgrade. It is going to a a very essential feature and reduce lot of testing time and effort for sure. Oracle called this as Pluggable model.

The following modules will be available here:
– Data verification
• Compare result output (i.e. actual numbers or text) from different versions
– Visual verification
• Capture screenshots of reports to check they are visually the same
– BI catalog verification
• Capture object and metadata and compare with the target system
– Logical query verification
• Capture logical query definitions and compare with the target system
– Performance verification
• Capture performance profile to check there’s not degradation

Misc07

F) Multi-Tenancy Model / Multi-Service Instance (MSI) enabled

Multitenancy refers to a principle in software architecture where a single installation of the software runs on one server or clustered servers, serving multiple client organizations.With a multitenant architecture, each client organization operates
independently of other organizations that share the same infrastructure. Multitenancy offers the ability to host multiple companies (even competitors) in one deployment without them knowing of each other.

Misc05 Misc06

G) System Administration and Configuration

  • Single Source for BIPLATFORM schemas. No Information on DB and topology info stored in Database
  • Full offline configuration speed up domain creation
  • Separate OBIEE Installation and Config (it was just reverse than 11g when RCU creation was first step)
  • Chef Capability allow most of the config parameters to be edited
  • One place to edit environment and/or configuration so no more duplication in config files
  • BI 12c will use CAM (not OPMN) to manage system components.All CAM-related configuration is also stored in DOMAIN_HOME.
  • Singleton Data Directory (SDD)– Simple conversion to HA scale-out
  • Zero downtime: All system and application operations should be supported on-line. No requirement for downtime of end-user functionality
  • Support offline configuration: All system and application configuration operations should be off-line. No requirement for run-time mid-tier processes to be started.
  • No more Middleware Home and all under one Oracle_Home.
  • No more OPMN . Everything managed inside weblogic
  • Migration process has been simplified in great extent using BAR files and easy validation tool pre and post migration
  • The Oracle BI Server supports the Fast Application Notification (FAN) event and Fast Connection Failover (FCF) Oracle Database configuration. It is excllent feature to add offline failover of the database connections. Fast Connection Failover enables quick failover when the data source’s Oracle database is not available. According to the documentation, This functionality will run in the background. When an Oracle Business Intelligence query initiated by an analysis user fails due to the Oracle database being unavailable, the query will fail quickly and the user can then retry the query rather than waiting for the database request to time out.
  • Invoke of the WLST from a single location

H) Mobile Analytics

  • Sharp Mobile UI interfacing with rich colors
  • Mobile Indexing data
  • Fast Mobile data query
  • Keyword search (“BI Ask”) empowers users to literally talk to their data, asking questions and having visualizations automatically created as responses, opening up an easy entry point for authoring.  Additionally, the interface for iOS has been completely redesigned; and Mobile BI for Android offers sharing and following for nearby devices, as well as the ability to project any dashboard or story to GoogleCast-enabled devices.

I) Repository enhancements

This is In progress. I will update pretty soon…

— Thanks

Life of OBIEE Catalog object permission – A niche Subject “ACL” cache


Access Control List(ACL) is very common terminology and not confined precisely within OBIEE. Several software use ACL to define security object permissions.An ACL specifies which users or system processes are granted access to objects, as well as what operations are allowed on given objects. We have seen ACL when we edit Objects permission or open Catalog object using Catalog Manager. I have noticed “preserve ACL” in OBIEE 10g Catalog Manager if you want to apply permission across objects. Also noticed ACL again on 11g while we embarked a very strange/weird and complicated issue.

We have OBIEE 11g .7 version 140415 running on production since launch past 5 months. Recently couple of customer raised concern about page visibility for different kind of subscription. We have customer could use multiple subscription which can be switched from a portal and based on that switch specific page should have to be visible. However we have experience a issue when a user logged in and seeing Dashboard A and logout and relogin again with different subscription Dashboard A still visible while Dashboard B should have to be visible.

Initially how you attack this problem ? I know what you are thinking of but we have very complicated system with several layers before you land to dashboard page. We have One portal which redirects to IDP (performing TCping Authentication) ,then control goes on Horizontal Cluster nodes via Load Balancer and then control goes to Analytics. So there are 3 way channels before you are landing to OBIEE page. On top of this we have deprecated 11g security system running initialization block. I know this is damn bad and Oracle recommended but we have to live with the crap because Oracle said this can still be functional. So in summary we have no Application Roles / Groups setup on EM and Console and all object based permission defined under Catalog is legacy Catalog Group permission.

So there are various aspect and point we need to carefully verify before we attack this problem. I started debugging this issue from Catalog Object level permission first , nothing seems weird but did little bit tweak here and there and it didn’t resolve the issue.

We thought it could be SSO Ping Auth issue which is bypassing the RPD security and not getting GROUPS variable populated right way. So we added WEBGROUPS and ROLES system variables in RPD and enabled all level of trace on EM and Console on oracle bi security objects including the most granular level of LOGLevel trace in instanceconfig.xml file.

We have found after logout of initial session and login new session is not picking up the permission correctly and it retains the old permission on object.

There are security masking logic on Read , Write and All permissions in each level of Catalog object while can be traceable in sawlog file which clearly depicts the issue ! But unfortunately we don’t have smoking gun yet to resolve the problem.

A search in OTN and googling around doesn’t seem to be matching to right use case ! this was a frustrating issue which bother for 4 days until actual fix has been found and Finally we have isolated our environment from SSO , Cluster , Load balancer everything to segregate the root cause of the issue. We came to know its OBIEE 11g issue for sure. We have tried several areas of tweak to fix this issue mostly on file system level , cleaning cache , clean volatileuserdata, removing cacheduserinfo, removing that user content and re-instate, GUID refresh and nothing works and we were almost out of ideas what to do next 😦 😦

FINALLY there is an idea triggered on my mind if there is any configuration can be done on refresh level which could potentially refresh dashboard object including the permission could resolve the issue. And I realize only Instanceconfig.xml is a place where you can declare on such parameter. So in search of some parameter I found below:

<Cache>
<CatalogAttributes>
<MaxAgeMinutes>1</MaxAgeMinutes>
<CleanupFrequencyMinutes>1</CleanupFrequencyMinutes>
</CatalogAttributes>
</Cache>

This will reload catalog object permission on 1 minutes interval . So potentially this could fix my issue.

And it Voila… it fixed my problem … Now right user getting correct permissions on multiple subscription switch. But still its not end of the game ! WHY ? Yes, we have seen catalog object refreshing in 1 minutes interval but my problem reoccur if multiple login/logout happens within 1 minute of interval …. Sad !! So this is not going to be a solution. Also reload catalog object frequently is not an good idea as SAW server does a hard IO work each time this happens to crawl across catalog and refresh object permission. Also there is no instanceconfig.xml parameter exist to lowered it down to seconds level  😦

Adding this parameter is as good as “Reload server metadata trigged” by Admin from analytics Admin link.  So I was in search how to invoke this automatically rather than required on demand.

Investing more energy on that reveals what is this object permission and seems closely related to ACL and we came to know below details how OBIEE handle the users in Catalog:

The catalog is designed to scale to thousands of concurrent users. To achieve this scaling, the catalog adheres to the following guidelines:

  • The average user typically only reads from the catalog and rarely, if ever, writes to it. In Release 11g, each user is constantly and automatically updating his or her Most Recently Used file, but each user’s “read” operations still far outweigh the user’s “writes” operations. Therefore, the read-to-write ratio is typically at least 100 to 1.
  • While a locking mechanism guarantees that only one user can write to an object at a time, it is rare for multiple users to attempt to write simultaneously to the same object. A feature called “lazy locking” allows users to continue reading an object even when another user is updating that object.
  • Modern file systems cache “small” files directly inside the directory record, such that reading any information on a directory simultaneously loads all small files directly into the operating system’s memory cache. Therefore, it is good practice to keep files in the catalog “small,” especially the frequently “read” .atr metadata files. When these metadata files remain small, then all the .atr files in a directory are loaded into memory with one physical hard disk read. Every file that exceeds the “small” threshold adds another physical hard disk read, which can cause a 100% degradation for each large file. In other words, use care when considering storing arbitrary “Properties” in .atr files.
  • Reading an object’s .atr metadata file using NFS is far slower than reading it directly from a local disk. For this reason, Presentation Services additionally caches all .atr files internally. This cache can become briefly “stale” when another node in the cluster writes data to the file that is newer than the data that is cached by the current node. Therefore, all nodes are refreshed according to the MaxAgeMinutes element in the instanceconfig.xml, whose default for a cluster is 5 minutes. This default setting commonly achieves the best trade-off between the possibility of stale data and the known performance impact. (The default for an environment without clusters is 60 minutes.)

Ours are pretty similar configuration of having millions of .atr file (as we have very large user base in production) in NAS storage continuously accessed by Sawserver catalog crawler.

Looking much on ACL on OTN reveals not same but similar kind of issue and I came to know about bunch of very important config parameters listed at the end of this blog.

Now I have full fledged smoking gun which could potentially resolve my issue which is below:

<ServerInstance>
 
<Cache>
<ACLs>
   <Enabled>false</Enabled>
</ACLs>
</Cache>

</ServerInstance>

So I applied in instanceconfig.xml to see the results and unfortunately sawserver was not started as this is unrecognized tag ! When I see OTN carefully and I found this can be added only 140715 version on wards and not less . So we have asked Oracle to provide patch for 140415. After following up Oracle with Sev 1 issue with several escalation we finally got the fix . We applied it , add this params in instanceconfig and it resolves the issue…. 🙂 🙂  Oracle provided the fix with 3 files , one xsd and 2 saw binary files (.so files) . So the long running nightmare is over !!! 🙂 I am sooo happy…

So we know NOW what is ACL Cache and how it could potentially behave weird and cause lot of trouble to our life and we know what should be the way to get rid of the problem around it. See below some of the other important reference on Cache under instanceconfig.xml file. Recommend is if you Disable ACL cache which Oracle confirmed has no performance impact.

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

Use those judiciously if you know what is that for and later don’t burn your hands … 😉

<Cache>
<UserPopulationExternalMembershipsCache>
<CleanupFrequencyMinutes>3</CleanupFrequencyMinutes>
<MaxAgeMinutes>3</MaxAgeMinutes>
</UserPopulationExternalMembershipsCache>

<UserPopulationAccountDetailsCacheByGUID>
<CleanupFrequencyMinutes>3</CleanupFrequencyMinutes>
<MaxAgeMinutes>3</MaxAgeMinutes>
</UserPopulationAccountDetailsCacheByGUID>

<UserPopulationAccountDetailsCacheByName>
<CleanupFrequencyMinutes>3</CleanupFrequencyMinutes>
<MaxAgeMinutes>3</MaxAgeMinutes>
</UserPopulationAccountDetailsCacheByName>

<RoleDirectMembersCache>
<CleanupFrequencyMinutes>3</CleanupFrequencyMinutes>
<MaxAgeMinutes>3</MaxAgeMinutes>
</RoleDirectMembersCache>

<RoleHierarchyCache>
<CleanupFrequencyMinutes>3</CleanupFrequencyMinutes>
<MaxAgeMinutes>3</MaxAgeMinutes>
</RoleHierarchyCache>

<ParentRolesForGrantedRolesCache>
<CleanupFrequencyMinutes>3</CleanupFrequencyMinutes>
<MaxAgeMinutes>3</MaxAgeMinutes>
</ParentRolesForGrantedRolesCache>

<!– this cache is NOT related to the security stack It is only for the account info in the webcat itself –>
<Accounts>
<CleanupFrequencyMinutes>3</CleanupFrequencyMinutes>
<MaxAgeMinutes>3</MaxAgeMinutes>
</Accounts>

<!– this cache is NOT related to the security stack It is only for the account info in the webcat itself –>
<AccountIndex>
<CleanupFrequencyMinutes>3</CleanupFrequencyMinutes>
<MaxAgeMinutes>3</MaxAgeMinutes>
</AccountIndex>

<!– This CatalogAttributes cache contains all webcat objects’ metadata (the owner, permissions, properties, attributes etc).
The internal permissions have a their own cache as to who is allowed to access the webcat object.

So to cleanup the internal permissions cache, this CatalogAttributescache must also be cleaned. –>

<CatalogAttributes>

<CleanupFrequencyMinutes>3</CleanupFrequencyMinutes>

<MaxAgeMinutes>3</MaxAgeMinutes>

</CatalogAttributes>

</Cache> 

OBIEE 12c(OBIEE 12.2.1.0.0) Installation guide on Linux Platform


Oracle Business Intelligence Enterprise Edition 12c – OBIEE 12.2.1.0.0 released on OTN 26th Oct 2015. As soon as I get all the downloadable contents this is my first attempt to install the software.

We know Oracle has made bunch of amendments / enhancements to 12c on top of very successful 11g release. I am going to blog couple of new 12c features pretty soon in terms of UI as well as config changes.

So lets first follow the step by step process to install the software(64 bit) in my Oracle Linux 6.0 OS .

Step 1 – Download and Procure the required files

Download Oracle Business Intelligence 12c from :

http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/index.html

1Unlike 11g the installation files are little different and need to installed as per mentioned order. As I am using the server version I have downloaded below files :

2So you have to download and Install JDK first. For me, I have downloaded jdk-8u66-linux-x64.rpm package.

3

So it looks like First JDK , then Weblogic and then OBIEE main installer and no separate RCU required to be installed. Also notice the files are little bit different with .jar for Weblogic Infrastructure and .bin for OBIEE installation.

Step 2 – Install JDK

Should be simple install command:

sudo yum localinstall jdk-8u66-linux-x64.rpm

Post install verify the version:

4

Step 3 – Install below Prerequisite packages

compat-libcap1-1.10
compat-libstdc++-33-3.2.3-x86_64
libgcc-4.8.2-x86_64
libstdc++-4.8.2-x86_64
libstdc++-devel-4.8.2-x86_64
sysstat-10.1.5
gcc-4.8.2
gcc-c++-4.8.2
make-3.82
glibc-2.17-x86_64
glibc-devel-2.17-x86_64
libaio-0.3.109-x86_64

Should be simple sudo yum install command

Step 4 – Install Weblogic (Fusion Middleware) Infrastructure

My Linux server has 48 GB Memory with 140GB storage available to perform the installation. It is the simple java command to Launch the setup.

5So this opens the Oracle Inventory window: Select default Inventory location :

Middleware-Install-01Middleware-Install-02Middleware-Install-03 Middleware-Install-04 Middleware-Install-05 Middleware-Install-06 Middleware-Install-07 Middleware-Install-08 Middleware-Install-09 Middleware-Install-10Middleware-Install-11

Step 5 – Install OBIEE 12c Software

Execute the binary content as below:

OBIEE-1 OBIEE-2 OBIEE-3 OBIEE-4 OBIEE-5 OBIEE-6 OBIEE-7 OBIEE-8OBIEE-9 OBIEE-92

Step 6 – Configure RCU, Middleware, OBIEE

In this step we have to run some config setup file to configure RCU , Middleware domain .

I would prefer to install RCU separately (using ……\Middleware\Oracle_Home\oracle_common\bin\rcu.bat)before you come to this step because RCU creation , DB type validations etc can results several errors which you want to be fixed before Middleware config started.

Config01 Config02 Config03

In Below steps if you see error called Domain “bi” already exists then remove the content from below and click Next.

…\Middleware\Oracle_Home\user_projects\domains

You can experience this issue only if you aborted any previous session of the config steps.

Config04STOP here and check if you have 12c Database or at-least 11g R2 (11.2.0.4.0) installed where you are trying to create RCU. If not then it will throw you error and Installer will abort.

RCU2Also check the Database Character Set otherwise you will get warning to change data-set to AL32UTF8 like below:

issue 1For me it was WE8MSWIN1252 in Oracle Db 12c and I have executed below steps to changed it to acceptable CharacterSet.

SHUTDOWN IMMEDIATE;
STARTUP RESTRICT;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;

ALTER DATABASE CHARACTER SET AL32UTF8 ;

— if the above fails:
ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8 ;
SHUTDOWN IMMEDIATE;
STARTUP;

Also If your 12c Database configured with multi tenancy Container Database RCU can’t be created and it will give below error:

issue3I have resolved that error using below steps: fortunately for me during Oracle Db 12c installation I have clicked option ‘pdborcl’ as Pluggable database.

  1. Connect as sys user as sysdba
  2. then execute  : ALTER SESSION SET container = pdborcl ;
  3. select name, open_mode from v$pdbs;   — it should return pdborcl
  4. Alter pluggable database all open;
  5. Add below in TNSNAMES.ora

PDBORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdborcl)
)
)

If everything works great follow next steps of Configuration:

Config05

Config06 Config07 Config08 Config09 Config10As mentioned before If you encounter issue with RCU installation just isolate this configuration step from it and trigger RCU script from :

/u00/app/Middleware/Oracle_Home/oracle_common/bin/

This will prompt a RCU Config steps and you can debug any issue here like SYSDBA privilege, Connect String issue, Port issue, password issue etc etc. Here is sample screen.

RCU1

Assuming the installation and configuration steps completed. Notice there are change of ports in 12c comparing 11g:

WebLogic Console
http://<host&gt;:9500/console
Fusion Middleware Control
http://<host&gt;:9500/em
Visal Analyzer
http://<host&gt;:9502/va
BI Publisher
http://<host>l:9502/xmlpserver
BI Analytics
http://<host&gt;:9502/analytics

Step 7 – Testing post Installation / UI

This is Analytics:

analytics01analytics03This is visual Analyzer:analytics02 Some of the Visual Analyzer interfaces:

analytics04 analytics05 analytics06