Oracle case study ORA-01722 troubleshooting

Oracle case study ORA-01722 troubleshooting

1.1. ORA-01722
 
Date:
 
Environment: test environment
 
[Scenario description]
 
During Database Upgrade, execute SQL> @? /Rdbms/admin/catupgrd. SQL script reports an error and exits.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian
 
 
[Error message]
 
DOC> ###################################### #################################
 
DOC> The following statement will cause an "ORA-01722: invalidnumber"
 
DOC> error if the Oracle Database Vault option is TRUE. Upgrades cannot
 
DOC> be run with the Oracle Database Vault option set to TRUE since
 
DOC> as sysdba connections are restricted.
 
DOC>
 
DOC> Perform "alter system checkpoint" prior to "SHUTDOWNABORT", relink
 
DOC> the server without the Database Vault option, and restart the server
 
DOC> using UPGRADE mode.
 
DOC>
 
DOC>
 
DOC> ###################################### #################################
 
DOC> ###################################### #################################
 
DOC> #
 
SELECT TO_NUMBER ('data _ VAULT_OPTION_ON ') FROM v $ option
 
*
 
ERROR at line 1:
 
ORA-01722: invalid number.
 
 
 
[Error cause]
 
The Database instance is configured with the Oracle Database Vault function.
 
 
 
[Solution]
 
After the Oracle Database Vault function is disabled, perform the upgrade operation again.
 
 
 
Disable Oracle Database Vault:
 
# Check whether the OracleDatabase Vault function is Enabled:
 
SQL> col parameter for a30
 
SQL> col value for a20
 
SQL> SELECT * FROM V $ OPTION WHEREPARAMETER = 'oracle Database Vault ';
 
 
 
PARAMETER VALUE
 
--------------------------------------------------
 
Oracle Database Vault TRUE
 
# Disabling database instances
 
SQL> shutdown immediate
 
SQL> exit
 
$ Emctl stop dbconsole
 
$ Lsnrctl stop
 
# Compile ins_rdbms.mk
 
[Oracle @ oracle ~] $ Cd $ ORACLE_HOME/rdbms/lib/
 
[Oracle @ oracle lib] $ make-f ins_rdbms.mkdv_off
 
/Usr/bin/ar d/U01/app/oracle/product/11.2.3/db_1/rdbms/lib/libknlopt. a kzvidv. o
 
/Usr/bin/ar cr/U01/app/oracle/product/11.2.3/db_1/rdbms/lib/libknlopt. a/U01/app/oracle/product/11.2.3/db_1/rdbms/lib/kzvnewcastle. o
 
[Oracle @ oracle lib] $
 
# Execute relink
 
[Oracle @ oracle lib] $ cd $ ORACLE_HOME/bin
 
[Oracle @ oracle bin] $ relink all
 
Writing relink log to:/U01/app/oracle/product/11.2.3/db_1/install/relink. log
 
[Oracle @ oracle bin] $
 
# Start a database instance
 
$ Sqlplus/as sysdba
 
SQL> startup
 
# Check the Oracle DatabaseVault status
 
SQL> SELECT * FROM V $ OPTION WHERE PARAMETER = 'oracle Database Vault ';
 
PARAMETER VALUE
 
----------------------------------------
 
Oracle Database Vault FALSE
 
SQL>
 
[References]
 
Close/enable Oracle Database Vault:
 
Http://docs.oracle.com/cd/B28359_01/server.111/b31222/dvdisabl.htm#DVADM70984
 
Install Oracle Database Vault:
 
Http://www.stanford.edu/dept/itss/docs/oracle/10gR2/install.102/b32496/upgrd.htm
 
What is Oralce Database Vault:
 
OracleDatabase Vault helps users solve existing extremely difficult security problems, that is, to prevent internal threats, meet compliance requirements, and implement division of duties. Oracle Database Vault prevents DBAs from viewing application data, solving the most worrying issues that must protect sensitive business information or private data involving partners, employees, and customers. Oracle Database Vault prevents high-Permission application DBAs from accessing other applications and executing tasks beyond their permissions. Oracle Database Vault can easily and quickly protect existing applications without affecting the functions of applications.