Issue with 2k db_block_size – ORA-1450 when upgrading from Oracle 12.1 to Oracle 12.2.0.1

Recently we got alerted by a customer and a colleague from Italy about an issue with the upgrade from Oracle 12.1.0.x to Oracle 12.2.0.1 if – and only if – your database got created with 2k block size.

Problem

If your database got created with 2k block size, and you attempt an upgrade from Oracle 12.1.0.x to Oracle 12.2.0.1 the upgrade – regardless of DBUA or catctl.pl – will fail with an ORA-1450: maximum key length (1478) exceeded.

Analysis

In the catupgrd0.log – regardless of using the DBUA or the command line upgrade with catctl.pl – you’ll get the following error pattern:

SQL> create unique index i_radm_pe1 on sys.radm_pe$(pe_name)
   2  /
create unique index i_radm_pe1 on sys.radm_pe$(pe_name)
                                      *
ERROR at line 1:
ORA-01450: maximum key length (1478) exceeded

SQL> create index i_radm_pe2 on sys.radm_pe$(pe_obj#, pe_name)
  2  /
create index i_radm_pe2 on sys.radm_pe$(pe_obj#, pe_name)
                               *
ERROR at line 1:
ORA-01450: maximum key length (1478) exceeded

Solution

Please apply the one-off patch 24714096/25976885 (backport on top of 12.2.0.1 for bug 24714096 (HIT ORA-01450 WHEN UPGRADE SI DB FROM 11204 TO 12.2 WITH DB_BLOCK_SIZE 2K).

You may verify your current database block size with this queries:

SQL> show parameter db_block_size

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_block_size			     integer	 8192


SQL> select TABLESPACE_NAME, BLOCK_SIZE from DBA_TABLESPACES order by 2;

TABLESPACE_NAME 	       BLOCK_SIZE
------------------------------ ----------
SYSTEM				     8192
SYSAUX				     8192
USERS				     8192
TEMP				     8192
UNDOTBS1			     8192

Sorry for the inconvenience – and thanks to Francesco for logging the issue and to Alessandro for alerting our team.

–Mike

Upgrade Everything at Once – Multitenant Upgrade from Oracle 12.1 to 12.2

I did blog about this topic a while back.

Upgrade PDBs – Everything At Once (Full CDB Upgrade)

Theory is nice but it’s a big difference when you do it the first time with a non-lab deployment at a customer with a real Multitenant database with almost 100 PDBs.Then you learn that neither the documentation nor my previous blog post contain all the necessary steps.

My test setup

I have a simple deployment of just 5 PDBs. But it will showcase with my limited compute capabilities how the process should work.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 PDB2 			  READ WRITE NO
	 5 PDB3 			  READ WRITE NO
	 6 PDB4 			  READ WRITE NO
	 7 PDB5 			  READ WRITE NO

Run preupgrade,jar

First of all download the newest version of preupgrade.jar from MOS Note:884522.1 and copy it to your system. Execute it with all PDBs in OPEN mode. It will generate consolidated logs and sql files. The files will be written into $ORACLE_BASE/cfgtoollogs/CDB1/preupgrade.

The output does look like:

$ java -jar /home/oracle/preupgrade.jar TEXT TERMINAL


Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0

Upgrade-To version: 12.2.0.1.0

=======================================
Status of the database prior to upgrade
=======================================

      Database Name:  CDB1
     Container Name:  CDB$ROOT
       Container ID:  1
            Version:  12.1.0.2.0
         Compatible:  12.1.0.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  18
  Database log mode:  NOARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

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


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

  Run /preupgrade_fixups_CDB_ROOT.sql to complete all
  of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.

  REQUIRED ACTIONS
  ================
   + Adjust TABLESPACE SIZES as needed.
                                                Auto      12.2.0.1.0
     Tablespace                        Size     Extend    Min Size    Action
     ----------                     ----------  --------  ----------  ------

     SYSAUX                             550 MB  ENABLED       647 MB  None
     SYSTEM                             700 MB  ENABLED       758 MB  None
     TEMP                                20 MB  ENABLED       150 MB  None
     UNDOTBS1                           475 MB  ENABLED       400 MB  None

     Note that 12.2.0.1.0 minimum sizes are estimates.
     If you plan to upgrade multiple pluggable databases concurrently,
     then you must ensure that the UNDO tablespace size is equal to at least
     the number of pluggable databases that you upgrade concurrently,
     multiplied by that minimum.  Failing to allocate sufficient space can
     cause the upgrade to fail.

   + Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.

     Parameter                         12.2.0.1.0 minimum
     ---------                         ------------------
     sga_target*                               2260729856

     * These minimum memory/pool sizes are recommended for the upgrade process

  RECOMMENDED ACTIONS
  ===================
   + (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.1.0.2
     Oracle Database SQL Tuning Guide.

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

  Run /postupgrade_fixups_CDB_ROOT.sql to complete all
  of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.

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

  RECOMMENDED ACTIONS
  ===================
   + Upgrade the database time zone version using the DBMS_DST package.

     The database is using timezone datafile version 18 and the target
     12.2.0.1.0 database ships with timezone datafile version 26.

     Oracle recommends using the most recent timezone data.  For further
     information, refer to My Oracle Support Note 1585343.1.

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


Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0

Upgrade-To version: 12.2.0.1.0

=======================================
Status of the database prior to upgrade
=======================================

      Database Name:  CDB1
     Container Name:  PDB$SEED
       Container ID:  2
            Version:  12.1.0.2.0
         Compatible:  12.1.0.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  18
  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 XML Database                    [to be upgraded]  VALID


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

  Run /preupgrade_fixups_PDB_SEED.sql to complete all
  of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.

  REQUIRED ACTIONS
  ================
   + Adjust TABLESPACE SIZES as needed.
                                                Auto      12.2.0.1.0
     Tablespace                        Size     Extend    Min Size    Action
     ----------                     ----------  --------  ----------  ------

     SYSAUX                             355 MB  ENABLED       542 MB  None
     SYSTEM                             210 MB  ENABLED       561 MB  None
     TEMP                                20 MB  ENABLED       150 MB  None

     Note that 12.2.0.1.0 minimum sizes are estimates.
     If you plan to upgrade multiple pluggable databases concurrently,
     then you must ensure that the UNDO tablespace size is equal to at least
     the number of pluggable databases that you upgrade concurrently,
     multiplied by that minimum.  Failing to allocate sufficient space can
     cause the upgrade to fail.

  RECOMMENDED ACTIONS
  ===================
   + (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.1.0.2
     Oracle Database SQL Tuning Guide.

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

  Run /postupgrade_fixups_PDB_SEED.sql to complete all
  of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.

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

  RECOMMENDED ACTIONS
  ===================
   + Upgrade the database time zone version using the DBMS_DST package.

     The database is using timezone datafile version 18 and the target
     12.2.0.1.0 database ships with timezone datafile version 26.

     Oracle recommends using the most recent timezone data.  For further
     information, refer to My Oracle Support Note 1585343.1.

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


Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0

Upgrade-To version: 12.2.0.1.0

=======================================
Status of the database prior to upgrade
=======================================

      Database Name:  CDB1
     Container Name:  PDB1
       Container ID:  3
            Version:  12.1.0.2.0
         Compatible:  12.1.0.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  18
  Database log mode:  NOARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

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

...

I cut off here as the information will repeated as my PDBs are all generated from the PDB$SEED right now. But it’s important to understand that the log and the sql files are consolidated together.

Important:
When you see the tag (AUTOFIXUP) that means “we’ll take care”. The action will be execute by either the preupgrade_fixups.sql or the postupgrade_fixups.sql. No action required from your side.

In $ORACLE_BASE/cfgtoollos/CDB1/preupgrade you’ll find also the files for each container in case you will need them.

-rw-r--r--. 1 oracle dba   3246 May  9 11:50 preupgrade_fixups_CDB_ROOT.sql
-rw-r--r--. 1 oracle dba   5534 May  9 11:50 postupgrade_fixups_CDB_ROOT.sql
-rw-r--r--. 1 oracle dba   4346 May  9 11:50 preupgrade_CDB_ROOT.log
-rw-r--r--. 1 oracle dba   3246 May  9 11:50 preupgrade_fixups_PDB_SEED.sql
-rw-r--r--. 1 oracle dba   5534 May  9 11:50 postupgrade_fixups_PDB_SEED.sql
-rw-r--r--. 1 oracle dba   3937 May  9 11:50 preupgrade_PDB_SEED.log
-rw-r--r--. 1 oracle dba   3230 May  9 11:50 preupgrade_fixups_PDB3.sql
-rw-r--r--. 1 oracle dba   5518 May  9 11:50 postupgrade_fixups_PDB3.sql
-rw-r--r--. 1 oracle dba   3926 May  9 11:50 preupgrade_PDB3.log
-rw-r--r--. 1 oracle dba   3230 May  9 11:50 preupgrade_fixups_PDB2.sql
-rw-r--r--. 1 oracle dba   5518 May  9 11:50 postupgrade_fixups_PDB2.sql
-rw-r--r--. 1 oracle dba   3926 May  9 11:50 preupgrade_PDB2.log
-rw-r--r--. 1 oracle dba   3230 May  9 11:50 preupgrade_fixups_PDB1.sql
-rw-r--r--. 1 oracle dba   5518 May  9 11:50 postupgrade_fixups_PDB1.sql
-rw-r--r--. 1 oracle dba   3926 May  9 11:50 preupgrade_PDB1.log
-rw-r--r--. 1 oracle dba   3230 May  9 11:50 preupgrade_fixups_PDB4.sql
-rw-r--r--. 1 oracle dba   5518 May  9 11:50 postupgrade_fixups_PDB4.sql
-rw-r--r--. 1 oracle dba   3926 May  9 11:50 preupgrade_PDB4.log
-rw-r--r--. 1 oracle dba   3230 May  9 11:50 preupgrade_fixups_PDB5.sql
-rw-r--r--. 1 oracle dba   5518 May  9 11:50 postupgrade_fixups_PDB5.sql
-rw-r--r--. 1 oracle dba   3926 May  9 11:50 preupgrade_PDB5.log
-rw-r--r--. 1 oracle dba  27913 May  9 11:50 preupgrade.log
-rw-r--r--. 1 oracle dba  12739 May  9 11:50 preupgrade_fixups.sql
-rw-r--r--. 1 oracle dba  19669 May  9 11:50 postupgrade_fixups.sql

Please don’t delete any of the files named written by preupgrade.jar as they all are necessary to progress the actions later on.

Pre Upgrade

From the above output the only real action I will have to do is to change the value of SGA_TARGET and then execute the preupgrade_fixups.sql There’s no need to execute it by each PDB as the preupgrade_fixups.sql is a consolidated file having all actions for each PDB.

SQL> alter system set sga_target=2500M scope=spfile;
System altered.

To execute preupgrade_fixups.sql now catcon.pl must be used – and (even though this is not documented in detail at the moment) you should leverage your compute power by specifying a parallel degree matching your CPU_COUNT. In my example I use -n 4 to have 4 workers execute the task.

$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \
   -n 4 \
   -d /u01/app/oracle/cfgtoollogs/CDB1/preupgrade \
   -l /home/oracle/mike \
   -b preupgrade_fixups \
  preupgrade_fixups.sql 
catcon: ALL catcon-related output will be written to /home/oracle/mike/preupgrade_fixups_catcon_22960.lst
catcon: See /home/oracle/mike/preupgrade_fixups*.log files for output generated by scripts
catcon: See /home/oracle/mike/preupgrade_fixups_*.lst files for spool files, if any
catcon.pl: completed successfull

Once this step has been completed successfully, I will SHUTDOWN IMMEDIATE the database, copy the spfile over, generate a new password file in the destination Oracle home and then progress the upgrade.

Multitenant database upgrade

Once I have copied the spfile and created a password file I can startup my database in UPGRADE mode and startup all PDBs in UPGRADE mode as well.

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 9 13:37:28 2017

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

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2634022912 bytes
Fixed Size		    8796240 bytes
Variable Size		  520095664 bytes
Database Buffers	 2097152000 bytes
Redo Buffers		    7979008 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open upgrade;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  MIGRATE    YES
	 3 PDB1 			  MIGRATE    YES
	 4 PDB2 			  MIGRATE    YES
	 5 PDB3 			  MIGRATE    YES
	 6 PDB4 			  MIGRATE    YES
	 7 PDB5 			  MIGRATE    YES

SQL> exit

Then I will kick-off the upgrade. You can leverage again the full CPU power of your system. In my case, as I have only two CPUs I will overload it a bit risking contention just to demonstrate some of the capabilities of catctl.pl.

The CDB$ROOT will be upgraded always first. With the -M option you can decide that it will stay for the entire upgrade, until the last PDB has been finished upgrading, in UPGRADE mode – or you let it on the default as I do meaning that the CDB$ROOT and each PDB become available as soon as they are upgraded. The -M option is suppose to generate a faster overall upgrade as it lowers potential contention in the dictionary of the CDB$ROOT. But you will see only a difference when you bring load to the database while PDBs get upgraded.

As I defined -n 6 here my CDB$ROOT will be upgraded by 6 workers. Once the PDBs get progressed, then 2 workers will take care on each PDB. And as -n 6 is set, 6 workers all together will be up meaning a maximum of 3 PDBs can be processed at one time.

Once a PDB has been upgraded the workers go on with the next accessible PDB based on the CON_ID. You can define also priority lists. Please see the documentation for further information.

$ cd /u01/app/oracle/product/12.2.0.1/rdbms/admin/
[CDB2] oracle@localhost:/u01/app/oracle/product/12.2.0.1/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l /home/oracle/mike catupgrd.sql

Argument list for [catctl.pl]
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 = /home/oracle/mike
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 6
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: [12.2.0.1.0]
           STATUS: [production]
            BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125]


/u01/app/oracle/product/12.2.0.1/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0.1]
/u01/app/oracle/product/12.2.0.1/bin/orabasehome = [/u01/app/oracle/product/12.2.0.1]
catctlGetOrabase = [/u01/app/oracle/product/12.2.0.1]

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

Log file directory = [/home/oracle/mike]

catcon: ALL catcon-related output will be written to [/home/oracle/mike/catupgrd_catcon_9755.lst]
catcon: See [/home/oracle/mike/catupgrd*.log] files for output generated by scripts
catcon: See [/home/oracle/mike/catupgrd_*.lst] files for spool files, if any

Number of Cpus        = 2
Database Name         = CDB1
DataBase Version      = 12.1.0.2.0
Parallel SQL Process Count (PDB)      = 2
Parallel SQL Process Count (CDB$ROOT) = 6
Concurrent PDB Upgrades               = 3
Generated PDB Inclusion:[PDB$SEED PDB1 PDB2 PDB3 PDB4 PDB5]
Components in [CDB$ROOT]
    Installed [CATALOG CATPROC XDB]
Not Installed [APEX APS CATJAVA CONTEXT DV EM JAVAVM MGW ODM OLS ORDIM OWM RAC SDO WK XML XOQ]

------------------------------------------------------
Phases [0-115]         Start Time:[2017_05_09 13:40:30]
Container Lists Inclusion:[CDB$ROOT] Exclusion:[NONE]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [CDB$ROOT] Files:1    Time: 33s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [CDB$ROOT] Files:5    Time: 55s
Restart  Phase #:2    [CDB$ROOT] Files:1    Time: 0s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [CDB$ROOT] Files:19

While the upgrade is running you won’t see a correct ordered output anymore as there’s no guarantee that each PDB will get upgrade at time X in phase Y. Especially when you have many PDBs of different sizes and contents you may see PDB1 in phase 49 whereas PDB2 is already in phase 109.

***************   Catproc DataPump   ***************
Serial   Phase #:40   [PDB1] Files:3    Time: 5s
Restart  Phase #:39   [PDB$SEED] Files:1    Time: 1s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [PDB$SEED] Files:3    Time: 1s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [PDB2] Files:3    Time: 103s
Restart  Phase #:41   [PDB2] Files:1    Time: 1s
******************   Catproc SQL   *****************
Parallel Phase #:42   [PDB2] Files:13    Time: 104s
Restart  Phase #:41   [PDB$SEED] Files:1    Time: 0s
******************   Catproc SQL   *****************
Parallel Phase #:42   [PDB$SEED] Files:13    Time: 105s
Restart  Phase #:41   [PDB1] Files:1    Time: 1s
******************   Catproc SQL   *****************

After the upgrade is finished, the consolidated upg_summary.log will tell you all the details about duration of each upgrade for each PDB. You may recognize that the PDB$SEED will take always longer than any other upgrade. This is because the PDB$SEED gets recompiled as part of the upgrade already as it is untouchable. All other containers will need to get recompiled after the upgrade is finished.

Post Upgrade

Final steps are now the recompilation and the postupgrade_fixups.sql.

The status of my database after upgrade and before recompilation:

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 9 16:11:51 2017

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


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

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  MOUNTED
	 4 PDB2 			  MOUNTED
	 5 PDB3 			  MOUNTED
	 6 PDB4 			  MOUNTED
	 7 PDB5 			  MOUNTED
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 PDB2 			  READ WRITE NO
	 5 PDB3 			  READ WRITE NO
	 6 PDB4 			  READ WRITE NO
	 7 PDB5 			  READ WRITE NO

That does look good at first sight. But it isn’t actualy.
If you check the component’s STATUS in CDB_REGISTRY you’ll see that it is UPGRADED – thus it is not finished yet.

SQL> alter session set "_exclude_seed_cdb_view"=false;

Session altered.

SQL>  select con_id, comp_id, status from cdb_registry order by 1,2;

    CON_ID COMP_ID    STATUS
---------- ---------- ----------
	 1 CATALOG    UPGRADED
	 1 CATPROC    UPGRADED
	 1 RAC	      UPGRADED
	 1 XDB	      UPGRADED
	 2 CATALOG    VALID
	 2 CATPROC    VALID
	 2 RAC	      OPTION OFF
	 2 XDB	      VALID
	 3 CATALOG    UPGRADED
	 3 CATPROC    UPGRADED
	 3 RAC	      UPGRADED
	 3 XDB	      UPGRADED
	 4 CATALOG    UPGRADED
	 4 CATPROC    UPGRADED
	 4 RAC	      UPGRADED
	 4 XDB	      UPGRADED
	 5 CATALOG    UPGRADED
	 5 CATPROC    UPGRADED
	 5 RAC	      UPGRADED
	 5 XDB	      UPGRADED
	 6 CATALOG    UPGRADED
	 6 CATPROC    UPGRADED
	 6 RAC	      UPGRADED
	 6 XDB	      UPGRADED
	 7 CATALOG    UPGRADED
	 7 CATPROC    UPGRADED
	 7 RAC	      UPGRADED
	 7 XDB	      UPGRADED

The recompilation is done with utlrp.sql but need to be executed with catcon.pl.

[CDB2] oracle@localhost:/u01/app/oracle/product/12.2.0.1/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0.1/rdbms/admin/utlrp_catcon_5538.lst]
catcon: See [/u01/app/oracle/product/12.2.0.1/rdbms/admin/utlrp*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0.1/rdbms/admin/utlrp_*.lst] files for spool files, if any
catcon.pl: completed successfully

Afterwards check if all the PDBs are OPEN UNRESTRICTED now.

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 9 20:02:13 2017

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


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

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 PDB2 			  READ WRITE NO
	 5 PDB3 			  READ WRITE NO
	 6 PDB4 			  READ WRITE NO
	 7 PDB5 			  READ WRITE NO

As final step the postupgrade_fixups.sql needs to be executed:

$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 4 -d /u01/app/oracle/cfgtoollogs/CDB1/preupgrade -l /home/oracle/mike -b postupgrade_fixups postupgrade_fixups.sql 
catcon: ALL catcon-related output will be written to [/home/oracle/mike/postupgrade_fixups_catcon_28596.lst]
catcon: See [/home/oracle/mike/postupgrade_fixups*.log] files for output generated by scripts
catcon: See [/home/oracle/mike/postupgrade_fixups_*.lst] files for spool files, if any
catcon.pl: completed successfully
[CDB2] oracle@localhost:/u01/app/oracle/product/12.2.0.1/rdbms/admin

And a final check:

SQL> select con_id, owner, object_type, object_name from cdb_objects where status='INVALID' order by 1;
no rows selected

All fine – database is upgraded. Final action could be to adjust the time zone but regarding this topic I may produce another blog post soon.

Please note that with Oracle Database 12.2 we offer the “dbupgrade” utility as well which simplifies the call to catctl.pl. See the documentation and Mouhamadou Diaw’s blog post.

–Mike

Multiple hop upgrades? Execute the matching preupgrade scripts for each hop

We discussed an interesting upgrade case last week together with our upgrade colleagues in Support – and learned the double-hop (or triple-hop) upgrade case is not described in the documentation somewhere.

What is a multiple-hop upgrade?

Triple Jump

Triple Jump – Willie Banks – Olympics 1988 Seoul

Actually this describes the case where somebody can’t upgrade directly and has to do several upgrades in a sequence to reach the targeted release. For example, you start of with an Oracle 10.2.0.3 database – and your targeted release is Oracle 12.2.0.1 . A direct upgrade is not possible. I would opt now for Data Pump or TTS as you could jump directly. But lets assume Data Pump would take too long because your database is too big – and TTS would require too many steps and cause you more work than sequential upgrades.

Therefore you’d move from 10.2.0.3 to 11.2.0.4 directly. And then further in a second upgrade step to 12.2.0.1. Upgrades from 10.2.0.3 to 11.2.0.4 were supported as they are from 11.2.0.4 to 12.2.0.1. I’d call this a double-hop upgrade.

We have even seen cases with triple-hops needed, especially when one is starting off with an early Oracle 9.2 database. And the complication often is caused by the OS certification as such techniques often involve also a move of the database to a temporary server.

What has happened in this particular customer case?

In the case we’ve discussed last week internally the customer wanted to step from Oracle 10.2.0.5 via Oracle 11.2.0.4 to Oracle 12.1.0.2. Please don’t ask me why they did this intermediate (and unnecessary) step into 11.2.0.4 as direct upgrades from 10.2.0.5 to 12.1.0.2 are supported. But they ended with a situation where the catctl.pl tool raised an ORA-01722: invalid number SELECT TO_NUMBER(‘INVALID_TABLEDATA’)  error and quit the upgrade from Oracle 11.2.0.4 to Oracle 12.1.0.2 before it even started. At this stage, there’s no way back into 10.2.0.5 as COMPATIBLE had been changed – and there’s no step forward possible as we don’t allow you to upgrade because of invalid table data which needed to be cleaned up before approaching the upgrade.

Only solution: restore your 10.2.0.5 backup and start off again.

Solution: Run the matching preupgrade scripts per step

The correct path would be to run the matching preupgrade scripts for each step you’d approach. This is not documented and my colleague Eric filed a doc bug for it so we’ll get it documented soon. As in both scenarios above the steps have to be seen as independent upgrades. For the upgrade from Oracle 10.2.0.5 to Oracle 11.2.0.4 the utlu112i.sql script had to be run prior to the upgrade. And once this upgrade is completed, preuprd.sql need to be run as the second step is an upgrade from Oracle 11.2.0.4 to Oracle 12.1.0.2.

Please note:
This is only necessary if you do multiple hops. If the customer here would have gone directly from Oracle 10.2.0.5 to Oracle 12.1.0.2 (which is supported) then ONLY the preupgrd.sql would have to be executed prior to the upgrade.

Always download the most recent preupgrade scripts

In any case, please remember to always download the most recent preupgrade script(s) from MOS Note:884522.1, regardless of upgrade to Oracle 11.2, 12.1 or 12.2 – and please use always the matching script.

MOS Note:884522.1 - Preupgrade scripts

MOS Note:884522.1 – Always get the newest preupgrade script

Simple reason why we ask you to download it:
We have included a lot of enhancements, fixes and improvements over the version you get with the base release or the patch set. And as the scripts are not part of PSUs and BPs at the moment (we are working on it) you should get the most recent one to benefit from all the changes we’ve done.

–Mike

 

Upgrade to Oracle Database 12.2 and Converting it into an 12.2 PDB (plugin)

2 customers requested a bit more explanation on how to:
Convert and Plugin an Oracle 12.1 non-CDB database into an Oracle 12.2 container database

Cool, I like that 🙂 I will do this right away and use the approach to Upgrade first, then Plugin afterwards.

Upgrading an Oracle 12.1.0.2 database to Oracle 12.2.0.1

First the preupgrade.jar must be executed – please download always the most recent version from MOS Note:884522.1 as the version we publish is usually newer including important improvements (and fixes) than the version you’ll get with the release drop. We are working on including always the newest drop with future PSUs and BPs.

$ java -jar /u01/app/oracle/product/12.2.0.1/rdbms/admin/preupgrade.jar TEXT TERMINAL

Report generated by Oracle Database Pre-Upgrade Information Tool Version 12.2.0.1.0
Upgrade-To version: 12.2.0.1.0

=======================================
Status of the database prior to upgrade
=======================================

Database Name:  DB12
Container Name:  DB12
Container ID:  0
Version:  12.1.0.2.0
Compatible:  12.1.0.2.0
Blocksize:  8192
Platform:  Linux x86 64-bit
Timezone File:  18
Database log mode: NOARCHIVELOG
Readonly:  FALSE
Edition:  EE

Oracle Component                       Upgrade Action    Current Status
----------------                       --------------    --------------
Oracle Server                          [to be upgraded]  VALID
Oracle Workspace Manager               [to be upgraded]  VALID
Oracle XML Database                    [to be upgraded]  VALID

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

Run /preupgrade_fixups.sql to complete all
of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.

REQUIRED ACTIONS
================
+ Adjust TABLESPACE SIZES as needed.
Auto      12.2.0.1.0
Tablespace                        Size     Extend    Min Size    Action
----------                     ----------  --------  ----------  ------
SYSAUX                             550 MB  DISABLED      500 MB  None
SYSTEM                             700 MB  ENABLED       765 MB  None
TEMP                                20 MB  ENABLED       150 MB  None
UNDOTBS1                           295 MB  ENABLED       400 MB  None

Note that 12.2.0.1.0 minimum sizes are estimates.
If you plan to upgrade multiple pluggable databases concurrently,
then you must ensure that the UNDO tablespace size is equal to at least
the number of pluggable databases that you upgrade concurrently,
multiplied by that minimum.  Failing to allocate sufficient space can
cause the upgrade to fail.

RECOMMENDED ACTIONS
===================
+ (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.1.0.2
Oracle Database SQL Tuning Guide.

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

Run /postupgrade_fixups.sql to complete all
of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.

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

RECOMMENDED ACTIONS
===================
+ Upgrade the database time zone version using the DBMS_DST package.

The database is using timezone datafile version 18 and the target
12.2.0.1.0 database ships with timezone datafile version 26.

Oracle recommends using the most recent timezone data.  For further
information, refer to My Oracle Support Note 1585343.1.

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

Preupgrade generated files:
/u01/app/oracle/cfgtoollogs/DB12/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/DB12/preupgrade/postupgrade_fixups.sql

Next step is to follow the guidelines displayed by the output, i.e. changing parameters, gathering stats, running the preupgrade_fixups.sql etc., then shutting down the database and copying the SPFILE and creating a new password file in the 12.2 Oracle Home’s directory.

[oracle@localhost ~]$ . db121
[DB12] oracle@localhost:~
$ s
SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 7 14:09:25 2017Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

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

Auto-Generated by:       Oracle Preupgrade Script
Version: 12.2.0.1.0 Build: 3
Generated on:            2017-03-07 13:46:51

For Source Database:     DB12
Source Database Version: 12.1.0.2.0
For Upgrade to Version:  12.2.0.1.0

Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
dictionary_stats          Passed  None

PL/SQL procedure successfully completed.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[DB12] oracle@localhost:~
$ cp /u01/app/oracle/product/12.1.0.2/dbs/spfileDB12.ora $OH12/dbs
[DB12] oracle@localhost:~
$ cp /u01/app/oracle/product/12.1.0.2/dbs/orapwDB12 $OH12/dbs
[DB12] oracle@localhost:~

-- it's Best Practice to create a new password file instead of copying the old one!

Now the database is ready to get upgraded to Oracle Database 12.2.0.1.

Database Upgrade to Oracle Database 12.2.0.1

Database must be in STARTUP UPGRADE mode – and the upgrade gets executed from the command line in my case. Alternative would be the DBUA of course. You could also use the shortcut “dbupgrade” instead of typing all the characters below.

[DB12] oracle@localhost:~
$ . db122
[DB12] oracle@localhost:~
$ s
SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 7 14:16:20 2017Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1048576000 bytes
Fixed Size                  8628640 bytes
Variable Size             369100384 bytes
Database Buffers          662700032 bytes
Redo Buffers                8146944 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[DB12] oracle@localhost:~

$ cd /u01/app/oracle/product/12.2.0.1/rdbms/admin/
[DB12] oracle@localhost:/u01/app/oracle/product/12.2.0.1/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l /home/oracle catupgrd.sql

Argument list for [catctl.pl]
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 = /home/oracle
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 6
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: [12.2.0.1.0]
STATUS: [production]
BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170107]

/u01/app/oracle/product/12.2.0.1/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0.1]
/u01/app/oracle/product/12.2.0.1/bin/orabasehome = [/u01/app/oracle/product/12.2.0.1]
catctlGetOrabase = [/u01/app/oracle/product/12.2.0.1]

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

Log file directory = [/home/oracle]

catcon: ALL catcon-related output will be written to [/home/oracle/catupgrd_catcon_13547.lst]
catcon: See [/home/oracle/catupgrd*.log] files for output generated by scripts
catcon: See [/home/oracle/catupgrd_*.lst] files for spool files, if any

Number of Cpus        = 2
Database Name         = DB12
DataBase Version      = 12.1.0.2.0
Parallel SQL Process Count            = 6
Components in [DB12]
Installed [CATALOG CATPROC OWM XDB]
Not Installed [APEX APS CATJAVA CONTEXT DV EM JAVAVM MGW ODM OLS ORDIM RAC SDO WK XML XOQ]

------------------------------------------------------
Phases [0-115]         Start Time:[2017_03_07 14:17:11]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [DB12] Files:1    Time: 29s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [DB12] Files:5    Time: 43s
Restart  Phase #:2    [DB12] Files:1    Time: 1s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [DB12] Files:19   Time: 22s
Restart  Phase #:4    [DB12] Files:1    Time: 0s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [DB12] Files:6    Time: 16s
*****************   Catproc Start   ****************
Serial   Phase #:6    [DB12] Files:1    Time: 12s
*****************   Catproc Types   ****************
Serial   Phase #:7    [DB12] Files:2    Time: 11s
Restart  Phase #:8    [DB12] Files:1    Time: 0s
****************   Catproc Tables   ****************
Parallel Phase #:9    [DB12] Files:69   Time: 38s
Restart  Phase #:10   [DB12] Files:1    Time: 0s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [DB12] Files:1    Time: 37s
Restart  Phase #:12   [DB12] Files:1    Time: 0s
**************   Catproc Procedures   **************
Parallel Phase #:13   [DB12] Files:97   Time: 12s
Restart  Phase #:14   [DB12] Files:1    Time: 1s
Parallel Phase #:15   [DB12] Files:118  Time: 21s
Restart  Phase #:16   [DB12] Files:1    Time: 1s
Serial   Phase #:17   [DB12] Files:13   Time: 2s
Restart  Phase #:18   [DB12] Files:1    Time: 0s
*****************   Catproc Views   ****************
Parallel Phase #:19   [DB12] Files:33   Time: 31s
Restart  Phase #:20   [DB12] Files:1    Time: 0s
Serial   Phase #:21   [DB12] Files:3    Time: 7s
Restart  Phase #:22   [DB12] Files:1    Time: 0s
Parallel Phase #:23   [DB12] Files:24   Time: 117s
Restart  Phase #:24   [DB12] Files:1    Time: 0s
Parallel Phase #:25   [DB12] Files:11   Time: 67s
Restart  Phase #:26   [DB12] Files:1    Time: 0s
Serial   Phase #:27   [DB12] Files:1    Time: 0s
Serial   Phase #:28   [DB12] Files:3    Time: 3s
Serial   Phase #:29   [DB12] Files:1    Time: 0s
Restart  Phase #:30   [DB12] Files:1    Time: 0s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [DB12] Files:1    Time: 1s
Restart  Phase #:32   [DB12] Files:1    Time: 0s
Serial   Phase #:34   [DB12] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [DB12] Files:283  Time: 18s
Serial   Phase #:36   [DB12] Files:1    Time: 0s
Restart  Phase #:37   [DB12] Files:1    Time: 0s
Serial   Phase #:38   [DB12] Files:1    Time: 5s
Restart  Phase #:39   [DB12] Files:1    Time: 0s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [DB12] Files:3    Time: 48s
Restart Phase #:41   [DB12] Files:1    Time: 1s
******************   Catproc SQL   *****************
Parallel Phase #:42   [DB12] Files:13   Time: 69s
Restart  Phase #:43   [DB12] Files:1    Time: 0s
Parallel Phase #:44   [DB12] Files:12   Time: 12s
Restart  Phase #:45   [DB12] Files:1    Time: 1s
Parallel Phase #:46   [DB12] Files:2    Time: 0s
Restart  Phase #:47   [DB12] Files:1    Time: 0s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [DB12] Files:1    Time: 4s
Restart  Phase #:49   [DB12] Files:1    Time: 0s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [DB12] Files:1    Time: 20s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [DB12] Files:1    Time: 0s
Restart  Phase #:52   [DB12] Files:1    Time: 0s
****************   Upgrading Java   ****************
Serial   Phase #:53   [DB12] Files:1    Time: 1s
Restart  Phase #:54   [DB12] Files:1    Time: 0s
*****************   Upgrading XDK   ****************
Serial   Phase #:55   [DB12] Files:1    Time: 0s
Restart  Phase #:56   [DB12] Files:1    Time: 1s
*********   Upgrading APS,OLS,DV,CONTEXT   *********
Serial   Phase #:57   [DB12] Files:1    Time: 0s
*****************   Upgrading XDB   ****************
Restart  Phase #:58   [DB12] Files:1    Time: 0s
Serial   Phase #:60   [DB12] Files:3    Time: 8s
Serial   Phase #:61   [DB12] Files:3    Time: 5s
Parallel Phase #:62   [DB12] Files:9    Time: 3s
Parallel Phase #:63   [DB12] Files:24   Time: 4s
Serial   Phase #:64   [DB12] Files:4    Time: 6s
Serial   Phase #:65   [DB12] Files:1    Time: 0s
Serial   Phase #:66   [DB12] Files:30   Time: 3s
Serial   Phase #:67   [DB12] Files:1    Time: 0s
Parallel Phase #:68   [DB12] Files:6    Time: 2s
Serial   Phase #:69   [DB12] Files:2    Time: 14s
Serial   Phase #:70   [DB12] Files:3    Time: 31s
Restart  Phase #:71   [DB12] Files:1    Time: 0s
*********   Upgrading CATJAVA,OWM,MGW,RAC   ********
Serial   Phase #:72   [DB12] Files:1    Time: 32s
****************   Upgrading ORDIM   ***************
Restart  Phase #:73   [DB12] Files:1    Time: 0s
Serial   Phase #:75   [DB12] Files:1    Time: 1s
Parallel Phase #:76   [DB12] Files:2    Time: 0s
Serial   Phase #:77   [DB12] Files:1    Time: 0s
Restart  Phase #:78   [DB12] Files:1    Time: 0s
Parallel Phase #:79   [DB12] Files:2    Time: 1s
Serial   Phase #:80   [DB12] Files:2    Time: 0s
*****************   Upgrading SDO   ****************
Restart  Phase #:81   [DB12] Files:1    Time: 0s
Serial   Phase #:83   [DB12] Files:1    Time: 1s
Serial   Phase #:84   [DB12] Files:1    Time: 0s
Restart  Phase #:85   [DB12] Files:1    Time: 0s
Serial   Phase #:86   [DB12] Files:1    Time: 0s
Restart  Phase #:87   [DB12] Files:1    Time: 1s
Parallel Phase #:88   [DB12] Files:3    Time: 0s
Restart  Phase #:89   [DB12] Files:1    Time: 0s
Serial   Phase #:90   [DB12] Files:1    Time: 1s
Restart  Phase #:91   [DB12] Files:1    Time: 0s
Serial   Phase #:92   [DB12] Files:1    Time: 0s
Restart  Phase #:93   [DB12] Files:1    Time: 1s
Parallel Phase #:94   [DB12] Files:4    Time: 0s
Restart  Phase #:95   [DB12] Files:1    Time: 0s
Serial   Phase #:96   [DB12] Files:1    Time: 1s
Restart  Phase #:97   [DB12] Files:1    Time: 0s
Serial   Phase #:98   [DB12] Files:2    Time: 0s
Restart  Phase #:99   [DB12] Files:1    Time: 0s
Serial   Phase #:100  [DB12] Files:1    Time: 1s
Restart  Phase #:101  [DB12] Files:1    Time: 0s
***********   Upgrading Misc. ODM, OLAP   **********
Serial   Phase #:102  [DB12] Files:1    Time: 0s
****************   Upgrading APEX   ****************
Restart  Phase #:103  [DB12] Files:1    Time: 1s
Serial   Phase #:104  [DB12] Files:1    Time: 0s
Restart  Phase #:105  [DB12] Files:1    Time: 0s
***********   Final Component scripts    ***********
Serial   Phase #:106  [DB12] Files:1    Time: 0s
*************   Final Upgrade scripts   ************
Serial   Phase #:107  [DB12] Files:1    Time: 118s
**********   End PDB Application Upgrade   *********
Serial   Phase #:108  [DB12] Files:1    Time: 0s
*******************   Migration   ******************
Serial   Phase #:109  [DB12] Files:1    Time: 32s
Serial   Phase #:110  [DB12] Files:1    Time: 0s
Serial   Phase #:111  [DB12] Files:1    Time: 40s
*****************   Post Upgrade   *****************
Serial   Phase #:112  [DB12] Files:1    Time: 73s
****************   Summary report   ****************
Serial   Phase #:113  [DB12] Files:1    Time: 0s
Serial   Phase #:114  [DB12] Files:1    Time: 0s
Serial   Phase #:115  [DB12] Files:1     Time: 22s

------------------------------------------------------
Phases [0-115]         End Time:[2017_03_07 14:34:45]
------------------------------------------------------

Grand Total Time: 1055s

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

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

Grand Total Upgrade Time:    [0d:0h:17m:35s]
[DB12] oracle@localhost:/u01/app/oracle/product/12.2.0.1/rdbms/admin

Database DB12 is now upgraded to Oracle Database 12.2.0.1 but needs some post-upgrade treatments.

Post-Upgrade Treatments

Those include recompilation, postupgrade_fixups.sql and time zone adjustment.

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 7 14:36:06 2017

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size            8628640 bytes
Variable Size          369100384 bytes
Database Buffers      662700032 bytes
Redo Buffers            8146944 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN          2017-03-07 14:36:40

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>        WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>        WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END          2017-03-07 14:41:52

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

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

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

No errors.

Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
Version: 12.2.0.1.0 Build: 3
Generated on:            2017-03-07 13:46:52

For Source Database:     DB12
Source Database Version: 12.1.0.2.0
For Upgrade to Version:  12.2.0.1.0

Fixup
Check Name                Status  Further DBA Action
----------                ------  ------------------
old_time_zones_exist      Failed  Manual fixup recommended.
post_dictionary           Passed  None

PL/SQL procedure successfully completed.

Session altered.

SQL> @/home/oracle/DST/upg_tzv_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 12.2.0.1 .
INFO: Database RDBMS DST version is DSTv18 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv26 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
SQL> @/home/oracle/DST/upg_tzv_apply.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be upda
ted to DSTv26 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  8628640 bytes
Variable Size             369100384 bytes
Database Buffers          662700032 bytes
Redo Buffers                8146944 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  8628640 bytes
Variable Size             369100384 bytes
Database Buffers          662700032 bytes
Redo Buffers                8146944 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv26 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.
SQL>

Database is now fully upgraded and ready to go. You may create fixed objects statistics a while after the database is up and running. But I can proceed now with the plugin operation.

Plugin the upgraded database as a PDB into a CDB

This step is simple and straight forward, though on part, the sanity script noncdb_to_pdb.sql, which will be only run once in the life span of a database and is irreversible (but restartable in Oracle Database 12.2.0.1 – it wasn’t before), may take a bit to complete depending on the number of objects and some other constraints.

First I’m creating the XML manifest file which basically described the physical layout of my database. The database has to be on read only mode – and it has to be at least an Oracle 12.1.0.1 database as otherwise the package DBMS_PDB wouldn’t exist.

$ . db122
[DB12] oracle@localhost:~
$ sSQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 7 22:58:38 2017Copyright (c) 1982, 2016, Oracle.  All rights reserved.

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

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only;
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  8628640 bytes
Variable Size             369100384 bytes
Database Buffers          662700032 bytes
Redo Buffers                8146944 bytes
Database mounted.
Database opened.
SQL> exec DBMS_PDB.DESCRIBE('/tmp/pdb1.xml');

PL/SQL procedure successfully completed.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Once the file has been created and the database is shutdown I’ll connect now into my already existing container database and progress from there. I will plugin now the DB12 database, give it a new name (not required but I’d recommend some obvious name identifying it as a PDB), doing a compatibility check (for errors please see PDB_PLUG_IN_VIOLATIONS) and then kick off the sanity script noncdb_to_pdb.sql.

[DB12] oracle@localhost:~
$ . cdb2
[CDB2] oracle@localhost:~
$ s
SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 7 23:07:19 2017Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set serverout on
SQL> DECLARE
compatible CONSTANT VARCHAR2(3) := CASE
DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/tmp/pdb1.xml',
pdb_name => 'PDB1')
WHEN TRUE THEN 'YES' ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('Is UPGR compatible? ' || compatible);
END;
/
Is UPGR compatible? YES
PL/SQL procedure successfully completed.

