BI Server Crash-obiee12c

saketsrv

Hello Experts,

I have migrated OBIEE from 11.1.1.7 to 12.2.1.2.0 few month back but facing a weird issue where every time i try to drill down any column, BI Servers crashes.

I have also raised an SR to oracle but all they have asked is stack traces of core dump repeatedly with no action plan till now.

Can anyone guide me on where it might have gone wrong and where to look for the fix.

Joel

You'll definitely need to start off with the log files to see if there is anything happening that has been logged. That will be your starting point. If you do come across anything in the logs, share it here and hopefully it should help to point to the right direction.

Christian Berg-0racle

+1 logs first

Then some more specific analysis - i.e. is it ANY drill or just some?

Then try a vanilla RPD to see if it is your environment or your content.

There are 5000 things to do and check. Simply raising an SR is pretty much pointless 99.9% of the time.

Ganim Altiok

Hi saketsrv,

We had the similar issue on 12.2.1.3, we also raised multiple SR's but still sending adplus logs and diagnostics dumps. Waiting for dev team to respond.

BUT we have some workarounds.

First of all you better check your RDP, we found some unmapped columns while consistency checker finds no warning or error on mentioned column. I can suggest you to check all mappings and logical table sources.

Second, when you seed cache with agents and then access to report from cache, again nqsserver.exe crashes. So we disabled cache totally, now the system is OK.

Cheers.

saketsrv

Hi Ganim,

Did you disable the caching from EM or from disabled the Cacheable option for physical table from Admin tool for that query?.

Ganim Altiok

I disabled all caching from NQSConfig just like disabling from EM.

saketsrv

Hi Christian

Thanks for the reply!!

As you suggested ,i have tried with Vanilla RPD and its working fine so it seems our content?

I have also gone through various logs but that didn help.

Also diaabling cache didn work for me as suggested by Ganim.

For ref. pasting the logs below.

Please suggest.

obis1-diagnostic.log

-------------------------

[2018-01-08T12:02:35.551+05:30] [OBIS] [NOTIFICATION:1] [] [] [ecid: ] [sik: ssi] [tid: 2c] Connection Pool exit for DSN OLAPSMIS, DB name is ITC Conn Pools, connection pool name is ITC Connection Pool #1 [[

Properties: description=RpScopeVar Exchange; producerID=0x2ce95a0; requestID=0xfffe0001; sessionID=0xfffe0000; userName=nQS;

]]

[2018-01-08T12:02:35.552+05:30] [OBIS] [NOTIFICATION:1] [] [] [ecid: ] [sik: ssi] [tid: 2c] Connection destroyed due to timeout. dsn is VALUEOF(OLAP_DSN), DB name is ITC Conn Pools [[

Properties: description=RpScopeVar Exchange; producerID=0x2ce95a0; requestID=0xfffe0001; sessionID=0xfffe0000; userName=nQS;

obis1-query.log

--------------------------

[2018-01-08T11:47:23.761+05:30] [OBIS] [TRACE:5] [] [] [ecid: ] [sik: ssi] [tid: 2e] [messageid: USER-42] [requestid: 5457001c] [sessionid: 54570000] [username: weblogic] -------------------- Query Result Cache: [59123] The query for user 'weblogic' was not inserted into the query result cache.  Additional information (if any): '[nQSError: 52061] The cache entry, /home/oraclebi/Oracle/Middleware/Oracle_Home/user_projects/domains/smis/servers/obis1/cache/NQS__736704_42408_21.TBL, has been corrupted; could not finish the operation in Local row count mismatch. We have attempted to remove this entry.  Please re-issue your query.  If the problem persists, please restart OBIS to resolve the issue.'.

sawlog.log

------------------------

[2018-01-08T12:02:26+05:30] [OBIPS] [ERROR:31] [] [saw.connect.bipsdrilldown] [ecid: ] [tid: 30] [SI-Name: ] [IDD-Name: ] [IDD-GUID: ] [userId: weblogic] An error occurred while trying to generate drill down query.

Odbc driver returned an error (SQLExecDirectW).

State: HY000.  Code: 10058.  [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.

(HY000)

State: HY000.  Code: 12010.  [nQSError: 12010] Communication error connecting to remote end point: address = ubiuat; port = 9514. (HY000)

SQL Issued: {CALL NQSGenerateDrillDownQuery('SELECT "Division Hierarchy".HO saw_11, CASE "Fact - Productivity"."Data Level" WHEN ''ALL'' THEN ''ALL'' WHEN ''CATGRP'' THEN "Fact - Productivity"."Sify Category Group" WHEN ''CAT'' THEN "Fact - Productivity"."Category Desc" WHEN ''SUB'' THEN "Product"."Category" END saw_20, CASE "Fact - Productivity"."Data Level" WHEN ''ALL'' THEN ''ALL'' WHEN ''CATGRP'' THEN "Fact - Productivity"."Sify Category Group" WHEN ''CAT'' THEN "Fact - Productivity"."Category Desc" WHEN ''SUB'' THEN "Fact - Productivity"."Category Desc" END saw_6, Case when "Fact - Productivity"."Data Level"=''SUB'' then "Fact - Productivity".Brand else '' '' end saw_19 FROM "Productivity" WHERE ("Fact - Productivity"."Data Level" = ''ALL'') AND ("Fact - Productivity"."Bucket Period Value" IN (''22 to EOM'', ''1 to 7'', ''15 to 21'', ''8 to 14'')) AND (Time."Year" = ''2016-2017'') AND (Time."Month" = ''January'') AND ("Fact - Productivity"."DS Type" = ''ALL DS TYPES'')','0','''ITC-ITD'',''ALL'',''ALL'',','-1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1','')}[[

File:drilldownutils.cpp

Line:1972

Location:

saw.connect.bipsdrilldown

saw.views.evc.activate

saw.httpserver.processrequest

saw.rpc.server.responder

saw.rpc.server

saw.rpc.server.handleConnection

saw.rpc.server.dispatch

saw.threadpool.socketrpcserver

saw.threads

RSP: d:dashboard~p:mmccpct0vv4kj2vc~r:n8go48vv1o570k9u

HttpCommand: ReloadDashboard

SID: p24cs3hqad59i9qio3avkr6sda

AuthProps: AuthSchema=UidPwd|PWD=******|UID=weblogic|User=weblogic|GUID=weblogic

SessionID: 10.1.145.65.e3.veh3fo0

RptPath: /shared/Snd Productivity Measures Reports/Snd Productivity Measures Reports/SPM Enhancement/Direct Sify SPM-Branch

RemoteIP: 10.1.145.65

SIKey: ssi

]]

[2018-01-08T12:02:38+05:30] [OBIPS] [ERROR:31] [] [saw.views.evc.activate] [ecid: ] [tid: 30] [SI-Name: ] [IDD-Name: ] [IDD-GUID: ] [userId: weblogic] Error getting drill information: SELECT "Division Hierarchy"."HO" saw_0, CASE "Fact - Productivity"."Data Level" WHEN 'ALL' THEN 'ALL' WHEN 'CATGRP' THEN "Fact - Productivity"."Sify Category Group" WHEN 'CAT' THEN "Fact - Productivity"."Category Desc" WHEN 'SUB' THEN "Fact - Productivity"."Category Desc" END saw_1, "Fact - Productivity"."Brand" saw_2, "Fact - Productivity"."Bucket Period Value" saw_3, CASE "Fact - Productivity"."Bucket Period Value" WHEN '1 to 7' THEN 1 WHEN '8 to 14' THEN 2 WHEN '15 to 21' THEN 3 WHEN '22 to EOM' THEN 4 WHEN '1 to 14' THEN 5 WHEN '1 to 21' THEN 6 WHEN '1 to EOM' THEN 7 ELSE 8 END saw_4, "Time"."Year" saw_5, "Time"."Month" saw_6, COUNT(DISTINCT "WD"."WD Destination Code") saw_7, "Fact - Productivity"."Total Outlet Nos. Mapped to DS - TO" saw_8, "Fact - Productivity"."Total Bill Value (TBV)" saw_9, "Fact - Productivity"."Total Bill Value (TBV)"/"Fact - Productivity"."Total Bills Cut - TBC" saw_10, ("Fact - Productivity"."Total No. of Lines Cut - TLC"*1.0)/"Fact - Productivity"."Total Bills Cut - TBC" saw_11, "Fact - Productivity"."No. of Unique Outlets Billed - OB"/"Fact - Productivity"."Total Outlet Nos. Mapped to DS - TO"*100.0 saw_12, "Fact - Productivity"."No. of Visit" saw_13, ("Fact - Productivity"."Total Bills Cut - TBC"*1.0)/

("Fact - Productivity"."No. of Visit"*"Fact - Productivity"."Total Outlet Nos. Mapped to DS - TO")*100.0 saw_14, CASE "Fact - Productivity"."Data Level" WHEN 'ALL' THEN 'ALL' WHEN 'CATGRP' THEN "Fact - Productivity"."Sify Category Group" WHEN 'CAT' THEN "Fact - Productivity"."Category Desc" WHEN 'SUB' THEN "Product"."Category" END saw_15 FROM "Productivity" WHERE ("Time"."Year" = '2017-2018') AND ("Time"."Month" = 'January') AND ("Fact - Productivity"."Data Level" = 'ALL') AND ("Fact - Productivity"."Bucket Period Value" IN ('1 to 7', '8 to 14', '15 to 21', '22 to EOM')) AND ("Fact - Productivity"."DS Type" = 'ALL DS TYPES') AND (IFNULL(Product.Category,'UNSPECIFIED') IS NOT NULL) AND (IFNULL(Product."Sub Category",'UNSPECIFIED') IS NOT NULL)

Odbc driver returned an error (SQLSetStmtAttrW).

State: 08004.  Code: 10092.  [NQODBC] [SQL_STATE: 08004] [nQSError: 10092] The Oracle BI Server is not currently running.

(08004)

State: HY000.  Code: 12008.  [nQSError: 12008] Unable to connect to port 9514 on machine ubiuat. (HY000)

SQL Issued: {call NQSGetLevelDrillability('SELECT "Division Hierarchy"."HO" saw_0, CASE "Fact - Productivity"."Data Level" WHEN ''ALL'' THEN ''ALL'' WHEN ''CATGRP'' THEN "Fact - Productivity"."Sify Category Group" WHEN ''CAT'' THEN "Fact - Productivity"."Category Desc" WHEN ''SUB'' THEN "Fact - Productivity"."Category Desc" END saw_1, "Fact - Productivity"."Brand" saw_2, "Fact - Productivity"."Bucket Period Value" saw_3, CASE "Fact - Productivity"."Bucket Period Value" WHEN ''1 to 7'' THEN 1 WHEN ''8 to 14'' THEN 2 WHEN ''15 to 21'' THEN 3 WHEN ''22 to EOM'' THEN 4 WHEN ''1 to 14'' THEN 5 WHEN ''1 to 21'' THEN 6 WHEN ''1 to EOM'' THEN 7 ELSE 8 END saw_4, "Time"."Year" saw_5, "Time"."Month" saw_6, COUNT(DISTINCT "WD"."WD Destination Code") saw_7, "Fact - Productivity"."Total Outlet Nos. Mapped to DS - TO" saw_8, "Fact - Productivity"."Total Bill Value (TBV)" saw_9, "Fact - Productivity"."Total Bill Value (TBV)"/"Fact - Productivity"."Total Bills Cut - TBC" saw_10, ("Fact - Productivity"."Total No. of Lines Cut - TLC"*1.0)/"Fact - Productivity"."Total Bills Cut - TBC" saw_11, "Fact - Productivity"."No. of Unique Outlets Billed - OB"/"Fact - Productivity"."Total Outlet Nos. Mapped to DS - TO"*100.0 saw_12, "Fact - Productivity"."No. of Visit" saw_13, ("Fact - Productivity"."Total Bills Cut - TBC"*1.0)/

("Fact - Productivity"."No. of Visit"*"Fact - Productivity"."Total Outlet Nos. Mapped to DS - TO")*100.0 saw_14, CASE "Fact - Productivity"."Data Level" WHEN ''ALL'' THEN ''ALL'' WHEN ''CATGRP'' THEN "Fact - Productivity"."Sify Category Group" WHEN ''CAT'' THEN "Fact - Productivity"."Category Desc" WHEN ''SUB'' THEN "Product"."Category" END saw_15 FROM "Productivity" WHERE ("Time"."Year" = ''2017-2018'') AND ("Time"."Month" = ''January'') AND ("Fact - Productivity"."Data Level" = ''ALL'') AND ("Fact - Productivity"."Bucket Period Value" IN (''1 to 7'', ''8 to 14'', ''15 to 21'', ''22 to EOM'')) AND ("Fact - Productivity"."DS Type" = ''ALL DS TYPES'') AND (IFNULL(Product.Category,''UNSPECIFIED'') IS NOT NULL) AND (IFNULL(Product."Sub Category",''UNSPECIFIED'') IS NOT NULL)')}[[

nqcluster.log

---------------------------------------

[2018-01-08T12:02:26.624+05:30] [OBICCS] [NOTIFICATION:1] [] [] [ecid: ] [sik: ] [tid: 2]  [71019] The connection with Oracle BI Server ubiuat:9514 (monitor port 9515) was lost.

[2018-01-08T12:02:26.625+05:30] [OBICCS] [NOTIFICATION:1] [] [] [ecid: ] [sik: ] [tid: 2]  [71011] Oracle BI Server ubiuat:9514 has transitioned to offline state.

[2018-01-08T12:02:31.645+05:30] [OBICCS] [NOTIFICATION:1] [] [] [ecid: ] [sik: ] [tid: 2]  [71020] A connection with Oracle BI Server ubiuat:9514 (monitor port 9515) was established.

[2018-01-08T12:02:38.49+05:30] [OBICCS] [ERROR:1] [] [] [ecid: ] [sik: ] [tid: 8]  [71096] The client with tenant ':ssi' (request type: 0) need to wait for node 'ubiuat:9514' to be ready.

[2018-01-08T12:02:38.72+05:30] [OBICCS] [ERROR:1] [] [] [ecid: ] [sik: ] [tid: 8]  [71096] The client with tenant ':ssi' (request type: 0) need to wait for node 'ubiuat:9514' to be ready.

[2018-01-08T12:02:47.23+05:30] [OBICCS] [ERROR:1] [] [] [ecid: ] [sik: ] [tid: 8]  [71096] The client with tenant ':ssi' (request type: 0) need to wait for node 'ubiuat:9514' to be ready.

[2018-01-08T12:02:53.137+05:30] [OBICCS] [ERROR:1] [] [] [ecid: ] [sik: ] [tid: 8]  [71096] The client with tenant ':ssi' (request type: 0) need to wait for node 'ubiuat:9514' to be ready

Christian Berg-0racle

So a whole host of issues.

a) kill all your cache to get rid of all corrupt entries

b) check all your timeout settings of the connection pools - especially the ITC Conn Pools

c) as for "The Oracle BI Server is not currently running" you can start attacking this once the other isseues are gone