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.

OCP Upgraded to 10g

I've been working with Oracle 10g for a couple of years now, but never got around to upgrading my Oracle 8i OCP certification. I had some down time this month, studied a couple of days, and as they say in Texas, "got 'er done."

I'm now an Oracle Database 10g Administrator Certified Professional.

MCITP Database Administrator

My certification upgrade for teaching SQL Server 2005 is now complete.  Just in time for the new year, I achieved my Microsoft Certified IT Professional certification with the Database Administrator specialty.

SQL Server 2005 has three specialities: Database Administrator, Database Developer, and Business Intelligence Developer. Although historically my most taught class was a database development class, my new certification doesn't qualify me for the equivalent in the SQL Server 2005 series. This is because Microsoft has realigned its Database Developer track around Visual Studio .NET and the .NET framework. It's not my key focus area, so I'll have to let that pass.  Shame.

But I'm very interested in the Business Intelligence certification and have some backround in that area, so we'll see what Microsoft comes up with (the exams are not yet released.)

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.