Master Note : ORA-1722 Errors during Upgrade (Doc ID 1466464.1)

Last updated on DECEMBER 14, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.3 and later
Information in this document applies to any platform.
***Checked for relevance on 16-Dec-2013***

Symptoms

Case I) ORA-1722 : invalid number reported even after pre-Upgrade Script(utluxxxi.sql) has been run

=================================================================

While upgrading database version homes, the upgrade process fails with the below error:

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

This Error is reported even after running the Pre-Upgrade Script & when the latest/acceptable DST Patch is applied on both the Source & Target Oracle Homes.

WARNINGS for Case I

1: If you did not run the pre-upgrade script, this action described in this note can cause corruption of the database.

2: The TZ value you insert into that table MUST be the proper value of the pre-upgraded DB. Any other value can cause corruption of the database.

Case II) Upgrade Fails With ORA-01722 'LABEL_SECURITY_OPTION_OFF'

=================================================================

Manual upgrade to 11.1 using a Data Vault environment fails with an error similar to...

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

Data Vault has been disabled using...

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk dv_off

...but the ORA-1722 error is still happening.

Case III) Upgrade Fails With ORA-01722 Error - 'MUST_BE_OPEN_UPGRADE'

=================================================================

When running Manual Upgrade Script - catupgrd.sql,below Error is shown

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

Case IV) Upgrade fails with ORA-1722 Error - 'MUST_BE_SAME_TIMEZONE_FILE_VERSION'

=================================================================

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

Case V) Upgrade fails with ORA-1722 Error - 'MUST_PATCH_TIMEZONE_FILE_VERSION_ON_NEW_ORACLE_HOME'

==================================================================================

DBUA is also known to fail with the below Error.

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

Case VI) Upgrade fails with ORA-1722 Error - 'DATA_VAULT_OPTION_ON'

==================================================================================

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

Case VII) Upgrade fails with ORA-01722: invalid number SELECT TO_NUMBER(‘INVALID_TABLEDATA’) while upgrading to 12c. Preupgrade script log has message "Invalid user table data found in your database"

================================================================================================================================================

  

 SQL> SELECT TO_NUMBER(‘INVALID_TABLEDATA’) FROM SYS.DUAL
2 WHERE (SELECT COUNT(*) FROM SYS.OBJ$ o, SYS.COL$ c, SYS.COLTYPE$ t
3 WHERE o.OBJ# = t.OBJ# AND c.OBJ# = t.OBJ# AND c.COL# = t.COL#
4 AND t.INTCOL# = c.INTCOL# AND BITAND(t.FLAGS, 256) = 256
5 ) > 0;
SELECT TO_NUMBER(‘INVALID_TABLEDATA’) FROM SYS.DUAL
*
ERROR at line 1:
ORA-01722: invalid number

 

  

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms