Category Archives: Single-/Multitenant

Issue with PDB Archives in Oracle 12.2.0.1 in ASM

Posted on by

There is a fancy new command to unplug a PDB in Oracle Database 12.2.0.1:

ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO 'pdb1.pdb';

The nice thing with this command differing in the file ending of ‘pdb‘ instead of ‘xml‘ as you used it in Oracle 12.1 (and the ‘xml‘ option is still available of course): Instead of just creating an xml description file it zips everything together into a PDB archive.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
SQL> create pluggable database PDB1 admin user adm identified by adm
  2  file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb1');
Pluggable database created.

SQL> alter pluggable database pdb1 open;
Pluggable database altered.

SQL> alter pluggable database pdb1 close;
Pluggable database altered.

SQL> alter pluggable database pdb1 unplug into '/home/oracle/pdb1.pdb';
Pluggable database altered.

Now lets have a quick look into the created file:

-rw-r--r--. 1 oracle dba   108414 May 19 16:20 pdb1.pdb
$ unzip pdb1.pdb 
Archive:  pdb1.pdb
  inflating: system01.dbf            
  inflating: sysaux01.dbf            
  inflating: undotbs01.dbf           
warning:  stripped absolute path spec from /home/oracle/pdb1.xml
  inflating: home/oracle/pdb1.xml

Not bad. Actually pretty cool.

Of course the same command fails in Oracle Database 12.1.0.2:

SQL> alter pluggable database pdb1 unplug into '/home/oracle/pdb1.pdb';
alter pluggable database pdb1 unplug into '/home/oracle/pdb1.pdb'
                                          *
ERROR at line 1:
ORA-65125: valid XML file name is required

Unfortunately there’s a significant flaw when your PDB is stored in ASM:
The zip file does not contain the datafiles of your PDB.

Please see:

It is supposed to be fixed with patch set 12.2.0.2.

A really handy feature – but don’t use it when your PDBs are located in ASM.

–Mike

Can I restart a failed Multitenant Upgrade as well?

Posted on by

A while back I did blog about the new -R option of the parallel upgrade tool catctl.pl in Oracle Database 12.2.

Restarting a failed Database Upgrade with catctl.pl

And in case you will do a real Multitenant upgrade and fail – as it happened to me today due to “no space left on device” (no audit files could be written anymore) I tried the -R option as well based on Joe’s (our lead catctl.pl developer) recommendation:

$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -R -l /home/oracle/mike2 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/mike2
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 = 1
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/mike2]

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

Number of Cpus        = 2
Database Name         = CDB1
DataBase Version      = 12.2.0.1.0
Parallel SQL Process Count (PDB)      = 2
Parallel SQL Process Count (CDB$ROOT) = 6
Concurrent PDB Upgrades               = 3
PDB$SEED Open Mode = [READ ONLY] NO UPGRADE WILL BE PERFORMED
PDB1 Open Mode = [MOUNTED] NO UPGRADE WILL BE PERFORMED
PDB2 Open Mode = [MOUNTED] NO UPGRADE WILL BE PERFORMED
PDB4 Open Mode = [READ WRITE] NO UPGRADE WILL BE PERFORMED
Generated PDB Inclusion:[PDB3 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]

** Database CDB$ROOT has already been upgraded successfully. **
    Time: 2s

Start processing of PDB3
[/u01/app/oracle/product/12.2.0.1/perl/bin/perl catctl.pl -n 2 -R -l /home/oracle/mike2 -I -i pdb3 -c 'PDB3' catupgrd.sql]

Start processing of PDB5
[/u01/app/oracle/product/12.2.0.1/perl/bin/perl catctl.pl -n 2 -R -l /home/oracle/mike2 -I -i pdb5 -c 'PDB5' catupgrd.sql]

Argument list for [catctl.pl]
Run in                c = PDB3
Do not run in         C = 0
Input Directory       d = 0
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = pdb3
Child Process         I = 1
Log Dir               l = /home/oracle/mike2
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 2
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 = 1
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]



