Gagandeep virk: Complete Checklist for Upgrading to Oracle Database 12c Release 1 using DBUA

Database Upgrade Assistant (DBUA)

DBUA graphical user interface are performed from within the new Oracle home where the Oracle Database 12c software has been installed.
Only an Administrator or Installed owner should invoke DBUA for Windows systems.

It is the recommended method for performing a major release upgrade or patch release upgrade.

It shows the upgrade process for each component.

It performs all the necessary tasks to upgrade the database.

It also gives certain recommendations on certain areas belonging to the database.
The recommendations can then be acted on making the upgrade process user friendly and easy .

As with previous releases of DBUA, 12c DBUA restricts the carry over of hidden parameters since Oracle recommends not to have hidden parameters other than those suggested via support during the upgrade.

To view existing hidden parameters execute the following command while connected AS SYSDBA:

SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\'   

DBUA performs some of the checks before actually starting the database upgrade. Some of the checks can be done manually to reduce downtime for the upgrade.
DBUA has options to

            - Upgrade timezone

            - Gather dictionary statistics before upgrade

            - Make user tablespaces read only

            - Take RMAN backup before upgrade

            - Restore database backup to rollback upgrade

            - Option to execute Custom scripts before and after upgrade

            - Has facility to display Alert log and Activitity log to monitor upgrade activity

            - Option to upgrade existing listener to 12c home

Various steps performed by DBUA

Apart from the features available in previous DBUA releases, 12c DBUA includes these new actions:

New Pre-Upgrade Information Tool

Parallel Processing for Database Upgrad. By Default DBUA sets upgrade parallelism to the number of CPU or 2 if the number of CPU is less than 4. It can be adjusted

DBUA Can Be Restarted During the Upgrade Process

Enhanced Pre-Upgrade and Post-Upgrade Summary Report

Activity log and Alert log can be invoked from DBUA to monitor upgrade progress

Oracle Grid Infrastructure Upgrade Enhancements

Pluggable Databases

Oracle XML Database is Installed When You Upgrade

One or more listeners from the source Oracle home can be migrated to the new upgraded Oracle home

All the database files including data and tempfiles should have autoextensible on. it is mandatory for upgrade through DBUA

Recommendations for Source database

1) Ensure that all database components/objects provided by Oracle are VALID in the source database prior to starting the upgrade.

2) Ensure that you do not have duplicate objects in the SYS and SYSTEM schema. For 1 and 2 refer to:

Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

dbupgdiag.sql script is a set of sql statements intended to provide a user friendly output to diagnose the status of the database either before (or) after upgrade. The script will create a output file called db_upg_diag_<sid>_<timestamp>.log

3) Disable the custom triggers that would fire before/after DDL and enable them after the upgrade is complete.

4) Either take a Cold or Hot backup of your source database (advisable to have cold backup).

5) Check the database server upgrade/downgrade compatibility matrix before upgrading the database.

6) Set Archive Log ON during upgrade. Oracle recommends that you set Archive Log ON in order for DBUA to create and update the log file for the upgrade process.

7) For Oracle RAC, if you upgrade a cluster database using DBUA, then you must leave the CLUSTER_DATABASE initialization parameter set to TRUE.

8) Ensure to run the pre-upgrade utility prior to upgrading the database . The script is available in the 12c ORACLE_HOME/rdbms/admin named preupgrd.sql. Refer to 12c documentation for complete details.:

Oracle Database Upgrade Guide 12c Release 1 (12.1) E17642-10
2 Preparing to Upgrade Oracle Database
2.5 About the Pre-Upgrade Information Tool for Oracle Database

9) If mitigation patch is applied at source database, it would have disabled Java. Enable it before performing upgrade to avoid java related error

Database Upgrade failed with Errors “ORA-02290: check constraint (SYS.JAVA_DEV_DISABLED) violated” & “ORA-04045: SYS.DBMS_ISCHED” (Doc ID 1985725.1)

10) Materialized views in source database should be stopped before upgrade

How to Handle Materialized Views When You Upgrade or Clone a Database (Doc ID 1406586.1)

11) Disable scheduled custom jobs

12) Before starting the Database Upgrade Assistant it is required change the preference for 'concurrent statistics gathering' on the current release if the current setting is not set to 'FALSE'

