Retail Pro 9 Oracle database will not open due to an unexpected power event

Retail Pro 9 Oracle database will not open due to an unexpected power event

Symptoms

• The RP9 database will not open.
• The following error appears in the alert_rproods.log file following an attempt to access the database via the TTK (Tech Toolkit): "ORA-214 signaled during: ALTER /* Toolkit */ DATABASE MOUNT.."

Root Cause

• The database was improperly shut down due to an unexpected power event, while the mirrored copies of the Control File were being updated, resulting in inconsistent control files. Meaning, one or more of the control files have become corrupted.

Solution

• Identify the valid control file, overwrite the corrupted control files with the valid file, and open the database.

Please take note of the following points before performing this action:

1. Before performing these steps, or any other recovery procedure, you must make a complete cold backup of the database.
2. This procedure is only to be attempted in cases where the error entry "ORA-214 signalled during: ALTER /* Toolkit */ DATABASE MOUNT.." is present in the alert_rproods.log file.
3. Consult the "Backup and Recovery Best Practices" documentation located here https://partners.retailpro.com/Support/view.php?id=762 for details on where to locate the alert_rproods.log file and on how to make a cold backup.
4. The procedure may not resolve the problem in every case. There are times where all 3 copies of the control files are corrupted. If the procedure is unsuccessful, or you continue to see errors in the alert_rproods.log, you may have to restore a known good backup, or consult RPI Technical Support for billable diagnostic support assistance.

Steps:

1. Stop the RPROODS Service.
2. Browse to the Oracle\Oradata\RPROODS folder and create a TEMP folder.
3. In the RPROODS folder rename the 3 files ending in .CTL to end in .OLD and place copies of them into TEMP folder.
4. In the RPROODS folder make 3 copies of control01.old and rename them with the following file names (control01.ctl, control02.ctl, control03.ctl).

5. Restart the RPROODS service.
6. Check the alert_rproods.log for any new ora-###### errors once the service has finished starting.
7. If there are no errors, restart the server and attempt to enter Retail Pro.
 

If the database cannot be opened. or you see errors in the alert_rproods.log, then do the following:

1. Stop the RPROODS Service
2. Remove the 3 control##.ctl files from the Oracle\Oradata\RPROODS folder
3. In the RPROODS folder make 3 copies of control02.old and rename them with the following file names (control01.ctl, control02.ctl, control03.ctl).

4. Restart the RPROODS service
5. Check the alert_rproods.log for any new ora-###### errors once the service has finished starting.
6. If there are no errors, restart the server and attempt to enter Retail Pro.
 

If the database cannot be opened. or you see errors in the alert_rproods.log, then do the following:

1. Stop the RPROODS Service
2. Remove the 3 control##.ctl files from the RPROODS folder
3. In the RPROODS folder make 3 copies of control03.old and rename them with the following file names (control01.ctl, control02.ctl, control03.ctl).

4. Then restart the RPROODS service
5. Check the ALERT_RPROODS.LOG file for any new ora-###### errors once the service has finished starting
6. If there are no errors, restart the server and attempt to enter Retail Pro.

At this point we have copied each of the 3 controlfiles and used them to create new controlfiles. If you still cannot open the database then you may have a different problem in addition to inconsistent controlfiles and you will need to restore to a known good database backup, or contact Technical Support for billable System Down diagnostic support assistance.

System Down Support Policy: http://www4.retailpro.com/P000Z04IHQ03K00g00aJ204

***Prevention***: Maintain a managed UPS backup battery unit to avoid sudden power loss to the server.