Argument list for [catctl.pl]
Run in                c = PDB5
Do not run in         C = 0
Input Directory       d = 0
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = pdb5
Child Process         I = 1
Log Dir               l = /home/oracle/mike2
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 2
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 = 1
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/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/mike2]

/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/mike2]

catcon: ALL catcon-related output will be written to [/home/oracle/mike2/catupgrdpdb5_catcon_10837.lst]
catcon: See [/home/oracle/mike2/catupgrdpdb5*.log] files for output generated by scripts
catcon: See [/home/oracle/mike2/catupgrdpdb5_*.lst] files for spool files, if any
catcon: ALL catcon-related output will be written to [/home/oracle/mike2/catupgrdpdb3_catcon_10835.lst]
catcon: See [/home/oracle/mike2/catupgrdpdb3*.log] files for output generated by scripts
catcon: See [/home/oracle/mike2/catupgrdpdb3_*.lst] files for spool files, if any

Number of Cpus        = 2

Number of Cpus        = 2
Database Name         = CDB1
Database Name         = CDB1
DataBase Version      = 12.2.0.1.0
DataBase Version      = 12.2.0.1.0
Generated PDB Inclusion:[PDB5]
CDB$ROOT  Open Mode = [OPEN]
Generated PDB Inclusion:[PDB3]
Components in [PDB5]
    Installed [CATALOG CATPROC XDB]
Not Installed [APEX APS CATJAVA CONTEXT DV EM JAVAVM MGW ODM OLS ORDIM OWM RAC SDO WK XML XOQ]
CDB$ROOT  Open Mode = [OPEN]
Components in [PDB3]
    Installed [CATALOG CATPROC XDB]
Not Installed [APEX APS CATJAVA CONTEXT DV EM JAVAVM MGW ODM OLS ORDIM OWM RAC SDO WK XML XOQ]

*******Upgrade being restarted on database PDB5 from failed phase 40*******

------------------------------------------------------
Phases [40-115]         Start Time:[2017_05_09 15:25:29]
Container Lists Inclusion:[PDB5] Exclusion:[NONE]
------------------------------------------------------

*******Upgrade being restarted on database PDB3 from failed phase 109*******

------------------------------------------------------
Phases [109-115]         Start Time:[2017_05_09 15:25:29]
Container Lists Inclusion:[PDB3] Exclusion:[NONE]
------------------------------------------------------
   Time: 2s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [PDB5] Files:3    Time: 2s
*******************   Migration   ******************
Serial   Phase #:109  [PDB3] Files:1

And yes, it works!

The upgrade will be restarted for PDB3 and PDB5 exactly in the failed phased from the first run. Upgrades for CDB$ROOT, PDB$SEED, PDB1, PDB2 and PDB4 are completed already and will be skipped.

–Mike

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

Posted on by

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

Posted on by

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

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

Posted on by

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

Install components in Multitenant ALWAYS with catcon.pl

Posted on by

I did blog several times about how to remove an unwanted component from a database. But yesterday I came across this interesting scenario worth a blog post.

How to install a component afterwards in Single-/Multitenant?

I haven’t checked all the MOS Notes but I recognized that some MOS Notes explaining how to (re-)create a component such as JVM are not updated to deal with a Single and/or Multitenant environment as they simply call the scripts from SQL*Plus. But the key to script execution in such an environment is catcon.pl, the perl driver meant to execute database scripts not only in the CDB$ROOT but also in the PDB$SEED and in all opened PDBs.

If you don’t use catcon.pl for script executions then you may get something in CDB$ROOT but not in the PDB$SEED and therefore not in any future PDB.

Example: Create JVM after database creation

Creating JVM is pretty well handled and described in MOS Note:1612279.1. With one exception: It does not talk about what to do in a Multitenant environment. I learned about this as one of my colleagues ran the steps described in the note from SQL*Plus connected to the CDB$ROOT – and recognized that JVM did not get created in the existing PDB(s).

