Oracle 18c Database Upgrade From 11.2.0.4 to 18.3.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,

Backup 11g database using RMAN

rman target /

run
{
backup database plus archivelog;
backup current controlfile;
backup spfile;
}

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.
Stop the running 11g listener

lsnrctl stop LISTENER_NAME

Run preupgrade tool and fixups

Oracle strongly recommends that you run the Pre-Upgrade Information Tool before starting the upgrade with DBUA. Although DBUA runs the Pre-Upgrade Information Tool as part of the pre-requisite checks, it is good practice to run the tool ahead of time in order to analyze the database and take actions that can decrease downtime for upgrading.

Connect to 11.2.0.4 database environment as SYS,

 Pre-upgrade checks :
Run the preupgrade tool .
[oracle@ram ~]$ export ORACLE_SID=orcldb
[oracle@ram ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 12 19:49:03 2019

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 4893769728 bytes
Fixed Size 2261568 bytes
Variable Size 2717912512 bytes
Database Buffers 2164260864 bytes
Redo Buffers 9334784 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ram ~]$ java -jar /u01/app/oracle/product/18.3/rdbms/admin/preupgrade.jar TEXT TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
18.0.0.0.0 on 2019-02-12T19:49:51

Upgrade-To version: 18.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
Database Name:        ORCLDB
Container Name:       Not Applicable in Pre-12.1 database
Container ID:         Not Applicable in Pre-12.1 database
Version:              11.2.0.4.0
Compatible:           11.2.0.4.0
Blocksize:            8192
Platform:             Linux x86 64-bit
Timezone File:        14
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 
Oracle Workspace Manager             [to be upgraded]     VALID 
OLAP Analytic Workspace              [to be upgraded]     VALID 
Oracle Enterprise Manager Repository [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 
Expression Filter                    [to be upgraded]     VALID 
Rule Manager                         [to be upgraded]     VALID 
Oracle OLAP API                      [to be upgraded]     VALID

==============
BEFORE UPGRADE
==============

REQUIRED ACTIONS
================
1. Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 5082
MB. Check alert log during the upgrade to ensure there is remaining free
space available in the recovery area.

DB_RECOVERY_FILE_DEST_SIZE is set at 4182 MB. There is currently 4050 MB
of free space remaining, which may not be adequate for the upgrade.

Currently:
Fast recovery area : /u01/app/oracle 
Limit :              4182 MB 
Used :               132 MB 
Available :          4050 MB 

The database has archivelog mode enabled, and the upgrade process will
need free space to generate archived logs to the recovery area specified
by initialization parameter DB_RECOVERY_FILE_DEST. The logs generated
must not overflow the limit set by DB_RECOVERY_FILE_DEST_SIZE, as that
can cause the upgrade to not proceed.

RECOMMENDED ACTIONS
===================
2. Remove the EM repository.

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

Step 1: If database control is configured, stop EM Database Control,
using the following command

$> emctl stop dbconsole

Step 2: Connect to the database using the SYS account AS SYSDBA

SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql

Without the set echo and serveroutput commands, you will not be able to
follow the progress of the script.

The database has an Enterprise Manager Database Control repository.

Starting with Oracle Database 12c, the local Enterprise Manager Database
Control does not exist anymore. The repository will be removed from your
database during the upgrade. This step can be manually performed before
the upgrade to reduce downtime.

3. Remove OLAP Catalog by running the 11.2.0.4.0 SQL script
$ORACLE_HOME/olap/admin/catnoamd.sql script.

The OLAP Catalog component, AMD, exists in the database.

Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is
desupported and will be automatically marked as OPTION OFF during the
database upgrade if present. Oracle recommends removing OLAP Catalog
(OLAP AMD) before database upgrade. This step can be manually performed
before the upgrade to reduce downtime.

4. Upgrade Oracle Application Express (APEX) manually before the database
upgrade.

The database contains APEX version 3.2.1.00.12. Upgrade APEX to at least
version 5.1.3.00.05.

Starting with Oracle Database Release 18, APEX is not upgraded
automatically as part of the database upgrade. Refer to My Oracle Support
Note 1088970.1 for information about APEX installation and upgrades.

5. Directly grant ADMINISTER DATABASE TRIGGER privilege to the owner of the
trigger or drop and re-create the trigger with a user that was granted
directly with such. You can list those triggers using "SELECT OWNER,
TRIGGER_NAME FROM DBA_TRIGGERS WHERE BASE_OBJECT_TYPE=''DATABASE'' AND
OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE
PRIVILEGE=''ADMINISTER DATABASE TRIGGER'')"

