Direct PDB upgrade to 12.1.0.2 , myth or reality ?

Workshops, Oracle Events and Conferences about 12C Multitenant features let us think of a direct migration path for the next release. We hope this should be done in very few steps :

1)  New binary installation on new ORACLE HOME
2) New root container creation  with new binary
3) Target PDB  shutdown (running previous release)
4) Unplug  target PDB from previous release container
5) Plug target PDB on new root container
6)  Open target PDB (new release)
That’s it , quick, simple and straitght forward.  Downtime is reduce to database files copy between steps  3) and 6) and even that copy is not necessary if you doesn’t need to !  A dream for DBAs.
As Oracle stuff dictionnary (mainly tight to the release)  is on the root container,  this could be true and now that patchset 12.1.0.1 is out there, let see how it really works.
Environment Setup:

  • Linux  64bits (Virtual Box) using OEL6.1
  • Oracle Database Enterprise Edition 12.1.0.1 installed
  • Container cdbELE release12.1.2.0.1 up and running (ORACLE_HOME=/u01/app/oracle/product/12.1.0.1)
  • PDB database ORACLE_ID= PDBELE plugged  on cdbELE  (Order Entry schema sample)
  • Oracle Database Enterprise Edition installed on new ORACLE_HOME=/u01/app/oracle/product/12.1.0.2
  • Container cdb1212 created empty using new binary

Step 1) and 2) l are done so lets go further :
Step  3) Target PDB shutdown

Little addon to the process,  Target pre upgrade checkup. This could be useful if something wrong is detected before the upgrade . The script is preupgrd.sql  and must be running from directory new  $ORACLE_HOME/rdbms/admin
–  Administrator account on root container release  12.1.0.1 : cdbELE

[oracle@ele1ole6 ~]$ . oraenv
ORACLE_SID = [oracle] ? cdbELE
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ele1ole6 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 14 09:28:30 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show pdbs     CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO        
         3 PDBELE                         READ WRITE NO
SQL> alter session set container=pdbELE ;
SQL> @/u01/app/oracle/product/12.1.0.2/rdbms/admin/preupgrd.sql

Output directed to the logfile:

  • All componants are VALID
  • No errors
  • No warning
  • One informational message for APEX release wich may be upgraded.
…/…
******************************************************************
                          [Pre-Upgrade Checks]
**********************************************************************
INFORMATION: --> Oracle Application Express (APEX) can be
     manually upgraded prior to database upgrade
     APEX is currently at version 4.2.0.00.27 and will need to be
     upgraded to APEX version 4.2.5 in the new release.
     Note 1: To reduce database upgrade time, APEX can be manually
             upgraded outside of and prior to database upgrade.
     Note 2: See MOS Note 1088970.1 for information on APEX
             installation upgrades.
…/…

We feel ready for the  jump.
– Shutdown of target  PDBELE  on original root container :

SQL> alter session set container=CDB$ROOT ;
SQL> alter pluggable database pdbELE close ;
SQL> show pdbs
2 PDB$SEED                       READ ONLY  NO
3 PDBELE                         MOUNTED

Step 4) Unplug target PDB from previous release container

Only one SQL command, wich  generates quickly  one database description on the specified xml file :

SQL> alter pluggable database pdbELE unplug into '/home/oracle/pdbELE.xml' ;

Once the PBD is unplug, the only option in this container is to suppress pdbELE using the « DROP » clause . If you need to reaccess the database, you have to repluged it using specifics options.
Step 5) Plug target PDB on new root container CDB1212
Before that,  we can use DBMS_PDB.CHECK_PLUG_COMPATIBILTY()  to validate the plugin action :
Connected on root container  CDB1212:

[oracle@ele1ole6 ~]$ . oraenv
ORACLE_SID = [oracle] ? cdb1212
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ele1ole6 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 14 09:40:30 2014 Copyright (c) 1982, 2013, 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> set serveroutput on
SQL> DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/home/oracle/pdbELE.xml' ,
pdb_name => 'PDBELE' )
WHEN TRUE THEN 'YES' ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/

First bad sign,  the ouput result is  : NO ,  more details from view  PDB_PLUG_IN_VIOLATIONS  :

SQL> select message, status from pdb_plug_in_violations where type like '%ERR%';
MESSAGE                                                                                           STATUS
---------------------------------------------------------------------------------------------------------
PDB's version does not match CDB's version: PDB's version 12.1.0.0.0. CDB's version 12.1.0.2.0.   PENDING
APEX mismatch: PDB installed version 4.2.0.00.27 CDB installed version 4.2.5.00.08                PENDING

