Thursday Jul 07, 2016

Global Temporary Tables - Change in Oracle 12c

A few weeks back I was copied on an email conversation about a important change with Global Temporary Tables (GTT) in Oracle Database 12c

Something you need to be aware of when using GTTs in Oracle Database 12.1.0.2:
Prior to this release GTTs shared statistics between sessions. Statistics were SHARED between different sessions. Now since Oracle Database 12c this is different by default - statistics on GTTs are visible to the SESSION only.

This can be changed and altered of course. And there are pros and cons. But as I'm not an optimizer expert I'm glad that my colleague Nigel Bayliss, our Optimizer Product Manager, has blogged about it. If you use this functionality please read all the details about this change here: 

Thanks Nigel!

--Mike


PS: Original post said: Oracle Database 12.1.0.2 but I misread it - and thanks for the hint - it got introduced with Oracle Database 12.1.0.1 :-)

Tuesday Jul 05, 2016

Speed up Upgrade Phase 65 with a new catuposb.sql

Credits to Chris Smids from Proximus in Belgium :-) Thanks, Chris!!!


Upgrade to Oracle 12.1.0.2 is slow in phase: #65 ?

You are wondering why phase: #65 of the database upgrade to Oracle Database 12.1.0.2 takes quite a while. You dig down into the catupgrd0.log and recognized this statement taking a while: 

dbms_output.put_line('catuposb, update 4 - rows updated ' || rows_updated);
END; -- end of update for system internally generated objs
/

The cause for this issue is buried in the script catuposb.sql hitting stale histograms which did not get refreshed even if you gathered dictionary stats before the upgrade as recommended.

Offered Workarounds

Important things to know

  • Bug 21744290 - catuposb.sql can be slow during upgrade depending on no. of objects in the DB
    is supposed to be fixed in the April 2016 BPs and PSU - but in Chris' case Oracle Support confirmed that even though Chris had the BP the issue still happens. And this observation is correct. The fix from December 2015 is missing in the catuposb.sql distributed with the April 2016 PSU and BP.



    When you compare it now with the version Oracle Support is distributing you'll find the fix being included - but the two more recent fixes in the current catuposb.sql (see above) are missing.


    We'll sort this out and I will update you via this blog post.
    .
  • These are the lines missing in the version of catuposb.sql distributed with the April 2016 PSU/BP:

    -- bug 22178855: gather table stats on user$ after update to avoid slow
    -- select in update 4

    dbms_stats.gather_table_stats('SYS', 'USER$');
    .

What should you do?

Actually it is not supported to edit "our" scripts in ?/rdbms/admin so please wait for a clean version of catuposb.sql being distributed. I will update the blog asap. In between I think it should be ok to gather stats on USER$ beforehand in order to prevent this from happening:

  • SQL> exec dbms_stats.gather_table_stats('SYS', 'USER$');

Or you'll wait for the July 2016 Proactive BP and PSU which should be available on July 19, 2016. It will contain the correct version of the script including all fixes.

 --Mike

.

Monday Jun 27, 2016

Full Transportable Export/Import - Migrating an 11.2.0.4 database to Oracle Database 12c- into the Oracle Cloud

Full Transportable Export/Import - one of the coolest features in Oracle Database 12c 

We blogged about Full Transportable Export/Import a while back. It is - no doubt - one of the coolest features in Oracle Database 12c. And it is part of our Hands-On Lab exercise (Hands On Lab - Upgrade, Migrate, Consolidate to Oracle Database 12c) as well.

It utilizes the technique of Transportable Tablesspaces - cross-platform, cross- Endianness, cross-version - but lets Oracle Data Pump do all the "dirty" work of rebuilding everything kept in your SYSTEM and SYSAUX tablespace including views, synonyms, public objects, grants, sequences etc etc etc.

You can even combine it with RMAN Incremental Backups - of course cross-platform and cross-Endianness as well - in order to decrease the downtime for large databases.
Please find our presentation about the combination of these feature here in the Slides Download Center to the right:

In addition: You can use this Oracle Database 12c feature with your source database Oracle 11.2.0.3 or 11.2.0.4. Just the destination database you are upgrading or migrating to must be at least an Oracle 12.1.0.1 database (or newer of course). 

See the feature in action

Roy recorded a 20 minute video demonstrating how to:

  • Migrate an Oracle Database 11.2.0.4 database into an Multitenant's pluggable database using
  • Full Transportable Export/Import
  • into the Oracle DBaaS Cloud
    .

And of course the feature works also without migrating into a Container database environment - and without migrating to the Oracle DBaaS Cloud. 

If you need to migrate a database from Oracle 11.2.0.3 or newer to Oracle Database 12c Full Transportable Export/Import can become your best friend.

--Mike

Monday Jun 20, 2016

Minor Upgrade? Going from 11.2.0.1 to 11.2.0.4?

My Belgium friend Phillipe Fierens raised a great question on Twitter last week and dropped me an email again after having a discussion with his client:

For Phillipe and myself the answer is pretty clear and straight forward:

There is no minor upgrade anymore since every (patch set) release is a full release and since new parameters, parameter values, features and whatever appears even in patch sets. 

But the following discussion on Twitter with comments from all sides made me think about why people would honestly declare going for instance from 11.2.0.1 to 11.2.0.3 as a minor upgrade whereas going to 12.1.0.2 is seen as a major upgrade?

Let me summarize why I completely disagree - and actually Dom Giles nailed it:

  • Since Oracle Database 11.2.0.1 we deliver patch sets as a full release
  • A patch set can contain not only new parameters or parameter values but may occasionally also contain new features (typical examples in Oracle 11.2.0.4 are the new value for optimizer_dynamic_sampling=11 or the DBMS_REDACT package)
  • Therefore you will have to do exactly the same amount of testing, regardless of going to Oracle Database 11.2.0.4 vs Oracle Database 12.1.0.2 - it is ZERO difference in the tests, the time, the effort, the manpower ...
    .

You don't believe me? Then please check MOS Note:1962125.1 (Oracle Database - Overview of Database Patch Delivery Methods). Scroll down a bit to Testing Recommendations By Patch Type and see the rightmost column of this table headlined "Patch Set Release":

I hope this clarifies it all.

There is no "minor" patch set upgrade anymore. Even though I would love to tell you that going from 11.2.0.3 to 11.2.0.4 is just a minor hop it will require exactly the same testing and evaluation work then going to Oracle Database 12.1.0.2.

But going to Oracle Database 12.1.0.2 will mean that you are under Premier Support until end of June 2018 - whereas Free Extended Support for Oracle Database 11.2.0.4 will end in May 2017.

--Mike

.

Thursday Jun 16, 2016

EM 13c - How to Upgrade from EM Cloud Control 12c

I'm not a Cloud Control expert but I use the tool from time to time - and most of my customers are heavy users of it, especially in larger deployments.

If you use Oracle Enterprise Manager Cloud Control 12c (12.1.0.3, 12.1.0.4 or 12.1.0.5) and would like to evaluate your options to upgrade to Oracle Enterprise Manager Cloud Control 13c then please consult the following very useful documentation:

If you are searching for the software please find it here:

Just be aware:
As of July 15, 2016 I received several emails and comments by customers about issues with the migration to OEM 13c, e.g.:

So please check with Oracle Support first - and test the migration before doing it on a actual life system.
.

--Mike
.

Wednesday May 25, 2016

Transportable Tablespaces - Characters Sets - Same same but different?

All credits go to Don Wolf, an Oracle Advanced Customer Support engineer from Ohio as he dug out this information :-) Thanks Don!

Do database character sets have to match EXACTLY for Transportable Tablespaces?

That sounds like a simple question. When you look into our big slide deck the answer will be a straight "Yes". No doubts. Regardless if you would like to do Transportable Tablespaces or Full Transportable Export/Import your sources and your target's database character sets must be equal. Otherwise Data Pump won't allow you to process the meta data import.

