Purging Data and Regaining Space in an Oracle Database

1. Purging Data and Regaining Space in an Oracle Database Panel Discussion July 15, 2010 Sun C 4:15pm-5:00pm

2. Panelists Mike Cooling, Manager of LMS and Portal Services California State University – Sacramento Cheryl McCann, Vista Applications Administrator Connecticut State University System Mary Holmes, PhD., E-Learning Systems Administrator Scott Randle, E-Learning Application SpecialistFerris State University Felicia Kendall, Manager of IT Infrastructure University of Central Florida Tracy Tolliver, Manager, Production Applications Group, CITES Rich White, Service Manager – Illinois Compass University of Illinois at Urbana-Champaign Wonda Yuhasz, e-Learning Coordinator University of Nevada-Las Vegas

3. Panelist Statistics * Only Crosslisted Parent Sections are Counted

4. Space Recovery Analysis Methods • Shrink database • Alter table cms_content_entry enable row movement • Alter table cms_content_entry shrink space Cascade/compact • Alter table cms_content_entry disable row movement • Tablespace Reports

5. Talking Points • User Management • Section Archiving Policy • Requirements • Why do it • Section Management • Deleting Sections from the GUI • Deleting Sections from the Command Line • Action Plan for Purging Sections • Statistics for results of space recovered • Using tablespace report methods • Using shrink database commands

6. Talking Points • Garbage Collection Process • Supporting Documentation • Communication to faculty and students , messages , timing, excel spreadsheet • Processing from GUI • Processing from Command Line • Supporting SQL queries • Supporting Perl scripts • Sample Spreadsheet for analysis

7. User Management Mike Cooling California State University – Sacramento Rich WhiteUniversity of Illinois at Urbana-Champaign

8. User Management • Aggressive removal could impact students taking a semester off or faculty on sabbatical.

9. User Management • Aggressive removal could impact students taking a semester off or faculty on sabatical. • Users can have LOB content not in courses (students with JPG files, faculty using LMS as a PC backup repository).

10. User Management • Aggressive removal could impact students taking a semester off or faculty on sabatical. • Users can have LOB content not in courses (students with JPG files, faculty using LMS as a PC backup repository). • Need to control abuse by setting controls • Space limit for learner (ie. 20 meg) • Space limit for non-learner (ie. 600 meg)

11. User Management • Aggressive removal could impact students taking a semester off or faculty on sabatical. • Users can have LOB content not in courses (students with JPG files, faculty using LMS as a PC backup repository). • Need to control abuse by setting controls • Space limit for learner (i.e.. 20 mg) • Space limit for non-learner (i.e.. 600 mg) • Some kind of User Management Philosophy is important!

12. User Management CSU Sacramento • All students, faculty, and staff are added to LMS each morning.

13. User Management CSU Sacramento • All students, faculty, and staff are added to LMS each morning. • A User Manager “Custom Column” was created called ‘Validated’ to mark current LDAP users.

14. User Management CSU Sacramento • All students, faculty, and staff are added to LMS each morning. • A User Manager “Custom Column” was created called ‘Validated’ to mark current LDAP users. • Weekly updates to users pick up name changes and post current date to Validated

15. User Management CSU Sacramento • All students, faculty, and staff are added to LMS each morning. • A User Manager “Custom Column” was created called ‘Validated’ to mark current LDAP users. • Weekly updates to users pick up name changes and post current date to Validated • Then use GUI for actual deletions in 2 steps.

16. User Management CSU Sacramento

17. User Management CSU Sacramento

18. User Management University of Illinois at Urbana-Champaign • All students, faculty, and staff are added to LMS each morning.

19. User Management University of Illinois at Urbana-Champaign • All students, faculty, and staff are added to LMS each morning. • User ID and name changes are made only when requested via the helpdesk.

20. User Management University of Illinois at Urbana-Champaign • All students, faculty, and staff are added to LMS each morning. • User ID and name changes are made only when requested via the helpdesk. • Full load from LDAP is done at the start of each term.

21. User Management University of Illinois at Urbana-Champaign Proposed User Cleanup • Remove all accounts which do not have a membership in a least one learning context.

22. User Management University of Illinois at Urbana-Champaign Proposed User Cleanup • Remove all accounts which do not have a membership in a least one learning context. • Full load from LDAP to restore all the accounts that are still valid.

23. Section Archiving Policy Decisions Tracy Tolliver University of Illinois at Urbana-Champaign

24. Section Management Deleting Sections Using the GUI Mary Holmes, PhD. – Ferris State University Wonda Yuhasz, University of Nevada, Las Vegas

25. UNLV keeps 6 semesters on the system FSU keeps 4 semesters on the system Summer 2009 Fall 2009 Spring 2010 Summer 2010 We would delete all other prior semesters. Section Information

26. Communication • Advance Warning: • Discussions usually occur at the university’s advisory board/coordination committees • Notes to Faculty that deletions will occur • Campus Announcements Page • Deletion of Semester

27. Deletion Process • Go to the Admin Tab • Click on Section Search • Use the following criteria • To remove Spring 2009 • Section Start Date before April 1, 2009 • Select all sections and click on Delete PowerSight Kit used to create an excel file Build XML file from this point Login to the Admin tab and import the file

28. Section Management Deleting Sections Using the Command Line Felicia KendallUniversity of Central Florida Cheryl McCann Connecticut State University System

29. UCF Statistics • 19 node cluster • Sun Solaris 10 • Big IP F5 load balancer • 2.3 TB Oracle 10g database

30. Set up for testing • Worked with Blackboard to come up with a process to check how data if and how much data is purged from the database using delivered and supported procedures • We deleted the first 2 years of data we put on the system. (not a lot of data in the first 2 years). • Process while cumbersome and time consuming worked. It is unlikely that results will be clear in a production instance due to new content being added. We froze our test instance from gaining any new content.

31. CT State University System Statistics • Multi-Institutional Deployment • Four Autonomous Universities • 7 Node Cluster • Linux Red Hat • Redundant F5 Load Balancers • Redundant Cisco ASA 5200 Firewalls • 900 GB Oracle 10g database • Daily database growth = 1.5 GB

32. Process • Run the Check_data_corruption script found at http://kb.blackboard.com/display/WebctKB/Database%20Garbage%20Collection%20configuration?sso=25DC75D4FED972AAADAE18BB52AEB6BA138EC83C69A62BFCC18824400FAB534ABAE10C689CE57A86 • Run queries to see current free space on the database • Per Blackboard Delete sections in small groups (a few hundred sections at a time.)

33. Process • Run the Garbage Collection job several times. There is a query you can run to check how much data Garbage Collection has marked to process. • Run the Check_data_corruption process again and mark differences • Ask the DBA to move the high water mark to show the free space created from course deletion • Run queries to see current free space

34. Deleting Sections… Command Line Action Plan Cheryl McCann Connecticut State University System

35. CSUS Process • Four Institutions to delete from • Universities responsible for communication to user population • Use Term Code to Identify Sections to be deleted • Use Tablespace Reports to estimate recovered space • Sudo –u ooracle tablespace.sh • Data sent to database table • Calculate recovered space by daily growth averages prior to deletions and days of No growth after deletions

36. CSUS Action Plan for Purging • Identify sections proposed for deletion • Estimate the size of the sections to be deleted •  Identify faculty teaching sections to be removed • Send Excel file to University IAs for review/marking exclusions • Export grade books, send to Universities  • Backup sections to be deleted, track following: Date Univ # of Files DB Space Disk Space • Access denied to sections

37. CSUS Action Plan for Purgingcontinued • Verify recovery of a section from archive/backup • Run a script to generate xml files for sections’ deletion (1 file per university), track section counts • Export Learning Context Hierarchy to a file before deletion begins • Check garbage collection logs for successful completion prior to beginning purging process. • Check type of logging for GC, change as appropriate • Gather statistics and log in the table

38. CSUS Action Plan for Purgingcontinued • Import XML files marking sections for deletion • Run Garbage Collection Twice – track time taken and success • Verify sections have been eliminated • Advise DBA of job completion so amount of cleared space can be identified and logged. •  Check nagios for java memory leaks; available memory heap. •  Advise universities purging completed • Analyze Tablespace Reports to calculate space recovered

39. Results… Shrink Database Felicia Kendall UCF Wonda Yuhasz UNLV

40. UCF Results • Shrink Database • Alter table cms_content_entry enable row movement • Alter table cms_content_entry shrink space Cascade/compact • Alter table cms_content_entry disable row movement • No one has done it on Production, only on Test • We recovered 120 Gigs of space in cms_content_entry

41. Shrink Database Considerations • Performance concerns while processing • Measuring the space recovered may not be worth work required • Difficult to estimate how long process will take • Difficult to estimate how much space will be recovered • Process not performed by Bb on their databases • Oracle not a support resource either

42. UNLV Testing of Shrink Command • Cloned production environment to test • Ran the Shrink procedure and reclaimed 200 GB of space (from a 2.1 TB database)

43. Results… Tablespace Reports Cheryl McCann CSUS

44. CSUS Results • Tablespace Reports – Non Growth of Webct_lob

45. Garbage Collection Process Mike Cooling California State University – Sacramento Felicia KendallUniversity of Central Florida

46. Garbage Collection Process • Support.jsp for manual processing • http://kb.blackboard.com/display/WebctKB/Database+Garbage+Collection+configuration • Maximum row limitation in garbagecollection.xml. The above wiki article should be updated with the details by Blackboard. I will check on it after the conference. • Check _data_corruptionhttp://kb.blackboard.com/dosearchsite.action?searchQuery.queryString=check+for+data+corruption&searchQuery.spaceKey=conf_global

47. If there is nothing marked for deletion in cms_content_entry then garbage collection is complete (select count(*) from cms_content_entry where deleted_flag>0;) Explanation of high water mark as it pertains to Garbage Collection http://kb.blackboard.com/display/WebctKB/Understanding+Oracle+Database+Size+and+Garbage+Collection Garbage Collection Processcontinued

48. Take Aways Wonda Yuhasz, e-Learning Coordinator University of Nevada-Las Vegas

49. Take Aways • Supporting Documentation • Documentation stored here: http://tinyurl.com/BbWorldDb • Communication to faculty and students messages, timing, excel spreadsheet • Processing from GUI • Processing from Command Line • Supporting SQL queries • Supporting Perl scripts • Sample Spreadsheet for analysis

50. Contact Information • Mike Cooling – cooling@csus.edu • Mary Holmes – mary_holmes@ferris.edu • Felicia Kendall – felicia@mail.ucf.edu • Cheryl McCann – mccannc@ct.edu • Scott Randle – DonaldRandle@ferris.edu • Tracy Tolliver – ttollive@illinois.edu • Rich White – rlwhite@illinois.edu • Wonda Yuhasz – wonda.yuhasz@univ.edu