ORA-01722 during upgrade 10.2 to 11.2

Yesterday I was trying to upgrade my 10.2 database to 11.2 version in new host and I got below error when I ran catupgrd.sql to upgrade it:-

Issue :-

SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
                 *
ERROR at line 1:
ORA-01722: invalid number

Cause :-

I checked the Timezone value in registry$database table, I got below value

SQL> col PLATFORM_NAME for a30
SQL> set lin 400
SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME                  EDITION                        TZ_VERSION
-----------             ------------------------------                ------------------------------   ----------
         13               Linux x86 64-bit

TZ_Version coloumn was blank, so was getting ORA-01722: invalid number error

Solution :-

Update the Tz_version column of registry$database table with 11.2 version details :-

SQL> create table registry_backup as select * from registry$database;

SQL> INSERT into registry$database (platform_id, platform_name, edition, tz_version) VALUES ((select platform_id from v$database), (select platform_name from v$database),NULL,(select version from v$timezone_file));

1 row created.

SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME                  EDITION                        TZ_VERSION
-----------             ------------------------------           ------------------------------    ----------
         13              Linux x86 64-bit
         13             Linux x86 64-bit                                                                 14

SQL> delete from sys.registry$database where TZ_VERSION is NULL;

1 row deleted.

SQL> select * from registry_backup;

PLATFORM_ID PLATFORM_NAME                  EDITION                        TZ_VERSION
-----------             ------------------------------           ------------------------------    ----------
         13              Linux x86 64-bit

SQL> commit;

Commit complete.

SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME                  EDITION                        TZ_VERSION
-----------             ------------------------------           ------------------------------    ----------
         13              Linux x86 64-bit                                                                14

Timezone issue resolved with above solution.

Again I ran catupgrd.sql and now this time I got a new error :-

Issue :-

SELECT TO_NUMBER('DATA_VAULT_OPTION_ON') FROM v$option
                 *
ERROR at line 1:
ORA-01722: invalid number

Cause :-

We have to disable the data vault option before running the upgrade and enable it once donewith upgrade

Solution :-

==>chopt disable dv

Writing to /opt/oracle/product/database/11.2.0.4/install/disable_dv.log...
/usr/bin/make -f /opt/oracle/product/database/11.2.0.4/rdbms/lib/ins_rdbms.mk dv_off ORACLE_HOME=/opt/oracle/product/database/11.2.0.4
/usr/bin/make -f /opt/oracle/product/database/11.2.0.4/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/opt/oracle/product/database/11.2.0.4

Now I ran the Catupgrd.sql and it ran without any issues and upgrade got successfully completed.

After the upgrade was done , I enable the data vault option by running below command :-

==>chopt enable dv

I hope this article helped you 

Regards,
Amit Rath