sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

案例:oracle 10g升级11g时执行catupgrd.sql出现报错ORA-01722:in

时间:2016-04-23 18:50   来源:Oracle研究中心   作者:代某人   点击:

天萃荷净

客户的数据库从10.2.0.5单机升级到11.2.0.4.在升级过程中,运行catupgrd.sql出现报错


DOC>#######################################################################
DOC>#######################################################################
DOC>   The following error is generated if the pre-upgrade tool has not been
DOC>   run in the old ORACLE_HOME home prior to upgrading a pre-11.2 database:
DOC>
DOC>   SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
DOC>                       *
DOC>      ERROR at line 1:
DOC>      ORA-01722: invalid number
DOC>
DOC>     o Action:
DOC>       Shutdown database ("alter system checkpoint" and then "shutdown abort").
DOC>       Revert to the original oracle home and start the database.
DOC>       Run pre-upgrade tool against the database.
DOC>       Review and take appropriate actions based on the pre-upgrade
DOC>       output before opening the datatabase in the new software version.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
Session altered.
Table created.
Table altered.
SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
              *
ERROR at line 1:
ORA-01722: invalid number

从报错原因上似乎也很明确,在SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
从上面反馈的信息中也看到:
Revert to the original oracle home and start the database.Run pre-upgrade tool against the database.

Oracle研究中心解决办法与分析过程:

在升级前需要执行脚本utlu112i.sql脚本,有时执行脚本也不能成功。

跟踪catupgrd.sql脚本,执行到此处时报错:

CREATE TABLE registry$database
               (platform_id NUMBER, platform_name VARCHAR2(101),
                edition VARCHAR2(30), tz_version NUMBER);
ALTER TABLE registry$database add (tz_version number);


Rem Check if tz_version was populated if the db is pre-11.2
SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
   FROM sys.props$
   WHERE
     (
       (
        (0 = (select count(*) from registry$database))
        OR
        ((SELECT tz_version from registry$database) is null)
       )
       AND
       (
        ((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
          '9.2.') OR
        ((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
          '10.1') OR
        ((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
          '10.2') OR
        ((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
          '11.1')
       )
     );

创建registry$database并进行查询时候报错
检查数据库,发现该表不存在:
SQL>  select count(*) from registry$database;
 select count(*) from registry$database
                      *
ERROR at line 1:
ORA-00942: table or view does not exist
 
由于在报错的时提示:   
DOC>       Shutdown database ("alter system checkpoint" and then "shutdown abort").
DOC>       Revert to the original oracle home and start the database.
DOC>       Run pre-upgrade tool against the database.
DOC>       Review and take appropriate actions based on the pre-upgrade
DOC>       output before opening the datatabase in the new software version.
那么证明在运行utlu112i.sql脚本的时候一定是做了类似的创建表操作。
我们检查utlu112i.sql脚本发现以下代码:

  IF sqlcode = -942 AND NOT db_readonly THEN — no registry$database table so create it
         EXECUTE IMMEDIATE
           'CREATE TABLE registry$database(
             platform_id   NUMBER,
             platform_name VARCHAR2(101),
             edition       VARCHAR2(30),
             tz_version    NUMBER
             )';


         IF substr(db_version,1,3) != '9.2' THEN — no v$ views for 9.2
            EXECUTE IMMEDIATE
               '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))';

搜索MOS,发现以下文档:
Bug 14174083 : PRE-UPGRADE FAILS TO CREATE REGISTRY$DATABASE & POPULATE THE TZ VALUE
严重性 2 – Severe Loss of Service 产品版本 10.2.0.3
WORKAROUND:
————————–
Can the registry$database table be created manually & if the Value of the DST 
Patch is inserted into the Table,Will it impact the Upgrade Process in any 
Way??
 
我们尝试手工创建该表格,从以上代码中拷贝创建语句:
CREATE TABLE registry$database
               (platform_id NUMBER, platform_name VARCHAR2(101),
                edition VARCHAR2(30), tz_version NUMBER);
                
并做insert 操作:
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))
                        
再次运行upgrade升级脚本,升级成功

-----------------温馨提示--------------------
操作有风险,动手需谨慎
Oracle研究中心
http://www.oracleplus.net
转载请尽量保留本站网址

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:oracle 10g升级11g时执行catupgrd.sql出现报错ORA-01722:in

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/113.html

Oracle研究中心

关键词:

catupgrd.sql出现报错ORA-01722:invalid number