1 / 46

Database Administration for Vista: Lessons Learned

Database Administration for Vista: Lessons Learned. George Hernandez Amy Edwards Ezra Freelove University System of Georgia, Board of Regents July 11, 2007. Audience. Database Administrators Application Administrators Powerlink Developers. Who’s in the Audience?. Vista 3.x?

Download Presentation

Database Administration for Vista: Lessons Learned

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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!”

More Related