Oracle 18c – Complete Checklist for upgrading Oracle 12.x Container Database (CDB) to Oracle Database 18c (18.x) using DBUA
The purpose of this article is to help DBA’s / Support Teams to perform the upgrade of a database using DBUA to 18.x.
Database Upgrade Assistant (DBUA)
Database Upgrade Assistant (DBUA) provides a graphical user interface to guide you through the upgrade of Oracle Database.
DBUA works for CDB and non-CDB database systems. It is the recommended method for performing a major release upgrade or patchset release upgrade.
You can use DBUA to upgrade multitenant architecture container databases (CDB), pluggable databases (PDBs), and non-CDB databases.
The procedures are the same, but the choices you must make and the behavior of DBUA are different, depending on the type of upgrade.
DBUA automates the upgrade process by performing all of the tasks.
DBUA provides support for Oracle Real Application Clusters (Oracle RAC) databases. In an Oracle RAC environment,
DBUA upgrades all the database and configuration files on all nodes in the cluster.
DBUA, graphical user interface must be invoked within the new Oracle home where the Oracle Database 18c software has been installed.
For windows, Only an Administrator or Installed owner should invoke DBUA for Windows systems.
DBUA runs the Pre-Upgrade Information Tool as part of the prerequisite checks it performs before starting the upgrade.
However, to reduce downtime, Oracle recommends that you run the Pre-Upgrade Information Tool as part of your upgrade planning, so that you can analyze the database,
and take proactive steps before your planned upgrade date.
Once, you address / fix the pre-upgrade recommendation / warnings /errors and continue with the upgrade, DBUA shows the progress of the upgrade for each component of source database.
As with previous releases of DBUA, 18c 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:
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 provides below options:
– Upgrade timezone. The default timezone vetrsion in 18.x is 26.
– Gather dictionary statistics before upgrade.
– Make user tablespaces read only.
– Take RMAN backup before upgrade.
– Create Restore Point for Database Flashback
– Restore database backup to rollback upgrade
– Option to execute Custom scripts before and after upgrade
– show the location of DBUA logs and Alert log files.
– Option to upgrade existing listener to 18.x home or create a new listener in 18.x target home.
About Read-Only Oracle Homes
In a read-only Oracle home, all the configuration data and log files reside outside of the read-only Oracle home. This feature allows you to use the read-only Oracle home as a software image that can be distributed across multiple servers.
Upgrade Path / Compatibility Matrix for 18.x Oracle Database
DBUA can upgrade only supported versions of direct upgrade.
Direct Upgrade to 18.x:
|Source Database||Target Database|
|12.1.0.x (184.108.40.206 – 220.127.116.11)||18.x|
Requirements and Recommendation for source database
– Ensure that all database components/objects provided by Oracle are VALID in the source database prior to starting the upgrade.
– Before you start an upgrade or downgrade process, Oracle strongly recommends that you update both your earlier release and your new release (18.x) Oracle Database to the latest Oracle bundle patch or patch set update (BP or PSU).
– Ensure that you do not have duplicate objects in the SYS and SYSTEM schema. For 1 and 2 refer to:
Doc Id 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
– IF APEX is installed then it is recommended to upgrade APEX in the source DB first before upgrading DB
– Timezone should less than or equal to target database timezone version.
– Make sure to have a valid backup of source database prior to upgrade.
– Disable any custom triggers that would get executed before / after DDL statements. Re-enable after the upgrade.
– Check the database server upgrade/downgrade compatibility matrix before upgrading the database.
– 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.
– For Oracle RAC, if you upgrade a cluster database using DBUA, then you must leave the CLUSTER_DATABASE initialization parameter set to TRUE.
– Ensure to run the pre-upgrade utility prior to upgrading the database.
– Examine and follow the recommendation given in the preupgrade log file.
– Materialized views in source database should be stopped before upgrade
Doc ID 1406586.1 – How to Handle Materialized Views When You Upgrade or Clone a Database
– Disable scheduled database custom jobs / cron jobs.
– Make sure you have COMPATIBLE parameter is set to 12.1.0 or greater
Requirements and Recommendations for Target database
– Verify whether your operating system / platform is certified for 18.x release.
– Download and Install Oracle 18c (18.x) in a new Oracle_Home and make sure there are no binary relinking errors.
– Download and Install the latest available RU or RUR from My Oracle Support (MOS).
– Make sure to set the ORACLE_HOME, PATH, LD_LIBRARY_PATH, LIBPATH etc. to 18.x target home.
– Review patch recommendations as given in the article “Patches to apply before upgrading Oracle GI and DB to 18c (Doc ID 2414935.1)”
– Apply patch 29213893 on target ORACLE_HOME to avoid ORA-01422 error – refer: Database Upgrade to 12.2, 18c, 19c fails with ORA-01422, ORA-06512 for SYS.DBMS_STATS (Doc ID 2525596.1)
Prerequisites for Preparing Oracle Home on Windows
Your system must meet these requirements before you can upgrade Oracle Database on Microsoft Windows platforms. For security reasons, different Microsoft Windows user accounts configured as Oracle
home users for different Oracle homes are not allowed to share the same Oracle Base.
– Database upgrade is supported when the same Windows user account is used as the Oracle home user in both the source and destination Oracle homes.
– Database upgrade is supported when the Oracle home from which the database is being upgraded uses the Windows Built-in Account. Releases earlier than Oracle Database 12c (release 11.2 and earlier) only supported the built-in account option for the Oracle home user on Windows.
– The Oracle home user may not have access to files outside its own Oracle Base and Oracle home. If that is the case, then if you choose a different Oracle Base during upgrade, it is possible that Oracle Database services cannot access files in the older Oracle Base. Using DBUA for database upgrade ensures that the Oracle home user has access to files outside of its own Oracle Base and its own Oracle home.
FILE|TERMINAL – Use this option to direct script output to a file. Use TERMINAL to direct output to the terminal. If it is not specified then defaultr is FILE.
TEXT – Use this option to specify log should be in Text format. Use TEXT to specify text output. Use XML to specify XML output. If you do not specify an output type, then the default is text.
DIR – Logs will be created under <output_dir>. Directs the output to a specific directory. If you do not specify an output directory with the DIR option, then the output is directed to one of the default locations: If you define ORACLE_BASE environment variable then the generated scripts and log files will be created under $ORACLE_BASE/cfgtoollogs/<dbname>/preupgrade/ location else it will create under $ORACLE_HOME/cfgtoollogs/db_name/preupgrade/.
source Oracle Home : /refresh/home/oracle/ora_base/product/18.104.22.168/dbhome_1
target Oracle Home : /refresh/home/oracle/ora_base/product/18.1
$ export ORACLE_SID=orcl
$ export ORACLE_BASE=/refresh/home/oracle/ora_base
$ export ORACLE_HOME=/refresh/home/oracle/ora_base/product/22.214.171.124/dbhome_1
$ $ORACLE_HOME/jdk/bin/java -jar /refresh/home/oracle/ora_base/product/18.1/rdbms/admin/preupgrade.jar FILE TEXT
Execute fixup scripts across the entire CDB:
1. Execute preupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /refresh/home/oracle/ora_base/product/126.96.36.199/dbhome_1/cfgtoollogs/ORCL/preupgrade/ -b preup_ORCL /refresh/home/oracle/ora_base/product/188.8.131.52/dbhome_1/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql
2. Review logs under /refresh/home/oracle/ora_base/product/184.108.40.206/dbhome_1/cfgtoollogs/ORCL/preupgrade/
After the upgrade:
1. Execute postupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /refresh/home/oracle/ora_base/product/220.127.116.11/dbhome_1/cfgtoollogs/ORCL/preupgrade/ -b postup_ORCL /refresh/home/oracle/ora_base/product/18.104.22.168/dbhome_1/cfgtoollogs/ORCL/preupgrade/postupgrade_fixups.sql
2. Review logs under /refresh/home/oracle/ora_base/product/22.214.171.124/dbhome_1/cfgtoollogs/ORCL/preupgrade/
You may find the below scripts :
$ ls -l /refresh/home/oracle/ora_base/product/126.96.36.199/dbhome_1/cfgtoollogs/ORCL/preupgrade/
-rw-r–r– 1 oracle oinstall 8237 Jul 10 05:37 postupgrade_fixups_CDB_ROOT.sql
-rw-r–r– 1 oracle oinstall 8221 Jul 10 05:38 postupgrade_fixups_PDB1.sql
-rw-r–r– 1 oracle oinstall 8221 Jul 10 05:38 postupgrade_fixups_PDB2.sql
-rw-r–r– 1 oracle oinstall 8221 Jul 10 05:38 postupgrade_fixups_PDB3.sql
-rw-r–r– 1 oracle oinstall 8237 Jul 10 05:37 postupgrade_fixups_PDB_SEED.sql
-rw-r–r– 1 oracle oinstall 28440 Jul 10 05:39 postupgrade_fixups.sql
-rw-r–r– 1 oracle oinstall 8778 Jul 10 05:37 preupgrade_fixups_CDB_ROOT.sql
-rw-r–r– 1 oracle oinstall 8693 Jul 10 05:38 preupgrade_fixups_PDB1.sql
-rw-r–r– 1 oracle oinstall 8693 Jul 10 05:38 preupgrade_fixups_PDB2.sql
-rw-r–r– 1 oracle oinstall 8693 Jul 10 05:38 preupgrade_fixups_PDB3.sql
-rw-r–r– 1 oracle oinstall 8709 Jul 10 05:37 preupgrade_fixups_PDB_SEED.sql
-rw-r–r– 1 oracle oinstall 36645 Jul 10 05:39 preupgrade_fixups.sql
Examine the preupgrade.log file and follow the recommendation. Execute the preupgrade_fixups_<pdb_name>.sql against all or respective pluggable database
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b /tmp/preupgrade_fixups /refresh/home/oracle/ora_base/product/188.8.131.52/dbhome_1/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql
$ORACLE_HOME/perl/bin/perl catcon.pl -n 2 -e -b /tmp/preupgrade_fixups /refresh/home/oracle/ora_base/product/184.108.40.206/dbhome_1/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql
Above command will run the preupgrade_fixups.sql against all the pluggable databases.
To run the preupgrade_fixups.sql against individual pluggable database, below command can be used:
$ORACLE_HOME/perl/bin/perl catcon.pl -c ‘CDB$ROOT’ -n 1 -e -b /tmp/preupgrade_fixups /refresh/home/oracle/ora_base/product/220.127.116.11/dbhome_1/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups_CDB_ROOT.sql
$ORACLE_HOME/perl/bin/perl catcon.pl -c ‘PDB1’ -n 1 -e -b /tmp/preupgrade_fixups /refresh/home/oracle/ora_base/product/18.104.22.168/dbhome_1/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups_CDB_PDB1.sql
The latest preupgrade utility for 18.x can be found from :
How to Download and Run Oracle’s Database Pre-Upgrade Utility (Doc ID 884522.1)
Check for Invalid Objects / Components
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 utlrp.sql to validate the invalid objects in the database. You can execute the utlrp.sql scripts multiple times to validate the invalid objects.
From 12.1 home,execute the utlrp.sql against the container and all the pluggable databases.
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d ”’.”’ utlrp.sql
$ sqlplus “/ as sysdba”
using -b utlrp, the log file utlrp0.log is generated as the script is run. The log file provides results of the recompile.
Gathering Optimizer Statistics to Decrease Oracle Database Downtime
Oracle strongly recommends that you use this procedure to gather statistics before performing Oracle Database upgrades.Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, enter the following SQL statement:
Above command gather dictionary statistics for all PDBs in a container database.
you can use the below command to gather statistics against a particular PDB.
For PDB1 pluggable database :
For CDB$ROOT container:
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
It will verify against all the pluggable databases or below query can be used:
Check of TIMESTAMP WITH TIMEZONE Datatype
The time zone files that are supplied with Oracle Database 12c release 2 (18.x) is version 26.
Case 1 Timezone version of source database is lower or equal 26.
If the source database is using a timezone file lower than version 26 then there is no DST patch to apply in source oracle home or target 12cR2 home.
Case 2 Timezone version of source database is higher than 26.
If the source database uses a Timezone version higher than 26 then BEFORE the upgrade you MUST patch the target 12cR2 $ORACLE_HOME with a timezone data file of the SAME version as the one used in the source release database.
Ensuring That No Files Are in Backup Mode and no files need media recovery Before Upgrading
Execute below query to check for the status of the backup:
SQL> SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE’;
Ensure that no files require media recovery:
SQL> SELECT * FROM v$recover_file;
Purging Recycle Bin before upgrade
Below command can be used to purge from all the pdbs:
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -b purge_recyclebin — –x”PURGE DBA_RECYCLEBIN”
SQL> PURGE DBA_RECYCLEBIN;
we can purge the recycle bin via DBUA.
Databases That Use Oracle Label Security and Oracle Database Vault
Audit Table Preupgrade and Archive Requirements
For Oracle Database releases earlier than 12.1 using Oracle Label Security and Oracle Database Vault, you must run the OLS preprocess script before you upgrade.
If you are upgrading from a database earlier than Oracle Database release 12.1 that uses Oracle Label Security (OLS) and Oracle 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 the olspreupgrade.sql script before upgrading is mandatory for upgrading databases earlier than Oracle Database release 12.1 that use Oracle Label Security and Oracle Database Vault. Once you have upgraded to Oracle Database release 12.1, you do not have to perform the OLS preprocessing procedure going forward to patch or upgrade the database.
Granting the DV_PATCH_ADMIN Role to SYS for Oracle Database Vault
If Oracle Database Vault is enabled, then to perform checks for Oracle Data Vault, the upgrade process requires running three SQL scripts – olspreupgrade.sql, emremove.sql, catnoamd.sql
Start SQL*Plus and connect as DVOWNER to the database that you want to upgrade.
Run the following statement:
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 18.x Oracle home.
You must copy the sqlnet.ora and the wallet file manually before starting the upgrade.
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. Open the Oracle wallet in mount.
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN;
Check the accounts use Case-Insensitive password version
Log in to SQL*Plus as an administrative user, and enter the following SQL query
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;
If there are any 10g versions, suggest you to refer Oracle documentation to fix 10g versions, failing to do so, user accounts with LOCKED after upgrade is completed.
About Password Case Sensitivity
Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE.
DBUA ( Step 1 of 10 )
Select the database to be upgrade , as there is only 1 database in example , it is auto selected — ORCL
DBUA ( Step 2 of 10 )
We can prioritize the upgrade of the pluggable database. in the given screen, the ORCL container database is having three pdbs : pdb1, pdb2, pdb3. The priority of pdb1 is set to 1 whereas for pdb2,pdb3 it is 2. Based on this selection DBUA will upgrade pdb1 first followed by pdb2, pdb3.
DBUA ( Step 3 of 10 )
Once the selection is done, DBUA will perform the pre-upgrade steps. It will execute the preupgrade scripts against all the pdbs and show errors / warnings. Choose “Show All Container” for the reported errors. If the errors are fixable than click on “Fix & Check Again”. If there are any errors which can not be fixed by DBUA then manually fix and verify that there are no errors in the prerequisite checks page.
DBUA ( Step 4 of 10 )
Once the preupgrade warnings / errors has been addressed, In step 4, we can choose options like Enable Parallel upgrade, Recompile Invalid Objects in post-upgrade, Timezone upgrade for all the pdbs (The timezone version on 18.x is 26), Gather statistics for all the pdbs.
DBUA ( Step 5 of 10 )
In the given screen, there are recovery options available. you can choose to Create a Guaranteed Restore Point or RMAN backup in case of failure of upgrade.
DBUA ( Step 6 of 10 )
We can configure new listener or upgrade the existing “LISTENER_ORCL” listener which is running from 22.214.171.124 home to Target 18.x home.
DBUA ( Step 7 of 10 )
This screen is to configure EM express or register the upgraded database with EM Cloud control.
DBUA ( Step 8 of 10 )
DBUA will show the summary of what actions it will perform
DBUA ( Step 9 of 10 )
The DBUA will start the upgrade process of ORCL database. Once the upgrade starts, DBUA will first perform the upgrade of CDB$ROOT container. it will perform the pre-upgrade steps and then it will start upgrading Oracle Server,XML component followed by other components except Oracle APEX ( which is not upgraded as a part of Database Upgrade , you need to do it separately )
Once the upgrade completed for CDB$ROOT, database will be restarted and DBUA will perform the post upgrade steps like running catuppst.sql, configuring listener for 18.x home, recompile invalid objects and upgrading timezone,configuring EM Express.
Once the CDB$ROOT gets upgraded successfully, DBUA will use the priority list where the PDB1 will be upgraded first. The PDB1 priority is set to 1.So based on the priority, DBUA will consider priority list “P1” for pluggable database PDB$SEED and PDB1 to be upgraded first. In below screen, DBUA will parallely perform the upgrade for PDB$SEED and PDB1. It will perform the upgrade of components viz Oracle Server, XDB, Workspace Manager component for both PDB$SEED, PDB1 pdbs.
Note that the CDB$ROOT and PDB$SEED has highest priority.
After completing the upgrade of PDB$SEED and PDB1, DBUA will start the post upgrade process for “P1” priority list for PDB$SEED and PDB1 pdbs. DBUA will bounce back both the pdbs PDB$SEED and PDB1 for post upgrade steps.
Once the upgrade of P1 list gets completed, DBUA will start the upgrade of P2 priority list which contains PDB2 and PDB3. In the below screen we can see that the PDB2 and PDB3 pdbs are getting upgraded parallely.Followed by the post upgrade steps for both the PDB’s respectively.
DBUA ( Step 10 of 10 )
The final screen will show the result of the container database upgraded to 18.x. It will show the list of pluggable databases upgraded. It will show the detailed summary for the time taken by DBUA to complete the upgrade of individual PDBs
It will show the timezone upgrade details for CDB$ROOT and all the pluggable databases.
Execute dbupgdiag.sql script against the CDB$ROOT container and all the pluggable databases PDB1, PDB2, PDB3 to verify the status of objects and components . If there are invalid objects then run utlrp.sql to recompile the invalid objects as follows:
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d ”’.”’ utlrp.sql ===> To run the utlrp.sql against all the PDBs.
$ORACLE_HOME/perl/bin/perl catcon.pl -l /tmp -n 2 -e -b utlrp -c ‘PDB1’ utlrp.sql ===> To recompile invalid objects for PDB1 pluggable database.
sql> connect / as sysdba