Monday Feb 01, 2016

New PREUPGRD.SQL is available for Upgrades to 12c

üreupgrd.sql

As of today a new version of our upgrade tool preupgrd.sql (including the package utluppkg.sql) for upgrades to Oracle Database 12.1.0.2 is available as download from MOS:

Download it and exchange the existing preupgrd.sql and utluppkg.sql in your current Oracle 12.1.0.2 ?/rdbms/admin directory.

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

Wednesday Jan 20, 2016

Oracle January 2016 CPU PSU BP available now - BE AWARE OF CHANGES IN PATCH NUMBERING

Last night the PSUs and BPs for January 2016 have been made available for download on support.oracle.com.

Oracle Critical Patch Update Advisory - January 2016

http://www.oracle.com/technetwork/topics/security/cpujan2016-2367955.html 

It contains 248 security fixes across all products and platforms. And of course important non-security fixes - and that's why we recommend to apply the PSUs (or the BPs in case you are on Exadata or an Oracle In-Memory user) as soon as possible. 

Change in Patch Numbering

Please be aware that as of November 2015 there's been a change in patch numbering introduced which most of you may not be aware of. A database PSU was named 12.1.0.2.5 before (or I used to call it 12.1.0.2.PSU5 before to make clear that a PSU and not a BP has been applied). But the new notation will change the 5th digit to a 6-digit-number to include the date. See MOS Note:2061926.1 for details.

Example:

  • Before: Oracle Database 12c PSU October 2015 ... 12.1.0.2.5
  • Now: Oracle Database 12c PSU January 2016 ... 12.1.0.2.160119 

More Information? 

--Mike

Tuesday Jan 19, 2016

Clean up APEX - Journey to the Cloud IV

DBaaS Oracle Cloud

What happened so far on my Journey to the Cloud?

Today's journey: Cleanup APEX removal leftovers 

When you read my "Patch" blog post from Dec 22, 2015 you'll see that I was left with an incomplete Oracle Application Express (APEX) removal situation. Something looked really strange and didn't work as expected and documented. 

But thanks to Jason Straub and Joel Kallman, my APEX colleagues, The solution below got provided by Jason. And let me say upfront that it's not APEX's fault ;-) Somebody has mixed up stuff in the current DBaaS deployment and thus the correct scripts to remove things in the right order are simply missing.

