Step by Step Manual Upgrade Oracle Database from 12c to 19c

DBsGuru February 20, 2022 0 Comments

Step by Step Manual Upgrade Oracle Database from 12c to 19c

In a recent post, we demonstrated Step by Step Oracle Database Upgrade from 11g (11.2.0.4) to 12c (12.2.0.1) using DBUA, click here to read more about it. In this article, we are going to demonstrate Step by Step Manual Upgrade Oracle Database from12c to 19c on Linux. Follow the below steps to perform a manual upgrade to 19c.

List of target upgrade versions from supported source version along with the certificate, data source support.oracle.com.

Direct Upgrade 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 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 a manual upgrade from 12c to 19c in the below three-part followed by detailed steps.

1. Pre-Checks / Pre-Steps
2. Manual Upgrade using command Steps
3. Post upgrade Steps

1. Pre-Checks / Pre-Steps

1.1: Install 19c Binary: Install Oracle 19c binary if it’s not already available on the DB server. Click here to get 19c binary installation steps and follow the same.

1.2: Execute the pre-upgrade command: Execute the preupgrade tool from the source home (12c).

$12C_ORACLE_HOME/jdk/bin/java -jar $19C_ORACLE_HOME/rdbms/admin/preupgrade.jar FILE TEXT DIR output_dir

[oracle@DBsGuruN2 bkp]$ . oraenv
ORACLE_SID = [labdb03] ? labdb03
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@DBsGuruN2 ~]$ /u01/app/oracle/product/12201/db_1/jdk/bin/java -jar /u01/app/oracle/product/1930/db_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/bkp/labdb03_upgrade
==================
PREUPGRADE SUMMARY
==================
  /home/oracle/bkp/labdb03_upgrade/preupgrade.log
  /home/oracle/bkp/labdb03_upgrade/preupgrade_fixups.sql
  /home/oracle/bkp/labdb03_upgrade/postupgrade_fixups.sql
 
Execute fixup scripts as indicated below:
 
Before upgrade:
 
Log into the database and execute the preupgrade fixups
@/home/oracle/bkp/labdb03_upgrade/preupgrade_fixups.sql
 
After the upgrade:
 
Log into the database and execute the postupgrade fixups
@/home/oracle/bkp/labdb03_upgrade/postupgrade_fixups.sql
 
Preupgrade complete: 2022-02-08T18:02:01

Switch to the directory to review generated files and log by preupgrade tool as mentioned in preupgrade.jar.

[oracle@DBsGuruN1 ~]$ cd /home/oracle/bkp/labdb03_upgrade
[oracle@DBsGuruN2 labdb03_upgrade]$ ls -lrt
total 728
drwxr-xr-x. 3 oracle oinstall     21 Feb  8 18:01 oracle
-rw-r--r--. 1 oracle oinstall  15085 Feb  8 18:01 dbms_registry_extended.sql
-rw-r--r--. 1 oracle oinstall   7884 Feb  8 18:01 preupgrade_driver.sql
-rw-r--r--. 1 oracle oinstall 455876 Feb  8 18:01 preupgrade_package.sql
-rw-r--r--. 1 oracle oinstall 100166 Feb  8 18:01 preupgrade_messages.properties
-rw-r--r--. 1 oracle oinstall  14016 Feb  8 18:01 parameters.properties
drwxr-xr-x. 3 oracle oinstall     24 Feb  8 18:01 upgrade
-rw-r--r--. 1 oracle oinstall  41134 Feb  8 18:01 components.properties
-rw-r--r--. 1 oracle oinstall      2 Feb  8 18:01 checksBuffer.tmp
-rw-r--r--. 1 oracle oinstall   7734 Feb  8 18:02 preupgrade_fixups.sql
-rw-r--r--. 1 oracle oinstall   8618 Feb  8 18:02 postupgrade_fixups.sql
-rw-r--r--. 1 oracle oinstall   7079 Feb  8 18:02 preupgrade.log
-rw-r--r--. 1 oracle oinstall   1083 Feb  8 18:46 upgrade_initlabdb03.ora
[oracle@DBsGuruN1 labdb03_upgrade]$ cat preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2022-02-08T18:02:01
 
Upgrade-To version: 19.0.0.0.0
 
=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  LABDB03
     Container Name:  labdb03
       Container ID:  0
            Version:  12.2.0.1.0
     DB Patch Level:  No Patch Bundle applied
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  26
  Database log mode:  ARCHIVELOG
           Readonly:  FALSE
            Edition:  EE
 
  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID         
  JServer JAVA Virtual Machine           [to be upgraded]  VALID         
  Oracle XDK for Java                    [to be upgraded]  VALID         
  Real Application Clusters              [to be upgraded]  OPTION OFF   
  Oracle Workspace Manager               [to be upgraded]  VALID         
  OLAP Analytic Workspace                [to be upgraded]  VALID         
  Oracle Label Security                  [to be upgraded]  VALID         
  Oracle Database Vault                  [to be upgraded]  VALID         
  Oracle Text                            [to be upgraded]  VALID         
  Oracle XML Database                    [to be upgraded]  VALID         
  Oracle Java Packages                   [to be upgraded]  VALID         
  Oracle Multimedia                      [to be upgraded]  VALID         
  Oracle Spatial                         [to be upgraded]  VALID         
  Oracle OLAP API                        [to be upgraded]  VALID         
 
