OraNA :: Oracle News Aggregator

I can see this post degenerating into a rant, so I would like to preemptively appologize to anyone involved in the production of this exam. I’m guessing it’s a real pain to develop these exams, especially when some ass like me starts moaning about them. Added to that, I’m guessing the word “Expert” means slightly different things to different people…

I’ve been barking on recently that in my opinion, the most important skill required by any PL/SQL developer is SQL, with knowledge of PL/SQL itself coming in second place. Having recently taken the “Oracle Database 11g: Advanced PL/SQL (1Z0-146)” exam (mentioned here), I thought it was a little hypocritical not to sit the “Oracle Database SQL Expert (1Z0-047)” exam as well, so this morning I did just that.

Here are some of my thoughts on the exam, in no particular order of importance:

• Regular Expressions: I think it is important that people understand what regular expressions can do and when it is appropriate to use them, but I don’t think it is necessary to test people on the meta-characters themselves. That’s what the docs are for.
• Analytic Functions: No sign of them in my questions from the pool. Surely analytic functions are more important than regular expression meta-characters.
• The majority of the exhibits were pointless. It seems like they were placed there to waste the time of people with bad exam technique, rather than to assist in answering the question. This was especially true of the schema diagrams, which I only referred to once when the datatype of one of the columns was important.
• Several of the questions could be answered without reading the question at all, as the incorrect answers jumped out at you because they contained blatantly incorrect statements.
• Several of the questions included the “ANY” and “ALL” comparison operators, which are barely mentioned in the documentation, and I don’t think are included in the 11gR2 documentation at all. I guess these are only included in Oracle because the are part of ANSI SQL. I can’t remember ever using them in Oracle or seeing them being used by others. I have come across them in mySQL so I knew what they were for, which was fortunate.
• There were lots of questions that included DML against inline views rather than directly against tables. It got to the point where I felt like, “If it’s got braces in it I’m going to tick it”.

I very quickly turned into a grumpy old man and started to rush through the exam, spending most of my time thinking about writing this blog post, rather than the exam itself.

In the end I got 96%, which I guess means I got 3 questions wrong out of the 70. Serves me right for rushing it so I could come home and bitch about it.

So I am now an “Oracle Database: SQL Certified Expert” as well as a grumpy old shite…

Cheers

Tim…

Becoming a bit of a tradition actually…

Trying for others to avoid the same, at least it was in 2008/2009, ordeal going thru the O.O.W. Schedule / Content builder trying to find XMLDB topics, I listed those I could find on the XMLDB OTN Forum. As said, trying to follow up on a tradition and to get myself (and hopefully you) an overview on things to come during Oracle Open World 2010 (/Oracle Develop /JavaOne). This year I think its becoming BIG regarding amounts /attendance of people if not only due to the combination of O.O.W./Develop/JavaOne on the same spot in San Francisco…

So, for me and those who are interested, just like the year before, hereby an attempt to find all XMLDB related presentations, workshops and other events during Oracle Open World 2010. I will try to add info, time and days later on (and/or you might) if I find them and/or if they become known. See for XMLDB presentations and Hands-on Lab sessions here:

• Oracle Open World 2010: XMLDB Presentations and Hands-on Labs

I hope you enjoy your presentations during O.O.W. and who knows we meet this year.

Not really into XMLDB presentations? Have a look at the 50+ Oracle ACE(D) presentations Oracle Open World and JavaOne, Oracle Develop listing here…

See also fbi_stats.sql for reproducible test case There are often odd little details that need finishing off. The wrong index is chosen – cost based – if it starts with a virtual column and the predicate is range-based, and there is an alternative index that starts with a real columns http://forums.oracle.com/forums/thread.jspa?threadID=1064154&tstart=0

Oracle 11i Applications Working on IE8, Firefox 3.5 & VISTA and Windows 7 What you need to do is below and please follow the exact steps for this to work. I got this to work on latest Firefox 3.5 and IE8 and that too on VISTA. It also works on the current Windows 7 RTM [...]

From time to time I add links to my blog list, but highlight for a few days with  a blog posting as well.  I’ve just been browsing through http://gavinsoorma.com and it’s probably worth keeping an eye on it – especially if you’re   getting to grips with Goldengate. Filed under: Uncategorized

If you’re running an Oracle Application Server 10g instance you are probably familiar with Oracle Enterprise Manager Application Server Control. If not, go back to the manual. This is not a how-to on setting it up or using it. If you want to know how to secure it and refresh the certificate when it expires, [...]

