1. Database Upgrade Guide
  2. Troubleshooting the Upgrade for Oracle Database

6 Troubleshooting the Upgrade for Oracle Database

Use these troubleshooting tips to address errors or issues that you may encounter while upgrading your database.

Error Upgrading Non-CDB Oracle Databases

If you attempt to upgrade a non-CDB Oracle Database release, you receive the error ORA-O1722: invalid number.

Starting with Oracle Database 21c, you must use the multitenant architecture for Oracle Database upgrades. When you attempt to upgrade a non-CDB Oracle Database release to Oracle Database 21c, and do not upgrade to a multitenant architecture, you receive the following error:

SELECT TO_NUMBER('UPGRADE OF A NON-CDB TO TARGET RELEASE IS NOT SUPPORTED') * ERROR at line 1: ORA-01722: invalid number

To resolve this issue, use one of the Non-CDB to CDB upgrade methods, so that you upgrade to a multitenant architecture.

Parent topic: Troubleshooting the Upgrade for Oracle Database

Fixed View Queries Restriction When Starting Oracle Database in Upgrade Mode

When you start Oracle Database in upgrade mode, you can only run queries on fixed views. If you attempt to run other views or PL/SQL, then you receive errors.

When the database is started in upgrade mode, only queries on fixed views execute without errors. This restriction applies until you either run the Parallel Upgrade Utility (catctl.pl) directly, or indirectly by using the dbupgrade script). Before running an upgrade script, using PL/SQL on any other view, or running queries on any other view returns an error. If you receive any of the errors described in this section, then issue the SHUTDOWN ABORT command to shut down the database, and then correct the problem.

The following list of errors can occur when you attempt to start the new Oracle Database release. Some of these errors write to the alert log, and not to your session.

  • ORA-00401: the value for parameter compatible is not supported by this release

    The COMPATIBLE initialization parameter is set to a value less than 11.2.0.

  • ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE

    The CLUSTER_DATABASE initialization parameter is set to TRUE instead of FALSE.

  • ORA-39700: database must be opened with UPGRADE option

    The STARTUP command was issued without the UPGRADE keyword.

  • Ora-00704: bootstrap failure

    The path variable can be pointing to the earlier release Oracle home.

  • ORA-00336: log file size xxxx blocks is less than minimum 8192 blocks

    A redo log file size is less than 4 MB.

If errors appear listing desupported initialization parameters, then make a note of the desupported initialization parameters, and continue with the upgrade. Remove the desupported initialization parameters the next time you shut down the database.

Related Topics

Parent topic: Troubleshooting the Upgrade for Oracle Database

Resolving PDBs in Restricted Mode After Successful Upgrades

If your upgrade is successful, but the upgraded PDBs are in Restricted Mode, then this may be due to components set to OPTION OFF.

If a PDB is opened in restricted mode, then query pdb_plug_in_violations for errors to see if this result is due to one or more components in that PDB with OPTION OFF status. If the result of your query shows a database option mismatch for the same components, then close and restart the PDB. After the restart, check to see of the PDB status for RESTRICTED mode is changed from YES to NO.

For example, look for Database option mismatch results similar to the following: 

SQL> select time, message from pdb_plug_in_violations where status='PENDING'
and type='ERROR';
TIME -------------------MESSAGE----------------------------------------------

11-SEP-20 12.40.56.096230 PM
Database option APS mismatch: PDB installed version 21.0.0.0.0. CDB installed
version NULL.

11-SEP-20 12.40.56.096789 PM
Database option DV mismatch: PDB installed version 21.0.0.0.0. CDB installed
version NULL.

11-SEP-20 12.40.56.097315 PM

Database option OLS mismatch: PDB installed version 21.0.0.0.0. CDB installed
version NULL.

11-SEP-20 12.40.56.098157 PM
Database option XOQ mismatch: PDB installed version 21.0.0.0.0. CDB installed
version NULL.

Parent topic: Troubleshooting the Upgrade for Oracle Database

