Transcript Database Administration for Vista

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
2
Who’s in the Audience?
• Vista 3.x?
• Vista 4.x/CE6.x?
• BB Academic Suite?
3
Panel Goals
• Share Perspectives
• Knowledge Exchange
• Stress Elemental View of Technology
Stack
4
Agenda
•
•
•
•
•
•
•
•
•
5
What’s Vista?
Component Specifics
Database Performance
Backup and Recovery
Reporting
Capacity Planning
New Stuff
Reflections
Queries
Our Team
• Ezra : <2yrs DBA experience, 10+ IT
• Amy: <4yrs DBA experience, 20+ IT
• George: 9+yrs DBA Experience, 12+ IT
6
Our Deployment History
•
•
•
•
•
7
Various individual and shared CE installs
Beta’d 1.x
Almost sank with 2.x
Stabilized on 3.x
4.x appservicepack wha??
Our Architecture
8
What is a DBA?
•
•
•
•
9
Database Management
Application Installation/Administration
Incident response
Technology Cynic
High Level Vista Opinions
•
•
•
•
10
Incredibly complex
Highly functional from a use perspective
Tedious system practice
Stated from the outset with a split
personality
Vista 2.x/3.x Split Schemas
11
AppSchema
(webct/vista)
- records
ContentSchema
(ifssys)
- content
- logical object defs
- content ACLs
- tracking/status
- content rendering
metadata
Nasty Content
•
•
•
•
12
Assessments/Surveys
Discussions
Mail
Grades
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
13
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
14
Why I Think it Didn’t Make it to 4.x
•
•
•
•
15
Licensure
Complexity
Bias
Not portable to other databases
Common Traits of the Vista Family
• Content stored in BLOBs
• Tracking
• Record Management for:
– Hiearchy
– Users
– Security
16
Performance Issues
• Typically originate from user actions on the
apptier
• Long data retention means new sql
explain plans
• Murky performance indicators
17
Things to Check
• Statspack and archived performance
metrics
• Normal maintenance
• Run suspect functionality in quiet systems
and observe
• Profile of system or users
18
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
19
Tools We Use
•
•
•
•
•
•
20
SqlPlus
Statspack
Tora
SqlDeveloper
Nagios
Bash,Excel
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
21
Backups
• Impact to the online system/experience
• Timing
• Adequate sub-architecture
22
Recovery
• Backup times versus recoverability
• How to handle logical Vista corruptions?
• Testing your recovery plan
23
Vista Recovery
• Sincere admiration for deployments who
have it enabled
• Expensive resource-wise
• Unique nuances of academic data
24
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
25
Reporting
•
•
•
•
26
Active Sections
Active Users
Tracking Audits
SIS synchronization
Audits
• Session debugging
• Academic misconduct
• Research
27
Ad Hoc Reporting
•
•
•
•
28
Metrics grouped by role
Content distribution*
Template use metrics
Issue Triage
VPAR
• Attempt/Failure at warehousing tracking
data and related schema objects
• Powersight data not deep enough
• 2.0 in early discussion
29
Tools Used
•
•
•
•
•
30
SchemaSpy
Ifsbrowser/ifsshell
HTML Db
Oracle Warehouse Builder
PL/SQL
DB Capacity Planning
• Transaction Volume
• Redo Volume
• Trending previous growth
31
What We Did
• Doubled DB resources every refresh
• We’ve reached the limit of monolithic
servers
• More sizing work for RAC planning
32
The Road Ahead
• Not Clear
• V4 migration elapsed times not feasible
• V3 aging somewhat gracefully but doesn’t
have the longevity of CE
33
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
34
Our Response
•
•
•
•
35
Froze all Vista 4.x plans
RAC Planning
Virtualization Planning
Assess full disaster recovery capabilities
RAC
• 10gr2 only
• Oracle matured, efficient hardware
plentiful
• Significant system administration changes
36
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
37
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
38
Things to Read
•
•
•
•
•
•
•
•
•
39
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
That’s it, the rest is sql
40
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
/
41
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
/
42
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}
/
43
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
/
44
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
/
45
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!”
46