How to remove Oracle APEX from the DBaaS Cloud's CDB$ROOT

  1. Download APEX 4.2.0.6 and PDBSS 2.0 (Multitenant Self Service Provisioning Application):
    http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-archive-42-1885734.html
    http://www.oracle.com/technetwork/database/multitenant/downloads/multitenant-pdbss-2016324.html

    Actually if you have removed PDBSS already as I did in my "Patch" blog post from Dec 22, 2015 you don't have to download, unzip and execute the PDBSS removal script again.


  2. Copy the zip files to the Cloud environment

    scp -i ./yourCloudKey /media/sf_CTEMP/Download/apex_4.2.6_en.zip oracle@<your_cloud_IP>:/home/oracle

    Enter passphrase for key './yourCloudKey':

    apex_4.2.6_en.zip              100%   82MB 116.9KB/s   11:58

    Repeat the same with PDBSS in case you didn't remove it already.


  3. Connect to your Cloud environment and unzip both files.

    sh -i ./yourCloudKey oracle@<your_cloud_IP>

    cd
    unzip apex_4.2.6_en.zip


    Repeat the same with PDBSS in case you didn't remove it already.


  4. Remove PDBSS by using your unzipped archive

    cd /home/oracle/pdbss
    sqlplus / as sysdba
    SQL> @pdbss_remove.sql


  5. Remove APEX 5 by using the existing default installation
    (this is the part I did already in my previous blog post from Dec 22)

    cd $ORACLE_HOME/apex
    sqlplus / as sysdba
    SQL> @apxremov.sql 



  6. Remove APEX 4.2 parts by using your unzipped archive
    (this is the part I didn't do yet)

    cd /home/oracle/apex
    sqlplus / as sysdba
    SQL> @apxremov_con.sql


  7. Drop the DBaaS Monitor common users

    As the DBaaS Monitor is based on APEX as well removing APEX will "kill" the DBaaS Monitor as well. So in oder to have a proper working environment you must drop the common users for DBaaS Monitor as well. Otherwise you'll receive ORA-959 synch errors in PDB_PLUG_IN_VIOLATIONS whenever you deploy a new PDB and - even worse - the PDB won't open unrestricted.

    SQL> drop user C##DBAAS_BACKUP cascade;
    SQL> drop user C##DBAAS_MONITOR cascade;


  8. Finally recompilation and check for invalid objects

    sqlplus / as sysdba
    SQL> @?/rdbms/admin/utlrp.sql

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


  9. Now you are free to install APEX 5.0 in the PDBs of your choice.

    As theres's a PDB1 already I will create APEX inside this PDB first.

    cd $ORACLE_HOME/apex
    sqlplus / as sysdba
    SQL> alter session set container=pdb1;
    SQL> create tablespace APEX5 datafile '
    /u02/app/oracle/oradata/CDB1/PDB1/apex5_01.dbf' size 100M autoextend ON next 1M;


    Be aware: PDB_FILE_NAME_CONVERT is not set. And your database is not in ASM. Therefore avoiding the data file path and name will let you end up with a file named after OMF standards in:
    /u02/app/oracle/oradata/CDB1/26A65D56D16F21A1E05336036A0A1AD8/datafile/o1_mf_apex5_c9wg9kly_.dbf

    Create APEX 5.0 in PDB1, change the admin password, create the APEX listener and REST service and unlock the public user:

    SQL> @apexins.sql APEX5 APEX5 TEMP /i/
    SQL> @apxchpwd.sql


    Be aware about the password requirements:

    --------------------------------------------------------------------------------
    Password does not conform to this site's password complexity rules.
    * Password must contain at least 6 characters.
    * Password must contain at least one numeric character (0123456789).
    * Password must contain at least one punctuation character
    (!"#$%&()``*+,-/:;?_).
    * Password must contain at least one upper-case alphabetic character.
    * Password must not contain username.
    --------------------------------------------------------------------------------


    SQL> @apex_rest_config.sql
    SQL> alter user APEX_PUBLIC_USER identified by SecretPWD account unlock;

Let me add that it is possible to have different APEX versions in different PDBs. The only thing which you'll need to really really take care on is the images directory and the different apex homes.

That's it ... and in a future cloud deployment the extra step to remove APEX 4.2 shouldn't be necessary anymore. 

--Mike

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 



Wednesday Jan 13, 2016

Have an Excellent Start into 2016!!!

Roy and I and the entire Database Upgrade Team would like to wish you a very good start into 2016!!!

And thanks for your confidence in our work in the past year(s). The Upgrade Your Database - NOW! Blog had over 440,000 hits in 2015 which is quite a surprise as neither Roy nor I do update the blog full time. But the many mails and comments we consistently get demonstrate your interest. 

Upgrade Your Database - NOW! Statistics 2015

We will continue to report about things related to upgrades and migrations and performance and testing and the cloud and ... and ... and regarding Oracle Database 12c in 2016 - I promise :-)

Thanks for your support and all the helpful emails and comments.
We learn a lot from you folks out there!!!

Have a great start into 2016! 

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

Friday Dec 25, 2015

Best Albums in 2015

Yes, from time to time I write something fully off-topic.

If you look for tech stuff please scroll down just a bit ;-)

I love listen to music. Every day. Especially when I travel my two friends (Pono Player and B&O H6 Headphones) are always with me. And at home I have a decent stereo system in my man's cave. But this blog post is not meant about gear, it's meant about music released in 2015.

When I look at "Best Record of the Year" in terms of popularity or sells I always get a bit scared. So please take the following list as purely based on my own views and likes. And let me say that I bought all these records or CDs as real media
.

2015 - A Vinyl Year 

For me 2015 was a true vinyl year. I purchased more than 75 LPs, some new, some from Used Record stores. I never sold my record collection as some of my friends did, I just kept it in good shape - and since this year I owe also a record washing machine which allows me to refresh especially used examples quite easily. I have a fairly good British turntable with a almost 10 year old Dutch cartridge mounted to it. One thing you'll recognize immediately when listening to music on vinyl: you don't skip songs. No playlists. And you'll discover hidden gems on records you haven't listened to for many many years. 

But be aware when rushing down the road to your next record store. A lot of the oh-so-popular reissues settle on digital transfer copies, sometimes not even from the original master tapes but from a second sourced copy. Positive examples are the recent editions of Simon and Garfunkel and the huge Queen box. But others such as the Roxy Music collection or - the worst of the worst, shame on you, Klaus Meine and Rudolf Schenker - the 50th Anniversary Scorpions box and re-editions. Terrible sound. No dynamic at all. Luckily I have my copies of "Love at the First Sting", "Virgin Killer", "Blackout" and "World Wide Live" in good shape from the past. 

Vinyl has another great aspect as well besides sounding often a bit better and warmer than CD:
I can read the lyrics without looking for a magnifying glass ;-)

If you miss Adele, well, she has a great voice, no doubt. But I can't really listen to deadly compressed music anymore. It makes me feel sick as all dynamics, all transparency gets completely lost. And music lives on things such as dynamics ... if you don't believe me look up Adele's "25" CD edition in the Loudness War DR Database. Then you'll see what I mean - the CD version will sound only well in your car or on an iPhone - but on a real stereo system it sounds like a dead fish ... If you still don't believe it come by and bring your "21" CD and we'll compare it to my "21" vinyl edition (which is not a good pressing in terms of quality either).

Enough said. 
.

Here's my top 5 list of records in 2015

5. David Crosby - Croz (Vinyl)

Good ol' David Crosby has still a marvelous voice. No idea how he could keep it when you read what he has to say about his crazy years in the 60s and 70s. It's a miracle that he survived it. And his 2015 record titled CROZ is a very good collection of laid back folk and rock songs in the tradition of CSNY. The vinyl edition has an extra goody on side 4: a audiophile pressing at 45rpm of "What's Broken" (with Mark Knopfler on guitar) and "The Clearing". Plus it contains the digital download codes for WAV files. I'd wish other record companies would follow this outstanding example and deliver WAV or FLAC/ALAC files and not MP3 with their records.

4. Kamasi Washington - The Epic (CD)

So many things have been written about this jazz record. Most likely because it's hip and cool and very unusual. People who are usually not into jazz can enjoy this 3 hour mammoth piece on 3 CDs. I'd say the critics overrated it a bit as if it was the only good jazz record in 2015 (which is not true). But actually all the jazz records I bought in 2015 where from the years before with Marcin Wasilewski Trio's "Spark of Life" from 2014 or Stefano Bollani's "Stone in the Water" from 2008.

Nevertheless, Kamasi Washington's "Epic" is worth to listen. It's fun. It's fresh and old fashioned and funky and full of soul. It's available on vinyl as well but when I purchased it the vinyl was not yet available. People rumor that the pressing should be quite good. You may read on here in my favorite US Audio magazine Stereophile.

3. Anathema - A Sort of Homecoming (Vinyl) 

My favorite Rock band - from Liverpool, England. They started their career 25 years ago as a Death/Doom Metal band - and when you listen today you won't realize this. A Sort Of Homecoming got recorded in 2015 during their anniversary show in Liverpool Cathedral. A have seen Anathema live some years ago in Munich. And I would say it was one of the 3 best concerts I've ever been to. And I have been to many concerts so far. I have almost all of their CDs. For me Anathema is one of the best Rock bands on the planet. If you ever have the chance to visit one of their shows spend the €30-40,- and you'll be amazed.  

I bought the concert on vinyl on 3 records. But for those who prefer visual impressions and have a decent surround system the 5.1 mix of the BluRay is supposed to be excellent. They play the entire show acoustic with many of their die-hard fans in the audience. I wish I would have had a chance to be there as well.

2. Bruce Springsteen - Album Collection (Vinyl) 

Bob Ludwig remastered all the first 7 Springsteen albums. And I had to get it as many of Springsteen's LPs never got a decent remastering treatment. "Nebraska" did not sound any good - given the circumstances under which The Boss recorded it this was expected. But the remastering let it sound way better than any version I've had heard before.

Before I write too many things you may read what Michael Fremer from analogplanet.com wrote about this awesome edition:

Bruce Springsteen: The Album Collection Vol. 1 1973-1984 Reviewed 

Springsteen Complete Album Collection

The only real caveats are:

  • The download code allows you to download only 320k MP3s - who needs that? 
  • A bit more space in the box would have been better.
  • Two of my copies were warped.
  • The included "book" is more or less unreadable as pictures are generally overlapping the text.

But the overall sound quality is very good compared to the original editions.

.

1. Steven Wilson - Hand. Cannot. Erase. (CD, DVD-A)

This is THE album of 2015.

Not only for myself. But also for many magazines.

You may have never heard of Steven Wilson as he's not present usually on your regular radio station. But for almost everybody interested in prog rock he's called the "God of Prog" for reasons. He is/was the mastermind of Porcupine Tree, he runs many side projects such as No-Man and Blackfield - and he recently did awesome remasters of some of Jethro Tull's, Yes' and Gentle Giant's classic records. No idea when he finds time to sleep because he seems to be on tour almost half of the year.

