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
.

Monday Nov 02, 2015

Ouch, this hurts: bug 21923026 - patch Oracle 12c Home before upgrading if you have OLTP Compression in 11g

Patch

Actually I'd consider this as a real serious issue which may affect many customers with larger deployments using the Advanced Compression Option's OLTP Compression.

I came across it as Don Seiler full of anger twittered some bad words about Oracle Database 12.1.0.2.. We've exchanged a good number of emails - and I could see the issues Don and his colleagues got while assisting a customer to go live on Oracle Database 12c.

The most interesting one happened on the physical standby after the primary got upgraded. The MRP failed with an ORA-600.
.

Issue

After upgrading from Oracle 11.2.0.3 to Oracle 12.1.0.2 the MRP (Managed Recovery Process) on the physical standby database is failing with:

ORA-600 [kdBlkCheckError], [5]

Restoring the data file does not solve the issue.

This happens if the source table has OLTP Compression enabled, and this database has been upgraded to Oracle Database 12.1.0.2

This corruption on compressed tables will happen during media recovery while applying redo generated for those tables when the source database was running on the Oracle 11g software version.

This also applies to Oracle 12c upgraded physical standby databases when recovering redo that was generated on 11g.

The latter is actually the issue Don must have had encountered here.
.

Which versions are affected?

This happens only in Oracle Database 12.1.0.1 and Oracle Database 12.1.0.2. The issue is fixed in the MAIN code line in Oracle 12.2. You can detect tables with OLTP Compression with these queries:

select owner, table_name, compression, compress_for  
from   dba_tables
where  compress_for in ('ADVANCED','QUERY LOW','QUERY HIGH','ARCHIVE LOW','ARCHIVE HIGH','OLTP');

select table_owner, table_name, compression, compress_for  
from   dba_tab_partitions
where  compress_for in ('ADVANCED','QUERY LOW','QUERY HIGH','ARCHIVE LOW','ARCHIVE HIGH','OLTP');

select table_owner, table_name, compression, compress_for  
from   dba_tab_subpartitions
where  compress_for in ('ADVANCED','QUERY LOW','QUERY HIGH','ARCHIVE LOW','ARCHIVE HIGH','OLTP');

Workaround/Solution

Uncompress the table would work - but is not a pleasant idea.

Install the fix for Bug 21923026 in the new Oracle 12c home BEFORE upgrading the database

Issue got filed as bug 21682261 but has been replaced by the patch for bug 21923026. It should be applied to Oracle 12.1.0.2 before upgrade. If you hit the issue on a physical standby then apply the patch to the standby's home first, then refresh the affected data file by RMAN with a copy of this file from production - and restart MRP again.

Patch 21923026 is available on top of a plain Oracle 12.1.0.2 installation but also on top of several PSUs and BPs. Please check the RELEASE selection of the patch download for bug 21923026
.

Further Information

  • Bug 21682261 
    ORA-600 [KDBLKCHECKERROR]..[6126] OR [6110] FOR COMPRESS BLOCK DURING RECOVERY
    (non published bug - therefore no link added)

--Mike


Friday Oct 30, 2015

OOW 2015 - Upgrade Hundreds and Thousands

Uhh .. OOW15 is finally over.

Lots of work - tons of great input - and so many excellent discussions with customers, users and Oracle ACE's.

Just in case you'd like to download our slides please find the in the Slides Download Center to your right.

Thanks again - and hope to see you again next year!

--Mike

 

Tuesday Oct 27, 2015

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

Thank You, Thank You, Thank You!!!

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

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

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

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

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

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

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

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

--Mike

Monday Oct 26, 2015

OOW 2015 - if you've missed our Upgrade Hands On Lab

Ready to kick off with the first of our four Hands-On-Labs at OOW15 at the Nikko Hotel. Currently it's calm before the storm  ...

OOW 2015

But just in case you didn't grab a seat at one of the labs then please come to our talk today (Monday!) at 1:30pm in Moscone South 102 as we'll show you the core parts of the lab - and you'll be able to download it later on from the blog and run it by yourself.

Download the lab via this link:

--Mike

Oracle Database 12.2 - just if you weren't aware

News from the keynote on Sunday, Oct. 25, 2015 at OOW 2015:

Availability of Oracle Database 12.2 ...

So still a while to go ;-)

--Mike

Friday Oct 23, 2015

OOW 2015 - Upgrade and Migration Talks

Oracle Open World 2015
will kick off in a day and a bit.
And still some work to do ;-)

