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
• Data Pump export/import
• Oracle Streams
• Oracle GoldenGate

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

[oracle@standby dbs]$ export ORACLE_SID=upgr
[oracle@standby dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 21 20:02:00 2019

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2449473536 bytes
Fixed Size 8623640 bytes
Variable Size 654313960 bytes
Database Buffers 1778384896 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.

Ensure backup is complete before upgrade.

Empty Recycle bin.

Run Gather statistics to finish upgrade soon.

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

no rows selected


SQL> PURGE DBA_RECYCLEBIN ;

DBA_Recyclebin purged.


SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.


SQL> !

Stop the running 12c listener

lsnrctl stop LISTENER

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 12.2.0.1 database environment as SYS,

 Pre-upgrade checks :

Run the preupgrade tool .

[oracle@standby dbs]$ java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TEXT TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2019-03-21T20:07:45

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
Database Name:      UPGR
Container Name:     upgr
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:  NOARCHIVELOG
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 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
================
2. 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         490 MB     509 MB
SYSTEM         800 MB     914 MB
TEMP           131 MB     150 MB
UNDOTBS1       70 MB      439 MB

Minimum tablespace sizes for upgrade are estimates.

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

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

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

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

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

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

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

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

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


==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/cfgtoollogs/upgr/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/upgr/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/upgr/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/upgr/preupgrade/preupgrade_fixups.sql

After the upgrade:

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

Preupgrade complete: 2019-03-21T20:07:46

Run the preupgrade_fixup.sql

[oracle@standby dbs]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 21 20:13:03 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> @/u01/app/oracle/cfgtoollogs/upgr/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-21 20:07:39

For Source Database: UPGR
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.      pre_fixed_objects        YES            None.
2.      tablespaces_info         NO             Informational only.
                                            Further action is optional.
3.      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.

UPGRADE:

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

Now run the dbua utility from 12c oracle_home location.

[oracle@standby ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@standby ~]$ export ORACLE_SID=upgr
[oracle@standby ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@standby ~]$ 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 12c listener, accept the defaults, so the listener will be upgraded. If you prefer to manually define a new listener, you can do that now too and click the “Next” button

 

 

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/upgr/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: 2019-03-21 20:07:45

For Source Database: UPGR
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
------ ------------------------ ---------- ----------------------------
4.     old_time_zones_exist     YES                None.
5.     dir_symlinks             YES                None.
6.     post_dictionary          YES                None.
7.     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.

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         UPGR        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