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

天萃荷净

客户的数据库从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