A few things I would like to mention:

  • Our four HOLs are all overbooked already with many people on the waiting lists. You can always come by and wait in line at the Nikko Hotel's Golden Gate lab room as some people don't show up. But no guarantee. What I would recommend to you in case you are interested in the lab but didn't get a seat:

    Come to our talk instead on Monday at 1:30pm as Roy and I will demonstrate LIVE and UNCENSORED parts of the lab. Then you'll download it from the blog (find it in the Slides Download Center) and try it out by yourself afterwards: Hands On Lab Upgrade, Migrate, Consolidate to 12c

    Upgrade and Migrate to Oracle Database 12c: Live and Uncensored! [CON6777]
    Mike Dietrich, Master Product Manager, Oracle
    Roy Swonger, Sr Director, Software Development, Oracle

    Monday, Oct 26, 1:30 p.m. | Moscone South—102
    .
    .
  • Our 2nd talk will happen on Wednesday at 12:15pm (skip the unhealthy lunch).
    We'll contrast some nasty things with some very good and detailed customer examples included in it. Real world examples, no artificial lab exercises. Again uncensored (mostly ;-) ) and just from first hand experience.

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

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

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

    Thursday, Oct 29, 9:30 a.m. | Moscone South—305
    .
    .
  • And the 4th talk I'd like to highlight is the Database Upgrade 12c - Oracle Support talk from Support Architect Roderick Manalac.

    Oracle Database 12c Upgrade: Tools and Best Practices from Oracle Support [CON8664]
    Roderick Manalac, Architect - Database Support, Oracle
    Thursday, Oct 29, 12:00 p.m. | Moscone South—305
    .
    .

Ship

CU soon :-)

--Mike

Thursday Oct 22, 2015

PSU (and CPU/SPU) October 2015 got released

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

See:

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

Patch Set Updates

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

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

Grid Infrastructure

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


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

--Mike

.

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" = N;

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.
If you have cursor sharing issues you may set this parameter higher therefore not every invalidation causes a dump, then investigate and solve the issue, and finally switch the parameter to 0 once the issue is taken care of. 

Please be aware that switching the parameter to 0 will lead to a lack of diagnostics information in case of cursor invalidations.

--Mike

Saturday Oct 17, 2015

Extended Support Fee for Oracle 11.2.0.4 waived until May 31, 2017 - Extended Support until Dec 2020

Friday, Oct 16, 2015, Oracle announced that the Extended Support for Oracle Database 11.2.0.4 will be waived until May 31, 2017. After this period of Waived Extendend Support, Extended Support for Oracle Database 11.2.0.4 will be offered until end of December 2020.

This information can be found here:

Something to mention:

  • There's no bug fixing support for Oracle Database 11.2.0.3 or below available anymore
  • If you'll compare the effort to upgrade to Oracle Database 11.2.0.4 to Oracle 12.1.0.2 you'll have to do the exact same amount of work. tests etc - no difference


    .
  • End of Premier Support means that Oracle will not offer fixes for new issues unless an extra-cost offer called Extended Support has been purchased. But you'll still get Sustaining Support until you'll stop working on the release. For a description what terms such as Premier Support, Extended Support and Sustaining Support mean please refer to the Lifetime Support Policy.
    Premier Support for Oracle Database 11.2 ended on Jan-31, 2015
    .
  • For Oracle Database 11.2.0.4 - the Terminal (final) Patch Set for Oracle Database 11.2 - Oracle will waive (no extra cost, no action required) Extended Support to every customer with a valid support contract until May 31, 2017.
    You can extend the Extended Support for Oracle Database 11.2.0.4 until 31-DEC-2020 - but then at the usual extra cost. 
    This information can be found in MOS Note:742060.1.
    .
  • For Oracle Database 11.2.0.3 Oracle has waived the Extended Support, but as Extended Support for Oracle Database 11.2.0.3 ended on 27-AUG-2015 no bug fixes are available (including PSUs, SPUs and BPs) anymore.
    There's no bug fixing support for Oracle Database 11.2.0.3 anymore.
    This information can be found in MOS Note:742060.1.
    .
  • For Oracle Database 11.2.0.2 there's no Extended Support offering available.
    There's no bug fixing support for Oracle Database 11.2.0.2 anymore.
    This information can be found in MOS Note:742060.1.
    ..
  • For Oracle Database 11.2.0.1 there's no Extended Support offering available.
    There's no bug fixing support for Oracle Database 11.2.0.1 anymore.
    This information can be found in MOS Note:742060.1.
    .
--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

Friday Oct 02, 2015

OOW 2015 Sessions and Labs - Oracle Open World

OOW 2015OMG ... only a few weeks to go ... Oracle Open World 2015 in San Francisco is coming closer and closer ...

And this year will be really tough as we have a reduced number of people there - but more work to do as in previous years. 3 talks (2 for Upgrade, 1 for Data Pump), 4 labs (all in Nikko Hotel 15min walking distance from Moscone Center) - plus a good number of customer meetings already lined up. Plus the chance to meet so many great people ... and not to forget the Data Warehouse Global Leaders event at the Oracle HQ. 

I have that strange feeling that I will be VERY tired when I'll board the plane on Friday night heading back to Germany ... ;-)
.

Focus On Upgrades/Migrations

As the fantastic application we are using for the OOW content catalog doesn't allow me to link directly to a session Roy has built a Focus On document to guide you to some important talks around Upgrades and Migrations at OOW2015.for your convenience: 


Talks

Session ID

Title

Start Time

Room

CON6777 Upgrade and Migrate to Oracle Database 12c: Live and Uncensored!

Many customers now have database environments numbering in the hundreds or even thousands. This session addresses the challenge of maintaining technical currency of such an environment while also containing upgrade and migration costs at a reasonable level. Learn from Oracle Database upgrade experts about product features, options, tools, techniques, and services that can help you maintain control of your database environment. You will also see examples of how real customers are successfully meeting this challenge today.
.
.
October 26
at 13:30h
Moscone South—102
CON8375 How to Upgrade Hundreds or Thousands of Databases in a Reasonable Amount of Time

Many customers now have database environments numbering in the hundreds or even thousands. This session addresses the challenge of maintaining technical currency of such an environment while also containing upgrade and migration costs at a reasonable level. Learn from Oracle Database upgrade experts about product features, options, tools, techniques, and services that can help you maintain control of your database environment. You will also see examples of how real customers are successfully meeting this challenge today.
.
.
October 28
at 12:15h
Moscone South—102
CON8376 Deep Dive: More Oracle Data Pump Performance Tips and Tricks

The Oracle Data Pump development team is back with even more performance tips and tricks for DBAs! In this session, learn about Oracle Data Pump features, parameters, and patches—some added since the first patch set of Oracle Database 12c 12.1.0.2—that will improve performance and decrease overhead for Oracle Data Pump projects. Whether you are an Oracle Data Pump novice or already an expert, you are sure to learn something new in this session that will help you maximize the throughput of your export and import operations.
.
.
October 29
at 9:30h

Moscone South—305

HOL10348 Upgrade, Migrate, and Consolidate to Oracle Database 12c [HOL10438]

The Oracle Data Pump development team is back with even more performance tips and tricks for DBAs! In this session, learn about Oracle Data Pump features, parameters, and patches—some added since the first patch set of Oracle Database 12c 12.1.0.2—that will improve performance and decrease overhead for Oracle Data Pump projects. Whether you are an Oracle Data Pump novice or already an expert, you are sure to learn something new in this session that will help you maximize the throughput of your export and import operations.
.
Oct 26 at 11:00h
Oct 27 at 11:45h
Oct 28 at 13:15h
Oct 29 at 12:30h

Hotel Nikko - Golden Gate


Hope to see you at OOW 2015!

--Mike

Thursday Oct 01, 2015

Some Seoul Impressions - 09/2015

A very intense week in Seoul, Korea, is over - and Roy and I both returned a bit sick ;-) The pleasures of traveling ...

But thanks to all our colleagues who did all the orga work, to everybody who traveled from near and far to our 2 day internal workshop (and I still remember many names :-) - it was great fun to spend those two days with all of you) - and of course the the customers and partners we've had the pleasure either to visit or come to our workshop. Upgrade is in your hands now - just do it

Finally some Seoul impressions - and even though it was my 5th visit to Seoul I'd realize still haven't seen a lot of the city :-( Next time ...

Roy and I ended our trip again - our usual ritual - with a visit to the Best Bar on the Planet :-) 

Seoul 1

Seoul 2

Seoul 3

Seoul 4

Seoul 5

Seoul 6

Seoul 7

Seoul 8

Seoul 9

Seoul 10

Pictures were taken in the Bongeunsa Temple, and in and around the COEX Center.

CU soon again!

--Mike

PS. Forgot to mention this one here ... looks a bit like row-organization inside ... :-)

Hana

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
.

Monday Sep 28, 2015

Upcoming Upgrade Workshops in Prague and Bucharest

Oracle Upgrade Workshops Fall 2015

I've just returned from a work intense week from Seoul, South Korea - and there are upcoming Upgrade / Migrate /Consolidate to Oracle Database 12c workshops already on my radar - and both are almost booked out completely. So if you are either based in the Prague or the Bucharest region you may quickly sign up (and show up as well) :-)
.

  • Upgrade / Migrate / Consolidate to Oracle Database 12c
    .
    Wednesday, October, 7, 2015
    Prague, Czech Republic
    @Oracle Czech
    .
    Registration Link is here
    .
    .
  • Upgrade / Migrate / Consolidate to Oracle Database 12c
    .
    Tuesday, October 13, 2015
    Bucharest, Romania
    @Oracle Romania
    .
    Registration Link is here 
    .

