Usn’s IT Blog

This is my example schema, basis for the DB tuning lectures. Please use your “save as” function or copy the URI to use wget.

expdp_klm1.dmp.bz2
expdp_klm2.dmp.bz2

Regards
Martin Klier

As a follow-up of my last post, I learned that creating traces is much simpler in 11g than I expected it to be. Dion Cho and Tanel Poder pointed me to some interesting links

http://oraclue.com/2009/03/24/oracle-event-sql_trace-in-11g/

http://tech.e2sn.com/oracle/troubleshooting/oradebug-doc

describing how to set up a trace event on one SQL_ID without need to do it on session level (“alter system” possible). A quick copy&paste of an example to demonstrate the value:
alter system set events ‘sql_trace [sql:b6z8h59a39gv9]‘;

Great? Great. All connection-pool plagued DBAs will like it I guess.
Have a good trace
Martin Klier

Once again, there was a lesson to be learned about the Oracle RDBMS. The occaison was a direct upgrade from Enterprise Edition 10gR2 to EE 11gR2, the application was kept untouched for good reason.

Problem
After running the new version for two hours, everything became incredibly slow, and the CPU load on the DB server was hitting 100%. Research in the Oracle Wait Interface quickly showed me the wait event: “Cursor: mutex S”. The mutex S is a serialization mutex for the cursor cache. It is involved, as soon as two sessions try to (hard/soft) parse the same statement in sense of SQL_IDs.

A quick look into the cursor cache with v$sql immediately showed an UPDATE statement that has more than 1000 child cursors for the same SQL_ID, it was very likely to have found the culprit, because creating new child cursors means expensive hard parsing, and searching the list of existing children means CPU load as well.

During the next hours, it turned out that on 11gR2 for this machine and constellation off peak hours (thus with low DB load), the critical mass was around 1500 cursors. Beyond this point, the server became slow as described above: The mutex S ate up all CPU power available. In peak time, with lots of DB activity and high basic load, the critical number of child cursors for this UPDATE was around 300, so about 1/5 of “peacetime”. With a growth of 200 new child cursors per minute, expect no fun!

So what can we do about it?
Read more…

The oracle-l mailing list made me try a new tool, created by David Litchfield. It’s called DDLDUMP V. 0.1 and extracts DDL from redo logs or archived redo logs, the output format is XML. Since it’s a windows .exe file for now, I had to use wine to test it, but it works flawlessly, and David promised to provide Linux binaries as well.

Look here:
Read more…

After long-aking FUSE implemented ZFS on Linux, Lawrence Livermore Labs have released a reimplemented ZFS for native use with a Linux Kernel.

See details here: http://github.com/behlendorf/zfs

Still have to test it, but I’d daresay, it was time.
Good pooling
Martin

I plan to evaluate a DBMS based on columns (not rows like Oracle, SQL Server…), called Sybase IQ these days, in order to see if we can avoid some aggregation in DSS environments. It’s a nice little niche product, quite handy, but due to its niche status, you don’t find a lot about it in the www. I want to try to change this a little, little bit today.

Installing Sybase IQ 15.1 is not that complicated. By following the installation guide from sybase.com, it took me about an hour to figure out what’s to do, and another to get the box up and running. Creating and starting a demo database is described in the same paper, and worked flawlessly.

But now my problem did rise: My target is to do mass data tests within the DB, so I need mass data storage access, preferably on a raw device, bypassing the file system caches of my Linux/ext3 system. Since using persistent device names and directly accessed (“raw”) devices from /dev/disk/by-id works greatly with my Oracle systems, I headed for this. My final attempt, after looking up some SQL syntax, was:

CREATE DBSPACE KLMTEST USING FILE DF1 '/dev/disk/by-id/scsi-1HITACHI_730109670008' IQ STORE;

But just forget it:

Could not execute statement.
The file '/dev/disk/by-id/scsi-1HITACHI_730109670008' already exists.
-- (st_database.cxx 2215)
SQLCODE=-1010000, ODBC 3 State="HY000"
Line 1, column 1

So what, OF COURSE the device exists…
Read more…

srvctl in Oracle Grid Infrastructure 11g Release 2 has at least one discrepancy between docs and functionality.