Invalid Objects and Premature Use of Postupgrade Tool

Never run the postupgrade status tool for the new Oracle Database release (utlusts.sql) until after you complete the upgrade.

Oracle recommends that you run the postupgrade status tool only after the upgrade process is complete, and after you have run utlrp.sql. If the postupgrade status tool is run before you run @utlrp.sql, then the output of tool may not display the accurate final component status value. If the tool is run before running utlrp.sql, then the component status values may not properly reflect the final state. You can only determine the final component state after running utlrp.sql.

Parent topic: Troubleshooting the Upgrade for Oracle Database

Resolving Oracle Database Upgrade Script Termination Errors

Review this section if you encounter ORA-00942, ORA-00904, or ORA-01722 errors.

If you did not run AutoUpgrade with the preupgrade parameter before starting the upgrade, then the catctl.pl and catupgrd.sql scripts terminate with errors such as the following: 

ORA-00942: table or view does not exist
ORA-00904: "TZ_VERSION": invalid identifier
ORA-01722: invalid number

Note:

Database Upgrade Assistant (DBUA) is desupported. Oracle recommends using AutoUpgrade to upgrade your database.

If you receive any of these errors, then use this procedure to correct the problem:

  1. Enter a SHUTDOWN ABORT command, and wait for the command to complete running.

  2. Revert to the original Oracle home directory

  3. Run the AutoUpgrade utility using the preupgrade parameter, and correct the issues that it reports in the upgrade.xml file.

Related Topics

Parent topic: Troubleshooting the Upgrade for Oracle Database

Troubleshooting Causes of Resource Limits Errors while Upgrading Oracle Database

Review this section if you encounter ORA-01650, ORA-01651, ORA-01652, ORA-01653, ORA-01654, ORA-01655, ORA-0431, ORA-01562, ORA-19815, or other errors that suggest resource limit errors.

If you run out of resources during an upgrade, then increase the resource allocation. After increasing the resource allocation, shut down the instance with SHUTDOWN ABORT, and restart the instance in UPGRADE mode before re-running the catupgrd.sql script. After you fix issues, AutoUpgrade automatically resumes upgrades.

The resources that generally require increases for a new Oracle Database release are as follows:

  • SYSTEM and SYSAUX tablespaces

    If your SYSTEM tablespace size is insufficient, then typically you receive the following error message: 

    ORA-01650: unable to extend rollback segment string by string in tablespace string
ORA-01651: unable to extend save undo segment by string for tablespace string
ORA-01652: unable to extend temp segment by string in tablespace string
ORA-01653: unable to extend table string.string by string in tablespace string
ORA-01654: unable to extend index string.string by string in tablespace string
ORA-01655: unable to extend cluster string.string by string in tablespace string

    To avoid these errors, set AUTOEXTEND ON MAXSIZE UNLIMITED for the SYSTEM and SYSAUX tablespaces.

  • Shared memory

    In some cases, you may require larger shared memory pool sizes. The error message indicates which shared memory initialization parameter you must increase, in the following format:

    ORA-04031: unable to allocate string bytes of shared memory ("string","string","string","string")

    See Also:

    Oracle Database Administrator's Guide for information about using manual shared memory management

  • Rollback segments/undo tablespace

    If you are using rollback segments, then you must have a single large (100 MB) PUBLIC rollback segment online while the upgrade scripts are being run. Smaller public rollback segments should be taken offline during the upgrade. If your rollback segment size is insufficient, then typically you encounter the following error: 

    ORA-01562: failed to extend rollback segment number string

    If you are using an undo tablespace, then be sure it is at least 400 MB.

  • Fast Recovery Area

    If you are using a Fast Recovery Area and it fills up during the upgrade, then the following error appears in the alert log, followed by suggestions for recovering from the problem:

    ORA-19815: WARNING: db_recovery_file_dest_size of string bytes is 98.99%
used, and has string remaining bytes available.

    Identify the root cause of the problem, and take appropriate actions to proceed with the upgrade. To avoid issues during the upgrade, increase the amount of space available in your Fast Recovery Area before starting the upgrade.

