Wednesday Feb 03, 2016

DBUA and Read-Only Tablespaces - Things to Know

Some people prefer the manual upgrade on the command line, others prefer the graphical tool Database Upgrade Assistant (DBUA).

DBUA and Read-Only Tablespaces 

The DBUA offers you an option of setting your non-Oracle tablespaces read-only during the upgrade.

DBUA Read Only 1

What the option doesn't tell you is the purpose - and the use case when to "click" it on.

Partial Offline Backup 

The option of having data tablespaces which don't belong to the Oracle supplied components is simply to do an offline backup and - in case of a failing upgrade - restore quickly. You'll find this in our big slide deck under "Fallback Strategies - Partial Offline Backup". We have used this method in several scenarios:

  • Large telco systems where the time to restore/recover the entire database would have taken hours or days
  • DWHs where the database is large and intentionally operated in NOARCHIVELOG mode
  • Standard Edition databases where Guaranteed Restore Points in combination with FLASHBACK DATABASE are not available

FLASHBACK DATABASE is my all-time favorite as it is simple, fast and easy to use. You'll set a Guaranteed Restore Point - and in case of failure during the upgrade you'll flashback. Just don't forget to drop the restore point later when you don't need it anymore. Otherwise your FRA will run out of space the sooner or later. The only real caveat in this case is the fact that you can't change COMPATIBLE

When setting data tablespaces read-only the idea is to offline backup the "heart" of the database consisting of all files belonging to SYSTEM, SYSAUX and UNDO tablespaces plus the redologs plus the controlfiles. The tricky part: you'll have to backup also all other repository tablespaces. Those can exist for instance when the database has seen several upgrades already and started its life maybe in the Oracle 8i or 9i days. So you may see also XDB, DRSYS and ODM. You'll have to leave them in read-write as well during the upgrade and backup the files offline beforehand.


The Customer Case

The real tricky part is something Marvin hit and commented on the upgrade blog:

I am upgrading from 11.2.0.3 to 12.1.0.2. During the DBUA setup screens, I checked "Set User Tablespaces to Read Only During the Upgrade". It just seemed like the right thing to do. All of my tablespaces were ONLINE. All tablespace files were autoextendable. During the upgrade I got this error.

Context component upgrade error
ORA-01647 tablespace xxxxx is read-only.
Cannot allocate space in it.

There was plenty of space. I re-ran without the box checked and it ran ok. Just curious if anyone else has seen this.

The read-only option in DBUA has an issue - it does not detect all repository tablespaces right now.

DBUA Upgrade - Read Only Tablespaces

Marvin and I exchanged some mails and from the DBUA logs I could see what happened:

[AWT-EventQueue-0] [ 2016-01-28 11:07:03.768 CST ] [ProgressPane$RunNextProgressItem.run:1151]  Progress Item passedCONTEXT

[AWT-EventQueue-0] [ 2016-01-28 11:07:03.768 CST ] [ProgressPane$RunNextProgressItem.run:1151]  Progress Item passedCONTEXT

[AWT-EventQueue-0] [ 2016-01-28 11:07:03.768 CST ] [ProgressPane$RunNextProgressItem.run:1151]  Progress Item passedCONTEXT

[AWT-EventQueue-0] [ 2016-01-28 11:07:03.781 CST ] [ProgressPane$RunNextProgressItem.run:1154]  progress to next step CONTEXT

[Thread-364] [ 2016-01-28 11:07:43.758 CST ] [BasicStep.handleNonIgnorableError:479]  oracle.sysman.assistants.util.InteractiveMessageHandler@5bd44e0b:messageHandler

[Thread-364] [ 2016-01-28 11:07:43.759 CST ] [BasicStep.handleNonIgnorableError:480]  CONTEXT component upgrade error:

ORA-01647: tablespace 'WCI_OCS' is read-only, cannot allocate space in it

:msg

[Thread-364] [ 2016-01-28 11:15:42.179 CST ] [SummarizableStep$StepSummary.addDetail:783]  Adding detail: CONTEXT component upgrade error:

ORA-01647: tablespace 'WCI_OCS' is read-only, cannot allocate space in it

The repository of TEXT (or CONTEXT) is not in SYSAUX as it would be the default but in another tablespace. And this tablespace obviously was set to read-only as DBUA did not discover this tablespace as a repository but a regular user data tablespace. Bang!!!

Simple workaround:
Run the upgrade without the Read-Only Option. And this worked out fine. 

You can create the TEXT component by yourself and decide in which tablespace it should be created:

SQL> connect SYS/password as SYSDBA
SQL> spool text_install.txt
SQL> @?/ctx/admin/catctx.sql change_on_install SYSAUX TEMP NOLOCK

Thanks to my team mates Cindy, Hector and Byron

Yesterday I forwarded the email to our Upgrade Team and I received three replies within minutes explaining:

  • The query the DBUA uses is not as sophisticated as you would think:
    select tablespace_name from dba_tablespaces where contents != 'UNDO' and contents != 'TEMPORARY' and status = 'ONLINE' and tablespace_name != 'SYSTEM' and tablespace_name != 'SYSAUX' and tablespace_name != (select PROPERTY_VALUE from database_properties where PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE') 

  • We have proposed a improved query already

  • It should be included in a future release of the database 


Summary

The option having data tablespaces read-only during an upgrade is meant for a fast fallback in case of an failure during an upgrade. But your first option should always be a Guaranteed Restore Point instead. If you still need the read-only solution than please be careful as you may have repositories in non-standard tablespaces. DBA_USER's DEFAULT_TABLESPACE column may give you an indication - but you should also check DBA_SEGMENTS. And I personally would use this option in conjunction with a command line approach.

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

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 message quite disturbing. But this is a generic one and non-critical of course ...

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

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

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?

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.
My conclusion: There's no real reason to take JOX out of the RDBMS kernel. Better 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

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

APEX installed locally within the PDB only

  • 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

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

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
.

Tuesday Nov 10, 2015

Switch off "_rowsets_enabled" in Oracle Database 12c

Please find a recent update here:

 


 

Twitter is a good thing. I get alerted on things I haven't seen before. And sometimes some things are more than interesting.

This one is actually proven by Jonathan Lewis - and you can read all the details in Jonathan's blog post here:

There seems to be a realistic chance to get wrong query results displayed (regardless of using SQL*Plus or a JDBC or any other client - see the comment by Stefan Koehler below Jonathan's posting) because of the row sets feature in Oracle 12.1 allowing faster initial processing of query results.

You'll find this in the query information (marked in bold red):

Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "K"."KONTO_ID"[NUMBER,22]
   2 - "KWP"."WAEHRUNG"[VARCHAR2,20], "SV"."SONDERVERMOEGEN_ID"[NUMBER,22],
       "K"."KONTO_ID"[NUMBER,22]
   3 - "KP"."KONTO_FK"[NUMBER,22], "KWP"."WAEHRUNG"[VARCHAR2,20],
       "SV"."SONDERVERMOEGEN_ID"[NUMBER,22]
   4 - (#keys=1) "KP"."KONTO_FK"[NUMBER,22],
       "KP"."SONDERVERMOEGEN_FK"[NUMBER,22], "KWP"."WAEHRUNG"[VARCHAR2,20]
   5 - (rowset=256) "KP"."KONTOPOSITION_ID"[NUMBER,22],
       "KP"."SONDERVERMOEGEN_FK"[NUMBER,22], "KP"."KONTO_FK"[NUMBER,22]
   6 - (rowset=256) "KWP"."KONTOPOSITION_FK"[NUMBER,22],
       "KWP"."WAEHRUNG"[VARCHAR2,20]
   7 - "SV"."SONDERVERMOEGEN_ID"[NUMBER,22]
   8 - "K"."KONTO_ID"[NUMBER,22] 


Roy did some analysis yesterday as well (and now our Optimizer Support Experts look into this case to check whether any other wrong query result bugs are related to this topic). Because there are some open WRONG RESULTS bugs in this area, some of which were filed within the past week, we'd recommend to switch this feature off at the moment.  For instance these bugs are related to this misbehavior:

Recommendation - Updated (Nov 15, 2015)

Please read the update blog post here:


--Mike

Friday Nov 06, 2015

Oracle Database Release Status - MOS Note:742060.1

Almost every day I get at least one email by somebody asking things such as ...

  • What is the current status of Oracle 11.2.0.3? 
  • Is Oracle 11.2.0.4 Standard Edition One under Extended Support's treatment?
  • Do I get bug fixes for Oracle 12.1.0.1?
  • And is it really true that I don't get bug fixes for Oracle 11.2.0.3 anymore???

All valid questions - and all of them get answered within this reference note called:

Why am I writing this?

Simply because the Lifetime Support Policy Brochure only has now a link to a central MOS note added - but no explanatory text such as "Full Extended Support coverage will only apply to the terminal release". And the link is added in the right bottom corner underneath the table on (real) page 6 (the document's pagination doesn't count pages 1 and 2 as 1 and 2 for whatever reason). 

MOS Note:742060.1 has now an updated chart displaying clearly the Support Periods:

Oracle Release Chart

And a nice overview table clearly showing the Patching End Dates as well:

Oracle Patching End Dates for the Database

MOS Note:742060.1 is clearly THE REFERENCE in terms of questions such as:

  • How long will I get bug fixes for release X?
  • When is Premier Support ending for release Y?
  • Do I get Waived Extended Support for release Z?
.
--Mike
.

Tuesday Oct 20, 2015

Where do these large trace files come from in Oracle 12c?

Just had an observation about very large trace files on one of my customers I'm working with at the moment. When I write "very" I mean "VERY" as some grew over 10GB within a few hours.

Growth - Trace Files - (c) Mike Dietrich

The files contained a ton of such messages:

----- Cursor Obsoletion Dump sql_id=5p8a9d4017bq3 -----
Parent cursor obsoleted 1 time(s). maxchild=1024 basephd=00007FFB8AD45CB0 phd=00007FFB8AD45CB0

After doing a bit of research I came across this document and an explanation:

MOS Note:1955319.1;
Huge Trace Files Created Containing "----- Cursor Obsoletion Dump sql_id=%s -----"

Well, we introduced an Enhancement - via an unpublished bug (and I'd guess it is undocumented then) in Oracle Database 12.1.0.2 to improve cursor sharing diagnostics by dumping information about an obsolete parent cursor and it's child cursors after the parent cursor has been obsoleted N times.

You can control this behavior by altering the value for:

alter system set "_kks_obsolete_dump_threshold" = 0;

Possible value range is 0..8 whereas 0 means: switch the obsolete cursor dump off completely, and other values (N) defined the number of invalidations after which the cursor will be dumped.

The default in Oracle Database 12.1.0.2 is 1 meaning this will happen after every single invalidation

Just be aware that the underlying cursor sharing problem needs to be investigated always.

--Mike

Friday Oct 16, 2015

Ouch, this hurts: bug 20880215 - patch Oracle 12c

Patch

Just realized that my preivous blog post heading was misleading: it said "patch AFTER upgrade" which is misleading. You should apply this patch BEFORE you upgrade but of course to your new/future Oracle 12c home. If you hit the issue then apply the patch after upgrading to remedy it. Sorry for the confusion.


No updates for over a week? Sorry for that but too many workshops, swamped with OOW prep work - and a wonderful issue with the browser cache and the blog software putting all my new blog post work directly into /dev/null.

Thanks to Marcel Paul for highlighting this issue to me. I really benefit from such emails as I can learn a lot from those - and furthermore distribute information to many other tech folks as well.

Actually Marcel updated me as a follow up to me recent blog post:

describing an issue which will require to apply a patch before upgrade if you are not on Oracle 11.2.0.4.2 or higher - and the below issue is related to this one but requires a patch AFTER upgrade, regardless what your source version was.

Issue

Marcel let me know about an issue they saw with their upgrade from Oracle 11.2.0.3 to Oracle 12.1.0.2 after the upgrade causing a core dump and potential table metadata corruption when you'll ADD a column to a table with a DEFAULT and ENABLE NOVALIDATE. This issue gets treated as bug 20880215 (ORA-7445 [QCSISCOLINFRO()+358] FOR ADD COLUMN WITH DEFAULT AND ENABLE NOVALIDATE)
.

Test Case

Very simple test case done by Marcel (thanks again!) - I could verify it within 30 seconds. 

create table dummy(code varchar2(5), text varchar2(30));
insert into dummy(code, text) values ('CD1','Hello World');
commit;
select * from dummy;
alter table dummy ADD condition varchar2(3) DEFAULT 'YES' not null enable novalidate;
select * from dummy;
:

Result

First of all the client (in my case SQL*Plus) will disconnect with the meaningless ORA-3113. So lets have a look into the alert.log for more useful information:

Wed Oct 14 15:06:59 2015
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4] [PC:0xCDB0046, qcsIsColInFro()+358] [flags: 0x0, count: 1]
Errors in file /oradata/diag/rdbms/cdb2/CDB2/trace/CDB2_ora_2424.trc  (incident=3433) (PDBNAME=CDB$ROOT):
ORA-07445: exception encountered: core dump [qcsIsColInFro()+358] [SIGSEGV] [ADDR:0x4] [PC:0xCDB0046] [Address not mapped to object] []
Incident details in: /oradata/diag/rdbms/cdb2/CDB2/incident/incdir_3433/CDB2_ora_2424_i3433.trc

Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

Wed Oct 14 15:07:08 2015
Dumping diagnostic data in directory=[cdmp_20151014150708], requested by (instance=1, osid=2424), summary=[incident=3433].

So we've got a core dump. And whatever we query now from the table involving the column "condition" we'll get the same error.
.

Solution

Apply the fix for bug 20880215 (ORA-7445 [QCSISCOLINFRO+358] FOR ADD COLUMN WITH DEFAULT AND ENABLE NOVALIDATE). You may have to request the fix for your platform if it hasn't been done yet. 
.

Further Information

A request for inclusion into the next Bundle Patches had been filed as well.
.

--Mike 

Tuesday Oct 06, 2015

New in Oracle 12c: _optimizer_gather_stats_on_load

Received an email from Roy last night with some performance issues a customer in the US encountered recently during their upgrade testing

One issue the customer encountered has to do with tons of parallel slaves creating a massive noise on the system when they are doing a CTAS (Create Table As Select) - and the same thing happens with an IAS (Insert Append Select).
.

What caused this change?

In this case the behavior change is well documented, even though not linked to the responsible underscore parameter. 

Quoting from the first paper: 

Online statistics gathering

In Oracle Database 12c, online statistics gathering “piggybacks” statistics gather as part of a direct-path data loading operation such as, create table as select (CTAS) and insert as select (IAS) operations. Gathering statistics as part of the data loading operation, means no additional full data scan is required to have statistics available immediately after the data is loaded.

The parameter controlling this change is not mentioned:

  • _optimizer_gather_stats_on_load

The default is TRUE since Oracle 12.1.0.1 - the parameter or functionality did not exist before Oracle Database 12c.
.

Things to Know

The online stats gathering for IAS can happen only if the object you are loading data into is empty. You'll recognize the feature when you see a line saying OPTIMIZER STATISTICS GATHERING in the execution plan. Additionally you'll find STATS_ON_LOAD in the NOTES column of below query:

select COLUMN_NAME, NUM_DISTINCT, DENSITY, HISTOGRAM, NOTES from USER_TAB_COL_STATISTICS where TABLE_NAME='MYTAB' ;

Consider now that first of all immediate stats gathering may not be desired as it will mean additional load to the system. Even though the feature has been designed to generate as little load as possible it's still doing something in the background. Hence there may be situations where you'd like to switch it off, e.g. with a hint:

insert /*+append NO_GATHER_OPTIMIZER_STATISTICS*/ into MYTAB select ...

And how about the number of parallel slaves creating the statistics? 

There's no easy answer to this based on the information I have at the moment - but I'd highly recommend this article about parallel execution in Oracle:

--Mike

Tuesday Sep 29, 2015

No OS Authentication? datapatch will fail in every upgrade

Thanks to Daniel from SimCorp for bringing this to my attention:Patch
Actually, command line upgrades are affected as well,
if you do not use OS authentication. Apparently, datapatch
is not able to execute in non-OS authentication mode.
See MOS note 1635007.1. 
.

Symptom:

You are doing a command line upgrade to Oracle Database 12c with catctl.pl - and you don't use OS authentication allowing connections with "/ as sysdba" then datapatch.pl won't be able to execute the SPU/PSU/BP related SQL commands as it will fail to connect to your database with an ORA-1017 (invalid username/password) error.
.

Solution:

Bug 18361221 is fixed in Oracle 12.2 and got backported to 12.1.0.1.2 and 12.1.0.1.3 but not actually included in any 12.1.0.2 bundles at the moment. Without this fix, datapatch will only connect with '/ as sysdba'. 

Either apply generic patch 18361221 to your destination Oracle Home or switch on OS authentication by setting:

  • Unix: SQLNET.AUTHENTICATION_SERVICES = (BEQ)
  • Windows: SQLNET.AUTHENTICATION_SERVICES = (NTS)
temporarily in your sqlnet.ora for the duration of the upgrade only. See the documentation for further information about SQLNET.AUTHENTICATION_SERVICES. Or, of course, run datapatch.pl -verbose after upgrading your database in any case ...
.

Further Information and Links:

Related Blog Posts datapatch.pl:

--Mike
.

Thursday Sep 17, 2015

SE2 - Some questions, some answers ...

Oracle Standard Edition SE2 12.1.0.2*** Updated on Nov 6  to reflect 11.2.0.4 changes***

There were a lot of questions regarding Oracle Database Standard Edition 12.1.0.2 SE2 in the past days.And you may find some things mentioned especially in the COMMENT section of the blog post from Sep 1, 2015.

And please find the official Oracle SE2 "brief" document here giving you a good overview on SE2:
http://www.oracle.com/us/products/database/oracle-db-se2-brief-2680836.pdf

I'll try to summarize some topics I'm able to answer by myself. And please don't expect me to assist with license or license migration questions - that is far beyond my scope or knowledge or responsibility, and you may please contact your Oracle sales person regarding these topics instead.

Please find all our articles about Oracle Database Standard Edition 12.1.0.2 SE2:

Software and Support 

  • Q:
    How long will Premier Support offered for Oracle Database Standard Edition 12.1.0.1 SE and SE1?
  • A:
    Premier Support for Oracle Database Standard Edition 12.1.0.1 SE and SE1 will continue until Aug 31, 2016. After this date (as of Sep 1, 2016) Oracle 12.1.0.1 SE/SE1 will enter Sustaining Support.
    .
  • Q:
    Will there be Extended Support offered for Oracle Database Standard Edition 12.1.0.1 SE and SE1?
  • A:
    No. Extended Support for 12.1 SE and SE1 is not offered. The Lifetime Support Policy document has been updated to reflect this.
    .
  • Q:
    Is the extension of the Waived Extended Support and the Paid Extended Support for Oracle 11.2.0.4 valid for SE and SE1 as well?
  • A:
    Yes, it is - there's actually no distinction (as far as I know) between support periods for SE, SE1 and EE.
    .
  • Q:
    Are there plans to release Oracle 12.1.0.2 SE/SE1?
  • A:
    Oracle Standard Edition 12.1.0.2 will be offered as SE2 only.
    .
  • Q:
    Is hard partitioning allowed with SE2?
  • A:
    Yes, it is as long as there are no more that 2 physical sockets in the server and you are using a supported hard partitioning software to bind a VM to a specific CPU. SE2 is not allowed to run on servers with more than 2 sockets.
    For an explanation and a list of supported hard partitioning technologies please see here:
    http://www.oracle.com/us/corporate/pricing/partitioning-070609.pdf
    .
  • Q:
    Is Oracle Multitenant available with SE2?
  • A:
    Oracle Multitenant is an Oracle Enterprise Edition only license. But you can have Oracle Single Tenant with many benefits meaning you'll have one pluggable database within one container database. Features such as remote cloning will be fully supported. See: https://blogs.oracle.com/UPGRADE/entry/can_you_have_oracle_multitenant
    .
  • Q:
    Can we move PDB from SE2 Single Tenant to a EE Single/Multitenant database? 
  • A:
    Yes, of course - this is fully supported.

Database Installation and Patching

  • Q:
    Can I install Oracle Standard Edition 12.1.0.2 SE2 into the existing home (in-place)?
  • A:
    Yes, this is possible even though I would never recommend to do this for the simple reason that wiping out your current home will increase the potential downtime and cause you plenty of extra work in case of fallback. If you still want to install for whatever reason into the existing home there's no change in handling to any other Oracle release since Oracle 11.2.0.1:
    https://blogs.oracle.com/UPGRADE/entry/why_is_every_patchset_now
    .
  • Q:
    Which PSU should I apply on top of the releases Oracle Database Standard Edition 12.1.0.2 SE2 installation?
  • A:
    Take the same PSU as you would take for an EE database - there's no difference and there won't be specific PSUs (or SPU/CPUs or BPs) released for SE2. Check out MOS Note:1683799.1 (12.1.0.2 Patch Set - Availability and Known Issues) for the most recent recommended PSU
    .
  • Q:
    Is RAC (Real Application Clusters) still included in SE2?
  • A:
    Yes it is - limited to 2 nodes, and each node must be a single-socket server. See above: there's the possibility to either remove a CPU physically - or hard partitioning with a supported virtualization solution such as OVM.
    .
  • Q:
    I would like to use SE2 with RAC. Which Grid Infrastructure will I need to download?
  • A:
    When deploying a SE2 RAC Cluster please download the regular Oracle Grid Infrastructure (GI) packages from the official download locations (OTN, MOS, eDelivery). There's no separate download for an SE2 GI.
    ,
  • Q:
    Can I install/use SE2 on a 4-socket server?
  • A:
    No - SE2 is limited to be deployed on 2-socket servers only. And physically removing 2 CPUs from a 4-socket-machine does not satisfy the license agreement.
    .
  • Q: 
    Can I deploy more than one SE2 database on a server?
  • A:
    Yes, of course you can.

Database Upgrade

  • Q:
    I want to upgrade from Oracle 12.1.0.1 SE/SE1 to Oracle 12.1.0.2 SE2. Is there any specific procedure or tool to use?
  • A:
    Upgrade works exactly as expected. There's no difference for SE2 compared to the upgrade procedure from Oracle 12.1.0.1 EE to Oracle 12.1.0.2 EE. 
    .
  • Q:
    I would like to upgrade from Oracle 12.1.0.1 SE/SE1 to Oracle 12.1.0.2 EE. Is there any specific procedure or tool to use?
  • A:
    Upgrade is always the same procedure, regardless of SE, SE1, SE2 or EE. But if you'll convert from SE/1/2 to EE please follow the advises in MOS Note:2046103.1 (How to Convert Oracle Database 12c from Standard to Enterprise Edition ?)
    .

Other topics

  • Q:
    Does the thread limitation in SE2 (16 in single instance mode, 8 per node in RAC mode) include background processes?
  • A:
    No, background processes such as LGWR or PMON are not included in this limitation.
    .
  • Q:
    Is the thread limitation valid across all instances on the same server?
  • A:
    No, the limit is enforced per database instance and not per server.
    .
  • Q:
    Does the thread limitation happen based on number of OS threads or CPU threads?
  • A:
    Actually the use of the term "threads" may be a bit misleading. Please have a look into the documentation about Instance Caging. That will explain how the thread limitation will be enforced. Views to monitor the resource usage are: V$RSRC_CONSUMER_GROUPV$RSRCMGRMETRIC and V$RSRCMGRMETRIC_HISTORY.
    .
  • Q:
    Is there a core limitation for the hardware SE2 is installed on?
  • A:
    No, the box you'll use Oracle SE2 on can have as many cores as the CPUs offer. But there's a socket limitation of a maximum of 2 physical sockets. But the most modern CPUs can present 36 cores to SE2 on a 2-socket machine. 
    .
  • Q:
    Can I disable Hyper Threading to present more "real" threads to the database?
  • A:
    Yes, this is possible but the effect may not be huge.
    .
  • Q:
    Can I license any options/packs with SE2 such as Diagnostics or Tuning Packs?
    A:
    Unfortunately you can't license any additional packs or options with SE2. You will have to rely on things such as PERFSTAT and similar tools.

--Mike

Monday Sep 14, 2015

SQL Plan Management - Known Issues in Oracle 12.1.0.2

Our Support colleagues released the patch recommendation note for SQL Plan Management (SPM) for Oracle Database 12.1.0.2.

 

SPM Note

In the unlikely event you'd like to upgrade to Oracle Database 11.2.0.4 or (very very unlikely hopefully) 11.2.0.3 please see these matching notes:

 

Some additional things to mention:

  • SPM is an Oracle Enterprise Edition feature at no extra cost
  • SPM is THE feature to ensure plan stability tackling changes such as (of course) upgrades and migrations
  • SPM has been improved a lot internally in Oracle Database 12.1.0.2:
    • We now store entire plans instead of a large accumulation of hints in the SQL Management Base (SMB) in SYSAUX tablespace
    • The "Evolve" task does happen automatically (SYS_AUTO_SPM_EVOLVE_TASK) as part of the Automatic SQL Tuning Task 
  • You should always adjust the retention when starting to play with SQL Plan Management as the default retention of 53 weeks may lead to a too large LOB segment in SYSAUX tablespace (and LOB segments never shrink)
    • SQL> exec DBMS_SPM.CONFIGURE('plan_retention_weeks',5);
  • See the Oracle Database 12c documentation about SPM:

--Mike

 

Thursday Sep 10, 2015

Don't get puzzled by "sqlpatch" messages during Upgrade

During my last Hands-On-Labs in Uruguay and Argentina I've had several people wondering about these messages below when running the command line upgrade with catctl.pl:

catctl.pl - sqlpatch

This (and another) message breaks the nicely structured format of the catctl.pl output. And as it ends with an "err" extension it looks to many people as if the upgrade had gotten an error,

But please don't feel disturbed. It's just messages from sqlpatch invocation - and the "err" extension is just pointing to an error file in case something has gone wrong.

In a future release such messages will be written to the logfiles only but not to the screen output anymore.

--Mike

Wednesday Sep 09, 2015

Different Metrics for SPA (SQL Performance Analyzer)

I'm more the command line type of person. Once I've understand what's going on behind the curtains I certainly switch to the GUI-click-click tools. But in the case of Real Application Testing - even though the support via the OEM GUI is excellent - sometimes I prefer to run my procedures from the command line and check my reports in the browser.

Recently Thomas, a colleague from Oracle ACS Support, and I were asking ourselves about the different comparison metrics for the SQL Performance Analyzer reporting We did scan the documentation but we found only examples but no complete list. Then we did ask a colleague but thanks to OEM we got an incomplete list as well.

Finally Thomas dug it out - it's stored in the dictionary in the table V$SQLPA_METRIC

SQL> SELECT metric_name FROM v$sqlpa_metric;

METRIC_NAME   
-------------------------
PARSE_TIME               
ELAPSED_TIME             
CPU_TIME                 
USER_IO_TIME             
BUFFER_GETS              
DISK_READS               
DIRECT_WRITES            
OPTIMIZER_COST           
IO_INTERCONNECT_BYTES    

9 rows selected.

What do you do with these metrics now?

You can use them in such a way:

set timing on

begin
dbms_sqlpa.execute_analysis_task(
   task_name=>'SPA_TASK_MR07PLP_11107_12102',
   execution_name=>'Compare workload Elapsed',
   execution_type=>'compare performance',
   execution_params=>dbms_advisor.arglist(
                     'comparison_metric','elapsed_time',
                     'execution_name1','EXEC_SPA_TASK_MR07PLP_11107',
                     'execution_name2','TEST 11107 workload'),
   execution_desc=>'Compare 11107 Workload on 12102 Elapsed');
end;
/

You can vary the elapsed_time in my example with the various comparison metrics mentioned in v$sqlpa_metric

--Mike 

Tuesday Sep 08, 2015

MOS Download for Oracle Database 12.1.0.2 including SE2

Please find all our articles about Oracle Database Standard Edition 12.1.0.2 SE2:

 

You have 3 options to download Oracle Database Enterprise Edition 12.1.0.2 and Oracle Database Standard Edition 12.1.0.2. SE2.

Regarding OTN and eDelivery nothing has been changed since Sep 1, 2015 when Oracle Database Standard Edition 12.1.0.2 SE2 got published. But the download from MOS now has changed.

When you search in the under Patches and Updates tab ...

Patches And Updates - MOS

... and display the Latest Patch Sets ...

Latest Patch Sets - MOS

... check for instance for platform Linux x86-64bit ...

MOS - 12.1.0.2 Linux

... then you'll wonder about the two patch numbers ...

Patch Numbers Oracle SE2 12.1.0.2

The only difference of 2.5 GB in size results from two more zip files included in the new patch number 2141921 ...

... you'll have to check the README button to find out what's hidden behind each file ...

Files 3 and 4 contain Oracle Database Standard Edition 12.1.0.2 SE2.

So please don't get puzzled by the different patch numbers - the only real difference is the inclusion of SE2 into the download package but the included patches etc are exactly the same as in the previous patch number. 

--Mike

.

Monday Sep 07, 2015

Oracle Database 12.1.0.2 SE2 - Support and Patches for Oracle Database 12.1.0.1 SE/SE1

Oracle SE2

Please find all our articles about Oracle Database Standard Edition 12.1.0.2 SE2:

 

Some questions regarding support for Oracle Database Standard Edition 12.1.0.1 (SE/SE1) came up in the past days.

Most of those are answered already in MOS Note 2027072.1 (Oracle Database 12c Standard Edition 2 (12.1.0.2) )

How long will Oracle Database 12.1.0.1 in any edition remain under Premier Support? 

Full patching support for 12.1.0.1 for all versions of 12gR1 (Enterprise Edition, Standard Edition and Standard One Edition) will be proviced for an additional 12 months from the release of 12.1.0.2 SE2, so through until end August 2016. 

After that period Oracle Database 12.1.0.1 will enter Sustaining Support. There won't be any Extended Support for Oracle Database 12.1.0.1 in any edition.
.

Will there be PSUs and SPUs/CPUs available?

Yes, quarterly Patch Set Updates and Critical Patch Updates for 12.1.0.1 will continue to be delivered until end of Premier Support for Oracle Database 12.1.0.1.  
.

Will there be an Oracle Database Standard Edition 12.1.0.2 SE/SE1? 

Beginning with the release of Oracle Database 12.1.0.2,  Oracle Database Standard Edition (SE) and Oracle Database Standard Edition One (SE1) are no longer being released.  12.1.0.1 was the final edition that we will produce for SE and SE1.  

MOS Note:742060.1 Release Schedule of Current Database Releases does reflect this extension already:

Release Patching Ends

Notes and Exceptions*

12.1.0.1 31-Aug-2016

.

--Mike 

Thursday Sep 03, 2015

Can you have Oracle Multitenant in Oracle 12.1.0.2 SE2?

Please find all our articles about Oracle Database Standard Edition 12.1.0.2 SE2:

 

Can you have Oracle Multitenant in Oracle Standard Edition 12.1.0.2 SE2?

No, you can't as Oracle Multitenant is a licensable option for Enterprise Edition (EE) databases only.

But wait a second ...

You can do Oracle Single Tenant with SE2 of course meaning you can have one active pluggable database within one container database at a time. That is possible and does not require any additional licenses.

See the DBCA (Database Configuration Assistant) screen when you'll try to create a container database:

DBCA - Single Tenant

More information? 

You'll find more information about the differences in handling and such between Oracle non-CDB, Oracle Single Tenant and Oracle Multitenant databases in this presentation:

--Mike 

Tuesday Sep 01, 2015

Upgrade downtime credited to APEX

Upgrade Blog posts about Oracle Application Express: 


What do you think when you see this post-upgrade result?

Oracle Database 12.1 Post-Upgrade Status Tool           08-07-2015 15:08:26

Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS

Oracle Server                          UPGRADED      12.1.0.2.0  00:19:26
JServer JAVA Virtual Machine              VALID      12.1.0.2.0  00:10:52
Oracle Workspace Manager                  VALID      12.1.0.2.0  00:01:52
OLAP Analytic Workspace                   VALID      12.1.0.2.0  00:00:34
OLAP Catalog                         OPTION OFF      11.2.0.3.0  00:00:00
Oracle OLAP API                           VALID      12.1.0.2.0  00:00:42
Oracle XDK                                VALID      12.1.0.2.0  00:01:07
Oracle Text                               VALID      12.1.0.2.0  00:01:36
Oracle XML Database                       VALID      12.1.0.2.0  00:03:55
Oracle Database Java Packages             VALID      12.1.0.2.0  00:00:22
Oracle Multimedia                         VALID      12.1.0.2.0  00:03:57
Spatial                                UPGRADED      12.1.0.2.0  00:08:56
Oracle Application Express                VALID     4.2.5.00.08  00:46:19
Final Actions                                                    00:03:48

Total Upgrade Time: 01:44:16

I've got a bit worried as the time to upgrade APEX took 44% of the complete database upgrade downtime. APEX (Oracle Application Express) is a fantastic piece of software which is still completely underrated - potentially because it is for free for everybody who has an Oracle Database license. And things not costing anything are just worth nothing, ey? 

Simply be aware when you have APEX in your databases installed - and especially if you ACTIVELY use APEX - it may be a very good idea to upgrade APEX upfront without causing downtime for your entire database.

See this blog post here about how to upgrade APEX upfront:

https://blogs.oracle.com/UPGRADE/entry/save_upgrade_downtime_upgrade_apex 

--Mike

Friday Aug 21, 2015

DBMS_STATS. GATHER_DICTIONARY_STATS fails with ORA-20001 ORA-6502 ORA-6512 - Concurrent Stats

I really have to say "Thank you very much" to the people out there alerting me about issues I haven't seen before. Just in the past week I've got to learn about three issues which are related to the upgrade - and I haven't seen before. Please don't expect me always to follow up with the progress of your SRs or the related bugs. But your experiences are such an important source for me - so thanks again!

The most recent issue (thanks to Bernd Tuba from MM Warburg) ...

Symptom

execute dbms_stats.gather_dictionary_stats

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_STATS", line 24268
ORA-20001: Unexpected configuration: sofar: 41 objList.count: 41 sofar_part: 0 partObjList.count: 0 sofar_ind: 0 indObjList.count: 21 concurrent: TRUE runPartTable: TRUE jstats.Running: -19
ORA-06512: at "SYS.DBMS_STATS", line 27383
ORA-06512: at "SYS.DBMS_STATS", line 27402
ORA-06512: at line 1

I see the word "CONCURRENT" - and it immediately rings a bell. Wasn't there an issue with the change of setting for default stats gathering in Oracle Database 12.1.0.2 being now CONCURRENT=TRUE?

Yes ... the reason for this blog's existence is not only to give you some interesting useful information to read about but also to dump of details from my limited brain capacity :-) I have written something about a similar issue in October 2014:

https://blogs.oracle.com/UPGRADE/entry/ora_20000_unable_to_gather
.

Analysis

This is interesting. Whereas in my other blog post the concurrent stats gathering conflicted with the resource manager settings and is fixed by a patch (Patch 19664340: ORA-20000: UNABLE TO GATHER STATISTICS CONCURRENTLY: RESOURCE MANAGER IS NOT ENABLED) this one is a different topic.

Bug:18406728  DBMS_STATS.GATHER_DICTIONARY_STATS GOES INTO SPIN CONSUMING 100% CPU 
(unpublished bug - therefore no link included here)

Solution

Very simple to solve - switch CONCURRENT stats gathering to FALSE

SQL> exec dbms_stats.set_global_prefs('CONCURRENT', 'FALSE');

In the Exadata Upgrade Note (MOS Note: 1681467.1) you'll find the same recommendation. 

More Information? 

--Mike 

About

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

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

- -

Search

Archives
« February 2016
SunMonTueWedThuFriSat
 
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
     
       
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