oracle 升级数据字典,升级数据字典，解决ORA-01092: ORACLE instance terminated. Disconnection forced问题...

在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 Area322961408 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 Utility09-11-2014 21:48:21

ComponentStatus         Version  HH:MM:SS

Oracle Database ServerVALID      10.2.0.5.0  00:15:41

JServer JAVA Virtual MachineVALID      10.2.0.5.0  00:06:46

Oracle XDKVALID      10.2.0.5.0  00:00:51

Oracle Database Java PackagesVALID      10.2.0.5.0  00:00:45

Oracle TextVALID      10.2.0.5.0  00:01:07

Oracle XML DatabaseVALID      10.2.0.5.0  00:03:26

Oracle Workspace ManagerVALID      10.2.0.5.0  00:01:21

Oracle Data MiningVALID      10.2.0.5.0  00:00:39

OLAP Analytic Workspace   VALID      10.2.0.5.0  00:00:48

OLAP CatalogVALID      10.2.0.5.0  00:02:01

Oracle OLAP APIVALID      10.2.0.5.0  00:02:01

Oracle interMediaVALID      10.2.0.5.0  00:06:35

SpatialVALID      10.2.0.5.0  00:04:50

Oracle Expression FilterVALID      10.2.0.5.0  00:00:30

Oracle Enterprise ManagerVALID      10.2.0.5.0  00:02:59

Oracle Rule ManagerVALID      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_BGN2014-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_END2014-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>

原文：http://blog.csdn.net/clark_xu/article/details/39215189