Oracle Upgrade fails with an ORA-01078 in the trace.log

Products

• Data Loss Prevention Enforce

Issue/Introduction

During an Oracle upgrade from 10.2.0.3 to 10.2.0.4, the upgrade fails during PreUpgrade.

- In the UpgradeResults.html file, the top line looks like:

DBUA failed to upgrade the database or some of its components. See upgrade detail.

- The Upgrade Detail section shows the Pre-Upgrade step as having failed.

- The Initialization Parameter section lists the db_recovery_file_dest parameter with old and new values that look remarkably alike.

- Looking in the associated trace.log, the following error appears:

ORA-01078: failure in processing system parameters

- the PreUpgrade.log looks something like this:

Database closed.
Database dismounted.
ORACLE instance shut down.
select count(*) from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available

LRM-00116: syntax error at 'D:\oracle\Flash_' following '='
ORA-01078: failure in processing system parameters

Resolution

This error is an indication that the database template provided with the Symantec DLP installation package was NOT used in the creation of the database. 

This is a known Oracle upgrader issue.  For parameter db_recovery_file_dest, the associated value contains an extra '\' at the end of the directory.  In order for the upgrader to appropriately run, this '\' will need to be removed.

This is the solution:

Connect through SQLPlus using "sys as sysdba" to the existing pre-upgrade database.

1) create pfile from spfile

SQL> create pfile='%ORACLE_HOME%\dbs\init<SID>.ora' from spfile;

2) shutdown database

3) remove the trailing \ from the db_recovery_file_dest path within the newly generated pfile. 

-Using your favorite text editor, open init_fix.ora

-find the line with:

   db_recovery_file_dest='D:\oracle\flash_recovery_area\'

-and change it to:

   db_recovery_file_dest='D:\oracle\flash_recovery_area'

-Then save and close the file.

4) startup mount  pfile=$ORACLE_HOME/dbs/init<SID>.ora

startup mount  pfile=$ORACLE_HOME/dbs/init<SID>.ora

5) create spfile from pfile

SQL> create spfile from pfile='%ORACLE_HOME%\dbs\init<SID>.ora';

6) shutdown database

7) stop database service (windows only)

8) Start DBUA to upgrade this database.

... And the upgrade should continue sans this issue.