Monday Jun 16, 2014

DBUA can ignore Underscores with "keepHiddenParams"

The Database Upgrade Assistant (DBUA) by default will remove all underscores and events before actually doing an upgrade. This is expected behaviour as we believe that underscores or events were meant to treat misbehaviour of the database just in a specific release only. And in our experience a nice collection of underscores will at least make your upgrades run slower.

Now with Oracle Database 11.2.0.4 and Oracle Database 12.1.0.1 the DBUA has a new parameter which can be used during startup:

$> dbua -keepHiddenParams

Then it will keep the hidden/underscore parameters during and after the upgrade.

-Mike 

PS: Many thanks to Mr. Frank Becker for highlighting this to myself - I wasn't aware of it - screenshot is courtesy from Mr Becker as well 

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 Oct 15, 2010

How to deinstall "old" SW after 11.2.0.2 has been applied?

I'm still learning continuously new things. On my test box I've installed 11.2.0.2 a few weeks ago in-place which is not our recommended solution - and can be quite tricky with 11.2.0.2. But I was running out of space ...

Customer have asked us for a looooooong time why we don't deliver full release but patch sets which have to be applied to a base installation. Now this all has been changed with 11.2.0.2 as it is a full release. And that's the new strategy: We force people to install into a separate $ORACLE_HOME. This is called (and recommended) an out-of-place patch upgrade. Which is the default for Grid Infrastructure as well beginning with 11.2.0.1 already.

You can still go "the old way" for the database installation but you'll have to 'detach' your current home from the OUI inventory first in order to be able to install into the existing one. Something we fully disrecommend as it will increase your downtime if anything fails.

Nevertheless the question came up at the upgrade workshop in Montreal (and you might have asked yourself as well already):
How do I deinstall the "old" software once I'm using the new 11.2.0.2 Oracle home???

 

2010_10_15_iPhone_remove.jpg
My first thought: Hey, if it's a Unix box simply 'rm -rf' the directories ;-)
Second thought: Use the OUI as it always did offer a 'REMOVE' button to remove leftovers.
Third thought: Use the magic 'X' to deinstall similar to your iPhone (ok. we are not this far yet)

 

Lesson learned now (thanks Roy!):
There's a deinstallation tool called 'deinstall' available in your $ORACLE_HOME/deinstall directory. Please find a detailed description right here in the Oracle Database Installation Guide(s) for 11.2. It will work for the database, Clusterware and ASM. But you could download it separately from OTN as well as part of the software distribution.

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]

Thursday Aug 20, 2009

Is there a FALLBACK strategy?

[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
Master Product Manager - Database Upgrade & Migrations - Oracle Corp

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

Follow me on TWITTER

Contact me via LinkedIn or XING

Search

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