Completion Report

Project Summary

Extended support for Oracle 11.2 ends in December 2020, so we needed to upgrade Oracle 12c on our central databases:  APPS, NEWS and GEN. Each required an upgrade and migration onto new infrastructure to ensure the continued benefits of speed, reliability, failover and patch releases. This project was due to run over 16/17 and 17/18, and be delivered in three phases.

Project Scope

The scope of the project was to migrate the central databases APPS, NEWS and GEN (Dev, Test & Live for all three) to the new Oracle 12c Database Architecture. In the planning stage of the project, research was carried out into getting a list of all services related to each of the central databases. They were then to be evaluated as to whether they are in use or could be removed. Removal of any redundant services would be done once live and were not expected to impact existing services or any applications using them.  

Once migrated, the Oracle 11.2 APPS, NEWS and GEN databases were to be decommissioned.

The project relied on the business owners to communicate fully with their users and report any issues - in particular relating to suitable delivery dates - back to the project team. This should have resulted in a delivery date that was less likely to be delayed.

Analysis of Resource Usage

Staff Usage Estimate: 197 days

Staff Usage Actual: 530 days

Staff Usage Variance: 269%

Outcome

Explanation for variance

• Effort

The overall effort for the project was estimated at 197 days at the planning stage, and a total of 530 days have been used - a net increase of 333 days. The table below enumerates the estimates and final totals for each high-level stage of the project*. The notable differences are across the time used for each of the three builds and migrations; project management; unplanned activity and a block of time that has been listed as "undefined work".

Each of the three migrations have taken a proportionately similar extra amount of effort against what was estimated at the earlier stages of the project, so it may be fair to say that these estimates were consistently optimistic, though they were made in good faith taking similar projects (from c.2016) into consideration. Breaking down the effort for each of the three shows a consistency of effort with GEN using 119 days, NEWS requiring 77.5 days, and APPS using a total 129.7 days.

The total for unplanned effort is very high - 65 days - but it appears that 62 days of this is time used across the GEN and NEWS migrations, with colleagues recording time under this heading rather than it being apportioned into more particular areas. There is a total of c.130 entries in ASTA making up this effort, much of which could probably be found to belong more properly to particular stages of the first two migrations. This has not been done by the new PM because of the time required for this.

There is also a total of 31.8 days listed as "undefined work" which is attributable to a temporary member of staff in August-October 2017. There is no further detail on what this should be listed under as it stands alone from any of the named tasks or stages within the project record.

Finally, the project management time saw an increase of approximately 50% but this is to be expected in a project that has been extended by more than 12 months in its duration.

Project Issues/Changes were raised in April 2017, December 2017, November 2018 and January 2019 to seek approval for each increase in project budget.

*The original estimates are organised differently, so it has been easier to use totals for most stages rather than piece together corresponding figures for each lower level stage of work.

• Time

This is the aspect of the project that had the most changes over the duration of the project, with several  PICCLs raised to revise the milestones. The earlier days of the project saw amendments to the original planning milestone as the estimation and planning was being carried out at a very busy time and was problematic. Once the migrations got under way, there were more (almost regular)  milestone changes  through 2017 - these were attributable to more schemas being added to the original list of planned migrations, and the planned work taking longer than envisioned at the planning stage. Once NEWS and GEN were completed, the decision was made to suspend the project in early 2018, ahead of the APPS migration so that this could be planned for a more opportune time for all involved.

The suspension ended in August 2018 and the build of APPS started shortly after this. The work on the APPS environments continued over the period up to December 2018, with this aspect being led by colleagues in Applications Management. There has been subsequent delays to getting to the final closure because of difficulties in getting the last post-deployment checks completed, and these are reflected in the final PICCLs.

This last stage - covering the migration of APPS - was led by Suran, and his knowledge of the 'bigger picture' undoubtedly helped this run much more smoothly. His contribution to the project was very much appreciated.

• Scope

There were some changes to the scope established at the planning stage, with the decision to move to Oracle 12.2 instead of version 12.1 as originally planned. The other modification to our original intention was the addition of some schemas that were not originally identified for the listings of what was to be included. The stated objectives and deliverables, though difficult to achieve in the long run, were set out in a straightforward fashion, and there was no deviation from those top-level statements. The hard part was in the detail of the migrations!

Key Learning Points

This project has been extended time-wise and in the amount of effort used. It has also involved a very high number (approx. 50) of colleagues across the Applications Directorate at different points of its lifetime and so opinion has been sought from a number of those who contributed a sizeable amount of effort to the project to build up a listing of lessons learned. This feedback includes the following points*:

• This type of  project is highly technical (infrastructure/migrations) and future ones would benefit from having a technical lead.
• Technical projects need a PM who is very proactive and able to manage complex dependencies – both technical and business. The technical lead may help and could work with the PM
• This type of project requires a PM (or tech lead as an alternative) who has the ability to drive and push rather than expect things to happen
• The original estimates were probably too low.
• The work was dragged out for too long. This caused additional costs and re-doing things, losing momentum and focus. Short but intensive stages of work are better.
• This relates to the point about estimates, but a project like this needs weekly or more regular standups/team meetings (short but often)  looking at what the plans are. At some point (DEV/TEST in particular) dragged on for months.
• Resources (contractor) were booked but almost no delivery was achieved. The project lost a substantial number of days [see above]. We need better management by PM and Team Managers as to what staff do when booked.
• We should not have databases with too many schemas as it makes these difficult to manage in one upgrade.

*These are not listed in any type of order.

The discussions around these points raised further feedback, especially  on the matter of complex databases and it was acknowledged by others that "complex databases are hard to update, so one option might be to have a policy that aims to simplify databases and reduce resident schema rather than consolidate. Our colleagues in production might want to lead on defining reasonable targets for this simplification."

Another comment concerning the multiple databases was that, "the nature of these 3 databases is that they include multiple schemas supporting multiple applications, and as a result it was not always clear which systems would be affected by the work, and also whether we had actually taken account of all of them. This kind of information would usually be in a TAD, but for NEWS the information was distributed across multiple TADs.  Or in CMDB, if CMDB was kept up to date! What is needed is an easy way to look up the dependencies on a particular database."

Finally, another set of points made covered the following:

• The sheer size of APPSLIVE. Believe we are already implementing the lesson learnt here with the MySQL to Maria db EOL project, we will be breaking down one of the shared instances by web technology.
• We did pre-checks on applications but we did experience issues with BAMBOO plans. Some plans were not working, there were also a number of plans that needed QA.
• We did experience difficulty at times getting resource. Appreciate this is not the only project and there will be higher priorities but when we stop/start there is a loss of momentum.

Outstanding Issues

There are no outstanding issues. Some of the Bamboo plans that were updated as part of the APPS migration still need to have a final sign-off and these are being covered by the Bamboo project (INF131).

.