The APEX warning is ok,  we can delete and reinstall any release using instructions described on note MOS 558340.1.  But the PENDING status of PDB’s version warn me about my theory !  Let’s get a try anyway.:
– Create pluggable database :
we set  “using <xml file>”  and  “file_name_convert”  for database localisatiion, ny default files are copied over and not moved (that means original files are not impacted, wich may be useful later on) :

SQL> create pluggable database pdbELE using '/home/oracle/pdbELE.xml'
file_name_convert=('/u01/oradata/cdbELE/pdbELE','/u03/oradata/cdb1212/pdbELE') ;

Result  :  “Pluggable database created.“
There’s still little hope, it’s the moment of thruth.
Step  6)  Open PDB in read / write

SQL> alter pluggable database PDBELE open ;
Warning: PDB altered with errors.

Oups, that’s not what i expect !  The theory seems wrong !
in fact the PDB is open but in mode « MIGRATE » and « RESTRICTED »,  no access for applications :

SQL> show pdbs
CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED                       READ ONLY  NO
3 PDBELE                         MIGRATE    YES

I’s the same command result :

SQL>  alter pluggable database pdbele open upgrade ;

Proofs are on alert log file :

ORA-65000 signalled during: alter pluggable database open ...
2014-08-14 10:30:58.166000 +02:00
alter pluggable database PDBELE open
Pluggable database PDBELE dictionary check beginning
Pluggable Database PDBELE Dictionary check complete
Database Characterset for PDBELE is WE8MSWIN1252
***************************************************************
WARNING: Pluggable Database PDBELE with pdb id - 3 is
altered with errors or warnings. Please look into
PDB_PLUG_IN_VIOLATIONS view for more details.
***************************************************************
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32
2014-08-14 10:30:59.665000 +02:00
Opening pdb PDBELE (3) with Resource Manager plan: DEFAULT_PLAN
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY;
Resource Manager disabled during database migration: plan '' not set
ALTER SYSTEM SET resource_manager_plan= SCOPE=MEMORY;
ALTER SYSTEM SET recyclebin='OFF' DEFERRED SCOPE=MEMORY;
Pluggable database PDBELE opened read write
Completed: alter pluggable database PDBELE open

The error details are the same as when we did the pluggin check  on the view  PDB_PLUG_IN_VIOLATIONS . Clearly the two dictionnary are not compatible due to version mismatch.

SQL> select message, status from pdb_plug_in_violations where type like '%ERR%';
MESSAGE                                                                                           STATUS
---------------------------------------------------------------------------------------------------------
PDB's version does not match CDB's version: PDB's version 12.1.0.1.0. CDB's version 12.1.0.2.0.   PENDING

Plug out, plug in is not enough , forget  straight migration. The links between own Oracle Objects in the root container those  and access from the PDB need  to be rebuild. The initial hypthesis is false:  there is two dictionnary not one and even if it’s links and not copy, there is job to be done !
To get out of that, no otherway than add one upgrade step :
Step 6) become : PDB dictionnary Upgrade

The tool is the perl named catctl.pl  the one from the new release, the same we use for non multitenant database, the one we check during this journey (url link  vers la version anglaise)
It’s located under  $ORACLE_HOME/rdbms/admin of the new release, and need a destination directory if you want logging to files. I used with the container name passe to « -c » option :

[oracle@ele1ole6 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@ele1ole6 admin]$ mkdir /home/oracle/upgradePDBELE
[oracle@ele1ole6 admin]$ /u01/app/oracle/product/12.1.0.2/perl/bin/perl catctl.pl -c "PDBELE" -l /home/oracle/upgradePDBELE catupgrd.sql

The output is  :

