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_ASUME_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

.

Thursday Nov 19, 2015

Why you should remove APEX from the CDB$ROOT

Upgrade Blog posts about Oracle Application Express:


Oracle APEX (Application Express) is great piece of software. But it gets installed by default into the container database's CDB$ROOT unless you'd customized your CDB creation via scripts in Oracle 12.1.0.2. See: 

on how to customize a Single/Multitenant Database with less options.

But that is not the topic I would like to write about. I came across an pitfall in the past days twice.

Think of having a local APEX (Application Express) application in a standalone database or already inside a PDB. You'd like to plug it into a CDB, regardless if this CDB is local or in the Oracle DBaaS Cloud. But your current APEX is on a lower version than the APEX inside the target CDB.

Let's assume you'd like to migrate this standalone database or PDB over into the cloud. But as soon as you plug it in - even though you'll have Oracle Database 12.1.0.2 in both sites locally and in the cloud, you can't open your PDB in read/write mode. It will open RESTRICTED only.

Then you'll check PDB_PLUG_IN_VIOLATIONS and get a plug in error:

"APEX mismatch: PDB installed version 4.2.5.00.08 CDB installed version 5.0.0.00.31" 

Reason is simply that an Oracle Database 12.1.0.2 standard deployment will contain APEX 4.2.0 whereas the Oracle Cloud deployment in this scenario uses the more recent APEX version 5.0 already.

Having APEX installed into the CDB$ROOT was not a great idea. Actually it will cause you a lot of trouble and does not make much sense in the idea of Multitenant concept.

Oracle APEX Multitenant

I see why it has been done this way as not having APEX in the CDB$ROOT would have meant not having it in the PDB$SEED either - and all further provisioned PDBs would not have had APEX deployed by default. In this case every PDB would have required a separate APEX installation - but then you'd get the freedom of different APEX versions within the same Multitenant environment. And way more freedom during unplug/plug. But I'll elaborate this further down below.

Different APEX versions in Multitenant

First of all check your current APEX version with this query:

SQL> select COMP_ID, VERSION, STATUS from CDB_REGISTRY where COMP_ID='APEX' order by CON_ID;

COMP_ID  VERSION      STATUS
-------- ------------ -------
APEX     4.2.5.00.08  VALID

Find out in which container APEX is currently installed into:

SQL> select r.COMP_NAME, r.VERSION, c.NAME, c.CON_ID from CDB_REGISTRY r, V$CONTAINERS c where r.CON_ID=c.CON_ID and r.COMP_ID='APEX' order by CON_ID;

COMP_NAME                   VERSION   NAME      CON_ID
--------------------------- --------- --------- ------
Oracle Application Express  4.2.5.00  CDB$ROOT       1

Then choose the best solution for your environment to avoid trouble with APEX in a Multitenant environment.

Scenario

Migrate a PDB with containing an APEX 4.2 deployment into another CDB with APEX 5.0 already installed in the CDB$ROOT container (the standard deployment in the Oracle DBaaS Cloud).

Two Solutions

Basically I see two potential solutions:

  1. Remove APEX from the CDB$ROOT before plug in your PDB with a different APEX version
  2. Upgrade your APEX installation in the PDB after plugging it into the CDB to match the CDB's APEX version

Solution 1 - Remove APEX from the CDB$ROOT 

I'd prefer Solution 1 as this will give you way more freedom. It will even allow you to have different APEX versions in the same Multitenant environment in different PDBs. But you'll have to do this steps right after creation of the container database - or before you worked with APEX in any of your PDBs as the following steps will remove APEX from all your PDBs!!!! If you already have APEX applications inside one of your PDBs you must export them before the APEX removal. Keep in mind that the "application export" facility in APEX 4 does not carry your image files automatically (APEX 5 is smarter).

Remove APEX from CDB$ROOT will remove it from all PDBs as well

The Oracle Documentation describes the path pretty straight forward. 

  1. Make sure to change into the local $ORACLE_HOME/apex directory first before starting SQL*Plus as otherwise the removal process won't work and error out - I'd assume that path variables are not carried on correctly. 
    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. Check afterwards if APEX has been removed - also check for invalid objects. If necessary recompile. 
    In my tests two objects were INVALID after the removal but could be easily fixed
    (this part is not mentioned in the documentation):

    SQL> select COMP_ID, STATUS from DBA_REGISTRY where COMP_ID='APEX';
    no rows selected

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

    OBJECT_NAME                      STATUS
    -------------------------------- -------------
    APEX_PKG_APP_INSTALL_LOG         INVALID
    APEX_SPATIAL                     INVALID

    SQL> @?/rdbms/admin/utlrp.sql

    SQL> select object_name, status from dba_objects where status='INVALID';
    no rows selected

  5. Once you have done this your PDB containing the APEX application should not give you APEX errors anymore upon plugin as there's no mismatch anymore between CDB$ROOT's and the PDB's APEX.

  6. Just in the case - independently of my example - you'd like to install APEX now locally into a PDB then you'll have to use the apexins.sql script:

    cd $ORACLE_HOME/apex
    sqlplus / as sysdba
    SQL> alter session set container=PDB1;
    SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/


    Be aware - this step may run a long time (in my VBox image it took over 22 minutes to complete).

    timing for: Complete Installation
    Elapsed: 00:22:16.29


    Check the existence of the component finally:

    SQL> select comp_id, status , con_id from cdb_registry where comp_id='APEX';

    COMP_ID   STATUS  CON_ID
    --------- ------- -------
    APEX      VALID         3

