Oracle Database: ORA-39700: database must be opened with UPGRADE option

Today I faced below error while restoring my current database to a new server. PFB :-

SQL> alter database open resetlogs;

alter database open resetlogs

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: 21426

Session ID: 133 Serial number: 23

Cause :-

My current database version is 11.2.0.3.0

In new server database version is 11.2.0.4.0

So when I restored 11.2.0.3 backup to 11.2.0.4 database , it got successfully retored . But when I was trying to open the database got above error.

Solution :-

As in new server database version is higher(11.2.0.4.0), so we have to upgrade our restored database(11.2.0.3.0) to same higher version. As new server Oracle Home is at higher version, so dictionary of restored database also has to be at the same level.

Follow the below steps to start your database successfully in above kind of situations :-

$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 18 03:36:30 2014

Enter user-name: /as sysdba

Connected to an idle instance.

SQL> startup upgrade pfile='/ora_backup/amit/restore_test/initAMIT.ora'

ORACLE instance started.

Total System Global Area 1060585472 bytes

Fixed Size 2260000 bytes

Variable Size 633340896 bytes

Database Buffers 402653184 bytes

Redo Buffers 22331392 bytes

Database mounted.

Database opened.

SQL>

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

DOC>#######################################################################

DOC>

DOC> The first time this script is run, there should be no error messages

DOC> generated; all normal upgrade error messages are suppressed.

DOC>

DOC> If this script is being re-run after correcting some problem, then

DOC> expect the following error which is not automatically suppressed:

DOC>

DOC> ORA-00001: unique constraint (<constraint_name>) violated

DOC> possibly in conjunction with

DOC> ORA-06512: at "<procedure/function name>", line NN

DOC>

DOC> These errors will automatically be suppressed by the Database Upgrade

DOC> Assistant (DBUA) when it re-runs an upgrade.

DOC>

DOC>#######################################################################

DOC>#

DOC>######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if the user running this script is not SYS. Disconnect

DOC> and reconnect with AS SYSDBA.

DOC>######################################################################

DOC>#

no rows selected

DOC>######################################################################

DOC> The following statement will cause an "ORA-01722: invalid number"

DOC> error if the database server version is not correct for this script.

DOC> Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", and use

DOC> a different script or a different server.

DOC>######################################################################

DOC>#

no rows selected

Oracle Database 11.2 Post-Upgrade Status Tool 10-18-2014 04:38:30

Component Current Version Elapsed Time

Name Status Number HH:MM:SS

Oracle Server

. VALID 11.2.0.4.0 00:44:37

Oracle Real Application Clusters

. INVALID 11.2.0.4.0 00:00:04

Oracle Workspace Manager

. VALID 11.2.0.4.0 00:00:50

Final Actions

. 00:00:01

Total Upgrade Time: 00:45:35

PL/SQL procedure successfully completed.

SQL>

SQL> SET SERVEROUTPUT OFF

SQL> SET VERIFY ON

SQL> commit;

Commit complete.

SQL>

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> DOC

DOC>#######################################################################

DOC>

DOC> The above sql script is the final step of the upgrade. Please

DOC> review any errors in the spool log file. If there are any errors in

DOC> the spool file, consult the Oracle Database Upgrade Guide for

DOC> troubleshooting recommendations.

DOC>

DOC> Next restart for normal operation, and then run utlrp.sql to

DOC> recompile any invalid application objects.

DOC>

DOC> If the source database had an older time zone version prior to

DOC> upgrade, then please run the DBMS_DST package. DBMS_DST will upgrade

DOC> TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped

DOC> with Oracle.

DOC>

DOC>#######################################################################

DOC>#

SQL>

SQL> Rem Set errorlogging off

SQL> SET ERRORLOGGING OFF;

SQL>

SQL> REM END OF CATUPGRD.SQL

SQL>

SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.

SQL> REM This forces user to start a new sqlplus session in order

SQL> REM to connect to the upgraded db.

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

This script will run for almost an hour. kindly wait for script to complete.

Once the script completed , database is down as you can see above. Kindly start the database with normal option.

SQL> startup pfile='/ora_backup/amit/restore_test/initAMIT.ora'

ORACLE instance started.

Total System Global Area 1060585472 bytes

Fixed Size 2260000 bytes

Variable Size 633340896 bytes

Database Buffers 402653184 bytes

Redo Buffers 22331392 bytes

Database mounted.

Database opened.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

AMIT READ WRITE

SQL> select * from v$version;

BANNER

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

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

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

Run the utlrp script to check details regarding invalid objects.

SQL> @?/rdbms/admin/utlrp

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN 2014-10-18 05:18:42

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

PL/SQL procedure successfully completed.

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_END 2014-10-18 05:23:46

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.

Database opened successfully after upgrade.

I hope this article helped you.

Regards,

Amit Rath

3 comments:

Ricardo RavazzoloMay 23, 2017 at 3:00 AM
Helped a lot, thank you!
Carlos RodriguesAugust 8, 2018 at 3:28 AM
Veeeeery good!
UnknownFebruary 15, 2019 at 12:54 AM
Hi! this metod work in windows platform: 11.2.0.3 base up in new server 11.2.0.4.
On Linux not work: 11.2.0.3 base not up 11.2.0.4 oracle.
not work startup upgrade; command!
error: system01.dbf.

Oracle Database

About Me

Saturday, October 18, 2014

ORA-39700: database must be opened with UPGRADE option

3 comments:

Popular Posts

Followers

Labels