1. Database Upgrade Guide
  2. Preparing to Upgrade Oracle Database
  3. Database Preparation Tasks to Complete Before Starting Oracle Database Upgrades

Database Preparation Tasks to Complete Before Starting Oracle Database Upgrades

Ensure that you have completed these database preparation tasks before starting an Oracle Database upgrade.

Parent topic: Preparing to Upgrade Oracle Database

Release Updates and Requirements for Upgrading Oracle Database

Before starting upgrades, update your new release Oracle home to the latest Release Update (Update).

The software for new Oracle Database releases contains a full release that includes all the latest updates for Oracle Database at the time of the release.

Before you start an upgrade, Oracle strongly recommends that you update your new release Oracle home to the latest quarterly Release Update (Update).

My Oracle Support provides detailed notes about how you can obtain the updates, as well as tools for lifecycle management.. For example:
  • My Oracle Support note 2118136.2 contains a download assistant to help you select the updates, revisions, Patch Set Updates (PSU), SPU (CPU), Bundle Patches, Patchsets, and Base Releases that you need for your environment. Oracle highly recommends that you start here.

  • My Oracle Support note 1227443.1 contains a list of Oracle Database PSU/BP/Update/Revision known issues. This note provides information about all known issues notes for Oracle Database, Oracle Grid Infrastructure, and the Oracle JavaVM Component (OJVM).

Related Topics

Parent topic: Database Preparation Tasks to Complete Before Starting Oracle Database Upgrades

Copying Transparent Encryption Oracle Wallets

If you use Oracle wallet with Transparent Data Encryption (TDE), then ensure that the new release sqlnet.ora has a proper configuration for the wallet location (network/admin) folder.

Note:

This procedure using sqlnet.ora to configure keystores is deprecated. Oracle recommends that you use the instance initialization file-based approach using the WALLET_ROOT and TDE_CONFIGURATION initialization parameters. After the upgrade, review and configure the WALLET_ROOT initialization parameter.

If you use the sqlnet.ora file to configure keystores, then you must copy sqlnet.ora and the wallet file manually to a keystore location outside of the Oracle home. Wallets should be stored in a location outside of an Oracle Home. If you move the wallet, then you must update sqlnet.ora before starting the upgrade. For auto-login wallets, you must also copy the cwallet.sso file manually to the new keystore location.

  1. Log in as an authorized user.
  2. Manually copy the sqlnet.ora file, and the wallet file, ewallet.p12, to the new release Oracle home.
  3. If you have enabled an auto-login wallet, then also copy the cwallet.sso file to the new release Oracle home. There is no need to complete the next step. If you have not enabled an auto-login wallet, then proceed to the next step.
  4. If you have not enabled an auto-login wallet, then open the Oracle wallet in mount.

    For example:

    SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN

    To create a local auto-login keystore for a software keystore, use the following syntax:

    ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE 
FROM KEYSTORE 'keystore_location' 
IDENTIFIED BY software_keystore_password;

    In this specification:

    • LOCAL enables you to create a local auto-login software keystore. Otherwise, omit this clause if you want the keystore to be accessible by other computers.

    • keystore_location is the path to the keystore directory location of the keystore that is configured in the sqlnet.ora file.

    • software_keystore_password is the existing password of the configured software keystore.

Related Topics

Parent topic: Database Preparation Tasks to Complete Before Starting Oracle Database Upgrades

Recommendations for Oracle Net Services When Upgrading Oracle Database

You must ensure that the listener is running in your new release Oracle home.

If the Oracle Database that you are upgrading does not have a listener configured, then before you start the upgrade, you must run Oracle Net Configuration Assistant (NETCA) to configure the listening protocol address and service information for the new release of Oracle Database, including a listener.ora file. The current listener is backward-compatible with earlier Oracle Database releases.

If you are upgrading Oracle Real Application Clusters Oracle Database, or a release older than Oracle Database 12c, then review the following additional information.

When you upgrade an Oracle RAC database with DBUA, it automatically migrates the listener from your old Oracle home to the new Oracle Grid Infrastructure home. You must administer the listener by using the lsnrctl command in the Oracle Grid Infrastructure home. Do not attempt to use the lsnrctl commands from Oracle home locations for earlier releases.

In Oracle Database, underlying net services parameters enable data compression, which reduces the size of the session data unit that is transmitted over a SQL TCP connection.

The following new parameters for the sqlnet.ora file specify compression, and the preferred compression scheme:

  • SQLNET.COMPRESSION

  • SQLNET.COMPRESSION_LEVELS

  • SQLNET.COMPRESSION_THRESHOLD

These parameters, which were introduced with Oracle Database 12c, are not supported in earlier releases.

Related Topics

Parent topic: Database Preparation Tasks to Complete Before Starting Oracle Database Upgrades

Understanding Password Case Sensitivity and Upgrades

By default, Oracle Database 12c Release 2 (12.2) and later releases are upgraded to an Exclusive Mode. Exclusive Modes do not support case-insensitive password-based authentication.

Accounts that have only the 10G password version become inaccessible when the server runs in an Exclusive Mode.

In previous Oracle Database releases, you can configure the authentication protocol so that it allows case-insensitive password-based authentication by setting SEC_CASE_SENSITIVE_LOGON=FALSE. Starting with Oracle Database 12c release 2 (12.2), the default password-based authentication protocol configuration excludes the use of the case-insensitive 10G password version. By default, the SQLNET.ORA parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12, which is an Exclusive Mode. When the database is configured in Exclusive Mode, the password-based authentication protocol requires that one of the case-sensitive password versions (11G or 12C) is present for the account being authenticated. This mode excludes the use of the 10G password version used in earlier releases. After upgrading to Oracle Database 12c release 2 and later releases, accounts that have only the case-insensitive 10G password version become inaccessible. This change occurs because the server runs in an Exclusive Mode by default. When Oracle Database is configured in Exclusive Mode, it cannot use the old 10G password version to authenticate the client. The server is left with no password version with which to authenticate the client.

For greater security, Oracle recommends that you leave case-sensitive password-based authentication enabled. This setting is the default. However, you can temporarily disable case-sensitive authentication during the upgrade to new Oracle Database releases. After the upgrade, you can then decide if you want to enable the case-sensitive password-based authentication feature as part of your implementation plan to manage your password versions.

Before upgrading, Oracle recommends that you determine if this change to the default password-based authentication protocol configuration affects you. Perform the following checks:

  • Identify if you have accounts that use only 10G case-insensitive password authentication versions.

  • Identify if you have Oracle Database 11g release 2 (11.2.0.3) database or earlier clients that have not applied critical patch update CPUOct2012, or a later patch update, and have any account that does not have the case-insensitive 10G password version.

  • Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE. Setting this parameter to FALSE prevents the use of the case-sensitive password versions (the 11G and 12C password versions) for authentication.

Options for Accounts Using Case-Insensitive Versions

If you have user accounts that have only the case-insensitive 10G password version, then you must choose one of the following alternatives:

  • Before upgrade, update the password versions for each account that has only the 10G password version. You can update the password versions by expiring user passwords using the 10G password version, and requesting that these users log in to their account. When they attempt to log in, the server automatically updates the list of password versions, which includes the case-sensitive password versions.

  • Change the setting of the SQLNET.ORA parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER to any of the settings that are not Exclusive Mode. For example: SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

Related Topics

Parent topic: Database Preparation Tasks to Complete Before Starting Oracle Database Upgrades

Checking for Accounts Using Case-Insensitive Password Version

Use these procedures to identify if the Oracle Database that you want to upgrade has accounts or configuration parameters that are using a case-insensitive password version.

By default, in Oracle Database 12c release 2 (12.2) and later releases, the 10G password version is not generated or allowed.

If you do not set SQLNET.ALLOWED_LOGON_VERSION_SERVER to a permissive authentication protocol that permits case-insensitive versions, and you do not want user accounts authenticated with case-insensitive password versions to be locked out of the database, then you must identify affected accounts, and ensure that they are using case-sensitive password versions.

Example 2-1 Finding User Accounts That Use Case-Insensitive (10G) Version

Log in to SQL*Plus as an administrative user, and enter the following SQL query:

SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

The following result shows password versions for the accounts:

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

In this example, the backgrounds for each user account password verification version in use are different:

  • JONES was created in Oracle Database 10G, and the password for JONES was reset in Oracle Database 12C when the setting for the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter was set to 8. As a result, this password reset created all three versions. 11G and 12C use case-sensitive passwords.

  • ADAMS and CLARK were originally created with the 10G version, and then 11G, after they were imported from an earlier release. These account passwords were then reset in 11G, with the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to TRUE.

  • The password for BLAKE was created with the 10G version, and the password has not been reset. As a result, user BLAKE continues to use the 10G password version, which uses a case-insensitive password.

The user BLAKE has only the 10G password version before upgrade: 

SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

USERNAME PASSWORD_VERSIONS
------------------------------ -----------------
BLAKE 10G

If you upgrade to a new Oracle Database release without taking any further action, then this account becomes inaccessible. Ensure that the system is not configured in Exclusive Mode (by setting the SQLNET.ORA parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER to a more permissive authentication mode) before the upgrade.

Example 2-2 Fixing Accounts with Case-Insensitive Passwords

Complete the following procedure:

  1. Use the following SQL query to find the accounts that only have the 10G password version: 

          select USERNAME
         from DBA_USERS
        where ( PASSWORD_VERSIONS = '10G '
               or PASSWORD_VERSIONS = '10G HTTP ')
          and USERNAME <> 'ANONYMOUS';

  2. Configure the system so that it is not running in Exclusive Mode by editing the setting of the SQLNET.ORA parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER to a level appropriate for affected accounts. For example: 

    SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

    After you make this change, proceed with the upgrade.

  3. After the upgrade completes, use the following command syntax to expire the accounts you found in step 1, where username is the name of a user returned from the query in step 1:

    ALTER USER username PASSWORD EXPIRE;

  4. Ask the users for whom you have expired the passwords to log in.

  5. When these users log in, they are prompted to reset their passwords. The system internally generates the missing 11G and 12C password versions for their account, in addition to the 10G password version. The 10G password version is still present, because the system is running in the permissive mode.

  6. Ensure that the client software with which users are connecting has the O5L_NP capability flag.

    Note:

    All Oracle Database release 11.2.0.4 and later clients, and all Oracle Database release 12.1 and later clients have the O5L_NP capability. Other clients require the CPUOct2012 patch to acquire the O5L_NP capability.

    The O5L_NP capability flag is documented in Oracle Database Net Services Reference, in the section on the parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER.

  7. After all clients have the O5L_NP capability, raise the server security back to Exclusive Mode by using the following procedure:

    1. Remove the SEC_CASE_SENSITIVE_LOGON setting from the instance initialization file, or set the SEC_CASE_SENSITIVE_LOGON instance initialization parameter to TRUE. For example:

      SEC_CASE_SENSITIVE_LOGON = TRUE

    2. Remove the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter from the server SQLNET.ORA file, or set it back to Exclusive Mode by changing the value of SQLNET.ALLOWED_LOGON_VERSION_SERVER in the server SQLNET.ORA file back to 12. For example:

      SQLNET.ALLOWED_LOGON_VERSION_SERVER = 12

  8. Use the following SQL query to find the accounts that still have the 10G password version: 

           select USERNAME
         from DBA_USERS
        where PASSWORD_VERSIONS like '%10G%'
          and USERNAME <> 'ANONYMOUS';

  9. Use the list of accounts returned from the query in step 8 to expire all the accounts that still have the 10G password version. Expire the accounts using the following syntax, where username is a name on the list returned by the query:

    ALTER USER username PASSWORD EXPIRE;

  10. Request the users whose accounts you expired to log in to their accounts.

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

  11. Check that the system is running in a secure mode by rerunning the query from step 1. Ensure that no users are found. When the query finds no users, this result means that no 10G password version remains present in the system.

Example 2-3 Checking for the Presence of SEC_CASE_SENSITIVE_LOGON Set to FALSE

Oracle Database does not prevent the use of the FALSE setting for SEC_CASE_SENSITIVE_LOGON when the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter is set to 12 or 12a. This setting can result in all accounts in the upgraded database becoming inaccessible. 

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE
You can change this parameter by using the following command:
SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;

System altered.

Note:

Unless the value for the parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER is changed to a version that is more permissive than 12, such as 11, do not set the SEC_CASE_SENSITIVE_LOGON parameter to FALSE.

Related Topics

Parent topic: Database Preparation Tasks to Complete Before Starting Oracle Database Upgrades

Check for Profile Scripts (glogin.sql and login.sql)

For all upgrade methods, Oracle recommends that you run upgrades without the use of profile scripts.

Depending on the content of profile scripts (glogin.sql and login.sql), there is a risk that these scripts can interfere with the upgrade of Oracle Database, and that you can encounter an UPG-1400 UPGRADE FAILED error, or Unexpected error encountered in catcon, or ORA-04023: Object SYS.STANDARD could not be validated or authorized. Oracle recommends that you remove the site profile script (glogin.sql) from the target Oracle home (located in the Oracle home under /sqlplus/admin ) before starting the upgrade. Also ensure that no user profile script is defined, either in the current directory, or specified using the environment variable SQLPATH.

Related Topics

Parent topic: Database Preparation Tasks to Complete Before Starting Oracle Database Upgrades

Running Upgrades with Read-Only Tablespaces

Use the Parallel Upgrade Utility with the -T option to take schema-based tablespaces offline during upgrade.

Oracle Database can read file headers created in earlier releases, so you are not required to do anything to them during the upgrade. The file headers of READ ONLY tablespaces are updated when they are changed to READ WRITE.

If the upgrade suffers a catastrophic error, so that the upgrade is unable to bring the tablespaces back online, then review the upgrade log files. The log files contain the actual SQL statements required to make the tablespaces available. To bring the tablespaces back online, you must run the SQL statements in the log files for the database, or run the log files for each PDB.

Viewing Tablespace Commands in Upgrade Log Files

If a catastrophic upgrade failure occurs, then you can navigate to the log directory (Oracle_base/cfgtoologs/dbua), and run commands in the log files manually to bring up tablespaces. You can view tablespace commands in the following log files:

  • Non-CDB Upgrades: catupgrd0.log

  • PDB databases: catupgrdpdbname0.log, where pdbname is the name of the PDB that you are upgrading.

At the beginning of each log file, you find SQL statements such as the following, which sets tables to READ ONLY: 

SQL> ALTER TABLESPACE ARGROTBLSPA6 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE ARGROTBLSPB6 READ ONLY;

Tablespace altered.

Near the end of each log file, you find SQL statements to reset tables to READ WRITE: 

SQL> ALTER TABLESPACE ARGROTBLSPA6 READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE ARGROTBLSPB6 READ WRITE;

Tablespace altered.

See Also:

Oracle Database Administrator’s Guide for information about transporting tablespaces between databases

Parent topic: Database Preparation Tasks to Complete Before Starting Oracle Database Upgrades

High Availability Options for Oracle Database

Review the high availability options available to you for Oracle Database using Standard Edition High Availability, Oracle Restart, Oracle Real Application Clusters (Oracle RAC), and Oracle RAC One Node.

The following is an overview of the high availability options available to you for Oracle Database.

Standard Edition High Availability

  • Cluster-based active/passive Oracle Database failover solution
  • Designed for single instance Standard Edition Oracle Databases
  • Available with Oracle Database 19c release update (RU) 19.7 and later
  • Requires Oracle Grid Infrastructure 19c RU 19.7 and later, installed as a Standalone Cluster

Oracle Restart

  • Oracle Database instance restart only feature and basis for Oracle Automatic Storage Management (Oracle ASM) for standalone server deployments
  • For single instance Oracle Databases
  • Requires Oracle Grid Infrastructure for a standalone server (no cluster)

Oracle Real Application Clusters (Oracle RAC) One Node

  • Provides a cluster-based active/passive Oracle Database failover and online database relocation solution
  • Available for Oracle RAC-enabled Oracle Databases
  • Only one instance of an Oracle RAC-enabled Oracle Database is running under normal operations
  • Enables relocation of the active instance to another server in the cluster in an online fashion. To relocate the active instance, you can temporarily start a second instance on the destination server, and relocate the workload
  • Supports Rolling Upgrades - patch set, database, and operating system
  • Supports Application Continuity
  • Requires Oracle Grid Infrastructure to be installed as a Standalone Cluster

Oracle Real Application Clusters (Oracle RAC)

  • Provides active / active Oracle Database high availability and scalability solution
  • Enables multiple servers to perform concurrent transactions on the same Oracle Database
  • Provides high availability: a failure of a database instance or server does not interrupt the database service as a whole, because other instances and their servers remain operational
  • Supports Rolling Upgrades - patch set, database, and operating system
  • Supports Application Continuity
  • Requires Oracle Grid Infrastructure to be installed as a Standalone Cluster

Parent topic: Database Preparation Tasks to Complete Before Starting Oracle Database Upgrades

Options for High Availability with Oracle Database Standard Edition

To enable high availability for Oracle Database Standard Edition in releases after Oracle Database 19c, learn how you can use Standard Edition High Availability.

Parent topic: Database Preparation Tasks to Complete Before Starting Oracle Database Upgrades

Preparing to Upgrade Standard Edition Oracle RAC or Oracle RAC One Node

To maintain high availability after migrating from Standard Edition Oracle Real Application Clusters (Oracle RAC), you can use Standard Edition High Availability.

Starting with the Oracle Database 19c release, Oracle Database Standard Edition 2 does not support Oracle RAC. To continue to meet high availability needs for Oracle Database Standard Edition, Oracle is introducing Standard Edition High Availability.

Parent topic: Options for High Availability with Oracle Database Standard Edition

Requirements for Using Standard Edition High Availability With Oracle Databases

To use Standard Edition High Availability, deploy Oracle Database Standard Edition 2 in accordance with these configuration requirements.

  • The database is created in a cluster running Oracle Grid Infrastructure for a Standalone Cluster, with its database files placed in Oracle Automatic Storage Management (Oracle ASM) or Oracle Automatic Storage Management Cluster File System (Oracle ACFS).
  • When using the Database Configuration Assistant, do not create a listener when creating an Oracle Database Standard Edition 2 database that you want to configure for Standard Edition High Availability.
  • Register the database with Single Client Access Name (SCAN) listeners as remote listeners, and node listeners as the local listener.
  • Create a database service. Use this service, instead of the default database service, when you connect applications or database clients to the database.
  • Ensure that the server parameter file (spfile) and password file are on Oracle ASM or Oracle ACFS. If the spfile and password file were placed on a local file system when the database was created or configured, then move these files to Oracle ASM or Oracle ACFS.

Refer to the database installation documentation for additional requirements that must be met.

Related Topics

Parent topic: Options for High Availability with Oracle Database Standard Edition

Non-CDB Upgrades and Oracle GoldenGate

If you are upgrading a Non-CDB Oracle Database where Oracle GoldenGate is deployed, then you must shut down Oracle GoldenGate, and reconfigure it after conversion and upgrade for the multitenant architecture.

If you are using Oracle GoldenGate with the non-CDB Oracle Database that you want to upgrade, then before you convert and upgrade the source non-CDB Oracle Database to the multitenant architecture, you must shut down and remove the Oracle GoldenGate processes, and then reconfigure them after conversion and upgrade for the multitenant architecture. The following is a high level overview of the processes required:

  1. Drop Oracle GoldenGate users on the source Oracle Database.
  2. Wait until the Oracle GoldenGate processes finish processing all current DML and DDL data in the Oracle GoldenGate trails, and processes are at End of File (EOF).
  3. Stop all Oracle GoldenGate processes on the source database.
  4. Complete the conversion and upgrade of the source non-CDB Oracle Database to the target Oracle Database on the target release CDB.
  5. Restart the database.
  6. If you are also upgrading the database from an earlier release to a later major release family (for example, from Oracle Database 12.1 to Oracle Database 19c, which is the terminal patch set of the Oracle Database 12.2 family), then you must install a new version of Oracle GoldenGate that is supported for Oracle Database 19c. If you are upgrading both Oracle Database and Oracle GoldenGate simultaneously, then you must upgrade the database first.

After the database conversion and upgrade is complete, you can create new credentials for the Oracle GoldenGate extract user. With the new credentials you can then create a new Extract process and Extract pump and distribution service for the upgraded Oracle Database PDB on the target CDB, and start up the newly created processes. For more information about completing those procedures after the upgrade, refer to the Oracle GoldenGate documentation.

Related Topics

Parent topic: Database Preparation Tasks to Complete Before Starting Oracle Database Upgrades