OK, let’s look at the next portion of the index block dump.   Following the hex dump of the block (as we ended Part I of the series) is the second part of the block header (see below):    Block header dump:  0x0201490a  Object id on Block? Y  seg/obj: 0x1c205  csc: 0x00.2d11214  itc: 2  flg: -  [...]

The following are simple steps to change the ORMI default port in Oracle Warehouse Builder. Navigate to OWB_HOME\bin\win32 and run ccashut.bat(in case OC4J is already running). Next navigate to OWB_HOME\owb\jrt\config\rmi.xml file look out for rmi-server tag for port and ssl-port attributes. Change the default non-ssl port 23791 and ssl port 23943, to any port which [...]

I’ll be giving a webinar about Exadata implementation, where I’ll be talking about Exadata features and how best to use them. I’ll also be sharing some lessons learned from my own implementation experience.

The webinar will be on Wednesday August 11 at high noon eastern time. Note that this is a change from the previous date.

To register, visit https:// www2.gotomeeting.com/register/171889707. For more Pythian webinars, visit http://www.pythian.com/library/webinars/.

This is my first mobile post so please excuse any typos. I must admit though that T-Mobile 3G is _fast_.

July 27, 2010 A question in an email from an ERP mailing list combined with Cary Millsap’s latest blog article inspired this blog article.  The question from the ERP mailing list asked the following question: Does anyone have Oracle syntax for the ‘upsert‘ command?  I have found a few examples, but little success yet. Using VB.net, I [...]

Today, I was doing an installation of Oracle 10g Rel 2 Database on HP-UX B.11.31 and found the below error.

trying to install Oracle 10 G release 2 on HP-UX (HP-UX B.11.31) and it was giving me an error mentioned below.


Checking operating system version: must be B.11.23. Actual B.11.31


Failed

Here you are not missing any patch or u don't need to download the Oracle binary again for HP-UX 11.31.

Here is the solution you can try.

Just ignore this error and start executing with below command that will ignore system Pre-requisites check.

runInstaller -ignoreSysPreReqs

I am supposed to be working on the next post for my PeopleSoft/RAC project, but decided to take a break from that and talk about OpenWorld.  As just about anyone in the Oracle universe is aware, OpenWorld is September 19th to 23rd.  This year I am doing three sessions along with helping to organize the User Group Forum on Sunday.  Before that all gets going though, I'm hoping to catch the Milwaukee Brewers at the San Francisco Giants.  I am a huge Brewers fan and I have never seen a game outside of Milwaukee.  The Brewers are in San Francisco for a weekend series and I'm hoping to get to the game on Saturday evening.  Anyone want to join me?

Sunday is again User Group Forum day at OpenWorld.  The schedule is a little different and the sessions are not starting until 12:30 do to travel restrictions on Satruday because of Yom Kippur.  IOUG again has eight rooms that we have schedule four sessions per room.  In addition, Oracle is having MySQL Sunday during the same time at the Marriott.  IOUG will be participating with some sessions there too.  This will split the audience a little bit on Sunday, but we are hoping for huge crowds at both events.  Check out the IOUG list of sessions or via Schedule Builder or Content Catalog on the OpenWorld site.

My personal schedule is already busy as usual.  I will be helping out at the IOUG booth again along with various meetings.  Plus, I am involved with three sessions this year.  For the User Group Forum on Sunday, I will be leading a round table discussion about Linux.  We will have several users as well as people from the Oracle development team.  This has been a very interesting and well attended session in years past.  People have had many questions and there is a lot of information sharing that takes place.  If you are an Oracle user that has anything installed on Linux, this should be a very informational sessions.  Come and learn about Linux and also share your experiences.

Recently, my company updated our PeopleSoft application.  As our PeopleSoft Administrator (PSA) likes to say, "we did a minor upgrade."  That "minor" upgrade included PeopleTools 8.49 to 8.50.08, Application Bundles from 25 through 28, and implementing Multi-Language.  We are toward the end of phase 2 of the project, which is to move to Oracle Real Application Clusters (RAC) for the database.  The PSA that was in charge of phase 1 and I are doing a presentation on the overall project.  This should be fun because we should be crossing a large number of functions and users.  The title is more about PeopleTools 8.50 which is fine because I think it will draw a big crowd.

When we decided to replace our PeopleSoft database server we went through an evaluation process to determine the best solution.  As part of that evaluation, we decided to use Linux as our database operating system.  I have been a Linux person for several years and had to convert our HP-UX system administrator into an accepter of Linux.  As part of that conversion, he and I will be doing a Linux session with the Linux product team from Oracle.  They will lead in with how the support model is supposed to work and what is included.  We will finish up with how it all really works.

That's my current wrap up for OpenWorld.  I'm hoping to see a lot of people that I only get to see a couple times a year.  I also get to meet new people every year and it blows my mind how many people are out there that are doing some really cool stuff.  If you are planning on attending, let me know and check out the User Group Forum on Sunday!

Have you ever met anyone who attracted your attention because he had the right idea, but the more you got to know how he arrived at that idea, the less attracted you felt?

All our lives, we learn how important it is to be correct, to have the right answer. You gotta have the right answer to make good grades in school, to nail that interview, to be accepted by your peers and your families and your supervisors, .... But too many people think that an education is merely a sequence of milestones at which you demonstrate that you know the right answer. That view of education is unfortunate.

Here’s a little trick that will help me demonstrate. I’m sure you already know how to “cancel” factors in fractions, like I showed in my Filter Early post, to make division simpler. Like this:But, did you know that you can do this, too?
You never knew you could do that, did you?

Well, that’s because you can’t. Canceling the nines produces the right answer in this case: 95/19 is in fact 5/1. But the trick works only in a few special cases. It doesn’t, for example, work here:
Canceling digits like this is not a reliable technique for reducing fractions. (Here’s a puzzle for you. For how many two-digit number pairs will this digit-canceling trick work? What are they? How did you figure it out?)

The trick’s problem is precisely its lack of reliability. A process is reliable only if it works every time you use it. Incomplete reliability is the most insidious of vices. If you have a tool that never works, you learn quickly never to depend upon it, so it doesn’t hurt you too badly. But if you have a tool that works sometimes, then you can grow to trust it—which increases the stakes—and then it really hurts you when it fails.

Of course, you can make a partially reliable tool useful with some extra work. You can determine under what limited circumstances the tool is reliable, and under what circumstances it isn’t. Engineers do it all the time. Aluminum is structurally unreliable in certain temperature ranges, so when a part needs to operate in those ranges, they don’t build it out of aluminum. In some cases, a tool is so unreliable—like our cancel-the-digits trick—that you’re better off abandoning it entirely.

So, if your student (your child) were to compute 95/19 = 5/1 by using the unreliable cancel-the-digits method, should you mark the problem correctly solved? It’s the right answer; but in this case, the correctness of the answer is actually an unfortunate coincidence.

I say unfortunate, because any feedback that implies, “you can reduce fractions by canceling digits,” helps to create a defect in the student’s mind. It creates a bug—in the software sense—that he’ll need to fix later if he wants to function properly. That’s why showing your work is so important for students. How can someone evaluate your thinking if all you show is your final answer?

Being a good teacher requires many of the same skills as being a good software tester. It’s not just about whether the student can puke out the right answers, it’s whether the process in the student’s mind is reliable. For example, if a student is prone to believing in an unreliable trick like cancel-the-digits, then a test where all the problems submit nicely to that trick is a really bad test.

Likewise, being a good student requires many of the same skills as being a good software developer. It’s not just fitting your mind to the problems in the book; it’s exploring how the things you’re learning (both code path and data) can help you solve other problems, too. Being a good student means finding out “Why?” a lot. Why does this work? Does it always work? When does it not work?

Clear thinking is more important than the right answer. Certainly you want the right answer, but knowing how to find the right answer is far more important. It’s the difference between having a fish and knowing how to catch more.

After covering hardware components of Sun Oracle Database Machine in part 1, our grand tour continues with a look at the software side. With the prominent exceptions of the Exadata storage server software and the Oracle database itself, the software stack is based on well-known and widely used open source products.

Database Nodes

The database nodes all run Oracle Enterprise Linux 5.3, a virtual clone of Red Hat Enterprise Linux 5.3, but also including some small performance-related kernel tweaks. Version 5.3, originally introduced by Red Hat way back in January 2009, is well behind the latest 5.5 version, but has been well tested and is still widely used. I wouldn’t expect major updates here before the next release of the Exadata hardware.

The hardware drivers, notably the OpenFabrics InfiniBand implementation, are GPL-licensed open source. Stacked on top of the infiniband are the Reliable Datagram Socket (RDS) protocol and the proprietary Oracle iDB. This is what Kevin Closson said about it in his interview on our blog:

… we’ve developed and brought to market the lightest—at least in our assessment—the lightest and most adaptable of all of them, which is Reliable Datagram Sockets [RDS]. Sure, you can do IP over InfiniBand, and that starts to chew into some of the value propositions involving InfiniBand, but we’ve done none of the sort. We are fully Remote Direct Memory Access (RDMA) from point to point over RDS.