==============
BEFORE UPGRADE
==============
 
  REQUIRED ACTIONS
  ================
  None
 
  RECOMMENDED ACTIONS
  ===================
  1.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:
       
        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
       
      Dictionary statistics do not exist or are stale (not up-to-date).
       
      Dictionary statistics help the Oracle optimizer find efficient SQL
      execution plans and are essential for proper upgrade timing. Oracle
      recommends gathering dictionary statistics in the last 24 hours before
      database upgrade.
       
      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.
 
  2.  (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.
       
      None of the fixed object tables have had stats collected.
       
      Gathering statistics on fixed objects, if none have been gathered yet, is
      recommended prior to upgrading.
       
      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.
 
  INFORMATION ONLY
  ================
  3.  To help you keep track of your tablespace allocations, the following
      AUTOEXTEND tablespaces are expected to successfully EXTEND during the
      upgrade process.
       
                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                             450 MB       500 MB
      SYSTEM                             800 MB       912 MB
      TEMP                                32 MB       150 MB
      UNDOTBS1                            70 MB       439 MB
       
      Minimum tablespace sizes for upgrade are estimates.
 
  4.  Check the Oracle Backup and Recovery User's Guide for information on how
      to manage an RMAN recovery catalog schema.
       
      If you are using a version of the recovery catalog schema that is older
      than that required by the RMAN client version, then you must upgrade the
      catalog schema.
       
      It is good practice to have the catalog schema the same or higher version
      than the RMAN client version you are using.
 
  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database LABDB03
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following
 
    SQL>@/home/oracle/bkp/labdb03_upgrade/preupgrade_fixups.sql
 
=============
AFTER UPGRADE
=============
 
  REQUIRED ACTIONS
  ================
  None
 
  RECOMMENDED ACTIONS
  ===================
  5.  Upgrade the database time zone file using the DBMS_DST package.
       
      The database is using time zone file version 26 and the target 19 release
      ships with time zone file version 32.
       
      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 19 Oracle Database
      Globalization Support Guide.
 
  6.  To identify directory objects with symbolic links in the path name, run
      $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
      Recreate any directory objects listed, using path names that contain no
      symbolic links.
       
      Some directory object path names may currently contain symbolic links.
       
      Starting in Release 18c, symbolic links are not allowed in directory
      object path names used with BFILE data types, the UTL_FILE package, or
      external tables.
 
  7.  (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:
       
        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
       
      Oracle recommends gathering dictionary statistics after upgrade.
       
      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.
 
  8.  Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:
       
        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
       
      This recommendation is given for all preupgrade runs.
       
      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.
       
      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.
 
  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database LABDB03
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following
 
    SQL>@/home/oracle/bkp/labdb03_upgrade/postupgrade_fixups.sql
[oracle@DBsGuruN1 labdb03_upgrade]$ 
SQL> @/home/oracle/bkp/labdb03_upgrade/preupgrade_fixups.sql

NOTE: 1. Review log preupgrade.log and act if any action requires under “REQUIRED ACTIONS” especially for tablespaces SYSTEM, SYAUX, UNDO, TEMP, and optionally “RECOMMENDED ACTIONS“.

2. After taking action to fix issues as required, must execute again preupgrade tool and make sure no action require in preupgrade.log.

3. preupgrade_fixups.sql command will be executed before the upgrade, here we will be doing also manual execution of various SQLs and other commands which are the surety of success along with the smooth upgrade.

1.3. Refresh Materialized Views if any: Wait for the completion of materialized views if exist in the database.

SQL> declare
list_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
end;
/  2    3    4    5    6
 
PL/SQL procedure successfully completed.
 
SQL>  SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
 
no rows selected

1.4: Manually gather statistics: Execute the below commands to gather statistics which will reduce the total time of upgrade.

SQL> exec dbms_stats.gather_fixed_objects_stats;
 
PL/SQL procedure successfully completed.
 
SQL> exec dbms_stats.gather_schema_stats ('SYSTEM');
 
PL/SQL procedure successfully completed.
 
SQL> exec dbms_stats.gather_schema_stats ('SYS');
 
PL/SQL procedure successfully completed.
 
SQL> exec dbms_stats.gather_dictionary_stats;
 
PL/SQL procedure successfully completed.

1.5: Active Backup Validation: Validate database for active-backup or if any datafiles are in recovery mode.

SQL> SELECT * FROM v$recover_file;
 
no rows selected
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
 
no rows selected

1.6: Default Tablespace for SYS & SYSTEM: Validate users SYS and SYSTEM have assigned default tablespace SYSTEM.

SQL> SELECT * from dba_users where username in ('SYS','SYSTEM') and default_tablespace!='SYSTEM';
 
no rows selected

1.7: Pending 2phase pending transactions: Validate any Pending 2phase Transactions active.

SQL> SELECT * FROM dba_2pc_pending;
 
no rows selected

In case the above command returns any selected rows with data, then run the below following commands:

SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;

1.8: Invalid Objects compile: Execute the below command to validate invalid objects and Recompile them if found any invalid objects, especially for database default users.

SQL> select owner, count(*) from dba_objects where status <> 'VALID'group by owner;
 
no rows selected
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

1.9: Component’s version along with status: Validate components status and make sure none of should be INVALID.

SQL> set lines 333 pages 111
SQL> col COMP_NAME form a55
SQL> select comp_name, version, status from dba_registry;
 
COMP_NAME                                               VERSION                        STATUS
------------------------------------------------------- ------------------------------ --------------------------------------------
Oracle Database Catalog Views                           12.2.0.1.0                     VALID
Oracle Database Packages and Types                      12.2.0.1.0                     VALID
JServer JAVA Virtual Machine                            12.2.0.1.0                     VALID
Oracle XDK                                              12.2.0.1.0                     VALID
Oracle Database Java Packages                           12.2.0.1.0                     VALID
OLAP Analytic Workspace                                 12.2.0.1.0                     VALID
Oracle Real Application Clusters                        12.2.0.1.0                     OPTION OFF
Oracle XML Database                                     12.2.0.1.0                     VALID
Oracle Workspace Manager                                12.2.0.1.0                     VALID
Oracle Text                                             12.2.0.1.0                     VALID
Oracle Multimedia                                       12.2.0.1.0                     VALID
Spatial                                                 12.2.0.1.0                     VALID
Oracle OLAP API                                         12.2.0.1.0                     VALID
Oracle Label Security                                   12.2.0.1.0                     VALID
Oracle Database Vault                                   12.2.0.1.0                     VALID
 
15 rows selected.

1.10: Empty Database Recyclebin: Purge deleted objects from recyclebin.

SQL>  purge DBA_RECYCLEBIN;
 
DBA Recyclebin purged.
 
SQL> select count(*) from DBA_RECYCLEBIN;
 
  COUNT(*)
----------
         0

1.11: Validate value of parameter sec_case_sensitive_logon: If the value is FALSE then change to TRUE.

SQL> alter system set sec_case_sensitive_logon=TRUE scope=both;
 
System altered.
 
SQL> show parameter sec_case_sensitive_logon
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

1.12: Timezone: Validate the timezone.

SQL> COL PROPERTY_NAME FOR A25
SQL> COL PROPERTY_VALUE FOR A21
SQL> Select version from v$timezone_file;
 
   VERSION
----------
        26
 
1 row selected.
 
SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name ='DST_PRIMARY_TT_VERSION';
 
PROPERTY_NAME             PROPERTY_VALUE
------------------------- ---------------------
DST_PRIMARY_TT_VERSION    26
 
1 row selected.

1.13: Upgrade component APEX: If component APEX is installed in 12c then it’s recommended to upgrade it prior to upgrading the database to 19c following doc ID 1088970.1.

SQL> select COMP_ID, VERSION, STATUS from DBA_REGISTRY where COMP_ID='APEX';
 
no rows selected

1.14: Execute dbupgdiag.sql: This is recommended to execute dbupgdiag.sql which captured detailed information of installed COMPONENTS, INVALID OBJECTS along with much more detailed information of database and also must fix if found any action prior to upgrade to 19c. Click here to download dbupgdiag.sql.

SQL> @/home/oracle/bkp/labdb03_upgrade/dbupgdiag.sql
 
Enter location for Spooled output:
 
Enter value for 1: /home/oracle/bkp/labdb03_upgrade
 
09_Feb_2022_0556          .log
 
labdb03_
 
 
 
                          *** Start of LogFile ***
 
  Oracle Database Upgrade Diagnostic Utility       02-09-2022 17:56:06
 
===============
Hostname
===============
 
DBsGuruN2.labdomain
 
===============
Database Name
===============
 
LABDB03
 
===============
Database Uptime
===============
 
17:14 09-FEB-22
 
=================
Database Wordsize
=================
 
This is a 64-bit database
 
================
Software Version
================
 
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                                0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0
 
=============
Compatibility
=============
 
Compatibility is set as 12.2.0
 
================
Archive Log Mode
================
 
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     9
Next log sequence to archive   11
Current log sequence           11
 
================
Auditing Check
================
 
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/labdb03/
                                                 adump
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      DB
unified_audit_sga_queue_size         integer     1048576
 
================
Cluster Check
================
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
 
DOC>################################################################
DOC>
DOC> If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
DOC> upgrading the database
DOC>
DOC>################################################################
DOC>#
 
===========================================
Tablespace and the owner of the aud$ table  ( IF Oracle Label Security and Oracle Database Vault are installed then aud$ will be in SYSTEM.AUD$)
===========================================
 
OWNER        TABLESPACE_NAME
------------ ------------------------------
SYS          SYSTEM
 
============================================================================
count of records in the sys.aud$ table where dbid is null- Standard Auditing
============================================================================
 
 
         0
 
 
============================================================================================
count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed
============================================================================================
select count(*) from system.aud$ where dbid is null
                            *
ERROR at line 1:
ORA-00942: table or view does not exist
 
 
 
 
=============================================================================
count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing
=============================================================================
 
         0
 
 
 
==========================================
Oracle Label Security is installed or not
==========================================
 
Oracle Label Security is installed
 
================
Number of AQ Records in Message Queue Tables
================
 
SYS - ALERT_QT - 0
SYS - AQ$_MEM_MC - 0
SYS - AQ_EVENT_TABLE - 0
SYS - AQ_PROP_TABLE - 0
SYS - KUPC$DATAPUMP_QUETAB - 0
SYS - ORA$PREPLUGIN_BACKUP_QTB - 0
SYS - SCHEDULER$_EVENT_QTAB - 0
SYS - SCHEDULER$_REMDB_JOBQTAB - 0
SYS - SCHEDULER_FILEWATCHER_QT - 0
SYS - SYS$SERVICE_METRICS_TAB - 0
WMSYS - WM$EVENT_QUEUE_TABLE - 0
 
================
Time Zone version
================
 
 
        26
 
================
Local Listener
================
 
 
LISTENER_LABDB03
 
================
Default and Temporary Tablespaces By User
================
 
 
USERNAME                     TEMPORARY_TABLESPACE   DEFAULT_TABLESPACE
---------------------------- ---------------------- ----------------------
SYS                          TEMP                   SYSTEM
SYSTEM                       TEMP                   SYSTEM
XS$NULL                      TEMP                   SYSTEM
OJVMSYS                      TEMP                   SYSTEM
LBACSYS                      TEMP                   SYSTEM
OUTLN                        TEMP                   SYSTEM
SYS$UMF                      TEMP                   SYSTEM
DBSNMP                       TEMP                   SYSAUX
APPQOSSYS                    TEMP                   SYSAUX
DBSFWUSER                    TEMP                   SYSAUX
GGSYS                        TEMP                   SYSAUX
ANONYMOUS                    TEMP                   SYSAUX
CTXSYS                       TEMP                   SYSAUX
SI_INFORMTN_SCHEMA           TEMP                   SYSAUX
DVSYS                        TEMP                   SYSAUX
DVF                          TEMP                   SYSAUX
GSMADMIN_INTERNAL            TEMP                   SYSAUX
ORDPLUGINS                   TEMP                   SYSAUX
MDSYS                        TEMP                   SYSAUX
OLAPSYS                      TEMP                   SYSAUX
ORDDATA                      TEMP                   SYSAUX
XDB                          TEMP                   SYSAUX
WMSYS                        TEMP                   SYSAUX
ORDSYS                       TEMP                   SYSAUX
GSMCATUSER                   TEMP                   USERS
MDDATA                       TEMP                   USERS
SYSBACKUP                    TEMP                   USERS
REMOTE_SCHEDULER_AGENT       TEMP                   USERS
GSMUSER                      TEMP                   USERS
SYSRAC                       TEMP                   USERS
AUDSYS                       TEMP                   USERS
DIP                          TEMP                   USERS
SYSKM                        TEMP                   USERS
ORACLE_OCM                   TEMP                   USERS
SYSDG                        TEMP                   USERS
SPATIAL_CSW_ADMIN_USR        TEMP                   USERS
 
 
================
Component Status
================
 
Comp ID Component                          Status    Version        Org_Version    Prv_Version
------- ---------------------------------- --------- -------------- -------------- --------------
APS     OLAP Analytic Workspace            VALID     12.2.0.1.0
CATALOG Oracle Database Catalog Views      VALID     12.2.0.1.0
CATJAVA Oracle Database Java Packages      VALID     12.2.0.1.0
CATPROC Oracle Database Packages and Types VALID     12.2.0.1.0
CONTEXT Oracle Text                        VALID     12.2.0.1.0
DV      Oracle Database Vault              VALID     12.2.0.1.0
JAVAVM  JServer JAVA Virtual Machine       VALID     12.2.0.1.0
OLS     Oracle Label Security              VALID     12.2.0.1.0
ORDIM   Oracle Multimedia                  VALID     12.2.0.1.0
OWM     Oracle Workspace Manager           VALID     12.2.0.1.0
RAC     Oracle Real Application Clusters   OPTION OFF12.2.0.1.0
SDO     Spatial                            VALID     12.2.0.1.0
XDB     Oracle XML Database                VALID     12.2.0.1.0
XML     Oracle XDK                         VALID     12.2.0.1.0
XOQ     Oracle OLAP API                    VALID     12.2.0.1.0
 
 
 
======================================================
List of Invalid Database Objects Owned by SYS / SYSTEM
======================================================
 
 
Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects
 
DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
 
 
no rows selected
 
 
================================
List of Invalid Database Objects
================================
 
 
Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects
 
DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
 
 
no rows selected
 
 
======================================================
Count of Invalids by Schema
======================================================
 
==============================================================
Identifying whether a database was created as 32-bit or 64-bit
==============================================================
 
DOC>###########################################################################
DOC>
DOC> Result referencing the string 'B023' ==> Database was created as 32-bit
DOC> Result referencing the string 'B047' ==> Database was created as 64-bit
DOC> When String results in 'B023' and when upgrading database to 10.2.0.3.0
DOC> (64-bit) , For known issue refer below articles
DOC>
DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
DOC>               Upgrading Or Patching Databases To 10.2.0.3
DOC> Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
DOC>              OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6
DOC>
DOC>###########################################################################
DOC>#
 
 
Metadata Initial DB Creation Info
-------- -----------------------------------
B047     Database was created as 64-bit
 
===================================================
Number of Duplicate Objects Owned by SYS and SYSTEM
===================================================
 
Counting duplicate objects ....
 
 
  COUNT(1)
----------
         0
 
=========================================
Duplicate Objects Owned by SYS and SYSTEM
=========================================
 
Querying duplicate objects ....
 
 
DOC>
DOC>################################################################################
DOC>Below are expected and required duplicates objects and OMITTED in the report .
DOC>
DOC>Without replication installed:
DOC>INDEX           AQ$_SCHEDULES_PRIMARY
DOC>TABLE           AQ$_SCHEDULES
DOC>
DOC>If replication is installed by running catrep.sql:
DOC>INDEX           AQ$_SCHEDULES_PRIMARY
DOC>PACKAGE         DBMS_REPCAT_AUTH
DOC>PACKAGE BODY        DBMS_REPCAT_AUTH
DOC>TABLE           AQ$_SCHEDULES
DOC>
DOC>If any objects found please follow below article.
DOC>Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
DOC>Read the Exceptions carefully before taking actions.
DOC>
DOC>################################################################################
DOC>#
 
========================
Password protected roles
========================
 
DOC>
DOC>################################################################################
DOC>
DOC> In version 11.2 password protected roles are no longer enabled by default so if
DOC> an application relies on such roles being enabled by default and no action is
DOC> performed to allow the user to enter the password with the set role command, it
DOC> is recommended to remove the password from those roles (to allow for existing
DOC> privileges to remain available). For more information see:
DOC>
DOC> Note 745407.1 : What Roles Can Be Set as Default for a User?
DOC>
DOC>################################################################################
DOC>#
 
Querying for password protected roles ....
 
 
================
JVM Verification
================
 
 
================================================
Checking Existence of Java-Based Users and Roles
================================================
 
DOC>
DOC>################################################################################
DOC>
DOC> There should not be any Java Based users for database version 9.0.1 and above.
DOC> If any users found, it is faulty JVM.
DOC>
DOC>################################################################################
DOC>#
 
 
User Existence
---------------------------
No Java Based Users
 
DOC>
DOC>###############################################################
DOC>
DOC> Healthy JVM Should contain Six Roles. For 12.2 Seven Roles
DOC> If there are more or less than six role, JVM is inconsistent.
DOC>
DOC>###############################################################
DOC>#
 
 
Role
------------------------------
There are 7 JAVA related roles
 
Roles
 
 
ROLE
------------------------------
JAVAUSERPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVADEBUGPRIV
DBJAVASCRIPT
JAVA_ADMIN
JAVA_DEPLOY
 
=========================================
List of Invalid Java Objects owned by SYS
=========================================
 
There are no SYS owned invalid JAVA objects
 
DOC>
DOC>#################################################################
DOC>
DOC> Check the status of the main JVM interface packages DBMS_JAVA
DOC> and INITJVMAUX and make sure it is VALID.
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>#################################################################
DOC>#
 
 
no rows selected
 
 
DOC>
DOC>#################################################################
DOC>
DOC> If the JAVAVM component is not installed in the database (for
DOC> example, after creating the database with custom scripts), the
DOC> next query will report the following error:
DOC>
DOC>   select dbms_java.longname('foo') "JAVAVM TESTING" from dual
DOC>   *
DOC>   ERROR at line 1:
DOC>   ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
DOC>
DOC> If the JAVAVM component is installed, the query should succeed
DOC> with 'foo' as result.
DOC>
DOC>#################################################################
DOC>#
 
 
JAVAVM TESTING
---------------
foo
 
===================================
Oracle Multimedia/InterMedia status
===================================
 
.
Oracle Multimedia/interMedia is installed and listed with the following version: 12.2.0.1.0 and status: VALID
.
Checking for installed Database Schemas...
ORDSYS user exists.
ORDPLUGINS user exists.
MDSYS user exists.
SI_INFORMTN_SCHEMA user exists.
.
Checking for Prerequisite Components...
JAVAVM installed and listed as valid
XDK installed and listed as valid
XDB installed and listed as valid
Validating Oracle Multimedia/interMedia...(no output if component status is valid)
 
PL/SQL procedure successfully completed.
 
 
                            *** End of LogFile ***
 
 
 
Upload db_upg_diag_labdb03_09_Feb_2022_0556.log from "/home/oracle/bkp/labdb03_upgrade" directory
 
SQL>

1.15: Database Backup: Take the database a full backup before upgrade.

[oracle@DBsGuruN2 bkp]$ . oraenv
ORACLE_SID = [labdb03] ? labdb03
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@DBsGuruN2 bkp]$ rman target /
 
Recovery Manager: Release 12.2.0.1.0 - Production on Tue Feb 8 17:54:05 2022
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: LABDB03 (DBID=326197667)
 
RMAN> run
{
ALLOCATE CHANNEL D1 DEVICE TYPE DISK FORMAT '/home/oracle/bkp/labdb03_%U';
ALLOCATE CHANNEL D2 DEVICE TYPE DISK FORMAT '/home/oracle/bkp/labdb03_%U';
ALLOCATE CHANNEL D3 DEVICE TYPE DISK FORMAT '/home/oracle/bkp/labdb03_%U';
BACKUP tag 'UPGRADE_DB' FORCE AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
BACKUP CURRENT CONTROLFILE TAG 'UPGRADE_CTL' FORMAT '/home/oracle/bkp/labdb03ctl_%u';
BACKUP SPFILE TAG 'UPGRADE_SPFILE' FORMAT '/home/oracle/bkp/labdb03sf_%U';
RELEASE CHANNEL D1;
2> RELEASE CHANNEL D2;
RELEASE CHANNEL D3;
}3> 4> 5> 6> 7> 8> 9> 10> 11> 12>
 
using target database control file instead of recovery catalog
allocated channel: D1
channel D1: SID=17 device type=DISK
 
allocated channel: D2
channel D2: SID=140 device type=DISK
 
allocated channel: D3
channel D3: SID=268 device type=DISK
 
 
Starting backup at 08-FEB-22
current log archived
channel D1: starting compressed archived log backup set
channel D1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=1096134857
channel D1: starting piece 1 at 08-FEB-22
channel D1: finished piece 1 at 08-FEB-22
piece handle=/home/oracle/bkp/labdb03_010lbc69_1_1 tag=UPGRADE_DB comment=NONE
channel D1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-FEB-22
 
Starting backup at 08-FEB-22
channel D1: starting compressed full datafile backup set
channel D1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/labdb03/system01.dbf
input datafile file number=00007 name=/oradata/labdb03/users01.dbf
channel D1: starting piece 1 at 08-FEB-22
channel D2: starting compressed full datafile backup set
channel D2: specifying datafile(s) in backup set
input datafile file number=00003 name=/oradata/labdb03/sysaux01.dbf
input datafile file number=00004 name=/oradata/labdb03/undotbs01.dbf
channel D2: starting piece 1 at 08-FEB-22
channel D1: finished piece 1 at 08-FEB-22
piece handle=/home/oracle/bkp/labdb03_020lbc6a_1_1 tag=UPGRADE_DB comment=NONE
channel D1: backup set complete, elapsed time: 00:00:35
channel D2: finished piece 1 at 08-FEB-22
piece handle=/home/oracle/bkp/labdb03_030lbc6a_1_1 tag=UPGRADE_DB comment=NONE
channel D2: backup set complete, elapsed time: 00:00:35
Finished backup at 08-FEB-22
 
Starting backup at 08-FEB-22
current log archived
channel D1: starting compressed archived log backup set
channel D1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=2 STAMP=1096134894
channel D1: starting piece 1 at 08-FEB-22
channel D1: finished piece 1 at 08-FEB-22
piece handle=/home/oracle/bkp/labdb03_040lbc7e_1_1 tag=UPGRADE_DB comment=NONE
channel D1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-FEB-22
 
Starting backup at 08-FEB-22
channel D1: starting full datafile backup set
channel D1: specifying datafile(s) in backup set
including current control file in backup set
channel D1: starting piece 1 at 08-FEB-22
channel D1: finished piece 1 at 08-FEB-22
piece handle=/home/oracle/bkp/labdb03ctl_050lbc7f tag=UPGRADE_CTL comment=NONE
channel D1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-FEB-22
 
Starting backup at 08-FEB-22
channel D1: starting full datafile backup set
channel D1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel D1: starting piece 1 at 08-FEB-22
channel D1: finished piece 1 at 08-FEB-22
piece handle=/home/oracle/bkp/labdb03sf_060lbc7h_1_1 tag=UPGRADE_SPFILE comment=NONE
channel D1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-FEB-22
 
Starting Control File and SPFILE Autobackup at 08-FEB-22
piece handle=/home/oracle/FRA/LABDB03/autobackup/2022_02_08/o1_mf_s_1096134898_k04r8tz4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 08-FEB-22
 
released channel: D1
 
released channel: D2
 
released channel: D3
 
RMAN>
[oracle@DBsGuruN2 ~]$ cd /home/oracle/bkp/
[oracle@DBsGuruN2 bkp]$ ls -lrt
total 329004
-rw-r-----. 1 oracle oinstall   1245696 Feb  8 17:54 labdb03_010lbc69_1_1
-rw-r-----. 1 oracle oinstall 111738880 Feb  8 17:54 labdb03_030lbc6a_1_1
-rw-r-----. 1 oracle oinstall 213123072 Feb  8 17:54 labdb03_020lbc6a_1_1
-rw-r-----. 1 oracle oinstall      7168 Feb  8 17:54 labdb03_040lbc7e_1_1
-rw-r-----. 1 oracle oinstall  10665984 Feb  8 17:54 labdb03ctl_050lbc7f
-rw-r-----. 1 oracle oinstall    114688 Feb  8 17:54 labdb03sf_060lbc7h_1_1
[oracle@DBsGuruN2 bkp]$

1.16: Create guaranteed restore point: You can also create a guaranteed restore point, for this feature your DB should be in archive log mode along with flashback ON with ample free space in FRA and this is a completely optional step depending on your requirements & criticality.

SQL> create restore point PRE_UPGRADE_LABDB03 guarantee flashback database;
 
Restore point created.
 
SQL> COL NAME FOR A25
SQL> COL GUARANTEE_FLASHBACK_DATABASE FOR A31
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
 
NAME                      GUARANTEE_FLASHBACK_DATABASE    TIME
------------------------- ------------------------------- ---------------------------------------------------------------------------
PRE_UPGRADE_LABDB03       YES                             08-FEB-22 06.37.46.000000000 PM
[oracle@DBsGuruN2 ~]$ cd /home/oracle/FRA/LABDB03/flashback
[oracle@DBsGuruN2 flashback]$ ls -lrt
total 102416
-rw-r-----. 1 oracle oinstall 52436992 Feb  8 18:37 o1_mf_k04ts4sd_.flb
-rw-r-----. 1 oracle oinstall 52436992 Feb  8 18:38 o1_mf_k04ts2gn_.flb

1.17: Change the parameter CLUSTER_DATABASE to FALSE: To change the value of CLUSTER_DATABASE to FALSE is applicable only to the RAC database.

SQL> alter system set cluster_database=FALSE scope=spfile;
 
System altered.
 
SQL> show parameter cluster_database
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE

1.18: Others:
1.19.1: Disable cronjobs/scheduler jobs/Triggers if any.
1.19.2: Blackout database in OEM. Click here to get steps for Target Blackouts in OEM 13c.
1.19.3: Stop all dependent applications.

1.19: Stop the database: Shutdown the database.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

For RAC:

1	`[oracle@DBsGuruN2 ~]$ srvctl stop` `database` `-db <DB` `NAME>`

1.20: Copy SPFILE & PASSWORD FILE: Copy SPFILE & PASSWORD FILE from 12c to 19c ORACLE HOME.

[oracle@DBsGuruN2 dbs]$ cd $ORACLE_HOME/dbs
[oracle@DBsGuruN2 dbs]$ cp orapwlabdb03 spfilelabdb03.ora /u01/app/oracle/product/1930/db_1/dbs/
[oracle@DBsGuruN2 dbs]$ ls -lrt /u01/app/oracle/product/1930/db_1/dbs/*labdb03*
-rw-r-----. 1 oracle oinstall 3584 Feb  9 19:07 /u01/app/oracle/product/1930/db_1/dbs/spfilelabdb03.ora
-rw-r-----. 1 oracle oinstall 3584 Feb  9 19:07 /u01/app/oracle/product/1930/db_1/dbs/orapwlabdb03

2. Manual Upgrade using command Steps

2.1: Startup in Upgrade Mode: Start the database in upgrade mode from 19c ORACLE_HOME.

[oracle@DBsGuruN2 ~]$ export ORACLE_HOME=/u01/app/oracle/product/1930/db_1
[oracle@DBsGuruN2 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@DBsGuruN2 ~]$ export ORACLE_SID=labdb03
[oracle@DBsGuruN2 ~]$ which sqlplus
/u01/app/oracle/product/1930/db_1/bin/sqlplus
[oracle@DBsGuruN2 ~]$ sqlplus -v
 
SQL*Plus: Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
[oracle@DBsGuruN2 ~]$
 
[oracle@DBsGuruN2 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 9 19:22:43 2022
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> startup upgrade
ORACLE instance started.
 
Total System Global Area 1241513488 bytes
Fixed Size                  8896016 bytes
Variable Size             335544320 bytes
Database Buffers          889192448 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.
SQL> SELECT NAME,OPEN_MODE,STATUS,VERSION FROM V$DATABASE, V$INSTANCE;
 
NAME      OPEN_MODE            STATUS       VERSION
--------- -------------------- ------------ -----------------
LABDB03   READ WRITE           OPEN MIGRATE 19.0.0.0.0

2.2: Execute dbupgrade command-line tool: To manually upgrade the 19c database, we can execute any one of the commands dbupgrade or catctl.pl.

$ORACLE_HOME/bin/dbupgrade -n 5 -l /home/oracle/bkp/labdb03_upgrade/log

OR

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -n 5 -l /home/oracle/bkp/labdb03_upgrade/log catupgrd.sql

-n Maximum number of parallel SQL processes to use when upgrading the database. Multitenant database defaults to the total number of CPUs on your system. Traditional database defaults to 4.

-l Directory to use for spool log files.

Here we are upgrading using dbupgrade command-line tool and it took approx 26 minutes to complete the upgrade and executed in the background using nohup. Follow the below:

[oracle@DBsGuruN2 bin]$ ls dbupgrade
dbupgrade
[oracle@DBsGuruN2 bin]$ ls -lt dbupgrade
-rwxr-x---. 1 oracle oinstall 3136 Apr 17  2019 dbupgrade
[oracle@DBsGuruN2 bin]$
[oracle@DBsGuruN2 bin]$ nohup ./dbupgrade -l /home/oracle/bkp/labdb03_upgrade/log &
[1] 13156
[oracle@DBsGuruN2 bin]$ nohup: ignoring input and appending output to ‘nohup.out’
 
 
[oracle@DBsGuruN2 bin]$ tail -333f nohup.out
 
Argument list for [/u01/app/oracle/product/1930/db_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in                       c = 0
Do not run in                C = 0
Input Directory              d = 0
Echo OFF                     e = 1
Simulate                     E = 0
Forced cleanup               F = 0
Log Id                       i = 0
Child Process                I = 0
Log Dir                      l = /home/oracle/bkp/labdb03_upgrade/log
Priority List Name           L = 0
Upgrade Mode active          M = 0
SQL Process Count            n = 0
SQL PDB Process Count        N = 0
Open Mode Normal             o = 0
Start Phase                  p = 0
End Phase                    P = 0
Reverse Order                r = 0
AutoUpgrade Resume           R = 0
Script                       s = 0
Serial Run                   S = 0
RO User Tablespaces          T = 0
Display Phases               y = 0
Debug catcon.pm              z = 0
Debug catctl.pl              Z = 0
 
catctl.pl VERSION: [19.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]
 
 
/u01/app/oracle/product/1930/db_1/rdbms/admin/orahome = [/u01/app/oracle/product/1930/db_1]
/u01/app/oracle/product/1930/db_1/bin/orabasehome = [/u01/app/oracle/product/1930/db_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/1930/db_1]
 
Analyzing file /u01/app/oracle/product/1930/db_1/rdbms/admin/catupgrd.sql
 
Log file directory = [/home/oracle/bkp/labdb03_upgrade/log]
 
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/bkp/labdb03_upgrade/log/catupgrd_catcon_13161.lst]
 
catcon::set_log_file_base_path: catcon: See [/home/oracle/bkp/labdb03_upgrade/log/catupgrd*.log] files for output generated by scripts
 
catcon::set_log_file_base_path: catcon: See [/home/oracle/bkp/labdb03_upgrade/log/catupgrd_*.lst] files for spool files, if any
 
 
Number of Cpus        = 4
Database Name         = labdb03
DataBase Version      = 12.2.0.1.0
Parallel SQL Process Count            = 4
Components in [labdb03]
    Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [APEX EM MGW ODM RAC WK]
 
------------------------------------------------------
Phases [0-107]         Start Time:[2022_02_09 19:35:04]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [labdb03] Files:1    Time: 52s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [labdb03] Files:5    Time: 29s
Restart  Phase #:2    [labdb03] Files:1    Time: 4s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [labdb03] Files:19   Time: 11s
Restart  Phase #:4    [labdb03] Files:1    Time: 3s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [labdb03] Files:7    Time: 12s
*****************   Catproc Start   ****************
Serial   Phase #:6    [labdb03] Files:1    Time: 10s
*****************   Catproc Types   ****************
Serial   Phase #:7    [labdb03] Files:2    Time: 10s
Restart  Phase #:8    [labdb03] Files:1    Time: 4s
****************   Catproc Tables   ****************
Parallel Phase #:9    [labdb03] Files:67   Time: 19s
Restart  Phase #:10   [labdb03] Files:1    Time: 4s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [labdb03] Files:1    Time: 52s
Restart  Phase #:12   [labdb03] Files:1    Time: 3s
**************   Catproc Procedures   **************
Parallel Phase #:13   [labdb03] Files:94   Time: 10s
Restart  Phase #:14   [labdb03] Files:1    Time: 3s
Parallel Phase #:15   [labdb03] Files:120  Time: 14s
Restart  Phase #:16   [labdb03] Files:1    Time: 3s
Serial   Phase #:17   [labdb03] Files:22   Time: 5s
Restart  Phase #:18   [labdb03] Files:1    Time: 4s
*****************   Catproc Views   ****************
Parallel Phase #:19   [labdb03] Files:32   Time: 12s
Restart  Phase #:20   [labdb03] Files:1    Time: 4s
Serial   Phase #:21   [labdb03] Files:3    Time: 9s
Restart  Phase #:22   [labdb03] Files:1    Time: 4s
Parallel Phase #:23   [labdb03] Files:25   Time: 79s
Restart  Phase #:24   [labdb03] Files:1    Time: 3s
Parallel Phase #:25   [labdb03] Files:12   Time: 48s
Restart  Phase #:26   [labdb03] Files:1    Time: 3s
Serial   Phase #:27   [labdb03] Files:1    Time: 0s
Serial   Phase #:28   [labdb03] Files:3    Time: 4s
Serial   Phase #:29   [labdb03] Files:1    Time: 0s
Restart  Phase #:30   [labdb03] Files:1    Time: 3s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [labdb03] Files:1    Time: 3s
Restart  Phase #:32   [labdb03] Files:1    Time: 4s
Serial   Phase #:34   [labdb03] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [labdb03] Files:293  Time: 17s
Serial   Phase #:36   [labdb03] Files:1    Time: 0s
Restart  Phase #:37   [labdb03] Files:1    Time: 4s
Serial   Phase #:38   [labdb03] Files:6    Time: 5s
Restart  Phase #:39   [labdb03] Files:1    Time: 4s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [labdb03] Files:3    Time: 34s
Restart  Phase #:41   [labdb03] Files:1    Time: 4s
******************   Catproc SQL   *****************
Parallel Phase #:42   [labdb03] Files:13   Time: 48s
Restart  Phase #:43   [labdb03] Files:1    Time: 3s
Parallel Phase #:44   [labdb03] Files:11   Time: 7s
Restart  Phase #:45   [labdb03] Files:1    Time: 3s
Parallel Phase #:46   [labdb03] Files:3    Time: 3s
Restart  Phase #:47   [labdb03] Files:1    Time: 3s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [labdb03] Files:1    Time: 7s
Restart  Phase #:49   [labdb03] Files:1    Time: 4s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [labdb03] Files:1    Time: 3s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [labdb03] Files:1    Time: 3s
Restart  Phase #:52   [labdb03] Files:1    Time: 4s
**********   Upgrading Java and non-Java   *********
Serial   Phase #:53   [labdb03] Files:2    Time: 226s
*****************   Upgrading XDB   ****************
Restart  Phase #:54   [labdb03] Files:1    Time: 3s
Serial   Phase #:56   [labdb03] Files:3    Time: 10s
Serial   Phase #:57   [labdb03] Files:3    Time: 7s
Parallel Phase #:58   [labdb03] Files:10   Time: 5s
Parallel Phase #:59   [labdb03] Files:25   Time: 7s
Serial   Phase #:60   [labdb03] Files:4    Time: 8s
Serial   Phase #:61   [labdb03] Files:1    Time: 0s
Serial   Phase #:62   [labdb03] Files:32   Time: 6s
Serial   Phase #:63   [labdb03] Files:1    Time: 0s
Parallel Phase #:64   [labdb03] Files:6    Time: 9s
Serial   Phase #:65   [labdb03] Files:2    Time: 16s
Serial   Phase #:66   [labdb03] Files:3    Time: 20s
****************   Upgrading ORDIM   ***************
Restart  Phase #:67   [labdb03] Files:1    Time: 3s
Serial   Phase #:69   [labdb03] Files:1    Time: 4s
Parallel Phase #:70   [labdb03] Files:2    Time: 24s
Restart  Phase #:71   [labdb03] Files:1    Time: 4s
Parallel Phase #:72   [labdb03] Files:2    Time: 5s
Serial   Phase #:73   [labdb03] Files:2    Time: 4s
*****************   Upgrading SDO   ****************
Restart  Phase #:74   [labdb03] Files:1    Time: 3s
Serial   Phase #:76   [labdb03] Files:1    Time: 31s
Serial   Phase #:77   [labdb03] Files:2    Time: 5s
Restart  Phase #:78   [labdb03] Files:1    Time: 4s
Serial   Phase #:79   [labdb03] Files:1    Time: 32s
Restart  Phase #:80   [labdb03] Files:1    Time: 4s
Parallel Phase #:81   [labdb03] Files:3    Time: 50s
Restart  Phase #:82   [labdb03] Files:1    Time: 4s
Serial   Phase #:83   [labdb03] Files:1    Time: 7s
Restart  Phase #:84   [labdb03] Files:1    Time: 4s
Serial   Phase #:85   [labdb03] Files:1    Time: 10s
Restart  Phase #:86   [labdb03] Files:1    Time: 3s
Parallel Phase #:87   [labdb03] Files:4    Time: 51s
Restart  Phase #:88   [labdb03] Files:1    Time: 4s
Serial   Phase #:89   [labdb03] Files:1    Time: 4s
Restart  Phase #:90   [labdb03] Files:1    Time: 4s
Serial   Phase #:91   [labdb03] Files:2    Time: 8s
Restart  Phase #:92   [labdb03] Files:1    Time: 4s
Serial   Phase #:93   [labdb03] Files:1    Time: 2s
Restart  Phase #:94   [labdb03] Files:1    Time: 4s
*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******
Serial   Phase #:95   [labdb03] Files:1    Time: 12s
Restart  Phase #:96   [labdb03] Files:1    Time: 4s
***********   Final Component scripts    ***********
Serial   Phase #:97   [labdb03] Files:1    Time: 3s
*************   Final Upgrade scripts   ************
Serial   Phase #:98   [labdb03] Files:1    Time: 198s
*******************   Migration   ******************
Serial   Phase #:99   [labdb03] Files:1    Time: 2s
***   End PDB Application Upgrade Pre-Shutdown   ***
Serial   Phase #:100  [labdb03] Files:1    Time: 3s
Serial   Phase #:101  [labdb03] Files:1    Time: 0s
Serial   Phase #:102  [labdb03] Files:1    Time: 58s
*****************   Post Upgrade   *****************
Serial   Phase #:103  [labdb03] Files:1    Time: 32s
****************   Summary report   ****************
Serial   Phase #:104  [labdb03] Files:1    Time: 3s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:105  [labdb03] Files:1    Time: 2s
Serial   Phase #:106  [labdb03] Files:1    Time: 0s
Serial   Phase #:107  [labdb03] Files:1     Time: 30s
 
------------------------------------------------------
Phases [0-107]         End Time:[2022_02_09 20:00:34]
------------------------------------------------------
 
Grand Total Time: 1531s
 
 LOG FILES: (/home/oracle/bkp/labdb03_upgrade/log/catupgrd*.log)
 
Upgrade Summary Report Located in:
/home/oracle/bkp/labdb03_upgrade/log/upg_summary.log
 
Grand Total Upgrade Time:    [0d:0h:25m:31s]

3. Post upgrade Steps

3.1: Review Upgrade Logs: Go to upgrade log location and review log files.

[oracle@DBsGuruN2 ~]$ cd /home/oracle/bkp/labdb03_upgrade/log
[oracle@DBsGuruN2 log]$ ls -lrt
total 71344
-rw-------. 1 oracle oinstall      464 Feb  9 19:34 catupgrd_catcon_13161.lst
-rw-r--r--. 1 oracle oinstall        0 Feb  9 19:55 catupgrd_datapatch_upgrade.err
-rw-r--r--. 1 oracle oinstall     1307 Feb  9 19:58 catupgrd_datapatch_upgrade.log
-rw-------. 1 oracle oinstall  7802138 Feb  9 20:00 catupgrd1.log
-rw-------. 1 oracle oinstall  5728207 Feb  9 20:00 catupgrd2.log
-rw-------. 1 oracle oinstall  8599735 Feb  9 20:00 catupgrd3.log
-rw-r--r--. 1 oracle oinstall    37956 Feb  9 20:00 catupgrd_stderr.log
-rw-r--r--. 1 oracle oinstall     1732 Feb  9 20:00 upg_summary.log
-rw-------. 1 oracle oinstall 50854214 Feb  9 20:00 catupgrd0.log
[oracle@DBsGuruN2 log]$ cat upg_summary.log
 
Oracle Database Release 19 Post-Upgrade Status Tool    02-09-2022 20:00:0
Database Name: LABDB03
 
Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS
 
Oracle Server                          UPGRADED      19.3.0.0.0  00:09:30
JServer JAVA Virtual Machine           UPGRADED      19.3.0.0.0  00:01:27
Oracle XDK                             UPGRADED      19.3.0.0.0  00:00:31
Oracle Database Java Packages          UPGRADED      19.3.0.0.0  00:00:07
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:16
Oracle Text                            UPGRADED      19.3.0.0.0  00:00:30
Oracle Workspace Manager               UPGRADED      19.3.0.0.0  00:00:28
Oracle Real Application Clusters       UPGRADED      19.3.0.0.0  00:00:00
Oracle XML Database                    UPGRADED      19.3.0.0.0  00:01:23
Oracle Multimedia                      UPGRADED      19.3.0.0.0  00:00:36
Spatial                                UPGRADED      19.3.0.0.0  00:03:40
Oracle OLAP API                        UPGRADED      19.3.0.0.0  00:00:09
Datapatch                                                        00:03:08
Final Actions                                                    00:03:20
Post Upgrade                                                     00:00:27
 
Total Upgrade Time: 00:23:41
 
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:25m:31s]
[oracle@DBsGuruN2 log]$

NOTE: If don’t pass the parameter for log location in dbupgrade command then below is the default location for the upgrade log.

$ORACLE_HOME/cfgtoollogs/<DB NAME>/upgrade/

3.2: Update Oratab: Update new ORACLE_HOME for 19c in the file /etc/oratab.

[oracle@DBsGuruN2 ~]$ vi /etc/oratab
labdb03:/u01/app/oracle/product/1930/db_1:N
:wq
[oracle@DBsGuruN2 ~]$ cat /etc/oratab | grep -i labdb03
labdb03:/u01/app/oracle/product/1930/db_1:N

3.3: Start the Database: Set the environment and start 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 Thu Feb 10 19:29:57 2022
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> startup
ORACLE instance started.
 
Total System Global Area 1241513488 bytes
Fixed Size                  8896016 bytes
Variable Size             520093696 bytes
Database Buffers          704643072 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.
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 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. After completion of the script must review logs and validate the object’s status in DB.

[oracle@DBsGuruN2 ~]$ . oraenv
ORACLE_SID = [labdb01] ? labdb03
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@DBsGuruN2 ~]$ cd $ORACLE_HOME/rdbms/admin/
[oracle@DBsGuruN2 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/1930/db_1/rdbms/admin/utlrp_catcon_5328.lst]
 
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/1930/db_1/rdbms/admin/utlrp*.log] files for output generated by scripts
 
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/1930/db_1/rdbms/admin/utlrp_*.lst] files for spool files, if any
 
catcon.pl: completed successfully
 
[oracle@DBsGuruN2 admin]$ ls -lrt /u01/app/oracle/product/1930/db_1/rdbms/admin/utlrp*.log
-rw-------. 1 oracle oinstall 10268 Feb 10 19:39 /u01/app/oracle/product/1930/db_1/rdbms/admin/utlrp0.log
[oracle@DBsGuruN2 admin]$ cat  /u01/app/oracle/product/1930/db_1/rdbms/admin/utlrp0.log
 
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 10 19:39:34 2022
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
SQL> Connected.
SQL>   2
Session altered.
 
SQL>   2
Session altered.
 
SQL>
ALTER SYSTEM KILL SESSION '394,48893' force timeout 0 -- process 5379
/
------------Output Trimmed------------
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL>
END_RUNNING
--------------------------------------------------------------------------------
==== @./utlrp.sql Container:labdb03 Id:0 22-02-10 07:41:52 Proc:0 ====
 
SQL>
END_RUNNING
--------------------------------------------------------------------------------
==== @./utlrp.sql Container:labdb03 Id:0 22-02-10 07:41:52 Proc:0 ====
 
SQL> SQL>
SQL> ========== PROCESS ENDED ==========
SQL> ========== Process Terminated by catcon ==========
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@DBsGuruN2 admin]$
 
SQL> select owner, count(*) from dba_objects where status <> 'VALID' group by owner;
 
no rows selected

3.5: Execute postupgrade_fixups.sql: Execute postupgrade_fixups.sql which was generated in step 1.1.

[oracle@DBsGuruN2 admin]$ $ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labdb03_upgrade/ -b labdb04_postfix /home/oracle/bkp/labdb03_upgrade/postupgrade_fixups.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/bkp/labdb03_upgrade/labdb04_postfix_catcon_6355.lst]
 
catcon::set_log_file_base_path: catcon: See [/home/oracle/bkp/labdb03_upgrade/labdb04_postfix*.log] files for output generated by scripts
 
catcon::set_log_file_base_path: catcon: See [/home/oracle/bkp/labdb03_upgrade/labdb04_postfix_*.lst] files for spool files, if any
 
catcon.pl: completed successfully
 
[oracle@DBsGuruN2 admin]$
[oracle@DBsGuruN2 admin]$ ls -lrt /home/oracle/bkp/labdb03_upgrade/labdb04_postfix*.log
-rw-------. 1 oracle oinstall 1340 Feb 10 19:53 /home/oracle/bkp/labdb03_upgrade/labdb04_postfix0.log
[oracle@DBsGuruN2 admin]$ cat /home/oracle/bkp/labdb03_upgrade/labdb04_postfix0.log
 
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 10 19:53:43 2022
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
SQL> Connected.
SQL>   2
Session altered.
 
SQL>   2
Session altered.
 
SQL>
ALTER SYSTEM KILL SESSION '394,49534' force timeout 0 -- process 6420
/
 
SQL> SQL>
SQL>   2
Session altered.
 
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2
CATCONSECTION
--------------------------
==== CATCON EXEC ROOT ====
 
SQL>
BEGIN_RUNNING
--------------------------------------------------------------------------------
==== @/home/oracle/bkp/labdb03_upgrade/postupgrade_fixups.sql Container:labdb03
Id:0 22-02-10 07:53:43 Proc:0 ====
 
 
SQL>
BEGIN_RUNNING
--------------------------------------------------------------------------------
==== @/home/oracle/bkp/labdb03_upgrade/postupgrade_fixups.sql Container:labdb03
Id:0 22-02-10 07:53:43 Proc:0 ====
 
 
SQL>   2
Session altered.
 
SQL>   2
Session altered.
 
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-08 18:02:01
 
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
------  ------------------------  ----------  --------------------------------
    5.  old_time_zones_exist      NO          Manual fixup recommended.
    6.  dir_symlinks              YES         None.
    7.  post_dictionary           YES         None.
    8.  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.
 
SQL>
END_RUNNING
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
==== @/home/oracle/bkp/labdb03_upgrade/postupgrade_fixups.sql Container:labdb03 Id:0 22-02-10 07:55:15 Proc:0 ====
 
SQL>
END_RUNNING
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
==== @/home/oracle/bkp/labdb03_upgrade/postupgrade_fixups.sql Container:labdb03 Id:0 22-02-10 07:55:15 Proc:0 ====
 
SQL> SQL>
SQL> ========== PROCESS ENDED ==========
SQL> ========== Process Terminated by catcon ==========
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

3.6: Post upgrade validation tool: Execute utlusts.sql to validate upgrade status and review the log file.

SQL> @$ORACLE_HOME/rdbms/admin/utlusts.sql
Enter value for 1:
 
Oracle Database Release 19 Post-Upgrade Status Tool    02-10-2022 19:59:1
Database Name: LABDB03
 
Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS
 
Oracle Server                             VALID      19.3.0.0.0  00:09:30
JServer JAVA Virtual Machine              VALID      19.3.0.0.0  00:01:27
Oracle XDK                                VALID      19.3.0.0.0  00:00:31
Oracle Database Java Packages             VALID      19.3.0.0.0  00:00:07
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:16
Oracle Text                               VALID      19.3.0.0.0  00:00:30
Oracle Workspace Manager                  VALID      19.3.0.0.0  00:00:28
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:23
Oracle Multimedia                         VALID      19.3.0.0.0  00:00:36
Spatial                                   VALID      19.3.0.0.0  00:03:40
Oracle OLAP API                           VALID      19.3.0.0.0  00:00:09
Datapatch                                                        00:03:08
Final Actions                                                    00:03:20
Post Upgrade                                                     00:00:27
Post Compile                                                     00:02:09
 
Total Upgrade Time: 00:25:50
 
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.

3.7: Upgrade Timezone: Require to upgrade DB timezone file version to 32. Prior to the upgrade, it was 26, refer to the post-upgrade tool log.

Execute utltz_upg_check.sql to validate the current RDBMS DST version and the newest RDBMS DST version.

SQL> @$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv26 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv32 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
 
Session altered.

Execute utltz_upg_apply.sql to upgrade DST version to 32.

SQL> @$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql
 
Session altered.
 
INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv32 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
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.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
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.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv32 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.
 
Session altered.
 
SQL>
 
SQL> COL PROPERTY_NAME FOR A25
SQL> COL PROPERTY_VALUE FOR A21
SQL> Select version from v$timezone_file;
 
   VERSION
----------
        32
 
1 row selected.

Optionally execute again post-upgrade status tool.

SQL> @$ORACLE_HOME/rdbms/admin/utlusts.sql
Enter value for 1:
 
Oracle Database Release 19 Post-Upgrade Status Tool    02-10-2022 20:09:2
Database Name: LABDB03
 
Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS
 
Oracle Server                             VALID      19.3.0.0.0  00:09:30
JServer JAVA Virtual Machine              VALID      19.3.0.0.0  00:01:27
Oracle XDK                                VALID      19.3.0.0.0  00:00:31
Oracle Database Java Packages             VALID      19.3.0.0.0  00:00:07
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:16
Oracle Text                               VALID      19.3.0.0.0  00:00:30
Oracle Workspace Manager                  VALID      19.3.0.0.0  00:00:28
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:23
Oracle Multimedia                         VALID      19.3.0.0.0  00:00:36
Spatial                                   VALID      19.3.0.0.0  00:03:40
Oracle OLAP API                           VALID      19.3.0.0.0  00:00:09
Datapatch                                                        00:03:08
Final Actions                                                    00:03:20
Post Upgrade                                                     00:00:27
Post Compile                                                     00:02:09
 
Total Upgrade Time: 00:25:50
 
Database time zone version is 32. It meets current release needs.

3.8: 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 PROD databases upgrade.

SQL> show parameter COMPATIBLE parameter;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.2.0
 
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 parameter;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      19.0.0

3.9: 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> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
 
NAME                      GUARANTEE_FLASHBACK_DATABASE    TIME
------------------------- ------------------------------- ---------------------------------------------------------------------------
PRE_UPGRADE_LABDB03       YES                             08-FEB-22 06.37.46.000000000 PM
 
SQL> drop restore point PRE_UPGRADE_LABDB03;
 
Restore point dropped.
 
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
 
no rows selected

3.10: RAC DB Action: Follow the below steps for the RAC database only.

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

3.10.2: Upgrade the Oracle Clusterware keys for the database.

[oracle@DBsGuruN2 admin]$ srvctl upgrade database -db <DB NAME> -o <ORACLE_HOME>

3.11: Listener Status: Validate the services for the database.

[oracle@DBsGuruN2 admin]$ lsnrctl status LISTENER_19C
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 10-FEB-2022 20:37:33
 
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                10-FEB-2022 20:35:26
Uptime                    0 days 0 hr. 2 min. 7 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

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.

Step by Step Manual Upgrade Oracle Database from 12c to 19c

DATABASE, ORACLE, ORACLE 19C, UPGRADE

3587 Total Views , 34 Views Today

DBsGuru

Hello and welcome to DBsGuru,

DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training.
Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.

Thanks for the visits!

Share Learn Grow!

dbsguru.com/

SQL MY SQL databases phone tablat compouter And TV

Rac

Step by Step Manual Upgrade Oracle Database from 12c to 19c

Step by Step Manual Upgrade Oracle Database from 12c to 19c

Related Articles

Leave a Reply

Hits by Community

Subscribe Our Newsletter

Achievements

Latest Ratings on Post

Recent Posts

Recent Comments

Archives

Categories

LIVE

إرسال تعليق

الإبلاغ عن إساءة الاستخدام

SQL MY SQL databases phone tablat computer And TV

من أنا

Send Whatsapp Query

Rac

Step by Step Manual Upgrade Oracle Database from 12c to 19c

Step by Step Manual Upgrade Oracle Database from 12c to 19c

Related Articles

Leave a Reply

Hits by Community

Subscribe Our Newsletter

Achievements

Latest Ratings on Post

Recent Posts

Recent Comments

Archives

Categories

Tags

LIVE

إرسال تعليق

الإبلاغ عن إساءة الاستخدام

SQL MY SQL databases phone tablat computer And TV

من أنا

Send Whatsapp Query