SQL> create pluggable database PDB1 using '/tmp/pdb1.xml' nocopy tempfile reuse;
Pluggable database created.

SQL> alter session set container=PDB1;
Session altered.
SQL> @?/rdbms/admin/noncdb_to_pdb.sql
SQL> startup
Pluggable database opened.
SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1                           READ WRITE NO
SQL>

When I run the CREATE PLUGGABLE DATABASE command I use the NOCOPY option intentionally. But be aware that in this case where the databases stays in place and does not get moved you have to have a valid backup of the database as in case of failure you may be lost in outer space with no way back or forth.

In addition, after this operation is completed, the sanity script has been run successfully and the new PDB is OPEN you must take a fresh backup as all your archives and backups from “before” are useless from now on. Don’t forget this – it’s key!

Yep – all done!

Database DB12 got upgraded to Oracle Database 12.2.0.1 – and then plugged into CDB2 as a new PDB1.

–Mike

Upgrade to Oracle Database 12.2 – New MOS Notes

Some very helpful MOS Notes got published regarding the upgrade to Oracle Database 12.2.0.1:

Furthermore check regularly for Known Issues and Alerts – currently the note has no content but exists already:

Cheers
–Mike

DOAG Noon2Noon – Upgrade/Multitenant – 2.-3.2.2017