The open-source Linux kernel is tainted by three proprietary Oracle modules used to support the ASM cluster file system (ACFS), a general-purpose filesystem with ASM on the back end, which isn’t used by default. These kernel modules are installed with any regular Oracle Grid Infrastructure 11gR2 installation:

[root@cellnode ~]# lsmod | grep oracle
oracleacfs            877320  0
oracleadvm            221760  0
oracleoks             276880  2 oracleacfs,oracleadvm

Running on top of the OS is the Oracle Database 11g Release 2. While it’s a stock install, Oracle 11gR2 includes a substantial amount of code to support Exadata’s features like smart scans and columnar compression, and this code is simply unused in non-Exadata deployments. The InfiniBand communication on the database cells using the iDB protocol is handled by libcell11.so module, linked into the Oracle kernel.

InfiniBand Switches

Although administrators rarely need to interact with the switch software directly, it runs Linux like the other components. For a switch made by Sun pre-Oracle acquisition (Sun Datacenter InfiniBand Switch 36), this is a bit of a surprise.

Storage Servers

The Exadata storage servers are where most of the magic happens. As with the database nodes, they run Oracle Enterprise Linux 5.3, with the same kernel and drivers as the database nodes. The Exadata support agreement places strict limits on what software can be installed on storage servers, permitting only the core OS and supplied Exadata tools. Even Oracle Enterprise Manager agents are not permitted to run, and instead a special Exadata plug-in makes outgoing SSH connections from a remote Management Agent to the storage servers to monitor them. Rather than using the traditional oracle OS user, a new OS user celladmin is available for administrative tasks, with the core background processes running as the root superuser. There is also a new OS user cellmonitor with limited privileges to monitor status of the storage servers.

As a departure from ASM storage technology that uses a process architecture borrowed from database instances, the storage servers have a brand new set of processes to manage disk I/O. They are:

• RS, the restart service. Performing a similar role to SMON, RS monitors other processes, and automatically restarts them if they fail unexpectedly. RS also handles planned restarts in conjunction with software updates. The main cellrssrm process spawns several helper processes, including cellrsbmt, cellrsomt, and cellrsssmt.
• MS, the management service. MS is the back-end process that processes configuration and monitoring commands. It communicates with cellcli, described in the next section. MS is written in Java, unlike the other background processes which are distributed in binary form and are likely written in C.
• CELLSRV, the cell service. CELLSRV handles the actual I/O processing of the storage server. It is not uncommon to see heavy usage from CELLSRV process threads during periods of heavy load. Among other things, CELLSRV provides:
  • Communication with database nodes using the iDB/RDS protocols over the InfiniBand network
  • Disk I/O with the underlying cell disks
  • Offload of SQL processing from database nodes
  • I/O resource management, prioritizing I/O requests based on a defined policy

In addition to the core background processes, storage servers have tools for administration and monitoring. They include:

• cellcli, the command-line interface that allows administrators to configure and monitor storage servers with a SQL-like syntax. This tool is unique to the Exadata Storage Server Software.
• dcli, a shell script allowing commands to be run on multiple machines with a single invocation. Since storage servers do not communicate with each other directly, the dcli command is particularly useful to run configuration commands across all storage servers. The dcli tool is also available on database servers, and can similarly perform administrative actions across database nodes.
• OSWatcher, a series of scripts that gather system performance information from such tools as top, vmstat, and iostat, at regular intervals. OSWatcher output can be useful in problem diagnosis. This is the same tool that is advised to install on the database servers and that many DBA’s are already familiar with.
• ORION, Oracle I/O Numbers, an I/O performance benchmarking tool. Please remember that ORION doesn’t do anything with the data unlike the database instance so it’s generally possible to achieve slightly higher numbers compare to the full blown benchmark including the database.
• ADRCI, a command-line interface to the Automatic Diagnostic Repository. This tool can automate the generation of diagnostic reports, typically when working with Oracle support services. Oracle 11g DBAs are already familiar with this tool that came around with the new logging and tracing mechanism in Oracle 11g.

Stay tuned for the part 3, where I’ll be talking about how Exadata is packaged and sold.

An article, by J A Flinn, on the "costing" of the human side of Projects / Change Projects, titled "What's the body count ?"

We do Guesstimations (calculated or not) once in a while… and this is an interesting read about it…

http://3quarksdaily.blogs.com/3quarksdaily/2008/09/useless-calcula.html

In the Oracle side… we also use this back-of-the-envelope-calculations for coming up with man-days, capacity planning, hardware recommendations, or just simply knowing what’s happening. And I like these phrases from the article…

