Upgrading Oracle Grid Infrastructure 11.2 (it’s just another marketing name for the Oracle Clusterware, formerly known as Cluster Ready Services CRS) usually is easy: The runInstaller offers an upgrade mode, and discovers nodes and versions without further effort. But what, if not? The message will be “[INS-40406] The installer detects no existing Oracle Grid Infrastructure […]
Category: Oracle
What’s the difference between Larry Ellison and god?
Speaking at IOUG COLLABORATE 12: “Resolving child cursor issues resulting in mutex waits”
On April 22-26, 2012 the Independent Oracle Users Group (IOUG) is holding the COLLABORATE 12 forum in Mandalay Bay Convention Center, Las Vegas (US-NV). The COLLABORATE is a widely known event in the Oracle Community, and attracts lots of Oracle guys and girls from all over the world. I feel honored to contribute a presentation […]
DOAG Conference 2011 – Impressions and Look-at’s
Once again this year, the German Oracle Users Group has its annual conference and exhibition in Nuremberg. (DOAG Konferenz und Ausstellung 2011, Nürnberg). Being there is nearly a must for Oracle guys in German speaking countries. As usual, here comes my unordered, incomplete and ad-hoc list of things I wrote down to have a closer […]
Oracle Clusterware 11.2: ASM crashes at startup
These days, a customer’s Oracle Clusterware (2 nodes) crashed one ASM instance at every startup. More Facts: It was not possible to start it manually, too. The CSSD was running. For obvious reasons, CRSD did not start. The other ASM instance in the cluster recognized CLUSTER RECONFIGURATION for a short period of time. The ASM […]
Oracle: Manage the lifetime of trace files et al. with ADRCI (purge interval)
Oracle Database 11g and above offers a great feature by managing its trace files by itself. In combination with the new “incident” system, a tool was necessary to handle those without enterprise manager: ADRCI. It has several other cool functions, such as a tail-like log file display, but today I just want to show a […]
Oracle Grid Infrastructure: How to recover from a messed up ASM/CSSD diskstring
Oracle Grid Infrastructure 11.2 with voting files and OCR in an ASM diskgroup can be a little tricky if you mess up the voting file voodoo. You know the basic situation? With Oracle Grid Infrastructure aka Oracle Clusterware, we are storing your cluster quorum and config repository (OCR) into a disk group. But CSSD needs […]
Oracle ASM / CloudFS licensing policy
Sad to see, at least from June 1st 2011 the new Grid Infrastructure ASM features “ACFS” (ASM cluster file system) and “ADVM” (ASM dynamic volume manager) are now licensed as “Cluster FIle System” (ClusterFS). As far as I can see from the Technology Global Price List, it’s US$ 5000 per CPU, US$ 1100 perpetual. Alternatively, […]
Oracle Performance Firefighting with Craig Shallahamer (Frankfurt, April 2011)
In early April, I had the honour to attend Craig Shallahamer’s Oracle Performance Firefighting and Advanced Performance Analysis courses. It was a great opportunity, since he came to Frankfurt which is just a three hours drive away. It was the second time meeting him – and it was great, again. The last two days have […]
Terrifying Execution Plan with cost of 18E (exa)
The newest piece of my bloomer collection is a query with an execution plan that has 18E of cost. I am no believer in absolute cost numbers, but this one is just nice. This exa-executionplan is the new definition of Exadata…. Keep your fingers off 😉 Usn […]
DOAG Konferenz 2010 – Look at’s
For the last three days, I have been in Nuremberg, attending the DOAG Konferenz 2010 (annual conference of the german Oracle user group). This year, my list of “Look at’s” is way shorter than last years list was. It’s not the fault of the event, I just did not write down that many facts. My […]
Oracle Database patch set 11.2.0.2 is out
For all who may concern 😉 Oracle 11.2.0.2 is out for Linux x86 and Linux x86_64 It’s patch set 10098816! Packaging: Starting with the first patch set for Oracle Database 11g Release 2 (11.2.0.2), Oracle Database patch sets are full installations of the Oracle Database software. In past releases, Oracle Database patch sets consisted of […]
Oracle ASM quote of the week
Please, do yourself a favour and NEVER EVER use ASM on Windows! “Dear Sir, obviously I forgot to put a file system on drives E:, F: and G: of the DB machine. I did this now, and hope you excuse the delay. Best regards John Doe Windows Server Administrator” /* no comment */ Martin […]
Oracle: Example schema for my DB lectures
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 […]
Oracle 11g trace particular SQL_ID
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 […]
Oracle 11.2: Cursor Mutex S wait event and too many (2^30) child cursors
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 […]
Extracting Oracle DDL from online redo logs or archived redo logs with DDLDUMP
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 […]
Oracle srvctl does not remove EONS despite it’s documented (PRKO-2013)
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 IMPDP, ORA-1555 and UNDO_RETENTION
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 […]
Oracle Grid Infrastructure: UDEV fixing ORA-15081: failed to submit an I/O operation to a disk
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 […]
Oracle Grid Infrastructure 11gR2: How to clean up to repeat root.sh
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 […]
Lecture about Database Technology Basics at Augustinus Gymnasium Weiden
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, […]
Oracle: How to create a good execution plan
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 […]
Oracle 11g Adaptive Cursor Sharing does not work with PL/SQL?
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 […]
Talk about Database Technology Basics at Berufsschule Wiesau
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 […]
Oracle Technology Day Storage Optimisation – Impressions
In January, I’ve bee rather busy, so the blog was left alone. No sense in blogging just to blog. Now some content. I attended the Oracle Technology Day – Storage Optimisation in Bonn, Germany. It was a nice event, lots of information, a bit commercials, excellent catering. But this is a technical blog, let’s talk […]
Oracle 11g: Password expired, account locked – unwanted!
Hi, it’s old news, but oracle 11g expires passwords after 180 days, and locks you out if you mistype your password 10 times, and most DBAs don’t like that. It’s unsecure, but for client-server-applications a locked account is no single-user-annoyance, it’s a downtime killing SLAs, nerves and – hopefully not – DBA jobs. So just […]
DOAG Konferenz 2009: Impressions Day 3 (final)
Thursday 19th was the last day of DOAG Confernce 2009. Compared with the second day, the program was much more dense: For my (DBA) profile there have been several hours, where more that one talk would have been appropriate. This list is, what I collected during the day. […]
DOAG Konferenz 2009: Impressions Day 2
As promised, today my impressions of yesterday (confusing, I know). Clarification: This is about Wednesday, November 18th. I’m still at DOAG Conference 2009 in Nuremberg. Wednesday was a silent day for my interests, especially at noon there have been less talks being of interest, but maybe they have done something for my general knowledge… So […]
DOAG Konferenz 2009: Impressions Day 1
These days, the DOAG Conference 2009 happens in Nuremberg, Germany. First day was yesterday, Tuesday November 11th 2009. This is a machine-readable copy of my scratchpad, coming from the different talks I attended. Sun Oracle Database Machine Exadata 2: The “Quarter Rack” (consisting of 2 Servers and 3 storage nodes) sounds interesting. 11gR2 has some […]
New My Oracle Support (ex. Metalink): Use without flash: HTML Option
In the last days, My Oracle Support was redesigned. But since flash is a performance eater and not always available, people like to have an option to use My Oracle Support (formerly Metalink) with HTML only. The link to it is well-hidden, but nevertheless useful! Give it a try: https://supporthtml.oracle.com Best regards Usn […]
Oracle: Recreate PLAN_TABLE
drop table plan_table; @?/rdbms/admin/utlxplan.sql […]
AIX: Avoiding “ORA-27126: unable to lock shared memory segment in core”
Did you ever change lock_sga = true for an Oracle Instance on AIX 5.3, and did see ORA-27126: unable to lock shared memory segment in core IBM AIX RISC System/6000 Error: 1: Not owner in Return? Do the following: # vmo -o v_pinshm v_pinshm = 0 # vmo -o v_pinshm=1 Setting v_pinshm to 1 # […]
Oracle 11gR2 ASM / ACFS: A first benchmark (poorly)
Hi folks, since Oracle 11g Release 2 is out now, I had to test one of the most-missed ASM features: the ASM cluster file system ACFS. My Setup: Two VMware nodes with 2 CPUs and 1,5GB of RAM each Oracle Enterprise Linux 5.3 x86_64 Four virtual cluster disks from the ESX server, 10GB in size […]
Oracle 11gR2 ASM: Changed permission policy (ORA-15260)
Did you recently see the new error ‘ORA-15260: permission denied on ASM disk group’ in your ASM administration? Maybe you are still connected as SYSDBA, old habit from 10g? A quick citation from the Oracle Docs: The SYSOPER privilege permits the following subset of the ALTER DISKGROUP operations: diskgroup_availability, rebalance_diskgroup_clause, check_diskgroup_clause (without the REPAIR option). […]
Oracle 11g Release 2 documentation
The long-awaited 11gR2 is out, at least for Linux x86 and x86_64 now. Marketing will show you all the features, but the most important link is to the documentations: http://www.oracle.com/pls/db112/homepage Use it well! Usn […]
Oracle: Executing an OS command from PL/SQL – ICMP ping from database
Executing OS commands from SQL or PL/SQL is easy as long as you are in SQL*PLUS – just do a host command. But I must confess, that this is a really useless hint. 🙂 Much more interesting is to execute an OS command with a DBMS_SCHEDULER job, but this is not possible in every case, […]
Oracle: Tracing of another session
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 […]
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 […]
“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 […]