升級資料字典，解決ORA-01092: ORACLE instance terminated. Disconnection forced問題，oracleinstance

升級資料字典，解決ORA-01092: ORACLE instance terminated. Disconnection forced問題，oracleinstance

在oracle 執行個體關閉的情況下，Oracle 軟體從10.2.0.1升級到 10.2.0.5 之後，存在的資料庫也要升級。

此時啟動執行個體會報錯 ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> alter database open;

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

第一步，通過startup upgrade啟動執行個體

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 322961408 bytes

Fixed Size             2095992 bytes

Variable Size               100664456 bytes

Database Buffers     213909504 bytes

Redo Buffers                6291456 bytes

Database mounted.

Database opened.

 

第二步，升級資料字典和執行個體等。

SQL>@ORACLE_HOME\rdbms\admin\catupgrd.sql

整個過程需要15分鐘至30分鐘左右

升級完畢顯示如下：

Oracle Database 10.2 Upgrade Status Utility          09-11-2014 21:48:21

Component                               Status         Version  HH:MM:SS

Oracle Database Server                   VALID      10.2.0.5.0  00:15:41

JServer JAVA Virtual Machine             VALID      10.2.0.5.0  00:06:46

Oracle XDK                               VALID      10.2.0.5.0  00:00:51

Oracle Database Java Packages            VALID      10.2.0.5.0  00:00:45

Oracle Text                              VALID      10.2.0.5.0  00:01:07

Oracle XML Database                      VALID      10.2.0.5.0  00:03:26

Oracle Workspace Manager                 VALID      10.2.0.5.0  00:01:21

Oracle Data Mining                       VALID      10.2.0.5.0  00:00:39

OLAP Analytic Workspace                  VALID      10.2.0.5.0  00:00:48

OLAP Catalog                             VALID      10.2.0.5.0  00:02:01

Oracle OLAP API                          VALID      10.2.0.5.0  00:02:01

Oracle interMedia                        VALID      10.2.0.5.0  00:06:35

Spatial                                  VALID      10.2.0.5.0  00:04:50

Oracle Expression Filter                 VALID      10.2.0.5.0  00:00:30

Oracle Enterprise Manager                VALID      10.2.0.5.0  00:02:59

Oracle Rule Manager                      VALID      10.2.0.5.0  00:00:18

.

Total Upgrade Time: 00:53:47

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>#######################################################################

DOC>#

 

第三步，shutdown執行個體。Startup執行個體

第四步，再次編譯無效的應用對象

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

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN 2014-09-11 21:52:02

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-09-11 21:53:40

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>

oracle資料庫啟動出錯ORA-01092: ORACLE instance terminated Disconnection forced 該怎解決 ?

把建庫指令碼跟INIT.ORA貼出來
 
ORA-01092: ORACLE instance terminated Disconnection forced

startup upgrade 只適用於資料庫的補丁程式安裝完畢後，需要登陸資料庫執行一些指令碼的時候。
比如你這個例子，如果資料庫已經升級完，應該馬上執行catupgrade.sql
(具體運行哪個指令碼，你要看一下readme檔案)。