Happy New YouTube Channel!

As announced, I start into 2017 with my own YouTube channel. You can access it at the right upper part of this site by clicking on the red icon:

youtube_channel

Do it, by the way 🙂

Leave a comment

Happy New Year 2017!

fireworks-animation

An interesting year has passed and 2017 awaits us. My best wishes to you and your families – have a great and happy New Year 🙂

2016 saw this site crossing the threshold of 1.5 Million hits. Thanks to all you visitors from around the world! The top five articles in 2016 have been

  1. adrci: A survival guide for the DBA
  2. Turning Flashback Database on & off with Instance in Status OPEN
  3. Reorganizing Tables in Oracle – is it worth the effort?
  4. Retrieve SQL and Execution Plan from AWR Snapshots
  5. Common Users & SYSDBA with #Oracle 12c Multitenancy

You may have noticed that I changed the branding of uhesse.com to Uwe Hesse | about Database Technology in order not to limit my scope to educational topics like the former The Oracle Instructor label did to some degree.

On the right upper corner of this site, you see now buttons to connect with me on LinkedIn and Xing – I decided to pay some more attention to that platform which is popular mainly in Germany, Switzerland and Austria.

Momentan führt bei mir LinkedIn (1930 Kontakte) mit großem Abstand vor Xing (167 Kontakte). Klicken Sie hier Uwe Hesse um dies Verhältnis zugunsten von Xing zu verbessern 🙂

If you wonder, the above was a call for action to connect with me on Xing in German.

Another planned new feature for 2017 is the launch of my own YouTube channel. Stay tuned, watch me becoming an irresistible social media force  😉

2 Comments

How to upgrade a Pluggable Database to 12cR2 in #Oracle

plugin

I have a Puggable Database (PDB) inside a 12.1.0.2 Container Database (CDB) and want to plug it into a 12.2.0.1 CDB. The destination CDB has been created on the same server sharing the same filesystem as the source CDB. Just to see if it works, some additional challenges:

  • The source CDB is Standard Editon
  • The destination CDB is Enterprise Edition
  • The source CDB uses shared undo (only option in 12cR1)
  • The destination CDB uses local undo

Good news: It did work quite smoothly. The steps are exactly the same as outlined here for upgrade from 12.1.0.1 to 12.1.0.2. The final call of catctl.pl looks like this:

 

[oracle@uhesse admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -c 'pdb1' catupgrd.sql

Argument list for [catctl.pl]
Run in                c = pdb1
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 = 0
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 0
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_160914]


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

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

Log file directory = [/tmp/cfgtoollogs/upgrade20161216145848]

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

Number of Cpus        = 1
Database Name         = cdb2
DataBase Version      = 12.2.0.1.0
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/cdb2/upgrade20161216145849/catupgrd_catcon_6894.lst]
catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/cdb2/upgrade20161216145849/catupgrd*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/cdb2/upgrade20161216145849/catupgrd_*.lst] files for spool files, if any

Log file directory = [/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/cdb2/upgrade20161216145849]

PDB Parallel SQL Process Count = [2] is higher or equal to CPU Count = [1]
Concurrent PDB Upgrades defaulting to CPU Count [1]
Parallel SQL Process Count (PDB)      = 2
Parallel SQL Process Count (CDB$ROOT) = 4
Concurrent PDB Upgrades               = 1
Generated PDB Inclusion:[PDB1]
CDB$ROOT  Open Mode = [OPEN]

Start processing of PDB1
[/u01/app/oracle/product/12.2.0/dbhome_1/perl/bin/perl catctl.pl -c 'PDB1' -I -i pdb1 -n 2 -l /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/cdb2/upgrade20161216145849 catupgrd.sql]

Argument list for [catctl.pl]
Run in                c = PDB1
Do not run in         C = 0
Input Directory       d = 0
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = pdb1
Child Process         I = 1
Log Dir               l = /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/cdb2/upgrade20161216145849
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 = 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_160914]


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

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

Log file directory = [/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/cdb2/upgrade20161216145849]

catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/cdb2/upgrade20161216145849/catupgrdpdb1_catcon_7048.lst]
catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/cdb2/upgrade20161216145849/catupgrdpdb1*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/cdb2/upgrade20161216145849/catupgrdpdb1_*.lst] files for spool files, if any

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

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

------------------------------------------------------
Phases [0-117]         End Time:[2016_12_16 15:28:54]
Container Lists Inclusion:[PDB1] Exclusion:[NONE]
------------------------------------------------------

Grand Total Time: 1800s [PDB1]

 LOG FILES: (/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/cdb2/upgrade20161216145849/catupgrdpdb1*.log)

Upgrade Summary Report Located in:
/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/cdb2/upgrade20161216145849/upg_summary.log

Total Upgrade Time:          [0d:0h:30m:0s]

     Time: 1809s For PDB(s)

Grand Total Time: 1809s 

 LOG FILES: (/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/cdb2/upgrade20161216145849/catupgrd*.log)


Grand Total Upgrade Time:    [0d:0h:30m:9s]

Just to confirm that the new local undo feature is in place:

[oracle@uhesse ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Dec 16 15:43:37 2016

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


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

SQL> select name,open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
READ ONLY

PDB1
MOUNTED


SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter pluggable database all save state;

Pluggable database altered.

SQL> select * from v$tablespace;

       TS# NAME 			  INC BIG FLA ENC     CON_ID
---------- ------------------------------ --- --- --- --- ----------
	 1 SYSAUX			  YES NO  YES		   1
	 0 SYSTEM			  YES NO  YES		   1
	 2 UNDOTBS1			  YES NO  YES		   1
	 4 USERS			  YES NO  YES		   1
	 3 TEMP 			  NO  NO  YES		   1
	 0 SYSTEM			  YES NO  YES		   2
	 1 SYSAUX			  YES NO  YES		   2
	 2 UNDOTBS1			  YES NO  YES		   2
	 3 TEMP 			  NO  NO  YES		   2
	 0 SYSTEM			  YES NO  YES		   3
	 1 SYSAUX			  YES NO  YES		   3

       TS# NAME 			  INC BIG FLA ENC     CON_ID
---------- ------------------------------ --- --- --- --- ----------
	 2 TEMP 			  NO  NO  YES		   3
	 3 USERS			  YES NO  YES		   3
	 4 UNDO_1			  YES NO  YES		   3

14 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb2/system01.dbf
/u01/app/oracle/oradata/cdb2/sysaux01.dbf
/u01/app/oracle/oradata/cdb2/undotbs01.dbf
/u01/app/oracle/oradata/cdb2/pdbseed/system01.dbf
/u01/app/oracle/oradata/cdb2/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/cdb2/users01.dbf
/u01/app/oracle/oradata/cdb2/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/pdb1/system01.dbf
/u01/app/oracle/oradata/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/pdb1/users01.dbf
/u01/app/oracle/oradata/pdb1/pdb1_i1_undo.dbf

11 rows selected.

As you have seen, it’s not exactly just unplug/plug to get the upgrade done. As always: Don’t believe it, test it! 🙂

Leave a comment

%d bloggers like this: