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.
- Release Updates and Requirements for Upgrading Oracle Database
Before starting upgrades, update your new release Oracle home to the latest Release Update (Update). - Understanding Password Case Sensitivity and Upgrades
By default, Oracle Database 12c Release 2 (12.2) and later releases use Exclusive Mode authentication protocols. Exclusive Modes do not support case-insensitive password-based authentication. - 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. - Running Upgrades with Read-Only Tablespaces
To take user schema-based tablespaces offline during upgrade, use AutoUpgrade with thecatctl_options
parameter-T
option.
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 note 2118136.2 contains a download assistant to help you select the updates 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
Understanding Password Case Sensitivity and Upgrades
By default, Oracle Database 12c Release 2 (12.2) and later releases use Exclusive Mode authentication protocols. 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.
Note:
Starting with Oracle Database 21c, theSEC_CASE_SENSITIVE_LOGON
parameter is desupported. You must use a
case-sensitive password version. If a user with only a 10G
password
version is upgraded to Oracle Database 21c, then that user account is locked, until an
administrator resets the password.
In previous Oracle Database releases, you could 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
excluded 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.
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-insensitive10G
password version.
Update Accounts Using Case-Insensitive Versions
If you have user accounts that have only the case-insensitive 10G
password version, then 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.
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.
Note:
Starting with Oracle Database 21c, the
SEC_CASE_SENSITIVE_LOGON
parameter is desupported. You must use a
case-sensitive password version.
If you do not want user accounts authenticated with case-insensitive password versions to be locked out of the database after an upgrade, then before the upgrade, you must identify affected accounts, and ensure that they are using case-sensitive password versions.
Example 2-2 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 Database10G
, and the password forJONES
was reset in Oracle Database12C
when the setting for theSQLNET.ALLOWED_LOGON_VERSION_SERVER
parameter was set to8
. As a result, this password reset created all three versions.11G
and12C
use case-sensitive passwords. -
ADAMS
andCLARK
were originally created with the10G
version, and then11G
, after they were imported from an earlier release. These account passwords were then reset in11G
, with the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to TRUE. -
The password for
BLAKE
was created with the10G
version, and the password has not been reset. As a result, user BLAKE continues to use the10G
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-3 Fixing Accounts with Case-Insensitive Passwords
Complete the following procedure:
-
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';
-
Configure the system so that it is not running in Exclusive Mode by editing the setting of the
SQLNET.ORA
parameterSQLNET.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.
-
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;
-
Ask the users for whom you have expired the passwords to log in.
-
When these users log in, they are prompted to reset their passwords. The system internally generates the missing
11G
and12C
password versions for their account, in addition to the10G
password version. The10G
password version is still present, because the system is running in the permissive mode. -
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 theCPUOct2012
patch to acquire theO5L_NP
capability.The
O5L_NP
capability flag is documented in Oracle Database Net Services Reference, in the section on the parameterSQLNET.ALLOWED_LOGON_VERSION_SERVER
. -
After all clients have the
O5L_NP
capability, raise the server security back to Exclusive Mode by using the following procedure:-
Remove the
SEC_CASE_SENSITIVE_LOGON
setting from the instance initialization file, or set theSEC_CASE_SENSITIVE_LOGON
instance initialization parameter toTRUE
. For example:SEC_CASE_SENSITIVE_LOGON = TRUE
-
Remove the
SQLNET.ALLOWED_LOGON_VERSION_SERVER
parameter from the serverSQLNET.ORA
file, or set it back to Exclusive Mode by changing the value ofSQLNET.ALLOWED_LOGON_VERSION_SERVER
in the serverSQLNET.ORA
file back to12
. For example:SQLNET.ALLOWED_LOGON_VERSION_SERVER = 12
-
-
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';
-
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, whereusername
is a name on the list returned by the query:ALTER USER username PASSWORD EXPIRE;
-
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
and12C
password versions for their account. Because the system is running in Exclusive Mode, the10G
password version is no longer generated. -
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-4 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
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.
Running Upgrades with Read-Only Tablespaces
To take user schema-based tablespaces offline during upgrade, use AutoUpgrade
with the catctl_options
parameter -T
option.
For all Oracle Database releases that AutoUpgrade can upgrade, Autoupgrade can read
file headers created in earlier releases. 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
.
Note:
If you are performing a non-CDB to PDB conversion, then using read-only tablespaces is not a valid fallback option. During a non-CDB to PDB conversion, tablespaces must be online during conversion, because each data file header requires changes during the upgrade.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
dbupgrade
log directory, and run commands in the log files
manually to bring up tablespaces. You can view tablespace commands in the following
log files:
-
Non-CDB Upgrade format:
catupgrdYYYYMMDDHHMMSC0.log
, where:YYYY
is the year,MM
is the month,DD
is the day,HH
is the hour,MM
is the minute in the hour, andSC
is the seconds. -
PDB databases format:
catupgrdYYYYMMDDHHSCpdbname0.log
, where:YYYY
is the year,MM
is the month,DD
is the day,HH
is the hour,MM
is the minute in the hour,SC
is the seconds, andpdbname
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