April 18, 2009 Production Materialized views creation

Owned by Prahbu E Pilli
Last updated: Apr 16, 2009Version comment

April 18, 2009 

Confirm maintenance mode

  • We need to allow access from dedicated IPs for possible pre-upgrade work
  • Adjust iptables on production nodes to allow JMX access to port 9000 for specified IPs
    • PORT 8443:

# 169.237.11.246 (thomas)

# 69.12.224.247 (thomas)

# 169.237.97.91 (zoidberg: vpn, terminal server)

# 169.237.97.133 (kirk)

# 169.237.11.220 (james)

# 169.237.11.130 (sandra)

# 169.237.11.6 (jon)

# 169.237.11.242 (mike)

# 169.237.11.37 (Pete Peterson)

# 169.237.205.242 (Nancy Wilkison)

# OTHERS

  • PORT 9000:

# 169.237.11.246

# OTHERS

  • (00:00 ? Joncarlo/ Patching sysadmin) Shutdown all 7 sakai app servers and hammer for OS patching
  • (00:00 ? Joncarlo/ Patching sysadmin) Inform Prabhu when hammer is patched and returned for database maintenance.
  • (04:30 Prabhu)  scheduled full backup begins. confirm.
  • (05:25 Prabhu) Confirm scheduled full backup is complete. Stop log tranfer.
  • (05:30 Prabhu) Run the sql script for SAK-2695 from https://mware.ucdavis.edu/svn/ucd-sakai/maintenance/db-scripts/SAK-2695.sql
  • (05:35 Prabhu) Run the script to collect the row counts of all the mviews.
  • (05:40 Prabhu) Drop mviews in SAKAI_EXTERNAL schema and in UCDSOM schema.
  • (05:50 Banner DBA) Drop and create mview logs in banner production.
  • (06:05 Prabhu) Create mviews in SAKAI_EXTERNAL schema and in UCDSOM schema. This step includes SAK-2661 changes.
  • (07:00 Prabhu) Run the script to collect the row counts of all the mviews and compare with the row count taken prior to drop mviews.
  • (07:05 Prabhu) Run job execution script and monitor mview script to identify any failures. 
  • (07:15 Prabhu) Recompile views/packages in SAKAI_EXTERNAL schema and in UCDSOM schema.
  • (07:20 Prabhu) Run TB script, sakai_feed script, and drop_stats script. 
  • (07:25 Prabhu ) Run the sql statement below and make sure the result is '0' , else run SAK-2695 from https://mware.ucdavis.edu/svn/ucd-sakai/maintenance/db-scripts/SAK-2695.sql

            SELECT COUNT(1) FROM SAKAI_DYNAMIC_CONFIGURATION WHERE ITEM_KEY='org.sakaiproject.sitemanage.GuestAccessInfoMap';

  • (07:35 Prabhu) Resume log transfer.
  • (07:40 Prabhu) Inform joncarlo/Patching sysadmin about completion of database maintenance.
  • (08:00 Joncarlo/automatic Exit maintenance mode

Fall-back Procedure (If needed)

  • If the ddl's can't be run, restore from the back ups.  This is temporary until we can resolve the issues.

TEST PLAN

  • (Sandra and Kirk) Simple functionality
    • Log in.
    • Visit at least 1 course site and be sure provided students are visible
    • Visit one Section info and see that section data is visible
    • View course site  to see if meeting times can be viewed for Monday sections.  See sak-2661 jira, attached word document, for sample sites.