OBIEE System Administrator's Troubleshooting Toolkit

Transcript

1. info@rittmanmead.com www.rittmanmead.com @rittmanmead 1
2. info@rittmanmead.com www.rittmanmead.com @rittmanmead OBIEE System Administrator's Troubleshooting Toolkit Francesco Tisiot, Principal Consultant, Rittman Mead 2
3. info@rittmanmead.com 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: francesco.tisiot@rittmanmead.com • Twitter: @FTisiot
4. info@rittmanmead.com 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
5. info@rittmanmead.com www.rittmanmead.com @rittmanmead Why Troubleshooting Toolkit? 5 • Real Problems • Correct Tools - > Correct Job • Prevention • “Fun” IRC (freenode | #obihackers)
6. info@rittmanmead.com www.rittmanmead.com @rittmanmead Chaos 6
7. info@rittmanmead.com 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
8. info@rittmanmead.com 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
9. info@rittmanmead.com www.rittmanmead.com @rittmanmead 9 Controlled Environment
10. info@rittmanmead.com www.rittmanmead.com @rittmanmead Controlled Environment 10 • OBIEE logs • OS monitoring • DB monitoring • Usage Tracking • Instrumenting Database Connections
11. info@rittmanmead.com www.rittmanmead.com @rittmanmead Performance Analytics Dashboard 11 https://www.rittmanmead.com/performance-analytics/
12. info@rittmanmead.com www.rittmanmead.com @rittmanmead We're Only Getting Started! 12
13. info@rittmanmead.com www.rittmanmead.com @rittmanmead Who Deleted my Analysis? 13
14. info@rittmanmead.com www.rittmanmead.com @rittmanmead Log Levels 14 Analysis XML
15. info@rittmanmead.com 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/ <Writer implementation="FileLogWriter" name="RM Presentation Catalog Audit" disableCentralControl="true" writerClassId="6" dir="{%ORACLE_BIPS_INSTANCE_LOGDIR%}" filePrefix="rm_pres_cat_audit" maxFileSizeKb="10240" filesN="10" fmtName="ODL-Text"/> <WriterClassGroup name="RMLog">6</WriterClassGroup> <FilterRecord writerClassGroup="RMLog" disableCentralControl="true" path="saw.catalog.local.moveItem" information="32" warning="32" error="32" trace="32" incident_error="32"/> <FilterRecord writerClassGroup="RMLog" disableCentralControl="true" path="saw.catalog.local.deleteItem" information="32" warning="32" error="32" trace="32" incident_error="32"/>
16. info@rittmanmead.com www.rittmanmead.com @rittmanmead Move Object 16 New Analysis Name Action Original Analysis Name User Details IP Address
17. info@rittmanmead.com www.rittmanmead.com @rittmanmead Deleted Object 17 Action Original Analysis Name User Details IP Address • Push to DB • Expose • Review
18. info@rittmanmead.com www.rittmanmead.com @rittmanmead Did You Say Catalog Problems?
 18
19. info@rittmanmead.com www.rittmanmead.com @rittmanmead I Can’t Access my Folder? 19
20. info@rittmanmead.com 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)
21. info@rittmanmead.com 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 <path_to_catalog> > cat_validation.log
22. info@rittmanmead.com www.rittmanmead.com @rittmanmead Session Variables?
 22
23. info@rittmanmead.com 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
24. info@rittmanmead.com www.rittmanmead.com @rittmanmead System Session Variables 24 • DISPLAYNAME • SKIN • TIMEZONE • USERLOCALE
25. info@rittmanmead.com 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.
26. info@rittmanmead.com 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
27. info@rittmanmead.com www.rittmanmead.com @rittmanmead NQS Calls 27 • SASeedQuery • SAPurgeCacheByTable • SAPurgeCacheByDatabase • NQSSetSessionValue • NQS_SetRPDReadOnlyMode
28. info@rittmanmead.com www.rittmanmead.com @rittmanmead OBIS_DIAGNOSTICS_ENABLED 28 NQS_GETCurrentActivitiesProcedure()
29. info@rittmanmead.com www.rittmanmead.com @rittmanmead 29 OBIS_DIAGNOSTICS_ENABLED
30. info@rittmanmead.com 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
31. info@rittmanmead.com www.rittmanmead.com @rittmanmead 31 NQS Calls NQS_GetAllCacheEntries SAGetXSACacheEntries
32. info@rittmanmead.com www.rittmanmead.com @rittmanmead 32 NQS Calls - New in 12c NQS_LockSessionAgainstAutoRPSwitchOver NQS_SwitchOverThisSessionToNewRP
33. info@rittmanmead.com www.rittmanmead.com @rittmanmead SSH? 33
34. info@rittmanmead.com www.rittmanmead.com @rittmanmead SSH Sessions 34
35. info@rittmanmead.com www.rittmanmead.com @rittmanmead Screen 35 • Session Persistence • Multiple Sessions • Scrollback history
36. info@rittmanmead.com www.rittmanmead.com @rittmanmead Analysis Tools 36
37. info@rittmanmead.com 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
38. info@rittmanmead.com www.rittmanmead.com @rittmanmead Cluster 38
39. info@rittmanmead.com 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
40. info@rittmanmead.com 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
41. info@rittmanmead.com 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/
42. info@rittmanmead.com www.rittmanmead.com @rittmanmead This all looks normal… where are the hacks?
 42
43. info@rittmanmead.com 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.
44. info@rittmanmead.com 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)"
45. info@rittmanmead.com 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
46. info@rittmanmead.com www.rittmanmead.com @rittmanmead From New Browser 46 Missing Cookie? How to Test? Google Chrome Developer Tool
47. info@rittmanmead.com 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?
48. info@rittmanmead.com 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
49. info@rittmanmead.com www.rittmanmead.com @rittmanmead Parsing the Cookie 49 - Calling cURL from terminal - --cookie-jar option - grep/awk
50. info@rittmanmead.com www.rittmanmead.com @rittmanmead 50
51. info@rittmanmead.com 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.
52. info@rittmanmead.com www.rittmanmead.com @rittmanmead 52 Questions? How Italians Ask Questions?
53. info@rittmanmead.com www.rittmanmead.com @rittmanmead 53