General Information: Pre-9.1 SP6 Upgrade, V 1.0 -------------- Part of the 9.1 SP6 upgrade applies a change to the gradebook_log table, which may take a long time on larger Oracle databases. To reduce the required downtime, run this gb_log_utility.sql pre-upgrade script on the live production system before taking it down for the 9.1 SP6 upgrade. A 'large' database in this context would be defined as having more than 5 million records in the gradebook_log table. This utility can also be run on systems with fewer records. Log in to SQL*Plus as bblearn (or bb_bb60) and run the following query to find the number of records in the gradebook_log table: select count(1) from gradebook_log; It is recommended that this utility be run during a period of lower system activity, as it will update every row in the gradebook_log table. For customers with more than 5 million records in the gradebook_log table, this utility may take over an hour to complete, for users with 100 million records, the process may last over 1 day. Work with the local Oracle database administrator to ensure the BBLEARN_DATA (BB_BB60_DATA) tablespace has enough space to handle the increase. There will be a small, permanent increase of the gradebook_log table, as a column (datatype: number) will be added. While the utility runs, there will be two full-size gradebook_log tables in addition to whatever resources the database requires during the redefinition process, leading to a temporary increase in needed space in the above mentioned tablespaces. You can use the following query to determine the size of the existing grabook_log table: select SUM(bytes)/1024/1024 GB_LOG_SIZE_MB from user_segments where segment_name ='GRADEBOOK_LOG'; To be on the safe side, ensure that there is at least four times the size of the existing table space free in the BBLEARN_DATA or BB_BB60_DATA tablespace. While the utility is running, the number of Oracle archived redo logs will increase. The "ORA-01555: snapshot too old: rollback segment number ... too small" error may occur. Work with the local Oracle database administrator to ensure there is enough space in the UNDOTBS tablespace. An article, "How To Size UNDO Tablespace For Automatic Undo Management", is available on the Oracle Metalink. Oracle Bug 6970071 affects Oracle 10.2.0.4 with the recyclebin active. The best approach is to apply 10.2.0.5, where the fix is included. Systems running Blackboard Learn application version 9.1 SP4 or 9.1 SP5 may encounter Oracle bug 8239513 and related bug 10082467 (ORA-600 [kgskupdatepoolstats1]). Please see Appendix 1 below for the workaround. As with any upgrade activity, running the utility on a test system first is recommended. NOTE that if the utility is not used, the 9.1 SP6 upgrade will work, but it may take hours or days to complete. How to Run the Utility ---------------------- Required information: Database TNSNAME BBLEARN (BB_BB60) username BBLEARN (BB_BB60) password system password sys password PARALLEL_DEGREE BBLEARN_INDX (BB_BB60_INDX) tablespace name Prepare all necessary information in advance. Using parallel_degree > 1 may allow the utility to finish faster, but it will consume more resources. Carefully balance the degree of parallelism with the number of CPUs in the system and number of disk spindles where data resides. Unzip the files to a folder on the Oracle database server, for example, in the folder /tmp/bb_utility. Log in to SQL*Plus as any user. Spooling out the output for reference is recommended before running the utility: SQL>spool /tmp/upgradelog.txt SQL>@/tmp/bb_utility/gb_log_utility.sql; SQL>spool off Then run the utility and provide the information prepared above. If, for any reason, the utility does not complete, it is safe to re-run until it does. PERFORMANCE TESTS RESULTS ------------------------- If gradebook_log table contains 10 million records, this utility will take approximately 1 to 2.5 hours to complete, depending on the Blackboard Learn application release, Oracle release, OS, and hardware. If gradebook_log table contains 20 million records, this utility will take approximately 2 to 7.5 hours to complete, depending on the Blackboard Learn application release, Oracle release, OS, and hardware. If gradebook_log table contains 100 million records, this utility will take approximately 21 to 56 hours to complete, depending on the Blackboard Learn application release, Oracle release, OS, and hardware. The overall Blackboard Application response time can be up to 30% longer while this utility is running. Non-Grade Center areas are generally less than 10% longer. The parallel degree 4 was used for the test. The impact should be less for the parallel degree 1. The higher response time impacts are for the Grade Center-related transactions, especially for Grade History, View Grades via the Performance Dashboard, and Submit Grades for Self and Peer Assessment. NOTE ---- Some of the steps during this upgrade may take a considerable amount of time to complete and will not display anything on the screen during the process. If you are connecting to your system via a telnet or ssh session which is configured to terminate the connection after a short period of inactivity (no input or output), take whatever steps are required in your environment to avoid having the session killed. If the local IT department does not enforce an idle-session-kill environment, this will not apply. If it does, use software, such as GNU Screen, that will allow a long-running session. Another possible option is to use multiple windows and "type blindly": In one window, run nohup sqlplus bb_bb60/password >/tmp/output.log 2>&1 and in a second window run tail -f /tmp/output.log Then, in the first window, enter your responses to the prompts you see in the second window. While this is awkward, it will let the sqlplus session complete if the terminal session is killed. Utility Side Effects -------------------- In addition to the basic functionality this utility provides (adds a non-nullable PK1 field to gradebook_log table), the BBLEARN (BB_BB60) user will also be modified to have this privilege: grant execute on sys.dbms_lock to BBLEARN; or grant execute on sys.dbms_lock to BB_BB60; Once the utility has completed, this privilege may be removed. Final Note ---------- If you encounter any problem while running this utility, please contact Blackboard Support before proceeding with any further action. Appendix 1 ---------- Systems running Blackboard Learn application version 9.1 SP4 or 9.1 SP5, may encounter Oracle bug 8239513 and related bug 10082467 (ORA-600 [kgskupdatepoolstats1]). These bugs are related to the Oracle Resource Manager. If ORA-600 [kgskupdatepoolstats1] is encountered during the upgrade, the following steps should be followed before restarting the Gradebook_Log utility. !!!DO NOT EXECUTE THESE STEPS IN ANY OTHER SITUATION!!! Workaround steps for Oracle bugs 8239513 and 10082467: -- Logon as sys to SQL*Plus: ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=''; -- Make sure the resource plan is cleared for each day of the week as well BEGIN DBMS_SCHEDULER.set_attribute (name=>'MONDAY_WINDOW',attribute=>'resource_plan',value=>''); DBMS_SCHEDULER.set_attribute (name=>'TUESDAY_WINDOW',attribute=>'resource_plan',value=>''); DBMS_SCHEDULER.set_attribute (name=>'WEDNESDAY_WINDOW',attribute =>'resource_plan',value=>''); DBMS_SCHEDULER.set_attribute (name=>'THURSDAY_WINDOW',attribute =>'resource_plan',value=>''); DBMS_SCHEDULER.set_attribute (name=>'FRIDAY_WINDOW',attribute =>'resource_plan',value=>''); DBMS_SCHEDULER.set_attribute (name=>'SATURDAY_WINDOW',attribute =>'resource_plan',value=>''); DBMS_SCHEDULER.set_attribute (name=>'SUNDAY_WINDOW',attribute =>'resource_plan',value=>''); END; / -- Delete subplan OLTP BEGIN DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE(plan=>'BB_PLAN',group_or_subplan =>'OLTP'); DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA(); END; / -- Connect as system user -- Replace with the appropriate name in the script below and run it: BEGIN DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname =>'',orig_table => 'GRADEBOOK_LOG',int_table =>'GRADEBOOK_LOG_INTERIM'); END; / ------------------------------------ !!! Restart the utility !!! ------------------------------------ Once the Gradebook_Log utility is finished, rollback changes done to the Oracle Resource Manager with the following steps: Logon as sys to SQL*Plus -- Set the resource_manager_plan to BB_PLAN ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='BB_PLAN'; -- Set value 'DEFAULT_MAINTENANCE_PLAN' for the scheduler BEGIN DBMS_SCHEDULER.set_attribute (name=>'MONDAY_WINDOW',attribute =>'resource_plan',value=>'DEFAULT_MAINTENANCE_PLAN'); DBMS_SCHEDULER.set_attribute (name=>'TUESDAY_WINDOW',attribute =>'resource_plan',value=>'DEFAULT_MAINTENANCE_PLAN'); DBMS_SCHEDULER.set_attribute (name=>'WEDNESDAY_WINDOW',attribute =>'resource_plan',value=>'DEFAULT_MAINTENANCE_PLAN'); DBMS_SCHEDULER.set_attribute (name=>'THURSDAY_WINDOW',attribute =>'resource_plan',value=>'DEFAULT_MAINTENANCE_PLAN'); DBMS_SCHEDULER.set_attribute (name=>'FRIDAY_WINDOW',attribute =>'resource_plan',value=>'DEFAULT_MAINTENANCE_PLAN'); DBMS_SCHEDULER.set_attribute (name=>'SATURDAY_WINDOW',attribute =>'resource_plan',value=>'DEFAULT_MAINTENANCE_PLAN'); DBMS_SCHEDULER.set_attribute (name=>'SUNDAY_WINDOW',attribute =>'resource_plan',value=>'DEFAULT_MAINTENANCE_PLAN'); END; / -- Recreate subplan OLTP BEGIN DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (plan=>'BB_PLAN',group_or_subplan =>'OLTP', COMMENT => 'Give OLTP processes higher priority - level 1', MGMT_P1 => 90, SWITCH_GROUP => 'BATCH', SWITCH_TIME => 3, UNDO_POOL => 200); DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA(); END; /