ORA-01722 during upgrade 10.2 to 11.2

As we are migrating the last databases from Oracle 10gR2 to the latest 11.2.0.4 release, there are some errors that we stumbled upon along our way. For example, when we tried to upgrade one particular database, during the upgrade the following error was thrown:

SQL> SELECT TO_NUMBER('DATA_VAULT_OPTION_ON') FROM v$option
  2   WHERE
  3    value = 'TRUE' and parameter = 'Oracle Database Vault';
SELECT TO_NUMBER('DATA_VAULT_OPTION_ON') FROM v$option
                 *
ERROR at line 1:
ORA-01722: invalid number

Ouch. That is something that you do not want to see during an upgrade! Needless to say, the upgrade won’t continue here, so how to fix this?

Well, basically this is a well-known error, and there is MOS note 1409844.1 that describes how to fix the problem: Catupgrd Returns : Ora-01722 Select To_number(‘DATA_VAULT_OPTION_ON’) From V$option

Follow the steps lined out in the MOS note, so basically shutdown everything and disable the Data Vault option for the new environment before upgrading:

$ chopt disable dv

Then, restart the database in upgrade mode again and confirm the ORA-1722 has been cleared. After the upgrade, re-enable the Data Vault option like so:

$ chopt enable dv