“But how can we know the actual figure? We cannot. We can only get closer and closer approximations by measuring things more and more accurately (the volume, not just of the building, but of everything in it, which must be subtracted). It’s not like there’s an easy way to pour the air out of the building and weigh it!
The fun in doing these estimates is in NOT looking anything up, and instead trying to answer questions by using, along the way, what we do know to estimate everything we need to know to answer our question.”

Which means if you are building a mission critical system.. the goodness of the guesstimates is determined by the quality of the sizing requirements and how detailed it could be. And it is impossible to build good systems without this information which may result in being oversized (money loss) or undersized (customer loss).

I also like this comment from Greg Rahn (http://goo.gl/gFNO)

“The problem I see with IT shops and planning is there is there is either no planning, or it is done incorrectly. Very few shops take the time to build and test, it is more like build and deploy, then “tune”. Let me put this into a slightly different context: If you were tasked with building a bridge from point A to point B what information would you need? Would things like amount of traffic, both peak and average, type of traffic: train, bus, trucks, cars, bicycle & foot be important? It would to me. I see IT shops that design a foot bridge (because it is cheaper) and then try and put cars onto it and then it falls down and they complain. Capacity planning and building computer systems is not about guessing, it is about engineering and calculations. That being said, what calculations go into your system design?”

How about you? Do you have any interesting Oracle Guesstimations?

Question? How fast an Oracle server can you create for £1,000 pounds?

{I’d really appreciate feedback and suggestions on this particular post}

The power of domestic PCs continues to grow, with four-core chips become pretty much standard and starting RAM looking more like 4GB than 2GB, with 8GB quite reasonable. So, how quick an Oracle server can you make based on a domestic PC? After all, those of us who play with Oracle in our spare time tned to use such machines and, in fact, they are often not far off what are our smaller servers at the office really are. When I worked at the Wellcome Trust Sanger Institute, we had to make our IT budget pounds go a long way. We were, after all, a charity with a limited budget but also a scientific organisation with a huge demand for data and processing. So we used a lot of cheap kit.

I’m seriously thinking of giving this a go. I need a new PC anyway and so I am willing to use it, at least initially, to see what can be done.

If I do this, I’m going to need to set some boundaries on the exercise. How about:

• The oracle licence is being ignored in the cost {and please, I don’t need to be told how the licence can be more than the hardware costs!}. OS cost is though.
• I am not aiming for enterprise-level resilience, so I am not going to consider hot-swappable components, dual redundant power supplies or things like that.
• I am going to use new kit, so no scavenging or buying second-hand. It must all be easily available and repeatable.
• I will use local storage in the server or connected to ports available on the server.
• It will support a database of 1TB in size {yet to be designed}.
• Oracle v11. Enterprise edition but nothing special like TimesTen or Exadata (unless Oracle are willing to sell me an Exadata box for a grand, then I’ll consider it).
• I’m not considering backup and recovery performance {and this would be a serious oversight if this was a real system, but most places have central backup/recovery facilities}.

I would also have a few other things to decide.

The main one is “Do I use Linux or Windows?” Yes, you are all probably shouting “Linux!!!” but I have never been a Linux sys admin (I was an incredibly poor HP-UX system admin for 3 months though) so it will take me more time to deal with issues under Linux - in work situations I have always had access to people who know all this stuff to sort out issues but in this case I will be doing this on my own. On the other hand, you can just chuck Oracle on a standard windows box and it works, and as a rule hardware just works under Windows. If I decide to use USB3 ports, for example, is it going to be a major pain getting drivers under Linux? But then if I want the fastest oracle box under a grand why would I slow it down with windows and spend money on the licence? I just want the box to run Oracle and a workload.

The second “software” decision is, how do I measure performance? I think I could be getting to grips with Dom Giles’ excellent Swingbench {BTW, nice tag line on that page, Dom }. But it runs on Java and guess what boys and girls? I’ve never been a Java developer. How limited are my skills! So that would take some of my precious spare time up too.

I’d love feedback on this, I’d love to know what hardware suggestions you would make, what you think about the overall idea, what else I need to consider to make the tests valid… I have a few ideas already for the hardware architecture and the intention would be to try lots of things but I’ll save that for a second post. After all, if I get no feedback I might just spend the money on a gaming machine and a week’s walking in the Lake District instead.

And if anyone want to help with the cost, please send cheques to….

I received some emails recently asking were will be my next presentations, and to make it public here we go: Insync’10, Melbourne August 16 – Topic: Tips and Best Practices for DBAs @ 9:55AM to 10:40 AM more info at  http://www.insync-conference.com.au/ NZOUG OTN Days, August 18 in Auckland and August 20 in Wellington – Topic How to [...]