I saw EONS consuming lots of memory in a non-database grid installation, and wanted to get rid of it. (The eONS is used by Oracle Enterprise Manager to receive notification of change in status of components managed by Oracle Restart.) Documentation tells us, it would work:
See the docs for removing eons with “srvctl remove eons”.

But if you try in reality:

[oracle@ASM01 ~]$ LANG=C srvctl remove eons -f -v
Usage: srvctl <command> <object> [<options>]
commands: enable|disable|start|stop| <...>
objects: database|instance|service| <...>
For detailed help on each command and object and its options use:
srvctl <command> -h or
srvctl <command> <object> -h
PRKO-2013 : eons object is not supported in Oracle Clusterware
[oracle@ASM01 ~]$

Repeat: PRKO-2013 : eons object is not supported in Oracle Clusterware
Nice.

Read more…

Many topics are to be found on the web about IMPDP and UNDO_RETENTION. This one will become a more theoretical one, around the central question: Why, please why does a huge IMPDP fail with ORA-1555 if there is too less UNDO_RETENTION configured?

Like so often, these questions come from a problem. I had to impdp about 30GB from one machine to another via database link. (For newcomers to this technology, this is done via impdp on the target machine, no expdp needed, no dumpfile is created.) The impdp failed with ugly ORA-1555s after about 2 1/2 hours. It happened after the import itself, but during the index creation. The web said in several places, that increasing the UNDO_RETENTION period will help, but I have not been able to explain it. For my understanding, UNDO_RETENTION was something to enable flashback-table-like operations over a longer period in a busy database.

Oracle Support
So I opened a Service Request in order to get some clarification. The result was:

QUESTION
——–
Why does the undo_retention affect the impdp behaviour?

Read more…

If you plan to install Oracle products, be prepared for surprise of random kind. :) The new Grid Infrastructure is no exception of this rule: I tried to install it on a SuSE Linux Enterprise Server (SLES) 10.

Quick facts about the setup:

• SLES 10 SP 2 64bit
• Oracle Grid Infrastructure 11.2.0.1 x86_64
• all devices are specified via UDEV symlinks like /dev/disk/by-id/scsi-1HITACHI_732342670003

Problem description

Nearly everything went well, but $GRID_HOME/root.sh failed this way:

ORA-27091: unable to queue I/O
ORA-15081: failed to submit an I/O operation to a disk
ORA-06512: at line 4
PROT-1: Failed to initialize ocrconfig

(By the way, see how to repeat root.sh here.)

This just tastes like catastrophe: Why does a full-fledged SAN-provided LUN has I/O errors? The cross-check with “dd” cooled me down immediately, because it did not show any problems, reading and writing provided full power, no stalls and no errors. So calmly looking up My Oracle Support (fka Metalink) brought me to Article ID 955550.1 that’s talking about ASMLIB. I did not use ASMLIB because I don’t like it, but they solved their problem by an owner change of the device used for OCR. The trouble described there is, that the Oracle Clusterware does not accept OS user “oracle” just as a member in unix group “disk” that has rw permissions on the device, no, user “oracle” has to be the owner of the device!

Read more…

To clean up a tilted installation of Oracle Grid Infrastructure isn’t that intuitive. I had to do it today, and this is my collection what to do and what to read. It’s made for repeating root.sh, that’s not allowed to run twice or more times without cleaning up in between.

So first have a look at Coskan’s page, which contains all hints I did need. By the way, he had the same problem that made me trying this:

ORA-27091: unable to queue I/O
ORA-15081: failed to submit an I/O operation to a disk
ORA-06512: at line 4
PROT-1: Failed to initialize ocrconfig

Furthermore, read in the official docs about the correct cleanup procedure. The keyword is: Deconfiguring Oracle Clusterware Without Removing Binaries

Last but not least, metalink notes 942166.1 and 955550.1 (for Linux) might be enlighting.

Summary, for the folks in a hurry:

# $GRID_HOME/root.sh
... fails ...
# $GRID_HOME/crs/install/rootcrs.pl -verbose -deconfig -force [-lastnode]
... fix your problem ...
# $GRID_HOME/root.sh
... hopefully - works!

Be careful
Martin Klier