Parent topic: Troubleshooting the Upgrade for Oracle Database

Resolving SQL*Plus Edition Session Startup Error for Oracle Database

Use this section to understand and resolve SP2–1540: "Oracle Database cannot startup in an Edition session."

If an upgrade script or a command running in SQL*Plus set the EDITION parameter, then Oracle Database cannot start properly afterward. When you attempt to start the database, you receive the following error: 

SP2-1540: "Oracle Database cannot startup in an Edition session"

To avoid this problem, after running catugrd.sql or any SQL*Plus session where this parameter is changed, exit the SQL*Plus session and restart the instance in a different session.

Parent topic: Troubleshooting the Upgrade for Oracle Database

Error ORA-00020 Maximum Number of Processes Exceeded When Running utlrp.sql

This error may indicate that your Oracle configuration does not have sufficient number of processes available for the recompile.

Refer to Oracle documentation for more details about setting the PROCESSES parameter.

See Also:

Oracle Database Reference

Parent topic: Troubleshooting the Upgrade for Oracle Database

Resolving issues with view CDB_JAVA_POLICY

If the view CDB_JAVA_POLICY becomes invalid, then use this procedure.

After an upgrade to Oracle Database 12c release 2 (12.2) and later releases, or a downgrade from release 12.2 or later releases to 12.1, you can encounter issues with the CDB_JAVA_POLICY view. CDB_JAVA_POLICY can become invalid, or it can encounter errors when you use it in a manner that normally works. If this happens, then connect as SYS, and run the following commands.

Non-CDBs:

alter session set "_ORACLE_SCRIPT"=true;

exec CDBView.create_cdbview(false,'SYS','dba_java_policy','CDB_java_policy');

grant select on SYS.CDB_java_policy to select_catalog_role
/
create or replace public synonym CDB_java_policy for SYS.CDB_java_policy
/

Multitenant architecture systems:

Run these same commands, but run them first in CDB$ROOT, and then in other containers in the CDB.

Parent topic: Troubleshooting the Upgrade for Oracle Database

Continuing Upgrades After Server Restarts (ADVM/ACFS Driver Error)

On Windows platforms, an error may occur related to ADVM or ACFS drivers if a server restarts during an upgrade.

If a server restarts during the upgrade, then you may see one of the following error messages:

ACFS-9427: Failed to unload ADVM/ACFS drivers. A system reboot is recommended

ACFS-9428 Failed to load ADVM/ACFS drivers. A system reboot is recommended.

  • Cause

    The ADVM and ACFS drivers are still in use. You must restart the system to start the new drivers.

  • Action

    Complete the steps as described in the following procedures.

For nodes other than the first node (the node on which the upgrade is started):

  1. Restart the node where the error occurs.

  2. Run the root script on that node again.

For first nodes (the node on which the upgrade is started):

  1. Complete the upgrade of all other nodes in the cluster.

  2. Restart the first node.

  3. Run the root script on the first node again.

  4. To complete the upgrade, log in as root, and run the script configToolAllCommands, located in the path Grid_home/cfgtoollogs/configToolAllCommands.

See Also:

Oracle Grid Infrastructure Installation Guide for your operating system for more information about troubleshooting upgrade issues for clusters

Parent topic: Troubleshooting the Upgrade for Oracle Database

Component Status and Upgrades

Component status settings are affected both by the components that you previously installed, and by the support of those components for upgrades.

Topics:

Parent topic: Troubleshooting the Upgrade for Oracle Database

Understanding Component Status With the Post-Upgrade Status Tool

The Post-Upgrade Status tool, utlusts.sql, reports database component status after an upgrade is completed.

You can run the Post-Upgrade Status Tool utlusts.sql anytime after upgrade, post-upgrade, or after recompiling invalid objects with utlrp.sql.

