Database Administration for Vista: Lessons Learned

1. Database Administration for Vista: Lessons Learned George Hernandez Amy Edwards Ezra Freelove University System of Georgia, Board of Regents July 11, 2007

2. Audience • Database Administrators • Application Administrators • Powerlink Developers

3. Who’s in the Audience? • Vista 3.x? • Vista 4.x/CE6.x? • BB Academic Suite?

4. Panel Goals • Share Perspectives • Knowledge Exchange • Stress Elemental View of Technology Stack

5. Agenda • What’s Vista? • Component Specifics • Database Performance • Backup and Recovery • Reporting • Capacity Planning • New Stuff • Reflections • Queries

6. Our Team • Ezra : <2yrs DBA experience, 10+ IT • Amy: <4yrs DBA experience, 20+ IT • George: 9+yrs DBA Experience, 12+ IT

7. Our Deployment History • Various individual and shared CE installs • Beta’d 1.x • Almost sank with 2.x • Stabilized on 3.x • 4.x appservicepack wha??

8. Our Architecture

9. What is a DBA? • Database Management • Application Installation/Administration • Incident response • Technology Cynic

10. High Level Vista Opinions • Incredibly complex • Highly functional from a use perspective • Tedious system practice • Stated from the outset with a split personality

11. Vista 2.x/3.x Split Schemas

12. Nasty Content • Assessments/Surveys • Discussions • Mail • Grades

13. Hierarchy and B+Trees • Folderpaths are elemental to the system • Rich content delivery requires expensive access methods • CMSDK data structures meant to be accessed via Java • “wicked” nested records

14. What is iFS/CMSDK? • Obfuscate mime or raw content in the db • Control Access via Java security level • Deliver content via secure dbtier • Scalability of Oracle • Granular content controls

15. Why I Think it Didn’t Make it to 4.x • Licensure • Complexity • Bias • Not portable to other databases

16. Common Traits of the Vista Family • Content stored in BLOBs • Tracking • Record Management for: • Hiearchy • Users • Security

17. Performance Issues • Typically originate from user actions on the apptier • Long data retention means new sql explain plans • Murky performance indicators

18. Things to Check • Statspack and archived performance metrics • Normal maintenance • Run suspect functionality in quiet systems and observe • Profile of system or users

19. How We Identify Database Issues • Read and understand statspack reports • Examine specific metrics on • cpu utilization • wait events • Latches • SQL execution • Run captured queries manually • Provide data to support • Be Wary of First Assumptions

20. Tools We Use • SqlPlus • Statspack • Tora • SqlDeveloper • Nagios • Bash,Excel

21. Database Observations • High transactional volume OLTP system • Oracle’s concurrency model handles most issues • Schema design is mostly rational • High number of obscure Oracle components in use • Java/XML in the database • BLOBs • Oracle Text

22. Backups • Impact to the online system/experience • Timing • Adequate sub-architecture

23. Recovery • Backup times versus recoverability • How to handle logical Vista corruptions? • Testing your recovery plan

24. Vista Recovery • Sincere admiration for deployments who have it enabled • Expensive resource-wise • Unique nuances of academic data

25. Archives v. Backups • Backups • Must be secure • Should be user-controlled • Archives • Should be platform independent • Full application functionality not required • All instructional data spooled

26. Reporting • Active Sections • Active Users • Tracking Audits • SIS synchronization

27. Audits • Session debugging • Academic misconduct • Research

28. Ad Hoc Reporting • Metrics grouped by role • Content distribution* • Template use metrics • Issue Triage

29. VPAR • Attempt/Failure at warehousing tracking data and related schema objects • Powersight data not deep enough • 2.0 in early discussion

30. Tools Used • SchemaSpy • Ifsbrowser/ifsshell • HTML Db • Oracle Warehouse Builder • PL/SQL

31. DB Capacity Planning • Transaction Volume • Redo Volume • Trending previous growth