Argument list for [catctl.pl]
SQL Process Count n = 0
SQL PDB Process Count N = 0
Input Directory d = 0
Phase Logging Table t = 0
Log Dir l = /home/oracle/upgradePDBELE
Script s = 0
Serial Run S = 0
Upgrade Mode active M = 0
Start Phase p = 0
End Phase P = 0
Log Id i = 0
Run in c = PDBELE
Do not run in C = 0
Echo OFF e = 1
No Post Upgrade x = 0
Reverse Order r = 0
Open Mode Normal o = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
Display Phases y = 0
Child Process I = 0
catctl.pl version: 12.1.0.2.0
Oracle Base = /u01/app/oracle
Analyzing file catupgrd.sql
Log files in /home/oracle/upgradePDBELE
catcon: ALL catcon-related output will be written to /home/oracle/upgradePDBELE/catupgrd_catcon_3693.lst
catcon: See /home/oracle/upgradePDBELE/catupgrd*.log files for output generated by scripts
catcon: See /home/oracle/upgradePDBELE/catupgrd_*.lst files for spool files, if any
Number of Cpus = 1
Parallel PDB Upgrades = 2
SQL PDB Process Count = 2
SQL Process Count = 0
New SQL Process Count = 1
[CONTAINER NAMES]
CDB$ROOT
PDB$SEED
PDBELE
PDB Inclusion:[PDBELE] Exclusion:[]
Starting
[/u02/app/oracle/product/12.1.0.2/perl/bin/perl catctl.pl -c 'PDBELE' -l /home/oracle/upgradePDBELE -I -i pdbele -n 2 catupgrd.sql]
Argument list for [catctl.pl]
SQL Process Count n = 2
SQL PDB Process Count N = 0
Input Directory d = 0
Phase Logging Table t = 0
Log Dir l = /home/oracle/upgradePDBELE
Script s = 0
Serial Run S = 0
Upgrade Mode active M = 0
Start Phase p = 0
End Phase P = 0
Log Id i = pdbele
Run in c = PDBELE
Do not run in C = 0
Echo OFF e = 1
No Post Upgrade x = 0
Reverse Order r = 0
Open Mode Normal o = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
Display Phases y = 0
Child Process I = 1
catctl.pl version: 12.1.0.2.0
Oracle Base = /u01/app/oracle
Analyzing file catupgrd.sql
Log files in /home/oracle/upgradePDBELE
catcon: ALL catcon-related output will be written to /home/oracle/upgradePDBELE/catupgrdpdbele_catcon_3775.lst
catcon: See /home/oracle/upgradePDBELE/catupgrdpdbele*.log files for output generated by scripts
catcon: See /home/oracle/upgradePDBELE/catupgrdpdbele_*.lst files for spool files, if any
Number of Cpus = 1
SQL PDB Process Count = 2
SQL Process Count = 2
[CONTAINER NAMES]
CDB$ROOT
PDB$SEED
PDBELE
PDB Inclusion:[PDBELE] Exclusion:[]
------------------------------------------------------
Phases [0-73]
Container Lists Inclusion:[PDBELE] Exclusion:[]
Serial Phase #: 0 Files: 1 Time: 10s PDBELE
Serial Phase #: 1 Files: 5 Time: 39s PDBELE
.../...
Serial Phase #:62 Files: 1 Time: 126s PDBELE
Restart Phase #:63 Files: 1 Time: 0s PDBELE
Serial Phase #:64 Files: 1 Time: 0s PDBELE
Serial Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u02/app/oracle/product/12.1.0.2/lib; export LD_LIBRARY_PATH;/u02/app/oracle/product/12.1.0.2/perl/bin/perl -I /u02/app/oracle/product/12.1.0.2/rdbms/admin -I /u02/app/oracle/product/12.1.0.2/rdbms/admin/../../sqlpatch /u02/app/oracle/product/12.1.0.2/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only -pdbs PDBELE > /home/oracle/upgradePDBELE/catupgrdpdbele_datapatch_upgrade.log 2> /home/oracle/upgradePDBELE/catupgrdpdbele_datapatch_upgrade.err
returned from sqlpatch
Time: 10s PDBELE
Serial Phase #:66 Files: 1 Time: 3s PDBELE
Serial Phase #:68 Files: 1 Time: 3s PDBELE
Serial Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u02/app/oracle/product/12.1.0.2/lib; export LD_LIBRARY_PATH;/u02/app/oracle/product/12.1.0.2/perl/bin/perl -I /u02/app/oracle/product/12.1.0.2/rdbms/admin -I /u02/app/oracle/product/12.1.0.2/rdbms/admin/../../sqlpatch /u02/app/oracle/product/12.1.0.2/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -pdbs PDBELE > /home/oracle/upgradePDBELE/catupgrdpdbele_datapatch_normal.log 2> /home/oracle/upgradePDBELE/catupgrdpdbele_datapatch_normal.err
returned from sqlpatch
Time: 11s PDBELE
Serial Phase #:70 Files: 1 Time: 79s PDBELE
Serial Phase #:71 Files: 1 Time: 11s PDBELE
Serial Phase #:72 Files: 1 Time: 5s PDBELE
Serial Phase #:73 Files: 1 Time: 0s PDBELE
Grand Total Time: 1355s PDBELE
LOG FILES: (catupgrdpdbele*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/12.1.0.2/cfgtoollogs/cdb1212/upgrade/upg_summary.log
Total Upgrade Time: [0d:0h:22m:35s]
Time: 1358s For PDB(s)
Grand Total Time: 1358s
LOG FILES: (catupgrd*.log)
Grand Total Upgrade Time: [0d:0h:22m:38s]

It takes almost as long as for a classical (non mutitenant) database and it’s a pity !
I had a hope that rebuild links could be quicker than updates and inserts.
Once it’s over you can verify there is no error in the log files and go to the last step : opening the PDB database to the world.
Beware that the PDB is closed at  the end , check the alert logfile :

2014-08-14 11:40:12.656000 +02:00
SERVER COMPONENT id=POSTUP_END: timestamp=2014-08-14 11:40:12 Container=PDBELE Id=3
2014-08-14 11:40:28.333000 +02:00
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE
2014-08-14 11:40:31.680000 +02:00
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
Pluggable database PDBELE closed
Completed: ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE

Step 7)   PDB opening and last objects recompilation

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

Recompile on last time invalid objects that may stay using  utlrp :

SQL> alter session set container=PDBELE
SQL> select count(*) from dba_objects where status not like 'VALID' ;
COUNT(*)
---------
728
SQL> @?/rdbms/admin/utlrp
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2014-08-19 10:24:07
DOC>
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2014-08-19 10:24:11
DOC> The following query reports the number of objects that have compiled
.../...
DOC>
OBJECTS WITH ERRORS
-------------------
                  0
DOC> The following query reports the number of errors 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>#
ERRORS DURING RECOMPILATION
---------------------------
                          0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
...Database user "SYS", database schema "APEX_040200", user# "98" 10:24:34
...Compiled 0 out of 3014 objects considered, 0 failed compilation 10:24:34
...271 packages
...263 package bodies
...452 tables
...11 functions
...16 procedures
...3 sequences
...457 triggers
...1320 indexes
...211 views
...0 libraries
...6 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 10:24:34
...Completed key object existence check 10:24:35
...Setting DBMS Registry 10:24:35
...Setting DBMS Registry Complete 10:24:35
...Exiting validate 10:24:35
PL/SQL procedure successfully completed.

Verify all the components are in the valid state :

SQL>select comp_name ,version , status from dba_registry ;
COMP_NAME VERSION STATUS
------------------------------ ------------------------------ -----------
Oracle Database Vault 12.1.0.2.0 VALID
Oracle Application Express 4.2.5.00.08 VALID
Oracle Label Security 12.1.0.2.0 VALID
Spatial 12.1.0.2.0 VALID
Oracle Multimedia 12.1.0.2.0 VALID
Oracle Text 12.1.0.2.0 VALID
Oracle Workspace Manager 12.1.0.2.0 VALID
Oracle XML Database 12.1.0.2.0 VALID
Oracle Database Catalog Views 12.1.0.2.0 VALID
Oracle Database Packages and T 12.1.0.2.0 VALID
JServer JAVA Virtual Machine 12.1.0.2.0 VALID
Oracle XDK 12.1.0.2.0 VALID
Oracle Database Java Packages 12.1.0.2.0 VALID
OLAP Analytic Workspace 12.1.0.2.0 VALID
Oracle OLAP API 12.1.0.2.0 VALID
Oracle Real Application Cluste 12.1.0.2.0 OPTION OFF

End over
Some last things needs to be done. specificaly the new listener setup. Netca is the best tool for that . Warn if you doesn’t want to use default Oracle port 1521 you need to set parameter « local_listener » for the root container CDB1212.  Sample for port number 1522 :
a) Add a line in  tnsnames.ora  ($ORACLE_HOME/network/admin new release) :

LISTENER_CDB1212 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ele1ole6)(PORT = 1522))

b) Set parameter « local_listener » for the database:

[oracle@ele1ole6 ~]$ . oraenv
ORACLE_SID = [cdb1212] ? cdb1212
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ele1ole6 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 20 09:10:32 2014
Copyright (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> alter system set local_listener="LISTENER_CDB1212" ;
System altered.
SQL> show parameter local_listener
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_CDB1212

A little bit frustrated and disapointed to not have this direct upgrade option (myth push by Oracle people themself) There are still numerous benefis to use this technology . The main one is that you could have the same PDB database on two containers with different release, which let you some time for test purpose (as soon as you stay in read only mode, at least for one of them) . This configuration (same PDB on two containers) is not supported by Oracle, statement is you couldn’t have two pluggable database with the same name on two roots containers on the same server,  but the trick is to have one different listener for each PDB. In fact the problem come frome the same service name for the PDBs. If we use a different port it works !
So no miracle this time for the DBAs but some new possibilities to tes, t like plugin of a PDB thrugh a DBLINK. Another story to share with you.