Oracle 19c Database Upgrade From 11.2.0.4 to 19.2.0.0 Using DBUA

Description:-

There are different ways of upgrading to the latest release of Oracle database and Oracle provides multiple methods to upgrade. Few are listed below:

  • Database Upgrade Assistant (DBUA)
  • Manual Upgrade
  • Transportable Tablespaces
  • Datapump export/import
  • Oracle Streams
  • Oracle GoldenGate

Here I am going to choose DBUA to upgrade my database,

DBUA provides a graphical user interface to guide you through the upgrade of Oracle Database.

Oracle Database Releases That Support Direct Upgrade

Review the supported options for direct upgrades to the latest Oracle Database release.
You can perform a direct upgrade to the new release from the following releases:
• 11.2.0.4
• 12.1.0.1 and 12.1.0.2
• 12.2.0.1

Steps for upgrade from 11.2.0.4 to 19.2.0.0 using DBUA :-

Presteps for db upgrade :-

Take RMAN full backups before upgrade.

rman target / 

run {
allocate channel test_backup_disk1 type disk format '/u01/backup/%d_%U';
backup incremental level 0 tag 'ORCL_BEFORE_UPG'  database;
backup format  tag 'ORCL_CONTROL_FILE' current controlfile;
release channel test_backup_disk1;
}

Ensure backup is complete before upgrade.

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

no rows selected

Empty Recycle bin.

SQL> PURGE DBA_RECYCLEBIN ;

DBA Recyclebin purged.

Run Gather statistics to finish upgrade soon.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

Remove EM Database Control :-

Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target 19.2.0.0.0 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.

[oracle@19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 14 20:56:29 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> @emremove.sql
old 70: IF (upper('&LOGGING') = 'VERBOSE')
new 70: IF (upper('VERBOSE') = 'VERBOSE')

PL/SQL procedure successfully completed.

Remove OLAP Catalog :-

[oracle@19c ~]$ cd $ORACLE_HOME/olap/admin/

SQL> @catnoamd.sql

Purge Recyclebin :-

SQL> purge recyclebin;

Recyclebin purged.

Run the preupgrade tool.

The preupgrade.jar Pre-Upgrade Information Tool is supplied with Oracle Database 19c.
This tool has reside in new oracle home.
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar

oracle@19c ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /u01/preupgrade/
==================
PREUPGRADE SUMMARY
==================
/u01/preupgrade/preupgrade.log
/u01/preupgrade/preupgrade_fixups.sql
/u01/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

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

After the upgrade:

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

Preupgrade complete: 2019-03-14T21:28:55

Run preupgrade fixups.sql

SQL> @/u01/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2019-03-14 21:28:51

For Source Database: UPG19C
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0

Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. parameter_min_val NO Manual fixup recommended.
2. em_present NO Manual fixup recommended.
3. amd_exists NO Manual fixup recommended.
4. apex_manual_upgrade NO Manual fixup recommended.
5. dictionary_stats YES None.
6. trgowner_no_admndbtrg YES None.
7. mv_refresh NO Informational only.
Further action is optional.
8. pre_fixed_objects YES None.
9. tablespaces_info NO Informational only.
Further action is optional.
10. exf_rul_exists NO Informational only.
Further action is optional.
11. 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.

Run DBUA upgrade Utility :

Now run the dbua utility from 19c oracle_home location.

[oracle@19c ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@19c ~]$ export ORACLE_SID=upg19c
[oracle@19c ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@19c ~]$ dbua
  • select the Upgrade Oracle Database option, then click the “Next” button.

  • If the prerequisite checks highlight any issues, take the appropriate action to fix the issues. If all the prerequisite checks are passed, click the “Next” button.

  • Amend the upgrade options if necessary, then click the “Next” button.

  • Select the recovery options for use in the event of an upgrade failure, then click the “Next” button.

  •  If the database is using the 11g listener and you need to upgraded, use existing listener or create new 19c listener

  • If you want configure EM database express enable it and provide port number or uncheck it.

Now the database upgrade is complete and the database is ready for normal use.

SQL> select VERSION,name,open_mode from v$instance,v$database;

VERSION    NAME   OPEN_MODE
---------  ------ ----------
19.0.0.0.0 UPG19C READ WRITE

SQL> SELECT * FROM v$timezone_file;

FILENAME             VERSION    CON_ID
-------------------- ---------- ----------
timezlrg_32.dat      32         0

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
32
Catch Me On:- Hariprasath Rajaram  Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba                       FB Group:https://www.facebook.com/groups/894402327369506/              FB Page: https://www.facebook.com/dbahariprasath/?                 Twitter: https://twitter.com/hariprasathdba

Leave a Reply

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