32. What We Did • Doubled DB resources every refresh • We’ve reached the limit of monolithic servers • More sizing work for RAC planning

33. The Road Ahead • Not Clear • V4 migration elapsed times not feasible • V3 aging somewhat gracefully but doesn’t have the longevity of CE

34. Click What Our Last Major Fault Taught Us • We can scale the apptier dynamically, but to what effect? • No means to dynamically scale monolithic dbserver architectures • Content store size isn’t our only liability

35. Our Response • Froze all Vista 4.x plans • RAC Planning • Virtualization Planning • Assess full disaster recovery capabilities

36. RAC • 10gr2 only • Oracle matured, efficient hardware plentiful • Significant system administration changes

37. Virtualization • Logical extension from our large appfarms • Node “build” process pre-existing • Dynamic resource response via staged node images • Assumption that Virtualization is the best means to utilized shared chassis hardware architectures

38. Disaster Recovery • Current • Physical standby on production scale hardware • BCV splits of standby • Delayed tape spool • Possible • Multiple standby targets with staged virtualized node images • Two fully physical sites already in use

39. Things to Read • Vista 2.x-4.x Powerlink dev guides • Powersight Kit Guide • 9.0.1-9.0.4 CMSDK javadoc • “The Data Warehouse Toolkit” by Richard Kimball • “Oracle SQL High-Performance Tuning” by Guy Harrison • Oracle Technology forums • Bb wiki • Newsgroups • HiveMind/Tapesty framework docs

40. That’s it, the rest is sql

41. Queries • Full Tracking Audit set lines 220 pages 8000 col user format a20 col action format a30 col pagename format a60 col lcname format a50 clear breaks computes break on User skip 1 compute count of Action on User select tp.user_name "User",ta.name "Action",to_char(tua.event_time,'MM/DD/RR HH24:MI') "Time", tpg.name "PageName", lc.name "LCName", lc.id "LC_Id" from trk_person tp, trk_action ta, trk_user_action tua, trk_page tpg, learning_context lc where tp.id = tua.trk_person_id and ta.id = tua.trk_action_id and tua.trk_page_id = tpg.id and tua.trk_learning_context_id = lc.id and lc.id = '__' order by tp.user_name,tua.event_time /

42. Queries • Active Vista User count select 'ACTIVE='||a.active||chr(10)||'TOTAL='||b.logged_in from ( select count(1) active from vista.wio_user_information where last_access_time>sysdate - 1/192) a, ( select count(1) logged_in from vista.wio_user_information) b /

43. Queries • SIS Synchronization select webct_id||','||remote_userid||','||sourcedid_source from person where webct_id not like 'webct_demo%' and learning_context_id = {learning_context.id of inst} /

44. Queries • Active Users select lc.name||','||count(1) from person p,learning_context lc where lc.id=p.learning_context_id and p.id in (select distinct trk_person_id from trk_user_action where event_time between '11-MAY-07' and '06-AUG-07’ ) group by lc.name /

45. Queries • Active Sections select lc.name||','||lcs.name||','||lcs.source_id||','||count(1) from learning_context lc, learning_context lcs, trk_user_action tua, (select right_lc_id,left_lc_id from learning_context_index where left_lc_id in (select id from learning_context where typelevel='20')) lci where lc.id = lci.left_lc_id and tua.trk_learning_context_id=lci.right_lc_id and lcs.id = lci.right_lc_id and lcs.typelevel='90' and tua.event_time between '11-MAY-07' and '06-AUG-07' group by lc.name,lcs.name,lcs.source_id having count(*) > 100 /

46. Want More? • To view my resources and references for this presentation, visit www.scholar.com • Simply click “Advanced Search” and search by my username and tag: ‘bbworld07’ Sample Scholar Page “Personalize and use this page if you’re using Scholar – then delete this box. Delete this whole page if you’re not using Scholar!”