[email protected] www.rittmanmead.com @rittmanmead 1 

[email protected] www.rittmanmead.com @rittmanmead OBIEE System Administrator's Troubleshooting Toolkit Francesco Tisiot, Principal Consultant, Rittman Mead 2 

[email protected] www.rittmanmead.com @rittmanmead About the Speaker 3 • Francesco Tisiot • Principal Consultant at Rittman Mead • Based in Verona, Italy • Writer for Rittman Mead Blog - http://www.rittmanmead.com/blog/ • Over 9 years of experience in BI • Email: [email protected] • Twitter: @FTisiot 

[email protected] www.rittmanmead.com @rittmanmead About the Rittman Mead 4 •Oracle BI and DW Gold partner •Winner of several UKOUG Partner of the Year awards including BI •World leading specialist partner for technical excellence, 
 solutions delivery and innovation in Oracle BI •Approximately 100 consultants worldwide •Experts in BI, DW and Big Data •Offices in US (Atlanta), Europe •Skills in broad range of supporting tools: - OBIEE, OBIA, Essbase, Oracle OLAP - GoldenGate, ODI - Big Data • Partnership with Cloudera and Amazon 

[email protected] www.rittmanmead.com @rittmanmead Why Troubleshooting Toolkit? 5 • Real Problems • Correct Tools - > Correct Job • Prevention • “Fun” IRC (freenode | #obihackers) 

[email protected] www.rittmanmead.com @rittmanmead Chaos 6 

[email protected] www.rittmanmead.com @rittmanmead Chaos 7 How many users are connected? Where is my analysis? Who deleted my file? OBIEE is not working!!!!!!!! My analysis was working 5 minutes ago! What’s happening at 11AM? How much space do I have left for my analysis Can I have the interface to upload the RPD back? Error Message says to contact the Administrator!!!! URGNET!!!!!!!!!!! 1111!!!!!!!!!!!! DO THE NEEDFUL!!!! Blog ABC says to change this parameter to 42 

[email protected] www.rittmanmead.com @rittmanmead Chaos 8 Can you have a look at my analysis “ABC”? it’s not working! Ok I’ll have a look at it at 10AM Is working now! Thanks, what did you do??? 8.45AM 9.00AM 9.01AM 

[email protected] www.rittmanmead.com @rittmanmead 9 Controlled Environment 

[email protected] www.rittmanmead.com @rittmanmead Controlled Environment 10 • OBIEE logs • OS monitoring • DB monitoring • Usage Tracking • Instrumenting Database Connections 

[email protected] www.rittmanmead.com @rittmanmead Performance Analytics Dashboard 11 https://www.rittmanmead.com/performance-analytics/ 

[email protected] www.rittmanmead.com @rittmanmead We're Only Getting Started! 12 

[email protected] www.rittmanmead.com @rittmanmead Who Deleted my Analysis? 13 

[email protected] www.rittmanmead.com @rittmanmead Log Levels 14 Analysis XML 

[email protected] www.rittmanmead.com @rittmanmead Custom Log Filters for Presentation Catalog 15 • Changes in instanceconfig.xml ‣ Writer ‣ WriterClassGroup ‣ Filter ‣ sawserver -logsources https://www.rittmanmead.com/blog/2014/11/auditing-obiee-presentation-catalog-activity-with-custom-log-filters/ 6 

[email protected] www.rittmanmead.com @rittmanmead Move Object 16 New Analysis Name Action Original Analysis Name User Details IP Address 

[email protected] www.rittmanmead.com @rittmanmead Deleted Object 17 Action Original Analysis Name User Details IP Address • Push to DB • Expose • Review 

[email protected] www.rittmanmead.com @rittmanmead Did You Say Catalog Problems?
 18 

[email protected] www.rittmanmead.com @rittmanmead I Can’t Access my Folder? 19 

[email protected] www.rittmanmead.com @rittmanmead Catalog Validation 20 •Check of Catalog Corruptions • Pre-Upgrade, Post-Upgrade or on Daily Basis • ValidateAccounts • ValidateHomes • ValidateItems • ValidateLinks https://www.rittmanmead.com/blog/2016/11/performing-a-catalog-validation/ OBIEE 12c: Catalog Validation "OBIPS startup catalog validate is not supported/ deprecated." (Doc ID 2199938.1) 

[email protected] www.rittmanmead.com @rittmanmead And in 12c? 21 • Doc ID 2199938.1 • runcat.sh -cmd validate ./runcat.sh -cmd validate -items Report -links Report -accounts Report -homes Report -offline > cat_validation.log 

[email protected] www.rittmanmead.com @rittmanmead Session Variables?
 22 

[email protected] www.rittmanmead.com @rittmanmead System Presentation Variables 23 • session.locale • session.language • currency.symbol • dashboard.currentPage • report.currency.symbol http://host:9704/analytics/saw.dll?Dashboard&PortalPath=YourPath&locale=en-us&lang=en 

[email protected] www.rittmanmead.com @rittmanmead System Session Variables 24 • DISPLAYNAME • SKIN • TIMEZONE • USERLOCALE 

[email protected] www.rittmanmead.com @rittmanmead Safe Harbour Statement 25 None of these settings are documented, and they should therefore be assumed to be completely unsupported by Oracle. This presentation is purely for geek interest. Using undocumented APIs leaves you at risk of the API changing at any time. 

[email protected] www.rittmanmead.com @rittmanmead NQS Calls 26 • Kudos to Christian Berg ‣ Neo’s voyage in OBIEE https://www.slideshare.net/ChristianBerg8/oracle-open-world-neos-voyage-2014 ‣ Exposes NQS Calls • call NQSGetSQLProcedures(‘%’,'%','%') • call NQSGetSQLProcedureColumns(‘%’,’%’,’%’,’%’) • 120 NQS Calls in 12.2.1.1.0 ‣ Cache Cleaning ‣ Retrieving Information from the Repository ‣ ...and things better not touched 

[email protected] www.rittmanmead.com @rittmanmead NQS Calls 27 • SASeedQuery • SAPurgeCacheByTable • SAPurgeCacheByDatabase • NQSSetSessionValue • NQS_SetRPDReadOnlyMode 

[email protected] www.rittmanmead.com @rittmanmead OBIS_DIAGNOSTICS_ENABLED 28 NQS_GETCurrentActivitiesProcedure() 

[email protected] www.rittmanmead.com @rittmanmead 29 OBIS_DIAGNOSTICS_ENABLED 

[email protected] www.rittmanmead.com @rittmanmead 30 NQS Calls NQS_GETCurrentActivitiesProcedure •DbGateway Prepare - DB Connection •Executing Query ‣ Logical Hash of SQL ‣ SQL Sent ‣ Source Analysis •Post Aggregation 

[email protected] www.rittmanmead.com @rittmanmead 31 NQS Calls NQS_GetAllCacheEntries SAGetXSACacheEntries 

[email protected] www.rittmanmead.com @rittmanmead 32 NQS Calls - New in 12c NQS_LockSessionAgainstAutoRPSwitchOver NQS_SwitchOverThisSessionToNewRP 

[email protected] www.rittmanmead.com @rittmanmead SSH? 33 

[email protected] www.rittmanmead.com @rittmanmead SSH Sessions 34 

[email protected] www.rittmanmead.com @rittmanmead Screen 35 • Session Persistence • Multiple Sessions • Scrollback history 

[email protected] www.rittmanmead.com @rittmanmead Analysis Tools 36 

[email protected] www.rittmanmead.com @rittmanmead Problem: Default Colours for Graphs not Working 37 OBIPS OBIJH Chart Request Chart Rendering Apply Default Colors Build Graph Request Analysis Analysis Rendering viewui/chart/dvt-graph-skin.xml CustomerResourcePhysicalPath 

[email protected] www.rittmanmead.com @rittmanmead Cluster 38 

[email protected] www.rittmanmead.com @rittmanmead Analysing Traffic 39 Tcpdump ‣ Analysis of TCP Packets ‣ Intra Components Traffic • OBIPS to JavaHost • port 9810: JavaHost • Flag A: ASCII representation of the packet 

[email protected] www.rittmanmead.com @rittmanmead Analysing Traffic 40 Tcpdump ‣ Analysis of TCP Packets ‣ Intra Components Traffic • OBIPS to JavaHost • port 9810: JavaHost • Flag A: ASCII representation of the packet 

[email protected] www.rittmanmead.com @rittmanmead Analysing System Calls 41 Strace ‣ Tracing System Calls ‣ Calls to/from any Process ‣ -o: output to a file ‣ -f: follow child processes ‣ -p: process to follow /data/instance1/s_custom/ /data/instance2/s_custom/ 

[email protected] www.rittmanmead.com @rittmanmead This all looks normal… where are the hacks?
 42 

[email protected] www.rittmanmead.com @rittmanmead OBIEE 12c vs OBIEE11g 43 • No more OPMN -> Node Manager • Same black box protocols (sawserver, nqserver) • Same old SOAP webservices • New REST-based webservices None of these Web Services are documented, and they should therefore be assumed to be completely unsupported by Oracle. This presentation is purely for geek interest. Using undocumented APIs leaves you at risk of the API changing at any time. 

[email protected] www.rittmanmead.com @rittmanmead Sysdig 44 • Open Source • Linux system level exploration • -A: Print ASCII, i.e. human-readable data • fd.port=7780: Managed Server • (evt.buffer contains GET or evt.buffer contains POST): GET or POST traffic only sudo sysdig -s 2000 -A "fd.port=7780 and (evt.buffer contains GET or evt.buffer contains POST)" 

[email protected] www.rittmanmead.com @rittmanmead Sysdig Output 45 18168 17:40:42.202935645 0 java (7563) < read res=636 data= GET /va/api/v1/dataset/limits HTTP/1.1 Host: 192.168.56.101:7780 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:53.0) Gecko/20100101 Firefox/53.0 Accept: */* Accept-Language: en-US,en;q=0.5 Accept-Encoding: gzip, deflate Content-Type: application/x-www-form-urlencoded; charset=UTF-8 X-CSRF-Token: bZTrpHmsXqDhqj03HtWOG53dujUpavZrkLtraCppbxQyysJe X-Requested-With: XMLHttpRequest Referer: http://192.168.56.101:7780/va/?pageid=datasources Cookie: JSESSIONID=6JUXECYd13o9Si7pFf8jYT_fTu-u7jkTJLURXsPxzzoRCBlB51wV!-87768635; ORA_BIPS_NQID=tkhrmqr1scmhkba6si8eg362i558136crprqo gcia27beheg Connection: keep-alive 

[email protected] www.rittmanmead.com @rittmanmead From New Browser 46 Missing Cookie? How to Test? Google Chrome Developer Tool 

[email protected] www.rittmanmead.com @rittmanmead Paw 47 REST API client for Mac - Similar to Postman, SoapUI - Copy Request as cURL - Paste into Paw - Complete Control of Headers - Formatted Layout - Provides the cURL Syntax to Export Wonderful…How do I get this Cookie? 

[email protected] www.rittmanmead.com @rittmanmead Getting the Login Cookie 48 - Check Login to /va - URL - Set-Cookie - Form Data (Plain Text - SSL Anyone?) - Import to Paw - Remove headers - Get cURL 

[email protected] www.rittmanmead.com @rittmanmead Parsing the Cookie 49 - Calling cURL from terminal - --cookie-jar option - grep/awk 

[email protected] www.rittmanmead.com @rittmanmead 50 

[email protected] www.rittmanmead.com @rittmanmead Same Stuff 51 ‣ Web Page to Download/Upload RPD ‣ Download Files ‣ Refresh Data Sources ‣ Any other Rest API! None of these Web Services are documented, and they should therefore be assumed to be completely unsupported by Oracle. This presentation is purely for geek interest. Using undocumented APIs leaves you at risk of the API changing at any time. 

[email protected] www.rittmanmead.com @rittmanmead 52 Questions? How Italians Ask Questions? 

[email protected] www.rittmanmead.com @rittmanmead 53