But Don was wondering about slightly differing information in MOS notes and the documentation.
And asked me if I can clarify things. I couldn't. 

  • MOS Note:1454872.1
    Transportable Tablespace (TTS) Restrictions and Limitations: Details, Reference, and Version Where Applicable

    tells you:
  •  And the above documentation link then tells you:
    • The source and the destination databases must use compatible database character sets. That is, one of the following must be true:
      • The database character sets of the source and the target databases are the same.
      • The source database character set is a strict (binary) subset of the target database character set, and the following three conditions are true:
        • The source database is in version 10.1.0.3 or higher.
        • The tablespaces to be transported contain no table columns with character length semantics or the maximum character width is the same in both the source and target database character sets.
        • The tablespaces to be transported contain no columns with the CLOB data type, or the source and the target database character sets are both single-byte or both multibyte.
      • The source database character set is a strict (binary) subset of the target database character set, and the following two conditions are true:
        • The source database is in a version lower than 10.1.0.3.
        • The maximum character width is the same in the source and target database character sets.
          .
  • And furthermore from "Choosing a Character Set" section of Oracle 12.1  Database Globalization Support Guide:
    • Oracle Database does not maintain a list of all subset-superset pairs but it does maintain a list of binary subset-superset pairs that it recognizes in various situations such as checking compatibility of a transportable tablespace or a pluggable database. Table A-11 and Table A-12 list the binary subset-superset pairs recognized by Oracle Database.
    • In those tables the only binary subset-superset pairs involving AL32UTF8 are:
      • UTF8/AL32UTF8
      • US7ASCII/AL32UTF8
        .
  • This is not 100% congruent to the information provided in
    MOS Note 371556.1 - How to Migrate to different Endian Platform Using Transportable Tablespaces With RMAN
    saying: 
    "
    The source and target database must use the same character set and national character set."
    .

What is correct, what's not?

First of all the wording of "compatible character sets" seems to be gotten introduced with Oracle Database 11.2. 

In fact the scope for the target system has become broader in Oracle Database 11.2.  These rule here are correct as soon as your target database is an Oracle Database 11.2 or newer database release.

  • The source and the destination databases must use compatible database character sets. That is, one of the following must be true:
    • The database character sets of the source and the target databases are the same.
    • The source database character set is a strict (binary) subset of the target database character set, and the following three conditions are true:
      • The source database is in version 10.1.0.3 or higher.
      • The tablespaces to be transported contain no table columns with character length semantics or the maximum character width is the same in both the source and target database character sets.
      • The tablespaces to be transported contain no columns with the CLOB data type, or the source and the target database character sets are both single-byte or both multibyte.
    • The source database character set is a strict (binary) subset of the target database character set, and the following two conditions are true:
      • The source database is in a version lower than 10.1.0.3.
      • The maximum character width is the same in the source and target database character sets.
        .

--Mike

Friday May 06, 2016

Upgrade NOW! - OTN Interview at Collaborate16

Thanks again to Laura for this interview at Collaborate 2016 :-)

Why you need to Upgrade to Oracle Database 12c - NOW!

--Mike

Friday Apr 29, 2016

Upgrade to Oracle Database 12c: We don't insist :-)

It's so funny - for years I discuss with customers minimal downtime upgrade strategies back and forth, up and down. I saw DBAs really hunting to save a few seconds potential downtime - and I always take this serious as there is usually a real reason behind that. Just to learn a few days later by repeating experience that my work Windows7 laptop goes down to apply upgrades ... and this takes looooooooong .... sometimes it takes a lengthy +30 minutes of quiet time. Whereas my OL6 VBox image applies upgrades in the background and needs a simple restart to be back in less than a minute or so.
Different strategies of course. 

For those who think I'm writing nonsense about the upgrade of my Win7 on my Lenovo x230 with 16GB of RAM, an i5-320M cpu and a 5400rpm WD spinning disk here's a screenshot taken from one of those upgrades where I sat already 15 minutes waiting ... waiting ... waiting ...

As Win7 had to configure the upgrades afterwards, once it was done with restarting I had to wait another 10 minutes for completion. And no, my laptop is not broken (not that I know) - it's 3 years old and heavily used of course. But all running fine usually ... :-) 

Anyhow ... 

Of course I discussed with a ton of people why you should upgrade now to Oracle Database 12.1.0.2 now! - and stay away from that "we go live on the 2nd release only" thinking as there's no such thing as THE 2nd release anymore.

But whatever my position on upgrades is, I can ensure that we don't discuss methods like this internally to force you to upgrade ...

MS Win10 Forced Upgrade Message

... not yet *haha* !!!
.

--Mike

PS: The picture is not a fake - you can watch the 41 seconds youtube video here :-) 
.

Thursday Mar 31, 2016

DROP PLUGGABLE DATABASE - things you need to know

Directly after my DOAG (German Oracle User Group) Conference presentation about "How Single-/Multitenant will change a DBA's life" Martin Bach (Enkitec) approached me and told me about his experiences with the DROP PLUGGABLE DATABASE command and future recoverability.

Martin discovered that once you issued the DROP PLUGGABLE DATABASE command you can't reuse a previously taken backup of this particular PDB anymore and recover the PDB into this existing CDB. I wasn't aware of this and I'm glad that Martin told me about it.

Actually only the meta information in the controlfile or the RMAN catalog will be deleted. But archive logs and backup still persist.

See also my blog post from Jan 9, 2015:
Recent News about Pluggable Databases - Oracle Multitenant

This is the error message you'll see when you try to recover a dropped pluggable database:

RMAN> restore pluggable database pdb2drop;

Starting restore at 01-JUN-15
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/01/2015 10:10:40
RMAN-06813: could not translate pluggable database pdb2drop

Just a few weeks ago a colleague from German presales asked me if I know a more convenient way to restore a PDB once the magic command (DROP PLUGGABLE DATABASE) has been issued than recovering it into an auxiliary container database abd unbplug/plug it. I haven't.

But Nik (thanks!!!) told me that he pushed a MOS Note to being published explaining how to workaround this issue: 

MOS Note: 2034953.1
How to Restore Dropped PDB in Multitenant

In brief this MOS Note describes how to:

  • Create an auxiliary container database
  • Recover the backup (yes, you will have to have a backup of your container database) including this particular PDB
  • Unplug the PDB after recovery has been finished and plug it back into the original CDB

Now some will say: Hey, that's simple and obvious. For me it wasn't ;-) That's why I write about it to remind myself of this workaround ...

--Mike
.



Tuesday Feb 02, 2016

How to find out if a PSU has been applied? DBMS_QOPATCH

pflaster.jpgSince we change the PSU and BP patch numbering from Oracle Database 12.1.0.2.PSU6 to 12,1,0,2,160119 it is almost impossible to distinguish from the patch name only if you have applied a PSU or a BP.

But:
In Oracle Database 12c there's a package available which is very useful to query plenty of information about patches from within the database: DBMS_QOPATCH.

Here are a few helpful examples which I created by checking in our DBaaS Cloud database.

Which patches have been applied (or rolled back)?

SQL> set serverout on

SQL> exec dbms_qopatch.get_sqlpatch_status;

Patch Id : 20415564
        Action : APPLY
        Action Time : 24-JUN-2015 06:19:23
        Description : Database PSU 12.1.0.2.3, Oracle JavaVM Component (Apr2015)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20415564/18617752/
                  20415564_apply_ORCL_CDBRO
OT_2015Jun24_06_18_09.log
        Status : SUCCESS

Patch Id : 20299023
        Action : APPLY
        Action Time : 24-JUN-2015 06:19:23
        Description : Database Patch Set Update : 12.1.0.2.3 (20299023)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20299023/18703022/
                  20299023_apply_ORCL_CDBRO
OT_2015Jun24_06_18_11.log
        Status : SUCCESS

Patch Id : 20848415
        Action : APPLY
        Action Time : 24-JUN-2015 06:19:23
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20848415/18918227/
                  20848415_apply_ORCL_CDBRO
OT_2015Jun24_06_18_15.log
        Status : SUCCESS

Patch Id : 20848415
        Action : ROLLBACK
        Action Time : 24-JUN-2015 06:52:31
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20848415/18918227/
                  20848415_rollback_ORCL_CD
BROOT_2015Jun24_06_52_29.log
        Status : SUCCESS

Patch Id : 20618595
        Action : APPLY
        Action Time : 24-JUN-2015 13:52:13
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20618595/18956621/
                  20618595_apply_ORCL_CDBRO
OT_2015Jun24_13_52_12.log
        Status : SUCCESS

Patch Id : 20618595
        Action : ROLLBACK
        Action Time : 24-JUN-2015 14:37:11
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20618595/18956621/
                  20618595_rollback_ORCL_CD
BROOT_2015Jun24_14_37_10.log
        Status : SUCCESS

Patch Id : 20415564
        Action : ROLLBACK
        Action Time : 27-JAN-2016 17:43:18
        Description : Database PSU 12.1.0.2.3, Oracle JavaVM Component (Apr2015)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20415564/18617752/
                  20415564_rollback_MIKEDB_
CDBROOT_2016Jan27_17_42_16.log
        Status : SUCCESS

Patch Id : 21555660
        Action : APPLY
        Action Time : 27-JAN-2016 17:43:18
        Description : Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/21555660/19361790/
                  21555660_apply_MIKEDB_CDB
ROOT_2016Jan27_17_42_17.log
        Status : SUCCESS

