ORA-32002 and ORA-32001 when upgrading a database with DBUA
Some days ago I upgraded some Oracle 18c databases to Oracle 19c with the Database Upgrade Assistant (DBUA). Usually, I prefer command line tools, but for some reason I chose DBUA this time. Basically, everything went fine, except an error message in the middle of the upgrade: „ORA-32002: cannot create SPFILE already being used by the instance“.
Of course, I ignored the error and let DBUA continue the work. And the upgrade succeeded after another „ORA-32001: write to SPFILE requested but no SPFILE is in use“.
But what had happened?
Obviously, upgrading a database does not only mean changing the data dictionary but switching to another installation of the Oracle database software, too. With an Oracle installation as we know it until Oracle 12.2 (and which is of course still available with 18c and 19c), the configuration files like spfile and password file had to be copied from the $OLD_ORACLE_HOME/dbs directory to the $NEW_ORACLE_HOME/dbs directory (RAC installations in which these files are stored centrally in ASM are out of scope of this post).
But since Oracle 18c there‘s this new feature called „read-only Oracle home“. I‘ve written some blog posts on this feature (see here or here) and in my opinion, „software-only Oracle home“ would have been a better name.
When using a „read-only Oracle Home“, the configuration files for the database instances and other configuration files are not stored in a subdirectory of your Oracle Home, but in a subdirectory of your Oracle Base:
| $ORACLE_BASE/dbs | Spfile, init.ora, password files |
| $ORACLE_BASE/homes | Oracle Home specific information like- Oracle Net configuration- DBCA templates- log + audit files |
And in the case of my upgrade, both Oracle Homes were „read-only Oracle Homes“.
DBUA was aware of the different location of the spfile ($ORACLE_BASE/dbs/spfile<ORACLE_SID>.ora), but still tried to copy it in the same way as it had done before in previous versions (when the spfile was copied from the $OLD_ORACLE_HOME>/dbs to $NEW_ORACLE_HOME/dbs). This copy attempt fails because source and target were the same (=> ORA-32002) and after ignoring this error DBUA continues with an init.ora and continues the upgrade. Later on, DBUA wants to write into the spfile, but step fails because the instance is using the init.ora (=> ORA-32001). Finally, the upgrade completes successfully.
So at the end, the database is upgraded, but there is no spfile for the instance. There are two files in the $ORACLE_BASE/dbs directory:
- a backup of the old spfile (spfile<ORACLE_SID>.ora.bak)
- an init.ora file (init<ORACLE_SID>.ora) which is used by the instance.
So you have to create the spfile from the init.ora
SQL> create spfile='<ORACLE_BASE>/dbs/spfile<ORACLE_SID>.ora' from pfile='<ORACLE_BASE>/dbs/init<ORACLE_SID>.ora';
and bounce the instance to make it use the spfile.
Of course, this issue can be handled easily, but in my opinion it’s a bug. DBUA should be aware of the fact the both homes are “read-only-homes” and should not try to copy the existing spfile to the same location.
Amazon Partner Link