Oracle Database Upgrade 12c to 19c step by step

Oracle Database Upgrade: Oracle Database Upgrade 12c to 19c | dbatutorial

 In this article we have disscussion the steps for upgrade the single instance database 12c to 19c. Direct upgrade to 19c supported database versions are: 11.2.0.4, 12.1.0.2, 12.2.0.1, 18c. This tutorial we are going to show oracle database upgrade 12.2.0.1 to 19c.

Oracle database upgrade steps are as follow.

Step 1: Download and install oracle 19c software only 

Step 2: Run preupgrade.jar

Step 3: Before upgrade required actions

Step 4: Database upgradation required actions

Step 5: After upgrade required actions

==============================================================

Step 1: Download and install oracle 19c software only

==============================================================

Download the oracle database 19c software and upload downloaded zip file into the oracle database server.

# Create Oracle Home for 19c database using following command

mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1

unzip the 19c software file in 19c oracle home using following command

unzip LINUX.X64_193000_db_home.zip -d /u01/app/oracle/product/19.0.0/dbhome_1

[oracle@proddb dbhome_1]$ export ORACLE_SID=/u01/app/oracle/product/19.0.0/dbhome_1

[oracle@proddb dbhome_1]$ ./runInstaller











Oracle 19c software only installation completed successfully.

================================

Step 2: Run preupgrade.jar

================================

Run preupgrade.jar into the 19c Oracle Home. preupgrade.jar provide pre and post upgradation recommanded steps so we should carefully go through the recommanded actions. Set original oracle home (12c) and run preupgrade.jar from 19c home

export ORACLE_HOME=/u01/app/oracle/product/12.0.0/dbhome_1

export ORACLE_SID=TESTDB

# Run preupgrade.jar

[oracle@proddb ~]$ /u01/app/oracle/product/19.0.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT

Command Output:

Report generated by Oracle Database Pre-Upgrade Information Tool Version

19.0.0.0.0 Build: 1 on 2020-12-26T16:27:44

Upgrade-To version: 19.0.0.0.0

=======================================

Status of the database prior to upgrade

=======================================

      Database Name:  TESTDB

     Container Name:  TESTDB

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

  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                             460 MB       500 MB

      SYSTEM                             810 MB       918 MB

      TEMP                                32 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 TESTDB

  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by

  executing the following

    SQL>@/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/TESTDB/preupgrade/p

    reupgrade_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 TESTDB

  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by

  executing the following

    SQL>@/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/TESTDB/preupgrade/p

    ostupgrade_fixups.sql

==================

PREUPGRADE SUMMARY

==================

  /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade.log

 /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql

 /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/TESTDB/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups

@/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups

@/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/TESTDB/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2020-12-26T16:27:44


========================================

Step 3: Before Upgrade Required Actions

========================================

All of the issues in database TESTDB which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by executing the following

[oracle@proddb ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 26 16:56:11 2020

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/product/12.2.0/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql

Script Output:

Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script

                         Version: 19.0.0.0.0 Build: 1

Generated on:            2020-12-26 16:27:38

For Source Database:     TESTDB

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

==========================================

Step 4: Database Upgrade Required Actions

==========================================

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

cd $ORACLE_HOME/network/admin for 12c database home

[oracle@proddb admin]$ cp *.ora /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/

# Add this to $/u01/app/oracle/product/19.0.0/dbhome_1/network/admin//sqlnet.ora

# This should be temporary while you deal with old passwords.

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

# Switch to the 19c listener.

[oracle@proddb ~]$ lsnrctl stop

[oracle@proddb ~]$ export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1

[oracle@proddb ~]$ export PATH=${ORACLE_HOME}/bin:$PATH

[oracle@proddb ~]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-DEC-2020 17:13:28

@ Start the database using the 19c Oracle home, ready for the upgrade.

[oracle@proddb ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Dec 26 17:16:46 2020

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 1543500144 bytes

Fixed Size                  8896880 bytes

Variable Size             939524096 bytes

Database Buffers          587202560 bytes

Redo Buffers                7876608 bytes

Database mounted.

Database opened.

# You can run the upgrade from 19c ORACLE_HOME

[oracle@proddb ~]$ $ORACLE_HOME/bin/dbupgrade

Script Output:

Argument list for [/u01/app/oracle/product/19.0.0/dbhome_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 = 0

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/19.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0/dbhome_1]

/u01/app/oracle/product/19.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0/dbhome_1]

catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0/dbhome_1]