Patch Id : 21359755
        Action : APPLY
        Action Time : 27-JAN-2016 17:43:18
        Description : Database Patch Set Update : 12.1.0.2.5 (21359755)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/21359755/19194568/
                  21359755_apply_MIKEDB_CDB
ROOT_2016Jan27_17_42_18.log
        Status : SUCCESS

Patch Id : 21962590
        Action : APPLY
        Action Time : 27-JAN-2016 17:43:18
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/21962590/19426224/
                  21962590_apply_MIKEDB_CDB
ROOT_2016Jan27_17_42_21.log
        Status : SUCCESS

PL/SQL procedure successfully completed.
.

Where's my home and inventory?

SQL> set pagesize 0

SQL> set long 1000000 

SQL> select xmltransform(dbms_qopatch.get_opatch_install_info, dbms_qopatch.get_opatch_xslt) "Home and Inventory" from dual;

Home and Inventory
-------------------------------------------------------------

Oracle Home     : /u01/app/oracle/product/12.1.0/dbhome_1
Inventory    
    : 
/u01/app/oraInventory


Has a specific patch been applied?

Lets check for the latest PSU. 

SQL> select xmltransform(dbms_qopatch.is_patch_installed('21359755'), dbms_qopatch.get_opatch_xslt) "Patch installed?" from dual;

Patch installed?
-------------------------------------------------------

Patch Information:
         21359755:   applied on 2015-10-22T21:48:17Z

.

What's tracked in my inventory?

The equivalent of opatch lsinventory -detail ...

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

Oracle Querayable Patch Interface 1.0
----------------------------------------------------------------
Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Inventory         : /u01/app/oraInventory
----------------------------------------------------------------

Installed Top-level Products (1):
                                    12.1.0.2.0
Installed Products ( 135)
                               ...

.

Additional Information and Patches

If you need more helpful examples you may check this excellent blog post by Simon Pane (Pythian):

And credits to Martin Berger for sending me this important information:

Just in case there are multiple DBs running from the same O_H, and someone      
queries dbms_qopatch.get_opatch_lsinventory automated from all DBs (as in       
automated monitoring/reporting scripts) I'd recommend Patch 20599273 -          
otherwise there might be strange XM errors due to race conditions. 

.

--Mike 

Thursday Jan 28, 2016

TDE is wonderful - Journey to the Cloud V

DBaaS Oracle Cloud

 What happened so far on my Journey to the Cloud?

Today's journey:
Learn about TDE (Transparent Data Encryption) and other secrets

What I really really love about my job: Every day I learn something new.

But sometimes learning can be frustrating at the beginning. And so it was for Roy and myself in the past days when we explored the use of TDE (Transparent Data Encryption) in our DBaaS Cloud environments. But many thanks to Brian Spendolini for his continuous 24x7 support :-)

Never heard of Transparent Data Encryption before? Then please read on here. It's usually part of ASO (Advanced Security Option) but it is included in the cloud offering. 

But first of all before taking care on TDE and PDBs I tried to deploy a new DBaaS VM ...
.

PDB names can't contain underscores?

Well, one learning experience is that initially you can't create a PDB in the DBaaS environment with an underscore in the name. I wanted to name my PDB in my new env simply "TDE_PDB1" (8 characters, all should be fine) - but received this nice message:

Don't worry if you don't speak German - it basically says that it can't be longer than 8 characters (ok, I knew that), must begin with a character (mine does of course) and can only contain characters and number (eh?? no underscores???). Hm ...?!?

Ok, I'll name mine "TDEPDB1".

Of course outside this page you can create PDBs containing an underscore:

SQL> create pluggable database PDB_MIKE admin user mike identified by mike
  2  file_name_convert=('/oradata/CDB2/pdbseed', '/oradata/CDB2/pdb_mike');

Pluggable database created
.

That's what happens when application logic tries to superseed database logic.
.

(Almost) undocumented parameter: encrypt_new_tablespace

Thanks to Robert Pastijn for telling me about this hidden secret. A new parameter which is not in the regular database deployment but only in the cloud.

encrypt_new_tablespaces

First check in MOS:

Interesting.

So let's check with Google.
And here it is: 7 hits, for example:

Controlling Default Tablespace Encryption

The ENCRYPT_NEW_TABLESPACES initialization parameter controls default encryption of new tablespaces. In Database as a Service databases, this parameter is set to CLOUD_ONLY.

Value
Description

ALWAYS

Any tablespace created will be transparently encrypted with the AES128 algorithm unless a different algorithm is specified on the ENCRYPTION clause.

CLOUD_ONLY

Tablespaces created in a Database Cloud Service database will be transparently encrypted with the AES128 algorithm unless a different algorithm is specified on the ENCRYPTION clause. For non-Database Cloud Service databases, tablespaces will only be encrypted if the ENCRYPTION clause is specified. This is the default value.

DDL

Tablespaces are not transparently encrypted and are only encrypted if the ENCRYPTION clause is specified.

What I found really scary is the fact that I couldn't find it in my spfile/pfile. You can alter it with an "alter system" command but you can't remove it.

The idea behind this is great as tablespaces should be encrypted, especially when they reside in a cloud environment. TDE is a very useful feature. And this mechanism exists regardless of your edition, whether you have Standard Edition or Enterprise Edition in any sort of flavor in the DBaaS Cloud.

A new tablespace will be encrypted by default:

SQL> CREATE TABLESPACE TS_MIKE DATAFILE 'ts_mike01.dbf' SIZE 10M;

Then check:

SQL> select TABLESPACE_NAME, ENCRYPTED from DBA_TABLESPACES;

But we'll see later if this adds some constraints to our efforts to migrate a database for testing purposes into the DBaaS cloud environment.
.

Is there anything encrypted yet?

Quick check after setting:

SQL> alter system set exclude_seed_cdb_view=FALSE scope=both;

I tried to find out if any of the tablespaces are encrypted.

SQL> select tablespace_name, encrypted, con_id from CDB_TABLESPACES order by 3;

TABLESPACE_NAME                ENC     CON_ID
------------------------------ --- ----------
SYSTEM                         NO           1
USERS                          NO           1
SYSAUX                         NO           1
UNDOTBS1                       NO           1
TEMP                           NO           1

SYSTEM                         NO           2
USERS                          NO           2
TEMP                           NO           2
SYSAUX                         NO           2

EXAMPLE                        NO           3
USERS                          NO           3
TEMP                           NO           3
SYSAUX                         NO           3
APEX_1701140435539813          NO           3
SYSTEM                         NO           3

15 rows selected.

Looks good.  Nothing encrypted yet.
.

How does the new parameter ENCRYPT_NEW_TABLESPACES effect operation?

Ok, lets try.

SQL> show parameter ENCRYPT_NEW_TABLESPACES

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
encrypt_new_tablespaces              string      CLOUD_ONLY

And further down the road ...

SQL> alter session set container=pdb1;

SQL> create tablespace MIKE_PLAYS_WITH_TDE datafile '/u02/app/oracle/oradata/MIKEDB/PDB1/mike_plays_with_tde.dbf' size 10M;

Tablespace created.

SQL> select tablespace_name, encrypted, con_id from CDB_TABLESPACES order by 3;

TABLESPACE_NAME                ENC     CON_ID
------------------------------ --- ----------
SYSTEM                         NO           3
SYSAUX                         NO           3
TEMP                           NO           3
USERS                          NO           3
EXAMPLE                        NO           3
APEX_1701140435539813          NO           3
MIKE_PLAYS_WITH_TDE            YES          3

7 rows selected.

Ah ... so my new tablespace is encrypted. Not bad ... so far TDE has no influence. I can create objects in this tablespace, query them etc. It is not disturbing at all. Good.
.

How does this key thing work in the DBaaS Cloud?

The documentation in above WP tells us this:

Managing the Software Keystore and Master Encryption Key

Tablespace encryption uses a two-tiered, key-based architecture to transparently encrypt (and decrypt) tablespaces. The master encryption key is stored in an external security module (software keystore). This master encryption key is used to encrypt the tablespace encryption key, which in turn is used to encrypt and decrypt data in the tablespace.

When the Database as a Service instance is created, a local auto-login software keystore is created. The keystore is local to the compute node and is protected by a system-generated password. The auto-login software keystore is automatically opened when accessed.

You can change (rotate) the master encryption key by using the tde rotate masterkey  subcommand of the dbaascli  utility. When you execute this subcommand you will be prompted for the keystore password. Enter the password specified when the service instance was created.
.

Creating a new PDB

That's easy, isn't it?

SQL> alter session set container=cdb$root;

Session altered.

SQL> create pluggable database pdb2 admin user mike identified by mike;

Pluggable database created.

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL> select tablespace_name, encrypted, con_id from CDB_TABLESPACES order by 3;

TABLESPACE_NAME                ENC     CON_ID
------------------------------ --- ----------
SYSTEM                         NO           4
SYSAUX                         NO           4
TEMP                           NO           4
USERS                          NO           4

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/MIKEDB/2A6680A0D990285DE053BA32C40AED53/datafile/o1_mf_s
ystem_cbn8fo1s_.dbf

/u02/app/oracle/oradata/MIKEDB/2A6680A0D990285DE053BA32C40AED53/datafile/o1_mf_s
ysaux_cbn8fo20_.dbf

/u02/app/oracle/oradata/MIKEDB/2A6680A0D990285DE053BA32C40AED53/datafile/o1_mf_u
sers_cbn8fo27_.dbf

Ah, bad thing. As I neither used the file_name_convert option nor changed the PDB_FILE_NAME_CONVERT initialization parameter my new PDB files get created in the "root" path of the CDB. I don't want this. But isn't there this cool new feature called ONLINE MOVE OF DATAFILES in Oracle Database 12c? Ok, it's an EE feature but let me try this after checking the current OMF file names in DBA_DATA_FILES and DBA_TEMP_FILES:

SQL> !mkdir /u02/app/oracle/oradata/MIKEDB/PDB2

SQL> ALTER DATABASE MOVE DATAFILE '/u02/app/oracle/oradata/MIKEDB/2A6680A0D990285DE053BA32C40AED53/datafile/o1_mf_system_cbn8fo1s_.dbf' TO '/u02/app/oracle/oradata/MIKEDB/PDB2/system01.dbf';

SQL> ALTER DATABASE MOVE DATAFILE '/u02/app/oracle/oradata/MIKEDB/2A6680A0D990285DE053BA32C40AED53/datafile/o1_mf_sysaux_cbn8fo20_.dbf' TO '/u02/app/oracle/oradata/MIKEDB/PDB2/sysaux01.dbf';

SQL> ALTER DATABASE MOVE DATAFILE '/u02/app/oracle/oradata/MIKEDB/2A6680A0D990285DE053BA32C40AED53/datafile/o1_mf_users_cbn8fo27_.dbf' TO '/u02/app/oracle/oradata/MIKEDB/PDB2/users01.dbf';

Be prepared:
This will create a 1:1 copy of the file in the designated location and synch afterwards. It may take a minute per file.

And moving the TEMP tablespace(s) file(s) will fail.

SQL> ALTER DATABASE MOVE DATAFILE '/u02/app/oracle/oradata/MIKEDB/2A6680A0D990285DE053BA32C40AED53/datafile/o1_mf_temp_cbn8fo25_.dbf' TO '/u02/app/oracle/oradata/MIKEDB/PDB2/temp01.dbf';

*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"/u02/app/oracle/oradata/MIKEDB/2A6680A0D990285DE053BA32C40AED53/datafile/o1_mf_temp_cbn8fo25_.dbf"

The temporary tablespace will have to be dropped and recreated. But not a big deal. 

Check;

SQL> select file_name from dba_data_files;

FILE_NAME
-----------------------------------------------------
/u02/app/oracle/oradata/MIKEDB/PDB2/sysaux01.dbf
/u02/app/oracle/oradata/MIKEDB/PDB2/users01.dbf
/u02/app/oracle/oradata/MIKEDB/PDB2/system01.dbf

Let me fix this so I don't hit this pitfall again:

SQL> alter system set pdb_file_name_convert='/u02/app/oracle/oradata/MIKEDB/pdbseed','/u02/app/oracle/oradata/MIKEDBPDB2';

Final verification:

SQL> select name, value from v$system_parameter where con_id=4;

NAME                   VALUE
---------------------- ----------------------------------
resource_manager_plan
pdb_file_name_convert  /u02/app/oracle/oradata/MIKEDB/pdbseed, /u02/app/oracle/oradata/MIKEDBPDB2


Now the fun part starts ... ORA-28374: typed master key not found in wallet

Remember this command from above in my PDB1? It run fine. But now it fails in PDB2.

SQL> create tablespace MIKE_PLAYS_WITH_TDE datafile '/u02/app/oracle/oradata/MIKEDB/PDB2/mike_plays_with_tde.dbf' size 10M;

create tablespace MIKE_PLAYS_WITH_TDE datafile '/u02/app/oracle/oradata/MIKEDB/PDB2/mike_plays_with_tde.dbf' size 10M
*
ERROR at line 1:
ORA-28374: typed master key not found in wallet

Voodoo in the database? I'm worried - especially as Roy had the same issue days before. But why did the command pass through without issues before in PDB1 - and now it doesn't in PDB2? Is it because the PDB1 is precreated - and my PDB2 is not?

Kinda strange, isn't it?
So connecting back to my PDB1 and trying again: 

SQL> alter session set container=pdb1;

Session altered.

SQL> create tablespace MIKE_STILL_PLAYS_WITH_TDE datafile '/u02/app/oracle/oradata/MIKEDB/PDB1/mike_still_plays_with_tde.dbf' size 10M;

Tablespace created.

Ok, now I'm worried.
What is the difference between the precreated PDB1 and my new PDB2?
.

Why do I get an ORA-28374 in my fresh PDB2?

When we compare the wallet status in both PDBs we'll recognize the difference:

PDB1:

SQL> select wrl_type, wallet_type, status from v$encryption_wallet;

WRL_TYPE        WALLET_TYPE          STATUS
--------------- -------------------- -----------------------
FILE            AUTOLOGIN            OPEN

PDB2:

SQL> select wrl_type, wallet_type, status from v$encryption_wallet;

WRL_TYPE        WALLET_TYPE          STATUS
--------------- -------------------- -----------------------
FILE            AUTOLOGIN            OPEN_NO_MASTER_KEY

.
Now thanks to Brian Spendolini I have a working solution. But I'm not convinced that this is an obvious path ...
Remember? I just would like to create a tablespace in my new (own, non-precreated) PDB. That's all ... 

SQL> alter session set container=cdb$root;

SQL> administer key management set keystore close;

keystore altered.

SQL> administer key management set keystore open identified by <your-sysadmin-pw> container=all;

keystore altered.

SQL> alter session set container=pdb2;

Session altered.

SQL> SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

WRL_PARAMETER                             STATUS             WALLET_TYPE
----------------------------------------- ------------------ -----------
/u01/app/oracle/admin/MIKEDB/tde_wallet/  OPEN_NO_MASTER_KEY PASSWORD

SQL>  ADMINISTER KEY MANAGEMENT SET KEY USING TAG  "tde_dbaas" identified by <your-sysadmin-pw>  WITH BACKUP USING "tde_dbaas_bkup"; 

keystore altered.

SQL> SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

WRL_PARAMETER                             STATUS   WALLET_TYPE
----------------------------------------- -------- -----------
/u01/app/oracle/admin/MIKEDB/tde_wallet/  OPEN     PASSWORD

And finally ... 

SQL> create tablespace MIKE_STILL_PLAYS_WITH_TDE datafile '/u02/app/oracle/oradata/MIKEDB/PDB2/mike_still_plays_with_tde.dbf' size 10M;

Tablespace created.

Wow!!!

That was not straight forward at all. Maybe it all happens due to my almost non-existing knowledge about TDE.

Ah ... and let me say that I find the missing uppercase letter with all keystore altered. echo messages quite disturbing. But this is a generic one and non-critical of course ...

--Mike
.

PS: Read on about Seth Miller's experience here on Seth's blog:
http://sethmiller.org/oracle-2/oracle-public-cloud-ora-28374-typed-master-key-not-found-in-wallet/ 




Thursday Jan 21, 2016

SuSE SLES 12 certified with Oracle Database 12.1.0.2

Puh ... I've got many mails over several months asking about the current status of certification of SuSE SLES12 for Oracle Database 12.1.0.2. It took a while - and I believe it was not in our hands. But anyhow ... finally ...

See Release Notes for additional package requirements

Minimum kernel version: 3.12.49-11-default
Mininum PATCHLEVEL: 1

Additional Notes

  • Edit CV_ASSUME_DISTID=SUSE11 parameter in database/stage/cvu/cv/admin/cvu_config & grid/stage/cvu/cv/admin/cvu_config
  • Apply Patch 20737462 to address CVU issues relating to lack of reference data
  • Install libcap1 (libcap2 libraries are installed by default); i.e. libcap1-1.10-59.61.x86_64 & libcap1-32bit-1.10-59.61.x86_64
  • ksh is replaced by mksh; e.g. mksh-50-2.13.x86_64
  • libaio has been renamed to libaio1 (i.e. libaio1-0.3.109-17.15.x86_64); ensure that libaio1 is installed


Note: OUI may be invoked with -ignoreSysPreqs to temporarily workaround ongoing CVU check failures

I had a SuSE Linux running on my previous laptop as dual-boot for quite a while. And I still like SuSE way more than any other Linux distributions potentially because of the fact that it was the Linux I started developing some basic Unix skills. I picked up my first Linux at the S.u.S.E. "headquarters" near Fürth Hauptbahnhof in 1994. I used to live just a few kilometers away and the version 0.9 a friend had given to me on a bunch of 3.5'' floppies had a disk failure. I believe the entire package did cost DM 19,90 by then - today roughly 10 Euro when you don't consider inflation - and was distributed on floppy disks. The reason for me to buy it was simply that I had no clue about Linux - but SuSE had a book delivered with the distribution.

This is a distribution I had purchased later on as well - they've had good discounts for students by then.


Picture source: Wikipedia - https://en.wikipedia.org/wiki/SUSE_Linux_distributions

--Mike

PS: Updated with more recent information on 15-02-2016 

Thursday Jan 14, 2016

VBox 5.0.10/12 issues with PERL and Seg Faults - UPDATE

A bit more than two months ago I did hear from several people having issues with our Hands-On Lab environment. And it became clear that only those who use Oracle Virtual Box 5 see such errors. 

Then I read Danny Bryant's blog post (thanks to Deiby Gomez for pointing me to it) about similar issues and a potential solution yesterday:

And interestingly one of my colleagues, our PL/SQL product manager Bryn Llewellyn started an email thread and a test internally yesterday as well. The issue seem to occur only on newer versions of Apple's MacBooks.
.

Potential Root Cause

The PERL issues seem to happen only on specific new Intel CPUs with a so called 4th level cache.

The current assumption is that Intel CPUs with Iris Pro graphics are affected. Iris Pro means eDRAM (embedded DRAM) which is reported as 4th level cache in CPUID. We have confirmed that Crystal Well and Broadwell CPUs with Iris Pro are affected. It is likely that the Xeon E3-1200 v4 family is also affected.

It seems to be that there's a bug in the perl binary. It links against ancient code from the Intel compiler suite doing optimizations according to the CPU features. Very recent Intel CPUs have 4 cache descriptors.

People who encountered this used Virtual Box VBox 5.0.x - and it passes this information to the guest. This leads to a problem within the perl code. You won't see it on VBox 4.3 as this version does not pass the information to the guest. 

But actually it seems that this issue is independent of Virtual Box or any other virtualization software. It simply happens in this case as many people use VBox on Macs - and some Macs are equipped with this new CPU model. But people run Oracle in VBox environments and therefore see the issue as soon as they upgraded to VBox 5.0.x.
.

Potential Solutions

If you are using Oracle in VBox there are actually two solutions:

  • Revert to VBox 4.3 as this won't get you in trouble
    This problem was not triggered on VBox 4.3.x because this version did not  pass the full CPUID cache line information to the guest.
    .
  • Run this sequence of commands in VBox 5.0 to tweak the CPUID bits passed to the guest:
    .
    VBoxManage setextradata VM_NAME "VBoxInternal/CPUM/HostCPUID/Cache/Leaf" "0x4"
    VBoxManage setextradata VM_NAME "VBoxInternal/CPUM/HostCPUID/Cache/SubLeaf" "0x4"
    VBoxManage setextradata VM_NAME "VBoxInternal/CPUM/HostCPUID/Cache/eax"  "0"
    VBoxManage setextradata VM_NAME "VBoxInternal/CPUM/HostCPUID/Cache/ebx" "0" 
    VBoxManage setextradata VM_NAME "VBoxInternal/CPUM/HostCPUID/Cache/ecx" "0" 
    VBoxManage setextradata VM_NAME "VBoxInternal/CPUM/HostCPUID/Cache/edx"  "0"
    VBoxManage setextradata VM_NAME "VBoxInternal/CPUM/HostCPUID/Cache/SubLeafMask" "0xffffffff" 

    • Of course you'll need to replace VM_NAME by the name of your VM
      .

If the error happens on a bare metal machine meaning it happens not inside a virtual image but on a native environment then the only chance you'll have (to my knowledge right now) is to exchange the PERL before doing really something such as running root.sh or rootupgrade.sh in your Grid Infrastructure installation or before using the DBCA or the catctl.pl tool to create or upgrade a database.

In this case please refer to the blog post of Laurent Leturgez:

Issues with Oracle PERL causing segmentation faults:

http://laurent-leturgez.com/2015/05/26/oracle-12c-vmware-fusion-and-the-perl-binarys-segmentation-fault

.

Further Information

This issues is currently tracked internally as bug 22539814: ERRORS INSTALLING GRID INFRASTRUCTURE 12.1.0.2 ON INTEL CPUS WITH 4 CACHE LEVEL.

So far we have not seen reports by people encountering this in a native environment but only by people using VBox 5.0.x or Parallels or VMware on a very modern version of Apple hardware.

.
--Mike 



Monday Jan 11, 2016

New Webcast "Why Upgrade to Oracle 12c" available

In December 2015 I did a recording for a webcast about "Why Upgrade to Oracle Database 12c" refreshing the content from a few months back.

Webcast "Why Upgrade to Oracle Database 12c"

Oracle's latest generation of database technology, Oracle Database 12.1.0.2, has some impressive new features. It offers great potential for a fast upgrade, simple migrations and consolidation, making more efficient use of hardware and delivering major improvements in management efficiency.

Join our webcast on Upgrading to Oracle Database 12c and you will learn:

  • Why you still need to upgrade to Oracle Database 12.1.0.2
  • How to ensure that your applications are ready for Oracle Database 12c
  • How to evaluate and test all enhancements of the upgrade process plus other new features
  • Best practices to upgrade and migrate successfully 

If you would like to watch it please access the webcast via this link: 

And please excuse the suboptimal audio quality - there were some technical difficulties to work around.

--Mike 

Thursday Jan 07, 2016

Oracle Database certification on Microsoft Windows 10

The MOS Note: 1307195.1  about

Certification Information for Oracle Database on Microsoft Windows x64 (64-bit)

got updated right before the 2015 Christmas holidays with the official certification information for Oracle Database 12c on Microsoft Windows 10.

And it says:

Windows 10 O/S Information:
RAC is not certified.
The earliest release certified on Windows 10 is 12.1.0.2.

--Mike 


Previous related blog posts:

Tuesday Dec 22, 2015

Patch, patch, patch - Journey to the Cloud - III

DBaaS Oracle Cloud

What happened so far on my Journey to the Cloud?

I haven't stopped my journey if you wonder about the inactivity for two weeks. I just had to learn a bit as my pretty naive approach caused me some trouble. Based on my last experience I had to find out why APEX left so many leftovers when I removed it the friendly way. And I wanted to patch my environment to a higher version of PSUs as - learning experience - a new deployment does not get deployed with the most recent PSU applied from scratch.  So there's still plenty of stuff to do.
.

Patching my DBaaS database with the most recent PSU

If you've got frustrated in the past by yourself while applying a PSU then please read on - I guarantee you'll smile more than once ...

First of all what is my expectation from a cloud environment?
Yes, Push A Button.

Well ... with the July 2015 PSU (12.1.0.2.4) this worked just fine even though it took a bit of time to download the PSU. But it got applied flawlessly by just pushing a button.

PSU Choice

But we have December 2015 already. So I would like to apply the Oct 2015 PSU (12.1.0.2.5) to my environment. And it turns out that currently this is the only one getting offered in the DBaaS environment to be applied. 

First step: Execute the precheck

See the results ...

Oh, it failed. But why? It does not tell me anything about the reason why it had failed. Was it no space left? Or a patch conflict? Or something else? No idea as Error [1] isn't very useful.  

And what's next? Live without the PSU even though I'm preaching since ages in our workshops that you MUST apply PSUs on a regular basis? Ok, so I have to sort this out.
,

Let's find it out

I did login to my environment via SSH. Then:

cd /u01

Ah, and there's a psu.n subdirectory - that looks promising. And there's a file conflictlog in it - that does look even more promising. 

It tells me:

Invoking prereq "checkconflictagainstohwithdetail"

ZOP-47: The patch(es) has supersets with other patches installed in the Oracle Home (or) among themselves.

ZOP-40: The patch(es) has conflicts with other patches installed in the Oracle Home (or) among themselves.

Prereq "checkConflictAgainstOHWithDetail" failed.

Summary of Conflict Analysis:
There are no patches that can be applied now.

Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :
21359755, 21627366

Following patches will be rolled back from Oracle Home on application of the patches in the given list :
20281121

Whole composite patch Conflicts/Supersets are:
Composite Patch : 21359755

        Conflict with 21627366
        Bug Superset of 20281121

Detail Conflicts/Supersets for each patch are:

Sub-Patch : 21359755
        Conflict with 21627366
        Conflict details:
        /u01/app/oracle/product/12.1.0/dbhome_1/lib/libserver12.a:kzan.o
        /u01/app/oracle/product/12.1.0/dbhome_1/lib/libserver12.a:kspt.o
        /u01/app/oracle/product/12.1.0/dbhome_1/lib/libserver12.a:kcb.o
        /u01/app/oracle/product/12.1.0/dbhome_1/lib/libserver12.a:kcrfw.o
        /u01/app/oracle/product/12.1.0/dbhome_1/lib/libserver12.a:kokt.o

        Bug Superset of 20281121
        Super set bugs are:
        20281121

Patch failed with error code 1

Ah, my friend Error [1] again. Now all is clear, isn't it? There's a conflict as the previous installation deployed in the cloud does seem to have gotten some extra treatments - which is good in one way but bad in another as I will have to solve this now. The Cloud Console does not offer me anything to solve this.
.

Luckily ...

I'm subscribed to our internal cloud mailing lists. And other people are way more smarter than I am so I found an email linking an explanation in the official documentation (Known Issues for the Database Cloud As A Service). There a quite a few known issues and it's very useful to have such a document. And here we go with the solution to my problem:

Ok, I have two options, one in the graphical interface, the other one on the command line. I'll go with the first option as this is meant to be Push A Button style and not type on the command line.

So first I click on PATCH in the hamburger menu: 

And then I chose the FORCE option.
May the force be with me.

The alternative would have been on the command line using the dbpatchm subcommand of the dbaascli utility:

Before applying the patch, set the value of the ignore_patch_conflict key to 1 in the /var/opt/oracle/patch/dbpatchm.cfg patching configuration file; for example:

ignore_patch_conflict=1
.

Et voilà ...

I took a bit ...

Actually a bit over 30 minutes ... but finally ...

The most recent PSU 12.1.0.2.5 from October 2015 has been applied to my DBaaS Cloud installation. It wasn't that complicated - if I had known upfront to hit the magic "FORCE" button ;-)

Finally let's check:

COLUMN PATCH_ID FORMAT 99999999
COLUMN PATCH_UID FORMAT 99999999
COLUMN VERSION FORMAT A12
COLUMN STATUS A12
COLUMN DESCRIPTION A30

select  PATCH_ID, PATCH_UID, VERSION, STATUS, DESCRIPTION from DBA_REGISTRY_SQLPATCH order by  BUNDLE_SERIES;


.

Remove APEX from my database and install it into my PDB

In my previous journey removing APEX from my cloud database didn't work quite well. I had leftovers afterwards, mainly from the Multitenant Self Service Provisioning APEX application (owner: C##PDBSS) and from the Database-As-A-Service-Monitor. 

Removing  Multitenant Self Service Provisioning Application

From some internal email conversation (and from the readme.pdf included in the download of the Multitenant Self Service Provisioning Application) I learned that there's a pdbss_remove.sql. And a find showed me the unusual location in the cloud deployment:

/var/opt/oracle/log/pdbss/pdbss

So first of all I connected to my CDB$ROOT and started the removal script:

sqlplus / as sysdba
spool /tmp/pdbss_remove.log
@/var/opt/oracle/log/pdbss/pdbss/pdbss_remove.sql

It took 1:45 minutes to complete. Spool off is not necessary as the script ends SQL*Plus.

Then I started the APEX removal script from the $ORACLE_HOME/apex subdirectory:

cd $ORACLE_HOME/apex
sqlplus / as sysdba

@apxremov_con.sql

Well ... but again something seemed to fail as I end up with a good bunch of invalid objects. 

First I did recompile:

@?/rdbms/admin/utlrp.sql 

But even then those 3 objects kept INVALID after the removal.

SQL> select owner, object_name from dba_objects where status='INVALID';

OWNER         OBJECT_NAME
------------- --------------------------
SYS           WWV_DBMS_SQL
FLOWS_FILES   WWV_BIU_FLOW_FILE_OBJECTS
APEX_040200   APEX$_WS_ROWS_T1

And furthermore a good number of APEX user schema did not get removed as well.

CON_ID USERNAME
------ --------------------------
     1 APEX_REST_PUBLIC_USER
     1 APEX_PUBLIC_USER
     1 APEX_LISTENER
     1 APEX_040200
     3 APEX_REST_PUBLIC_USER
     3 APEX_PUBLIC_USER
     3 APEX_LISTENER
     3 APEX_040200

8 rows selected.

Will update this blog post as soon as I have more news about how to remove APEX flawless from the cloud deployment. The issue is under investigation.

So better don't remove APEX at the moment from the DBaaS cloud deployment. 

--Mike


Friday Dec 18, 2015

Tech Tip: Get updates about new/important MOS Notes

There's nothing more annoying than unwanted and useless email newsletters in your inbox.

No doubt.

But sometimes I wish to have an alert about important MOS Note changes, recent additions to my favorite notes, updates to the bugs I monitor etc. I would like to know about helpful new MOS articles - when they get published ;-)

And this is what you need to do (and I'll have to apologize for the small pictures but our fancy, modern blog design template does not allow to include larger pictures ...).
.

Log into MOS and change your SETTINGS

Once you've logged in you'll click on MORE and then navigate to SETTINGS.

Change SETTINGS in MOS
.

Choose HOT TOPICS EMAIL in the left navigation menu

HOT TOPICS EMAIL
.

Update the settings

Here it is important that you choose "Send with Selected Options" first and then "HTML" as otherwise the email does not seem to get sent ("Text" option does not seem to work).

HTML Option
.

Add Products you'd like to get updates about 

Add Product Selection

Of course you may add more products - I chose only the "Database" in my example. 
.

Change the number of items you'd like to see 

No of Items

*** AND DON'T FORGET TO HIT THE "APPLY" BUTTON FURTHER DOWN ON THE SCREEN ***
.

 Check your current Favorites

Then it's important to check your current "Favorites". Click on the yellow/orange STAR in the upper menu close to the search field and select "Documents". If "Documents" does not get displayed there's no need to clean up as you haven't marked anything as favorite yet. But in my below example you see that I have a lot of very old docs in my list. 

Check Favorites

So I will clean them up first.
.

Clean Up the favorite documents list

Click on "Manage Favorites" via the yellow/orange STAR and mark all unwanted documents by using either the usual SHIFT or STRG keys. Hit "Remove".

In my case the list is empty now.  
.

Mark documents as FAVORITES 

The important work is now to mark certain documents or bugs as FAVORITES. In the below example I'd choose the most important MOS Note 161818.1 via the plain ID search (right side of the screenshot), and once the note gets displayed I'll push the withe STAR on the upper left corner above the document - the STAR will turn yellow/orange

Empty fav list

Once I've did that I will find this note now in my list of favorite documents:

Repeat this with the notes you'd like to get informed about changes. For instance I monitor always the "Known Issues and Alerts" for Oracle Database 12.1.0.2 and 11.2.0.4:

Issues and Alerts
.

Receive the email update

Once you've did that you'll get an email per day with the recent changes - and it will contain information about the updates in your fav notes.


.

Summary

It's hard to keep track about recent changes and updates to important MOS Notes. And it's even harder to learn about newly published articles in your areas of interest. The HOT Topics email feature in MOS is pretty cool and extremely helpful. I like it a lot.

But you'll have to enable it first in your settings as MOS does not want to spam you. 

Credits to Roy who dug out all the tiny pieces such as the HTML switch ;-)

--Mike

Wednesday Dec 16, 2015

New version of our Hands-On Lab available for download "Upgrade/Migrate/Consolidate to Oracle 12c" HOL

Yes! Finally ...

Roy did all the hard work and patched all our environments with the most recent PSUs. And he managed also to upload our Hands-On Lab not only internally to retriever (Oracle internally) but also to OTN.

What you'll be able to do?

  • Upgrade an 11.2.0.4 database UPGR to Oracle Database 12.1.0.2
  • Plug in UPGR into the CDB2, an Oracle Database 12.1.0.2 Container database, and make it a PDB
  • Migrate an 11.2.0.4 database FTEX into another PDB using the Full Transportable Export/Import feature
  • Unplug an Oracle Database 12.1.0.1 PDB and plug it into an Oracle Database 12.1.0.2 container database and finally upgrade it
  • And of course you can play around with the entire environment
    .