Solution 2 - Upgrade APEX in the PDB after plugin

Even though I'd prefer solution 1 there may be some cases where you'll have to deal with Solution 2 and upgrade APEX after plugin. One simple reason could be that somebody uses APEX already in another PDB and you can't export and preserve it for later import - then you better don't follow Solution 1 then as it will force you to test your backup/restore strategy concept soon after ... 

  1. Run apexins.sql (from the APEX 5.0 installation - $ORACLE_HOME/apex) only in this particular PDB after plugin. You'll have to use catcon.pl

  2. The documentation https://docs.oracle.com/cd/E59726_01/install.50/e39144/db_pluggable.htm#HTMIG29441
    may not be correct mentioning all parameters without the --p option. Jason Straub corrected it:

    host &OH_HOME/perl/bin/perl -I &OH_HOME/rdbms/admin &OH_HOME/rdbms/admin/catcon.pl -b apexins -c '<PDB_name>' apexins_nocdb.sql --pSYSAUX --pSYSAUX --pTEMP --p/i/

    whereas &OH_HOME represents the full path to the Oracle home.


More Scenarios 

If you look at some of the potential scenarios you may see when trying to move PDBs around including APEX applications it is pretty obvious why having APEX only locally inside each PDB is a wise move. It will give you way more flexibility, less trouble, actually less upgrades (and APEX upgrades can take a while) - and simply less headache.

Assume that my CDB1 and CDB2 below are at the same database version - having different database versions will require upgrade/downgrade tasks in addition. 

APEX installed commonly within CDB$ROOT

  • CDB1 has APEX 4.2 in CDB$ROOT
    CDB2 has APEX 5.0 in CDB$ROOT
    • Unplug a PDB from CDB1 and want to plug it into CDB2 but you'll get an error in PDB_PLUG_IN_VIOLATIONS:
      “APEX mismatch: PDB installed version 4.2.5.00.08 CDB installed version 5.0.0.00.31”
    • That is actually the simple case as you'd only have to upgrade APEX inside the PDB:
      host &OH_HOME/perl/bin/perl -I &OH_HOME/rdbms/admin &OH_HOME/rdbms/admin/catcon.pl -b apexins -c '<PDB_name>' apexins_nocdb.sql --pSYSAUX --pSYSAUX --pTEMP --p/i/

  • CDB1 has APEX 5.0 in CDB$ROOT
    CDB2 has APEX 4.2 in CDB$ROOT
    • You unplug a PDB from CDB1 and want to plug it into CDB2 - but you can't actually plug in.
    • As APEX can't be downgraded to 4.2 in your PDB you'll have to upgrade APEX in the CDB2 to 5.0 first
    • Then you can plugin your PDB into CDB2
  • CDB1 has APEX in CDB$ROOT
    CDB2 has NO APEX in CDB$ROOT
    • Unplug a PDB with an APEX application from CDB1 and want to plug it into CDB2- fails
    • You will need to install APEX into CDB2's CDB$ROOT before being able to plug in the PDB
      • And it has to be the same version of APEX as used in CDB1

APEX installed locally within the PDB only

  • CDB1 has NO APEX in CDB$ROOT
    CDB2 has APEX  in CDB$ROOT
    • Unplug a PDB from CDB1 (having APEX locally in the PDB only) and want to plug it into CDB2 - works if ...
      • the PDBs local APEX version match the CDB2's common APEX version
      • If they don't match ...
        • PDB has APEX 4.2
          CDB2 has APEX 5.0
          Works, but you will need to upgrade APEX in the PDB after plugin
        • PDB has APEX 5.0
          CDB2 has APEX 4.2
          Fails, and you'll have to upgrade CDB2s common APEX 4.2 to 5.0 first before being able to plugin.

  • CDB1 has NO APEX in CDB$ROOT
    CDB2 has NO APEX  in CDB$ROOT
    • That is actually the best case as you don't have to care.
      • The only thing you will need to be aware of is that you'll need to patch your ?/apex home in case that PDB has a higher APEX version already than the one we ship by default 