First, while still on the 11.2. release, obtain the current setting:

SQL> SELECT dbms_stats.get_prefs('CONCURRENT') from dual;

When 'concurrent statistics gathering' is not not set to 'FALSE', change the value to 'FALSE before the upgrade.

BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','FALSE');
END;
/

In both 11.2 as in 12.1 concurrency is disabled by default for both manual and automatic statistics gathering. If the database requires changing this value back to the original setting, do this after the upgrade.

Please see Note 2037154.1 DBMS_STATS.GATHER_DICTIONARY_STATS Fails with "ORA-06502: PL/SQL: numeric or value error: character string buffer too small"

Requirements and Recommendations for Target database

1) Download and Install Oracle 12c Release 1 in a new Oracle_Home and make sure there are no binary relinking errors

2) Download and Install the latest available Patchset (If available) or the latest available Critical Patch Update (PSU/SPU) (If available) from My Oracle Support (MOS).

Compatibility Matrix

The matrix below represents the direct upgrade possibilities:

Source Database	Target Database
10.2.0.5	12.1.x
11.1.0.7	12.1.x
11.2.0.2 and Higher	12.1.x

The following database releases require an Indirect upgrade path

Source Database	Upgrade Path	Target Database
7.3.3 (lower)	7.3.4 --> 9.2.0.8 --> 10.2.0.5	12.1.x
8.0.5 ( or lower )	8.0.6 --> 9.2.0.8 --> 10.2.0.5	12.1.x
8.1.7 ( or lower )	8.1.7.4 --> 10.2.0.5	12.1.x
9.0.1.3 ( or lower )	9.0.1.4 --> 10.2.0.5	12.1.x
9.2.0.7 ( or lower )	10.2.0.5	12.1.x
10.2.0.4 ( or lower )	10.2.0.5	12.1.x
11.1.0.6	11.1.0.7	12.1.x
11.2.0.1	11.2.0.2	12.1.x

If you are upgrading from 10.2.0.5 then the COMPATIBLE initialization parameter will be changed to 11.0.0 by DBUA as part of upgrade. Since compatible parameter value is changed, we cannot downgrade the database.

Example:

If source database is 8.1.7.0.0, the upgrade path to be followed is as below:
8.1.7.0.0 --> 8.1.7.4 --> 10.2.0.5--> 12.1.x

Run the Pre-Upgrade Information Tool for Collecting Pre-Upgrade Information

A log file, preupgrade.log, is created containing the output of the Pre-Upgrade Information Tool.
The preupgrade_fixups.sql script is created to list and describe issues that can be fixed using SQL*Plus in the source database.
It also attempts to resolve trivial issues when you execute it.
The postupgrade_fixups.sql script is created to address issues that can be fixed after the database has been upgraded.

Step 1:

Copy the Pre-Upgrade Information Tool (preupgrd.sql and utluppkg.sql) from the Oracle Database 12c Release 1 (12.1) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle Home, such as the temporary directory on your system. In case 12.1 oracle home is not yet installed, then you can get preupgrade scripts from Note 884522.1 How to Download and Run Oracle's Database Pre-Upgrade Utility

$ORACLE_HOME/rdbms/admin/preupgrd.sql

Step 2:

Change to the directory where preupgrd.sql had been copied in the previous step.

Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges. Then run and spool the preupgrd.sql file. Please note that the database should be started using the Source Oracle Home.

$ sqlplus '/ as sysdba'
SQL> spool upgrade_info.log
SQL> @preupgrd.sql
SQL> spool off

Check the spool file and examine the output of the Upgrade Information Tool.

The sections which follow describe the output of the Upgrade Information Tool.

Check for INVALID database components and objects in the Source database

Ensure that there are NO INVALID database components/objects in the source database prior to starting the upgrade

You can execute the following query to check the invalid database components/objects in the source database:

set pagesize500
set linesize 100
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;

select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status='INVALID' order by owner,object_type;

select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;

If you find invalid objects and/or database components then try to VALIDATE the invalid objects and/or database components by executing the following steps:
Run $ORACLE_HOME/rdbms/admin/utlrp.sql to validate the invalid objects in the database. You can execute the utlrp.sql scripts multiple times to validate the invalid objects.

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql

Check for TIMESTAMP WITH TIMEZONE Datatype

The time zone files that are supplied with Oracle Database 12c release 1 (12.1) have been updated to version 18
to reflect changes in transition rules for some time-zone regions.
The changes might affect existing data of the TIMESTAMP WITH TIME ZONE data type.

Case 1 Timezone version of source database is lower or equal than 18
If the source database is using a timezone file lower than version 18 then there is no DST patch to apply in source or target 12cR1 home.

In this case the Output of pre-upgrade utility will showing a statement like:
WARNING: -->Database is using a timezone file older than version 18 ...

It is suggested to update to the newest DST version included in Oracle 12cR1, which is DSTv18.
The DBUA has option to upgrade the RDBMS DST version during the upgrade however on some database using the scripts found in

Note 1585343.1 : Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database

will take less time to do the DST update than using the DBUA option to do the DST upgrade.

For a detailed description of the time zone upgrade, please refer to the following:

Note 1522719.1 : Actions For DST Updates When Upgrading To 12.1.0.1 Base Release

Case 2 Timezone version of source database i shigher than 18

If the source database uses a Timezone version higher than 18 then BEFORE upgrading you MUST patch the 12cR1 $ORACLE_HOME
with a timezone data file of the SAME version as the one used in the source release database.
In this case the Output of pre-upgrade utility will showing a statement like:
WARNING: --> Database is using a timezone file greater than ....
Note: If the source database is using a timezone file greater than version 18 ,
then the upgrade to 12cR1 will fail if the required DST patch is NOT applied on the 12cR1 home before doing the upgrade

For a detailed description of the time zone upgrade, please refer to the following:

Note 1522719.1 : Actions For DST Updates When Upgrading To 12.1.0.1 Base Release

Optimizer Statistics

When upgrading to Oracle Database 12c release 1 (12.1), optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade.

To determine the schema's which lack statistics, download and run the script from below MOS article:

Note 560336.1 Script to Check Schemas with Stale Statistics

To decrease the amount of downtime incurred when collecting statistics, you can collect statistics prior to performing the actual database upgrade. As of Oracle Database 10g Release 1 (10.1), Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, you can enter the following:

$ sqlplus "/as sysdba"

sql> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS

Disable Oracle Database Vault

When upgrading from Oracle Database Release 12.1, if you have enabled Oracle Database Vault option in your current Oracle Home, then you must disable Oracle Database Vault in the target Oracle home where the new release 12.1 software is installed before upgrading the database, and enable it again when the upgrade is finished. If Database Vault is enabled, then DBUA will return an error asking you to disable Database Vault prior to upgrade

You must do this before upgrading the database. Enable Oracle Database Vault again once the upgrade is complete.

Reference:

Note 453903.1 Enabling and Disabling Oracle Database Vault in UNIX

Note 453902.1 How To Enable And/Or Disable Oracle Database Vault

Audit records

From 10gr2 DBUA/catupgrd script can spend an infinite time to process the records in FGA_LOG$ and/or AUD$ if there are too many records

See:

Note 1062993.1 11.2.0.1 Catupgrd.sql Hangs While Running Procedure POPULATE_DBID_AUDIT

For 10.2 and later source versions there is now a pre-process script available:

Note 1329590.1 How to Pre-Process SYS.AUD$ Records Pre-Upgrade From 10.1 or later to 11gR1 or later.

If do not want to keep the records collected before 12cR1 then you can just do in source environment (READ PREVIOUS NOTES before proceeding):

SQL> truncate table sys.aud$;

SQL> truncate table sys.fga_log$;

Enterprise Manager Database Control

Starting with Oracle Database 12c, Oracle Enterprise Manager Database Control is desupported and is no longer available. Oracle introduces Oracle Enterprise Manager Database Express (Oracle EM Express) as a replacement. Oracle EM Express is installed when you upgrade to Oracle Database 12c. Oracle Database 12c also introduces Oracle Enterprise Manager Cloud Control (Cloud Control). Cloud Control provides you with complete monitoring across the Oracle technology stack and non-Oracle components. You must install Cloud Control separately after you upgrade to Oracle Database 12c.

EM is no longer supported in 12.1 and will be removed post upgrade while executing catuppst.sql. To save time, this action may be completed BEFORE the upgrade by invoking $ORACLE_HOME/rdbms/admin/emremove.sql.

Copy 12c $ORACLE_HOME/rdbms/admin/emremove.sql to different location and change the location to copied folder

Connect to source database as sysuser

sql> connect / as sysdba

sql> spool emremoval.log

sql>@emremove.sql

sql> spool off

Oracle Label Security

If you are upgrading from a database earlier than Oracle Database release 12.1 that uses Oracle Label Security (OLS) and Database Vault, then you must first run the OLS preprocess script, olspreupgrade.sql, to process the aud$ table contents. The OLS upgrade moves the aud$ table from the SYSTEM schema to the SYS schema. The olspreupgrade.sql script is a preprocessing script required for this move

Running olspreupgrade.sql on Oracle Database Release 11.1.0.7

If Oracle Label Security is installed in the earlier release that you are upgrading, then you must run the OLS preprocess olspreupgrade.sql script. If Database Vault is not installed with your release 11.1.0.7 database, then you can skip steps 2, 3, 6, and 7 in this section.

To run the OLS preprocess script on a release 11.1.0.7 database before upgrading:

1. Copy the ORACLE_HOME/rdbms/admin/olspreupgrade.sql script from the newly installed Oracle home to the Oracle home of the database to be upgraded.

2. Start SQL*Plus and connect to the database to be upgraded as DVOWNER.

3. Execute the following statement:

SQL> EXEC dbms_macadm.add_auth_to_realm('Database Vault','SYS',NULL, 0);

4. At the system prompt, enter:

CONNECT SYS AS SYSDBA

5.Run the OLS preprocess script:

ORACLE_HOME/rdbms/admin/olspreupgrade.sql

You may continue running your applications on the database while the OLS preprocess script is running.

Beta Draft Preparing to Upgrade Oracle Database 2-19

6. After the olspreupgrade.sql has been successfully run, start SQL*Plus and connect to the database as DVOWNER.

7. Execute the following statement:

SQL> EXEC dbms_macadm.delete_auth_from_realm('Database Vault','SYS');

Running olspreupgrade.sql on Oracle Database Release 10.2.0.5 or 11.2

If Oracle Label Security is installed in the earlier release that you are upgrading, then you must run the OLS preprocess olspreupgrade.sql script. If Database Vault is not installed with your release 10.2.0.5 or 11.2 database, then you can skip steps 2, 3, 6, and 7 in this section.

To run the OLS preprocess script on a release 10.2.0.5 or 11.2 database before upgrading:

SQL> GRANT DV_PATCH_ADMIN to SYS;

4. At the system prompt, enter:

CONNECT SYS AS SYSDBA

5. Run the OLS preprocess script:

ORACLE_HOME/rdbms/admin/olspreupgrade.sql

You may continue running your applications on the database while the OLS preprocess script is running.

6. After the olspreupgrade.sql has been successfully run, start SQL*Plus and connect to the database as DVOWNER.

7. Execute the following statement:

SQL> REVOKE DV_PATCH_ADMIN from SYS

Oracle Warehouse Builder

OWB is not installed as part of the software for Oracle Database 12c, and OWB components that may exist in earlier releases are not upgraded as part of the Oracle Database upgrade process. However, you can use OWB release 11.2.0.3 with Oracle Database 12c. Note that OWB releases earlier than release 11.2.0.3 do not work with Oracle Database 12c

Check XDB ACLs has start_date and end_date ACE attributes

Before upgrading the database to 12c, please run the below query as SYS:

SQL> select aclid, start_date, end_date from xds_ace where start_date is not null;  

If the query returns any row, then please follow Note 1958876.1 Upgrade to 12.1 fails with ORA-01830 date format picture ends before converting entire input string ORA-06512: at "SYS.XS_OBJECT_MIGRATION" to avoid failure in XDB's upgrade.

Invoke DBUA

Once all Prerequisite checks are completed and successful.

cd $ORACLE_HOME/bin

./dbua

Ensure environment variables are pointing to 12c oracle home

DBUA logs by default stored at $ORACLE_BASE/cfgtoollogs/$ORACLE_SID, It can be changed by specifying -logdir with DBUA in command line switch

