Upgrade Oracle 12.2.0.1 to Oracle Database 18c on-premises

Upgrade Oracle 12.2.0.1 to Oracle Database 18c on-premisesI’m currently refreshing our Hands-On Lab and will exchange Oracle 12.2.0.1 with Oracle 18.2.0. This blog post series is about how to Upgrade to Oracle 12.2.0.1 to Oracle Database 18c on-premises. There are other blog posts available you may use by yourself if you plan to refresh the lab by yourself until we uploaded a newer version. And it’s actually a good exercise.

For Multitenant upgrades from Oracle 12.1.0.2 to Oracle 12.2.0.1 you may read this:

Oracle Database 18c on-premises is supposed to be generally available with the July 2018 Update. For details please always check the single-source-of-truth MOS Note: 742060.1. Furthermore, please see my previous blog post from March 20, 2018, as well. And I’m very happy to see that some customers already moved to 18c.

Upgrade Oracle 12.2.0.1 to Oracle Database 18c on-premises

At first – even if you do the entire exercise in a different order, you will have to download the most recent preupgrade.jar from MOS Note:884522.1.Unpack it to any directory:

$ unzip preupgrade_181_cbuild_2_lf.zip 
Archive:  preupgrade_181_cbuild_2_lf.zip
  inflating: dbms_registry_extended.sql  
  inflating: preupgrade_driver.sql   
  inflating: preupgrade.jar          
  inflating: preupgrade_messages.properties  
  inflating: preupgrade_package.sql  

$ ls
dbms_registry_extended.sql      preupgrade.jar
preupgrade_181_cbuild_2_lf.zip  preupgrade_messages.properties
preupgrade_driver.sql           preupgrade_package.sql

Afterwards, execute preupgrade.jar. In my example I will do it for the CDB2 Multitenant database in the lab environment:

$ java -jar preupgrade.jar TEXT TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
18.0.0.0.0 on 2018-06-13T15:08:45

Upgrade-To version: 18.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  CDB2
     Container Name:  CDB$ROOT
       Container ID:  1
            Version:  12.2.0.1.0
         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         
  Oracle Label Security                  [to be upgraded]  VALID         
  Oracle XML Database                    [to be upgraded]  VALID         
  Oracle Java Packages                   [to be upgraded]  VALID         

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

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

  RECOMMENDED ACTIONS
  ===================
  1.  Run 12.2.0.1.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
      objects.  You can view the individual invalid objects with
      
        SET SERVEROUTPUT ON;
        EXECUTE DBMS_PREUP.INVALID_OBJECTS;
      
      3 objects are INVALID.
      
      There should be no INVALID objects in SYS/SYSTEM or user schemas before
      database upgrade.

  2.  Review and remove any unnecessary HIDDEN/UNDERSCORE parameters.
      
      The database contains the following initialization parameters whose name
      begins with an underscore:
      
      _exclude_seed_cdb_view
      
      Remove hidden parameters before database upgrade unless your application
      vendors and/or Oracle Support state differently.  Changes will need to be
      made in the pfile/spfile.

  3.  (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
  ================
  4.  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                             550 MB       616 MB
      SYSTEM                             700 MB      1117 MB
      TEMP                                22 MB       150 MB
      UNDOTBS1                           315 MB       433 MB
      
      Minimum tablespace sizes for upgrade are estimates.

  5.  No action needed.
      
      Using default parallel upgrade options, this CDB with 1 PDBs will first
      upgrade the CDB$ROOT, and then upgrade at most 1 PDBs at a time using 2
      parallel processes per PDB.
      
      The number of PDBs upgraded in parallel and the number of parallel
      processes per PDB can be adjusted as described in Database Upgrade Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB2 container CDB$ROOT
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL>@/u01/app/oracle/cfgtoollogs/CDB2/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 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.

  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 CDB2 container CDB$ROOT
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

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


Report generated by Oracle Database Pre-Upgrade Information Tool Version
18.0.0.0.0 on 2018-06-13T15:08:58

Upgrade-To version: 18.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  CDB2
     Container Name:  PDB$SEED
       Container ID:  2
            Version:  12.2.0.1.0
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  26
  Database log mode:  NOARCHIVELOG
           Readonly:  TRUE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID         
  Real Application Clusters              [to be upgraded]  OPTION OFF    
  Oracle Workspace Manager               [to be upgraded]  VALID         
  Oracle XML Database                    [to be upgraded]  VALID         

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

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

  RECOMMENDED ACTIONS
  ===================
  1.  Run 12.2.0.1.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
      objects.  You can view the individual invalid objects with
      
        SET SERVEROUTPUT ON;
        EXECUTE DBMS_PREUP.INVALID_OBJECTS;
      
      6 objects are INVALID.
      
      There should be no INVALID objects in SYS/SYSTEM or user schemas before
      database upgrade.

  2.  Review and remove any unnecessary HIDDEN/UNDERSCORE parameters.
      
      The database contains the following initialization parameters whose name
      begins with an underscore:
      
      _exclude_seed_cdb_view
      
      Remove hidden parameters before database upgrade unless your application
      vendors and/or Oracle Support state differently.  Changes will need to be
      made in the pfile/spfile.

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

  4.  (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
  ================
  5.  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                             235 MB       500 MB
      SYSTEM                             210 MB       584 MB
      TEMP                                20 MB       150 MB
      UNDOTBS1                           210 MB       412 MB
      
      Minimum tablespace sizes for upgrade are estimates.

  6.  No action needed.
      
      Using default parallel upgrade options, this CDB with 1 PDBs will first
      upgrade the CDB$ROOT, and then upgrade at most 1 PDBs at a time using 2
      parallel processes per PDB.
      
      The number of PDBs upgraded in parallel and the number of parallel
      processes per PDB can be adjusted as described in Database Upgrade Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB2 container PDB$SEED
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

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

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

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

  RECOMMENDED ACTIONS
  ===================
  7.  Upgrade the database time zone file using the DBMS_DST package.
      
      The database is using time zone file version 26 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.

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

  9.  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 CDB2 container PDB$SEED
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

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


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

Execute fixup scripts across the entire CDB:

Before upgrade:

1. Execute preupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/ -b preup_CDB2 /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/preupgrade_fixups.sql

2. Review logs under /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/

After the upgrade:

1. Execute postupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/ -b postup_CDB2 /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/postupgrade_fixups.sql

2. Review logs under /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/

Preupgrade complete: 2018-06-13T15:09:00

You may recognize that the script’s output is now better structured. And whenever it says AUTOFIXUP, there’s nothing to do for myself as this will be executed by the preupgrade_fixups.sql.

Execute the necessary fixups

The log of preupgrade.jar highlights that I have invalid objects I should recompile before upgrade:

  1.  Run 12.2.0.1.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
      objects.  You can view the individual invalid objects with
      
        SET SERVEROUTPUT ON;
        EXECUTE DBMS_PREUP.INVALID_OBJECTS;
      
      6 objects are INVALID.
      
      There should be no INVALID objects in SYS/SYSTEM or user schemas before
      database upgrade.

Therefore, I’m following the advice:

set serverout on

EXECUTE DBMS_PREUP.INVALID_OBJECTS;

SYS/SYSTEM INVALID OBJECTS
OWNER			      |OBJECT_NAME
|OBJECT_TYPE
--------------------------------------------------------------------------------
------------------------------------------------
NON SYS/SYSTEM INVALID OBJECTS
OWNER			      |OBJECT_NAME
|OBJECT_TYPE
--------------------------------------------------------------------------------
------------------------------------------------
PUBLIC			      LOCAL_CHUNKS
SYNONYM
PUBLIC			      LOCAL_CHUNK_TYPES
SYNONYM
PUBLIC			      SHA_DATABASES
SYNONYM

I clean it up with the recommended procedure and double-check afterwards:

start ?/rdbms/admin/utlrp

EXECUTE DBMS_PREUP.INVALID_OBJECTS;

PL/SQL procedure successfully completed.

“No results” means: everything is valid now.

Afterwards I kick of the preupgrade_fixups.sql and wait for its completion.

@/u01/app/oracle/cfgtoollogs/CDB2/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:            2018-06-13 15:08:42

For Source Database:     CDB2
Source Database Version: 12.2.0.1.0
For Upgrade to Version:  18.0.0.0.0

Executing in container:  CDB$ROOT

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    1.  invalid_objects_exist     YES         None.
    2.  hidden_params             NO          Informational only.
                                              Further action is optional.
    3.  dictionary_stats          YES         None.
    4.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
    5.  cycle_number              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.

The “PREUP ACTION NUMBER” match the enumeration from the output of preupgrade.jar. This will make your life easier.

My database is now ready for upgrade. I can shut it down.

create pfile from spfile;
shutdown immediate

If you don’t want the Hands-On Lab to grow a lot, it may be wise to deinstall the 12.2. software at this point, then install Oracle 18c, patch it and upgrade the database. Otherwise an additional Oracle Home will increase the size of the lab quite a bit.

To remove Oracle 12.2.0.1 at this point, follow this blog posts, then return afterwards and continue here.

  • Deinstalling Oracle Database 12.2.0.1 [Will be available on June 19, 2018]

As next step I will have to install Oracle Database 18c. Please follow this blog post:

  • Installing Oracle Database 18c [Will be available on June 20, 2018]

Finally I believe you always want to apply the most recent Update (RU) before you really start the upgrade. Find the details here:

  • Patch Oracle 18.1.0 to 18.2.0 [Will be available on June 21, 2018]

And once I completed all these actions I can go forward and upgrade my database(s).

Upgrade Oracle 12.2.0.1 to Oracle Database 18c on-premises

I kick off the upgrade as usual with catctl.pl:

$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 -l /home/oracle/log catupgrd.sql

And after a while, the database got upgraded (on very slow hardware in this case):

...
------------------------------------------------------

Grand Total Time: 2127s [PDB$SEED]

 LOG FILES: (/home/oracle/log/catupgrdpdb_seed*.log)

Upgrade Summary Report Located in:
/home/oracle/log/upg_summary.log

Total Upgrade Time:          [0d:0h:35m:27s]

     Time: 2284s For CDB$ROOT
     Time: 2133s For PDB(s)

Grand Total Time: 4417s 

 LOG FILES: (/home/oracle/log/catupgrd*.log)

Upgrade Summary Report Located in:
/home/oracle/log/upg_summary.log

Grand Total Upgrade Time:    [0d:1h:13m:37s]

Post-Upgrade Steps

Finally I will execute a few post-upgrade steps.

At first, I start the recompilation:

$ORACLE_HOME/perl/bin/perl catcon.pl -n 4 -e -b utlrp -d '''.''' utlrp.sql

And as the last step I execute the postupgrade_fixups.sql:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 4 -d /u01/app/oracle/cfgtoollogs/CDB2/preupgrade -l /home/oracle/log -b postupgrade_fixups postupgrade_fixups.sql

Final check:

 select count(*) from cdb_objects where status='INVALID';

  COUNT(*)
----------
	 0

That’s it. Done.

–Mike

 

6 thoughts on “Upgrade Oracle 12.2.0.1 to Oracle Database 18c on-premises

  1. Mike,

    What improvements have been made to the upgrade of the Oracle schema (i.e. catctl, dbua) to reduce the elapsed time and the corresponding downtime? This blog shows catctl being used and the elapsed time was 1h:13m:37s. The time was approximately 36 minutes for each container (i.e. cdb$root, pdb$seed) You did say the hardware was very slow, so the time may not be valid for comparison to prior upgrades.

    If it takes approximately the same type to upgrade each PDB/container in the database, would it be faster to create a new database using the new software release and plug the PDBs, other than cdb$root and pdb$seed, into the new database?

    Thank you, in advance.

    • Dean,

      yes, this is true. And a valid point.
      Single Tenant environments have the downside of running 3 upgrades for 1 database. And two of the phases sequentially, i.e. the CDB$ROOT first as a single upgrade, and then afterwards the PDB$SEED (which includes a forced recompilation) and the PDB containing data/application/users, both at least in parallel.

      Hence, your proposed solution (have a new CDB in the new environment, unplug PDB from env_old and plugin into env_new, then upgrade it) is the much faster approach as you have only one single upgrade.

      Cheers,
      Mike

  2. Mike,
    The 18c upgrade documentation has an option -T which puts the application tablespaces in read only mode. I tested an upgrade with this option (dbupgrade -T) and my upgrade just hung up. There was no entry in alert log, nothing in catupgrd*.log. Finally, after more than an hour, I killed the upgrade process and re-started without the -T option and upgrade finished in 25 minutes. When it hung up, this was the last entry in catupgrd0.log:

    Total Number of Phases: 109

    Number of Cpus = 28
    Database Name =
    DataBase Version = 11.2.0.4.0

    Log file directory = [/u01/app/oracle/product/18.0.0.0/dbhome_1/cfgtoollogs//upgrade20180713093129]

    Parallel SQL Process Count = 4
    Components in []
    Installed [CATALOG CATJAVA CATPROC JAVAVM ORDIM RAC XDB XML]
    Not Installed [APEX APS CONTEXT DV EM MGW ODM OLS OWM SDO WK XOQ]

    Have you had any issues with this option?

    Thanks,
    Arun

    • Arun,

      can you give me a list of your tablespaces please?
      Something such as:
      select tablespace_name, contents from dba_tablespaces order by 1;

      Can you check also which tablespaces have been taken READ ONLY?

      The option is meant to be used with a PARTIAL OFFLINE BACKUP only. But of course it shouldn’t get stuck.

      Thanks,
      Mike

  3. There are 189 tablespaces, PERMANENT, TEMP and UNDO. I cannot post the entire list here as it may not be permitted by our security folks. None of these are in READ ONLY mode. I did not see anything about PARTIAL OFFLINE BACKUP in the upgrade guide. Did I look in the wrong place?

    https://docs.oracle.com/en/database/oracle/oracle-database/18/upgrd/database-preparation-tasks-to-complete-before-upgrades.html#GUID-E10096D0-36E3-41AF-9381-D74089FC6E17

    Is guaranteed restore point a good way to rollback the upgrade if all goes south? This will work till the COMPATIBLE parameter is raised, and will protect against any catastrophic failures. If there are major issues after increasing COMPATIBLE, there is always the hot backup. We can do a DBPITR.

    Thanks for your help…
    Arun

Leave a Reply

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

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.