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).

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.

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.

1. Run the following SQL query:

   SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s
                      WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

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.

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.

1. Run the following statement:

   SQL> SELECT * FROM v$recover_file;

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.

1. Run the following statement:

   SQL> SELECT * FROM dba_2pc_pending;
   

2. 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;

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

1. 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%';
   

2. 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.

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

SQL> PURGE DBA_RECYCLEBIN

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.

1. Install the software for the new Oracle Database 12c release.

2. Set ORACLE_HOME to your old Oracle home.

3. Set ORACLE_SID to the SID of the database being upgraded.

4. Set PATH, LD_LIBRARY_PATH, and SHLIB_PATH to point to the upgraded Oracle Database Oracle home.

5. Change directory to the new Oracle Database release Oracle home.

6. 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

7. Enter the SYS password for the database that you want to upgrade.

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.

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.

$ dbupgrade -T

SQL> ALTER TABLESPACE ARGROTBLSPA6 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE ARGROTBLSPB6 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE ARGROTBLSPA6 READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE ARGROTBLSPB6 READ WRITE;

Tablespace altered.