I've seen him live several times, with Porcupine Tree as well as with his recent line-ups supported by guitar hero Guthrie Govan and drum legend Marco Minneman. And I'll see him again in January 13, 2016 in Munich. He's fantastic. Concerts are more a celebration of rock played at perfection levels but with a lot of energy and full of heart and passion.

Just as an update:
The show happened last night in Munich at the Philharmonie Classic Concert Hall - a quite unusual and suboptimal setting for a prog rock show. But it was truly awesome. Fantastic band (with Dave Kilminster from Roger Waters' Wall tour band replacing Guthrie Govan and Craig Blundell replacing Mario Minneman). Wonderful 2.5 hours of fantastic prog music ...  

Hand.Cannot.Erase. is inspired by a real story. In 2001 young London based Joyce Vincent died - but got found only more than 2 years later. Nobody noticed her disappearance. And this concept album is not only about this woman but also about isolation and our society not taking notice on anybody else. My favorite songs are Happy Returns and Perfect Life. But the entire album is full of great songs. It's an awesome piece of music.

It's actually the album I have listened the most often when being on the road.

If you'd like to read more:

Happy New Year :-)

 --Mike
.

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

Wednesday Dec 02, 2015

What or who is JOX? And what happens if ...

Are you worried because of the title of this blog post? Don't worry ... it will be about the Oracle database and not about miraculous additives.

Months back I wrote about how to remove options from the Oracle database's kernel by either using chopt or linking options off:

Recently a very interesting case was logged in relation to a bug:
A customer had linked off the JOX option from the database kernel.
.

What is JOX? 

Well, do a quick search for "JOX" in the Oracle Documentation and you'll get "zero results" (or you may get results in the 8.1 and 9.0 documentation which does not help here). So our documentation doesn't want to tell you further details on "JOX". But once you know that this is the acronym for the JavaVM JIT Compiler then we'll get more results leading directly to this spfile parameter:

The default in my environments is always TRUE. It enables or disables the Just-in-Time (JIT) compiler for the Oracle Java Virtual Machine (OracleJVM) environment.

And from here a link guides us into the Java Documentation explaining a bit more details about the JIT in case you are interested. 
.

Why am I writing this?

In theory you can unlink the JOX option - and compile oracle without the JIT:

$ cd $ORACLE_HOME/rdbms/lib
$ /usr/bin/
make -f /u01/app/oracle/product/12.1.0.2/rdbms/lib/ins_rdbms.mk jox_off ioracle

But let me add two things

  • We recently had a bug just happening because JOX was unlinked - all our tests have JOX linked in
  • As you can control the availability of JOX via the above initialization parameter I don't see a deeper reason to unlink JOX
  • And based on the feedback of Marcelo Ochoa (thanks Marcelo!!!) you'll encounter a performance dip in some components such as interMedia, XMLDB etc as code will now be interpreted, and not JIT compiled.
  • Furthermore based on the feedback of my former colleague, Norbert Debes, unlinking JOX may cause also trouble with the upgrade to Oracle Database 12c
My conclusion: There's no real reason to take JOX out of the RDBMS kernel. Please leave it linked in.


How do you find out if JOX is linked in?

You can't query the database for JOX as it is not mentioned in V$OPTION or DBA_REGISTRY. But you can find it out easily on the OS level: 

$ cd $ORACLE_HOME/rdbms/lib
$ ar -t libknlopt.a | grep -c jox.o

If the result is "1" it means "JOX installed", and "0"  means it is not installed.

Credits to Byron Motta from our Database Upgrade team! 

Further information about JIT?

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

DOAG Conference 2015 - My Talks

Guess how I realize that it's just 4 weeks and a bit towards Christmas?

When DOAG (German Oracle User Group) Conference is happening in my hometown Nürnberg in mid November every year. And this year's DOAG may be my 14th DOAG Conference as far as I remember.

Really looking forward to is as it is THE SPOT to meet with many German customers, some of them good friends, many many familiar faces, the DOAG leaders who all do a fantastic job - and I'll have the chance to see a lot of great presentations and learn a lot from experts, customers and my dear colleagues.

Only real downside? I'll have to board a train tomorrow early in the morning to be at the conference center right in time as my two appearance as a speaker will happen tomorrow only: 

Looking forward to see you there!

--Mike

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

.

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
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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