ORA-01722: invalid number" During Upgrade/Downgrade

最新推荐文章于 2023-09-26 19:10:50 发布

aicon

最新推荐文章于 2023-09-26 19:10:50 发布

阅读量1.2k

点赞数

分类专栏： Oracle Oracle EBS 文章标签： database oracle server sql user comments

Oracle 同时被 2 个专栏收录

523 篇文章 0 订阅

订阅专栏

Oracle EBS

416 篇文章 0 订阅

订阅专栏

"ORA-01722: invalid number" During Upgrade/Downgrade [ID 435536.1]

		Modified 01-JUN-2010 Type PROBLEM Status MODERATED

In this Document
  Symptoms
  Cause
  Solution
  References

Platforms: 1-914CU;

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.7 to 11.2.0.1 - Release: 9.2 to 11.2
Information in this document applies to any platform.

Symptoms

You are getting following error while performing upgrade/downgrade of the database.
This error appears when executing script in the following release only

catbkout.sql( Available on 9.2.0.7 and Above )
catbkout.sql(Available on 10.1.0.5 and Above )
catbkout.sql( 10.2.0.2 and Above )
catupgrd.sql (For 10g R2 Above)

SQL> @?/rdbms/admin/catupgrd.sql 


DOC>####################################################################### 
DOC>####################################################################### 
DOC> The following statement will cause an "ORA-01722: invalid number" 
DOC> error if the database has not been opened for UPGRADE. 
DOC> 
DOC> Perform a "SHUTDOWN ABORT" and 
DOC> restart using UPGRADE. 
DOC>####################################################################### 
DOC>####################################################################### 
DOC># 
SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance 
* 
ERROR at line 1: 
ORA-01722: invalid number

Cause

Instance has not started with UPGRADE ( MIGRATE for 9.2) mode , which caused catupgrd.sql failed execute.

There can be multiple cause for the ORA-01722 error. Following are few listed

1. Database not started as UPGRADE or MIGRATE Mode.
2. SQL*PLUS session user running this script is not SYS.
3. Database server version is not correct for this script.
4. If the SYSAUX tablespace does not exist or is not ONLINE for READ WRITE, PERMANENT, EXTENT MANAGEMENT LOCAL, andSEGMENT SPACE MANAGEMENT AUTO.[Applicable Only For 10G]

Solution

1. Ensure you have followed the following criteria

Database as UPGRADE or MIGRATE Mode.
Logged on as SYS user when running this script.
Database server version matches for this script i.e catupgrd.sql for 10.2 need to be executed for 10.2 database
If the SYSAUX tablespace exist and is ONLINE for READ WRITE, PERMANENT, EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO.[Applicable Only For 10G]

2. If you are meeting above criteria , then open the script from appropriate location %ORACLE_HOME%/rdbms/admin

3. Goto the SQL command which fails with error i.e in this case
SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance
and check you have performed the criteria mentioned above SQL command

While running the catbkout .sqlor catupgr.sql , you will also see the following appear on console . These are comments before SQL , which are for information purpose.

DOC 
###################################################################### 

###################################################################### 

The following statement will cause an "ORA-01722: invalid number" 
error 
if the user running this script is not SYS. Disconnect 
and reconnect with AS 
SYSDBA. 

###################################################################### 

######################################################################

<<End_of_Article>> FOLDER:ST.Server.DBA.UpgradeAndMigration TOPIC:Miscellaneous DOCUMENT-ID:435536.1 ALIAS: SOURCE:AWIZ 6337004.994 DOCUMENT-TYPE:PROBLEM ZCXTECH TITLE:"ORA-01722: invalid number" During Upgrade/Downgrade IMPACT:LOW SKILL-LEVEL:NOVICE STATUS:MODERATED DISTRIBUTION:EXTERNAL ZCXPUBLIC ZCXCURRENT AUTHOR:DROUT.IN ERROR:ORA-1722 PRODID-5 COMPONENT:INSTALL MINVER:9.2.0.7 MAXVER:10.2.0.1 PORTID-0 FDRSEG-799 FDRSEG-788 FDRSEG-773 FDRSEG-465