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

SGA_MIN_SIZE in Oracle Database 12.2

I did summarize all new init.ora/spfile parameters in Oracle Database 12.2.0.1 a while ago:

New SPFILE parameters in Oracle Database 12.2.0.1

And one caught Roy’s and my attention: SGA_MIN_SIZE.

When we present this new parameter as part of the Multitenant slides in our workshops, some people ask themselves:
“What happens if I oversubscribe such a parameter?” For example, if SGA_TARGET=4G and I have 10 PDBs and define in each of them SGA_MIN_SIZE=500M. Will the 9th and 10th PDB not startup anymore? Or will the 7th PDB already in trouble as the CDB$ROOT will need some SGA portion as well?

None of this is true.

If the sum of SGA_MIN_SIZE across all PDBs is larger than 50% of the CDB’s setting for SGA_TARGET, then SGA_MIN_SIZE will automatically scaled down to meet the criteria of not adding up across all PDBs to more than 50% of SGA_TARGET of the root.

If you scale down SGA_MIN_SIZE too small for a given PDB, you may see an error warning.

The documentation says:

  • You must set the SGA_MIN_SIZE value to a value that meets these requirements:
    • In a PDB, to a value that is less than or equal to 50% of the value of SGA_TARGET in the PDB
    • In a PDB, to a value that is less than or equal to 50% of the value of SGA_TARGET at the CDB level
    • Across all the PDBs in a CDB, the sum of SGA_MIN_SIZE values must be less than or equal to 50% of the SGA_TARGET value at the CDB level.

    When you set SGA_MIN_SIZE in a PDB to a value that does not meet these requirements, you receive an error. If these requirements are violated after the PDB’s parameter is set (for example, if the SGA_MIN_SIZE value is changed at the CDB level, Oracle will adjust the PDB’s value to meet these requirements.

As you see, you won’t receive always an error. But in such a case where my SGA_TARGET in the CDB$ROOT is only 1.5GB, I will get the following error when I try to massively oversubscribe SGA_MIN_SIZE in a PDB:

SQL> alter session set container=pdb1;
Session altered.

SQL> alter system set sga_target=1G;
System SET altered.

SQL> alter system set sga_min_size=800M;

Error starting at line : 1 in command -
alter system set sga_min_size=800M
Error report -
ORA-32017: failure in updating SPFILE
ORA-56746: invalid value 838860800 for parameter sga_min_size; must be smaller
than 50% of parameter sga_target
32017. 00000 -  "failure in updating SPFILE"
*Cause:    A failure occured while updating the SPFILE.
*Action:   See associated errors.

SQL> alter system set sga_min_size=500M;
System SET altered.

First learning: SGA_MIN_SIZE gets treated relatively to the SGA_TARGET setting in the PDB.

Now lets try the same exercise in my second PDB:

SQL> alter session set container=pdb2;
Session altered.

SQL> alter system set sga_target=1G;
System SET altered.

SQL> alter system set sga_min_size=500M;
System SET altered.

Hm … interesting … let’s check in the CDB$ROOT:

SQL> alter session set container=cdb$root;
Session altered.

SQL> show sga
Total System Global Area   1577058304 bytes      
Fixed Size                    8793208 bytes      
Variable Size               385876872 bytes      
Database Buffers           1174405120 bytes      
Redo Buffers                  7983104 bytes    
  
SQL> show parameter sga_
NAME                         TYPE        VALUE   
---------------------------- ----------- ------- 
sga_max_size                 big integer 1504M   
sga_min_size                 big integer 0       
sga_target                   big integer 1504M   

It becomes a bit more clear that the values get adjusted internally (secretly) when you compare the values across PDBs:

SQL> select con_id, name, value from v$system_parameter where name like 'sga%' order by con_id;

    CON_ID NAME           VALUE               
---------- -------------- --------------------
         0 sga_min_size   0                   
         0 sga_max_size   1577058304          
         0 sga_target     1577058304          
         2 sga_target     0                   
         3 sga_target     1073741824          
         3 sga_min_size   524288000           
         4 sga_target     1073741824          
         4 sga_min_size   524288000           

8 rows selected. 

So finally, let’s monitor the actual SGA spread and consumption per PDB (thanks to Tim Hall for his queries):

SET LINESIZE 150
COLUMN pdb_name FORMAT A10

SQL> SELECT r.con_id,
            c.pdb_name,
            round(r.sga_bytes/(1024*1024),1) "SGA in MB",
            round(r.buffer_cache_bytes/(1024*1024),1) "BCh in MB",
            round(r.shared_pool_bytes/(1024*1024),1) "ShP in MB"
     FROM   v$rsrcpdbmetric r,
            cdb_pdbs c
     WHERE  r.con_id = c.con_id
     ORDER BY r.con_id;

    CON_ID PDB_NAME    SGA in MB  BCh in MB  ShP in MB
---------- ---------- ---------- ---------- ----------
         3 PDB2             37.3       25.2       12.1
         4 PDB1             38.9       25.5       13.4

–Mike

GIMR DB in Oracle Database 12.2

I did blog in the past already about the Grid Infrastructure Management Repository Database (GIMR DB):

Grid Infrastructure Management Repository (GIMR)database now mandatory in Oracle GI 12.1.0.2

SID and DBNAME are kept the same in Oracle Database 12.2. But there are a few changes and additions in Oracle Database 12.2.0.1.

GIMR DB in its own Disk Group

During a fresh installation of Oracle Grid Infrastructure 12.2.0.1 you’ll have the chance to create a disk group for GIMR DB:

GIMR DB - Own Disk Group in Oracle 12.2

GIMR DB – Disk Group

But you can migrate GIMR DB into a separate disk group as well – just make sure the size is large enough (see below).

  1. Download MDBUtil from MOS Note 2065175.1 (see next chapter below)
  2. Create your designated disk group for GIMR DB with ASMCA
  3. Move GIMR DB into the new diskgroup with MDBUtil:
    mdbutil.pl --mvmgmtdb --target=+GIMRDB
  4. Verify and startup:
    srvctl config mgmtdb
    oclumon dumpnodeview –allnodes

Download the most recent MDBUtil

The MDBUtil got introduced to ease standard tasks to administer, create, relocate etc GIMR DB a while back. And it gets steadily improved. The most recent version has Oracle 12.2.0.1 support and dates (while I create this post) from mid of March 2017.

It’s a PERL script – and it’s not supported by Oracle Support as the note explaining the standard tasks and offering the tool for download explains:

MDBUtil usage:

# ./mdbutil.pl -h
Usage:
     Create/Enable MGMTDB & CHM
       mdbutil.pl --addmdb --target=
     Move MGMTDB to another location
       mdbutil.pl --mvmgmtdb --target=   
     Check MGMTDB status
       mdbutil.pl --status
   
     mdbutil.pl OPTIONS
       --addmdb            Create MGMTDB/CHM and reconfigure related functions
       --mvmgmtdb          Migrate MGMTDB to another location   
       --target='+DATA'    MGMTDB Disk Group location
       --status            Check the CHM & MGMTDB status
       --help              Display this help and exit
       --debug             Verbose commands output/trace
   
     Example:
       Create/Enable MGMTDB:
         mdbutil.pl --addmdb --target=+DATA
       Move MGMTDB to another location:
         mdbutil.pl --mvmgmtdb --target=+REDO             
       Check CHM:
         mdbutil.pl --status

The MOS Note contains examples for creating and relocating GIMR DB as well.

Avoid INS-43100 error during upgrade

To avoid the OUI logging an INS-43100 error please check the space requirements for GIMR DB in Oracle Database 12.2 upfront as they have been increased drastically from Oracle 12.1 to Oracle 12.2., from roughly 5GB for a two-node cluster in Oracle 12.1 to 36GB for the same cluster in Oracle Database 12.2.

See also MOS 2245603.1:12.2 Grid Infrastructure: INS-43100 and Storage Space Requirements

Further information

Please see this presentation by the RAC team held at several User Group conferences and meetings:

–Mike

PGA_AGGREGATE_LIMIT enforces default since Oracle Database 12.2.0.1

The init.ora/spfile parameter PGA_AGGREGATE_LIMIT got introduced in Oracle Database 12.1.0.1.

As per documentation in Oracle Database 12.1 it got defined as:

PGA_AGGREGATE_LIMIT specifies a limit on the aggregate PGA memory consumed by the instance.“.

Furthermore the algorithm for its setting got described as:

By default, PGA_AGGREGATE_LIMIT is set to the greater of 2 GB, 200% of PGA_AGGREGATE_TARGET, and 3 MB times the PROCESSES parameter. It will be set below 200% of PGA_AGGREGATE_TARGET if it is larger than 90% of the physical memory size minus the total SGA size, but not below 100% of PGA_AGGREGATE_TARGET.”

Default Value Change in Oracle Database 12.2.0.1

In Oracle Database 12.2.0.1 the default value gets adjusted a bit as it turned out that restricting PGA_AGGREGATE_LIMIT to a value too low will lead to a significant number of issues.

See the Oracle Database 12.2.0.1 documentation on PGA_AGGREGATE_LIMIT explaining:

“If MEMORY_TARGET is set, then PGA_AGGREGATE_LIMIT defaults to the MEMORY_MAX_TARGET value.
If MEMORY_TARGET is not set, then PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET.
If MEMORY_TARGET is not set, and PGA_AGGREGATE_TARGET is explicitly set to 0, then the value of PGA_AGGREGATE_LIMIT is set to 90% of the physical memory size minus the total SGA size.
In all cases, the default PGA_AGGREGATE_LIMIT is at least 2GB and at least 3MB times the PROCESSES parameter.”

And please remember, DO NOT use MEMORY_TARGET as it will lead to issues such as no use of huge pages etc.

What happened in Oracle Database 12.1.0.2 when you set PGA_AGGREGATE_LIMIT too low?

How much is too low? Too low means it has been set lower that 2x the value of PGA_AGGREGATE_TARGET. And then it got adjusted “silently”, i.e. the value of PGA_AGGREGATE_LIMIT got adjusted internally after startup to reflect the minimum. Unfortunately this adjusted value does not get displayed by “show parameter”.

Short example in Oracle Database 12.1.0.2:

SQL> show parameter pga 

NAME			TYPE                      VALUE
----------------------- ------------------------- -----------------------------
pga_aggregate_limit     big integer               2G
pga_aggregate_target    big integer               120M
SQL> alter system set pga_aggregate_limit=1G scope=spfile;
System altered.

SQL> alter system set pga_aggregate_target=1G scope=spfile;
System altered.

After restarting the database:

SQL> show parameter pga

NAME				     TYPE	  VALUE
------------------------------------ ------------ ------------------------------
pga_aggregate_limit		     big integer  1G
pga_aggregate_target		     big integer  1G
Different behavior in Oracle Database 12.2.0.1

Repeating the same test in Oracle Database 12.2.0.1 leads to ORA-93:

SQL> show parameter pga 

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit		     big integer 2G
pga_aggregate_target		     big integer 120M
SQL> alter system set pga_aggregate_limit=1G scope=spfile;
System altered.

SQL> alter system set pga_aggregate_target=1G scope=spfile;
System altered.

SQL> create pfile from spfile;
File created.

SQL> startup force
ORA-00093: pga_aggregate_limit must be between 2048M and 100000G
ORA-01078: failure in processing system parameters

After increasing PGA_AGGREGATE_LIMIT=2G I can startup my database again. PGA_AGGREGATE_LIMIT gets enforced as at least 2x the value of PGA_AGGREGATE_TARGET since Oracle Database 12.2.0.1.

–Mike

Is your APEX version certified with your Database release?

Last week I’ve had an interesting discussion with a customer about the certification of APEX and Oracle Database 12.2.0.1.

I did a bit of research and emailing – and thanks to the APEX team – this is the source of truth for APEX certifications.

Minimum Database version for APEX

See MOS Note:1344948.1: Application Express (APEX) Database and Web Server Certification Reference:

  • For APEX 4.0, the minimum database version is 10.2.0.3
  • For APEX 5.0, the minimum database version is 11.1.0.7
  • For APEX 5.1, the minimum database version is 11.2.0.4

Minimum APEX version per Database

See MOS Note:1344948.1: Application Express (APEX) Database and Web Server Certification Reference:

  • For Oracle Database 11.1.0.x, the minimum APEX version is 3.0.
  • For Oracle Database 11.2.0.x, the minimum APEX version is 3.2.1
  • For Oracle Database 12.1.0.2, the minimum APEX version is 4.2.5
    • For usage with Oracle Multitenant, APEX 4.2.3 or newer must be installed
  • For Oracle Database 12.2.0.1, the minimum APEX version is 5.0.4
    (this information is currently missing in the MOS note and may be added later – read below for more information)

Checking APEX certifications on MyOracle Support’s Certification Tab

  1. Go to https://support.oracle.com
  2. Click Certifications
  3. Enter Oracle Application Express as Product
  4. Select APEX version from the Release list, for example 5.0.4
Oracle APEX - Certification MOS

Oracle APEX Certification in MyOracle Support

By clicking myself through all the possible combinations I could find out that APEX 5.0.3 is not certified with Oracle Database 12.2.0.1 as are all versions below. So the minimum version of APEX for Oracle Database 12.2.0.1 is APEX 5.0.4.

Unfortunately there’s no reverse certification search in MOS available (“Which APEX version(s) does a given databases release support?”).

–Mike

Use the correct download to patch APEX from 5.1.0 to 5.1.1

When you plan to upgrade Oracle Application Express (APEX) from version 5.1.0 to 5.1.1 please be aware to download a patch from MOS instead the 5.1.1 version from OTN.

The text on the OTN page is a bit misleading – and a customer I work with downloaded the wrong version which led to a failed upgrade – even though the scripts seem to be made for this patch upgrade.

APEX 5.1.1 Full Version Download

Oracle APEX 5.1.1 download on OTN

Even though the page says “This is a cumulative patch set for Application Express 5.1.0” the page offers you a full install and not the patch for an existing APEX 5.1.0 installation.

See the message in the middle of the page starting with “For existing Application Express 5.1.0 installations, please download … from My Oracle Support.”

The patch you’ll need is available via this download link: patch 25341386

–Mike

Keep your patch versions between Grid Infrastructure and Databases Homes in synch

Patch RecommendationI’ve had some interesting discussions with Anil Nair, our RAC Product Manager and a customer in the past days. The customer was looking for a definite statement that they can have a higher version of Patch Set Updates (PSUs) or Proactive Bundle Patches (BPs) in the Database homes than in the Grid Infrastructure home managing the resources.

Can you have different PSU/BP versions between Database and GI homes?

Yes, you can have a higher version PSU or BP in the Database home than in the Grid Infrastructure home managing the resources. This is implicitly documented in MOS Note 337737.1 – Oracle Clusterware (CRS/GI) – ASM – Database Version Compatibility where it says:

The Oracle Grid Infrastructure (GI) /Clusterware (CRS) version must be of the highest version down to the 4th digit in the possible combinations at all times.

Therefore you can deviate that this rule does not apply for the 5th digit anymore. Anil and I discussed that this may be documented a bit more clear – and he will take care.

Should you have different PSU/BP versions between Database and GI homes?

Well, this question is a bit harder. I can see why some customers have a higher patch level (5th digit) in the Database homes than in their GI homes. Actually some of my customers have this setup as well. GI is pretty stable above a certain patch level and often gets not treated with the same importance than the database homes as the need for patching in database homes is more obvious and visible. When it’s stable don’t touch it. And honestly speaking, patching is no fun – but hard work.

But we strongly recommend that you keep your patch levels in synch. If you apply the GI PSU of April 2017 then please apply the Database PSU or BP of April 2017 as well. These are the combinations we test internally. Please bear in mind that while the GI and DB are separate entities, they are tightly integrated and work cohesively. This way you mitigate the potential risk of an untested issue by having differences in the 5th digit.

And as a final hint:
Grid Infrastructure PSUs (and BPs) can be applied always in a rolling fashion causing no downtime. Just saying …

–Mike

 

What happens to PASSWORD_VERSIONS during an upgrade to Oracle 12.2?

I did blog a day ago about ORA-1017 connection issues in Oracle Database 12.2 once you would like to use the deprecated init.ora/spfile parameter SEC_CASE_SENSITIVE_LOGON=FALSE:

Having some fun with SEC_CASE_SENSITIVE_LOGON and ORA-1017

But how can this change actually happen?

Let’s check how the PASSWORD_VERSIONS is set in a fresh Oracle Database 12.1.0.2 database with the January 2017 Proactive Bundle Patch applied to it:

SQL> select username, password_versions from dba_users order by 1;

USERNAME		       PASSWORD_VERSIONS
------------------------------ -----------------
ANONYMOUS
APPQOSSYS		       10G 11G 12C
AUDSYS			       10G 11G 12C
DBSNMP			       10G 11G 12C
DIP			       10G 11G 12C
GSMADMIN_INTERNAL	       10G 11G 12C
GSMCATUSER		       10G 11G 12C
GSMUSER 		       10G 11G 12C
ORACLE_OCM		       10G 11G 12C
OUTLN			       10G 11G 12C
SYS			       10G 11G 12C
SYSBACKUP		       10G 11G 12C
SYSDG			       10G 11G 12C
SYSKM			       10G 11G 12C
SYSTEM			       10G 11G 12C
WMSYS			       10G 11G 12C
XDB			       10G 11G 12C
XS$NULL

18 rows selected.

As I haven’t touched SEC_CASE_SENSITIVE_LOGON it will will default to TRUE. In my environment I use an Oracle 12.2 listener  therefore the default for the sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER is 12 already.

After the successful upgrade to Oracle Database 12.2 my DBA_USER’s PASSWORD_VERSIONS looks like this:

SQL> select USERNAME, PASSWORD_VERSIONS from DBA_USERS order by 1

USERNAME		       PASSWORD_VERSIONS
------------------------------ -----------------
ANONYMOUS
APPQOSSYS		       10G 11G 12C
AUDSYS			       10G 11G 12C
DBSFWUSER		       11G 12C
DBSNMP			       10G 11G 12C
DIP			       10G 11G 12C
GGSYS			       11G 12C
GSMADMIN_INTERNAL	       10G 11G 12C
GSMCATUSER		       10G 11G 12C
GSMUSER 		       10G 11G 12C
ORACLE_OCM		       10G 11G 12C
OUTLN			       10G 11G 12C
REMOTE_SCHEDULER_AGENT
SYS			       10G 11G 12C
SYS$UMF 		       11G 12C
SYSBACKUP		       10G 11G 12C
SYSDG			       10G 11G 12C
SYSKM			       10G 11G 12C
SYSRAC			       11G 12C
SYSTEM			       10G 11G 12C
WMSYS			       10G 11G 12C
XDB			       10G 11G 12C
XS$NULL

23 rows selected.

None of the existing user account’s PASSWORD_VERSIONS get changed. Only new users will be created with either PASSWORD_VERSIONS11G 12C” or locked.

Upgrading with SEC_CASE_SENSITIVE_LOGON=FALSE

What happens if you have set SEC_CASE_SENSITIVE_LOGON=FALSE in your source database prior to an upgrade to Oracle Database 12.2?

First of all you will receive a preupgrade warning when you execute preupgrade.jar:

  RECOMMENDED ACTIONS
  ===================
   + Consider removing the following DEPRECATED initialization parameters.
     They are not OBSOLETE in version 12.2.0.1.0
     but probably will be OBSOLETE in a future release.

     Parameter
     ------------------------------
     sec_case_sensitive_logon

My users in my current – in this case Oracle 11.2.0.4 database – look like this before upgrade:

SQL> select username, password_versions from dba_users

USERNAME		       PASSWORD
------------------------------ --------
SYSTEM			       10G 11G
SYS			       10G 11G
LBACSYS 		       10G 11G
OUTLN			       10G 11G
DBSNMP			       10G 11G
APPQOSSYS		       10G 11G
ANONYMOUS
XDB			       10G 11G
WMSYS			       10G 11G
XS$NULL 		       11G
DIP			       10G 11G
ORACLE_OCM		       10G 11G

12 rows selected.

And the result is “as expected” – no changes to the PASSWORD_VERSIONS when you upgrade to Oracle Database 12.2:

USERNAME		       PASSWORD_VERSIONS
------------------------------ -----------------
ANONYMOUS
APPQOSSYS		       10G 11G
AUDSYS			       11G 12C
DBSFWUSER		       11G 12C
DBSNMP			       10G 11G
DIP			       10G 11G
GGSYS			       11G 12C
GSMADMIN_INTERNAL	       11G 12C
GSMCATUSER		       11G 12C
GSMUSER 		       11G 12C
LBACSYS 		       10G 11G
ORACLE_OCM		       10G 11G
OUTLN			       10G 11G
REMOTE_SCHEDULER_AGENT
SYS			       10G 11G
SYS$UMF 		       11G 12C
SYSBACKUP		       11G 12C
SYSDG			       11G 12C
SYSKM			       11G 12C
SYSRAC			       11G 12C
SYSTEM			       10G 11G
WMSYS			       10G 11G
XDB			       10G 11G
XS$NULL

24 rows selected.

But what happens when you try to connect now with user SYSTEM? SYSTEM has the PASSWORD_VERSIONS="10G 11G" as only the new users will get created with PASSWORD_VERSIONS="11G 12C".

SQL> connect system/oracle
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Even if you ALTER now SYSTEM‘s password you can’t login as the SEC_CASE_SENSITIVE_LOGON=FALSE setting collides with the default SQL*Net authentication protocol (SQLNET.ALLOWED_LOGON_VERSION_SERVER=12).

SQL> alter user system identified by system;
User altered.

SQL> connect system/system
ERROR:
ORA-01017: invalid username/password; logon denied

SYSTEM‘s password is now "11G 12C" – but still you can’t connect because of the SEC_CASE_SENSITIVE_LOGON=FALSE setting:

SQL> select USERNAME, PASSWORD_VERSIONS from DBA_USERS where USERNAME='SYSTEM';

USERNAME		       PASSWORD_VERSIONS
------------------------------ -----------------
SYSTEM			       11G 12C

Simple solution: You change SEC_CASE_SENSITIVE_LOGON=TRUE, the default.

SQL> alter system set sec_case_sensitive_logon=true;
System altered.

SQL> alter user system identified by oracle;
User altered.

SQL> connect system/oracle
Connected.

Or you use the workaround from my previous blog post:

What happens during export/import?

Next question I’ve had: What happens if I export and import a user, let’s say from Oracle Database 11.2.0.4 into Oracle Database 12.2.0.1. Quick test again:

SQL> grant connect, resource, dba to hugo identified by hugo;
Grant succeeded.

SQL>  select USERNAME, PASSWORD_VERSIONS from DBA_USERS where username='HUGO';

USERNAME		       PASSWORD
------------------------------ --------
HUGO			       10G 11G

SQL> alter user hugo default tablespace users;
User altered.

SQL> create table hugo.tab1 as select * from tab$;
Table created.

Then over to my destination database:

SQL> create directory mydir as '/u02/oradata/DB12/mydir';
Directory created.

SQL> grant read, write on directory mydir to system;
Grant succeeded.

SQL> create public database link SOURCEDB connect to system identified by oracle using 'UPGR';
Database link created.

SQL> select instance from v$thread@SOURCEDB;

INSTANCE
--------------------------------------------------------------------------------
UPGR

Now let’s move HUGO over to the DB12 database using the database link:

$ impdp system/oracle network_link=sourcedb schemas=hugo metrics=y logtime=all exclude=statistics directory=mydir logfile=hugo.log 

Import: Release 12.2.0.1.0 - Production on Mon Apr 24 15:34:48 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
24-APR-17 15:34:54.082: Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** network_link=sourcedb schemas=hugo metrics=y logtime=all exclude=statistics directory=mydir logfile=hugo.log 
24-APR-17 15:34:55.181: W-1 Startup took 2 seconds
24-APR-17 15:34:55.299: W-1 Estimate in progress using BLOCKS method...
24-APR-17 15:34:56.428: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
24-APR-17 15:34:56.485: W-1      Estimated 1 TABLE_DATA objects in 1 seconds
24-APR-17 15:34:56.490: W-1 Total estimation using BLOCKS method: 256 KB
24-APR-17 15:34:56.807: W-1 Processing object type SCHEMA_EXPORT/USER
24-APR-17 15:34:56.975: W-1      Completed 1 USER objects in 0 seconds
24-APR-17 15:34:56.975: W-1 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
24-APR-17 15:34:57.014: W-1      Completed 1 SYSTEM_GRANT objects in 1 seconds
24-APR-17 15:34:57.014: W-1 Processing object type SCHEMA_EXPORT/ROLE_GRANT
24-APR-17 15:34:57.095: W-1      Completed 3 ROLE_GRANT objects in 1 seconds
24-APR-17 15:34:57.095: W-1 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
24-APR-17 15:34:57.385: W-1      Completed 1 DEFAULT_ROLE objects in 0 seconds
24-APR-17 15:34:57.385: W-1 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
24-APR-17 15:35:00.257: W-1      Completed 1 PROCACT_SCHEMA objects in 3 seconds
24-APR-17 15:35:00.257: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
24-APR-17 15:35:01.646: W-1      Completed 1 TABLE objects in 4 seconds
24-APR-17 15:35:01.841: W-1 . . imported "HUGO"."TAB1"                                 1325 rows in 0 seconds using network link
24-APR-17 15:35:02.015: W-1 Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
24-APR-17 15:35:02.106: W-1      Completed 1 PROCACT_SCHEMA objects in 0 seconds
24-APR-17 15:35:02.495: W-1      Completed 1 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 0 seconds
24-APR-17 15:35:02.609: Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Mon Apr 24 15:35:02 2017 elapsed 0 00:00:10

Check how the user got created:

SQL>  select USERNAME, PASSWORD_VERSIONS from DBA_USERS where username='HUGO';

USERNAME                     PASSWORD_VERSIONS
---------------------------- --------------------
HUGO                         10G 11G

No worries – Data Pump creates the user exactly as it did exist before.

But what happens if a mandatory password change happens to HUGO?

SQL> alter user hugo identified by hugo;
User altered.

SQL> select USERNAME, PASSWORD_VERSIONS from DBA_USERS where username='HUGO';

USERNAME		       PASSWORD_VERSIONS
------------------------------ -----------------
HUGO			       11G 12C

If you change SQLNET.ALLOWED_LOGON_VERSION_SERVER to 12a, the currently highest and most secure setting, and then ALTER the user within a new session, you’ll receive:

SQL> select USERNAME, PASSWORD_VERSIONS from DBA_USERS where username='HUGO'

USERNAME		       PASSWORD_VERSIONS
------------------------------ -----------------
HUGO			       12C

This leads now to the situation that a below-Oracle-12c client can’t connect to this database anymore. I tried to use my SQL*Plus from the Oracle 11.2.0.4 installation:

$ sqlplus "hugo/hugo@DB12"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 24 15:51:08 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-28040: No matching authentication protocol

So be aware of keepin your client environment in good shape when enforcing higher security standards – which we highly recommend of course.

–Mike

Having some fun with SEC_CASE_SENSITIVE_LOGON and ORA-1017

The init.ora/spfile parameter SEC_CASE_SENSITIVE_LOGON got deprecated since Oracle Database 12.1.0.1. This means, we don’t do any further developments to it, you shouldn’t change it from its default TRUE – and if you still do you’ll receive a nice warning during STARTUP of your database:

SQL> alter system set sec_case_sensitive_logon=false scope=spfile;

System altered.

SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Recently a customer asked me if we’d changed the behavior of this parameter in Oracle Database 12c Release 2 as he receives now an ORA-1017: Invalid username or password error when having SEC_CASE_SENSITIVE_LOGON=FALSE with every user except SYSDBAs.

In which situations may you receive an ORA-1017?

This is outlined in the Oracle 12.1 documentation already:

  • “Ensure that the SEC_CASE_SENSITIVE_LOGON parameter is not set to FALSE if the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter is set to 12 or 12a. This is because the more secure password versions used for this mode only support case-sensitive password checking. For compatibility reasons, Oracle Database does not prevent the use of FALSE for SEC_CASE_SENSITIVE_LOGON when SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12 or 12a. Setting SEC_CASE_SENSITIVE_LOGON to FALSE when SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12 or 12a causes all accounts to become inaccessible.”

The key is the sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER. And here’s the difference between Oracle Database 12.1 and Oracle Database 12.2:

  • Oracle Database 12.1: SQLNET.ALLOWED_LOGON_VERSION_SERVER defaults to 11 out of the box
  • Oracle Database 12.2: SQLNET.ALLOWED_LOGON_VERSION_SERVER defaults to 12 out of the box

Behavior difference Oracle 12.1 vs Oracle 12.2

See this simple example after switching SEC_CASE_SENSITIVE_LOGON=FALSE in both databases (as shown above):

  • Oracle Database 12.1.0.2:
    SQL> alter user system identified by oracle;
    
    User altered.
    
    SQL> connect system/oracle
    Connected.
    
  • Oracle Database 12.2.0.1:
    SQL> alter user system identified by oracle;
    
    User altered.
    
    SQL> connect system/oracle
    ERROR:
    ORA-01017: invalid username/password; logon denied
    
    
    Warning: You are no longer connected to ORACLE.

How to workaround the ORA-1017?

First of all you need to edit your sqlnet.ora adding (or lowering) the parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER to a value below 12. But if you try to connect directly after restarting your listener you will receive the same ORA-1017 again. The secret is mentioned in the above documentation link as well: you will have to recreate the user’s passwords if you need the logon process to work as it did work before Oracle Database 12.2.

sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

Check in DBA_USERS:

SQL> select username, password_versions from DBA_USERS where username='SYSTEM'; 

USERNAME      PASSWORD_VERSIONS
------------- ---------------------
SYSTEM        11G 12C

There’s no “10G” mentioned. This will prevent the connection.

Solution: You will have to specify the password again respective ALTER the user(s):

SQL> alter user system identified by oracle;

User altered.

SQL> select username, password_versions from DBA_USERS where username='SYSTEM'; 

USERNAME       PASSWORD_VERSIONS
-------------- ----------------------
SYSTEM         10G 11G 12C

SQL> connect system/oracle
Connected.

Further information and links

–Mike