What we've did for your convenience? 

  • Switched to a new PDF viewer which allows better copy&paste - see the instructions document.
    Unfortunately we are not allowed to distribute the relatively old version of Adobe PDF Reader for Linux. 
  • Applied the most recent PSUs to the 11.2.0.4 and 12.1.0.2 homes
  • Fixed some minor things
  • Updated the HOL Instructions
  • Added an "aritificial" environment variable $OH12 to shorten typing
  • And some other tiny little things ...
  • And the lab is packaged with Oracle VBox 5.0.10 including the matching VBox Extensions which caused us trouble with the previous lab environment which was settled on Vbox 4.3 - so we hope that now all will go well.
    .

Where can you download the lab and the instructions?

All credits to Roy!!!

Thanks
--Mike

Wednesday Dec 09, 2015

UKOUG Tech 2015 - Summary & Slides

My first UKOUG Conference is over.

Actually it was over yesterday already for me as I had to head back to Germany. I would have loved to stay a bit longer and pick more of the many excellent presentations. I visited a few - and all of them were great. It was a tough choice as there were too many interesting things going on at the same time. Only finding the right room was sometimes a bit of a challenge ;-)

And I would like to have met more and talked to more people. But time was very limited. I didn't see anything from Birmingham except the foot path from the hotel to the venue and return. Plus an excellent Indian restaurant (thanks again to the folks of Pythian for inviting me - that was a wonderful dinner).

And of course thanks to everybody who attended my 3 talks.

As promised here are the slides to download:

Finally a very special Thank You to the organizers of the UKOUG 2015 Conference - it was an excellent event!

Hope to be back again in 2016!

--Mike

Tuesday Dec 08, 2015

MOS Note:884522.1 - New preupgrd.sql available

Whenever you attempt to upgrade an Oracle database to Oracle Database 12.1.0.2 please get the most recent version of preupgrd.sql, the preupgrade check script, (including the utluppkg.sql package) from MOS Note:884522.1 first.

MOS Note 884522.1
How to Download and Run Oracle's Database Pre-Upgrade Utility

preupgrd.sql

The new version has many improvements over the previous one - and it is more than 2 years "younger" then the one you'll get with a standard installation of Oracle Database 12.1.0.2. It will give you also good advice when you are planning a migration. 

--Mike

Friday Dec 04, 2015

Switch on/off + Remove - Journey to the Oracle Cloud - II

DBaaS Oracle Cloud

What happened so far on my Journey to the Cloud?

Day 2 of my Cloud exploration journey.

In my blog posts about my DBaaS Cloud journey I won't explain a lot about how to login to the Oracle Cloud or how to setup a simple database as others have done this already including the SSH key topic:

But let me show you some findings I've had when logging in via SSH to my DBaaS environment. In the following article I will describe how to:

  • Switch off Flashback Database
  • Clean up expired Backups and Archivelogs
  • Clean up traces and set the retention policy
  • Remove APEX from the CDB$ROOT
.

Flashback Database is ON by default

After playing around with my DBaaS environment for a few days all got stuck. The database hung. A quick look into the alert.log revealed the simple reason: my database had no space anymore on the Flash Recovery Area mount point. "Why that?" I did ask myself.

Well,

SQL> select FLASHBACK_ON from V$DATABASE;
YES

told me the answer. By default FLASHBACK DATABASE is ON. Great idea but for playground-mode you may or may not switch it OFF. Furthermore in my (developers) environment the logs will be written to /u03/app/oracle/flash_recovery_area/flashback (DB_RECOVERY_FILE_DEST) - and DB_RECOVERY_FILE_DEST_SIZE is defined at only 7.5GB. That got consumed quickly.  

You may switch it OFF if you don't need FLASHBACK DATABASE at the moment. Or check at least increase the DB_RECOVERY_FILE_DEST_SIZE.

