ORA-39700 in alert log when recover from a full backup

Posted: December 17, 2008 in Uncategorized
ORA-39700: database must be opened with UPGRADE option
Cause: A normal database open was attempted, but the database has not been upgraded to the current server version.
Action: Use the UPGRADE option when opening the database to run catupgrd.sql (for database upgrade), or to run catalog.sql and catproc.sql (after initial database creation).
Complete the following sections to upgrade an Oracle Database 10g release 10.2.0.x to Oracle Database 10g release 10.2.0.4:
1. {Run the Pre-Upgrade Information Tool}
2. {Upgrading a Release 10.2 Database}
3. {Missing Components When Upgrading}
9.3.2.1 Run the Pre-Upgrade Information Tool

If you are upgrading database manually, then you should analyze it by running the Pre-Upgrade Information Tool.

The Pre-Upgrade Information Tool is a SQL script that ships with Oracle Database 10.2. Complete the following procedure to run the Pre-Upgrade Information Tool:
1. Start the database in the UPGRADE mode:
SQL> STARTUP UPGRADE
2. Set the system to spool results to a log file for later analysis:
SQL> SPOOL upgrade_info.log
3. Run the Pre-Upgrade Information Tool:
SQL> @?/rdbms/admin/utlu102i.sql
4. Turn off the spooling of script results to the log file:
SQL> SPOOL OFF
Check the output of the Pre-Upgrade Information Tool in the upgrade_info.log file. The following is an example of the output generated by the Pre-Upgrade Information Tool:

here exemple output

and then

After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:

Note:
If you do not run the catupgrd.sql script as described in this section and you start up a database for normal operation, then ORA-01092: ORACLE instance terminated. Disconnection forced errors will occur and the error ORA-39700: database must be opened with UPGRADE option will be in the alert log.
1. Log in with administrator privileges.
2. For Oracle RAC installations, start listener on each node of the cluster as follows:
C:\> ORACLE_BASE\ORACLE_HOME\bin\srvctl start listener -n node
3. If you are using Automatic Storage Management, start the Automatic Storage Management instance.
4. For single-instance installations, start the listener as follows:
C:\> lsnrctl start
5. For single-instance installations, use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:
C:\> sqlplus /NOLOG
SQL> CONNECT SYS AS SYSDBA
Enter password:SYS_password
Users of single-instance installations now proceed to step 7.
6. For Oracle RAC installations:
1. Use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:
C:\> sqlplus /NOLOG
SQL> CONNECT SYS AS SYSDBA
Enter password: SYS_password
SQL> STARTUP NOMOUNT
2. Set the CLUSTER_DATABASE initialization parameter to FALSE:
SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=spfile;
3. Shut down the database:
SQL> SHUTDOWN
7. Enter the following SQL*Plus commands:
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sql
SQL> SPOOL OFF
8. Review the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script.

This list provides the version and status of each SERVER component in the database.
9. If necessary, rerun the catupgrd.sql script after correcting any problems.
10. Restart the database:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
11. Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.
SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql

Note:
When the 10.2.0.4 patch set is applied to an Oracle Database 10g Standard Edition database, there may be 54 invalid objects after the utlrp.sql script runs. These objects belong to the unsupported components and do not affect the database operation.

Comments
  1. Excellent. Great job for providing solution with step by step detailing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s