The following list briefly describes the status values that the Post-Upgrade Status tool reports:

  • INVALID

    When the upgrade completed, some objects for the component remained in an invalid state. If you find no errors in the log file for component upgrades then run the script utlrp.sql. Running this script may change the status of invalid components to VALID without rerunning the entire upgrade. Check the DBA_REGISTRY view after running utlrp.sql.

  • VALID

    The component is valid with no errors.

  • LOADING

    The component is loading

  • LOADED

    The component has successfully finished loading.

  • UPGRADING

    The component is in process being upgraded.

  • UPGRADED

    The component has completed upgrading with no errors.

  • DOWNGRADING

    The component is in process being downgraded.

  • DOWNGRADED

    The component has completed downgrading with no errors.

  • REMOVING

    The component is in process being removed.

  • REMOVED

    The component was not upgraded because it was removed from the database.

  • OPTION OFF

    The server option required for the component was not installed or was not linked with the server. Check the V$OPTION view and the install logs. Install the component or relink the server.

  • NO SCRIPT

    The component upgrade script was not found in $ORACLE_HOME. Check the install logs, install the component software, and rerun the script.

Parent topic: Component Status and Upgrades

Component OPTION OFF Status and Upgrades

The upgrade status of OPTION OFF components is affected both by the support in the target release for a component, and if a component must be upgraded as part of an upgrade.

There are three cases where OPTION OFF components are upgraded, or are not upgraded.

Unsupported Components With Status OPTION OFF

If there is a component in the database that is in the status OPTION OFF, and that component is no longer supported for database upgrades to the target release, then this component is not upgraded. After the upgrade, its version and status remain unchanged.

Supported Components With Status OPTION OFF

If there is a component in the database that is in the status OPTION OFF, but that component is supported for database upgrades to the target release, then this component is upgraded. After the upgrade, the component’s version matches the target release version. The status for this component is either UPGRADED (a successful upgrade), or INVALID (errors). Rerun the upgrade as needed, until all the upgraded components have a status of UPGRADED. Then run utlrp.sql. If a component was in the status OPTION OFF before the upgrade, then after it is upgraded, and its compile and validation is successful, its status reverts back to OPTION OFF.

Supported Components With Required Options That Must Be Upgraded

All components with required options must be upgraded. These components are:

  • RAC

  • SDO

  • APS

  • XOQ

Components that must be upgraded follow the same procedure for upgrades as for standard supported components with status OPTION OFF

Parent topic: Component Status and Upgrades

Example of an Upgrade Summary Report

Upgrade summary reports provide information about the upgrade status of components.

After the upgrade completes, the upgrade utility script utlusts.sql displays an upgrade report.

Example of an Upgrade Summary Report

Oracle Database Release 23 Post-Upgrade Status Tool    02-10-2023 18:17:0
Container Database: MYCDB
[CON_ID: 1 => CDB$ROOT]

Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS

Oracle Server                          UPGRADED      23.1.0.0.0  00:13:01
JServer JAVA Virtual Machine           UPGRADED      23.1.0.0.0  00:02:37
Oracle XDK                             UPGRADED      23.1.0.0.0  00:00:40
Oracle Database Java Packages          UPGRADED      23.1.0.0.0  00:00:06
OLAP Analytic Workspace                UPGRADED      23.1.0.0.0  00:00:12
Oracle Label Security                  UPGRADED      23.1.0.0.0  00:00:07
Oracle Database Vault                  UPGRADED      23.1.0.0.0  00:00:15
Oracle Text                            UPGRADED      23.1.0.0.0  00:00:34
Oracle Workspace Manager               UPGRADED      23.1.0.0.0  00:00:49
Oracle Real Application Clusters       UPGRADED      23.1.0.0.0  00:00:00
Oracle XML Database                    UPGRADED      23.1.0.0.0  00:01:01
Spatial                                UPGRADED      23.1.0.0.0  00:03:43
Oracle OLAP API                        UPGRADED      23.1.0.0.0  00:00:07
Datapatch                                                        00:00:09
Final Actions                                                    00:00:16
Post Upgrade                                                     00:00:00

Total Upgrade Time: 00:21:44 [CON_ID: 1 => CDB$ROOT]

Database time zone version is 32. It is older than current release time
zone version 40. Time zone upgrade is needed using the DBMS_DST package.


Oracle Database Release 23 Post-Upgrade Status Tool    02-10-2023 18:47:1
Container Database: MYCDB
[CON_ID: 3 => CDB1_PDB1]

Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS

Oracle Server                          UPGRADED      23.1.0.0.0  00:20:31
JServer JAVA Virtual Machine           UPGRADED      23.1.0.0.0  00:01:42
Oracle XDK                             UPGRADED      23.1.0.0.0  00:00:38
Oracle Database Java Packages          UPGRADED      23.1.0.0.0  00:00:06
OLAP Analytic Workspace                UPGRADED      23.1.0.0.0  00:00:15
Oracle Label Security                  UPGRADED      23.1.0.0.0  00:00:06
Oracle Database Vault                  UPGRADED      23.1.0.0.0  00:00:18
Oracle Text                            UPGRADED      23.1.0.0.0  00:00:23
Oracle Workspace Manager               UPGRADED      23.1.0.0.0  00:00:41
Oracle Real Application Clusters       UPGRADED      23.1.0.0.0  00:00:00
Oracle XML Database                    UPGRADED      23.1.0.0.0  00:01:06
Spatial                                UPGRADED      23.1.0.0.0  00:03:02
Oracle OLAP API                        UPGRADED      23.1.0.0.0  00:00:07
Datapatch                                                        00:00:06
Final Actions                                                    00:00:13
Post Upgrade                                                     00:00:00

Total Upgrade Time: 00:27:41 [CON_ID: 3 => CDB1_PDB1]

Database time zone version is 32. It is older than current release time
zone version 40. Time zone upgrade is needed using the DBMS_DST package.


Oracle Database Release 23 Post-Upgrade Status Tool    02-10-2023 18:54:5
Container Database: MYCDB
[CON_ID: 2 => PDB$SEED]

Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS

Oracle Server                             VALID      23.1.0.0.0  00:20:33
JServer JAVA Virtual Machine              VALID      23.1.0.0.0  00:01:42
Oracle XDK                                VALID      23.1.0.0.0  00:00:38
Oracle Database Java Packages             VALID      23.1.0.0.0  00:00:06
OLAP Analytic Workspace                   VALID      23.1.0.0.0  00:00:15
Oracle Label Security                     VALID      23.1.0.0.0  00:00:06
Oracle Database Vault                     VALID      23.1.0.0.0  00:00:18
Oracle Text                               VALID      23.1.0.0.0  00:00:23
Oracle Workspace Manager                  VALID      23.1.0.0.0  00:00:38
Oracle Real Application Clusters     OPTION OFF      23.1.0.0.0  00:00:01
Oracle XML Database                       VALID      23.1.0.0.0  00:01:06
Spatial                                   VALID      23.1.0.0.0  00:03:02
Oracle OLAP API                           VALID      23.1.0.0.0  00:00:07
Datapatch                                                        00:00:05
Final Actions                                                    00:07:50
Post Upgrade                                                     00:00:00
Post Compile                                                     00:07:32

Total Upgrade Time: 00:35:16 [CON_ID: 2 => PDB$SEED * ]
Asterisks denotes compilation time has been included during the upgrade process.

Database time zone version is 32. It is older than current release time
zone version 40. Time zone upgrade is needed using the DBMS_DST package.


Upgrade Times Sorted In Descending Order

Total Upgrade Time: 00:35:16 [CON_ID: 2 => PDB$SEED * ]
Total Upgrade Time: 00:27:41 [CON_ID: 3 => CDB1_PDB1]
Total Upgrade Time: 00:21:44 [CON_ID: 1 => CDB$ROOT]
Grand Total Upgrade Time:    [0d:1h:1m:0s]

Parent topic: Component Status and Upgrades

