1. Database Upgrade Guide
  2. Oracle Database Upgrade Utilities
  3. Using the Pre-Upgrade Information Tool for Oracle Database

Using the Pre-Upgrade Information Tool for Oracle Database

Review these topics to understand and to use the Pre-Upgrade information tool (preupgrade.jar).

Note:

The Pre-Upgrade information tool is a legacy utility. Oracle recommends that you use the AutoUpgrade utility instead.

Parent topic: Oracle Database Upgrade Utilities

About the Pre-Upgrade Information Tool

To determine if your system is ready for upgrading, you can use the legacy Pre-Upgrade Information Tool (preupgrade.jar)

To help to ensure a successful upgrade, Oracle strongly recommends that you run the AutoUpgrade Utility in Analyze processing mode, and to use the AutoUpgrade Utility Fixup, and then complete your upgrade with the method that you prefer. However, you can continue to perform these tasks by using the Pre-Upgrade Information Tool before you begin your upgrade.

If you use the legacy Pre-Upgrade Information tool, then after the upgrade is complete, you can use the postupgrade scripts that it generates to help to assist you with fixing any issues that the tool discovers. To obtain the latest updates, Oracle recommends that you download the most recent version of the tool from My Oracle Support Note 884522.1.

You can run the tool from the operating system command line. The Pre-Upgrade Information Tool creates preupgrade scripts, which fix some issues before you start an upgrade, and postupgrade scripts, which fix some issues after an upgrade is completed.

The Pre-Upgrade Information Tool (preupgrade.jar) creates the following files:

  • The log file preupgrade.log.

    The log file contains the output of the Pre-Upgrade Information Tool.

  • The preupgrade_fixups_pdbname.sql for PDBs, where pdbname is the name of the PDB).

    Before you run the upgrade, you can run the preupgrade fixups script manually in SQL*Plus to resolve many of the issues identified by the preupgrade tool.

  • The postupgrade_fixups_pdbname.sql (for PDBs, where pdbname is the name of the PDB) or postupgrade_fixups.sql script (Non-CDB databases).

    You can run this script to fix issues after the database upgrade is completed.

Related Topics

Parent topic: Using the Pre-Upgrade Information Tool for Oracle Database

Preupgrade Scripts Generated By the Pre-Upgrade Information Tool

You can run preupgrade scripts that the Pre-Upgrade Information Tool generates to fix many issues before you upgrade to the new Oracle Database release.

The location of the preupgrade_fixups.sql and log files depends on how you set output folders, or define the Oracle base environment variable.

If you specify an output directory by using the dir option with the Pre-Upgrade Information Tool, then the output logs and files are placed under that directory in the file path /cfgtoollogs/dbunique_name/preupgrade, where dbunique_name is the name of your source Oracle Database. If you do not specify an output directory when you run the Pre-Upgrade Information Tool, then the output is directed to one of the following default locations:

  • If you do not specify an output directory with DIR, but you have set an Oracle base environment variable, then the generated scripts and log files are created in the following file path:

    Oracle-base/cfgtoollogs/dbunique_name/preupgrade

  • If you do not specify an output directory, and you have not defined an Oracle base environment variable, then the generated scripts and log files are created in the following file path:

    Oracle-home/cfgtoollogs/dbunique_name/preupgrade

The fixup scripts that the Pre-Upgrade Information Tool creates depend on whether your source database is a Non-CDB database, or a CDB database:

  • A log file (preupgrade.log).

    The log file contains log output for the Pre-Upgrade Information Tool.

  • Pre-upgrade fixups SQL scripts, depending on your source database type:

    • Non-CDB: preupgrade_fixups.sql

    • CDB: Two different sets of scripts:

      1. preupgrade_fixups.sql: A consolidated script for all PDBs.

      2. Multiple preupgrade_fixups_pdbname.sql scripts, wherepdbname is the name of the PDB for which a script is generated: Individual scripts, which you run on specific PDBs.

    Run the scripts either by using catcon.pl, or by using SQL*Plus commands. You must run these scripts to fix issues before you start the database upgrade. The scripts resolve many of the issues identified by the preupgrade tool.

    Each issue that the scripts identify includes a description of the problem, and a task that you can carry out to resolve the problem. The preupgrade tool itself does not make changes to your database to correct errors. However, you can run the scripts that it generates to correct identified errors. The scripts fix only those issues that an automated script can fix safely. Preupgrade issues that the automated script cannot fix safely typically require DBA knowledge of user applications. You can address those issues manually.