++++++ Sorry – this is a German event only thus I will write in German ++++++

.

DOAG Noon2Noon

Ich bin schon sehr gespannt.

Am 2. Februar 2017 wird im Intercity Hotel in Mainz das DOAG Noon2Noon zum Thema “Upgrade, Migration und Multitenant” stattfinden. Von 12:00h mittags bis 12:00h mittags am Folgetag. Der Praxisbezug steht im Vordergrund. Garniert mit kurzen Vorträgen.

In der praxisorientierten “Noon2Noon”-Veranstaltung wollen wir uns mit den Herausforderungen beim Upgrade nach Oracle 12c bzw. Oracle 12c Release 2 (falls diese zu diesem Zeitpunkt für On Premises Datenbanken freigegeben ist) auseinandersetzen. Das ungewöhnliche Format zielt auf Lösungsorientiertheit. Bei den interaktiven Sessions heißt es dann: Ärmel hochkrempeln und mitmachen!
Von 12 bis 12 Uhr, mittags bis mittags, reduzieren wir die Folienschlachten auf ein Minimum. Unser Ziel ist es, dass am Ende der Veranstaltung jeder Teilnehmer erfolgreich ein Datenbank-Upgrade von Version 11.2 auf 12.1 mit der Multitenant Architektur durchgeführt hat. Unterstützung erhalten wir dabei von Ernst Leber, MT AG, Mike Dietrich, ORACLE Corporation, Martin Bach, Enkitec/Accenture und Uwe Hesse (ex-Oracle University).