The MOS Note:1612279.1 proposes the following steps:

-- Start of File full_jvminst.sql
spool full_jvminst.log;
set echo on
connect / as sysdba
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter database open;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
@?/javavm/install/initjvm.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/xdk/admin/initxml.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/xdk/admin/xmlja.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/rdbms/admin/catjava.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
shutdown immediate
set echo off
spool off
exit
-- End of File full_jvminst.sql

But if you’d execute it you’ll get the Java components only in the CDB$ROOT – but not in the PDB$SEED: And as the PDB$SEED is untouchable for users you can’t simply force it open and run the script again.

Install JVM correctly in a Single-/Multitenant environment

Assuming that the above script does create everything correctly it needs to be transformed to deal with a Single-/Multitenant environment by using catcon.pl.:

-- Start of File full_jvminst_mike.sql
spool full_jvminst.log;
set echo on
alter pluggable database all open;
alter system set "_system_trig_enabled" = false scope=memory;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;

host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b initjvm /u01/app/oracle/product/12.1.0.2/javavm/install/initjvm.sql;

select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;

host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b initxml /u01/app/oracle/product/12.1.0.2/xdk/admin/initxml.sql;

select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;

host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b xmlja /u01/app/oracle/product/12.1.0.2/xdk/admin/xmlja.sql;

select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;

host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catjava /u01/app/oracle/product/12.1.0.2/rdbms/admin/catjava.sql;

select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
shutdown immediate
set echo off
spool off
exit
-- End of File full_jvminst_mike.sql

By the way, the (still) undocumented “-n 1” option simply does start only one worker and creates only one logfile. It is usually used to execute database standard scripts.

Testing it in my environment

Of course I did execute the script in my environment – an Oracle Database 12.1.0.2 container database:

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 2 10:03:36 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  2932336 bytes
Variable Size             369099152 bytes
Database Buffers          671088640 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
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

SQL> create pluggable database PDB1
     admin user adm identified by adm
     file_name_convert=('/u02/oradata/CDB1/pdbseed','/u02/oradata/CDB1/pdb1');
Pluggable database created.

SQL> alter pluggable database pdb1 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

SQL> @full_jvminst_mike.sql

SQL> alter pluggable database all open;
Pluggable database altered.

SQL> alter system set "_system_trig_enabled" = false scope=memory;
System altered.

SQL> select obj#, name from obj$
     where type#=28 or type#=29 or type#=30 or namespace=32;
no rows selected

SQL> host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b initjvm /u01/app/oracle/product/12.1.0.2/javavm/install/initjvm.sql;
catcon: ALL catcon-related output will be written to /home/oracle/initjvm_catcon_13696.lst
catcon: See /home/oracle/initjvm*.log files for output generated by scripts
catcon: See /home/oracle/initjvm_*.lst files for spool files, if any
catcon.pl: completed successfully

SQL> select count(*), object_type from all_objects
     where object_type like '%JAVA%' group by object_type;

COUNT(*)   OBJECT_TYPE
---------- ---------------------------------------------------------------------
302        JAVA DATA
832        JAVA RESOURCE
25893      JAVA CLASS

SQL> host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b initxml /u01/app/oracle/product/12.1.0.2/xdk/admin/initxml.sql;
catcon: ALL catcon-related output will be written to /home/oracle/initxml_catcon_15160.lst
catcon: See /home/oracle/initxml*.log files for output generated by scripts
catcon: See /home/oracle/initxml_*.lst files for spool files, if any
catcon.pl: completed successfully

SQL> select count(*), object_type from all_objects
     where object_type like '%JAVA%' group by object_type;

COUNT(*)   OBJECT_TYPE
---------- ---------------------------------------------------------------------
302        JAVA DATA
908        JAVA RESOURCE
27097      JAVA CLASS

