ORACLE11.2.0.4 版本升级ORA-39700: database must be opened with UPGRADE option问题解决记录-CSDN博客

本文链接：https://blog.csdn.net/csdnhsh/article/details/95769438

1，迁移报错记录

升级迁移报错记录，将所有的rman操作顺利执行，参考以前的博客文章：http://blog.csdn.net/mchdba/article/details/61624015，但是在open数据库的时候报错，如下所示：

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> alter database open ;

alter database open

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-39700: database must be opened with UPGRADE option

Process ID: 31488

Session ID: 1886 Serial number: 19

2，报错原因分析

Rman备份是11.2.0.1，所以在恢复的过程中，全备会将一些基础信息表以及系统信息表都恢复成11.2.0.1的信息，所以在恢复recover完打开数据库，就和现有版本11.2.0.4冲突了，而没法open database。

所以需要执行一些脚本，保证基础sys的信息和11.2.0.4同步，大概步骤如下：

（1）特殊方式启动

SQL> startup upgrade;

（2）查看预升级信息

SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql

（3）执行升级脚本

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql 1小时左右

startup

SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql

SQL> @$ORACLE_HOME/rdbms/admin/catuppst.sql

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=true SCOPE=SPFILE;

（4）重启数据库

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup;

3，执行升级脚本

需要执行升级脚本：$ORACLE_HOME\rdbms\admin\catupgrd.sql;

SQL> $ORACLE_HOME\rdbms\admin\catupgrd.sql;

SP2-0734: unknown command beginning "$ORACLE_HO..." - rest of line ignored.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@pldb2 oracle]$ rlwrap sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 16 14:08:48 2017

Connected to an idle instance.

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 6.6141E+10 bytes

Fixed Size 2269672 bytes

Variable Size 7381978648 bytes

Database Buffers 5.8653E+10 bytes

Redo Buffers 103772160 bytes

Database mounted.

Database opened.

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

SP2-0310: unable to open file "$ORACLE_HOME\rdbms\admin\catupgrd.sql"

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql;

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN 2017-03-16 14:09:40

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

DECLARE

ERROR at line 1:

ORA-04063: package body "SYS.DBMS_SQLTUNE" has errors

ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_SQLTUNE"

ORA-06512: at "SYS.UTL_RECOMP", line 783

ORA-06512: at line 4

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_END 2017-03-16 14:09:42

DOC> The following query reports the number of objects that have compiled

DOC> with errors.

DOC>

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

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

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

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

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

SQL>

重启数据库：

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 6.6141E+10 bytes

Fixed Size 2269672 bytes

Variable Size 7381978648 bytes

Database Buffers 5.8653E+10 bytes

Redo Buffers 103772160 bytes

Database mounted.

Database opened.

SQL>