Upgrade Oracle Database from 12.2.0.1 to 19c using DBUA

Upgrade Oracle Database from 12.2.0.1 to 19c using DBUA

Table of Contents
___________________________________________________________________________________________________

1. Environment

PRE-UPGRADE TASKS

2. Backup
3. Run pre-upgrade script
4. View Pre-upgrade log
5. Verify tablespace sizes for upgrade
6. Update INITIALIZATION PARAMETERS
7. Gather DICTIONARY STATS
8. Purge Recyclebin
9. Refresh MVs
10. Run preupgrade_fixups.sql
11. Verify archive log dest size
12. Create Flashback Guaranteed Restore Point

UPGRADE TASK

13. Run DBUA

POST-UPGRADE TASKS WHEN DBUA USING

14. Verify /etc/oratab
15. Verify Timezone version
16. Verify INVALID objects
17. Verify DBA_REGISTRY
18. Run postupgrade_fixups.sql
19. Drop Restore point
20. Update COMPATIBLE parameter

FOR YOUR INFORMATION ONLY

___________________________________________________________________________________________________


1. Environment

Hostname       		: RAC1.RAJASEKHAR.COM
Database Name  		: VFX
DB VERSION		: 12.2.0.1
CDB			: NON-CDB, Single Instance
DB Home Path 		: /u01/app/oracle/product/12.2.0/dbhome_1
Datafile Location 	: /u01/app/oracle/oradata/VFX

Target DB VERSION 	: 19c (19.4.0.0.0)
Target DB Path		: /u01/app/oracle/product/19.0.0/dbhome_1

Upgrade Method		: Database Upgrade Assistant


PRE-UPGRADE TASKS


2. Backup

** Database Backup  -- I have it.
** tnsnames.ora
** listener.ora
** sqlnet.ora
** pfile and spfile
** orapw file

DBUA adds the selected listener to the listener.ora file of the target Oracle home, and starts it.

DBUA removes the entry of the upgraded database from the old (source) listener.ora file.

DBUA reloads the listener.ora file in both the source and target Oracle Database environments.