Parent topic: Using the Pre-Upgrade Information Tool for Oracle Database

Postupgrade Scripts Generated By the Pre-Upgrade Information Tool

After the upgrade, you can run the postupgrade scripts that the Pre-Upgrade Information Tool generates to complete some of the fixups of your upgrade target database.

The Pre-Upgrade Information Tool generates postupgrade fixup scripts, which you can run after the upgrade to fix issues that can be fixed after the upgrade.

The location of the postupgrade SQL scripts and log files depends on how you set output folders, or define the Oracle base environment variable. The postupgrade fixup scripts are placed in the same directory path as the preupgrade fixup scripts.

If you specify an output directory by using the dir option with the Pre-Upgrade Information Tool, then the output logs and files are placed under that directory in the file path /cfgtoollogs/dbunique_name/preupgrade, where dbunique_name is the name of your source Oracle Database. If you do not specify an output directory when you run the Pre-Upgrade Information Tool, then the output is directed to one of the following default locations:

  • If you do not specify an output directory with DIR, but you have set an Oracle base environment variable, then the generated scripts and log files are created in the following file path:

    Oracle-base/cfgtoollogs/dbunique_name/preupgrade

  • If you do not specify an output directory, and you have not defined an Oracle base environment variable, then the generated scripts and log files are created in the following file path:

    Oracle-home/cfgtoollogs/dbunique_name/preupgrade

The postupgrade fixup scripts that the Pre-Upgrade Information Tool creates depend on whether your source database is a Non-CDB database, or a CDB database:

There are two different sets of scripts:

  1. postupgrade_fixups.sql: A consolidated script for all PDBs

  2. Multiple postupgrade_fixups_pdbname.sql scripts, wherepdbname is the name of the PDB for which a script is generated: Individual scripts, which you run on specific PDBs.

Postupgrade issues that the automatic script cannot fix safely typically require DBA knowledge of user applications. You can address those issues manually.

Guidelines for Running Postupgrade Fixup Scripts for Non-CDB Databases

Oracle recommends that when you run the postupgrade scripts, you set the system to spool results to a log file so you can read the output. However, do not spool results to the admin directory:

Parent topic: Using the Pre-Upgrade Information Tool for Oracle Database

Setting Up Environment Variables for the Pre-Upgrade Information Tool

Before you run the Pre-Upgrade Information Tool, set up the user environment variables for the Oracle user that runs the tool.

You must set up the user environment variables for the Pre-Upgrade Information Tool. This example shows how to use shell commands to set up user environment variables to point to an earlier release Oracle home. For multitenant architecture upgrades, you must also open up all the PDBs that you want the tool to analyze.

In this example, the operating system is Linux or Unix, the system identifier is sales01, and the earlier release Oracle home path is /u01/app/oracle/product/12.1.0/dbhome_1

  1. Log in as the Oracle installation owner (oracle).
  2. Set up the user environment variables to point to the earlier release Oracle home that you want to upgrade.

    For example:

    $ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
$ export ORACLE_BASE=/u01/app/oracle
$ export ORACLE_SID=sales01
$ export PATH=.:$ORACLE_HOME/bin:$PATH
  3. (Multitenant architecture upgrades): If you are running the Pre-Upgrade Information Tool in a CDB, then use the command alter pluggable database all open to open the PDBs so that the tool can analyze them.
    For example: Log in to the database and enter the following command to open all PDBs:
    $ sqlplus \ as SYSDBA
.
.
.
SQL> alter pluggable database all open;

Parent topic: Using the Pre-Upgrade Information Tool for Oracle Database

Pre-Upgrade Information Tool (preupgrade.jar) Command

Use Pre-Upgrade Information Tool (preupgrade.jar) commands to check your system before upgrades.

Prerequisites

The Pre-Upgrade Information Tool is in the new release Oracle home, in the file path ORACLE_HOME/rdbms/admin/preupgrade.jar. Oracle has configured it with the system checks necessary for the new Oracle Database release. However, the checks that the tool performs are carried out on the earlier release Oracle Database home. Set up the Oracle user environment variables so that they point to the earlier release Oracle home.

Run the Pre-Upgrade Information Tool by using the Java version in your earlier release Oracle home. For multitenant architecture (CDB and PDB) upgrades, open up all the PDBs that you want the tool to analyze before you run the tool.

Set the environment variables for your user account to point to the earlier release ORACLE_HOME, ORACLE_BASE, and ORACLE_SID.

File Path

The preupgrade.jar file is located in the new Oracle home: 

New_release_Oracle_home/rdbms/admin/preupgrade.jar

You can also copy the preupgrade.jar binaries to a path of your choosing. For example:

/tmp/preupgrade.jar

Syntax

$Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home
/rdbms/admin/preupgrade.jar [FILE|TERMINAL] [TEXT|XML] [DIR output_dir]

Options

Command Option Description

FILE|TERMINAL

Script output location. Use FILE to direct script output to a file. Use TERMINAL to direct output to the terminal. If you do not specify a value, then the default is FILE. If you specify TERMINAL, then screen output is directed to the display, and scripts and logs are placed in the output directory path.

TEXT|XML

Output type. Use XML to specify XML output. If you do not specify an output type, then the default is TEXT.

DIR output_dir

Directs the output to a specific directory. If you do not specify an output directory with the DIR option, then the output is directed to one of the following default locations:

  • If you do not specify an output directory with DIR, but you define an ORACLE_BASE environment variable, then the generated scripts and log files are created.

  • If you do not specify an output directory, and ORACLE_BASE is not defined, then the generated scripts and log files are created in the following path:

    ORACLE_HOME/cfgtoollogs/dbunique_name/preupgrade

-c "pdb1 pdb2 pdb3" (Windows)

-c 'pdb1 pdb2 pdb3' (Linux and UNIX)

Specifies a list of containers inside a CDB that you want to include for processing (an allow list). Provide a space-delimited list of PDBs that you want processed. To specify the list, use single quotes on Linux and UNIX operating systems, and use double quotes on Windows systems.

If you do not specify either -c or -C, then all PDBs in a CDB are processed.

-C "pdb1 pdb2 pdb3" (Windows)

-C 'pdb1 pdb2 pdb3' (Linux and UNIX)

Specifies a list of containers inside a CDB that you want to exclude from processing (a block list). Provide a space-delimited list of PDBs that you want to exclude from processing. To specify the list, use single quotes on Linux and UNIX operating systems, and use double quotes on Windows systems.

If you do not specify either -c or -C, then all PDBs in a CDB are processed.

-loadonly

Loads the DBMS_PREUP package into the database when it is in READ WRITE mode, without carrying out any other action.

You can use this parameter to prepare a given Non-CDB or CDB database so that the DBMS_PREUP package is loaded when you run the Pre-Upgrade Information Tool, and the DB (DB or Container) is in READ-ONLY mode. If you want use the tool to analyze a database in read-only mode, then you must use this command to load the DBMS_PREUP package into the database while it is in READ WRITE mode, before you set it to READ-ONLY mode.

You can also use this parameter with a Read-Only Standby database, in which you load the package in the primary database, and run the package in the standby.

-p password

Provides the password for the user.

If you do not use operating system authentication to connect to the database, then use the -p option to specify a password on the command line. If a username is specified on the command line with -u, but no password specified with -p, then the tool prompts you for a password.

-u username