SQL> alter database flashback off; 
SQL> exit
$  rm /u03/app/oracle/flash_recovery_area/flashback/*

And just for the records:
You don't need to have FLASHBACK DATABASE switched ON as a feature in order to use GUARANTEED RESTORE points (which is one of the best things - I used it almost daily before I "destroy" something in my databases).

Quick check:

SQL> select FLASHBACK_ON from V$DATABASE;
NO

SQL> create restore point DESTROY_ME guarantee flashback database;

If you'd check now:

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

Don't forget to drop your GUARANTEED RESTORE POINT later on. 

SQL> drop restore point DESTROY_ME; 

.

Clean up your expired BACKUPS and ARCHIVE LOGS 

Every database should be run in archivelog mode - no doubt. But if you are short on disk space for the archive logs you may need to clean up from time to time. Furthermore archivelog mode is necessary in order to support certain actions in Oracle Multitenant.

Keep in mind:
Backups and Archivelogs don't clean up themselves.

Clean up expired backups:

$ rman target /
RMAN> crosscheck backup;
RMAN> delete noprompt obsolete;
RMAN> delete expired backup;

Clean up archivelogs: 

$ rman target /
RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired archivelog all;


Clean up Trace and Log Files and set the Retention

The default retention for traces has been set to standard values in the cloud as well. You may change this to get a more frequent purging of traces. The interface to tweak settings is ADRCI. Find a nice ADRCI Survival Guide for DBAs here written by my colleague Uwe Hesse, 

adrci> show control

To solve DIA-48448 you'll have to set the correct diag directory you want to look at:

adrci> show homes
ADR Homes:
diag/rdbms/mike/MIKE
diag/tnslsnr/slc01qnv/listener
diag/tnslsnr/MIKEVM/listener

adrci> set home diag/rdbms/mike/MIKE

adrci> show control

ADR Home = /u01/app/oracle/diag/rdbms/mike/MIKE:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
4162796853           720                  8760 2015-09-17 13:51:04.699486 +00:00        2015-11-30 17:53:33.062134 +00:00                                                 1                    2                    82                   1                    2015-09-17 13:51:04.699486 +00:00
1 rows fetched

This means SHORTP_POLICY and LONGP_POLICY are set to 30 days and 365 days (~1 year). 

I'd usually change it (that is pretty up to your personal preferences) and purge whatever is older than a few days for a start.

adrci> set control (SHORTP_POLICY=96, LONGP_POLICY=720)

switching the short term policy to 4 days, the long term policy (alert.log, incidents) to 30 days. And finally cleaning up also older traces for a fresh start:

adrci> purge -age 24

This tool a while (~ 10 minutes) in my environment.
.

Remove Oracle Application Express (APEX) from the CDB$ROOT

As I wrote a few weeks ago Oracle APEX is a fantastic - and unfortunately still the most undervalued - Oracle tool. The only real treatment you should do to your Cloud database first is removing APEX from the CDB$ROOT before either plugging something in or creating pluggable database. If you have created pluggable databases (PDBs) already please make sure that APEX is not used in any of them as removing APEX from the CDB$ROOT will remove it from all existing PDBs as well.

See: https://blogs.oracle.com/UPGRADE/entry/apex_in_pdb_does_not 
on Why and How to remove Oracle APEX from the CDB$ROOT.

In short:

  1. Make sure to change into the local $ORACLE_HOME/apex directory first before starting SQL*Plus:
    cd $ORACLE_HOME/apex

  2. Connect to your CDB$ROOT:
    sqlplus / as sysdba

  3. Run the "Remove APEX from the CDB$ROOT" script:
    SQL> @apxremov_con.sql

  4. Recompile to get rid of any invalid objects left over by the removal script:
    SQL> @?/rdbms/admin/utlrp.sql

That sounds simple, doesn't it?

Well, but my journey ends here for today as I'm getting 28 invalid objects (which don't compile) afterwards - and plenty of nice ORA-600 errors in the alert.log looking fantastically similar to Data Dictionary corruptions. I think my Part 3 of the journey will be a patching cycle. 

ORA-00600: internal error code, arguments: [ktsircinfo_num1], [1], [4], [71210]
ORA-00600: internal error code, arguments: [6711], [4228381], [1], [4228380], [3
]

A look into the list of INVALID objects give me this result - from the Cloud documentation I see that C##PDBMGR is a self-provisioning user env:

The Oracle Pluggable Database Self-Service Provisioning application provides an interface to Oracle Database 12c Multitenant option and allows for the provisioning of Pluggable Databases (PDBs). You can perform PDB operations including create, clone, open/close, plug/unplug, and drop. Additionally, you can grant others access to the application, giving them rights to create and manage their own PDBs within the quota limits you set.

So maybe it is not a good idea to remove APEX from the CDB$ROOT in our Oracle DBaaS Cloud environment? I will find out next week ...

SQL> column object_type heading "Type" Format a15
SQL> column object_name heading "Name" format a30
SQL> column owner heading "Owner" format a20
SQL> set pagesize 100
SQL> select owner, object_type, object_name from dba_objects where status='INVALID';

Owner                Type            Name
-------------------- --------------- ------------------------------
SYS                  PACKAGE BODY    WWV_DBMS_SQL
FLOWS_FILES          TRIGGER         WWV_BIU_FLOW_FILE_OBJECTS
APEX_040200          TRIGGER         APEX$_WS_ROWS_T1
C##DBAAS_MONITOR     FUNCTION        DBM_IS_CDB
C##PDBMGR            PACKAGE BODY    PDBSS_PDB_MGR
C##PDBMGR            TRIGGER         PDBSS_APPLICATION_LOG_BI
C##PDBMGR            TRIGGER         PDBSS_ERROR_LOG_BI
C##PDBMGR            TRIGGER         PDBSS_PREFERENCES_BIU
C##PDBMGR            TRIGGER         PDBSS_RESERVED_DB_NAMES_BIU
C##PDBMGR            TRIGGER         PDBSS_DB_SIZES_BIU
C##PDBMGR            TRIGGER         PDBSS_RESOURCE_PLANS_BIU
C##PDBMGR            TRIGGER         PDBSS_USERS_BIU
C##PDBMGR            TRIGGER         PDBSS_USER_LOG_BI
C##PDBMGR            TRIGGER         PDBSS_ACCESS_REQUESTS_BIU
C##PDBMGR            TRIGGER         PDBSS_DATABASES_BIU
C##PDBMGR            TRIGGER         PDBSS_DATABASE_LOG_BI
C##PDBMGR            TRIGGER         PDBSS_DATABASE_ADMINS_BIU
C##PDBMGR            TRIGGER         BIU_PDBSS_EMAIL_TEMP_TYPES
C##PDBMGR            TRIGGER         BIU_PDBSS_EMAIL_TEMPLATES
C##PDBMGR            PACKAGE         PDBSS_FILTER_FW
C##PDBMGR            PACKAGE         PDBSS_FW
C##PDBMGR            TRIGGER         PDBSS_HISTORY_BIU
C##PDBMGR            TRIGGER         PDBSS_NOTIF_BIU
C##PDBMGR            TRIGGER         PDBSS_TZ_PREF_BIU
C##PDBMGR            PACKAGE BODY    PDBSS
C##PDBMGR            PACKAGE BODY    PDBSS_FILTER_FW
C##PDBMGR            PACKAGE BODY    PDBSS_FW
C##PDBMGR            PACKAGE BODY    PDBSS_EMAIL_API

28 rows selected.

I certainly can drop the user C##PDBMGR - but removing the Database Monitor user C##DBAAS_MONITOR may cause some trouble as the DBaaS Monitor won't work anymore. And furthermore still three APEX objects remain INVALID.

I'll update you as soon as I have my environment patched and refreshed sometime next week after UKOUG Conference.

--Mike

Thursday Dec 03, 2015

Push a Button - Journey to the Oracle Cloud - I

DBaaS Oracle Cloud

What happened so far on my Journey to the Cloud?

Isn't that a great marketing picture/poster?

But the real question is:

  • How do you move a database to the Oracle Cloud?
  • Is it easy or complicated?
  • What is your benefit?
  • Which techniques work, which don't?
  • And what should you switch on/off or tweak?

I'm not (yet) a cloud expert. But my goal for upcoming posts in the next weeks will be to show you my naive approach of moving databases to the Oracle Cloud and get some true benefit from it.

--Mike

Tuesday Dec 01, 2015

UKOUG Tech 2015 - 3 Talks - Upgrades & Consolidation

UKOUG 2015- I'ma  aSpeaker

UKOUG Tech 2015 Conference will begin on Monday, Dec 7, 2015. But there will be some activities on Sunday as well.  

It will be my first time at UKOUG's conference. It will happen in Birmingham/UK in the ICC.

Looking forward to meet great customers, experts and colleagues.

I'm happy to be invited to deliver 3 talks:

Hope to meet you there!

--Mike

Monday Nov 30, 2015

VBox 5.0.10 crash issues with our Hands-On-Lab

Milano - Nov 2015 (c) Mike Dietrich

I've ran two Hands-On-Workshops with customers and partners in Italy last week in Milano where we used our well known and thousands-of-times proven Hand-On-Lab environment

But this time some people failed while running the lab with random corruptions either shutting down the entire VM while running - or displaying file corruptions in the spfile - or other issues.

The common thing in all cases: People had VBox 5.0.10 downloaded and installed right before the workshop.

Of course they've did it - as I'm tempted too since weeks. Every time I start VBox on my PC Oracle Virtual Box asks me: 

Even though the screenshot is German you know what it offers me:
Download and Install Virtual Box 5.0.10.

Actually the current issue reminds me a lot on what I have experienced in 2014 in an Upgrade Hands-On Workshop in Vienna, Austria. 20 Oracle partners came together for two days for a Hands-On Upgrade/Migrate/Consolidate training. And 6 or 7 had random issues with their Virtual Box images. Corruptions. Failing upgrades at random phases. No patterns.

Only until somebody figured out via a Google search that at the same time other people started reporting similar behavior with their own VBox images using the brand new version of Virtual Box. It turned out that this newest version of Oracle Virtual Box 4.3 (I think it was 26) had exactly such issues. Everybody else in our room - including myself - running a version a few weeks older had no issues at all.

When we exchanged the affected installations the next morning replacing it (if I remember correctly: 4.3.24) all went fine for the rest of the workshop.

I won't say that VBox 5.0.10 is bad as I lack evidence, reproducible test cases, bugs. 

But I follow other people's Twitter and Facebook messages. And it seems to be that the PERL problem I did report a few days back:

is not he only issue with VBox images build in version 4 - and now running (more or less) on VBox 5.0.10.
.

Please see also:

--Mike 

Monday Nov 23, 2015

Network ACLs and Database Upgrade to Oracle 12c

What has been changed in Oracle Database 12c with Network ACLs?

Starting from 12c, network access control in the Oracle database is implemented using Real Application Security access control lists (ACLs). Existing 11g network ACLs in XDB will be migrated. Existing procedures and functions of the DBMS_NETWORK_ACL_ADMIN PL/SQL package and catalog views have been deprecated and replaced with new equivalents

In 12c, a network privilege can be granted by appending an access control entry (ACE) to a host ACL using DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE.  If you append an ACE to a host that has no existing host ACL, a new host ACL will be created implicitly. If the host ACL already exists, the ACE will be appended to the existing ACL.

(both paragraphs taken from MOS Note: 2078710.1)

What happens during/after upgrade?

  • Existing network ACLs will be migrated from XDB in Oracle 11g to Real Application Security in Oracle 12c.
    All privileges of the existing ACLs will be preserved
  • Existing ACLs will be renamed
  • Mapping between the old / new names is reflected in DBA_ACL_NAME_MAP.

    .

Issues before/during Database Upgrade?

First of all the current preupgrd.sql does not warn you correctly if such ACLs exist. This fix gets added to the preupgrd.sql. But you'll need to download the most recent version from MOS Note 884522.1. The one from January 2015 does not have it yet. But this is addressed and will be implemented soon.

Here's an issue which happened to one of my very experienced colleagues from Oracle Consulting in an upgrade project:

"Customer had network ACLs defined and Privileges (resolve,connect) granted for several hosts to several DB users in 11.2.0.3.

With the first DB, we observed the ACL renaming as you described it, but, much worse: 4 out of 9 privileges granted were completely gone away after the upgrade performed by DBUA (to 12.1.0.2.4). We then were able to evaluate the missing privileges and re-grant them again. Warned by that, for the next databases to be upgraded, we copied all the 11.2.0.3 content of the DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES to helper tables in order to be able to restore lost privileges (which was a good idea, as in one of the databases, only 87 out of 240 formerly existing privileges survived the upgrade)." 

Solution?

Check for existing Network ACLs before the upgrade or get the most recent preupgrd.sql once it contains the check.

Preserve the existing network ACLs and privileges (DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES) in a intermediate staging table to have the possibility to restore them afterwards in case the automatic migration fails or does not happen.

If you encounter a situation where your Network ACLs don't get migrated correctly, disappear and/or don't exist in the mapping table DBA_ACL_NAME_MAP afterwards please open an SR and let Oracle Support check. There are known issues with mappings and migrations not done correctly (find some bugs below) so needs to be verified if you have hit a known issue or encountered a new one.

More Information?

--Mike

.

About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle

Based in Germany. Interlink between customers/partners and the Upgrade Development. Running workshops between Arctic and Antartica. Assisting customers in their reference projects onsite and remotely. Connect via:

- -

Search

Archives
« July 2016
SunMonTueWedThuFriSat
     
1
2
3
6
8
9
10
11
12
13
14
15
16
17
18
19
22
23
24
25
26
27
28
29
30
31
      
Today
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
Workshop Map
x Oracle related Tech Blogs
This week on my Rega & Pono
Upgrade Reference Papers