Sie sollten unbedingt Ihren Laptop und vor allem eine Datenbank, entweder lokal installiert oder noch einfacher, in einer Oracle VBox oder VMware Umgebung, mitbringen, damit wir gemeinsam live und vor Ort das Upgrade oder eine Migration durchführen  und die DB dann nach Oracle Single/Multitenant überführen können.

Anmeldung bitte über die DOAG Seite:

  • http://www.doag.org/termine/termine.php?tid=527684

Hier noch ein wenig Motivation vorab:

Bis dahin 🙂

–Mike

Upgrade to Oracle Database 12.2 – Slides are available

The workshops in Brussels and Utrecht were awesome with great audience – I enjoyed both events a lot. And thanks for your patience with my voice and the microphone issues we’ve had in Brussels before lunch.

I promised the workshop slides – and here they are, ready for download:

Upgrade, Migrate and Consolidate to Oracle Database 12.2 and the Cloud

Slides Oracle 12.2 Upgrade Migrate Consolidate

Please be aware: It’s the first drop of the slides, not everything has been updated yet for Oracle Database 12.2, and of course there are no 12.2 customer examples in the slides yet. But as usual, if you plan to go live early on Oracle Database 12.2 – in the Oracle Cloud or as soon as on-premises is available please get in touch with me as we are looking for future reference customer cases.

