Oct 2012 PSU and CPU Released

The October 2012 Patch Set Update (PSU) and Critical Patch Update (CPU) for Oracle products were released yesterday, 16-Oct-2012.  The Availability Document, which lists the patches to get for different products, is My Oracle Support note 1477727.1.

A couple of interesting things this quarter.  Critical Patch Updates are being renamed Security Patch Updates, or SPUs. The release schedule is the same, as is the content.  MOS note 1430923.1, "New Patch Nomenclature for Oracle Products", describes the change.

The PSU/CPU also includes patches for the SHA-1 hashing algorithm to address two vulnerabilities.  There are special notes about how to apply patches this quarter because of these.  Always read the README, and don't assume that you know how to do this quarter's patch just because you've done them before.

The PSUs for 11.2.0.2  (starting with 11.2.0.2.7) and 11.2.0.3 (starting with 11.2.0.3.2) now use the new Composite Patch format, which makes overlay patches to previous updates less troublesome.  MOS note 1376691.1 describes the new format and its advantages.

The January 2013 PSU/CPU will be the final ones for Database 11.2.0.2,  Oracle Fusion Middleware 11.1.1.5.0, and Oracle Fusion Middleware 11.1.1.4.0 for Portal, Forms, Reports, and Discoverer (PFRD).

Change Data Capture for Oracle in SQL Server 2012

I just ran across this article on Microsoft's SQL Server Integration Services blog.  SSIS was always impressive, even back when it was called Data Transformation Services (DTS), because it could move data between any OLEDB data provider, not just Microsoft SQL Server.

Now apparently the latest version of SSIS goes one step further, mining the logs of an Oracle instance to provide Change Data Capture (CDC) services.  For those who need to maintain both platforms and get them talking to each other, this is an interesting read.

Database Sharding

I discovered a new term today, via a useful post on Julian Dontcheff's Database Blog (which I also discovered today). His post, Do not go beyond this point: on the "obvious advantages" of Database Shards, succinctly describes what database sharding is, and even better, points to three resources for deeper study.  He writes:

Caution! Database Sharding is like the anti-consolidation of databases. It is splitting the database into many small databases. You spend years and years on trying to unify and gather together databases and all of a sudden you are told that there is an application managed scaling technique using hundreds of independent databases. Tricky, right?

Sometimes, when planning database solutions in terms of scalability and massiveness, going beyond a certain point might be risky. This is the case when database shards may be of huge help (big website used globally). The word shard may sometimes refer to a piece of glass, a sea glass that can be found almost everywhere, for example at the beaches near San Francisco.

I share his amazement that after years of listening to vendor sermons on the benefits of server consolidation, now there's talk about going the opposite direction. Whatever happened to "green" in the data center?

He also makes the excellent point that proponents of standing up many servers with small databases typically ignore database licensing fees, which are typically charged per server (sometimes per core).

Anyway, good read.

Article: Collections in PL/SQL - An Introduction

My latest technical article, "Collections in PL/SQL: An Introduction", has been published online in the newsletter of the Dallas Oracle Users Group (DOUG). 

The article explains why collections are an important part of the PL/SQL language, and lists some typical uses for them. It then walks through three sample scripts showing an associative array indexed by a character string instead of an integer; using a collection to read multiple rows from the database at once with a bulk fetch; and creating a collection in which each element is an entire row from a table.

The newsletters are available to the public without membershp or registration on the site.

MR Trace: SQL Developer Add-In Gets Trace Files for You

Cary Millsap and the folks at Method R have devised a cute add-in to Oracle SQL Developer.  It's called Method R Trace (MR Trace or "Mister Trace" for short) and it does a very simple thing elegantly: when you run PL/SQL code, it automatically turns tracing on and off at the right times, fetches the trace file for you, and presents it in a list right there on the screen.

I read about it on Cary's blog.  Here's a short video demo with Cary at the keyboard:  

He's proud of it because it's the "smallest software tool we've ever designed," but it's usefulness seems way out of proportion to its size.  Just not having to bug the DBA to get your trace files seems worth it to me!

(I'm going to start a new policy here that I hope will catch on among all bloggers: anytime I talk about a product, I'm going to disclose whether or not I received anything of value for the mention.  I didn't, though I did win a Method R door prize at an Oracle User Group meeting once.)

Simple Oracle Practices

Sometimes, it's the simple things that get us into trouble -- or can save us big time.

Any experienced UNIX admin will tell you to always do an "ls" (list structure) command with your wildcards before you use them in a "rm"  (remove) command.  Similarly, you should test your WHERE criteria in a SELECT statement (perhaps with COUNT) before you use them with a DELETE.

Here are some more very simple practices to develop as a habit, and which will save you at some future time.

1.  After you set ORACLE_HOME manually, test it by changing your current directory to it.  In Unix/Linux, the command is "cd $ORACLE_HOME"; in Windows, it's "cd %ORACLE_HOME%".  If the cd command succeeds and you list the contents of the directory, you should see a standard ORACLE_HOME.  If the cd command fails, or doesn't have the usual subdirectories in it, you've mistyped the pathname and can fix it before relying on it in other commands.

2.  After you connect to the database using "/ AS SYSDBA", test it by the following query:

SELECT instance_name, status FROM v$instance;

You'd be surprised how many times you THINK you connected to one instance, but in reality, you've connected to a different one. This only takes a second, and can save you a LOT of grief.

3.  After a SHUTDOWN ABORT for any reason, immediately follow with STARTUP RESTRICT.  This lets the SMON process perform instance recovery, so that the database is consistent; but it doesn't allow non-privileged users into the database.  Then do a SHUTDOWN IMMEDIATE and continue with whatever corrective action you need.

Three simple things, which if made into a habit, can help prevent gunshot wounds in your feet.

Interesting Daylight Saving Time Problem

I was teaching an Oracle Database Administration class last week and ran into an interesting problem involving the change to Daylight Saving Time  (you did know that it's Daylight Saving, not Daylight Savings, right?)

For this class, we usually install just the base version of Oracle Database 10gR2 (10.2.0.1.0), because we're on a private network behind a firewall and we don't cover how to upgrade and patch Oracle until the advanced class. And then we show Enterprise Manager Database Control.

Except that this week, it wouldn't come up. We got Java errors, and the home page said that the status of the database instance couldn't be determined. When we tried to start the DB Console service (this is on Windows), it said it was already running; but when we tried to shut it down, it said it wasn't running. Very weird.

In doing a web search for the Java messages (Google is my friend), time and again we were advised to check how our time zone was set. That seemed odd; you wouldn't think that would matter. But it does: the EM agent checks to see that its time zone is (a) valid, and (b) configured is the same as the server.

Enter Daylight Saving Time. In 2007, the US and Canada changed the start date of Daylight Savings Time to be in mid-March (March 11 that year) instead of the first Sunday in April as it had been. This required patching Oracle products with a new timezone file, so they could properly detect the change. (The document ID on My Oracle Support is 359145.1).  This year, we changed to DST on Sunday, March 14.

The class was held the week of March 29, and the unpatched 10.2.0.1 software still thought we should be on Standard Time until April 4. So the EM agent wouldn't start. Had we done the class a week later, or three weeks earlier, this wouldn't have been a problem; and in fact we have done this class many times since 2007 and never hit this issue because the timing wasn't JUST right.

So we did an unscheduled lab to download 10.2.0.4 patchset and apply it, and EM came right up.

Stuff like this underscores the importance of patching your databases, even in a training environment!

There's something to learn from a training perspective here too. We often think that if things go wrong, our students will lose faith in us and think we don't know what we're talking about. But in this case, I was able to model the proper response a DBA should take: stay calm, research the issue, test. I've had students comment in the past when things went wrong that seeing how to resolve such issues was an unexpected bonus of the class. This class felt the same, they enjoyed the "detour" and the problem solving.

Jonathan Lewis Seminar in Dallas

I got to hear Oracle performance guru Jonathan Lewis today in an all-day seminar in Dallas. "Writing Optimal SQL" was part 2 of a two-part series here in Dallas, sponsored by Dallas Oracle Users Group (DOUG).

Jonathan started out by reviewing some general principles, such as the importance of design and optimizing for the global situation (many queries) not just a single query. "Making me go fast might make you go slower," he said.

Performance comes from precision in selecting just the blocks and rows you need to select, and from the structure of the data (where it's located).

My favorite part of the seminar was his discussion on coding style for SQL, a topic that is often ignored in performance discussions. His view that "Computers are cheap; humans are expensive" is one that I've been preaching for years in my SQL and PL/SQL classes.  "You have two audiences," I tell my classes, "the compiler, and the poor person who comes after you to maintain your code.  Be kind.  Someday that person will be you." Jonathan's take is that you should write SQL as a close translation of the business purpose of the query, and only fiddle with it if performance isn't acceptable.

He also suggested: "Don't tune the SQL; tune the end users," meaning that if a requirement is unclear or the data creates unexpected results, go back to the person requesting the report and get clarification instead of burning a lot of time over-engineering the solution to fit every possible case.

This talk could easily have been titled, "Unintended Side-Effects of SQL" because Jonathan went through many scenarios in which results differed from what might have been expected, and why it turned out that way.

It was a great session.  Thanks to Speak-Tech  and to DOUG's Mary Elizabeth McNeely, president and programs chair, for bringing Jonathan Lewis to town.

Oracle 11gR2 Podcast by Tom Kyte

Tom Kyte recorded a podcast this month about the new features of Oracle Database 11gR2. The podcast, part of the Oracle Database podcasts series, can be accessed here:

http://www.oracle.com/database/podcasts.html

In it, he describes an exciting new feature: the ability to compile stored procedures, packages, etc. while users are online using the same schema! The feature, called Edition-based Redefinition, refers to the current objects as an "edition".  The developer creates a child edition based upon the current one, then does development against the child edition. When it comes time to switch over, new sessions can begin using the new edition, as existing sessions continue to use the old. An upgrade that used to take an hour of downtime for compilations can appear to users to take no time at all. It still takes an hour to compile, he said, but the users are not locked out of the system while it's happening.

He also talks about the ability to create a table, but defer to allocation of storage until the table is first used. This will make it much faster to install complex software applications that have many optional tables in them. Only the metadata in the data dictionary will be created during the install, and tables never used will therfore not take up space.

It's a short talk, about 10 minutes, but well worth listening to.

Controlling Controlfile Backups

Did you know that you can control the name of your controfile trace backups? I didn't, until Bob Morgan of Stonehill College in Easton, MA pointed out some additional syntax in the ALTER DATABASE command.

I already knew that you could control the filename and location when backing up the controlfile in binary, via:

ALTER DATABASE BACKUP CONTROLFILE TO '<pathame>';

And I knew that you could get a text copy of the commands necessary to rebuild the controlfiles, via:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

The resulting text commands are placed in the user's trace file for the session. To find the proper trace file requires knowing the session number; it's in the filename.  (Though I usually cheat, and go to the directory pointed to by the user_dump_dest parameter, sort by modification time, and my session is near the top.)

What Bob pointed out was the variation:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '<filename>';

This still writes to the backup_dump_dest directory, but names the tracefile with your choice of filename instead of the default one that includes the session number.  Much easier to find.

Thanks, Bob!

Tom Kyte's New Years Resolutions

Anyone who knows me knows I'm a BIG Tom Kyte fan.

Tom's published his list of New Years Resolutions for 2009, and the top of the list is "Practice a restore at least once a month."  You can read the rest of the resolutions here:

http://www.oracle.com/newsletters/information-indepth/database-insider/jan-09/kyte.html

(Thanks to Chen Shapira on Twitter for the pointer to this.)

Oracle Database 11gR1 on VMWare

I've just finished creating the test environment I'm going to use to prepare for the 11g OCP upgrade test. Instead of installing directly on a lab machine as I've done before, I decided to do this one inside a VMWare virtual machine. Here are some notes on what I did, while they're fresh in my mind.

Don't you just love the Internet?  I found an excellent step-by-step install guide by John Smiley, and it really shortened the learning curve. It also contains links to the download sites for Oracle Enterprise Linux 5, which is functionally similar to Red Hat Enterprise Linux 5, and Oracle Database 11gR1.

VMWare layer: 

I created a VM with 1024Mb memory, a single 20GB hard disk, one network adapter bridged. I don't plan to do RAC scenarios with this image, it's just for the basics, so I kept it simple. I can add more virtual hard drives to it later to practice ASM.

Linux layer:

I used Oracle Enterprise Linux 5, downloaded from Oracle.  I followed the install instructions from Oracle-Base, which included details on disk partitioning. OEL5 includes 5 CD images as .iso files, but the install only required the first 3.

On my first attempt, I did as the Oracle notes suggested and did a default install of packages. But this led to problems later with missing libraries needed by various RPMs. So, I started over, selected "Customize Now" instead of "Customize Later," and followed instructions I had used successfully with 10gR2 in terms of which packages to include and not:

• GNOME Desktop Environment
  
• Editors
  
• Graphical Internet
  
• Text-based Internet
  
• Development Libraries
  
• Development Tools
  
• Legacy Software Development
  
• Server Configuration Tools
  
• Administration Tools
  
• Base
  
• Legacy Software Support
  
• System Tools
  
• X Window System

This worked much better.

I then followed the Oracle instructions exactly for preparing the OS for Oracle, in terms of adding several additional RPMs, setting kernel parameters, creating security groups and creating the oracle user.

Oracle installation:

My first try failed due to unzipping all the files on my Windows machine and burning an ISO image. I got permissions errors when trying to start the Oracle Universal Installer. So, I reburned the image with just the ZIP file as downloaded by Oracle, and unzipped it within the Linux environment. The rest of the install was textbook.

Throughout, I used the VMware Workstation 6.5 snapshot feature, taking snapshots of the base Linux machine, Linux machine plus additional packages, Linux machine plus customizations for Oracle, and finally after the successful install.  I connected with sqlplus and was rewarded with the opening banner giving the version as 11.1.0.6.0.

Let the studying begin!  Oh wait, it already has.

New Oracle Certified Associate (OCA) Requirements

New candidates for Oracle's entry level DBA certification must now pass an exam on SQL, in addition to the existing requirement for passing the Database Administration I test. The Oracle Certified Associate (OCA) credential is the first rung in Oracle's 3-step DBA certification ladder. The change went into effect December 1st, 2008.

According to the Oracle University website, any of four exams will meet the requirement:

• 1Z0-001: Introduction to SQL and PL/SQL


• 1Z0-007: Introduction to 9i SQL


• 1Z1-051: Oracle Database 11g SQL Fundamentals I


• 1Z0-047: Oracle Database SQL Expert

Existing OCAs and OCPs do not have to fulfill the additional requirement. Their upgrade paths remain the same.

Oracle on VMWare

One of my current projects is building VMWare virtual machines that I can use to practice installing Oracle 11g and third-party products.  I found some great "cheat sheets" at the Oracle Base website:

• Oracle DB 11gR1 RAC Installation on OEL5 Using VMWare.  This one covers setting up Oracle Enterprise Linux 5 on VMWare virtual machines in a configuration designed to be used as a RAC node (e.g., dual network interface cards). It's the whole meal, from soup to nuts. The level of detail is great.
  
• Oracle Enterprise Linux 5 Installation.  This is a step-by-step, generic setup of OEL5 suitable for adding other software (like Oracle) later. It's not Oracle-specific, but a good overview.

Both of these have lots of screenshots and the little tips that head off disaster.

I do have a couple of qualms, though.

Now that Oracle has released their own virtualization solution, OracleVM, they are not likely to ever support VMWare-based solutions. Part of me says I should be building these VMs on OracleVM. However, Oracle VM requires a separate machine to run, and VMWare doesn't. Plus, VMWare is a clear market leader at this point. Because these machines will be used as learning aids, not production, I don't think it's a big risk.

The other thing is using Oracle Enterprise Linux as the O.S. instead of something more generic. The only reasons I'm going this way are that the cheat sheets use it, and it is a free download.

But clearly, things have come a long way since people were first struggling with Oracle in virtual environments years ago.

First Look at Oracle Database 11g

Here are some notes I took at this month's Dallas Oracle Users Group meeting, at which Linda Smith of Oracle previewed the new features coming in Oracle Database 11g.

The first question she answered is:  When?  The official word is "2nd Half of Calendar Year 2007." Originally, it was closer to July, but now she suspects it will be around September, or timed for Oracle OpenWorld conference in San Francisco, in November.

The focus of the release is clear: Availability. Many of the new features are aimed at keeping the database available 7 x 24 x 365, despite the many database administration tasks that must be performed regularly. Here are some highlights:

• Change Management. "Change is the most common cause of instability," Smith said. So Oracle 11g will include features that permit more realistic testing of new releases and patches before committing them to production. You'll be able to snapshot a physical standby, use it to perform testing, then rollback the snapshot.



• Database Replay. This lets you capture the actual workload from a production system, and use it to stress-test your test environment to be sure the changes work. Furthermore, your test environment does not need to have live application servers and client stations to do this: the replay looks the same.  (Of course, you'll want those pieces in your test infrastructure anyway, to test them. But you won't need them to test the database.)



• Online patching.  This boggles my mind, but supposedly the new release will let you apply patches "hot," without a service outage. This includes the quarterly Critical Patch Updates we've all grown to know and love.



• SQL Performance Analyzer. This captures before and after measurements of your SQL, and after you apply an upgrade, can show you which SQL got better -- and which got worse (regressed). So, during testing of an upgrade, you can tune what needs to be tuned before taking the release to production. There's also an automatic SQL tuning feature that will transparently rewrite any statement that it can get a 3X improvement on.



• Flashback Data Archive. In the past, the Flashback Query feature could only go back as far as the size of your Undo Tablespace permits. In 11g, you can configure a more permanent archive that will enable you to flashback years, if required. This is aimed at auditors and compliance reporting, but I can see some interesting DBA possibilities too.

Obviously there's a bunch more, including new partitioning options, a replacement for the way LOBs are done that looks pretty slick (SecureFiles), improvements to ASM, and developer stuff.

Time to book my trip to San Francisco for September!

UPDATE, 4/25/07:  I forgot to mention that a new version of the free Express Edition is also planned, but will lag the Enterprise Edition ship date by some number of months.

Oracle Tips - March 2007

Here are my Oracle Tips columns published at TechRepublic.com during March, 2007:

Gather Query Tuning Data via Oracle's Dynamic Performance Views - March 6, 2007
The dynamic performance views supplied with the Oracle database include several that make the process of gathering tuning information easier. This tip describes four of them: V$SQL, V$SQLAREA, V$SQLTEXT, and a new one, V$SQLSTATS.

Test XML Queries Interactively in Oracle 10gR2 SQL*Plus - March 13, 2007
The SQL*Plus program can now process XML (and SQL) queries using the XQUERY command added in Oracle 10gR2. This tip shows you some of the possibilities.

Capture an Oracle 10g Audit Trail in XML Format - March 19, 2007
Oracle 10g can be configured to write its audit logs to XML files in operating system directories. This tip shows the configuration changes that need to be made, as well as how to access the resulting XML files.

Use Logon Triggers to Initialize User Sessions in Oracle - March 27, 2007
Application contexts enable you to store data in memory for applications to use. The After Logon database trigger is a convenient way to initialize a user session by setting application context values for it. This Oracle tip shows you why�and how�it's done.

Oracle Enterprise Manager 10gR3 Launch Webcast

Oracle held a live webcast today to announce the latest release of its Enterprise Manager product, 10g Release 3 (10gR3). (If you missed it, you can catch a replay.)

The webcast was hosted by Charles Phillips, president of Oracle, as well as Richard Sarwal, SVP, Applications and Systems Management. The fact that they have a SVP level executive overseeing Enterprise Manager indicates they're serious about moving this oft-maligned product forward.

The theme of the webcast was "Top-Down Application Management." The message was that unlike most systems management solutions, which are focused on monitoring "bottom-up," the underlying infrastrucure beneath applications, the new version of Enterprise Manager is designed to monitor applications directly, the top of Oracle's "stack". You can code service level agreements in the form of policies, and OEM will monitor which applications and underlying objects are performing within their SLAs. You can drill down on those that aren't to see what the problem is.

The speakers also made a distinction between systems management products that are "viewers" as opposed to "doers". All such products include dashboards to monitor thresholds and report violations. But OEM is designed to let you directly do something about it as well.

The key drivers that are making this an important product for Oracle are the increasing complexity of always-on, multi-product, multi-vendor, mission critical systems; and the increasing requirements for regulatory compliance not just in the U.S. but around the world.

The phrase, "Complete and Open" captured the intent of what OEM is to become. The new version will include adapters for SAP, WebSphere, and Microsoft Operations Manager (MOM) as well as for Oracle's own products. Features will be standards-based for interoperability.

One of the more fascinating parts of the show were the cutaways to Mark Sunday, CIO, at Oracle's data center in Austin, TX. Video segments showed some of the 23,000 servers and 5PB (that's PETAbytes) of storage in house, used both for Oracle's own internal processing and to support their Oracle On Demand service. The control center reminded me of the tour I took recently of Johnson Space Center in Houston: large display monitors covered the front wall, with the rest of the room dedicated to individual workstations. Appropriate image for a product "launch."

Oracle Tips - February 2007

Here are my Oracle Tips columns published at TechRepublic.com during February, 2007:

Hide Sensitive Data with Oracle 10g Column Masking - February 6, 2007
Oracle 10g's Virtual Private Database (VPD) feature has the ability to automatically mask out (set to NULL) certain columns in the results of a query on a row-by-row basis. This tip shows how to create a VPD policy that requests the column masking option.

Trade Risk for Speed with Oracle 10g's Asynchronous Commit - February 13, 2007
A new option to the COMMIT statement in Oracle 10g can defer the writing of committed data to the online redo log files. This involves a risk of data loss, but it speeds up the transaction response time. Find out more details in this tip.

Delete Rows During Updates using Oracle 10g's Merge Statement - February 20, 2007
In Oracle 10g Release 1, the MERGE statement gained the ability to delete rows, as well as update and insert them. This Oracle tip shows you how it works.

Move SQL Tuning Sets between Oracle Instances - February 27, 2007
SQL Tuning Sets (STS) capture the workload of an Oracle instance. Starting in Oracle 10g Release 2, these tuning sets can be transported from one instance to another to facilitate testing.

Oracle Tips - January 2007

Here are my Oracle Tips columns published at TechRepublic.com during January, 2007:

Resize BIGFILE Tablespaces Directly using ALTER TABLESPACE - January 9, 2007
Database administrators can now resize Oracle datafiles at the tablespace level using the BIGFILE tablespace feature in 10g.

Convert Databases to Different Platforms using RMAN - January 16, 2007
The Oracle Database supports the direct movement of tablespaces from one instance of Oracle to another. In the past, the destination was required to use the same operating system platform as the original. Oracle 10g contains tools in the Recovery Manager (RMAN) to extend these moves to other platforms.

Detect Hierarchy Loops with Oracle 10g's New Hierarchy Pseudocolumns - January 23,2007
Hierarchical queries sometimes fail because the data contain rows that reference each other: a loop. Oracle 10g has syntax and two new pseudocolumns to aid in finding such loops.

Look Inside ASM Disk Groups with Oracle 10gR2's ASMCMD - January 30, 2007
In Oracle 10g Release 2, Oracle introduced a new command line tool called ASMCMD that lets you look inside Automatic Storage Management (ASM) volumes. Get an overview of the ASMCMD commands, and learn about a few of the tool's quirks.

Oracle Tips - December 2006

Here are my Oracle Tips columns published at TechRepublic.com during December, 2006.

Enable Change Tracking in Oracle for Faster Incremental Backups - December 5, 2006.
DBAs can do incremental backups made via Recovery Manager significantly faster by enabling a new Oracle 10g feature called Change Tracking. Bob Watkins explains how to enable the feature.

Consider Oracle 10gR2's Instant Client Light for Application Deployment - December 12, 2006.
In 10g Release 2 (10gR2), a new Oracle Client option can save disk and memory requirements when accessing Oracle from a custom application. Consider whether Instant Client and Instant Client Light can do what you need instead of the full Oracle Client.

How to Order Siblings in Oracle Hierarchy Queries - December 19, 2006.
Developers can use the ORDER SIBLINGS BY feature of Oracle 10g and later to sort the records in a hierarchy created by the START WITH and CONNECT BY clauses in a SELECT statement.