I simply love 10046 level 12 traces. I described session tracing based on a logon trigger quite earlier, but some situations in real DBA life need this trace event switched on for a session other than my own AND for a short peroid in time (with no need for the connected user to log out). […]
Oracle: Audit a failed logon attempt without auditing
Oracle has a disadvantage: It allows no trigger BEFORE LOGON ON DTATBASE! 🙂 For obvious reasons, this would be nonsense, but there’s a need for it! Auditing failed logon attempts, for example. Of course, there’s Oracle Auditing. But IMO, setting up an audit trail for one Email in one case looked like the overkill to […]
String concat with hierarchical query and sys_connect_by_path(): Avoid ORA-30004
In the last days, I touched hierarchical queries – it’s a strange field for a DBA, but I did need it for DBA desires: A sophisticated query on statspack. Why? Since oddly, the stats$sql_text table of statspack / perfstat DOES contain the full SQL for a statement, but fragmented over various lines. But I had […]
Oracle IMPDP: Wildcard hacking
Sometimes you want to use datapump import (impdp) for smart problems. An example is excluding (or including) a subset of objects, like tables. The usual way to do so is the EXCLUDE keyword. (Footnote: All examples in this post are written for a parameter file, so don’t forget a proper quoting for your shell if […]
Oracle: Query/Monitoring autoextend of datafiles
Did you ever wonder how to see whether your datafile is on autoextend or not, or monitor the autoextension to keep eyeballs on target? The Oracle EM is a simple tool for that, but some people do prefer the direct query. See a little example to query or autoextend a datafile. select * from dba_data_files […]
Oracle: Change (default) TEMP and UNDO tablespace to bigfile tablespaces
That’s how to change the Oracle Database’s TEMP and UNDO tablespaces to bigfile (as possible in 10g and above). It’s something I usually do for every DB I install. create bigfile temporary tablespace TEMP1 tempfile \ size 10G autoextend on next 1G maxsize 20G; create bigfile undo tablespace UNDO1 datafile \ size 10G autoextend on […]
Oracle 11g look-at’s
That’s a personal, quite unsorted list of (new or older) features I recently collected. All of them are things, I’d consider valuable or at least important to care about as soon as 11g is involved. It might be for system architecture knowledge, concept tasks, DBA hands-on, good-to-know or any other thing that my happen in […]
Oracle: Convert a partitioned table into an unpartitioned one
License fees can bee massive. So for some machines it’s just useless to buy Oracle Enterprise Edition, sometimes Standard Edition is simply enough. Migration to SE for a schema with partitioned tables leaves you one option: CTAS all partitioned tables and export them, reimport them on your new box and modify all DBMS_METADATA-generated DDLs so […]
Talk: IT Performance
On Thuesday, March 24th, 2009 at 9 a.m. I will speak at Berufsschule Wiesau about general matters of IT performance. Papers and the presentation are available online now: Handout “IT-performance” Slides Regards Martin Klier EDIT: That’s a nice article about the event on the school’s webpage. Thanks! […]
Oracle: Specify the number of occurrences before metric alerting starts
It’s not possible to specify a minimum of consecutive occurrences before alerting starts in in Enterprise Manager, it will always scream out at first time touching the line. But you can do so in command line. Just execute DBMS_SERVER_ALERT.SET_THRESHOLD with parameter consecutive_occurrences set to a value >1. It’s a very nice way to stop annoying […]
Oracle Explain Plans or Execution Plans: Guess or Reality?
Oracle finally desupported the rule-based optimizer with version 10g. Since 9i, users are good adviced to make themself familiar with the cost-based-optimizer (CBO). In order to understand what happens with your statement after parsing with CBO involved, and to be able to tune your queries, displaying, understanding and optimizing explain- or execution plans is essential. […]
Get Oracle User DDL with dbms_metadata
Sometimes you have to recreate a user in a DB, and you like it to be the same as before again. To have its DDL at hand is usually rather convenient. DBMS_METADATA is a great toolbox to get it, my example is just a short but hopefully useful excerpt of its possibilities: set long 200000 […]
Oracle 11g JDBC driver hangs blocked by /dev/random – entropy pool empty
On a headless (=without console) network server, the 11g JDBC driver used for (java) application connect may cause trouble. In my case, it refused to connect to the DB without any error, trace or log entry. It simply hung. After several hours, it connected one time, and freezed again. Remote debugging done by the development […]
Creating Oracle AWR reports quicksheet
For comparing, classifying, benchmarking and forecasting databases, Oracle’s Automatic Workload Repository (AWR) is neat. (But remember! You will need a diagnostics pack license for that as described here and here – if you need a free technology for performance monitoring, consider using Craig Shallahamer’s OSM toolkit.) This post simply describes the package calls to create […]
Setting ulimit -m in AIX
AIX has its own rules, as I have to discover these days. Coming across ulimit, I found out that smitty configures value A here and value B completely elsewhere. Hardest one so far has been “ulimit -m” (“Specifies the size of physical memory, in number of K bytes.” a user can assign), since I could […]
“Basics of Database Technology” again
Tomorrow, there will be a version 2.0 of a lecture from last year, again for 9th-formers of Augustinus Weiden. The handout and the presentation are online now, we will see what Friday brings. Usn […]
New job in 2009 – looking back at A.T.U
After working at A.T.U Auto-Teile-Unger headquarters for over six years, there’s a new challenge calling on January 1st, 2009, and hopefully, it’s for a long time again. I guess it’s a good time to look back: […]
Single Sign On for Apache 2.2 and Active Directory 2003 R2 with SuSE 10.2, mod_auth_kerb and MIT Kerberos
Hi, this is a small howto out of my needs, suggestions are always welcome! Assumptions for this paper You know and basically follow http://www.grolmsnet.de/kerbtut, but there are some facts missing/wrong. 2003R2 KDC’s are 10.0.0.1 and 10.0.0.2 (DNS: dc1.example.com and dc2.example.com) Kerberos Realm for ADS is ADS.EXAMPLE.COM (upper case required!) Web server (hostname.domain) is webhostname.server.example.com DNS […]
Oracle: Remove scheduler jobs in a loop
If there are too much scheduler jobs in an Oracle database, the CJQ process may die unexpectedly. This has nothing to do with job history, not, it’s the number of jobs known to the system. In my experience, the critical number is somewhere around 32.000 in 10.2 64bit. By the way, that’s the solution for […]
Internationalization fever
“Because God got angry at some point in time, computer programmers are forced to deal with localisation and internationalisation.” (From a typo3 tutorial.) That’s a cool sentence, and the point I laughed about it is gone long time ago. The last days I spent with i18n for my “Usn’s web site (http://www.usn-online.de)“. (I don’t consider […]
This site is not tested with Internet Explorer(c) :)
Have you heard of the “Not tested in IE initiative“? If your site matches this criteria, use one of these images there. Regards Usn […]
Oracle: How to stop a (MTS) dispatcher process – mad behaviour of ALTER SYSTEM
Manually stopping a dispatcher process is one of the things I have to look up each time, since it’s needed not that often. And, the word “dispatcher” does not appear in the command line! 🙂 alter system shutdown immediate ‘D000’; where “D000” is the dispatcher process name selected from v$dispatchers or gv$dispatchers. (In the latter […]
Switch workspace on command line
Sometimes, it’s nice to change the current windowmanager workspace on command line, for example if you desire to start a application exactly HERE or THERE. For KDE you may want to do it with “kstart”. But a tool like kstart might not be available for your windowmanager, so have a look at “wmctrl” as well: […]
Oracle 10g Data warehouse ORION benchmark, size 20TB, 1200MB per second
During the last days, I have had the opportunity to test and benchmark a data warehousing hardware, that’s really fast for its money. It’s not suitable for real/available production, since it depends on disk striping over a bunch of components, but I considered it as a good way to push the limits a bit. Result: […]
Attended “Reactive Performance Management” with Craig Shallahamer
Monday and today (Tuesday) I attended a 2-days Oracle University class with Craig Shallahamer from OraPub, taking place in Munich. Honestly, I have had no expectations at all, so there has been nothing to be crestfallen or to be fulfilled. I just was looking forward to the one or other hint how to find the […]
memlock config for Debian Lenny
Hi, today I tried to start Oracle XE with parameters “pre_paged_sga=true” and “lock_sga=true” on my Debian Lenny toybox. But Lenny has had a rather strict and really sticky value for the user’s maximum amount of pinned memory (ulimit -l) value: 32 (kb). First attempt, change /etc/security/limits.conf: oracle – memlock 1073741824 Result: Nothing, “ulimit -l” as […]
Oracle DML Parallel Execution Don’ts
Have you ever waited on a parallelized statement? Parallel execution is said to be fast, efficient, system-exhausting. Far from that! There’s a 10.2.0.4 x86_64 system with 16 cores and over 600MB/s write-IO ability, one statement running, one CPU burning, one developer waiting. For days. Finally, at the end of the week, the admin is involved. […]
“16 penguins in a row” and a Linux kernel compilation contest (2min 33sec)
A nice view: What’s the story behind? […]
Nerd compliments.
If anybody ever considers joining irc.freenode.net #oracle (what I’d recommend for all Oracle interested folks), be prepared for being treated like that: <nayyares> it worked !!! <usn> wait some time, it might crash, or restart all cluster services to see if there is a boot bug <nayyares> ok, i am monitoring usn <Rudemeister> Im also […]
Sun ZFS – cool stuff
Basically, I don’t like mirroring google. But this one is really cool stuff, and, by the way, it’s merely linking google. ZFS-Video by Systemhelden Yeah, I really like it! But the video has a weak point: After randomizing the devices this way, you can’t simply pull the plug of one USB hub any more. Anyway, […]
New blood in IT sector (partially German)
Doing chat support in several IRC channels, I’m hardened in several ways. Especially newbie questions are not always easy to handle for all participants. Let me give you an example (in German), how things sometimes are seen by the youngsters. […]
TEMP tablespace too small
Today I got a mail from my grid control: “Tablespace TEMP1 is 97% full”, acting for one of the single instance servers. No problem, you may think, this might happen. It’s an OLAP database, so I wasn’t afraid of big numbers as well. But as you can see, using all this huge temp tablespace for […]
Cleanly removing an agent or host target from Oracle Grid Control 10.2.0.4
Removing a host/agent in Oracle’s Grid Control can be unnerving: There are enough dependencies between the agent, the (cluster-)database, the listener, the host and so to make your deleting action an endless sequence if you are following them. But GC really becomes stubborn if the agent we are about to remove is unavailable: due to […]
Blocking network loopback slows down firefox extremely
My newly installed Firefox web browser (versions 2 and 3, does not matter) was deadly slow. I don’t mean the display of web sites, no, I am talking about pulldown-menus or opening tabs. (For example, bookmarks folder opens within 6 long seconds.) Several hours I was not able to tell why it acts that way. […]
Grid Control startup fails, installUser=%s_userName%
Oracle seems to have an uncommon sense of humor. Executing gcstartup after patching the grid control server to 10.2.0.4 (Linux x86) fails with: /bin/su: user %s_userName% does not exist I am not surprised, since this is windows’ environment variable notation, and unknown to my Linux shells. Solution: In the head of /opt/oracle/grid/oms10g/install/unix/scripts/omsstup replace installUser=%s_userName% with the username […]
Discovered openstreetmap for me
Hi, http://www.openstreetmap.org is a very cool project. Just discovered it for me, and spent the weekend with tracking and editing my hometown by car. Have a look here to see. I am using SportsTracker for my Nokia E90 and JOSM for the editing at home. Also checked for TrekBuddy on the phone to use a […]
Oracle DB: Automatic tracing for all sessions of a user
Hi, SQL tracing is nice to find out details about several issues. But there is no out-of-the-box solution to enable tracing for a user, only for session- or system-wide tracing. But there’s a trick to close this gap. It’s old, but stil useful: Use a logon trigger to enable tracing for all sessions of a […]
Oracle expdp utility i18n – strange
Export> kill_job Möchten Sie diesen Job wirklich stoppen ([yes]/no): yes Möchten Sie diesen Job wirklich stoppen ([yes]/no): yes Möchten Sie diesen Job wirklich stoppen ([yes]/no): yes Möchten Sie diesen Job wirklich stoppen ([yes]/no): yes Möchten Sie diesen Job wirklich stoppen ([yes]/no): yes Möchten Sie diesen Job wirklich stoppen ([yes]/no): YES Möchten Sie diesen Job wirklich […]
Counterize II does not count any more
Hi, Counterize II is a beautiful tool for gathering WordPress statistics. But after updating to WP 2.5 it did simply not count any more. No action taken – including an upgrade to the most recent counterizeii version – did help, and after upgrading WP to 2.5.1 today the situation did not change. Now I asked […]
World’s fastest SATA drive: WD VelociRaptor WD3000GLFS
Hi, imagine a 2,5″ SATAII disk in a 3,5″ heat sink. It’s 300 GB in size, 120MB/s buffer-to-disk rate, 16 MB Cache, 10.000 RPM, consuming 6 Watts under load. Look here. I think this baby should take up to 150 IO/s, in combination with RAID 0+1 and a decent RAID controller or/and (Oracle’s) ASM this […]
Effectice load balancing for a (web?) service in RAC 10gR2
Hi, had to review service creation with DBMS_SERVICE package for a good-feeling load balancing in Oracle RAC 10gR2 several times – now I have to write it down for myself. 🙂 I needed it for a web service using shared server today, but the generic syntax is useful for all other purposes as well. Basic […]
The early bird …
The early bird may catch the worm. But the second mouse gets the cheese! […]
Computer addicted? Parent’s training with Jörg Kabierske
Today (huh, it’s still “today”) I attended a so-called parent’s training (in German “Elterntraining”) at a local grammar school (Kepler Gymnasium Weiden). It’s topic was the handling of youths in contact with computers, internet and addiction risks. The lecturer was Jörg Kabierske, Regensburg (klicksalat.de). I was there just out of curiosity, to see what kind […]
ORA-00600 [kgeade_is_0]
Have a look at this nice one (alert log), seen in 10gR2 (10.2.0.4 64bit on Linux): ORA-00600: Interner Fehlercode, Argumente: [kgeade_is_0], [], [], [], [], [], [], [] And from the trace file: Current SQL statement for this session: select tablespace_id, rfno, allocated_space, file_size, file_maxsize, changescn_base, changescn_wrap, flag from GV$FILESPACE_USAGE where inst_id != :inst and […]
New theme
Hi, decided to look for a new theme. After testing GenkiTheme, Mimetastic and Munch, I ended up with TechnoPlain 1.1 Hope it looks okay for you. Regards Usn […]
Disabling (or enabling) general job execution in Oracle 10g
Hi, disabling and enabling job execution in Oracle DB has two ways: If you are using dbms_jobs, it’s handy to set job_queue_processes to zero to disable the execution of jobs, and set it to a value >0 (maybe 100) to enable. If you are using dbms_scheduler, this parameter does not work for you. You will […]
Find out your character set in Oracle DB
Hi, since ‘show parameter’ does not tell you your database charset, use this statement to see how your characters are stored: select * from nls_database_parameters where parameter=’NLS_CHARACTERSET’; Regards Usn […]
Talk “Hope is not a strategy” at Vocational School Wiesau
Hi, there will be a talk about IT High Availability for rising IT professionals at Vocational IT School Wiesau on Monday, 10th of March 2008. The topic: “Hope is not a startegy” – IT high availability in theory and practical experience See paper and slides here. Regards Martin EDIT: There has been a nice press […]
The Speed-up loop
Hi, this is a good one. Old, but really good: The Speed-up Loop Regards Martin […]
Oracle Database with SGA of or larger than 16GB
Hi, using large SGA’s requires 64bit Oracle RDBMS, of course. But furthermore, some OS settings are to be done. SUSE walks it’s own lane, quite as usual, so I will provide two ways for doing without ORA-27102. […]