Uwe Hesse

Avatar von Unbekannt

Dieser Benutzer hat keine biographischen Informationen freigegeben

Startseite: http://uhesse.com

Happy New Year 2017!

Veröffentlicht in TOI am Dezember 31, 2016

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 Kommentare

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

Veröffentlicht in TOI am Dezember 19, 2016

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! 🙂

Hinterlasse einen Kommentar

How to configure FLASHBACK in #Oracle

Veröffentlicht in TOI am Dezember 13, 2016

flash

It’s easy to get confused by the various different forms of FLASHBACK possible with Oracle Databases. Let’s try to sort that out. I start with forms of FLASHBACK that work with little effort to the system and not much – if anything – to do for the DBA to enable it.

FLASHBACK TABLE TO BEFORE DROP

This is on per default since version 10g and configured with the initialization parameter RECYCLEBIN=ON. Set this parameter to OFF if you want the old behavior. The effect of RECYCLEBIN=ON is that tables are not removed from the Data Dictionary upon DROP TABLE commands. Instead, they just get another name and are marked as deleted. As long as other segments do not need the space that is consumed by the marked table, it can be re-renamed after the original name. See details here.

FLASHBACK QUERY

Possible since 9i, this feature makes use of the undo information that gets created upon every DML statement in case it needs to be rolled back. The initialization parameter UNDO_RETENTION can be used to make it reach further into the past. Otherwise, there is no effort for the system to enable it at all. See details here and an example here.

FLASHBACK TABLE TO TIMESTAMP

Introduced in 10g, it has the same foundation as FLASHBACK QUERY: works using undo information. Additionally, you need to enable it on the table layer with ALTER TABLE ENABLE ROW MOVEMENT.  See details here.

The FLASHBACK features above are all enabled by default and require little effort by the system. That’s different for the next two:

FLASHBACK ARCHIVE

Since 11g, that feature enables FLASHBACK QUERY for certain tables for as long as you like. The undo information of those tables is preserved in internal tables (the flashback archives). It is some effort involved to store the data in the flashback archive and it consumes space. The more space the longer you want to keep that undo data. See details here and an example here. Notice that this feature is since 12c included in all editions:

flashback_archive

FLASHBACK DATABASE

Introduced in 10g, that is the only form of Flashback that is affected by what you see in the column FLASHBACK_ON in V$DATABASE. It enables you to do a fast Database Point In Time Recovery by storing old versions of Oracle Blocks periodically into the Flashback Logs. You need to configure that on the Database Layer with ALTER DATABASE FLASHBACK ON – it is OFF by default. DB_RECOVERY_FILE_DEST has to be set to point to where the Flashback Logs are to be stored, while DB_RECOVERY_FILE_DEST_SIZE sets an upper limit for the space this database is allowed to consume in the Recovery Area. The Recovery Area must be used for Flashback Logs and is recommended for Backups and Archived Logs as well. See an example here.

The initialization parameter DB_FLASHBACK_RETENTION_TARGET specifies how long you wish to be able to do FLASHBACK DATABASE into the past. Default is 1440 Minutes which is one day. Again, this causes some effort for the system to write the Flashback Logs and requires space to store them. See details here.

So the various Flashback forms use quite different techniques underneath – they have just the term in common and that logical errors are being addressed to some degree. Hope this article helps to clarify some misconceptions that I observe in the Oracle community from time to time 🙂

3 Kommentare