Provides the user name of the user that you want to use to connect as SYSDBA to the database that you want to check. Use this option only if you do not use operating system authentication to connect to the database

For example, You log in as a user that is not a member of the OSDBA group for the database that you want to check. In that case, the user account does not have operating system authentication privileges for the SYSDBA system privilege. Use the -u and -p option to provide data dictionary authentication to log in as a user with SYSDBA system privileges.

-oh oracle_home

Specifies an Oracle home that you want to check. Provide the path of the Oracle home that you want to check.

If you do not specify an Oracle home path to check, then the Pre-Upgrade Information Tool defaults to the path specified by the user environment variable for the Oracle home. That variable is $ORACLE_HOME on Linux and Unix systems, and %ORACLE_HOME% on Windows systems.

-sid system_identifier

Specifies an Oracle system identifier that you want to check. Provide the ORACLE_SID of the database that you want to check.

-help

Displays the command-line syntax help text.

Example A-1 Non-CDB In the Source Oracle Home Example

  1. Set your user environment variables to point to the earlier release Oracle home.

    $ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
$ export ORACLE_BASE=/u01/app/oracle
$ export ORACLE_SID=sales01
$ export PATH=.:$ORACLE_HOME/bin:$PATH

  2. Run the new release Oracle Database Pre-Upgrade Information Tool on the earlier release Oracle Database server using the environment settings you have set to the earlier release Oracle home. For example:

    $ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/rdbms/admin/preupgrade.jar TERMINAL TEXT

Example A-2 CDB in a Source Oracle Home

  1. Open all the pluggable databases

    SQL> alter pluggable database all open;

  2. Set your user environment variables to point to the earlier release Oracle home.

    $ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
$ export ORACLE_BASE=/u01/app/oracle
$ export ORACLE_SID=sales01
$ export PATH=.:$ORACLE_HOME/bin:$PATH

  3. Run the Pre-Upgrade Information Tool with an inclusion list, using the -c option. In this example, the inclusion list is PDB1 and PDB2, and the command is run on a Linux or UNIX system. The output of the command is displayed to the terminal, and the output is displayed as text. 

    $ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT -c 'pdb1 pdb2'

Parent topic: Using the Pre-Upgrade Information Tool for Oracle Database

Output of the Pre-Upgrade Information Tool

The Pre-Upgrade Information Tool (preupgrade.jar) creates fixup scripts and log files in the output directory that you specify with the DIR command-line option.

When you run the Pre-Upgrade Information Tool, it generates the following files inside the directory that you specify as the output directory.

Log File (preupgrade.log)

The file preupgrade.log is the report that the Pre-Upgrade Information Tool generates whenever you run the command with the FILE option. The log file contains all the tool recommendations and requirements for upgrade. The log file is located in the following path: $ORACLE_BASE/cfgtoollogs/dbua/upgrade/SID/. If you run the command with the TERMINAL option, then the content of this file is output to the display. Refer to the section "Pre-Upgrade Information Tool Output Example" for an example of a log file.

XML DBUA File (upgrade.xml)

If you specify XML file output on the Pre-Upgrade Information Tool command line, then it generates the upgrade.xml file instead of preupgrade.log.

Preupgrade Fixup File (preupgrade_fixups.sql) and Postupgrade Fixup File (postupgrade_fixups.sql)

The Pre-Upgrade Information Tool identifies issues that can block or hinder an upgrade.

Some issues require a DBA to resolve, because it is not possible for the automated script to understand the specific goals of your application. However, other issues do not present any difficulty in resolving. In these cases, the Pre-Upgrade Information Tool automatically generates scripts that contain the SQL statements necessary to resolve the issues. Using these scripts can perform, track, and simplify the work that DBAs must do to resolve potential upgrade issues. The SQL statements that resolve issues before upgrade are placed in the preupgrade_fixups.sql script. The SQL statements that resolve issues after upgrade are placed in the postupgrade_fixups.sql script. When you run the Pre-Upgrade Information tool on a multitenant architecture Oracle Database, you can run the consolidated scripts preupgrade_fixups.sql script and postupgrade_fixups.sql across all the containers. Run the consolidated scripts using catcon.pl.