More Information

You can find more information via these links:

And Finally ...

I'd recommend to NEVER have APEX in any CDB$ROOT. I see the advantage of having it as a new PDB will always have APEX provisioned with it. That is convenient, no doubt. But honestly if you really need APEX in most or all of your PDBs then create your own PDB$MASTER and provision from this one instead of using the PDB$SEED. In all other cases it's by far easier to install APEX inside the PDB when you really need it. Even with the penalty that it requires some extra 10 or more minutes until it is ready to go.

Given also the fact that APEX upgrade may take a while as well (see this blog post: https://blogs.oracle.com/UPGRADE/entry/save_upgrade_downtime_upgrade_apex  ) there's another advantage which personally I consider way more important:

Having APEX locally in the PDB only offers you way more freedom and flexibility. All the complicated scenarios come up only when either the source has no APEX in the CDB$ROOT (APEX locally in the PDB) but the target has any version (most likely a different one) or the target has no APEX in the CDB$ROOT but the source had it. And even more fun, both CDBs have APEX in the CDB$ROOT but at different versions.

This can be totally avoided by NEVER having APEX installed in your CDB$ROOT.

Which means also for the Oracle Cloud DBaaS databases:
Remove APEX from the CDB$ROOT and install it into your PDB before really starting up doing APEX stuff inside. APEX is such cool piece of software - and it's free - and powerful - and easy!!!

And (thanks to Jason again for this hint) if you look into the APEX 5.0 Installation Guide you'll find this tip:

Oracle recommends removing Oracle Application Express from the root container database for the majority of use cases, except for hosting companies or installations where all pluggable databases (PDBs) utilize Oracle Application Express and they all need to run the exact same release and patch set of Oracle Application Express. To learn more, see "Installing Application Express into Different PDBs."


--Mike

Monday Nov 16, 2015

UPDATE: _rowsets_enabled in Oracle Database 12c

Last week I did post this entry with a strong recommendation to disable _rowsets_enabled in Oracle Database 12.1.0.2:

Today I can give you an update, more insight information and better workarounds.

Credits go to our DWH and Optimizer people (thanks to Hermann, Angela, Nigel and Mohammed).

When is the problem happening?

When a hash join operation receives rowsets from its right input but then produces one row at a time as output. This explains why one of the bugs had as potential workaround hash_join_enabled=false (and please don't use this as a w/a!!!).

Different Workarounds 

  • Set
    event = "10055 trace name context forever, level 2097152"
    in your spfile. This will disable rowsets only for the specific situation where the problem happens.

    An important comment from Angela if you intend to set this event via an ALTER SESSION command:
    "Note that if the event is set with an alter session, it will not take effect unless the query or queries are recompiled, such as by changing the query text (by adding spaces or comments) or by flushing the shared pool."

  • Set
    _rowsets_enable=false
    in your spfile. But this will switch off the entire feature, not only the particular situation where the problem happens.

  • Apply the fix for
    bug 22173980 WRONG RESULTS WHEN "_ROWSETS_ENABLED" = TRUE
    (as of Nov 16, 2015 in regression testing and not available yet) as soon as it is available.
    .

Update (Dec 2, 2015)

Thanks to Christian Ballweg from Optiz Consulting in Germany letting me know that the patch is available for download:

Bugs Resolved by This Patch
22173980 
WRONG RESULTS (NUMBER OF ROWS) WHEN "_ROWSETS_ENABLED" = TRUE

.

Thanks again to the Optimizer folks for their quick reaction!!!

--Mike
.

Thursday Nov 12, 2015

Oracle VirtualBox 5.0.x - Segmentation Fault in PERL

Please see also:

 


 

Yesterday and the day before I've exchanged several emails with Ana who downloaded our Hands-On-Lab from here:

after OOW15, encountering a SEGMENTATION FAULT when trying to start the database upgrade with catctl.pl:

$ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
Segmentation fault 

Very strange thing ... 

The database is in upgrade mode (checked this in the alert.log) and there are no strange things mentioned anywhere. Plus hundreds of people have run and completed our lab so far.

Tue Nov 10 20:39:47 2015
MMON started with pid=21, OS id=9828
Starting background process MMNL
Tue Nov 10 20:39:47 2015
MMNL started with pid=22, OS id=9832
Stopping Emon pool
Tue Nov 10 20:39:47 2015
ALTER SYSTEM enable restricted session;
Tue Nov 10 20:39:47 2015
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Autotune of undo retention is turned off.
Tue Nov 10 20:39:47 2015
ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;
Tue Nov 10 20:39:47 2015
ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;
Tue Nov 10 20:39:47 2015
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
Tue Nov 10 20:39:47 2015
ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY;
Resource Manager disabled during database migration: plan '' not set
Tue Nov 10 20:39:47 2015
ALTER SYSTEM SET resource_manager_plan= SCOPE=MEMORY;
Tue Nov 10 20:39:47 2015
ALTER SYSTEM SET recyclebin='OFF' DEFERRED SCOPE=MEMORY;
Resource Manager disabled during database migration
replication_dependency_tracking turned off (no async multimaster replication found)
AQ Processes can not start in restrict mode
Starting background process CJQ0
Tue Nov 10 20:39:47 2015
CJQ0 started with pid=27, OS id=9836
Completed: ALTER DATABASE OPEN MIGRATE 

We checked several other things - and then I came across this tweet by Martin Klier yesterday:

and started to search a bit

I have no 100% proof for the actual reason but several people seem to have issues with SEGMENTATION FAULTs in Oracle's PERL ($ORACLE_HOME/perl/bin/perl) when using Oracle VirtualBox 5.0.x - and according to VitualBox Forum that seems to happen with the most recent VBox 5.0.10 as well.

The "funny" thing is that all works perfectly well in VBox 4.3.x ...  

It reminds me a lot on the reoccuring VBox bug with my German keyboard not allowing me to type in the | (pipe) character which requires to press "ALT GR" +  "<" keys together.  

--Mike

.

Tuesday Oct 27, 2015

OOW 2015 - Upgrade and Migrate to Oracle 12c Talk - Live and Uncensored - Get the Slides

Thank You, Thank You, Thank You!!!

Roy and I were extremely happy yesterday as the room was full. Totally sold out. I've read on Twitter that some people wanted to go in and weren't allowed to join as the room capacity was reached.

If you want to get the few slides for our first talk:

And thanks again - you were an awesome audience! And we are looking forward to see you in one of our two other talks:.

  • Our 2nd talk will happen on Wednesday at 12:15pm (skip the unhealthy lunch).
    We'll contrast some nasty things with some very good and detailed customer examples included in it. Real world examples, no artificial lab exercises. Againuncensored (mostly <img src=" title=";-)" style="border: none;" /> ) and just from first hand experience.

    How to Upgrade Hundreds or Thousands of Databases in a Reasonable Amount of Time [CON8375]
    Mike Dietrich, Master Product Manager, Oracle
    Roy Swonger, Sr Director, Software Development, Oracle

    Wednesday, Oct 28, 12:15 p.m. | Moscone South—102
    .
    .
  • The 3rd talk of our group is the Data Pump  Performance Tips and Tricks talk delivered by Data Pump experts from Development.

    Deep Dive: More Oracle Data Pump Performance Tips and Tricks [CON8376]
    Dean Gagne, Consulting Member of Technical Staff, Oracle
    Jim Stenoish, Senior Director, Software Development, Oracle

    Thursday, Oct 29, 9:30 a.m. | Moscone South—305
    .
    .

--Mike

Thursday Oct 22, 2015

PSU (and CPU/SPU) October 2015 got released

October 21, 2015 - Oracle released the October 2015 SPU/CPU, PSU and BPs. 

See:

For Oracle Database 12.1.0.2 and Oracle Grid Infrastructure access the PSUs from here - if you have only a single instance database you can safely take the Combo patch as you'll get downtime anyways - but for customers running RAC you need to evaluate the OJVM component as this will incur downtime which the database-only patch does not require as it can be applied rolling.

Patch Set Updates

Document Description Rolling RAC Patch Download
Note:21555660.8 Oracle JavaVM Component 12.1.0.2.5 Database PSU (Oct 2015) (OJVM PSU) No Patch:21555660
Note:21520444.8 Combo of 12.1.0.2.5 OJVM PSU and 12.1.0.2.5 DB PSU (Oct 2015) Part Patch:21520444

No patch found at the moment [Mike]
Note:21359755.8 12.1.0.2.5 (Oct 2015) Database Patch Set Update (DB PSU) Yes Patch:21359755

Grid Infrastructure

Document Description Rolling RAC Patch Download
Note:21523260.8 Combo of 12.1.0.2.5 OJVM PSU and 12.1.0.2.5 GI PSU (Oct 2015) Part Patch:21523260
Note:21523234.8 12.1.0.2.5 (Oct 2015) Grid Infrastructure Patch Set Update (GI PSU) Yes Patch:21523234


And be aware to patch your DBaaS Cloud databases as well - this is not done automatically ;-)

--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
« May 2016
SunMonTueWedThuFriSat
1
2
5
7
8
9
10
12
14
15
16
18
20
21
22
23
24
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