Monday, April 29, 2013

Issue with running catupgrd.sql while Database upgrade from 10.2.0.4 to 11.2.0.3:



Getting below error while running catupgrd.sql scripts while DB upgrade.

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

Cause:
------------
The Pre-Upgrade Script is not creating the registry$database table & inserting the Platform DST Patch Information.The error may also appear if the the registry$database table exists,  but does not contain the required information.

Solution:
-----------------

SQL> select * from sys.registry$database;

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

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

Table created.

select * from sys.registry$database;

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

SQL> INSERT into registry$database
  2                      (platform_id, platform_name, edition, tz_version)
  3                 VALUES ((select platform_id from v$database),
  4                         (select platform_name from v$database),
  5                          NULL,
  6                         (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                                             4

SQL> commit;

Commit complete.

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

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from sys.registry$database;

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

Ref Doc: catupgrd.sql fails With ORA-01722 Invalid Number after running the Pre-Upgrade Script [ID 1466464.1]

5 comments: