Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 19, 2009

Oracle Database 11g Release 2 New Features Summary, Part 1

By Jim Czuprynski

Synopsis. Oracle at last released the long-awaited next version of Oracle Database 11g Release 2 (11gR2) in September 2009. This article – the first in this series – summarizes some of oracle 11gR2’s more intriguing new features and discusses how those features might help enterprises to decide to upgrade their Oracle 9i, 10g, and 11gR1 database to Oracle 11g Release 2 in the immediate future.

After what seemed an excruciatingly long time, Oracle suddenly announced the public release of Oracle Database 11g Release 2 on September 1, 2009. I’ve got to admit that I was surprised Oracle was able to keep the new features of this release under wraps for such a long time, and I’m sure that the release was timed to build excitement and anticipation for the attendees of Oracle OpenWorld 2009.

After scouring the Oracle Database 11gR2 New Features documentation for several hours, experimenting with the new Oracle Universal Installer (impressive!), and creating my first single instance RAC database (yes, you read that correctly), I’ve summarized my first five personal favorites from the voluminous list of new features in Oracle Database 11g Release 2. I’ll provide capsules of my final five favorite new features in an upcoming article, and please be sure to visit Database Journal periodically over the next several months to join me as I explore the depths of this new release in the coming months.

#1: Everywhere You Look, Clustering Is There

In prior releases, Oracle Clusterware had to be installed separately into its own Oracle Home only when constructing a Real Application Clusters database environment. That all changes in Oracle Database 11gR2 because this release supports the installation of the Oracle Grid Infrastructure, a separate Oracle home that incorporates the binaries for both Oracle Clusterware as well as Oracle Automatic Storage Management (ASM). Once the Grid Infrastructure home is installed via the upgraded Oracle Database 11gR2 Oracle Universal Installer (OUI), a whole new array of features and services are available. I’ve listed some of the more interesting ones here:

Single-Instance RAC (Oracle Restart). I know, it sounds like a contradiction in terms, but Oracle 11gR2 extends the capabilities of Oracle Clusterware to provide high availability features for any single-instance database, essentially turning that database into a single-instance RAC database. Oracle 11gR2’s new Oracle Restart features leverage the features of Oracle Grid Infrastructure’s High Availability Services (HAS) to control which Listeners, ASM instances, and Oracle databases should be started whenever the server is rebooted. This replaces the old DBSTART scripting DBAs have used in past releases to make sure that the server starts the appropriate database instances upon server startup based on the configuration retained in /etc/oratab. Also, should a single-instance database crash or otherwise terminate unexpectedly, Oracle Restart automatically detects the failure and will restart it just as if it were a database instance in a Real Applications Cluster environment.

SRVCTL Upgrades. You’re already familiar with the simplicity and power of the SRVCTL utility to control any aspect of a Real Applications Cluster environment if you have managed a RAC database in earlier Oracle releases. Oracle 11gR2 now expands this utility’s scope so that it can also manage any single-instance RAC database as well as that database environment’s listeners and ASM instances.

Cluster Time Synchronization Service. Oracle 11gR2 now requires time synchronization to be configured for all nodes in a RAC clustered database environment … and for good reason. If you’ve ever experienced a node eviction within a Real Application Cluster database’s cluster configuration, you know how difficult it can be to tie together the train of events that caused the eviction – especially when two servers have diverging time settings and the log file entries’ timestamps aren’t synchronized. Prior Oracle releases have offered the ability to synchronize all nodes to the same time using Network Time Protocol (NTP), but this is not always an option because it requires communication outside the firewall to a network time service like the US Naval Observatory (http://tycho.usno.navy.mil). As an alternative to NTP, Oracle 11gR2 now offers a new Cluster Time Synchronization Service to insure that all nodes in a cluster maintain time synchronization.

Grid “Plug+Play.” In prior database releases, one of the most complex parts of setting up a RAC environment is the determination and setup of all the required IP addresses for the public, private, and Virtual IP (VIP) node applications. To simplify RAC installations, Oracle 11gR2 now offers the Grid Naming Service (GNS) that cooperates with the environment’s domain name server to handle the assignment of IP addresses for each Grid component – an obvious advantage when a clustered database environment spans several nodes.

Clean Deinstallation of RAC Components. This feature is an obvious boon, especially if you’ve ever tried to remove all traces of Real Application Clusters from multiple nodes. In Oracle 11gR2 all of the installation and configuration assistants – especially Oracle Universal Installer (OUI), Database Configuration Assistant (DBCA), and Network Configuration Assistant (NETCA) – have been augmented so that RAC components are completely removed when complete deinstallation is requested.

#2: ASM Joins Forces With Clusterware

Oracle Database 11gR2 imbues Automatic Storage Management (ASM) with some intriguing new features. For starters, the ASM binaries reside within the same Oracle home as Oracle 11gR2 Clusterware, so the recommended best practice in prior releases – installing a redundant Oracle home to support separate patching of the ASM binaries redundant Oracle database home - is eliminated. Also, the installation of ASM is now managed from the new Automatic Storage Management Configuration Assistant (ASMCA) instead of DBCA. Here’s my take on some of the most intriguing new ASM features:

Intelligent Data Placement. Where ASM places data on an ASM disk determines how quickly it can be accessed, retrieved, and updated. In earlier releases, this meant I needed to get my storage administrator directly involved in the appropriate disk configuration of the disk I/O subsystem. However, Oracle 11gR2 can now place ASM allocation units so that they can benefit directly from the faster cylinders toward the outer edge of the disk platters for the files that can most benefit from that disk architecture (i.e. data files, online redo logs, and control files).

EM Support Workbench Extensions. The Automatic Diagnostic Repository (ADR) features that Oracle 11gR1 introduced in Enterprise Manager Database Control are extended in this release to include support for ASM diagnostics, including the ability to bundle up any diagnostic information into incident packages for direct shipment to Oracle Technical Support for faster resolution of ASM performance issues.

ASMCMD Enhancements. The Automatic Storage Management Command-Line utility (ASMCMD) gains some significant features, including the capability to:

  • start and stop an ASM instance;
  • backup, restore, and maintain the ASM instance’s server parameter file (SPFILE);
  • monitor ASM disk groups’ performance with the iostat directive; and
  • maintain the disk volumes, directories, and files store within the new ASM Clustered File System (ACFS), my next topic.

#3: ACFS: A Robust Clustered File System

Earlier Oracle releases offered the Oracle Cluster File System (OCFS) when I needed to store Oracle database files – or indeed, any file! – as part of a clustered file system. OCFS (and its later improved counterpart, OCFS2) was initially offered for the Linux and Windows operating systems when a proprietary cluster file system wasn’t available or desirable. This enabled any Oracle RAC database’s instance to read from or write to the database’s control files, datafiles, and online redo logs on shared storage.

OCFS also allowed the RAC database’s Oracle Cluster Registry (OCR) files and its Voting Disks to be stored and maintained within a clustered file system. In Oracle 10gR2, this requirement was lifted and it was possible to store the OCR files and the Voting Disks on either raw devices or raw block devices. If you’ve ever lost all copies of these files when they’ve been stored on raw devices, you know what a chore it is to recover from their loss. Therefore, Oracle 11gR2 no longer supports storing these files on raw devices.

To increase the viability of these mission-critical files, Oracle 11gR2 formally introduces a new clustered file system, the ASM Clustered File System (ACFS). ACFS is obviously intended for better protection of OCR files and Voting Disks in a RAC environment. For example, ACFS now allows the creation of up to five (5) copies of the OCR file for safe retention of the crucial information stored within them. (Prior releases only permitted two copies, a primary OCR and a mirror OCR.) But ACFS isn’t intended for RAC environments alone; there are plenty of files that we’ve normally thought of as under the purview of the host server’s operating system – for example, external LOBs that are referenced by the BFILE datatype, the source files for Oracle external tables, or even an Oracle home’s binary executable files - that can benefit from ACFS’s security and file sharing features:

Dynamic Volume Manager. Oracle 11gR2 provides the new ASM Dynamic Volume Manager (ADVM) to configure, control and maintain files stored within an ACFS file system. With ADVM I can construct ADVM volume devices within an ASM disk group, manage the files stored within ADVM volume devices, and even resize an ADVM volume device as the need for additional space arises. Best of all, since ADVM volumes are built on top of the ASM file system architecture, I can ensure that any files stored within those volumes are protected from unexpected data loss because of ASM’s RAID capabilities.

File Access Control. An ACFS directory or file can be granted read, write, or execute privileges at the appropriate level using either traditional Microsoft Windows-style access control lists (ACLs) or the UNIX / Linux user/group/other access permissions. ACFS file and directory security can be administered either graphically through Oracle 11gR2 Enterprise Manager or via the ASMCMD command-line utility.

File System Snapshot (FSS). Oracle 11gR2 now offers the ability to take snapshots of ACFS file systems through its new File System Snapshot (FSS) capabilities. A snapshot is a read-only version of the selected ACFS file system, and ACFS automatically retains up to 63 separate snapshots within the same ACFS. This feature is particularly useful when an ACFS file has been inadvertently updated, deleted or otherwise damaged. Either Oracle 11gR2 Enterprise Manager or the ACFS acfsutil command line interface can be used to locate the appropriate version of the file and restore it.

#4: Improvements to Software Installation and Patching

I’ve found that patching an Oracle database environment is just about the most stressful activity I’ve encountered as a DBA, and for an excellent reason: If the patching should introduce deleterious behavior into my databases, I’ve got a tremendous amount of work to do to ascertain if the issues I’m seeing are really the result of applying the patch. So I’m glad to see that Oracle 11gR2 continues to contribute to lowering my stress level with these new features.

Cluster Verification Utility Integration. The Cluster Verification Utility (CVU) that was introduced in Oracle 10g is now completely integrated into the Oracle Universal Installer and all other configuration assistants (e.g. DBCA and DBUA). Another nice feature is the implementation of “fix-it” scripts that can adjust many of the minor but crucial OS environment settings during creation and installation of a new Oracle 11gR2 Clusterware or database home.

Zero-Downtime Patching for Clusterware. When it’s time to patch an Oracle Clusterware home, Oracle 11gR2 applies the patches as an “out-of-place” upgrade. This means that the original version of the Oracle Clusterware home co-exists on the same server with the home that’s just been patched, but only one version of the Clusterware home is active at one time. In Oracle 11gR2, it’s therefore no longer necessary to shut down the entire Oracle cluster to complete the patching operation, which results in zero downtime when applying patches to clustered databases.

Say Goodbye to DST Patching Migraines. In the past few years, we’ve all had the joyful experience of having to patch our Oracle databases to ensure they handled the scheduled changes to Daylight Savings Time (DST). The good news here is that Oracle 11gR2 can now apply DST patches without requiring the “bounce” of even a single database instance.


The venerable DBMS_SCHEDULER package that revolutionized how DBAs schedule jobs and tasks in Oracle Database 10g gets some neat new and welcome features in Oracle Database 11gR2:

File Watcher. The ability to fire off a DBMS_SCHEDULER scheduled task in response to an event or raised condition was added in Oracle Release, as I illustrated in an earlier article on how to create Job Chains. However, there was no direct way to detect one of the most typical triggering events in batch processing: the arrival of an operating system file in a directory. Oracle Database 11gR2 alleviates this with the new File Watcher feature. Once the desired file has arrived within a specified directory, DBMS_SCHEDULER can now detect and register its arrival in new object type, SCHEDULER_FILEWATCHER_RESULT, that’s used to signal a DBMS_SCHEDULER job to start via the new CREATE_FILE_WATCHER procedure.

Built-In E-mail Notification. Whenever a DBMS_SCHEDULER scheduled task starts, fails, or completes, the task’s status can be signaled immediately by sending an e-mail to an appropriate destination address. Though I could implement this feature in earlier releases with a call to the appropriate procedure of either DBMS_MAIL or DBMS_SMTP, this functionality is now incorporated into every DBMS_SCHEDULER job and can be implemented with a few keystrokes.

Remote Jobs. DBMS_SCHEDULER now permits the DBA to create and schedule jobs on a remote database other than the current database. For example, I can now run either a PL/SQL anonymous block or invoke a stored procedure on production database PROD07 via a call from a scheduled job that I built via DBMS_SCHEDULER on production database PROD03 without having to create the scheduled task on database PROD07. This means I can now centralize scheduled task creation and maintenance on only one database.

Multiple Job Destinations. Finally, it’s now possible to schedule DBMS_SCHEDULER tasks that can be executed on one or more databases instances simultaneously. This is extremely useful in a RAC environment because I can take advantage of the power of multiple database instances to break down a long-running task into smaller tasks that can run in parallel across multiple nodes.

Next Steps

In the final article in this series, I’ll take a look at five more new features in Oracle Database 11gR2. Here’s a brief sample of the most intriguing new capabilities:

  • Significant database performance improvements, especially for parallelized operations
  • Upgrades to Recovery Manager, including automatic repair of corrupted blocks
  • Improvements to Data Guard features like Real Time Query
  • Large Object improvements, e.g., the ability to “watermark” LOBs
  • Database object sizing enhancements, including on-demand segment creation

References and Additional Reading

Before you proceed to experiment with any of these new features, I strongly suggest that you first look over the corresponding detailed Oracle documentation before trying them out for the first time. I’ve drawn upon the following Oracle Database 11g Release 2 documents for this article’s technical details:

E10881-02 Oracle Database 11gR2 New Features

E10592-03 Oracle Database 11gR2 SQL Language Reference

E10700-01 Oracle Database 11gR2 DataGuard Concepts and Administration

E10595-05 Oracle Database 11gR2 Administrator’s Guide

E10713-03 Oracle Database 11gR2 Concepts

E10820-02 Oracle Database 11gR2 Reference

E10500-02 Oracle Database 11gR2 Storage Administrator’s Guide

E10837-02 Oracle Database 11gR2 VLDB and Partitioning Guide

» See All Articles by Columnist Jim Czuprynski

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM