Database Preparation Tasks to Complete Before Starting Oracle Database Upgrades

Ensure you have completed these database preparation tasks before starting Oracle Database upgrades.

Patch Set Updates and Requirements for Upgrading Oracle Database
Update your new release Oracle Database to the latest Oracle bundle patch or patch set update (BP or PSU) before starting upgrades.
Gathering Optimizer Statistics to Decrease Oracle Database Downtime
Oracle strongly recommends that you use this procedure to gather statistics before performing Oracle Database upgrades.
Verifying Materialized View Refreshes are Complete Before Upgrade
Use this procedure to query the system to determine if there are any materialized view refreshes still in progress.
Ensuring That No Files Are in Backup Mode Before Upgrading
Use this procedure to query the system to obtain a list of any files in backup mode.
Ensuring That No Files Need Media Recovery Before Upgrading
Use this procedure to obtain a list of files that require media recovery.
Resolving Outstanding Distributed Transactions Before Upgrading
Use this procedure to resolve any outstanding distributed transactions before you start an upgrade.
Synchronizing the Standby Database with the Primary Database When Upgrading
If a standby database exists, then check to see if you need to synchronize it with the primary database before upgrading Oracle Database.
Purging the Database Recycle Bin Before Upgrading
Use the PURGE statement before the upgrade to remove items and their associated objects and to release their storage space.
Saving Oracle Enterprise Manager Database Control Configuration and Data
Use the emdwgrd utility before the upgrade to save DB Control files so that you can downgrade and restore Oracle Enterprise Manager Database Control (DB Control).
Manually Removing DB Control with emremove.sql
Use this SQL procedure to minimize downtime during the upgrade process.
Dropping JSON-Enabled Context Search Indexes
If you are upgrading from Oracle Database 12c release 1 (12.1) to 12c release 2 (12.2), then Oracle recommends that you drop the JSON-enabled context index before upgrading.
Copying Transparent Encryption Oracle Wallets
If you use Oracle wallet with Transparent Data Encryption (TDE), and you use Database Upgrade Assistant (DBUA) to upgrade the database, then copy the sqlnet.ora and wallet file to the new Oracle home.
Recommendations for Oracle Net Services When Upgrading Oracle Database
Review these procedures and parameter changes for Oracle Net Services before you upgrade.
Understanding Password Case Sensitivity and Upgrades
By default, Oracle Database 12c release 2 (12.2) is upgraded to an Exclusive Mode. 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 and Offline Tablespaces
Use the Parallel Upgrade Utility with the -T option available with Oracle Database 12c release 2 to take schema-based tablespaces offline during upgrade.

Parent topic: Preparing to Upgrade Oracle Database

Patch Set Updates and Requirements for Upgrading Oracle Database

Update your new release Oracle Database to the latest Oracle bundle patch or patch set update (BP or PSU) before starting upgrades.

The software for Oracle Database 12c contains a full release that includes all the latest patches and updates for Oracle Database at the time of the release.

Before you start an upgrade or downgrade process, Oracle strongly recommends that you update both your earlier release and your new release Oracle Database to the latest Oracle bundle patch or patch set update (BP or PSU).

My Oracle Support provides detailed notes about how you can obtain the latest patches, as well as tools for lifecycle management and automated patching. For example:

My Oracle Support note 854428.1 contains information about patch sets and updates.
My Oracle Support note 730365 contains an upgrade reference list for most available Oracle Database releases, including download information, patch numbers, and links to other notes.
My Oracle Support note 2180188.1 contains lists of one-off patches for upgrades, downgrades, and coexistence with previous releases.

Refer to

Gathering Optimizer Statistics to Decrease Oracle Database Downtime

Oracle strongly recommends that you use this procedure to gather statistics before performing Oracle Database upgrades.

Statistics gathering occurs for those tables that lack statistics, or that are significantly changed during the upgrade of Oracle Database.

If your database contains thousands of dictionary tables, then Oracle strongly recommends that you collect statistics the night before starting the upgrade.

To decrease the amount of downtime, use the procedure for your database configuration:

Non-CDB Oracle Database: Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, enter the following SQL statement:
```
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
```
CDB (multitenant architecture) Oracle Database: Oracle recommends that you use catcon to gather Data Dictionary statistics across the entire multitenant architecture.

To gather dictionary statistics for all PDBs in a container database, use the following syntax:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -b gatherstats -- --x"exec dbms_stats.gather_dictionary_stats"

To gather dictionary statistics on a particular PDB, use syntax similar to the following:
```
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -c
'SALES1' -b gatherstats -- --x"exec dbms_stats.gather_dictionary_stats"
```
In the preceding example, the -c SALES1 option specifies a PDB inclusion list for the command that you run, specifying the database named SALES1. The option -b gatherstatsspecifies the base name for the logs. The option --x specifies the SQL command that you want to execute. The SQL command itself is inside the quotation marks.

See Also:

Oracle Database PL/SQL Packages and Types Reference for the syntax and complete information for the GATHER_DICTIONARY_STATS procedure

Oracle Database Performance Tuning Guide for more information about gathering optimizer statistics.

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

Verifying Materialized View Refreshes are Complete Before Upgrade

Use this procedure to query the system to determine if there are any materialized view refreshes still in progress.

Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.

Run the following SQL query:

SQL> SELECT s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#,
bitand(s.mflags, 8) from obj$ o, sum$ s WHERE o.obj# = s.obj# AND o.type# =
42 AND bitand(s.mflags, 8) = 8;

See Also:

Oracle Database PL/SQL Packages and Types Reference for information on using the DBMS_MVIEW package to manage materialized views
Oracle Database SQL Language Reference for information about the DROP MATERIALIZED VIEW statement to permanently remove an existing materialized view from the database

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

Ensuring That No Files Are in Backup Mode Before Upgrading

Use this procedure to query the system to obtain a list of any files in backup mode.

You cannot have files in backup mode when upgrading Oracle Database. Run this v$backup procedure to check for the status of the backup:

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

If this SQL statement indicates that files are still in backup, then either wait for the backup to complete, or abort any backups that are not needed before you attempt to upgrade.

See Also:

Oracle Database Backup and Recovery User's Guide for information about backing up and archiving data

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

Ensuring That No Files Need Media Recovery Before Upgrading

Use this procedure to obtain a list of files that require media recovery.

Before upgrading Oracle Database, you must ensure that there are no files requiring media recovery. You can query the system to get a list of files, and then recover them as appropriate.

Run the following statement:
```
SQL> SELECT * FROM v$recover_file;
```

See Also:

Oracle Database Backup and Recovery User's Guide for information about performing block media recovery

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

Resolving Outstanding Distributed Transactions Before Upgrading

Use this procedure to resolve any outstanding distributed transactions before you start an upgrade.

You must resolve outstanding distributed transactions before upgrading Oracle Database. You can do this by first querying to see any pending transactions, and then committing the transactions. You must wait until all pending distributed transactions have been committed.

Run the following statement:
```
SQL> SELECT * FROM dba_2pc_pending;
```

If the query in the previous step returns any rows, then run the following statements:

SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;

Tip:

Oracle Database Administrator's Guide for information about managing distributed transactions

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

Synchronizing the Standby Database with the Primary Database When Upgrading

If a standby database exists, then check to see if you need to synchronize it with the primary database before upgrading Oracle Database.

Run the following query:

SQL> SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)
FROM v$parameter
WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';

If the query in the previous step returns a row, then synchronize the standby database with the primary database.
- Make sure all the logs are transported to the standby server after a final log switch in the primary.
- Start the recovery of the standby database with the NODELAY option.

See Also:

Oracle Database Backup and Recovery User’s Guide for information on synchronizing a physical standby database with the primary database

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

Purging the Database Recycle Bin Before Upgrading

Use the PURGE statement before the upgrade to remove items and their associated objects and to release their storage space.

All the user recycle bins in the database must be empty before you begin the upgrade process for Oracle Database. If you have the SYSDBA privilege, then you can purge all the recycle bins in the entire database by specifying DBA_RECYCLEBIN, instead of RECYCLEBIN. Starting with Oracle Database 12c, you can use the new PURGE DBA_RECYCLEBIN system privilege to perform the same action without the requirement of granting or being granted the SYSDBA privilege.

The PURGE DBA_RECYCLEBIN statement is a special PURGE command that removes all the objects from the systemwide recycle bin and is equivalent to purging the recycle bin of every user. In earlier releases, this statement required the SYSDBA administrative privilege, which is highly undesirable in terms of separation of duty and least privilege. To provide compliance with separation of duty, Oracle Database 12c introduces a new system privilege, PURGE DBA_RECYCLEBIN, which enables you to run PURGE DBA_RECYCLEBIN without having the SYSDBA administrative privilege.

To empty the database recycle bin, run the following command:

SQL> PURGE DBA_RECYCLEBIN

Caution:

The database recycle bin must be empty during the upgrade process to avoid possible ORA-00600 errors, and to minimize the upgrade time.

See Also:

Oracle Database Administrator's Guide for information about purging objects in the recycle bin
Oracle Database SQL Language Reference for complete information about the PURGE statement
Oracle Database Security Guide for information about separation of duty for database administration

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

Saving Oracle Enterprise Manager Database Control Configuration and Data

Use the emdwgrd utility before the upgrade to save DB Control files so that you can downgrade and restore Oracle Enterprise Manager Database Control (DB Control).

