Step by Step Upgrade Oracle Database from 12c to 19c using DBUA

February 27, 2022

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.

DATABASE, ORACLE, ORACLE 19C, UPGRADE

Step by Step Upgrade Oracle Database from 12c to 19c using DBUA

Related Articles