Analyzing file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20201226171944]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20201226171944/catupgrd_catcon_42108.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20201226171944/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20201226171944/catupgrd_*.lst] files for spool files, if any

Number of Cpus        = 4

Database Name         = TESTDB

DataBase Version      = 12.2.0.1.0

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/TESTDB/upgrade20201226172009/catupgrd_catcon_42108.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/TESTDB/upgrade20201226172009/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/TESTDB/upgrade20201226172009/catupgrd_*.lst] files for spool files, if any

Log file directory = [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/TESTDB/upgrade20201226172009]

Parallel SQL Process Count            = 4

Components in [TESTDB]

    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:[2020_12_26 17:20:35]

------------------------------------------------------

------------------------------------------------------

Phases [0-107]         End Time:[2020_12_26 18:27:37]

------------------------------------------------------

Grand Total Time: 4024s

 LOG FILES: (/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/TESTDB/upgrade20201226172009/catupgrd*.log)

Upgrade Summary Report Located in:

/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/TESTDB/upgrade20201226172009/upg_summary.log

Grand Total Upgrade Time:    [0d:1h:7m:4s]

========================================

Step 5: AFTER Upgrade Required Actions

=======================================

sql> shut immediate

sql>statup upgrade

SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID

-------------------- ---------- ----------

timezlrg_26.dat              26          0

# Begin upgrade to the latest timezone version.

SQL> SET SERVEROUTPUT ON

DECLARE

  l_tz_version PLS_INTEGER;

BEGIN

  l_tz_version := DBMS_DST.get_latest_timezone_version;

  DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);

  DBMS_DST.begin_upgrade(l_tz_version);

END;

/

SQL>   2    3    4    5    6    7    8    9

l_tz_version=32

An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

SQL> SHUTDOWN IMMEDIATE;


SQL> STARTUP;

ORACLE instance started.

Total System Global Area 1543500144 bytes

Fixed Size                  8896880 bytes

Variable Size            1157627904 bytes

Database Buffers          369098752 bytes

Redo Buffers                7876608 bytes

Database mounted.

Database opened.

-- Do upgradation (Check the upgradation status)

SQL> SET SERVEROUTPUT ON

DECLARE

  l_failures   PLS_INTEGER;

BEGIN

  DBMS_DST.upgrade_database(l_failures);

  DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);

  DBMS_DST.end_upgrade(l_failures);

  DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);

END;

/

SQL>   2    3    4    5    6    7    8    9

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"

Number of failures: 0

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"

Number of failures: 0

Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"

Number of failures: 0

Table list: "DVSYS"."SIMULATION_LOG$"

Number of failures: 0

Table list: "DVSYS"."AUDIT_TRAIL$"

Number of failures: 0

DBMS_DST.upgrade_database : l_failures=0

An upgrade window has been successfully ended.

DBMS_DST.end_upgrade : l_failures=0

PL/SQL procedure successfully completed.

# Check timezone for 19c which version will be 32

SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID

-------------------- ---------- ----------

timezlrg_32.dat              32          0

# Gather fixed object stats.

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

# POST Upgrade sql

SQL>@/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/TESTDB/preupgrade/postupgrade_fixups.sql

Script Output:

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-12-26 16:27:43

For Source Database:     TESTDB

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.

Database is upgraded 12c to 19c successfully. Lets enjoy 19c database. 

Done!

Post a Comment

0 Comments

3