SQL> host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b xmlja /u01/app/oracle/product/12.1.0.2/xdk/admin/xmlja.sql;
catcon: ALL catcon-related output will be written to /home/oracle/xmlja_catcon_16210.lst
catcon: See /home/oracle/xmlja*.log files for output generated by scripts
catcon: See /home/oracle/xmlja_*.lst files for spool files, if any
catcon.pl: completed successfully


SQL> select count(*), object_type from all_objects
     where object_type like '%JAVA%' group by object_type;

COUNT(*)   OBJECT_TYPE
---------- ---------------------------------------------------------------------
302        JAVA DATA
908        JAVA RESOURCE
27097      JAVA CLASS

SQL> host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catjava /u01/app/oracle/product/12.1.0.2/rdbms/admin/catjava.sql;
catcon: ALL catcon-related output will be written to /home/oracle/catjava_catcon_16273.lst
catcon: See /home/oracle/catjava*.log files for output generated by scripts
catcon: See /home/oracle/catjava_*.lst files for spool files, if any
catcon.pl: completed successfully


SQL> select count(*), object_type from all_objects
     where object_type like '%JAVA%' group by object_type;

COUNT(*)   OBJECT_TYPE
---------- ---------------------------------------------------------------------
302        JAVA DATA
911        JAVA RESOURCE
27473      JAVA CLASS
2          JAVA SOURCE

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> set echo off
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

[CDB1] oracle@localhost:/u01/app/oracle/product/12.1.0.2/rdbms/admin
$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 2 10:28:53 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  2932336 bytes
Variable Size             369099152 bytes
Database Buffers          671088640 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
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
SQL> select comp_id from dba_registry order by 1;

COMP_ID
--------------------------------------------------------------------------------
CATALOG
CATJAVA
CATPROC
JAVAVM
RAC
XDB
XML

7 rows selected.

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

SQL> select comp_id from dba_registry order by 1;

COMP_ID
--------------------------------------------------------------------------------
CATALOG
CATJAVA
CATPROC
JAVAVM
RAC
XDB
XML

7 rows selected.

So it looks good – the required components got all created within each container. But will the component be there as well when I provision a new pluggable database?

Provisioning a new pluggable database

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

SQL> create pluggable database PDB2
     admin user adm identified by adm
     file_name_convert=('/u02/oradata/CDB1/pdbseed','/u02/oradata/CDB1/pdb2');
Pluggable database created.

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

SQL> startup
Pluggable Database opened.

SQL> select comp_id from dba_registry order by 1;

COMP_ID
--------------------------------------------------------------------------------
CATALOG
CATJAVA
CATPROC
JAVAVM
RAC
XDB
XML

7 rows selected.

SQL>

This looks good as well.

Summary

It’s important to change habits when dealing with Single Tenant or Multitenant environments. You must ensure to execute scripts with catcon.pl in order to ensure that those will be executed in all containers, not only in the CDB$ROOT. Of course it is hard to find the right balance between either too many components or the need to recreate components here and there. Especially in a Multitenant environment it is key that you don’t alter component sets to heavily as otherwise you’ll see yourself ending up in situations where you want to unplug the above PDB1 and plug it in somewhere else – but if the destination’s CDB has no JVM installed you can’t really plugin.

–Mike

DBMS_QOPATCH does not work in PDBs (right now)

Posted on by

Thanks to Murthy who commented on this blog post and Jeannette Holland (SimCorp) who opened an SR resulting in an ER.

DBMS_QOPATCH in Multitenant

DBMS_QOPATCH will deliver useful information about installed patches only when executed within the CDB$ROOT. It has been designed this way for security reasons in Oracle Database 12.1 but I can easily see a need to check for installed patches within a PDB as well.

Testcase

I “borrowed” this test case from Jeannette’s SR:

SQL> COLUMN NAME FORMAT A8

SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

 NAME     CON_ID DBID       CON_UID    GUID
 -------- ------ ---------- ---------- ------------------
 CDB$ROOT      1 3424772713 1          47C8525C0DFE49...
 PDB$SEED      2 3983775695 3983775695 E6204BB1F6EB4F...
 MYPDB1        3 7270044002 7270044002 B975668B860049...
 MYPDB2        4 1943363979 1943363979 BCD7AAFAF3F641...

In a PDB:

ALTER SESSION SET container = myPDB;

Session altered.

SQL> select * from OPATCH_XML_INV ;
 ERROR:
 ORA-29913: error in executing ODCIEXTTABLEOPEN callout
 ORA-29400: data cartridge error
 KUP-04080: directory object OPATCH_LOG_DIR not found

no rows selected

SQL> select dbms_qopatch.get_opatch_install_info from dual;
 ERROR:
 ORA-20001: Latest xml inventory is not loaded into table
 ORA-06512: at "SYS.DBMS_QOPATCH", line 1986
 ORA-06512: at "SYS.DBMS_QOPATCH", line 133

In the CDB:

SQL> ALTER SESSION SET container = cdb$root;
Session altered.

SQL> select * from OPATCH_XML_INV ;

XML_INVENTORY
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <InventoryInstance>

SQL> select dbms_qopatch.get_opatch_install_info from dual;

GET_OPATCH_INSTALL_INFO
--------------------------------------------------------------------------------
<oracleHome><UId>OracleHome-2d1c0910-36ac-429b-98db-96a353d423b6</UId><targetTyp

Solution

There’s no solution available right now for Oracle Database 12.1.0.2. And this behavior does not seem to be documented yet. The SR resulted in an (unpublished) Enhancement Request. In a PDB the following workaround may help in Oracle Database 12.1.0.2:

 select patch_id, patch_uid, version, action, action_time, status, description from dba_registry_sqlpatch;

But this is not as fancy and easy to deal with as an API call to a DBMS package.

I tested in Oracle Database 12.2.0.1 – and there everything seems to work fine there 🙂

SQL>
 create pluggable database PDB3 admin user adm identified by adm
 file_name_convert=( '/u02/oradata/CDB2/pdbseed',
 '/u02/oradata/CDB2/pdb3');

Pluggable database created.

SQL> alter pluggable database pdb3 open;
Pluggable database altered.

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

SQL> select dbms_qopatch.get_opatch_install_info from dual;

GET_OPATCH_INSTALL_INFO
--------------------------------------------------------------------------------
<oracleHome><UId>OracleHome-3cb04a3a-3999-4767-86f1-bc845cab158e</UId><targetTyp

SQL> select * from OPATCH_XML_INV ;

XML_INVENTORY
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<InventoryInstance> <ora

SQL> select xmltransform(dbms_qopatch.get_opatch_lsinventory,
dbms_qopatch.get_opatch_xslt) from dual;

XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY,DBMS_QOPATCH.GET_OPATCH_XSLT)
--------------------------------------------------------------------------------

Oracle Querayable Patch Interface 1.0
-----------------------------------------

SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB3                           READ WRITE NO

Cheers
–Mike

Having fun with PDB LOCKDOWN PROFILES

Posted on by

In Oracle Database 12.2 (available in the Oracle DBaaS Cloud) there’s a new functionality called “PDB LOCKDOWN PROFILES“. Actually the parameter came already in undocumented and not functional in Oracle Database 12.1.0.2 (see here).

PDB Lockdown Profiles

PDB Lockdown Profiles are meant as a granular way to restrict or enable access – mostly under the aspect of using the database in a highly shared environment but requiring security. The idea is to embed restrictions on-top of a grant, i.e. taking away certain things. As an example you could allow a user logged into a certain PDB only to change the optimizer_mode and cursor_sharing parameters when doing an ALTER SYSTEM.

The glossary in the Concepts Guide says:

A security mechanism to restrict operations that are available to local users connected to a specified PDB.

And here is a good overview in the Concepts Guide and the Security Guide for details:

Lockdown Profiles in Oracle Multitenant

Lockdown Profiles – Create, Drop, Alter

How-to-Lockdown-Profile

Lets start with a fresh PDB:

SQL> create pluggable database PDB2 
     admin user adm identified by adm file_name_convert=('/oradata/CDB2/pdbseed', '/oradata/CDB2/pdb2');

First we need to create a lockdown profile:

SQL> create lockdown profile P1;

Then we can ALTER the profile and allow only to change optimizer_mode and cursor_sharing with an ALTER SYSTEM.

SQL> alter lockdown profile P1
      disable statement=('ALTER SYSTEM') clause=('SET') OPTION ALL
       EXCEPT=('optimizer_mode','cursor_sharing');

And finally, the PDB Lockdown Profile needs to be enabled:

SQL> alter system set PDB_LOCKDOWN=P1;

Check:

SQL> show parameter pdb_l

NAME          TYPE    VALUE
------------- ------- ------
pdb_lockdown  string  P1

Where the fun begins …

Now lets connect with my default SYS user – SYS per definitionem is a common user – and switch to my PDB2:

$> sqlplus / as sysdba

SQL> alter session set container=PDB2;

SQL> alter system set sql_trace=TRUE;
*
ERROR at line 1:
ORA-01031: insufficient privileges

Aha! Ok, so let’s try …

SQL> alter system set cursor_sharing='FORCE';
System altered.

SQL> alter system set optimizer_mode='FIRST_ROWS_10';
System altered.

Ok, this works. But can I still change SQL_TRACE on the session level? Of course I can …

SQL> alter session set SQL_TRACE=TRUE;
Session altered.

Makes sense as I restricted only ALTER SYSTEM but not ALTER SESSION.
So let’s do this as well:

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

SQL> alter lockdown profile P1 disable statement=(‘ALTER SESSION’) clause=(‘SET’) OPTION ALL EXCEPT=(‘optimizer_mode’,’cursor_sharing’)
Lockdown Profile altered.

Another tiny exercise taken directly from the documentation:

CREATE LOCKDOWN PROFILE medium;
ALTER LOCKDOWN PROFILE medium DISABLE STATEMENT=('ALTER SYSTEM');
ALTER LOCKDOWN PROFILE medium ENABLE STATEMENT=('ALTER SYSTEM') CLAUSE=('FLUSH SHARED POOL');

This will allow ONLY the ALTER SYSTEM FLUSH SHARED POOL command with ALTER SYSTEM.

SQL>   alter system set pdb_lockdown='MEDIUM';
  alter system set pdb_lockdown='MEDIUM'
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> alter system set sql_trace=true;
alter system set sql_trace=true
*
ERROR at line 1:
ORA-01031: insufficient privileges

And of course you can add more things to an existing profiles such as disabling certain features:

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

SQL> ALTER LOCKDOWN PROFILE medium DISABLE FEATURE=('XDB_PROTOCOLS');
Lockdown Profile altered

Which profiles do exist and what’s defined?

First of all, the parameter PDB_LOCKDOWN is modifiable on a PDB level meaning you can have different profiles for different PDBs. But as far as I tested only one profile per PDB can be active and enabled.

You can look up existing profiles and their contents?

SQL>  select profile_name, rule_type, rule, clause, clause_option, status from DBA_LOCKDOWN_PROFILES order by 1;

PROFILE_NAME   RULE_TYPE  RULE           CLAUSE     CLAUSE_OPTION   STATUS
-------------- ---------- -------------- ---------- --------------- -------
MEDIUM         STATEMENT  ALTER SYSTEM                              DISABLE
MEDIUM         FEATURE    XDB_PROTOCOLS                             DISABLE
P1             STATEMENT  ALTER SESSION  SET        CURSOR_SHARING  ENABLE
P1             STATEMENT  ALTER SYSTEM   SET        OPTIMIZER_MODE  ENABLE
P1             STATEMENT  ALTER SYSTEM   SET        CURSOR_SHARING  ENABLE
P1             STATEMENT  ALTER SESSION  SET        OPTIMIZER_MODE  ENABLE
P1             STATEMENT  ALTER SESSION  SET                        DISABLE
P1             STATEMENT  ALTER SYSTEM   SET                        DISABLE
PRIVATE_DBAAS                                                       EMPTY
PUBLIC_DBAAS                                                        EMPTY
SAAS                                                                EMPTY

11 rows selected.

Summary

This is a very powerful feature – but it can be a bit tricky in the future to find out why certain things don’t work. The ORA-1031 error may now always guide into the correct direction.

One thing which puzzles me a bit:
We speak always about simplifying things, easing administration. And with PDB Lockdown Profiles you can highly complicate things. Or tease your colleagues 🙂 (please don’t, ok?!). Just don’t over-complicate things … 🙂

–Mike

APEX is in CDB$ROOT again – Journey to the Cloud VII

Posted on by

Well … it’s been a while … but I would like to continue my journey to the cloud …

What happened so far on my Journey to the Cloud?

DBaaS Oracle Cloud

Oracle Database 12.2.0.1 in the Cloud

Since November 2016 Oracle Database 12.2.0.1 is available in the Oracle DBaaS Cloud. And I received this question in my inbox yesterday:

I have a customer who wants to migrate Apex 4.2 applications to DBCS and ORDS.war to Weblogic on Compute.

I recently went through your blog on removing Apex from CDB Root. The customer is planning to do something similar but had questions on repercussions of doing so in Oracle Public Cloud.

What are the factors that need to be considered. Also how would DBCS patching work in this scenario.

Is APEX is in CDB$ROOT again

I haven’t checked this yet as we’ve had a very productive conversation with the APEX folks a while back. And I’m 100% sure that the APEX group wasn’t involved in this decision as they recommend clearly in the doc to NOT HAVE APEX installed in the CDB$ROOT 😉
Ok, I did connect to our Cloud environment and … voila …

COLUMN VERSION FORMAT A15
COLUMN NAME FORMAT A12
COLUMN COMP_ID FORMAT A10

SQL> select r.comp_id, r.version, c.name from cdb_registry r, v$containers c where r.con_id=c.con_id and r.comp_id='APEX' order by 3

COMP_ID    VERSION         NAME
---------- --------------- ------------
APEX       5.0.4.00.12     CDB$ROOT
APEX       5.0.4.00.12     ROYPDB1

Ouch …

The presence of APEX in the CDB$ROOT may have to do with the Oracle DBaaS Monitor Console. This is just an assumption but when I removed the APEX in my old 12.1.0.2 cloud deployment I had to clean-up the DBaaS Monitor as well.

See:

for my experience a few months ago.

Solution?

Well, having APEX in the CDB$ROOT is still a brilliant [IRONY!] idea. As soon as you start unplug/plug operations with APEX in the PDB only or with a different APEX version you are asking for trouble.

See this blog post for the potential pitfalls:

Why you should remove APEX from the CDB$ROOT

Which options does the customer have assuming that his APEX 4.2 application is in a non-CDB?

  • Upgrade APEX locally to 5.0.4.00.12 before migrating the database to the DBaaS cloud
    This would be the easiest workaround if it wouldn’t involve an application software upgrade. Through the Oracle glasses this looks simple – but from a customer’s and application developer’s standpoint this isn’t trivial as most likely it will involve application testing
  • Export the APEX application and import it
    I haven’t done this by myself but first of all with APEX 4.2 (or below) you must take care to move the image files as well – and you’ll have to move data as well. And, of course, you won’t end up in APEX 4.2 but in APEX 5.0 so the above mentioned application upgrade will hit you as well. I don’t see any benefit over solution 1.
    See Jason Straub’s blog post about:
    Convert Common Oracle Application Express in a CDB to Local APEX in PDBs
  • Remove APEX from the 12.2.0.1 DBaaS Cloud deployment’s CDB$ROOT
    This is – in my humble opinion – the only viable solution here if the customer can’t upgrade APEX in the current environment for whatever reason. But this will most likely remove the DBaaS Monitor as well. I can live without it but I know that it offers a lot of good stuff especially when dealing with encrypted tablespaces which is otherwise hard to handle on the command line. The good part of this solution is the freedom and flexibility you’ll get once APEX is removed from the CDB$ROOT for unplug/plug operations in the future.
    The ugly part: this is hanging in my deployment and can’t be solved right now. I’m waiting for a newer DBaaS deployment release and will retest again.

