Perform a full backup of the production database.

Although this step is not required, Oracle strongly recommends that you back up your production database.

Review this procedure to understand how to use the postupgrade_fixups.sql scripts for CDB and Non-CDB databases.

The postupgrade fixup scripts are generated when you run the Pre-Upgrade Information Tool (preupgrade.jar). Run the postupgrade scripts any time after completing an upgrade. For both Container Databases (CDBs) with pluggable databases (PDBs), and for Non-CDB databases, the postupgrade fixup scripts provide general warnings, errors, and informational recommendations.

You can run the script either by using the catcon.pl utility, or by using SQL*Plus.

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:

• Non-CDB: postupgrade_fixups.sql

• CDB: 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.

Set the system to spool results to a log file so you can read the output. However, do not spool to the admin directory:

SQL> SPOOL postupgrade.log
SQL> @postupgrade_fixups.sql
SQL> SPOOL OFF

Turn off the spooling of script results to the log file:

SQL> SPOOL OFF

$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/here/ -b postupg /cfgtoollogs/sales1/preupgrade/postupgrade_fixups.sql

%ORACLE_HOME%\perl\bin\perl –I%ORACLE_HOME%\perl\lib –I%ORACLE_HOME%\rdbms\admin %ORACLE_HOME%\rdbms\admin\catcon.pl -l c:\tmp\logdir\ -b postupg c:\cfgtoollogs\sales1\preupgrade\postupgrade_fixups.sql

After an upgrade, or after other database configuration changes, Oracle strongly recommends that you regather fixed object statistics after you have run representative workloads on Oracle Database.

Fixed objects are the X$ tables and their indexes. V$ performance views are defined through X$ tables. Gathering fixed object statistics is valuable for database performance, because these statistics help the optimizer generate good execution plans, which can improve database performance. Failing to obtain representative statistics can lead to suboptimal execution plans, which can cause significant performance problems.

Gather fixed objects statistics by using the DBMS_STATS.GATHER_FIXED_OBJECTS_STATS PL/SQL procedure. DBMS_STATS.GATHER_FIXED_OBJECTS_STATS also displays recommendations for removing all hidden or underscore parameters and events from the INIT.ORA or SPFILE.

Because of the transient nature of X$ tables, you must gather fixed objects statistics when there is a representative workload on the system. If you cannot gather fixed objects statistics during peak load, then Oracle recommends that you do it after the system is in a runtime state, and the most important types of fixed object tables are populated.

To gather statistics for fixed objects, run the following PL/SQL procedure:

SQL> execute dbms_stats.gather_fixed_objects_stats;

For upgraded databases, improve security by using case-sensitive passwords for default user accounts and user accounts.

For greater security, Oracle recommends that you enable case sensitivity in passwords. Case sensitivity increases the security of passwords by requiring that users enter both the correct password string, and the correct case for each character in that string. For example, the password hPP5620qr fails if it is entered as hpp5620QR or hPp5620Qr.

To secure your database, create passwords in a secure fashion. If you have default passwords in your database, then change these passwords. By default, case sensitivity is enforce when you change passwords. Every password should satisfy the Oracle recommended password requirements, including passwords for predefined user accounts.

For new databases created after the upgrade, there are no additional tasks or management requirements.

For better security, find and reset passwords for user accounts that use the 10G password version so that they use later, more secure password versions.

SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

USERNAME                       PASSWORD_VERSIONS
------------------------------ -----------------
JONES                          10G 11G 12C 
ADAMS                          10G 11G
CLARK                          10G 11G
PRESTON                        11G
BLAKE                          10G

1. Query the DBA_USERS view to find users who only use the 10G password version.

   SELECT USERNAME FROM DBA_USERS 
   WHERE ( PASSWORD_VERSIONS = '10G '
   OR PASSWORD_VERSIONS = '10G HTTP ')
   AND USERNAME <> 'ANONYMOUS';
   

2. Configure the database so that it does not run in Exclusive Mode, as follows:
   1. Edit the SQLNET.ALLOWED_LOGON_VERSION_SERVER setting in the sqlnet.ora file so that it is more permissive than the default. For example:

      SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

   2. Restart the database.
3. Expire the users that you found when you queried the DBA_USERS view to find users who only use the 10G password version.

   You must expire the users who have only the 10G password version, and do not have one or both of the 11G or 12C password versions.

   For example:

   ALTER USER username PASSWORD EXPIRE;
   

4. Ask the users whose passwords you expired to log in.

   When the users log in, they are prompted to change their passwords. The database generates the missing 11G and 12C password versions for their account, in addition to the 10G password version. The 10G password version continues to be present, because the database is running in the permissive mode.

5. Ensure that the client software with which the users are connecting has the O5L_NP ability.

   All Oracle Database release 11.2.0.3 and later clients have the O5L_NP ability. If you have an earlier Oracle Database client, then you must install the CPUOct2012 patch.

6. After all clients have the O5L_NP capability, set the security for the server back to Exclusive Mode, as follows:
   1. Remove the SEC_CASE_SENSITIVE_LOGON parameter setting from the instance initialization file, or set SEC_CASE_SENSITIVE_LOGON to TRUE.

      SEC_CASE_SENSITIVE_LOGON = TRUE

   2. Remove the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter from the server sqlnet.ora file, or set the value of SQLNET.ALLOWED_LOGON_VERSION_SERVER in the server sqlnet.ora file back to 12, to set it to an Exclusive Mode.

      SQLNET.ALLOWED_LOGON_VERSION_SERVER = 12

   3. Restart the database.