After you upgrade your database, you can only restore Oracle Enterprise Manager DB Control if you saved your existing DB Control configuration files and data before the upgrade. Save these files if you need to preserve the option to downgrade and restore DB Control.

Starting with Oracle Database 12c release 1 (12.1), DB Control is removed as part of the upgrade process. Oracle provides the emdwgrd utility to use before upgrading your database to keep a copy of your DB Control configuration and data. To downgrade and restore the DB Control configuration from your earlier release Oracle Database, you must have a copy of your DB Control configuration and data.

The emdwgrd utility is located in the ORACLE_HOME/bin directory in the new Oracle Database 12c release. The emdwgrd utility consists of emdwgrd and emdwgrd.pl for Linux and UNIX, and emdwgrd.bat and emdwgrd.pl for Windows. Before running the utility, you must install the software for Oracle Database 12c, and then run the script from the new Oracle home. The emdwgrd utility requires that you set ORACLE_HOME to the Oracle home of the release that you are upgrading.

Install the software for the new Oracle Database 12c release.
Set ORACLE_HOME to your old Oracle home.
Set ORACLE_SID to the SID of the database being upgraded.
Set PATH, LD_LIBRARY_PATH, and SHLIB_PATH to point to the upgraded Oracle Database Oracle home.
Change directory to the new Oracle Database release Oracle home.
Run emdwgrd using the procedure for your database deployment, using the following guidelines:
- If the Oracle home is on a shared device, then add the -shared option to the emdwgrd command line.
- In these examples, old_SID is the system identifier (SID) of the database that you are upgrading, and save_directory is the path to the storage place that you select to save your existing DB Control files and data:
- Single-instance databases:
  
  emdwgrd -save -sid old_SID -path save_directory
  
  On Linux and UNIX systems, the script is in emdwgrd.sh.
  
  On Windows, the script is in emdwgrd.bat.
- Oracle Real Application Clusters (Oracle RAC) databases:
  1. You must have remote copy enabled across all cluster member nodes. To indicate which remote copy is configured, define an environment variable. For example:
    
    setenv EM_REMCP /usr/bin/scp
  2. Run emdwgrd using the following syntax:
    
    emdwgrd -save -cluster -sid old_SID -path save_directory
Enter the SYS password for the database that you want to upgrade.

Note:

The DBUA backup and restore process also enables you to revert to your previous Oracle Enterprise Manager Database Control environment after upgrading your database. However, all user data that is accumulated between the time of upgrade and restore operations is lost. Saving your database control files and data enables you to downgrade both your database and DB Control. All user data is retained even though all DB Control data that is accumulated between the time of upgrade and downgrade is lost.

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

Manually Removing DB Control with emremove.sql

Use this SQL procedure to minimize downtime during the upgrade process.

You can choose to run emremove.sql script as part of your pre-upgrade preparation.

The emremove.sql script drops the Oracle Enterprise Manager-related schemas and objects. This script can take a few minutes to complete, because it completes the process in six stages. The script can take longer to complete if you have SYSMAN and related sessions active from SQL*Plus, or Oracle Enterprise Manager, or other clients.

Caution:

To restore DB Control after a downgrade, you must first back up your DB Control configuration and data. Complete a backup before you start this procedure.

If the DB Control application is configured, then shut it down. Use the following command:
```
$ emctl stop dbconsole
```
Start SQL*Plus and connect to the database using the SYS account AS SYSDBA.
You can configure emremove.sql in silent and verbose modes. If you want to monitor the script while it is running, then set the following variables:
```
SET ECHO ON;
SET SERVEROUTPUT ON;
```
Run emremove.sql. The script is located in the new Oracle Database 12c home, in the path ORACLE_HOME/rdbms/admin/.
For example:
```
SQL> @emremove.sql
```
After emremove.sql completes, you must manually remove ORACLE_HOME/HOSTNAME_SID and ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID directories from your file system.

Note:

If you previously upgraded DB Control from release 10.2.0.3 to 10.2.0.4, then you must also remove the following directories from the file system:

ORACLE_HOME/HOSTNAME_SID.upgrade

ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID.upgrade
On Windows platforms, also delete the DB Console service, which generally is named OracleDBConsoleSID.

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

Dropping JSON-Enabled Context Search Indexes

If you are upgrading from Oracle Database 12c release 1 (12.1) to 12c release 2 (12.2), then Oracle recommends that you drop the JSON-enabled context index before upgrading.

Oracle recommends that you drop your existing JSON-enabled index.

Related Topics

Oracle Database JSON Developer’s Guide

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), and you use Database Upgrade Assistant (DBUA) to upgrade the database, then copy the sqlnet.ora and wallet file to the new Oracle home.