Example:
dbua -logdir /tmp

Post Upgrade steps

All the post upgrade steps are performed by DBUA. In case DBUA displays the final component status in the Upgrade Results page.
Please check the final component status on the Upgrade Result page. Invalid components are marked with an X and the option to recompile is provided.

Run utlrp.sql to recompile the invalid objects as follows:

sql> connect / as sysdba

sql> @?/rdbms/admin/utlrp.sql

Run the datapatch manually after upgrading the database to 12.1

Whenever a SPU/(DB or OJVM)PSU/BP is installed into the 12.1 home prior to the upgrade then please execute the datapatch manually after the upgrade

DBUA misses the post-upgrade datapatch execution in Oracle 12.1 The solution is to apply the SQL changes manually after DBUA has completed the database upgrade to Oracle Database 12c:

cd $ORACLE_HOME/OPatch./datapatch -verbose

this is only necessary when you used the DBUA for a database upgrade. This step is not required for the command line upgrade. This will be fixed in an upcoming release of the DBUA. In case PSU 12.1.0.2.160419 is applied on oracle home then this step is not necessary.

Upgrade the Time Zone File Version after Upgrading Oracle Database in case Timezone upgrade is not chosen in DBUA

If the Pre-Upgrade Information Tool instructed you to upgrade the time zone files after completing the database upgrade,
then use the DBMS_DST PL/SQL package to update the RDBMS DST (timezone) version.

Note 1585343.1 : Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database .

ATTENTION : Once you have updated the DST version you may still see the following warning returned by script postupgrade_fixups.sql :

******************************************************************************************
Check Tag:     OLD_TIME_ZONES_EXIST
Check Summary: Check for use of older timezone data file
Fix Summary:   Update the timezone using the DBMS_DST package after upgrade is complete.
*******************************************************************************************

Just ignore this warning, it is due to published Bug 17303129 : UPGRADE DATABASE FROM 11.1.0.7 TO 12.1.0.1, "OLDER TIMEZONE IN USE" OCCURRED

When such warning is returned then execute this query :

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

if not DStv18 then Oracle suggests to upgrade DST, if DSTv18 then it is Bug 17303129 and it can be safely ignored .

Known Issue

1) Once upgrade is completed, DBUA progress will stop at 100%, click 'Cancel' button to complete the upgrade. It is a known issue and it will be fixed in next patch set.

2) Upgrade to 12.1 fails with ORA-01830 date format picture ends before converting entire input string ORA-06512: at "SYS.XS_OBJECT_MIGRATION" (Doc ID 1958876.1)

3) Once database is upgraded to 12c, it will be a non-multitenant database. In case you want to convert this upgraded database to Pluggable database(PDB) and plugin into existing 12c Container database(CDB), check for characterset of CDB and PDB

In Oracle Database 12c with multitenant architecture, all pluggable databases (PDBs) in a container database (CDB) must have the same Database character set (NLS_CHARACTERSET) or the NLS_CHARACTERSET need to be a (Plug-in compatible) binary subset of the CDB NLS_CHARACTERSET the same National character set (NLS_NCHAR_CHARACTERSET) as the CDB's root container

If you have PDBs with Unicode characterset, its recommended to create the CDB with characterset as AL32UTF8. Please note we cannot migrate the CDB's characterset using DMU.

Refer:

12c Multitenant Container Databases (CDB) and Pluggable Databases (PDB) Character set restrictions / ORA-65116/65119: incompatible database/national character set ( Character set mismatch: PDB character set CDB character set ) (Doc ID 1968706.1)

Changing Or Choosing the Database Character Set ( NLS_CHARACTERSET ) (Doc ID 225912.1) [Section E]

Note:

1) DBUA expects that both the source (pre-12c) Oracle home and the destination (new 12c) Oracle home are owned by the same user

2) Starting with Oracle Database 12c, block file storage on raw devices is not supported. it should migrate any data files stored on raw devices to Oracle ASM, a cluster file system, or Network File System (NFS)

3) To enhance security, DBUA automatically locks new user accounts in the upgraded database

Gagandeep virk

Complete Checklist for Upgrading to Oracle Database 12c Release 1 using DBUA