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.
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?
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:
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!
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
This year, I will talk again about basics of DB technology for 9th grade of AGW (Augustinus Gymnasium Weiden, a secondary school) again. The same event has been a success in the last years, and it’s simply nice there.
Nice? Please allow me a word about this school. They have an engaged teacher, Klaus Märker, who does a lot more of IT teaching than he would have to. His voluntary IT classes are simply looking great and promising. It’s a big opportunity for the kids, to learn some IT specials during their school time. I hope, you know and value what he does for you.
It was self-evident for me to support his approach on showing the young ones more than ministry officials creating an official curriculum can imagine. Database technology is some kind of “poor cousin” in IT schooling. I see that every day. I am not under the imagination that the girls and boys there will become database pros from one lecture, may it be good or bad. But they have a chance to see more than their history-, latin- or maths books. And be honest, at least, if you don’t use a database by yourself, your name is already inside a pretty good one.
Ok, enough of this monologue, here comes the paper.
Hopefully, it will be fun again,
Martin
Sometimes you simple need to know whether a statement is fast or not, or why it is slow. (Sadly, it seems like virtually nobody wants to know why a statement is fast, but that’s another story…)
Do not use EXPLAIN PLAN in such cases, it does not (can not) deal bind variables right. I’ve got a simple script in my toolbox to create a real execution plan using bind variables, but avoid displaying all the lines the SQL returns. Just have a look, and use it if it’s suitable for your needs. Please keep in mind, that you need to set the binds the way I do, because the cost based optimizer handles bind statements entirely different (bind peeking, (adaptive) cursor sharing …)
This script was designed to run in Oracle SQL Developer very well, but it seems that version 2.1 has trouble with the LAST cursor, due to some changed output.
Read more…
Recently, there was trouble in the air. I migrated a database into 11g to enjoy adaptive cursor sharing, but it did not adapt cursors. In the trouble shooting process, I discovered the following:
Bug 8357294: ADAPTIVE CURSOR SHARING DOESN’T WORK FOR SQL FROM PL/SQL
Nice one. A short quotation from the bug text:
PL/SQL has famously enjoyed the so-called “soft-parse avoidance” optimization
for embedded SQL ever since its inception. Then, in 10.1, the benefit was
brough to native dynamic SQL, but only for “execute immediate”. Notice that
queries executed using a ref cursor (whether this is opened statically or
dynamically) do not enjoy the “soft-parse avoidance” optimization.It seems that this was overlooked when adaptive cursor sharing was
introduced.
Summary: 11g PL/SQL does adaptive cursor sharing when we are using EXECUTE IMMEDIATE, but does not if you are using embedded SQL (for example in a(n implicit) cursor, like a for loop).
The bug says, that setting SESSION_CACHED_CURSORS=0 fixes this problem, but I will have to test it by myself to classify this information.
Regards
Usn
Once again, I will talk about an IT topic at Vocational School (Berufsschule) Wiesau. This year, it’s Basics of Database Technology (Grundlagen der Datenbanktechnik) for 11th and 12th-graders, who will become certified IT specialists for software development soon. Their certification is called “Fachinformatiker der Fachrichtung Anwendungsentwicklung” in German.
The paper for the talk is available now: DB-Grundlagen.pdf
EDIT:
See the kind report about the day as well.
Best regards
Martin Klier