You must copy the sqlnet.ora and the wallet file manually before starting the upgrade.

Log in as an authorized user.
Manually copy the sqlnet.ora file, and the wallet file, ewallet.p12, to the new release Oracle home.

Open the Oracle wallet in mount.

For example:

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

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

Recommendations for Oracle Net Services When Upgrading Oracle Database

Review these procedures and parameter changes for Oracle Net Services before you upgrade.

In Oracle Database 12c, new, 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 new parameters are not supported in earlier releases, and are only available in Oracle Database 12c.

If the Oracle Database that you are upgrading does not have a listener configured, then before you run DBUA, 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. You must create a new version of the listener for releases of Oracle Database earlier than release 11.2. The new listener is backward-compatible with earlier Oracle databases.

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.

Note:

If there are listeners configured on the source home, and the Oracle Database in the source home is older than the target Oracle Database home, then DBUA by default selects the listeners in the source home for migration during the upgrade process .

See Also:

Oracle Database Net Services Reference for information about the new sqlnet.ora compression parameters

Oracle Database Net Services Administrator's Guide for complete information about using Oracle Net Configuration Assistant

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) is 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, the authentication protocol could be configured to allow 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, because, 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, accounts that have only the case-insensitive 10G password version become inaccessible. This occurs because the server runs in an Exclusive Mode by default. When Oracle Database is configured in Exclusive Mode, it cannot make use of 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 is the default. However, you can temporarily disable case-sensitive authentication during the upgrade to Oracle Database 12c release 2 (12.2). 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 perform the following checks to determine if you are affected by this change to the default password-based authentication protocol configuration:

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 do this by expiring user passwords using the 10G password version, and requesting these users to log in to their account. When they attempt to log in, the server automatically updates these user's 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

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), the 10G password version is neither generated nor 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

SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

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 background for each user account password verification versions 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 12c release 2 (12.2) without taking any further action, then this account becomes inaccessible. You must 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:

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 parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER to a level appropriate for affected accounts. For example:
```
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
```
After you do this, 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 and 12C password versions for their account, in addition to the 10G password version. The 10G password version continues to be 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 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.
After all clients have the O5L_NP capability, raise the server’s 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's SQLNET.ORA file, or set the value of SQLNET.ALLOWED_LOGON_VERSION_SERVER in the server's SQLNET.ORA file back to 12, to set it to an Exclusive Mode. 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 of 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;
Request the users whose accounts you expired to log into 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.
Check that the system is running in a secure mode by re-running the query from step 1. Ensure that no users are found. When no users are found by the query, this 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 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 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.

Running Upgrades with Read-Only and Offline Tablespaces

Use the Parallel Upgrade Utility with the -T option available with Oracle Database 12c release 2 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 offline data files are updated later when they are brought online. The file headers of READ ONLY tablespaces are updated when they are changed to READ WRITE. Setting tablespaces offline ensures that your tablespaces are not altered during the upgrade. After the upgrade is complete, any user tablespace that was taken offline during the upgrade is brought back online.

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.

Changes for Upgrades of Tablespaces in Oracle Database 12c Release 2 (12.2)

Starting with Oracle Database 12c release 2, you can run the Parallel Upgrade Utility with the -T option to have schema-based tablespaces (user tablespaces) taken offline during the upgrade. Taking these tablespaces offline can reduce the necessity of backing up before upgrades. The Parallel Upgrade Utility (catctl.pl) analyzes tablespaces, and automatically selects the right set of tablespaces to set to read only. The utility does not set to READ ONLY any tablespaces that contain Oracle-Maintained objects.

This change enables you to set more tablespaces automatically to READ ONLY mode as part of the upgrade. In previous releases, you could set tablespaces to READ ONLY or OFFLINE mode manually. However, in some cases you had to revert to READ WRITE to prevent upgrade failures.

Also in Oracle Database 12c release 2 (12.2), the behavior of the ALTER TYPE statement is changed. During upgrades, if a dependent table is in an accessible tablespace, then it is automatically upgraded to the new version of the type. If the dependent table is in a READ ONLY tablespace, then it is not automatically upgraded. In that case, run the utluptabdata.sql script after the upgrade is completed to upgrade those tables that were in READ ONLY tablespaces during the upgrade.

To take schema-based tablespaces offline, run the Parallel Upgrade Utility (catctl.pl) from the command line, using the -T option. You can run catctl.pl by using the dbupgrade script.

For example, on Linux and UNIX platforms:

$ dbupgrade -T

Run the utluptabdata.sql script after the upgrade completes to upgrade those tables set to READ ONLY tablespaces during the upgrade.

Viewing Tablespace Commands In Upgrade Log Files

If a catastrophic upgrade failure occurs, then you can 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: catupdrdpdbname0.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