7. Find the accounts that still have the 10G password version.

   SELECT USERNAME FROM DBA_USERS
   WHERE PASSWORD_VERSIONS LIKE '%10G%' 
   AND USERNAME <> 'ANONYMOUS';

8. Expire the accounts that still have the 10G password version.

   ALTER USER username PASSWORD EXPIRE;

9. Ask these users to log in to their accounts.

   When the users log in, they are prompted to reset their passwords. The database then generates only the 11G and 12C password versions for their accounts. Because the database is running in Exclusive Mode, the 10G password version is no longer generated.

10. Rerun the following query:

    SELECT USERNAME FROM DBA_USERS
    WHERE PASSWORD_VERSIONS LIKE '%10G%' 
    AND USERNAME <> 'ANONYMOUS';

    If this query does not return any results, then it means that no user accounts have the 10G password version. Hence, the database is running in a more secure mode than in previous releases.

Oracle Clusterware and Oracle Automatic Storage Management (Oracle ASM) are both part of an Oracle Grid Infrastructure installation.

If Oracle Grid Infrastructure is installed for a single server, then it is deployed as an Oracle Restart installation with Oracle ASM. If Oracle Grid Infrastructure is installed for a cluster, then it is deployed as an Oracle Clusterware installation with Oracle ASM.

Oracle Restart enhances the availability of Oracle Database in a single-instance environment. If you install Oracle Restart, and there is a temporary failure of any part of the Oracle Database software stack, including the database, listener, and Oracle ASM instance, Oracle Restart automatically restarts the failed component. In addition, Oracle Restart starts all these components when the database host computer is restarted. The components are started in the proper order, taking into consideration the dependencies among components.

Oracle Clusterware is portable cluster software that enables clustering of single servers so that they cooperate as a single system. Oracle Clusterware also provides the required infrastructure for Oracle RAC. In addition, Oracle Clusterware enables the protection of any Oracle application or any other application within a cluster. In any case Oracle Clusterware is the intelligence in those systems that ensures required cooperation between the cluster nodes.

Oracle ASM is installed with Oracle Grid Infrastructure.

In earlier releases, Oracle ASM was installed as part of the Oracle Database installation. Starting with Oracle Database release 11.2, Oracle ASM is installed when you install the Grid Infrastructure components. Oracle ASM shares an Oracle home with Oracle Clusterware.

Review new features as part of your database upgrade plan.

Oracle Database New Features Guide describes many of the new features available in the new Oracle Database release. Determine which of these new features can benefit the database and applications. You can then develop a plan for using these features.

It is not necessary to make any immediate changes to begin using your new Oracle Database software. You can choose to introduce new feature enhancements into your database and applications gradually.

Plan a review of your scripts and procedures, and change as needed.

After familiarizing yourself with the features of the new Oracle Database release, review your database administration scripts and procedures to determine whether any changes are necessary.

Coordinate your changes to the database with the changes that are necessary for each application. For example, by enabling integrity constraints in the database, you may be able to remove some data checking from your applications.

After an upgrade, thresholds for the upgraded Oracle Database Tablespace Alerts are set to null, disabling the alerts.

You must identify tablespaces in the database that are candidates for monitoring, and you must set the appropriate threshold values for these tablespaces.

Starting with Oracle Database 18c and later releases, in newly-created Oracle Database installations, the following values are used as defaults:

• 85% full warning

• 97% full critical

You can use the ALTER TABLE statement to change the data type of a LONG column to CLOB and that of a LONG RAW column to BLOB.

SQL> ALTER TABLE Long_tab MODIFY ( long_col CLOB );

To use the full facilities of unified auditing, you must manually migrate to unified auditing.

In unified auditing, all Oracle Database audit trails (SYS.AUD$ for the database audit trail, SYS.FGA_LOG$ for fine-grained auditing, DVYS.AUDIT_TRAIL$ for Database Vault, and so on) are combined into one single audit trail, which you can view by querying the UNIFIED_AUDIT_TRAIL data dictionary view for single-instance installations and GV$UNIFIED_AUDIT_TRAIL for Oracle Real Application Clusters environments.

• Understanding Unified Auditing Migration Process for Oracle Database
  Decide which audit policies you want to use in the upgraded database.
• Migrating to Unified Auditing for Oracle Database
  Use this procedure for multitenant container (CDB) databases to migrate to unified auditing.
• About Managing Earlier Audit Records After You Migrate to Unified Auditing
  Review, archive, and purge earlier audit trails in preparation for using the unified audit trail.
• Removing the Unified Auditing Functionality
  Use this procedure to remove unified auditing, and to use mixed-mode audit.
• Obtaining Documentation References if You Choose Not to Use Unified Auditing
  You can access documentation listed here to obtain configuration information about how to use non-unified auditing.

Repeat tests on your production database that you carried out on your test database to ensure applications operate as expected.

If you upgraded a test database to the new Oracle Database release, and then tested it, then you can now repeat those tests on the production database that you upgraded to the new Oracle Database release. Compare the results, noting anomalies. Repeat the test upgrade as many times as necessary.

To verify that your applications operate properly with a new Oracle Database release, test the newly upgraded production database with your existing applications. You also can test enhanced functions by adding available Oracle Database features, and then testing them. However, first ensure that the applications operate in the same manner as they did before the upgrade.