Unavalability of Apex applications running in ITCORE

Description

There was an intervention scheduled for Wednesday 17.30 to upgrade APEX installation in ITCORE database. This upgrade was already applied to a number of databases. Due to the corruption of APEX schema in ITCORE after the the upgrade of APEX applications running there were not accessible. This included SDB, tapelog, CHIMPS and a number of internal applications of IT-DB.

Impact

• SDB web application was down between 18.30 and 21.30 and it had to be recovered from a copy in devdb11
• Tapelog, chimps and other applications were down from 18.30 on Wednesday until 11.30 on Thursday

Time line of the incident

• 28-Mar-12 17:30 - Kate Dziedziniewicz killed all the sessions connected to APEX_PUBLIC_USER and started the upgrade script
• 28-Mar-12 17:32 - Script failed with a number of errors related to missing FLOW objects
• 28-Mar-12 17:40 - Kate Dziedziniewicz re-run the script
• 28-Mar-12 18:00 - Script finished claiming APEX component is valid but finished with a number of errors
• 28-Mar-12 18:05 - Kate Dziedziniewicz restarted HTTP servers but APEX application was reporting errors due to missing flow objects
• 28-Mar-12 18:05 - Kate Dziedziniewicz tried reverting to the old version but there was none accessible (updates usually created a new APEX_0... schemas, this one didn't)
• 28-Mar-12 18:10 - Kate Dziedziniewicz dropped APEX schema and started installation from scratch
• 28-Mar-12 19:10 - Ruben Gaspar started recovery of ITCORE db in a recovery machine aimed at recovering APEX_PUBLIC_USER, APEX_040100 and FLOWS_FILES
• 28-Mar-12 19:30 - Apex installation has finished (version 4.1.1.0.23 including patch)
• 28-Mar-12 20:30 - Kate Dziedziniewicz copied SDB workspace from devdb11 to ITCORE APEX
• 28-Mar-12 21:00 - Zhechka Toteva copied SDB application from devdb11 to ITCORE APEX
• 28-Mar-12 21:30 - After reconfiguration done by Zhechka Toteva and Kate Dziedziniewicz SDB was fully functional
• 28-Mar-12 21:30 - After consulting with Eva Dafonte it was decided that recovery of other applications will be done on Thursday morning (it could only be done after the recovery from backup has finished).
• 28-Mar-12 22:00 - Dawid Wojcik has exported out APEX_PUBLIC_USER, APEX_040100 and FLOWS_FILES from recovered ITCORE DB
• 29-Mar-12 9:30 - Kate Dziedziniewicz tried exporting APEX_PUBLIC_USER, APEX_040100 and FLOWS_FILES from ITCORE DB to have a backup of the upgraded version. This was not possible due to ITCORE compatible being set to 10.2.0.5. Since this would require a short downtime of a whole database a decision to test ApexEport tool was taken.
• 29-Mar-12 10:30 - Nilo Segura tested and patched ApexEport tool
• 29-Mar-12 10:30 - Nilo Segura exported Apex workspaces and applications from recovered ITCORE DB using ApexEport tool
• 29-Mar-12 11:30 - Nilo Segura imported Apex workspaces and applications to ITCORE database. All applications were recovered.

Analysis

• Due to patch application on ITCORE Apex installation Apex040100 schema was corrupted for some unknown reason. Since the patch was not creating a new schemas (which is the case with each Apex installation and most of the patches), there was no possibility to rollback the patch and Apex schema had to be recreated, which meant loss of all Apex applications. They had to be recovered from backup. Apex schemas were recovered using the recovery system and then applications were exported using ApexExport tool. Using sql scripts produces by ApexExport it was possible to recover all applications.

Follow up

• There are two steps to prevent this from happening in the future:
  • Not only current Apex schemas needs to be kept but also a previous one. In case the patch is not creating a new schema, but changing the existing, currently used one, it will leave a possibility of rolling back to a previous Apex schema. This older schema contains all the necessary data and can be used until the latest version available is installed properly.
  • In case there is only one APEX schema in the database ApexEport tool should be used to export all workspaces and applications before any intervention on Apex. In case Apex has to be reinstalled, files created during export will allow for a fast recreation of all applications. DB recovery will not be necessary, hence, prolongation of intervention due to problems will not be significant.

-- KateDziedziniewicz - 29-Mar-2012