Oracle Database Error：ORA-39700
有时候，你可能会在ALERT日志中发现如下的错误：
|
1 2 3 4 5 6 7 8 9 |
Fri Oct 03 23:37:33 CST 2014 Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_7704.trc: ORA-00704: bootstrap process failure ORA-39700: database must be opened with UPGRADE option Fri Oct 03 23:37:33 CST 2014 Error 704 happened during db open, shutting down database USER: terminating instance due to error 704 Instance terminated by USER, pid = 7704 ORA-1092 signalled during: ALTER DATABASE OPEN... |
通常，该错误发生的时候，SQL*Plus的呈现是：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
[root@ora10g ~]# su - oracle [oracle@ora10g ~]$ export ORACLE_SID=orcl [oracle@ora10g ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 3 23:36:55 2014 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 2095704 bytes Variable Size 88081832 bytes Database Buffers 188743680 bytes Redo Buffers 6291456 bytes Database mounted. ORA-01092: ORACLE instance terminated. Disconnection forced SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@ora10g ~]$ |
该错误发生在升级10g数据库为：10.2.0.5.0的时候。
数据库软件升级成功，但是数据库的实例没有升级，就会遇到上述错误。
解法：
以upgrade参数启动数据库，执行升级脚本：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 |
[oracle@ora10g bdump]$ ps -ef | grep ora_ oracle 7715 7619 0 23:39 pts/1 00:00:00 grep ora_ [oracle@ora10g bdump]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 3 23:39:45 2014 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 2095704 bytes Variable Size 88081832 bytes Database Buffers 188743680 bytes Redo Buffers 6291456 bytes Database mounted. SQL> alter database open upgrade; Database altered. SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl OPEN MIGRATE SQL> @?/rdbms/admin/catupgrd.sql （这里的输出非常多，... ...） .. creating Rule Manager package/type implementations No errors. No errors. No errors. No errors. No errors. No errors. No errors. No errors. No errors. No errors. No errors. No errors. No errors. No errors. No errors. No errors. No errors. No errors. No errors. No errors. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP RUL 2014-10-04 00:08:47 DBUA_TIMESTAMP RUL VALID 2014-10-04 00:08:47 TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UPGRD_END 2014-10-04 00:10:39 . Oracle Database 10.2 Upgrade Status Utility 10-04-2014 00:10:39 . Component Status Version HH:MM:SS Oracle Database Server VALID 10.2.0.5.0 00:05:52 JServer JAVA Virtual Machine VALID 10.2.0.5.0 00:02:03 Oracle XDK VALID 10.2.0.5.0 00:00:22 Oracle Database Java Packages VALID 10.2.0.5.0 00:00:16 Oracle Text VALID 10.2.0.5.0 00:00:23 Oracle XML Database VALID 10.2.0.5.0 00:01:58 Oracle Workspace Manager VALID 10.2.0.5.0 00:00:36 Oracle Data Mining VALID 10.2.0.5.0 00:00:16 OLAP Analytic Workspace VALID 10.2.0.5.0 00:00:16 OLAP Catalog VALID 10.2.0.5.0 00:00:41 Oracle OLAP API VALID 10.2.0.5.0 00:00:47 Oracle interMedia VALID 10.2.0.5.0 00:03:12 Spatial VALID 10.2.0.5.0 00:02:08 Oracle Expression Filter VALID 10.2.0.5.0 00:00:16 Oracle Enterprise Manager VALID 10.2.0.5.0 00:01:13 Oracle Rule Manager VALID 10.2.0.5.0 00:00:14 . Total Upgrade Time: 00:22:31 DOC>####################################################################### DOC>####################################################################### DOC> DOC> The above PL/SQL lists the SERVER components in the upgraded DOC> database, along with their current version and status. DOC> DOC> Please review the status and version columns and look for DOC> any errors in the spool log file. If there are errors in the spool DOC> file, or any components are not VALID or not the current version, DOC> consult the Oracle Database Upgrade Guide for troubleshooting DOC> recommendations. DOC> DOC> Next shutdown immediate, restart for normal operation, and then DOC> run utlrp.sql to recompile any invalid application objects. DOC> DOC>####################################################################### DOC>####################################################################### SQL> SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 2095704 bytes Variable Size 155190696 bytes Database Buffers 121634816 bytes Redo Buffers 6291456 bytes Database mounted. Database opened. SQL> SQL> @?/rdbms/admin/utlrp.sql TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN 2014-10-04 00:17:45 DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid DOC> objects in the database. Recompilation time is proportional to the DOC> number of invalid objects in the database, so this command may take DOC> a long time to execute on a database with a large number of invalid DOC> objects. DOC> DOC> Use the following queries to track recompilation progress: DOC> DOC> 1. Query returning the number of invalid objects remaining. This DOC> number should decrease with time. DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); DOC> DOC> 2. Query returning the number of objects compiled so far. This number DOC> should increase with time. DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; DOC> DOC> This script automatically chooses serial or parallel recompilation DOC> based on the number of CPUs available (parameter cpu_count) multiplied DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu). DOC> On RAC, this number is added across all RAC nodes. DOC> DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel DOC> recompilation. Jobs are created without instance affinity so that they DOC> can migrate across RAC nodes. Use the following queries to verify DOC> whether UTL_RECOMP jobs are being created and run correctly: DOC> DOC> 1. Query showing jobs created by UTL_RECOMP DOC> SELECT job_name FROM dba_scheduler_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC> DOC> 2. Query showing UTL_RECOMP jobs that are running DOC> SELECT job_name FROM dba_scheduler_running_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC># TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_END 2014-10-04 00:19:07 DOC> The following query reports the number of objects that have compiled DOC> with errors (objects that compile with errors have status set to 3 in DOC> obj$). If the number is higher than expected, please examine the error DOC> messages reported with each object (using SHOW ERRORS) to see if they DOC> point to system misconfiguration or resource constraints that must be DOC> fixed before attempting to recompile these objects. DOC># OBJECTS WITH ERRORS ------------------- 0 DOC> The following query reports the number of errors caught during DOC> recompilation. If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC># ERRORS DURING RECOMPILATION --------------------------- 0 SQL> SQL> |
如上操作成功后，数据库的实例升级就完成了。
重启数据库实例，查看相关状态：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
SQL> startup force; ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 2095704 bytes Variable Size 192939432 bytes Database Buffers 83886080 bytes Redo Buffers 6291456 bytes Database mounted. Database opened. SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl OPEN SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production SQL> SQL> col parameter for a38 SQL> col value for a12 SQL> set linesize 300 SQL> set pagesize 300 SQL> SQL> select * from v$option; PARAMETER VALUE -------------------------------------- ------------ Partitioning TRUE Objects TRUE Real Application Clusters FALSE Advanced replication TRUE Bit-mapped indexes TRUE Connection multiplexing TRUE Connection pooling TRUE Database queuing TRUE Incremental backup and recovery TRUE Instead-of triggers TRUE Parallel backup and recovery TRUE Parallel execution TRUE Parallel load TRUE Point-in-time tablespace recovery TRUE Fine-grained access control TRUE Proxy authentication/authorization TRUE Change Data Capture TRUE Plan Stability TRUE Online Index Build TRUE Coalesce Index TRUE Managed Standby TRUE Materialized view rewrite TRUE Materialized view warehouse refresh TRUE Database resource manager TRUE Spatial TRUE Visual Information Retrieval TRUE Export transportable tablespaces TRUE Transparent Application Failover TRUE Fast-Start Fault Recovery TRUE Sample Scan TRUE Duplexed backups TRUE Java TRUE OLAP Window Functions TRUE Block Media Recovery TRUE Fine-grained Auditing TRUE Application Role TRUE Enterprise User Security TRUE Oracle Data Guard TRUE Oracle Label Security FALSE OLAP TRUE Table compression TRUE Join index TRUE Trial Recovery TRUE Data Mining TRUE Online Redefinition TRUE Streams Capture TRUE File Mapping TRUE Block Change Tracking TRUE Flashback Table TRUE Flashback Database TRUE Data Mining Scoring Engine FALSE Transparent Data Encryption TRUE Backup Encryption TRUE Unused Block Compression TRUE Oracle Database Vault FALSE Real Application Testing TRUE SQL> SQL> |
————————————————————————————————————————————
Done.