Hope to see you there :-)

--Mike

Thursday Sep 24, 2015

New version of the UPGRADE 12c SLIDE DECK available

Good news - finally we publish a new version of our big upgrade/migrate/consolidate slide deck:

And don't be scared - we reached now the 600 slide count ;-)

New topics included (and subject to more extension within the next weeks) are:

  • Why you seriously can't wait for Oracle Database 12.2.0.2
  • Unicode Migration with DMU
  • Migrate into the Cloud 
  • Single Tenant
  • RMAN incremental backups speeding up Full Transportable Export/Import

and many more ...

Sorry that we didn't keep track in the "Change Log" at the end of the deck. But there were simply too many changes this time.

Enjoy :-)

--Mike

Monday Sep 21, 2015

DBUA displays wrong RMAN backup for restore - Oracle 12.1.0.2

Related Blog Posts:


If you are using the Database Upgrade Assistant (DBUA) to upgrade your database to Oracle Database 12.1.0.2 be aware when you choose to potentially restore your database from a existing backup in case of an error during the upgrade.

First of all I would always stop DBUA and try the command line upgrade after fixing the issues instead of restoring the entire database. But this is a different story.

Anyhow, the most recent available backup to be displayed is most likely your newest one as the underlying query uses a MAX function - but leading to an incorrect (or unintended) result.

Ignore the fact that the screenshot is in German - the interesting part is the displayed time stamp for the most recent available backup:

DBUA Restore Backup

The customer who alerted me was wondering as his list of backups showed also backups from August and early September.

The query being used in DBUA to gather the most recent date; 

SELECT MAX (TO_CHAR (completion_time, 'DD-MON-YYYY HH24:MI:SS')) AS end_time FROM (SELECT completion_time FROM v$backup_set)

may give you this result: 31-JUL-2015 23:59:52 - even if you have newer backups taken in August and September. The TO_CHAR conversion will lead to the incorrect handling of the date as the MAX function won't deliver the most recent date but the alphabetical highest value of the conversion result. 

The query should be: 

SELECT MAX (completion_time) AS end_time
FROM (SELECT completion_time FROM v$backup_set)

to display the most recent full backup.

It will be fixed in the next release.
Credits go to Bernd Tuba from MM Warburg - thanks!!

--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:
    How do I get an idea how many threads I'm using right now?
  • A:
    First of all your OS displays how many OS threads it offers right now.
    Either check the Windows task manager:

    .
    or on Unix use "top" and press "1":

    .
    In these examples you'll see that the OS can handle a maximum of 16 (Win) or 4 (Linux) threads in parallel. That means you can't hit the thread limitation per single instance database.
    If your OS displays more than 16 threads then you may check some calculation advice given in the following blog post (and please see also the Comments section below):
    http://www.redstk.com/current-thread-count-for-your-standard-edition-oracle-databases/
    .
  • 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

Wednesday Sep 16, 2015

Script: Is your database ready for Oracle GoldenGate?

Oracle GoldenGate can be a good addition to a lot of upgrade and migration projects to decrease the downtime to a bare minimum, or even zero in some cases.

But before you consider Oracle GoldenGate as THE solution to decrease your downtime you may evaluate if your database is ready for OGG. For this purpose you can download scripts from MyOracle Support (MOS) to check exactly this.

Complete Database Profile OGG readiness check for Classic Extract 

MOS Note:1298562.1:
Oracle 
GoldenGate database Complete Database Profile check script for Oracle DB (All Schemas) Classic Extract 

This script is intended to query all of the non default database users to identify current configuration and identify any unsupported data types or types that may need special considerations for Oracle GoldenGate in an Oracle environment.

Check OGG readiness for Schema Only

MOS Note: 1296168.1
Oracle GoldenGate database Schema Profile check script for Oracle DB

This script is intended to query the database by schema to identify current configuration and identify any unsupported data types or types that may need special considerations for Oracle GoldenGate in an Oracle environment. 

Difference and Execution 

The main difference between the two scripts is the amount of data being processed. The Schema Script is more targeted and therefore should return fewer items that need additional checking.

Log into sqlplus as sysdba and run the script:

SQL> @full-DB_CheckOracle_15092015.sql

SQL> @full-schemaCheckOracle_15092015.sql

Review the output.

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

.

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
« April 2016
SunMonTueWedThuFriSat
     
1
2
3
4
5
6
8
9
10
11
12
13
14
15
16
17
23
24
25
29
30
       
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