Closure Report
Project Summary
The SAC071 SITS database upgrade project has successfully delivered the upgraded from oracle 11g to Oracle 12c. With rigid timelines due to the annual SITS double upgrade project following on from this, SAC071 needed to deliver within the agreed milestones, and with only minor milestone alterations to work around business availability for UAT and deployment, this has been achieved. The project team delivered the upgrade in-hours with less downtime than anticipated and the whole project came in under budget.
The version of the SITS application software that was to follow this upgrade was not compatible with Oracle 12.2, so the database was upgraded to the previous version, Oracle 12.1. A further database upgrade will be required in a future project, but 12.1 is currently supported until 2021.
A summary of the events on the project follows:
- (July 2018 ) Performance issues noted on DEV. Resolved by upgrade of Oracle drivers. This required to be updated on all environments
- (October 2018) UAT milestone was changed to be in line with the business testers availability. Deployment date still unknown and to be discussed with business
- (October 2018) After discussions with the colleges and knowing the downtime required , the business have agreed a deployment date of 19/11/2018
- (November 2018) Project budget reviewed and reduced from 137 days to 118 days
- (November 2018) Deployment successfully completed
- (December 2018) Jiras raised by production on LIVE
- (January 2019) Discussion on whether the JIRAs were caused by the upgrade
Objectives and Deliverables
| Number | Description |
Priority MoSCoW |
Achieved? |
| Objective 1 | Investigate replacing STREAMS ahead of the upgrade to ORACLE 12.1 | ||
|
D1.1 |
After investigation it is found we are not able to replace Streams. Keep Streams and upgrade to a standalone 12.1 database
|
Should Have | Yes |
| D.1.2 | Eugex reporting to be unaffected and running as before | Must Have | Yes |
| Objective 2 | To ensure the upgrade does not suffer serious performance degradation | ||
| D2.1 | To provide benchmark before and after performance testing results and ensure they are within tolerance. | Must Have | Yes (see note below) |
| Objective 3 | Upgrade to Oracle 12.1 on all environments in situ | ||
| D3.1 | Refresh data on new DUST from OLD DEV only. | Must Have | Yes |
| D3.2 | Upgrade Oracle 11g to 12.1 on DUST, DEV, TEST, TRN and LIVE on the current infrastructure, including HUB and STAR and Standby database | Must Have | Yes |
| D3.3 | Updated technical and support documentation | Must Have | Yes |
| Objective 4 | To fully test the upgraded service and ensure it is fit for purpose | ||
|
D4.1 |
Selected key functional testing | Must Have | Yes |
| D4.2 | User acceptance testing of upgraded service | Must Have | Yes |
Performance results: before vs after
DEV
A test suite of eight automated tests were run nine times, and a drop in performance was noted. The average times for each test was approximately three minutes slower on Oracle 12.1 than Oracle 11g. Oracle drivers requiring an update was identified as the cause.
The times below are the average time to complete all eight tests, over run nine runs:
Average Before: 13 minutes 22 seconds.
Average After: 16 minutes 58 seconds.
TEST
The same eight automated tests were run fourteen times. TEST has the updated Oracle drivers installed. The results show that there was similar performance before and after the upgrade.
The times below are the average time to complete all eight tests, over run fourteen runs:
Average Before: 06 minutes 57 seconds
Average After: 06 minutes 52 seconds
LIVE
The eight tests were run nineteen times on LIVE. The average to complete all eight tests over nineteen runs is below:
Average Before: 06 minutes 47 seconds
Average After: 07 minutes 24 seconds
Overall the average to complete all eight tests over nineteen runs on both Oracle versions shows that the upgraded Oracle 12c database runs to be 37 seconds slower than Oracle 11g. It was noted that on a number of runs the Oracle 12c database did perform faster.
It can be concluded that with network traffic and application usage on LIVE, there will be negligible impact on performance of the SITS application after the database upgrade.
Since its deployment in November, users of the application have not raised any concerns regarding performance
Analysis of Resource Usage:
Staff Usage Estimate: 137 days
Staff Usage Actual: 120 days
Staff Usage Variance: -14%
Explanation for variance
A scope and budget change raised on 28/11/2018 saw scope removed from the project. Budget was reduced due to this and the project team had not used the days anticipated due to a largely problem free upgrade.
- DEV Refresh and DUST upgrade to be done under SAC076 SITS software upgrade project
- Budget reduced from 137 days to 118 days.
JIRAs raised after deployment
| JIRA | Title | Date Raised | Date Resolved | Comment |
| https://www.jira.is.ed.ac.uk/browse/SAC071-13 | SATSTAGE synchronisation task on LIVE - Timetabling | 19/11 | 23/11 | |
| https://www.jira.is.ed.ac.uk/browse/SAC071-14 | EUGEX_APPLICANT_MV has not refreshed | 19/11 | 23/11 | |
| https://www.jira.is.ed.ac.uk/browse/SAC071-17 | EUGEX Package EUGEX_UTIL has become un-compiled | 22/11 | Open | Have not been able to resolve this. This will be moved into the SAC076 SITS upgrade project. There is an Oracle support call open for this one. This is not causing issues. |
| https://www.jira.is.ed.ac.uk/browse/SAC071-18 | BI Suite: ORA-04045: errors during recompilation/revalidation of | 29/11 | 09/01/2019 | Moved into support. |
| https://www.jira.is.ed.ac.uk/browse/SAC071-19 | The EUGEX REFRESH Course job is running slowly in the IDM Populate Holding Queue | 04/12 | Open | Unsure if this was caused by this project. This will be moved into the SAC076 SITS upgrade project for further analysis. There is a need to understand why this has occurred and what has caused it 2 weeks after deployment. |
Key Learning Points
Issue: A number of JIRAs have been raised after deployment.
Key Learning Point: For the closure report: Post deployment JIRAs should be noted in closure reports to aid future similar projects that may encounter the same issues. Could there be a new heading added to closure reports to list these?
Outstanding Issues
https://www.jira.is.ed.ac.uk/browse/SAC071-17 EUGEX Package EUGEX_UTIL has become un-compiled
https://www.jira.is.ed.ac.uk/browse/SAC071-19 The EUGEX REFRESH Course job is running slowly in the IDM Populate Holding Queue
These issues are to be moved into the SAC076 SITS double upgrade project.
| Attachment | Size |
|---|---|
 performance_graph.jpg | 163.71 KB |
