Issue with PDB Archives in Oracle 12.2.0.1 in ASM

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

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

I did blog about this topic a while back.

Upgrade PDBs – Everything At Once (Full CDB Upgrade)

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

My test setup

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

SQL> show pdbs

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

Run preupgrade,jar

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

The output does look like:

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


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

Upgrade-To version: 12.2.0.1.0

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

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

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


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

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

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

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

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

   + Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.

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

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

  RECOMMENDED ACTIONS
  ===================
   + (AUTOFIXUP) Gather stale data dictionary statistics prior to database
     upgrade in off-peak time using:

      EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

     Dictionary statistics do not exist or are stale (not up-to-date).

     Dictionary statistics help the Oracle optimizer find efficient SQL
     execution plans and are essential for proper upgrade timing. Oracle
     recommends gathering dictionary statistics in the last 24 hours before
     database upgrade.

     For information on managing optimizer statistics, refer to the 12.1.0.2
     Oracle Database SQL Tuning Guide.

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

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

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

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

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

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

   + (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
     command:

       EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

     Oracle recommends gathering dictionary statistics after upgrade.
     Dictionary statistics provide essential information to the Oracle
     optimizer to help it find efficient SQL execution plans. After a
     database upgrade, statistics need to be re-gathered as there can now be
     tables that have significantly changed during the upgrade or new tables
     that do not have statistics gathered yet.


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

Upgrade-To version: 12.2.0.1.0

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

      Database Name:  CDB1
     Container Name:  PDB$SEED
       Container ID:  2
            Version:  12.1.0.2.0
         Compatible:  12.1.0.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  18
  Database log mode:  NOARCHIVELOG
           Readonly:  TRUE
            Edition:  EE

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


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

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

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

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

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

  RECOMMENDED ACTIONS
  ===================
   + (AUTOFIXUP) Gather stale data dictionary statistics prior to database
     upgrade in off-peak time using:

      EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

     Dictionary statistics do not exist or are stale (not up-to-date).

     Dictionary statistics help the Oracle optimizer find efficient SQL
     execution plans and are essential for proper upgrade timing. Oracle
     recommends gathering dictionary statistics in the last 24 hours before
     database upgrade.

     For information on managing optimizer statistics, refer to the 12.1.0.2
     Oracle Database SQL Tuning Guide.

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

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

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

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

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

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

   + (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
     command:
       EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

     Oracle recommends gathering dictionary statistics after upgrade.

     Dictionary statistics provide essential information to the Oracle
     optimizer to help it find efficient SQL execution plans. After a
     database upgrade, statistics need to be re-gathered as there can now be
     tables that have significantly changed during the upgrade or new tables
     that do not have statistics gathered yet.


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

Upgrade-To version: 12.2.0.1.0

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

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

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

...

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

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

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

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

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

Pre Upgrade

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

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

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

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

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

Multitenant database upgrade

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

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

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

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

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

Pluggable database altered.

SQL> show pdbs

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

SQL> exit

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

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

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

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

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

Argument list for [catctl.pl]
Run in                c = 0
Do not run in         C = 0
Input Directory       d = 0
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = 0
Child Process         I = 0
Log Dir               l = /home/oracle/mike
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 6
SQL PDB Process Count N = 0
Open Mode Normal      o = 0
Start Phase           p = 0
End Phase             P = 0
Reverse Order         r = 0
AutoUpgrade Resume    R = 0
Script                s = 0
Serial Run            S = 0
RO User Tablespaces   T = 0
Display Phases        y = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0

catctl.pl VERSION: [12.2.0.1.0]
           STATUS: [production]
            BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125]


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

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

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

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

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

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

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

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

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

Post Upgrade

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

The status of my database after upgrade and before recompilation:

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

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


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

SQL> show pdbs

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

Pluggable database altered.

SQL> show pdbs

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

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

SQL> alter session set "_exclude_seed_cdb_view"=false;

Session altered.

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

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

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

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

Afterwards check if all the PDBs are OPEN UNRESTRICTED now.

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

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


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

SQL> show pdbs

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

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

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

And a final check:

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

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

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

–Mike

DBMS_QOPATCH does not work in PDBs (right now)

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

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

Gather Fixed Objects Stats in PDBs as well?

Yesterday I received an interesting customer question:

“Do we have to gather fixed objects stats inside a PDB as well or only in the CDB$ROOT?”

The customer told me he couldn’t find anything in the documentation. Neither could I. And I did check also the usual suspects this morning “Best Practices for Gathering Statistics” – but it does not say anything about PDBs and CDBs.

Therefore I did a short test:

  • Create a PDB in my Oracle 12.1.0.2 CDB:
    SQL> create pluggable database PDB3
         admin user adm identified by adm
         file_name_convert=( '/u02/oradata/CDB1/pdbseed', '/u02/oradata/CDB1/pdb3');
  • Open the PDB:
    SQL> alter pluggable database PDB3 open;
  • Check for missing stats on X$ tables:
    SQL> select count(*) from sys.dba_tab_statistics
         where owner='SYS' and table_name like 'X$%' and  last_analyzed is not null;
    
    Result in CDB$ROOT: 0
    Result in PDB3:     0
  • Gather Fixed Objects Stats only in PDB3:
    SQL> exec dbms_stats.gather_fixed_objects_stats;
  • Check the results with:
    SQL> select count(*) from sys.dba_tab_statistics
         where owner='SYS' and table_name like 'X$%' and  last_analyzed is not null;
    Result in CDB$ROOT:    0
    Result in PDB3:     1025

    Gather Fixed Objects Stats in CDB$ROOT:

     SQL> exec dbms_stats.gather_fixed_objects_stats;
  • Check the results again with:
    SQL> select count(*) from sys.dba_tab_statistics
         where owner='SYS' and table_name like 'X$%' and  last_analyzed is not null;
    
    Result in CDB$ROOT: 1027
    Result in PDB3:     1025

Conclusion

Yes, you’ll have to gather fixed objects stats in PDBs independently from the CDB$ROOT.
But generally in Oracle Database 12c the Automatic Statistics Gathering job will take care on Fixed Objects Stats as well (see here). In a Multitenant environment you just may have to take care to unfold your default maintenance windows as otherwise this will happen in all PDBs at the same time generating plenty of noise on your system.

Oracle Database automatically gathers fixed object statistics as part of automated statistics gathering if they have not been previously collected (see Controlling Automatic Optimizer Statistics Collection).

–Mike

PDB unplug/plug/patch with PSUs or BPs

This question was posted on the blog a few days ago referring to my previous blog posts describing the two techniques to upgrade in an Oracle Multitenant environment:

Question: 

We are planning to upgrade from 12.1.0.2.1 to 12.1.0.2.5.
The plan is to create new 12.1.0.2.5 oracle home and
gradually moving PDB from 12.1.0.2.1 to 12.1.0.2.5.
Should I follow the same steps?
Is there any document for what we want to do?

And luckily there is a MOS Note out there describing the steps:

The part this note is not talking about is the unplug/plugin operations in detail.
But this is something you’ll find here:

Upgrade PDBs – One at a Time (unplug/plug)

–Mike

Why does a PDB require an upgrade?

Why do I need to upgrade (or downgrade) a Pluggable Database (PDB) once I unplug it from version A and replug into version B?

This question is one of the most often asked questions in our workshops when Roy and I present Upgrades and Oracle Multitenant.

If you take a look into the documentation you’ll find:

But unfortunately this is not 100% correct for every object.

When you do a simple query for basic dictionary structures such as OBJ$ you’ll easily find out that a PDB has its own Data Dictionary.  Some things are really linked into the PDB only – check the SHARING column of DBA_OBJECTS for instance. If it says “METADATA LINK” it will give you an indication that it exchanges information with its parent in the CDB$ROOT. But that doesn’t mean necessarily that it’s just an empty meta object. Some tables do exist in the PDB as well and allocate space. Check it by yourself and you’ll find out that OBJ$ inside a PDB has in fact the same attribute – but still owns segments in the PDB and in the CDB$ROOT – as other objects do allocate space as well. And of course the object’s definition exists in the PDB as well.

  • CDB$ROOT
    OBJ$ in CDB$ROOT
  • PDB
    OBJ$ in PDB

So the answer is simple:

A Pluggable Database (PDB) must be upgraded (or downgraded) when it get moved between versions simply because it has its own data dictionary. And this doesn’t get upgraded automatically.

–Mike

Recent News about Pluggable Databases – Oracle Multitenant

Three recent lessons about PDBs in the Oracle Single/Multitenant space you should be aware of.

And thanks to my teammates and the Multitenant PMs for bringing this into our radar.

Unplug/plug – don’t forget to DROP your PDB

I’ve had to add a single line to my previous blog post about the upgrade solution Unplug/Plug/Upgrade for PDBs:
https://mikedietrichde.com/2014/08/05/upgrade-pdbs-one-at-a-time-unplugplug/

Unplug Plug Upgrade PDB Oracle Mutitenant

You’ll have to DROP your PDB after you have unplugged it as otherwise the information will stay in the CDB’s dictionary where you have unplugged it from (a) forever and (b) during an subsequent upgrade of the entire source CDB. But the latter will cause trouble as catcon.pl, the PERL script to execute sql code in all the containers, will try to open the PDB you have unplugged a while ago as the information about it is still kept. To me this looks like an undesired behavior but there’s discussion going on internally about it.

 	alter pluggable database PDB1 close;
 	alter pluggable database PDB1 unplug into '/stage/pdb1.xml';
 	drop pluggable database PDB1 keep datafiles;

If you don’t issue the command marked in yellow you’ll get yourself in trouble the sooner or later unless this CDB will be deleted afterwards anyways as it was just meant to hold this single PDB.

Unplug/plug – take care on your backup

If you’d use the above procedure for Unplug/Plug/Upgrade (or even just Unplug/Plug without upgrade from one CDB into another) be aware that you will need to take a backup of your PDB right after the upgrade has been finished. This is something which is pretty clear and obvious but it doesn’t jump into your face when you don’t think about it – and it’s not mentioned in the docs as far as I know.

The previous backup is useless as you won’t be able to use a backup of PDB1 taken on CDB1 to recover PDB1 into CDB2. Therefore a backup taken directly after the upgrade or plug in has to be scheduled immediately – it’s a must and needs to be considered into your maintenance plans.

Every PDB must have its own TEMP tablespace

This one is fairly new to me [thanks Hector!].
MOS Note: 2004595.1 (PDB to Use Global CDB (ROOT) Temporary Tablespace Functionality is Missing

Basically this means that you’ll be unable to drop the local temporary tablespace of a PDB and instead use the global temporary tablespace (the one in CDB$ROOT). This is documented as a functionality which is described in the docs but does not exist right now. It is logged under Bug17554022. No major issue but I’ve a intense discussion with Johannes Ahrends a while back at the DOAG conference about it – so others saw this issue as well.

–Mike

Create Container Databases (CDB) with less options – it’s now supported in Oracle 12.1.0.2

When Oracle Multitenant was launched Roy and I amongst many other people always mentioned that the requirement of having all options in a Container Database (CDB$ROOT), and therefore also for the PDB$SEED with the immediate result that all PDBs provisioned from the PDB$SEED will have all options as well, will hinder customer adoption significantly.

Almost all customers I have talked to in the past 3-4 years about Oracle Multitenant mentioned immediately that it will be a huge problem for them to install all options as (1) their policy is to install only things they are licensed for to (2) prevent developers, users and DBAs to use things accidentally without even knowing that this or that will require a license.

As it is not allowed to manipulate and change the PDB$SEED the workaround – as PDBs were allowed to have less options – has been to create a stand-alone Oracle 12c database with exactly the options you’d like to have configured as your gold standard – and then plug it in under a remarkable name, for instance PDB$MASTER. Switch it to read only and make sure from now on you’ll provision a new PDB always as a clone from PDB$MASTER, and not from PDB$SEED.

All Options in a CDB

That would have even worked in the Single Tenant case, which does not require licensing the Oracle Multitenant option and where you have only one active (“customer-created PDB”) PDB. For this purpose you would have unplugged your PDB$MASTER after making it a pluggable database and provision new PDBs with just your desired options set as plugging in PDB$MASTER under a new name (e.g. PDB26) using the COPY option of the command.

Now this will become all obsolete as from now you it is allowed to have a CDB installation with less options. This applies to linked kernel modules (e.g. RAT) as well as to configured database components (e.g. JAVA, OWM, SPATIAL etc).

Please see the following new/rephrased MOS Notes:

MOS Note:2001512.1 basically explains the following steps:

  • Do all the click work in DBCA (Database Creation Assistant) to create a container database – but let DBCA only create the scripts
  • Edit the <SID>.sql script and remove the unwanted options according to the dependency table in the MOS Note
  • Edit the CreateDBCatalog.sql in case you want to remove OWM (Oracle Workspace Manager) creation as well
  • Add the Oracle PERL $ORACLE_HOME/perl/bin in front of your $PATH variable
  • Start the <SID>.sh script on the shell prompt

Here’s an example of a CreateDBCatalog.sql and a XXXX.sql creating a CDB with no options except XDB (which is mandatory in Oracle Database 12c):

cat CreateDBCatalog.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/XXXX/scripts/CreateDBCatalog.log
append
alter session set "_oracle_script"=true;
alter pluggable database pdb$seed close;
alter pluggable database pdb$seed open;
host perl
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1
-l /u01/app/oracle/admin/XXXX/scripts -b catalog
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catalog.sql;
host perl
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1
-l /u01/app/oracle/admin/XXXX/scripts -b catproc
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catproc.sql;
host perl
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1
-l /u01/app/oracle/admin/XXXX/scripts -b catoctk
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catoctk.sql;
-- host perl
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n
1 -l /u01/app/oracle/admin/XXXX/scripts -b owminst
/u01/app/oracle/product/12.1.0.2/rdbms/admin/owminst.plb;
host perl
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1
-l /u01/app/oracle/admin/XXXX/scripts -b pupbld -u
SYSTEM/&&systemPassword
/u01/app/oracle/product/12.1.0.2/sqlplus/admin/pupbld.sql;
connect "SYSTEM"/"&&systemPassword"
set echo on
spool /u01/app/oracle/admin/XXXX/scripts/sqlPlusHelp.log
append
host perl
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1
-l /u01/app/oracle/admin/XXXX/scripts -b hlpbld -u
SYSTEM/&&systemPassword -a 1
/u01/app/oracle/product/12.1.0.2/sqlplus/admin/help/hlpbld.sql
1helpus.sql;
spool off
spool off
cat XXXX.sql

set verify off
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
host /u01/app/oracle/product/12.1.0.2/bin/orapwd file=/u01/app/oracle/product/12.1.0.2/dbs/orapwXXXX force=y format=12
@/u01/app/oracle/admin/XXXX/scripts/CreateDB.sql
@/u01/app/oracle/admin/XXXX/scripts/CreateDBFiles.sql
@/u01/app/oracle/admin/XXXX/scripts/CreateDBCatalog.sql
-- @/u01/app/oracle/admin/XXXX/scripts/JServer.sql
-- @/u01/app/oracle/admin/XXXX/scripts/context.sql
-- @/u01/app/oracle/admin/XXXX/scripts/ordinst.sql
-- @/u01/app/oracle/admin/XXXX/scripts/interMedia.sql
-- @/u01/app/oracle/admin/XXXX/scripts/cwmlite.sql
-- @/u01/app/oracle/admin/XXXX/scripts/spatial.sql
-- @/u01/app/oracle/admin/XXXX/scripts/labelSecurity.sql
-- @/u01/app/oracle/admin/XXXX/scripts/apex.sql
-- @/u01/app/oracle/admin/XXXX/scripts/datavault.sql
-- @/u01/app/oracle/admin/XXXX/scripts/CreateClustDBViews.sql
@/u01/app/oracle/admin/XXXX/scripts/lockAccount.sql
@/u01/app/oracle/admin/XXXX/scripts/postDBCreation.sql
@/u01/app/oracle/admin/XXXX/scripts/PDBCreation.sql
@/u01/app/oracle/admin/XXXX/scripts/plug_PDB.sql
@/u01/app/oracle/admin/XXXX/scripts/postPDBCreation_PDB.sql

This results in a database having only these components – the minimal component set in Oracle 12.1.0.2:

COMP ID  NAME
-------- --------------------------------------
CATALOG  Oracle Database Catalog View
CATPROC  Oracle Database Packages and Types
XDB      Oracle XML Database

–Mike

Applying a PSU or BP to a Single-/Multitenant Environment

I have already explained in broad details a while ago how to:

But one may miss the steps for applying a PSU (Patch Set Update) or BP (Bundled Patch) to a Single-/Multitenant Environment.

At first everything will work just the same if you choose the Everything-at-Once strategy as datapatch will adjust all the required things regardless of being executed in a stand-alone or a singe/Multitenant environment.

But what happens if you apply a PSU or a BP to one of your Multitenant environments and want to move PDBs one after another (or a few at the same time) to the new environment?
Or revert a PSU by plugging out from a CDB with the PSU inside – and plug it back into a CDB with a lower version or no PSU at all?

First step – Check Plug In Compatibility 

Before you can even start your unplug/plug operation you should always perform the plugin check. This is divided in two simple steps:

  1. Create the XML description file for your PDB in CDB_SOURCE
    exec DBMS_PDB.DESCRIBE ('/home/oracle/PDB1_unplug.xml', 'PDB1');
  2. Run the plug in check in CDB_DEST
    begin
    if DBMS_PDB.CHECK_PLUG_COMPATIBILITY('/home/oracle/PDB1_unplug.xml','PDB1') then
    DBMS_OUTPUT.PUT_LINE('No violations found - you can relax');
    else
    DBMS_OUTPUT.PUT_LINE('Violations found - check PDB_PLUG_IN_VIOLATIONS');
    end if;
    end;
    /

No Plugin Violations?

Then please follow the procedure described in:
https://mikedietrichde.com/2014/08/05/upgrade-pdbs-one-at-a-time-unplugplug/
without the upgrade part as you don’t need to upgrade anything in this case of course.

Higher patch in CDB_DEST than in CDB_SOURCE?

Then run this query:

select TYPE,MESSAGE,ACTION from PDB_PLUG_IN_VIOLATIONS where NAME='PDB1';

It will tell you to execute datapatch:

TYPE    MESSAGE
------  ----------------------------------------------------------------------------
ERROR   PSU bundle patch 1 (PSU Patch 4711): Installed in the CDB but not in the PDB

ACTION
------------------------------------------------
Call datapatch to install in the PDB or the CDB

Lower patch in CDB_DEST than in CDB_SOURCE?

Now this becomes a bit more tricky. See the output of PDB_PLUG_IN_VIOLATIONS:

TYPE  MESSAGE
----- ----------------------------------------------------------------------------
ERROR PSU bundle patch 1 (PSU Patch 4711): Installed in the PDB but not in the CDB

ACTION
------------------------------------------------
Call datapatch to install in the PDB or the CDB

Huh? Install???
What does this mean? Should I install now the current CDB/PDB’s PSU into my target environment before being able to step down?

Actually I think this message is misleading. And when you look into the MyOracle Support Note describing this under scenario 3 (MOS Note:1935365.1 – Multitenant Unplug/Plug Best Practices) you’ll see that the author silently assumed as well that is is more likely that you’ll remove the patch from the PDB.

But how do you remove changes which came in with datapatch from within a PDB only?

You will need to run datapatch -rollback on the affected PDBs only:

$> datapatch -rollback <patch id> –force [–bundle_series] -pdbs <pdb1,pdb2,...,pdbn>

For further information see:

–Mike