Both of these fixup files are generated in the output directory that you specify with the Pre-Upgrade Information Tool DIR command-line option.

The script carries out the following steps to resolve pre-upgrade or post-upgrade issues:

  1. For each issue that the Pre-Upgrade Information Tool identifies, it reruns the individual Pre-Upgrade Information Tool check again, to determine if the issue is still present.

  2. If the issue is present, then the tool does one of the following, depending on whether there is a fixup routine for the issue:

    • If there is an Oracle-supplied fixup routine for that issue, then the script executes that fixup routine. It then reruns the Pre-Upgrade Information Tool check again, to confirm that the issue is resolved. If the issue is resolved, then the script displays a message that the issue is resolved.

    • If there is no Oracle-supplied fixup routine, then the script displays a message that the issue is still present.

Review the issues that the script indicates are still present after it completes its checks. Resolve all the issues marked Required before upgrading. You can choose to resolve recommended fixes and informational fixes. You can rerun the preupgrade_fixups.sql and postupgrade_fixups.sql scripts as many times as you want to. You can use the scripts as a progress report to track remaining issues as part of your issue resolution plan.

After you resolve issues identified by the Preupgrade fixup and Postupgrade fixup scripts, Oracle recommends that you rerun the Pre-Upgrade Information Tool (preupgrade.jar) one more time before upgrade. Running the tool one more time helps to confirm that you have resolved all issues that you intend to address.

If you are checking a multitenant environment system (CDBs and PDBs), then the preupgrade_fixups.sql and postupgrade_fixups.sql scripts contain a rollup of the fixup code for all the individual PDBs. They are coded so that only the code for the current PDB runs. You can run the same script in each PDB. The script fixes only the issues in that PDB. As a result, it is easy to use the preupgrade_fixups.sql and postupgrade_fixups.sql with catcon.pl to run fixups across an entire CDB.

Related Topics

Parent topic: Using the Pre-Upgrade Information Tool for Oracle Database

Pre-Upgrade Information Tool Output Example

In this example, you can see how the Pre-Upgrade Information Tool displays recommended fixes, but does not carry out fixes automatically.

You have control over how and when the fixup scripts are run.

The following example shows the output that is generated and written to preupgrade.log by running the Oracle Database 18c Pre-Upgrade Information Tool on a release 12.2.0.1 CDB: 

$ java -jar preupgrade.jar TEXT TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
18.0.0.0.0 on 2018-06-13T15:08:45

Upgrade-To version: 18.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  CDB2
     Container Name:  CDB$ROOT
       Container ID:  1
            Version:  12.2.0.1.0
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  26
  Database log mode:  NOARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID         
  JServer JAVA Virtual Machine           [to be upgraded]  VALID         
  Oracle XDK for Java                    [to be upgraded]  VALID         
  Real Application Clusters              [to be upgraded]  OPTION OFF    
  Oracle Workspace Manager               [to be upgraded]  VALID         
  Oracle Label Security                  [to be upgraded]  VALID         
  Oracle XML Database                    [to be upgraded]  VALID         
  Oracle Java Packages                   [to be upgraded]  VALID         

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  1.  Run 12.2.0.1.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
      objects.  You can view the individual invalid objects with
      
        SET SERVEROUTPUT ON;
        EXECUTE DBMS_PREUP.INVALID_OBJECTS;
      
      3 objects are INVALID.
      
      There should be no INVALID objects in SYS/SYSTEM or user schemas before
      database upgrade.

  2.  Review and remove any unnecessary HIDDEN/UNDERSCORE parameters.
      
      The database contains the following initialization parameters whose name
      begins with an underscore:
      
      _exclude_seed_cdb_view
      
      Remove hidden parameters before database upgrade unless your application
      vendors and/or Oracle Support state differently.  Changes will need to be
      made in the pfile/spfile.

  3.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:
      
        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
      
      Dictionary statistics do not exist or are stale (not up-to-date).
      
      Dictionary statistics help the Oracle optimizer find efficient SQL
      execution plans and are essential for proper upgrade timing. Oracle
      recommends gathering dictionary statistics in the last 24 hours before
      database upgrade.
      
      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  INFORMATION ONLY
  ================
  4.  To help you keep track of your tablespace allocations, the following
      AUTOEXTEND tablespaces are expected to successfully EXTEND during the
      upgrade process.
      
                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                             550 MB       616 MB
      SYSTEM                             700 MB      1117 MB
      TEMP                                22 MB       150 MB
      UNDOTBS1                           315 MB       433 MB
      
      Minimum tablespace sizes for upgrade are estimates.

  5.  No action needed.
      
      Using default parallel upgrade options, this CDB with 1 PDBs will first
      upgrade the CDB$ROOT, and then upgrade at most 1 PDBs at a time using 2
      parallel processes per PDB.
      
      The number of PDBs upgraded in parallel and the number of parallel
      processes per PDB can be adjusted as described in Database Upgrade Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB2 container CDB$ROOT
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@/u01/app/oracle/cfgtoollogs/CDB2/preupgrade/preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  6.  Upgrade the database time zone file using the DBMS_DST package.
      
      The database is using time zone file version 26 and the target 18.0.0.0.0
      release ships with time zone file version 31.
      
      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 18.0.0.0.0 Oracle Database
      Globalization Support Guide.

  7.  (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:
      
        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
      
      Oracle recommends gathering dictionary statistics after upgrade.
      
      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  8.  Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:
      
        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
      
      This recommendation is given for all preupgrade runs.
      
      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.
      
      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB2 container CDB$ROOT
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@/u01/app/oracle/cfgtoollogs/CDB2/preupgrade/postupgrade_fixups.sql


Report generated by Oracle Database Pre-Upgrade Information Tool Version
18.0.0.0.0 on 2018-06-13T15:08:58

Upgrade-To version: 18.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  CDB2
     Container Name:  PDB$SEED
       Container ID:  2
            Version:  12.2.0.1.0
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  26
  Database log mode:  NOARCHIVELOG
           Readonly:  TRUE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID         
  Real Application Clusters              [to be upgraded]  OPTION OFF    
  Oracle Workspace Manager               [to be upgraded]  VALID         
  Oracle XML Database                    [to be upgraded]  VALID         

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  1.  Run 12.2.0.1.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
      objects.  You can view the individual invalid objects with
      
        SET SERVEROUTPUT ON;
        EXECUTE DBMS_PREUP.INVALID_OBJECTS;
      
      6 objects are INVALID.
      
      There should be no INVALID objects in SYS/SYSTEM or user schemas before
      database upgrade.

  2.  Review and remove any unnecessary HIDDEN/UNDERSCORE parameters.
      
      The database contains the following initialization parameters whose name
      begins with an underscore:
      
      _exclude_seed_cdb_view
      
      Remove hidden parameters before database upgrade unless your application
      vendors and/or Oracle Support state differently.  Changes will need to be
      made in the pfile/spfile.

  3.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:
      
        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
      
      Dictionary statistics do not exist or are stale (not up-to-date).
      
      Dictionary statistics help the Oracle optimizer find efficient SQL
      execution plans and are essential for proper upgrade timing. Oracle
      recommends gathering dictionary statistics in the last 24 hours before
      database upgrade.
      
      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  4.  (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.
      
      None of the fixed object tables have had stats collected.
      
      Gathering statistics on fixed objects, if none have been gathered yet, is
      recommended prior to upgrading.
      
      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  INFORMATION ONLY
  ================
  5.  To help you keep track of your tablespace allocations, the following
      AUTOEXTEND tablespaces are expected to successfully EXTEND during the
      upgrade process.
      
                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                             235 MB       500 MB
      SYSTEM                             210 MB       584 MB
      TEMP                                20 MB       150 MB
      UNDOTBS1                           210 MB       412 MB
      
      Minimum tablespace sizes for upgrade are estimates.

  6.  No action needed.
      
      Using default parallel upgrade options, this CDB with 1 PDBs will first
      upgrade the CDB$ROOT, and then upgrade at most 1 PDBs at a time using 2
      parallel processes per PDB.
      
      The number of PDBs upgraded in parallel and the number of parallel
      processes per PDB can be adjusted as described in Database Upgrade Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB2 container PDB$SEED
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@/u01/app/oracle/cfgtoollogs/CDB2/preupgrade/preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  7.  Upgrade the database time zone file using the DBMS_DST package.
      
      The database is using time zone file version 26 and the target 18.0.0.0.0
      release ships with time zone file version 31.
      
      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 18.0.0.0.0 Oracle Database
      Globalization Support Guide.

  8.  (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:
      
        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
      
      Oracle recommends gathering dictionary statistics after upgrade.
      
      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  9.  Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:
      
        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
      
      This recommendation is given for all preupgrade runs.
      
      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.
      
      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB2 container PDB$SEED
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@/u01/app/oracle/cfgtoollogs/CDB2/preupgrade/postupgrade_fixups.sql


==================
PREUPGRADE SUMMARY
==================
  /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/preupgrade.log
  /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/preupgrade_fixups.sql
  /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/postupgrade_fixups.sql

Execute fixup scripts across the entire CDB:

Before upgrade:

1. Execute preupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/ -b preup_CDB2 /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/preupgrade_fixups.sql

2. Review logs under /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/

After the upgrade:

1. Execute postupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/ -b postup_CDB2 /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/postupgrade_fixups.sql

2. Review logs under /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/

Preupgrade complete: 2018-06-13T15:09:00

Parent topic: Using the Pre-Upgrade Information Tool for Oracle Database

Pre-Upgrade Information Tool Warnings and Recommendations for Oracle Database

Analyze any Pre-Upgrade Information Tool warnings before you upgrade to the new release of Oracle Database. For each item that the tool reports, it provides you with information about how to fix the issue or warning.

For more detailed information , refer to My Oracle Support note 472937.1 for information about installed database components and schemas. Refer to My Oracle Support note 753041.1 for information about diagnosing components with NON VALID status.

Related Topics

Parent topic: Using the Pre-Upgrade Information Tool for Oracle Database

Updating Access Control Lists and Network Utility Packages

Use this procedure to update access control lists (ACLs) and Network Utility Packages.

Starting with Oracle Database 12c, the access control of the UTL packages is implemented using Oracle Database Real Application Security. UTL packages include UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR. The access control does not require Oracle XML DB.

  1. Ensure that the logged-in user has the connect privilege for the host and port specified by DBMS_LDAP.init. There is new behavior for the DBMS_LDAP PL/SQL package and the HttpUriType type. Because of this new behavior, you must create or update access control lists (ACLs) after you upgrade to the new Oracle Database release.

    For example, if your application depends on the DBMS_LDAP package, then the error "ORA-24247: network access denied by access control list (ACL)" can occur. To avoid this error, the logged-in user must have the connect privilege for the host and port specified by DBMS_LDAP.init.

  2. If you have any of the following packages installed, then you can be required to reinstall these packages after upgrade:

    • UTL_TCP

    • UTL_SMTP

    • UTL_MAIL

    • UTL_HTTP

    • UTL_INADDR

    Ensure that you have the latest version of these packages for the new Oracle Database release.

See Also:

Oracle Database Real Application Security Administrator's and Developer's Guide for information about configuring access control lists

Parent topic: Pre-Upgrade Information Tool Warnings and Recommendations for Oracle Database

Evaluate Dependencies and Add ACLs for Network Utility Packages

You can receive a warning about network utility package dependencies. Use this procedure to evaluate the dependencies, and provide access by adding the appropriate access control lists (ACLs).

  1. Run the Pre-Upgrade Information Tool.

  2. Check the output from the Pre-Upgrade Information Tool (preupgrade.jar) for warning messages, such as the following example: 

    WARNING: --> Database contains schemas with objects dependent on network packages.
.... Refer to the Database Upgrade Guide for instructions to configure Network ACLs.
.... USER WKSYS has dependent objects.
.... USER SYSMAN has dependent objects.
.... USER FLOWS_010600 has dependent objects.
.

  3. Query the view DBA_DEPENDENCIES to obtain more information about the dependencies. For example: 

    SELECT * FROM DBA_DEPENDENCIES
WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR','DBMS_LDAP')
  AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');

  4. To ensure that the new access controls are part of your upgrade testing, prepare a post-upgrade script to make the scripts available in your database environment.

    Use the package DBMS_NETWORK_ACL_ADMIN to update your database access control lists (ACLs). You use this package to create, assign, and add privileges to the new access controls so that the updated access control packages can work as they did in prior releases. Refer to the example script provided in Oracle Database Real Application Security Administrator’s and Developer’s Guide to see how to use DBMS_NETWORK_ACL_ADMIN to update your access control list.

  5. After the upgrade, grant specific required privileges. Access is based on the usage in the original database.

Related Topics

Parent topic: Pre-Upgrade Information Tool Warnings and Recommendations for Oracle Database

About Database Links with Passwords from Earlier Oracle Database Releases

This information is important only for downgrading to your original database release after performing the upgrade.

During the upgrade to the new Oracle Database release, any passwords in database links are encrypted.

  • To downgrade to the release from which you upgraded, you must drop all of the database links with encrypted passwords before the downgrade. Consequently, the database links are nonexistent in the downgraded database.

  • If you must be able to downgrade to your original release, then save the information about affected database links from the SYS.LINK$ table, so that you can recreate the database links after the downgrade.

  • For information about earlier releases, refer to the original documentation for the Oracle Database release from which you upgraded. Also refer to your platform-specific Oracle Database Installation Guide for the earlier release.

See Also:

Oracle Database Administrator’s Guide for information about authentication and database links

Parent topic: Pre-Upgrade Information Tool Warnings and Recommendations for Oracle Database

About Oracle Database Warnings for TIMESTAMP WITH TIME ZONE Data Type

Oracle Database upgrades include updated time zone data types, which may affect existing TIMESTAMP WITH TIME ZONE data types.

The time zone files supplied with Oracle Database 12c are updated to reflect changes in transition rules for some time zone regions. The changes may affect existing TIMESTAMP WITH TIME ZONE data types.

Oracle recommends that you ensure that you have the latest time zone files before you upgrade the database. If the time zone file version of the database you are upgrading is not the most recent version of the time zone file available for the new release of Oracle Database, then the Pre-Upgrade Information Tool displays a warning and describes how to proceed. The following table describes the warnings and summarizes how to resolve a mismatch in time zone file versions.

Caution:

The TIMESTAMP WITH TIME ZONE data stored in the database can become corrupted during the upgrade if there is a time zone file version mismatch.

Table A-2 Choices for Fixing the Time Zone File Version

Time Zone Version On the Database Being Upgraded When to Fix the Time Zone Files

Earlier than the most current version included in the new database release and the Pre-Upgrade Information Tool displays "Database is using a time zone file older than version n."

After completing the database upgrade.

Use the DBMS_DST PL/SQL package and follow the instructions in "Steps to Upgrade Time Zone File and Timestamp with Time Zone Data. Refer to the following document:

Oracle Database Globalization Support Guide.

Later than the version included in the new database release and the Pre-Upgrade Information Tool displays "Database is using a time zone file greater than version n."

Before beginning the database upgrade.

You must patch the Oracle home by using an RDBMS DST patch with the appropriate patch for the time zone file version in use. Apply the patch for each database that you want to upgrade. Otherwise, the upgrade script terminates without upgrading the database. The RDBMS DST patches are available from My Oracle Support note ID 412160.1.

Related Topics

Parent topic: Pre-Upgrade Information Tool Warnings and Recommendations for Oracle Database