Standard Edition Starter Database and Components with Status OPTION OFF

Starting in Oracle Database 18c (18.1), all OPTION OFF components are upgraded to the new release, but these options are disabled for Oracle Database Standard Edition (SE) remain OPTION OFF.

When you upgrade Oracle Database Standard Edition (SE) starter databases, the components that are not included with starter databases are turned on and upgraded. When utlrp.sql is run, options that are not turned on with your server and not included with SE are reset to OPTION OFF in the DBA_REGISTRY view.

Parent topic: Troubleshooting the Upgrade for Oracle Database

Adjusting Oracle ASM Password File Location After Upgrade

You must create a new password file for Oracle ASM after an Oracle Grid Infrastructure upgrade.

The Oracle ASM password file location is not shown in the command output when you run srvctl config asm after a Grid Infrastructure upgrade. The location of the password file is not automatically passed to the new Oracle ASM disk group. To enable SRVCTL to have the password file location after upgrade, you must advance the diskgroup compatibility setting and create a PWFILE in the disk group. Then SRVCTL reports the configured location of the shared PWFILE.

See Also:

Oracle Automatic Storage Management Administrator's Guide for information about managing shared password files in disk groups

Parent topic: Troubleshooting the Upgrade for Oracle Database

Fixing "Warning XDB Now Invalid" Errors with Pluggable Database Upgrades

Review this topic if you encounter "Warning: XDB now invalid, invalid objects found” errors when upgrading pluggable databases (PDBs).

You can encounter XML object errors when you plug an Oracle Database 12c release 1 (12.1) pluggable database (PDB) into an Oracle Database 12c release 2 (12.2) or later release multitenant container database (CDB).

Common objects (objects with sharing='METADATA LINK' in dba_objects) are created by registering system-generated names in an object-relational XML schema. Those common types are created by registering some ORDSYS schemas with object-relational storage.

The names of these common objects are system-generated, and the names generated in release 12.1 can be different from the names used for these objects in release 12.2 and later releases. Because of these possible name changes, you can find that the release 12.1 object types do not have matching common types in the release 12.2 or later release CDB root.

Resolve this issue using the following procedure:

  1. Query the view PDB_PLUG_IN_VIOLATIONS in the target CDB root to see if there is any action containing 'GetTypeDDL'
    If you find 'GetTypeDDL' actions, then the upgraded PDB has the common objects issue.
  2. Run the PL/SQL packages SET SERVEROUTPUT ON and exec xdb.DBMS_XMLSTORAGE_MANAGE.GetTypeDDL in the target PDB to generate a user-named SQL script (for example, script1.sql).
  3. Run the script you created in step 2 (for example, script1.sql in the source 12.1 CDB to obtain the type creation script for each of the common types for which you are encountering errors
  4. Generate another user-named SQL script (for example, script2.sql) that contains these creation scripts.
  5. Run the script that you created on the source 12.1 CDB (for example, script2.sql) in the target PDB.
The script that you generate from the release 12.1 source CDB type creation scripts generates all of these objects in the target PDB. Making these common objects available in the target PDB should eliminate the invalid XDB errors.

Parent topic: Troubleshooting the Upgrade for Oracle Database

Fixing Failed Upgrades Where Only Datapatch Fails

If only datapatch fails during an upgrade, then rerun datapatch directly.

The Datapatch script is a shell script. In some patching operations, the final post-upgrade patches may not run, due to errors such as ORA-20001. If only the Datapatch script fails, then you do not need to run the upgrade again to fix this issue. Instead, run the datapatch script directly.

To fix a failed datapatch, log in as the Oracle user, and complete this procedure:

  1. Change directory to Opatch inside the upgraded Oracle home. 
    $ cd $ORACLE_HOME/OPatch
  2. Run the datapatch command.

    On Linux and Unix:

    ./datapatch -verbose

    On Microsoft Windows: 

    datapatch -verbose

Parent topic: Troubleshooting the Upgrade for Oracle Database