Finally, regarding patching:
I don’t see any issues. And the same for a future upgrade as we decouple APEX upgrades from the database upgrade with Oracle Database 12.2.0.

–Mike

SPFILE Parameter: max_pdbs – a must for Single Tenant

Posted on by

Sometimes my job has a aspect making me smile at the end of the day 😉

I sat together with Johannes Ahrends during a talk at the OUGN Conference on the boat from Oslo towards Kiel. And we were discussing afterwards why there’s no official way to limit the number of PDBs which will be essential for customers wanting to go the Single Tenant track. I had my Hands-On environment up and we played a bit in the break recognizing that a constraint on CONTAINER$ won’t be the correct solution as unplug/plug operations leave leftovers in it unless you DROP PLUGGABLE DATABASE. And even if you drop the remains the constraint solution does not work.

At the breakfast the next morning Johannes mentioned a trigger – and he published one soon after. But it is not a good idea in terms of keeping support for your database when you fiddle around with the data dictionary.

So I did ask my contacts internally and received a message saying clearly
“Somebody doesn’t want this.“. 🙂
Well, I work long enough for Oracle to know how to read it. It’s a common thing to blame it on “Somebody” when you don’t want to discuss things further. One can hide perfectly well behind “Somebody“.

The higher my surprise was when I started testing and playing with Oracle Database 12.2 – and collecting init.ora parameters between release labels to detect changes and additions. And apparently, this one here appeared:

as a new parameter in Oracle Database 12.2 saying “max number of pdbs allowed in CDB or Application ROOT” in its parameter description.
I was (a) surprised and (b) happy and (c) had to try it out immediately in my environment where I had already 3 PDBs (the PDB$SEED does not count):

SQL> alter system set max_pdbs=3;
System 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      CLONE_PDB              MOUNTED

SQL> alter system set max_pdbs=2;
alter system set max_pdbs=2
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-65331: DDL on a data link table is outside an application action.
.

So even though the error message is a bit rough (at least in my release drop) the parameter does what we want. In a single tenant environment you’ll set it to “1” and prevent the creation or plugin of a 2nd PDB in this container database..

Another test with a fresh container database:

SQL> show pdbs

CON_ID CON_NAME      OPEN MODE  RESTRICTED
------ ------------- ---------- ----------
2      PDB$SEED      READ ONLY  NO


SQL> alter system set max_pdbs=1;
System altered.

SQL> show parameter max_pdbs

NAME                     TYPE     VALUE
------------------------ -------- ----- 
max_pdbs                 integer      1


SQL> create pluggable database pdb1 admin user adm identified by adm file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb1');
Pluggable database created.

SQL> create pluggable database pdb2 admin user adm identified by adm file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb2');

create pluggable database pdb2 admin user adm identified by adm file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb2')
*
ERROR at line 1:
ORA-65010: maximum number of pluggable databases created

SQL> drop pluggable database pdb1 including datafiles;
Pluggable database dropped.

SQL> create pluggable database pdb2 admin user adm identified by adm file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb2');
Pluggable database created.

Looks like a solid solution to me.

But please see also this blog post by Oracle ACE Director Franck Pachot about an issue with the parameter:
http://blog.dbi-services.com/oracle-12cr2-max_pdbs/

–Mike