Thursday Apr 17, 2014

Avoid Poor Performance and Wrong Results in 11.2.0.4

One of the best notes in MOS got refreshed last night for Oracle Database 11.2.0.4. It has not only information about performance bugs but also about the worst and meanest category of issues, wrong query result bugs. Those are the ones I call the "sleeping beauties" as you as a DBA usually won't recognize them. It is recognized at the application level when the result of a report is incorrect or instead of 4 rows just 3 rows get delivered in the output. Therefore you should be highly aware of those and add this note to your bookmarks.

MOS Note 1645862.1Things to Consider Before Upgrading to 11.2.0.4 to Avoid Poor Performance or Wrong Results

And in case you don't want to move to Oracle 11.2.0.4 but searching for information and known issues in Oracle 11.2.0.3 or Oracle 11.2.0.2 then please find the corresponding documents:

  • MOS Note: 1320966.1 Things to Consider Before Upgrading to 11.2.0.2.x to Avoid Poor Performance or Wrong Results
  • MOS Note: 1392633.1 Things to Consider Before Upgrading to 11.2.0.3 to Avoid Poor Performance or Wrong Results
-Mike

Friday Dec 21, 2012

Creating ASM for test purposes in the file system

First of all, I'm back after pausing for a while - sorry for not updating the blog in the past weeks ... and you won't see many updates in the following weeks as it'll be holiday season (and we Germans have sooooo many public holidays) :-)

Anyway, back to tech topics. Today I want to test Oracle Restart upgrades. Oracle Restart internally is called SIHA (Single Instance High Availability) which explains the topic a bit more. Basically it means having your database reside in ASM and let Oracle Clusterware take care on it, even though you don't have a cluster. Not a bad idea as this can be very helpful in real world environments. But I did realize that the entire process is not documented in all details. So I'd thought I should give this a try.

The first challenge I do face: I have just one disk in my machine - so I'll have to tweak ASM a bit to make it work with files on the file system.

Creating two empty strawman files in file system with dd is not a big deal:
$ dd if=/dev/zero of=/oradata/ASM/dg_DATA bs=8192 count=1000000 oflag=direct
1000000+0 records in
1000000+0 records out
8192000000 bytes (8.2 GB) copied, 336.371 seconds, 24.4 MB/s
[V112] oracle@localhost:/oradata
$ dd if=/dev/zero of=/oradata/ASM/dg_BCK bs=8192 count=500000 oflag=direct
500000+0 records in
500000+0 records out
4096000000 bytes (4.1 GB) copied, 246.021 seconds, 16.6 MB/s

But the next step is to start the cssd (Cluster Synchronization Services Demon) in my Oracle Database 10.2.0.5 installation from within the $ORACLE_HOME/bin directory:
[root@localhost bin]# . localconfig add
/etc/oracle does not exist. Creating it now.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configuration for local CSS has been initialized
Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
        localhost
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)

Otherwise no chance for ASM to start up.