There is one or more database triggers whose owner does not have the
right privilege on the database.

The creation of database triggers must be done by users granted with
ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted
directly.

6. (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 11.2.0.4
Oracle Database Performance Tuning Guide.

INFORMATION ONLY
================
7. 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                  510 MB          723 MB
SYSTEM                  740 MB          1180 MB
TEMP                    20 MB           150 MB
UNDOTBS1                30 MB           446 MB

Minimum tablespace sizes for upgrade are estimates.

ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database ORCLDB
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following

SQL>@/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/preupgrade_fixups.sql

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

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

RECOMMENDED ACTIONS
===================
8. Upgrade the database time zone file using the DBMS_DST package.

The database is using time zone file version 14 and the target 18.0.0.0.0
release ships with time zone file version 31.

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 18.0.0.0.0 Oracle Database
Globalization Support Guide.

9. (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.

10. 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 11.2.0.4
Oracle Database Performance Tuning Guide.

INFORMATION ONLY
================
11. Check the Oracle documentation for the identified components for their
specific upgrade procedure.

The database upgrade script will not upgrade the following Oracle
components: OLAP Catalog,OWB

The Oracle database upgrade script upgrades most, but not all Oracle
Database components that may be installed. Some components that are not
upgraded may have their own upgrade scripts, or they may be deprecated or
obsolete.

ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database ORCLDB
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following

SQL>@/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/postupgrade_fixups.sql


==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade log into the database and execute the preupgrade fixups
@/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2019-02-12T19:49:51
Run the preupgrade_fixup.sql
SQL> @/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 18.0.0.0.0 Build: 1
Generated on: 2019-02-12 19:49:43

For Source Database: ORCLDB
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 18.0.0.0.0

Preup                              Preupgrade
Action                             Issue Is
Number    Preupgrade Check Name    Remedied   Further DBA Action
------   ---------------------    ----------  -----------------------
1.       trgowner_no_admndbtrg      NO        Informational only.
                                            Further action is optional.
2.       pre_fixed_objects          YES       None.
3.       tablespaces_info           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.
UPGRADE:

At this stage, we are still connected to 11g database which is up and running.

Now run the dbua utility from 18c oracle_home location.

[oracle@ram ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@ram ~]$ export ORACLE_SID=orcldb
[oracle@ram ~]$ export ORACLE_HOME=/u01/app/oracle/product/18.0/db_1
[oracle@ram ~]$ cd /u01/app/oracle/product/18.3/db_1/bin
[oracle@ram bin]$ ./dbua

DBUA performs some of the checks before actually starting the database upgrade. Some of the checks can be done manually to reduce downtime for the upgrade.

DBUA provides below options:

– Upgrade timezone.

– Gather dictionary statistics before upgrade.

– Make user tablespaces read only.

– Take RMAN backup before upgrade.

– Create Restore Point for Database Flashback

– Restore database backup to rollback upgrade

– Option to execute Custom scripts before and after upgrade

– show the location of DBUA logs and Alert log files.

– Option to upgrade existing listener to 18.x home or create a new listener in 18.x target home.

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, accept the defaults, so the listener will be upgraded.

Select the desired management options, then click the “Next” button.

 

 

 

Post-upgrade checks:

Run “postupgrade_fixups.sql” to check post upgrade status.

SQL> @/u01/app/oracle/cfgtoollogs/orcldb/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: 18.0.0.0.0 Build: 1
Generated on: 2019-02-12 19:49:51

For Source Database: ORCLDB
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 18.0.0.0.0

Preup                              Preupgrade
Action                             Issue Is
Number   Preupgrade Check Name     Remedied      Further DBA Action
------   ---------------------     ----------    -------------------
8.      old_time_zones_exist       YES           None.
9.      post_dictionary            YES           None.
10.     post_fixed_objects         NO            Informational only.
                                          Further action is optional.
11.     upg_by_std_upgrd           NO            Informational only.
                                          Further action is optional.

The fixup scripts have been run and resovled 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.

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
------------      --------     -----------
18.0.0.0.0         ORCLDB       READ WRITE
SQL> SELECT * FROM v$timezone_file;

FILENAME              VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_31.dat        31           0

SQL> select TZ_VERSION from registry$database;

  TZ_VERSION
  ----------
      31

 

 

 

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 *