[oracle@rac1 ~]$ cd /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/
[oracle@rac1 admin]$ cp -p listener.ora sqlnet.ora tnsnames.ora /u01/app/backup/
[oracle@rac1 admin]$ ls -ltr /u01/app/backup/*.ora
-rwxrwxr-x. 1 oracle oinstall 743 Jan 27 12:58 /u01/app/backup/listener.ora
-rwxrwxr-x. 1 oracle oinstall 202 Jan 27 12:59 /u01/app/backup/sqlnet.ora
-rwxrwxr-x. 1 oracle oinstall 816 Jan 27 13:02 /u01/app/backup/tnsnames.ora
[oracle@rac1 admin]$

[oracle@rac1 dbs]$ pwd
/u01/app/oracle/product/12.2.0/dbhome_1/dbs
[oracle@rac1 dbs]$ cp -p  spfileVFX.ora orapwVFX /u01/app/backup/


3. Run pre-upgrade script

. oraenv  (VFX)

/u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT

--- OR ---

/u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/preupgrade

[oracle@rac1 ~]$ mkdir -p /home/oracle/preupgrade
[oracle@rac1 ~]$

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [dell] ? VFX
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$
[oracle@rac1 ~]$ /u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/preupgrade
==================
PREUPGRADE SUMMARY
==================
  /home/oracle/preupgrade/preupgrade.log
  /home/oracle/preupgrade/preupgrade_fixups.sql
  /home/oracle/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@/home/oracle/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/home/oracle/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2020-01-27T16:04:10
[oracle@rac1 ~]$


4. View Preupgrade log

[oracle@rac1 ~]$ cat /home/oracle/preupgrade/preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-01-27T16:04:09

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  VFX
     Container Name:  VFX
       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                             470 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.  Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least
      4618 MB of archived logs.  Check alert log during the upgrade that there
      is no write error to the destination due to lack of disk space.

      Archiving cannot proceed if the archive log destination is full during
      upgrade.

      Archive Log Destination:
       Parameter    :  LOG_ARCHIVE_DEST_1
       Destination  :  /u01/app/archive/VFX

      The database has archiving enabled.  The upgrade process will need free
      disk space in the archive log destination(s) to generate archived logs to.

  5.  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 VFX
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/home/oracle/preupgrade/preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  6.  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.

  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 VFX
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/home/oracle/preupgrade/postupgrade_fixups.sql


[oracle@rac1 ~]$


5. Verify tablespace sizes for upgrade

** Tablespace Auto extend ON and max size also set, hence no action taken.

http://www.br8dba.com/asm-2/#tspace


TABLESPACE_NAME   AUT FILE_NAME                                 TOTAL_SPACE FREE_SPACE      Free%  MAX_SPACE
----------------- --- ----------------------------------------- ----------- ---------- ---------- ----------
SYSAUX            YES /u01/app/oracle/oradata/VFX/sysaux01.dbf          470         27       5.78 31.9999847
SYSTEM            YES /u01/app/oracle/oradata/VFX/system01.dbf          800          4        .49 31.9999847
UNDOTBS1          YES /u01/app/oracle/oradata/VFX/undotbs01.dbf          70         49      69.64 31.9999847
USERS             YES /u01/app/oracle/oradata/VFX/users01.dbf             5          4         80 31.9999847
*****************                                               ----------- ----------            ----------
sum                                                                    1345         84            127.999939

SQL>


6. Update INITIALIZATION PARAMETERS

In this test scenario, noting to update as per preupgrade.log. Hence no action taken.


7. Gather DICTIONARY STATS

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

SQL>


8. Purge Recyclebin

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

SQL>


9. Refresh MVs

SQL> declare
list_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
end;
/

PL/SQL procedure successfully completed.

SQL>


10. Run preupgrade_fixups.sql

It will run by DBUA, however we are executing manually before start upgrade.

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @/home/oracle/preupgrade/preupgrade_fixups.sql
SQL> REM
SQL> REM    Oracle PRE-Upgrade Fixup Script
SQL> REM
SQL> REM    Auto-Generated by:       Oracle Preupgrade Script
SQL> REM                             Version: 19.0.0.0.0 Build: 1
SQL> REM    Generated on:            2020-01-27 16:04:07
SQL> REM
SQL> REM    Source Database:         VFX
SQL> REM    Source Database Version: 12.2.0.1.0
SQL> REM    For Upgrade to Version:     19.0.0.0.0
SQL> REM
SQL>
SQL> REM
SQL> REM    Setup Environment
SQL> REM
SQL> SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 200;
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2020-01-27 16:04:07

For Source Database:     VFX
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
------  ------------------------  ----------  --------------------------------
    1.  dictionary_stats          YES         None.
    2.  pre_fixed_objects         YES         None.
    3.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
    4.  min_archive_dest_size     NO          Informational only.
                                              Further action is optional.
    5.  rman_recovery_version     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 is not ready
for upgrade.  To resolve the outstanding issues, start by reviewing
the preupgrade_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.

SQL>


11. Verify archive log dest size

*** Please verify free space on ALL LOG_ARCHIVE_DEST_ locations including ALL standby destinations

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/archive/VFX <-----
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
SQL>
SQL> !df -h /u01/app/archive/VFX
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        67G   27G   41G  41% /u01

SQL>


12. Create Flashback Guaranteed Restore Point

*** NO need to enable Flashback Database from 11.2.0.1 onwards
*** Database MUST be in Archive Log mode
*** MUST NOT change the compatible parameter to higher version

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO <-----

SQL> select name,open_mode,log_mode from v$database;

NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
VFX       READ WRITE           ARCHIVELOG <----

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.2.0 <----
noncdb_compatible                    boolean     FALSE
SQL>
SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL>
SQL>!mkdir -p /u01/app/oracle/fast_recovery_area/VFX

SQL> alter system set db_recovery_file_dest_size=10G;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

System altered.

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 10G
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL>

SQL> select * from V$restore_point;

no rows selected <------

SQL>

SQL> create restore point pre_upgrade guarantee flashback database;

Restore point created. <-----

SQL>

SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE          YES        27-JAN-20 05.26.20.000000000 PM

SQL>


UPGRADE TASK


13. Run DBUA

export DISPLAY=CLIENT_HOST_IP:0.0
example as below:
export DISPLAY=192.168.2.2:0.0

/u01/app/oracle/product/19.0.0/dbhome_1/bin/dbua

























Database upgrade has been completed successfully, and the database is ready to use.


POST-UPGRADE TASKS WHEN DBUA USING


14. Verify /etc/oratab

After upgrade, ORACLE_HOME location changed to new 19c ORACLE_HOME by DBUA

[oracle@rac1 ~]$ cat /etc/oratab | grep -i VFX
VFX:/u01/app/oracle/product/19.0.0/dbhome_1:N
[oracle@rac1 ~]$


15. Verify Timezone version

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        32 <-----

SQL>


16. Verify INVALID objects

SQL> select count(1) from dba_objects where status='INVALID';

  COUNT(1)
----------
         0 <----

SQL>


17. Verify DBA_REGISTRY

SQL> col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

COMP_ID    COMP_NAME                                VERSION         STATUS
---------- ---------------------------------------- --------------- --------------------------------------------
CATALOG    Oracle Database Catalog Views            19.0.0.0.0      VALID
CATPROC    Oracle Database Packages and Types       19.0.0.0.0      VALID
JAVAVM     JServer JAVA Virtual Machine             19.0.0.0.0      VALID
XML        Oracle XDK                               19.0.0.0.0      VALID
CATJAVA    Oracle Database Java Packages            19.0.0.0.0      VALID
APS        OLAP Analytic Workspace                  19.0.0.0.0      VALID
RAC        Oracle Real Application Clusters         19.0.0.0.0      OPTION OFF
XDB        Oracle XML Database                      19.0.0.0.0      VALID
OWM        Oracle Workspace Manager                 19.0.0.0.0      VALID
CONTEXT    Oracle Text                              19.0.0.0.0      VALID
ORDIM      Oracle Multimedia                        19.0.0.0.0      VALID
SDO        Spatial                                  19.0.0.0.0      VALID
XOQ        Oracle OLAP API                          19.0.0.0.0      VALID
OLS        Oracle Label Security                    19.0.0.0.0      VALID
DV         Oracle Database Vault                    19.0.0.0.0      VALID

15 rows selected.

SQL>


18. Run postupgrade_fixups.sql

*** This script alreday ran by DBUA under post-upgrade section. However i have executed again.

SQL> @/home/oracle/preupgrade/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:            2020-01-27 16:04:09

For Source Database:     VFX
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
------  ------------------------  ----------  --------------------------------
    6.  old_time_zones_exist      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>


19. Drop Restore point

SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE          YES        27-JAN-20 05.26.20.000000000 PM

SQL>
SQL> !ls -ltr /u01/app/oracle/fast_recovery_area/VFX/flashback
total 1433668
-rw-r-----. 1 oracle oinstall 209723392 Jan 27 17:55 o1_mf_h2xc5wy5_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 27 18:09 o1_mf_h2xc5zbo_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 27 18:12 o1_mf_h2xdwp08_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 27 18:30 o1_mf_h2xfq0wh_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 27 18:32 o1_mf_h2xfx790_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 27 18:32 o1_mf_h2xh1orn_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 27 19:06 o1_mf_h2xgxgwj_.flb

SQL>
SQL> drop restore point PRE_UPGRADE;

Restore point dropped. <-----

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

no rows selected <----

SQL>
SQL> !ls -ltr /u01/app/oracle/fast_recovery_area/VFX/flashback
total 0 <-----

SQL>


20. Update COMPATIBLE parameter

Warning: If the value of COMPATIBLE parameter is changed to 19.0.0 then if for some reasons database needs to be downgraded to 12.2.0.1 the DBA would not have any option other than export/import to downgrade the database. But if this parameter is left unchanged for sometime to see how the database performs after upgrade then it is very easy and fast to downgrade the database if for some reason it is required to be downgraded.

If you change COMPATIBLE you can directly drop your restore points as they are useless. You can’t use Flashback Database to restore point back across a compatibility change of your database.

SQL> show parameter COMPATIBLE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.2.0 <----
noncdb_compatible                    boolean     FALSE
SQL>

SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;

System altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup;
ORACLE instance started.

Total System Global Area 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size             922746880 bytes
Database Buffers          620756992 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter COMPATIBLE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      19.0.0 <----
noncdb_compatible                    boolean     FALSE
SQL>
SQL> select name,open_mode,version from v$database,v$instance;

NAME                 OPEN_MODE            VERSION
-------------------- -------------------- ---------------
VFX                  READ WRITE           19.0.0.0.0 <-----

SQL>

[oracle@rac1 ~]$ ps -ef | grep tns
root        15     2  0 12:32 ?        00:00:00 [netns]
oracle   18145     1  0 18:38 ?        00:00:00 /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr LISTENER_VFX -inherit
oracle   32707  4398  0 19:51 pts/0    00:00:00 grep --color=auto tns
[oracle@rac1 ~]$ lsnrctl status LISTENER_VFX

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 27-JAN-2020 19:51:50

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_VFX
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                27-JAN-2020 18:38:51
Uptime                    0 days 1 hr. 12 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener_vfx/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1621)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
Services Summary...
Service "VFX.rajasekhar.com" has 1 instance(s).
  Instance "VFX", status READY, has 1 handler(s) for this service...
Service "VFXXDB.rajasekhar.com" has 1 instance(s).
  Instance "VFX", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 ~]$


FOR YOUR INFORMATION ONLY

If local_listener set in INITIALIZATION parameter file, then please remove it before upgrade, after upgrade you can add it back manually. If you don’t remove, may get error while running Post upgrade steps DBUA. I have faced this issue while upgrade from 11g to 12c not in this test case. FYI ONLY


  
Found below error in upgrade log file...

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

16 thoughts on “Upgrade Oracle Database from 12.2.0.1 to 19c using DBUA

  1. I think this documentation is the best that I have seen online. I have a couple of questions:
    Do you copy all the files from the $ORACLE_HOME/network/admin to the new ORACLE_HOME or do the upgrade complete this step. Also how about the orpwXXXX file and the spfile?

    Csar

    1. Hi Cesr,

      If upgrade method is DBUA, it will copy Listener/TNS/Password file/init parameters to target ORACLE_HOME. No action required. However please verify manually both listener.ora and tnsnames.ora entries after upgrade completes. incase if you have any custom entries in listener.ora hope you need to add it manually.

      if upgrade method is Manual, then you need to take care of all the steps.

      Thank you,
      Rajasekhar

  2. Hi Rajasekhar,

    Could you please share similar upgrade document from oracle 12c to oracle 19c RAC environment on window 12 R2

  3. Thank you !
    I had the issue with the local_listener.
    I had to alter system set local_listener=”; to avoid the error and the let DBUA create a new listener.
    You’ve made my day:-)

    1. I had copied the files and it caused an error. I renamed them and was able to continue with the listener configuration steps. It created new files which did not have our local entries (encryption wallet, allow_logon_version_server) so I renamed the new files and then renamed the versions I had copied, updating the oracle home in the listener file. This was our TEST env. so for PROD, I will still copy the files but rename them. I hope the files truly get migrated with our local entries.

  4. Hi Rajasekhar,
    My DBUA failed from 11g to 19c upgrade .

    My question is post failure how we can proceed with this .

    If you give me some brief idea its will really help full .

Leave a Reply

Your email address will not be published. Required fields are marked *