Now my attempts to use simply DBCA (Database Configuration Assistant) to creare the ASM instance on these two strawman files did not work as the DBCA didn't want to recognize the "disks". So back to good old command line. By the way, there's a MOS Note out there which may be helpful as well (but didn't work in my case).
How To Create ASM Diskgroups using NFS/NAS Files? (Doc ID 731775.1)

  1. Create a password file for ASM instance in $ORACLE_HOME/dbs
  2. Create a fresh init.ora for ASM within the same directory having the following parameters set:
    _asm_allow_only_raw_disks='FALSE'
    asm_diskstring='/oradata/ASM/dg*'
    asm_power_limit=4
    instance_type=asm
  3. With these parameter set I could bring the instance into MOUNT state ready to create the two disk groups after setting the ORACLE_SID=+ASM in the environment:
    SYS:+ASM> create diskgroup DATA external redundancy disk '/oradata/ASM/dg_DATA';
    Diskgroup created.
    SYS:+ASM>  create diskgroup BCK  external redundancy disk '/oradata/ASM/dg_BCK';
    Diskgroup created.

Starting up ASM did work now well after shutting it down first - and a check for SELECT path from V$ASM_DISK did show me my disks.

Next step - simply - is to create a database with DBCA inside of ASM. So the first part of my test did complete.

... to be continued soon ...

Monday Nov 12, 2012

Rules Manager and Expression Filter getting removed

I doubt that many people are using the Oracle features "Rules Manager" and "Expression Filter" as usually people handle these things (such as ensuring that a zip code or a car number plate has a certain format) within the application code and not inside the database. Oracle Beehive for instance uses that just on the side. 

Anyway, just learned today that Rules Manager and Expression Filter components will get removed once our next database release most likely called Oracle Database 12c will get released. So before upgrading to Oracle Database 12c you can remove EXF and RUL components (SELECT COMP_ID FROM DBA_REGISTRY WHERE COMP_ID IN ('EXF','RUL'); ).

You'd simply do that by executing the following script before upgrade:
SQL> @?/rdbms/admin/catnoexf.sql
This will clean up Rules Manager and Expression Filter components inside the database. You could run ?/rdbms/admin/catnorul.sql before but I believe catnoexf.sql will clean up everything already.

And you'll find all this information plus guidelines for migration of existing content in MOS Note: 1233535.1 - Obsolescence Notice: Rules Manager and Expression Filter Features of Oracle Database

-M.

Friday Sep 02, 2011

Oracle Database Express Edition 11g Release 2 is now available!

Many customers have asked over the past year whether we would be producing a newer version of the Oracle Database Express Edition, because it makes such a terrific starter database for students, developers, and DBAs. Well, the answer is Yes!

As of today, Oracle Database Express Edition 11g Release 2 is available for download. As with the 10g release, this Express Edition is free of charge. To find more information about downloading the software and documentation, please visit the OTN page for Express Edition.

Remember, you can always upgrade your database to Standard or Enterprise Edition if you outgrow the resource constraints of Express Edition. So, free free to download the new release and check it out!

Enjoy!

Wednesday Aug 10, 2011

Upgrade - and an interesting surprise

Patchset 11.2.0.2 is out there for a long, looong time. But still Roy and me - and unfortunately our customers - sometimes experience some nice surprises after upgrade.

Roy did work on the weekend with a financial institution customer in the US to support them during their go-live on Oracle 11.2.0.2 with several systems. It was well tested and long planned. And overall the whole process went well except for one database (see Roy's entry below on the change with JOB_QUEUE_PROCESSES in 11.2.0.2).

Yesterday I've received a text message from Roy to have a closer look into a service request the customer did open due to massive performance problems after go-live. The customer has seen MUTEX S CONTENTION in the AWR and ADDM reports slowing down the whole cluster. And the support people from BDE did a good job to drill down and diagnose the issue. But the solution is an interesting surprise.

The April 2011 PSU 11.2.0.2.2 (Patch 11724916) did contain a fix:
Bug:10187168 Enhancement to obsolete parent cursors if VERSION_COUNT exceeds a threshold

And you might believe that having the fix included in the PSU does enable the code of this fix?? At least I would believe that. But as Roy would call it "the old 'hidden bug fix' trick..." the code is there but you'll have to enable it. MOS Note 10187168.8 describes what you'll have to do.

Actually you'll have to set this hidden parameter:
_cursor_features_enabled=1026
and besides that this nice event in your init.ora/spfile:
events= "106001 trace name context forever, level 1024"
Then bounce the instance ... an voila ... the patch is now enabled.

Scary, isn't it ...
As I've spent 6 years in Oracle's RDBMS Support as well I was first checking the patch readme to see whether this is mentioned somewhere - but the only remark pointing to this bug is:
Bug 10264680 - INCORRECT VERSION_NUMBER REPORTED AFTER PATCH FOR 10187168 APPLIED

Anyway, what is this parameter for?

It's an enhancement request to allow parent cursor to be obsoleted if the version count exceeds a defined threshold. This threshold gets set with the new underscore _cursor_obsolete_threshold.
But due to the fact that other patches included in this PSU don't know about this newly introduced underscore event 106001 has to be set instead. The value equals the threshold.

To make things more complicated and enable this fix now (see my comments above) the underscore _cursor_features_enabled has to be set as well. And its value is dependend on the patch level - for Oracle 11.2.0.2 it is 1026 whereas for Oracle 11.1.0.7 it would be 18.

And let me mention that these parameters will have to be set and adjusted just under Oracle's Support guidance!

Wednesday Mar 09, 2011

New hidden parameters in Oracle 11.2

[Read More]

Monday Jan 17, 2011

Is Oracle certified to run on VMWare?

[Read More]

Monday Nov 15, 2010

Recap on Deinstall Routines

[Read More]

Friday Apr 09, 2010

Instant Client 11.2 available for download

[Read More]

Tuesday Apr 06, 2010

Oracle Database 11g Release 2 for Windows available!

[Read More]

Thursday Mar 25, 2010

Fix invalid objects and components - BEFORE you upgrade!

[Read More]

Tuesday Mar 09, 2010

Gathering Workload Statistics

[Read More]

Monday Mar 08, 2010

Remove "old" parameters and events from your init.ora

[Read More]

Tuesday Feb 09, 2010

Dmitry's upgrade findings

[Read More]

Friday Dec 04, 2009

Optimizer patch recommendation for 11.1.0.7

[Read More]

Wednesday Aug 19, 2009

Backup your database!!!

[Read More]

Tuesday Aug 04, 2009

Patch your $OH before you upgrade

[Read More]
About

Mike Dietrich - Oracle Mike Dietrich
Senior Principal Technologist - Database Upgrade Development Group - Oracle Corporation

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite with reference customers. Acting as interlink between customers and the Upgrade Development.

Contact me either via XING or LinkedIn

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
4
5
6
9
10
12
13
15
16
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
Slides Download Center
OOW Slides Download
Visitors since 17-OCT-2011
White Paper and Docs
Oracle Blogs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers