Step by Step Upgrade Oracle Database from 12c to 19c using DBUA
In a previous post, we demonstrated Step by Step Manual Upgrade Oracle Database from 12c to 19c, click here to read more about it. In this article, we are going to demonstrate Step by Step Upgrade Oracle Database from12c to 19c using DBUA on Linux. Follow the below steps to perform an upgrade to 19c.
List of target upgrade versions from supported source version along with the certificate, data source support.oracle.com.
Direct Upgrade Version List:
| Source DB Version | Target DB Version |
| 18.1 | 19c |
| 12.2.0.2 | 19c |
| 12.1.0.2 | 19c |
| 11.2.04 | 19c |
Indirect Upgrade Version List:
| Source DB Version | Intermediate DB Version | Target DB Version |
| 12.1.0.1 | 12.1.0.2/12.2.0.1 | 19c |
| 11.2.0.1/11.2.0.2/11.2.0.3 | 11.2.0.4 | 19c |
| 11.1.0.6/11.1.0.7 | 11.2.0.4 | 19c |
| 10.2.0.2/10.2.0.3/10.2.0.4/10.2.0.5 | 11.2.0.4/12.1.0.2 | 19c |
| 10.1.0.5 | 11.2.0.4/12.1.0.2 | 19c |
| 9.2.0.8 or earlier | 11.2.0.4 | 19c |
19c Certificate:
Below are the environmental details of this demonstration.
| COMPONENTS | SOURCE | TARGET |
| Database Name & Type | LABDB03, Standalone | LABDB03, Standalone |
| Database Version | 12.2.0.1 | 19.3.0.0 |
| Oracle Home | /u01/app/oracle/product/12201/db_1 | /u01/app/oracle/product/1930/db_1 |
| DB Server Type | Linux, OEL 7.9 | Linux, OEL 7.9 |
We will complete the upgrade from 12c to 19c using DBUA in the below three-part followed by detailed steps.
1. Pre-Checks / Pre-Steps
2. Upgrade Dataase using DBUA
3. Post upgrade Steps
1. Pre-Checks / Pre-Steps
Click here to get all steps for prechecks and follow section 1. Pre-Checks / Pre-Steps.
2. Upgrade Dataase using DBUA
2.1: Set Environment: Set/Export the environment for 19c i.e new ORACLE_HOME
[oracle@DBsGuruN2 ~]$ export ORACLE_HOME=/u01/app/oracle/product/1930/db_1
[oracle@DBsGuruN2 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@DBsGuruN2 ~]$ which dbua
/u01/app/oracle/product/1930/db_1/bin/dbua
2.2: Launch DBUA: Launch dbua tool.
[oracle@DBsGuruN2 ~]$ dbua
Logs directory: /u01/app/oracle/cfgtoollogs/dbua/upgrade2022-02-25_05-27-12PM
2.3: Select Database: Select the database name which is required to be upgraded and also put the SYS password then click Next.
2.4: Prerequisite checks: Upgrade checks performed by dbua, once 100% looks good i.e there should not be any warnings, click on Next.
2.5: Upgrade options: Check the below options and click Next:
A. Enable parallel options.
B. Recompile invalid objects while post-upgrade.
C. Upgrade Timezone.
2.6: Backup & recovery options: Select option I have my own backup and recovery strategy since we have our own backup along with guaranteed restored options which have been performed in the prechecks step and click Next.
2.7: Network configuration (Listener): You can select the existing Listener name if it’s running from the same upgraded ORACLE HOME 19c else create a new Listener in case no LISTENER service from the new ORACLE HOME/version and click Next.
2.8: Configuration management: Safely skip this screen if not using EM and click Next.
2.9: Summary: Review the summary carefully which tells you about options opted from step 3 to step 8 along with the OLD & NEW version and HOME of the database. Still, we have the option to edit, upon completion of review/edit click on Finish.
2.10: Upgrade in progress: Take a long breath and closely monitor upgrade progress along with the database alert log and DBUA log. Here you also have an option to Pause your activities.
2.11: Upgrade Results: This is the last screen of the upgrade where you can review the result of the upgrade like database name, before and after upgrade version, components status along with the time taken in the upgrade, Timezone version, etc and click on Close.
3. Post upgrade Steps
3.1: Review upgrade log: Go to upgrade log location and review log files.
[oracle@DBsGuruN2 ~]$ cd /u01/app/oracle/cfgtoollogs/dbua/upgrade2022-02-25_05-27-12PM/labdb03/
[oracle@DBsGuruN2 labdb03]$ ls -lrt
total 72168
drwxr-x---. 3 oracle oinstall 21 Feb 25 17:27 oracle
-rw-r-----. 1 oracle oinstall 7884 Feb 25 17:27 preupgrade_driver.sql
-rw-r-----. 1 oracle oinstall 15085 Feb 25 17:27 dbms_registry_extended.sql
-rw-r-----. 1 oracle oinstall 455876 Feb 25 17:27 preupgrade_package.sql
-rw-r-----. 1 oracle oinstall 14016 Feb 25 17:27 parameters.properties
-rw-r-----. 1 oracle oinstall 100166 Feb 25 17:27 preupgrade_messages.properties
drwxr-x---. 3 oracle oinstall 24 Feb 25 17:27 upgrade
-rw-r-----. 1 oracle oinstall 41134 Feb 25 17:27 components.properties
-rw-r-----. 1 oracle oinstall 2 Feb 25 17:27 checksBuffer.tmp
-rw-r-----. 1 oracle oinstall 5633 Feb 25 17:27 preupgrade_fixups.sql
-rw-r-----. 1 oracle oinstall 8618 Feb 25 17:27 postupgrade_fixups.sql
-rw-r-----. 1 oracle oinstall 9275 Feb 25 17:27 upgrade.xml
-rw-r-----. 1 oracle oinstall 2742 Feb 25 17:35 PreUpgradeResults.html
-rw-r-----. 1 oracle oinstall 398 Feb 25 17:35 PreUpgrade.log
-rw-r-----. 1 oracle oinstall 69 Feb 25 17:36 Migrate_Sid.log
-rw-------. 1 oracle oinstall 530 Feb 25 17:37 catupgrd_catcon_9223.lst
-rw-r-----. 1 oracle oinstall 0 Feb 25 17:58 catupgrd_datapatch_upgrade.err
-rw-r-----. 1 oracle oinstall 1332 Feb 25 18:01 catupgrd_datapatch_upgrade.log
-rw-r-----. 1 oracle oinstall 9412420 Feb 25 18:03 catupgrd1.log
-rw-r-----. 1 oracle oinstall 6068047 Feb 25 18:03 catupgrd2.log
-rw-r-----. 1 oracle oinstall 7724647 Feb 25 18:03 catupgrd3.log
-rw-r-----. 1 oracle oinstall 38212 Feb 25 18:04 catupgrd_stderr.log
-rw-r-----. 1 oracle oinstall 49789767 Feb 25 18:04 catupgrd0.log
-rw-r-----. 1 oracle oinstall 10471 Feb 25 18:04 Oracle_Server.log
-rw-r-----. 1 oracle oinstall 1732 Feb 25 18:04 upg_summary_CDB_Root.log
-rw-r-----. 1 oracle oinstall 115 Feb 25 18:06 Utlprp.log
-rw-r-----. 1 oracle oinstall 3037 Feb 25 18:09 UpgradeTimezone.log
-rw-r-----. 1 oracle oinstall 672 Feb 25 18:10 PostUpgrade.log
-rw-r-----. 1 oracle oinstall 5574 Feb 25 18:11 UpgradeResults.html
-rw-r-----. 1 oracle oinstall 99149 Feb 25 18:11 sqls.log
[oracle@DBsGuruN2 labdb03]$ cat upg_summary_CDB_Root.log
Oracle Database Release 19 Post-Upgrade Status Tool 02-25-2022 18:03:3
Database Name: LABDB03
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 19.3.0.0.0 00:10:34
JServer JAVA Virtual Machine UPGRADED 19.3.0.0.0 00:01:35
Oracle XDK UPGRADED 19.3.0.0.0 00:00:33
Oracle Database Java Packages UPGRADED 19.3.0.0.0 00:00:06
OLAP Analytic Workspace UPGRADED 19.3.0.0.0 00:00:13
Oracle Label Security UPGRADED 19.3.0.0.0 00:00:07
Oracle Database Vault UPGRADED 19.3.0.0.0 00:00:15
Oracle Text UPGRADED 19.3.0.0.0 00:00:28
Oracle Workspace Manager UPGRADED 19.3.0.0.0 00:00:24
Oracle Real Application Clusters UPGRADED 19.3.0.0.0 00:00:00
Oracle XML Database UPGRADED 19.3.0.0.0 00:01:27
Oracle Multimedia UPGRADED 19.3.0.0.0 00:00:38
Spatial UPGRADED 19.3.0.0.0 00:03:46
Oracle OLAP API UPGRADED 19.3.0.0.0 00:00:09
Datapatch 00:02:56
Final Actions 00:03:08
Post Upgrade 00:00:25
Total Upgrade Time: 00:24:48
Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.
Grand Total Upgrade Time: [0d:0h:26m:36s]
3.2: Validate oratab: Validate in the file /etc/oratab, new ORACLE_HOME for 19c should be updated by dbua tool.
[oracle@DBsGuruN2 ~]$ cat /etc/oratab | grep -i labdb03
labdb03:/u01/app/oracle/product/1930/db_1:N
3.3: Validate the Database: Set the environment in the new terminal and login to the database.
[oracle@DBsGuruN2 ~]$ . oraenv
ORACLE_SID = [labdb01] ? labdb03
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@DBsGuruN2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Feb 27 13:53:45 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT NAME,OPEN_MODE,STATUS,VERSION FROM V$DATABASE, V$INSTANCE;
NAME OPEN_MODE STATUS VERSION
--------- -------------------- ------------ -----------------
LABDB03 READ WRITE OPEN 19.0.0.0.0
3.4: Execute postupgrade_fixups.sql: Execute postupgrade_fixups.sql which was generated in step 1.1.
SQL> @/home/oracle/bkp/labdb03_upgrade/postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package created.
No errors.
Package body created.
PL/SQL procedure successfully completed.
No errors.
Package created.
No errors.
Package body created.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2022-02-25 17:25:15
For Source Database: LABDB03
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
3. old_time_zones_exist YES None.
4. dir_symlinks YES None.
5. post_dictionary YES None.
6. post_fixed_objects NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
Session altered.
3.5: Execute utlrp.sql and validate Objects Status: Execute utlrp.sql and validate invalid objects in the database, should not be any invalid objects after the upgrade. You may ignore invalid objects which are owned by non-default users subject to the status prior to the upgrade.
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2022-02-25 18:38:41
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2022-02-25 18:38:42
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL> SELECT OWNER, COUNT(*) FROM DBA_OBJECTS WHERE STATUS <> 'VALID' GROUP BY OWNER;
no rows selected
3.6: Execute utlusts.sql post-upgrade validation tool: Execute utlusts.sql to validate upgrade status and review the output.
SQL> @$ORACLE_HOME/rdbms/admin/utlusts.sql
Oracle Database Release 19 Post-Upgrade Status Tool 02-25-2022 18:42:5
Database Name: LABDB03
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.3.0.0.0 00:10:34
JServer JAVA Virtual Machine VALID 19.3.0.0.0 00:01:35
Oracle XDK VALID 19.3.0.0.0 00:00:33
Oracle Database Java Packages VALID 19.3.0.0.0 00:00:06
OLAP Analytic Workspace VALID 19.3.0.0.0 00:00:13
Oracle Label Security VALID 19.3.0.0.0 00:00:07
Oracle Database Vault VALID 19.3.0.0.0 00:00:15
Oracle Text VALID 19.3.0.0.0 00:00:28
Oracle Workspace Manager VALID 19.3.0.0.0 00:00:24
Oracle Real Application Clusters OPTION OFF 19.3.0.0.0 00:00:00
Oracle XML Database VALID 19.3.0.0.0 00:01:27
Oracle Multimedia VALID 19.3.0.0.0 00:00:38
Spatial VALID 19.3.0.0.0 00:03:46
Oracle OLAP API VALID 19.3.0.0.0 00:00:09
Datapatch 00:02:56
Final Actions 00:03:08
Post Upgrade 00:00:25
Post Compile 00:00:01
Total Upgrade Time: 00:24:49
Database time zone version is 32. It meets current release needs.
NOTE: DST version should be must 32 and this is part of a successful upgrade.
3.7: Set the parameter Compatible: Change the COMPATIBLE parameter value to 19.0.0 to enable to use of all features of the upgraded version. This step is a very crucial step in terms of downgrading the database to the previous version or restoring guaranteed restore point. Make sure before the set a new value of COMPATIBLE parameter no major impact to DB in terms of performance, if possible test application for few days with an existing value of this parameter especially for the lower environment (DEV/TEST/UAT) upgrades so you have enough confidence to change it when you perform in PROD databases upgrade.
SQL> ALTER SYSTEM SET COMPATIBLE='19.0.0' SCOPE=SPFILE;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1241513488 bytes
Fixed Size 8896016 bytes
Variable Size 687865856 bytes
Database Buffers 536870912 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SQL> SHOW PARAMETER COMPATIBLE;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 19.0.0
3.8: Drop Guaranteed Restore Point: Drop restore point only after successful validation along with green signal by dependent applications/checkout.
SQL> COL NAME FOR A25
SQL> COL GUARANTEE_FLASHBACK_DATABASE FOR A31
SQL> SET LINES 333
SQL> SELECT NAME,GUARANTEE_FLASHBACK_DATABASE,TIME FROM V$RESTORE_POINT;
NAME GUARANTEE_FLASHBACK_DATABASE TIME
------------------------- ------------------------------- ---------------------------------------------------------------------------
PRE_UPGRADE_LABDB03_DBUA YES 25-FEB-22 04.59.50.000000000 PM
SQL> DROP RESTORE POINT PRE_UPGRADE_LABDB03_DBUA;
Restore point dropped.
SQL> SELECT NAME,GUARANTEE_FLASHBACK_DATABASE,TIME FROM V$RESTORE_POINT;
no rows selected
3.9: RAC DB Action: Follow the below steps for the RAC database only.
3.9.1: Set cluster_database to TRUE.
SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@DBsGuruN2 admin]$ srvctl start database -db <DB NAME>
SQL> SHOW PARAMETER CLUSTER_DATABASE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
3.9.2: Upgrade the Oracle Clusterware keys for the database.
[oracle@DBsGuruN2 admin]$ srvctl upgrade database -db <DB NAME> -o <ORACLE_HOME>
3.10: Listener Status: Validate the services for the database.
[oracle@DBsGuruN2 labdb03]$ lsnrctl status LISTENER_19C
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-FEB-2022 18:48:23
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DBsGuruN2.labdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER_19C
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 25-FEB-2022 17:32:03
Uptime 0 days 1 hr. 16 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/1930/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/DBsGuruN2/listener_19c/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DBsGuruN2.labdomain)(PORT=1521)))
Services Summary...
Service "labdb03" has 1 instance(s).
Instance "labdb03", status READY, has 1 handler(s) for this service...
Service "labdb03XDB" has 1 instance(s).
Instance "labdb03", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@DBsGuruN2 labdb03]$
3.11: Password file and Pfile: Create a password file and pfile as required and this is a completely optional step.
3.11.1: Password File Creation.
[oracle@DBsGuruN2 ~]$ cd $ORACLE_HOME/dbs
[oracle@DBsGuruN2 dbs]$ orapwd file=orapwlabdb03 password=XXXXXXXX@XXXX entries=10 force=y
[oracle@DBsGuruN2 dbs]$ ls -lrt orapwlabdb03
-rw-r-----. 1 oracle oinstall 6144 Feb 27 15:21 orapwlabdb03
3.11.2: Pfile Creation.
SQL> create pfile from spfile;
File created.
SQL> !ls -lrt $ORACLE_HOME/dbs/initlabdb03.ora
-rw-r-----. 1 oracle oinstall 1108 Feb 27 15:24 /u01/app/oracle/product/1930/db_1/dbs/initlabdb03.ora
3.12: Others:
3.12.1: Enable cronjobs/scheduler jobs/Triggers if any.
3.12.2: Delete blackout database in OEM. Click here to get steps for Target Blackouts in OEM 13c.
3.12.3: Start all dependent applications.
This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.
Related Articles
- Oracle Critical Database Patch ID for July 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for April 2024 along with enabled Download Link
- Step by Step Manual Upgrade Container (CDB-PDB) Database from 12c to 19c in Multitenant Architecture
- Steps to Upgrade Grid Infra – Standalone (GI) and Oracle Database from 12.2 to 19.14
- Steps to Apply Database and Grid Infrastructure RU Patch Before Grid Infrastructure Configuration (before root.sh or rootupgrade.sh execution)
