oracle10g升级后启动失败（ORACLE instance terminated. Disconnection forced）

qrqwe

SQL> startup
ORACLE instance started.
Total System Global Area  285212672 bytes
Fixed Size                  1267044 bytes
Variable Size             130026140 bytes
Database Buffers          146800640 bytes
Redo Buffers                7118848 bytes
Database mounted.
ORA-01092: ORACLEinstance terminated. Disconnection forced

查看alert_rhip.log文件（/opt/oracle/admin/rhip/bdump/alert_rhip.log）

Errors in file /opt/oracle/admin/rhip/udump/rhip_ora_17875.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened withUPGRADE option
Tue May 19 17:35:50 CST 2015
Error 704 happened during db open, shuttingdown database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 17875
ORA-1092 signalled during: ALTER DATABASEOPEN...

查看rhip_ora_17875.trc文件
/opt/oracle/admin/rhip/udump/rhip_ora_17875.trc
Oracle Database 10g Enterprise EditionRelease 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
ORACLE_HOME = /opt/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      test
Release:        2.6.18-164.el5
Version:        #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine:        x86_64
Instance name: rhip
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 17875, image: oracle@test(TNS V1-V3)

*** ACTION NAME:() 2015-05-19 17:35:50.802
*** MODULE NAME:(sqlplus@test (TNS V1-V3))2015-05-19 17:35:50.802
*** SERVICE NAME:(SYS$USERS) 2015-05-1917:35:50.802
*** SESSION ID:(159.3) 2015-05-1917:35:50.802
ORA-00704: bootstrap process failure
ORA-39700: database must be opened withUPGRADE option
原因：因为今天进行了数据库升级，数据字典的一些基表内容被修改了。

解决方法：以upgrade模式启动数据库，升级数据字典

1 2 3 4 5 6 7

SQL>startup upgrade    SQL> select status from v$instance;    STATUS ------------------------ OPEN MIGRATE

确认此时instance状态为OPEN MIGRATE（）

      升级数据库的大版本或大的patch的时候总是需要升级现有数据库的数据字典，升级数据字段的原因是因为随着Oracle版本的升级，某些对象的属性需要改变，以便保证系统的的数据词典的完整性和有效性这些变更都是在升级脚本$ORACLE_HOME/rdbms/admin/catupgrd.sql中。

1、以升级模式启动数据库后，开始升级数据字典

1	SQL>@/opt/product/10.2.0/db_1/rdbms/catupgrd.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

PL/SQL procedure successfully completed.    Type created.    Grant succeeded.    Package created.    No errors.    Table created.    PL/SQL procedure successfully completed.    View created.    Synonym created.    进而进一步验证了catupgrd.sql作用，升级完数据字典后，sqlplus下会出现以下信息    TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP RUL       2012-11-23 06:56:47 DBUA_TIMESTAMP RUL       VALID       2012-11-23 06:56:47 COMP_TIMESTAMP UPGRD_END  2012-11-2306:58:01 . Oracle Database 10.2 Upgrade StatusUtility           11-23-2012 06:58:01 . Component                              Status         Version  HH:MM:SS Oracle Database Server                   VALID     10.2.0.5.0  00:07:52 JServer JAVA Virtual Machine             VALID      10.2.0.5.0 00:00:56 Oracle XDK                              VALID      10.2.0.5.0  00:00:23 Oracle Database Java Packages            VALID      10.2.0.5.0 00:00:12 Oracle Text                             VALID      10.2.0.5.0  00:00:18 Oracle XML Database                      VALID     10.2.0.5.0  00:02:02 Oracle Workspace Manager                 VALID     10.2.0.5.0  00:00:36 Oracle Data Mining                       VALID     10.2.0.5.0  00:00:15 OLAP Analytic Workspace                  VALID     10.2.0.5.0  00:00:16 OLAP Catalog                            VALID      10.2.0.5.0  00:00:42 Oracle OLAP API                          VALID     10.2.0.5.0  00:00:31 Oracle interMedia                        VALID     10.2.0.5.0  00:02:21 Spatial                                 VALID      10.2.0.5.0  00:01:32 Oracle Expression Filter                 VALID     10.2.0.5.0  00:00:06 Oracle Enterprise Manager                VALID      10.2.0.5.0 00:00:30 Oracle Rule Manager                      VALID     10.2.0.5.0  00:00:06 . Total Upgrade Time: 00:20:01 DOC>####################################################################### DOC>####################################################################### DOC> DOC>   The above PL/SQL lists theSERVER components in the upgraded DOC>   database, along with theircurrent version and status. DOC> DOC>   Please review the statusand version columns and look for DOC>   any errors in the spool logfile.  If there are errors in the spool DOC>   file, or any components arenot VALID or not the current version, DOC>   consult the Oracle DatabaseUpgrade Guide for troubleshooting DOC>   recommendations. DOC> DOC>   Next shutdown immediate,restart for normal operation, and then DOC>   run utlrp.sql to recompileany invalid application objects. DOC> DOC>####################################################################### DOC>#######################################################################

2、 再次重启数据库

1 2 3

SQL> shutdown immediate               SQL> startup ORACLE instance started.

3、编译无效对象脚本utlrp.sql

        utlrp.sql脚本可以在数据库运行的状态下执行以编译、数据库中的invalid对象,oracle建议在对数据库进行迁移、升级、降级后都运行一遍utlrp.sql以编译无效对象。

以sysdba登陆来执行脚本

[oracle@Oel_10 ~]$ sqlplus / as sysdba

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

SQL> @/opt/product/10.2.0/db_1/rdbms/admin/utlrp.sql 注：要写全脚本路径 TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN  2012-11-2307:04:44    DOC>   The following PL/SQL blockinvokes UTL_RECOMP to recompile invalid DOC>   objects in the database.Recompilation time is proportional to the DOC>   number of invalid objectsin the database, so this command may take DOC>   a long time to execute on adatabase with a large number of invalid DOC>   objects. DOC> DOC>   Use the following queriesto track recompilation progress: DOC> DOC>   1. Query returning thenumber of invalid objects remaining. This DOC>      number shoulddecrease with time. DOC>         SELECTCOUNT(*) FROM obj$ WHERE status IN (4, 5, 6); DOC> DOC>   2. Query returning thenumber of objects compiled so far. This number DOC>      shouldincrease with time. DOC>         SELECTCOUNT(*) FROM UTL_RECOMP_COMPILED; DOC> DOC>   This script automaticallychooses serial or parallel recompilation DOC>   based on the number of CPUsavailable (parameter cpu_count) multiplied DOC>   by the number of threadsper CPU (parameter parallel_threads_per_cpu). DOC>   On RAC, this number isadded across all RAC nodes. DOC> DOC>   UTL_RECOMP usesDBMS_SCHEDULER to create jobs for parallel DOC>   recompilation. Jobs arecreated without instance affinity so that they DOC>   can migrate across RACnodes. Use the following queries to verify DOC>   whether UTL_RECOMP jobs arebeing created and run correctly: DOC> DOC>   1. Query showing jobscreated by UTL_RECOMP DOC>         SELECTjob_name FROM dba_scheduler_jobs DOC>           WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC> DOC>   2. Query showing UTL_RECOMPjobs that are running DOC>         SELECTjob_name FROM dba_scheduler_running_jobs DOC>           WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC>#    PL/SQL procedure successfully completed.       TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_END  2012-11-2307:05:46       PL/SQL procedure successfully completed.    DOC> The following query reports thenumber of objects that have compiled DOC> with errors (objects that compilewith errors have status set to 3 in DOC> obj$). If the number is higherthan expected, please examine the error DOC> messages reported with each object(using SHOW ERRORS) to see if they DOC> point to system misconfigurationor resource constraints that must be DOC> fixed before attempting torecompile these objects. DOC>#    OBJECTS WITH ERRORS -------------------                  0    DOC> The following query reports thenumber of errors caught during DOC> recompilation. If this number isnon-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORSto see if any of these errors DOC> are due to misconfiguration orresource constraints that must be DOC> fixed before objects can compilesuccessfully. DOC>#    ERRORS DURING RECOMPILATION ---------------------------                          0    PL/SQL procedure successfully completed SQL> shutdown  immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.    Total System Global Area 7516192768 bytes Fixed Size                 2107936 bytes Variable Size           1258292704 bytes Database Buffers        6241124352 bytes Redo Buffers              14667776 bytes Database mounted. Database opened.

1 2 3 4 5 6 7 8

SQL> select * from v$version;    //查看数据库版本 BANNER -------------------------------------------------------------------------------- Oracle Database 10g Enterprise EditionRelease 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

到此为止，升级工作初步完成