–Mike & Roy

Minor Upgrade? Going from 11.2.0.1 to 11.2.0.4?

My Belgium friend Phillipe Fierens raised a great question on Twitter last week and dropped me an email again after having a discussion with his client:

For Phillipe and myself the answer is pretty clear and straight forward:

There is no minor upgrade anymore since every (patch set) release is a full release and since new parameters, parameter values, features and whatever appears even in patch sets.

But the following discussion on Twitter with comments from all sides made me think about why people would honestly declare going for instance from 11.2.0.1 to 11.2.0.3 as a minor upgrade whereas going to 12.1.0.2 is seen as a major upgrade?

Let me summarize why I completely disagree – and actually Dom Giles nailed it:

  • Since Oracle Database 11.2.0.1 we deliver patch sets as a full release
  • A patch set can contain not only new parameters or parameter values but may occasionally also contain new features (typical examples in Oracle 11.2.0.4 are the new value for optimizer_dynamic_sampling=11 or the DBMS_REDACT package)
  • Therefore you will have to do exactly the same amount of testing, regardless of going to Oracle Database 11.2.0.4 vs Oracle Database 12.1.0.2 – it is ZERO difference in the tests, the time, the effort, the manpower …
    .

You don’t believe me? Then please check MOS Note:1962125.1 (Oracle Database – Overview of Database Patch Delivery Methods). Scroll down a bit to Testing Recommendations By Patch Type and see the rightmost column of this table headlined “Patch Set Release”:

I hope this clarifies it all.

There is no “minor” patch set upgrade anymore. Even though I would love to tell you that going from 11.2.0.3 to 11.2.0.4 is just a minor hop it will require exactly the same testing and evaluation work then going to Oracle Database 12.1.0.2.

But going to Oracle Database 12.1.0.2 will mean that you are under Premier Support until end of June 2018 – whereas Free Extended Support for Oracle Database 11.2.0.4 will end in May 2017.

–Mike

EM 13c – How to Upgrade from EM Cloud Control 12c

I’m not a Cloud Control expert but I use the tool from time to time – and most of my customers are heavy users of it, especially in larger deployments.

If you use Oracle Enterprise Manager Cloud Control 12c (12.1.0.3, 12.1.0.4 or 12.1.0.5) and would like to evaluate your options to upgrade to Oracle Enterprise Manager Cloud Control 13c then please consult the following very useful documentation:

If you are searching for the software please find it here:

Just be aware:
As of July 15, 2016 I received several emails and comments by customers about issues with the migration to OEM 13c, e.g.:

So please check with Oracle Support first – and test the migration before doing it on a actual life system.

–Mike

Upgrade to Oracle Database 12c: We don’t insist :-)

It’s so funny – for years I discuss with customers minimal downtime upgrade strategies back and forth, up and down. I saw DBAs really hunting to save a few seconds potential downtime – and I always take this serious as there is usually a real reason behind that. Just to learn a few days later by repeating experience that my work Windows7 laptop goes down to apply upgrades … and this takes looooooooong …. sometimes it takes a lengthy +30 minutes of quiet time. Whereas my OL6 VBox image applies upgrades in the background and needs a simple restart to be back in less than a minute or so.
Different strategies of course.

For those who think I’m writing nonsense about the upgrade of my Win7 on my Lenovo x230 with 16GB of RAM, an i5-320M cpu and a 5400rpm WD spinning disk here’s a screenshot taken from one of those upgrades where I sat already 15 minutes waiting … waiting … waiting …

As Win7 had to configure the upgrades afterwards, once it was done with restarting I had to wait another 10 minutes for completion. And no, my laptop is not broken (not that I know) – it’s 3 years old and heavily used of course. But all running fine usually … 🙂

Anyhow …

Of course I discussed with a ton of people why you should upgrade now to Oracle Database 12.1.0.2 now! – and stay away from that “we go live on the 2nd release only” thinking as there’s no such thing as THE 2nd release anymore.

But whatever my position on upgrades is, I can ensure that we don’t discuss methods like this internally to force you to upgrade …

MS Win10 Forced Upgrade Message

… not yet *haha* !!!

–Mike

PS: The picture is not a fake – you can watch the 41 seconds youtube video here 🙂