About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Tuesday, June 30, 2015

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

7 comments:

  1. Hey Amit
    I was trying to RMAN restore to 11.2.0.4 64Bit from a 10.2.0.3 32Bit instance as part of migration on Windows and your document was the one followed. Thank you, the TIME ZONE issue was the only one part I had to rectify and correct. chopt should be LINUX specific?

    Thanks buddy

    ReplyDelete
  2. bloody brilliant post - saved my butt - thank you!

    ReplyDelete
  3. Hello Amit
    I was able to migrate a Windows based 10g (10.2.0.3) 32Bit instance to Oracle 11g (11.2.0.4) following your document(s). As the other poster mentioned, the Only one issue I had was about the upgrade process TIME ZONE. In my case, "select * from sys.registry$database;" returned no rows, hence I inserted the row using the insert call. After that the upgrade process ran for approximately 80 minutes and the database was opened without any additional issues. Thank you very much. This is an excellent POST, that helps even a person like me who just started with RMAN to accomplish a migration without much hurdles.

    rajesh

    ReplyDelete
  4. Hi, Amit Rath
    I got the following error:
    SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
    ERROR at line 1: ORA-01722: invalid number

    ReplyDelete