ORA-39700: database must be opened with UPGRADE option

背景

一、检查升级脚本
su - oracle
ls -la $ORACLE_HOME/rdbms/admin/catupgrd.sql
ls -la $ORACLE_HOME/rdbms/admin/utlu112s.sql
ls -la $ORACLE_HOME/rdbms/admin/catuppst.sql
ls -la $ORACLE_HOME/rdbms/admin/utlrp.sql
※如果檔案都存在就可以進行下一步驟

二、以升级模式启动
SQL>startup upgrade;

三、查看环境
SQL>@$ORACLE_HOME/rdbms/admin/utlu112i.sql

四、执行
SQL>@$ORACLE_HOME/rdbms/admin/catupgrd.sql
SQL>@$ORACLE_HOME/rdbms/admin/utlu112s.sql
SQL>@$ORACLE_HOME/rdbms/admin/catuppst.sql
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

中间有问题，重新执行2

五、重新启动
SQL>shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>startup;

其它参考：

HOW TO RESOLVE THE ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-39700: database must be opened with UPGRADE option

HOME » HOW TO RESOLVE THE ORA-01092: ORACLE INSTANCE TERMINATED. DISCONNECTION FORCED ORA-00704: BOOTSTRAP PROCESS FAILURE ORA-39700: DATABASE MUST BE OPENED WITH UPGRADE OPTION

In this tutorial, we are going to learn about, “How to resolve these ORA ERRORS: ORA-01092, ORA-00704 and ORA-39700”.

 

I got the below type of error while startup the database. I workaround and resolved the issue.

ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option

 

If you get this type of error in the future do follow below steps:

[oracle@oracle19c ~]$ . testdb.env
[oracle@oracle19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 24 10:18:22 2021

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  914358272 bytes
Fixed Size                  2930800 bytes
Variable Size             239077264 bytes
Database Buffers          666894336 bytes
Redo Buffers                5455872 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 17462
Session ID: 355 Serial number: 8413

Step 1:

We should do shutdown the database and again startup with upgrade mode, it will take you to up the database without errors.

[oracle@oracle19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 24 10:18:22 2021

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area  914358272 bytes
Fixed Size                  2930800 bytes
Variable Size             239077264 bytes
Database Buffers          666894336 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.

FOR ORACLE-RAC

$ sqlplus / as sysdba   SQL> startup nomount;   SQL> alter system set cluster_database=FALSE scope=spfile sid='*';   SQL> shutdown immediate;   SQL> startup upgrade   ORACLE instance started. Total System Global Area 1996486272 bytes Fixed Size          8898176 bytes Variable Size         704643072 bytes Database Buffers     1275068416 bytes Redo Buffers            7876608 bytes Database mounted. Database opened.

After finishing your work, don’t forget to return cluster_database parameter to TRUE and restart your database:

SQL> alter system set cluster_database=TRUE scope=spfile sid='*';

 

Step 2:

Now after the database came up, Then we should run the below script as a sys user.

Connect as sys user “/ as sysdba” and execute the below SQL file into the database.

 SQL>@?/rdbms/admin/catalog.sql;

SQL>@?/rdbms/admin/catproc.sql;

 

Step 3:

After completion of SQL file execution. Now we want to check the status of the database by using the below command. Then the status should be in “OPEN MIGRATE” status.

SQL> select NAME,open_mode from v$database;

NAME OPEN_MODE
——— ——————–
TESTDB READ WRITE

 

SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
—————- ————
testdb OPEN MIGRATE

 

Step4:

All are set then now we want to shut immediate and startup the database as normally then that old error will not come.

 

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

 

SQL> startup

ORACLE instance started.
Total System Global Area  914358272 bytes
Fixed Size                  2930800 bytes
Variable Size             239077264 bytes
Database Buffers          666894336 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.

 

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
TESTDB READ WRITE PRIMARY

 

 

ORA-01105: mount is incompatible with mounts by other instances – ORA-19808: recovery destination parameter mismatch

Published 8 years ago by Jignesh Jethwa

 

After reboot of both the RAC nodes, we encountered “ORA-01105: mount is incompatible with mounts by other instances” & “ORA-19808: recovery destination parameter  mismatch” oracle error, after diagnosis i came to know about the size of the db recovery parameter, i.e. db_recovery_file_dest_size is mismatch over the nodes.

After setting identical size of the db recovery file destination, infected node up and running smooth as expected.

Error Logs:

 SQL> startup;

 ORACLE instance started.

 Total System Global Area 4275781632 bytes

 Fixed Size 2235208 bytes

 Variable Size 1694500024 bytes

 Database Buffers 2566914048 bytes

 Redo Buffers 12132352 bytes

 ORA-01105: mount is incompatible with mounts by other instances

 ORA-19808: recovery destination parameter mismatch

DB recovery parameter ‘db_recovery_file_dest_size’ verification on 2nd instance by issuing the following command:

 SQL> show parameter recovery

 NAME TYPE VALUE

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

 db_recovery_file_dest string +DATA

 db_recovery_file_dest_size big integer 150G

 recovery_parallelism integer 0

DB recovery parameter ‘db_recovery_file_dest_size’ verification on 1st instance by issuing the following command:

 SQL> show parameter recovery

 NAME TYPE VALUE

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

 db_recovery_file_dest string +DATA

 db_recovery_file_dest_size big integer 100G

 recovery_parallelism integer 0

According to above verification logs, highlighted area mismatched over the RAC nodes.

Workaround: Set ‘db_recovery_file_dest_size’ at 2nd instance to 100GB, as follows:

SQL> alter system set db_recovery_file_dest_size = 100G scope=spfile;

 System altered.

Bounce 2nd instance by normal shutdown and restart, as follows:

SQL> shutdown

 ORA-01507: database not mounted

 

 ORACLE instance shut down.

SQL> startup

 ORACLE instance started.

 Total System Global Area 4275781632 bytes

 Fixed Size 2235208 bytes

 Variable Size 1694500024 bytes

 Database Buffers 2566914048 bytes

 Redo Buffers 12132352 bytes

 Database mounted.

 Database opened.

By above workaround, Infected node up and running smooth.

 SQL> select inst_name from v$active_instances;

 INST_NAME

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

 database1:>>Instance_I<<

 database2:>>Instance_II<<