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

Synopsis. Oracle Database 11g Release 1 (11gR1)
was launched in mid-July 2007. This article – the second in this two-part
series – takes some tantalizing glimpses into the upcoming improvements to
database performance and database management that expand even further the
revolutionary concepts introduced in Oracle Database 10g.

In the prior
article
in this series, I focused on five impressive and obvious features
in Oracle Database 11g that are sure to change the way that DBAs gather data,
tune SQL, perform unit and system testing, and handle complex
transaction-level recovery
. However, there are equally impressive features
lurking just beneath the surface of Oracle Database 11g that demand attention
because they provide increased database security, improved data
storage
capabilities, and enhanced disaster recovery. So, here’s the
second half of my personal “top ten list” of Oracle Database 11g new features.
Please be sure to visit this article and Oracle 11g Central periodically
as I dive more deeply into these features over the next several months.

#6: SecureFiles

Oracle Database 11g provides a series of brand-new methods
for storing large binary objects (also known as LOBs) inside the
database. These new features, collectively called SecureFiles, will
allow Oracle Database 11g to store images, extremely large text objects, and
the more advanced datatypes introduced in prior Oracle releases (e.g. XMLType,
Spatial, and medical imaging objects that utilize the DICOM (Digital Imaging
and Communications In Medicine) format).

SecureFiles promises to offer performance that compares
favorably with file system storage of these object types, as well as the
ability to transparently compress and “deduplicate” these data. (Deduplication
is yet another brand-new feature in Oracle Database 11g. It can detect
identical LOB data in the same LOB column that’s referenced in two or more
rows, and then stores just one copy of that data, thus reducing the
amount of space required to store these LOBs.) Perhaps most importantly, Oracle
Database 11g will also insure that these data can be encrypted using
Transparent Data Encryption (TDE) methods – especially important (and welcome!)
in the current security-conscious environments we inhabit today as database
administrators.

#7: Improved Database Security

Oracle Database 10gR2 dramatically improved the options for
encrypting sensitive data both within Oracle database tables and
indexes, as well as outside the database (i.e. RMAN backups and DataPump export
files) with Transparent Data Encryption (TDE). Oracle Database 11g
continues to expand the use of TDE within the database. For example, it’s now
possible to encrypt data at the tablespace level as well as the table and index
level. Also, logical standby databases can utilize TDE to protect data that’s
been transferred from its corresponding primary standby database site. Moreover,
secured storage of the TDE master encryption key is insured by allowing it to
be stored externally from the database server in a separate Hardware Security
Module.

Secure By Default. Oracle Database 11g also
implements a new set of out-of-the-box security enhancements that are
collectively called Secure By Default. These security settings can be
enabled during database creation via the Database Configuration Assistant
(DBCA), or they can be enabled later after the database has been created.
Here’s a sample of these new security features:

  • Every user account password is now checked automatically to
    ensure sufficient password complexity is being used.
  • To further strengthen password security, the DEFAULT user
    profile now sets standard values for password grace time, life time, and lock
    time, as well as for the maximum number of failed login attempts.
  • Auditing will be turned on by default for over twenty of the most
    sensitive DBA activities (e.g. CREATE
    ANY PROCEDURE, GRANT ANY PRIVILEGE, DROP USER
    , and so forth).
    Also, the AUDIT_TRAIL
    parameter is set to DB by default when the database is created, so this means
    that a database “bounce” will no longer be required to activate auditing.
  • Fine-Grained Access Control (FGAC) is now available for
    network callouts when using raw TCP (e.g. via the UTL_TCP package), FGAC will be able to construct Access
    Control Lists
    (ACLs) to provide fine-grained access to external network
    services for specific Oracle Database 11g database user accounts.
  • Enterprise Manager now provides interfaces for direct management
    of the External Security Module (ESM), Fine-Grained Auditing
    (FGA) policies, and Row-Level Security (RLS) policies.
  • Finally, an RMAN recovery catalog can now be secured via Virtual
    Private Catalog
    to prevent unauthorized users from viewing backups that are
    registered within the catalog.

#8: Partitioning Upgrades

Oracle Database 10g made a few important improvements to
partitioned tables and indexes (e.g. hash-partitioned global indexes), but
Oracle Database 11g dramatically expands the scope of partitioning with several
new composite partitioning options: Range Within Range, List Within Range,
List Within Hash,
and List Within List. And that’s not all:

  • Interval Partitioning. One of the more intriguing new
    partitioning options, interval partitioning is a special version of
    range partitioning that requires the partition key be limited to a single
    column with a datatype of either NUMBER or DATE. Range partitions of a fixed duration can be
    specified just like in a regular range partition table based on this partition
    key. However, the table can also be partitioned dynamically based on
    which date values fall into a calculated interval (e.g. month, week, quarter,
    or even year). This enables Oracle Database 11g to create future new partitions
    automatically based on the interval specified without any future DBA
    intervention.
  • Partitioning On Virtual Columns. The concept of a virtual
    column
    – a column whose value is simply the result of an expression, but
    which is not stored physically in the database – is a powerful new construct in
    Oracle Database 11g. It’s now possible to partition a table based on a virtual
    column value, and this leads to enormous flexibility when creating a
    partitioned table. For example, it’s no longer necessary to store the date
    value that represents the starting week date for a table that is
    range-partitioned on week number; the value of week number can be simply
    calculated as a virtual column instead.
  • Partitioning By Reference. Another welcome partitioning
    enhancement is the ability to partition a table that contains only detail transactions
    based on those detail transactions’ relationships to entries in another
    partitioned table that contains only master transactions. The relationship
    between a set of invoice line items (detail entries) that corresponds directly
    to a single invoice (the master entry) is a typical business example. Oracle
    Database 11g will automatically place the detail table’s data into appropriate
    subpartitions based on the foreign key constraint that establishes and enforces
    the relationship between master and detail rows in the two tables. This
    eliminates the need to explicitly establish different partitions for both
    tables because the partitioning in the master table drives the partitioning of
    the detail table.
  • Transportable Partitions. Finally, Oracle Database 11g
    makes it possible to transport a partitioned table’s individual partitions
    between a source and a target database. This means it’s now possible to create
    a tablespace version of one or more selected partitions of a partitioned table,
    thus archiving that partitioned portion of the table to another database
    server.

#9: ASM Enhancements

Oracle Database 10g introduced Automatic Storage Management
(ASM) that at its essence is a file system specifically developed for Oracle
database files. (See my article series on
ASM for more details.) Oracle Database 11g expands the reach of ASM with
several new features, including:

  • SYSASM Role. A new role, SYSASM, has been created so that
    ASM instances can be managed separately from the roles typically granted for
    traditional Oracle database instance management.
  • ASM Rolling Upgrades. One of the most popular and sensible
    uses of ASM is in a Real Applications Cluster (RAC) environment. Oracle
    Database 10g made it possible to initiate a rolling patch set upgrade to the
    software in the Oracle database home on each node in the cluster. This insures
    that the clustered database remains accessible at all times because at least
    one Oracle database instance is active while the patch set is applied to the
    other node(s). The good news is that Oracle Database 11g now extends this
    concept to ASM instances in a RAC clustered environment.
  • Fast Mirror Resynchronization. An ASM disk group that’s
    mirrored using ASM two-way or three-way mirroring could lose an ASM disk due to
    a transient failure (e.g., failure of a Host Bus Adapter, SCSI cable, or
    disk I/O controller). Should this occur, ASM will now utilize the Fast
    Mirror Resynchronization
    feature to quickly resynchronize only the extents
    that were affected by the temporary outage when the disk is repaired, thus
    reducing the time it takes to restore the redundancy of the mirrored ASM disk
    group.
  • Preferred Mirror Read. An ASM disk group that’s mirrored
    using ASM two-way or three-way mirroring requires the configuration of failure
    groups.
    (A failure group defines the set of disks across which ASM will
    mirror allocation units; this insures that the loss of any disk(s) in the
    failure group doesn’t cause data loss.) In Oracle Database 11g, it’s now possible
    to inform ASM that it’s acceptable to read from the nearest secondary extent
    (i.e. the extent that’s really supporting the mirroring of the ASM allocation
    unit) if that extent is actually closer to the node that’s accessing the
    extent. This feature is most useful in a Real Application Clusters (RAC)
    database environment, especially when the primary mirrored extent is not local
    to the node that’s attempting to access the extent.
  • Resizable Allocation Unit. Oracle Database 11g now permits
    an ASM allocation unit to be sized at either 2, 4, 8, 16, 32, or 64 MB when an
    ASM disk group is first created. This means that larger sequential I/O is now
    possible for very large tablespaces, and/or tablespaces with larger block
    sizes. The extent size is now automatically increased as necessary and this
    allows an ASM file to grow up to the maximum of 128 TB as supported by Bigfile
    Tablespaces (BFTs).
  • Improved ASMCMD Command Set. ASMCMD now includes several
    new commands that increase visibility of ASM disk group information, support
    faster restoration of damaged blocks, and retain and restore complex metadata
    about disk groups:
    • A system / storage administrator can execute the lsdsk
      command to view a list of all ASM disks even if an ASM instance is not
      currently running
      .
    • The remap command utilizes the existing backup of a
      damaged block on an ASM-mirrored disk group to recover the damaged block
      to an alternate location elsewhere in the ASM disk group.
    • Commands md_backup and md_restore allow a
      DBA to back up and restore, respectively, the metadata reflecting the
      exact structure of an ASM disk group. These new commands are an immense
      boon because the recreation of extremely large disk groups consisting of
      several dozen mount points can be tedious, time-consuming, and prone to error.

#10: DataGuard Enhancements

Last but most certainly not least, Oracle Database 11g adds
plenty of enhancements to its flagship high-availability solution for site
survivability, DataGuard:

  • Snapshot Standby Database. Prior versions of Oracle
    Database supported two types of standby databases: the physical standby,
    which is an exact duplicate of the primary database and is updated via direct
    application of archived redo logs; and the logical standby, which
    contains the same logical information as the primary database, but whose
    data is organized and/or structured differently than on the primary database
    and which is updated via SQL Apply. Oracle Database 11g adds a third standby
    database type, the snapshot standby database, that’s created by
    converting an existing physical standby database to this format. A snapshot
    standby database still accepts redo information from its primary, but unlike
    the first two standby types, it does not apply the redo to the database
    immediately;
    instead, the redo is only applied when the snapshot standby
    database is reconverted back into a physical standby. This means that
    the DBA could convert an existing physical standby database to a snapshot
    standby for testing purposes, allow developers or QA personnel to make changes
    to the snapshot standby, and then roll back those data created during testing
    and immediately reapply the valid production redo data, thus reverting the
    snapshot standby to a physical standby again.
  • Rolling Database Upgrades Support Physical Standby Databases.
    Oracle Database 10g introduced the ability to utilize SQL Apply to perform rolling
    upgrades
    against a primary database and its logical standby database.
    During a rolling upgrade, the DBA first upgrades the logical standby database
    to the latest database version, and then performs a switchover to make the
    standby database the primary and vice versa. The original primary database is
    then upgraded to the new database version, and a switchover reverses the roles
    once again. This insures that the only interruption to database access is the
    time it takes to perform the switchovers. The good news is that Oracle Database
    11g now allows a rolling database upgrade to be performed on a physical
    standby database by allowing the physical standby to be converted into a logical
    standby database before the upgrade begins. After the rolling upgrade is
    completed, the upgraded logical standby is simply reconverted back into a
    physical standby.
  • Real-Time Query Capability. DataGuard will now allow the
    execution of real-time queries against a physical standby database, even
    while the physical standby continues to receive and apply redo transactions via
    Redo Apply. (In prior releases, the physical standby could only be accessed for
    reporting if it was opened in read-only mode while the application of redo was
    suspended.) This means that a physical standby database can be utilized more
    flexibly for read-only reporting purposes; also, the considerable resources
    needed to create and maintain the standby environment may now be put to much
    more effective use.
  • Expanded DataType and Security Support. Oracle Database
    11g now supports XMLType data stored in CLOB datatypes on logical
    standby databases. In addition, Transparent Data Encryption (TDE) can
    now support encrypted table data as well as encrypted tablespaces, and Virtual
    Private Database
    (VPD) is supported for logical standby databases
  • Heterogeneous DataGuard. Finally, it’s now possible to set
    up the primary database site using one operating system (e.g. Oracle Enterprise
    Linux 4.4) while using another operating system (e.g. Windows 2003 Server) for
    the standby database site.

Conclusion

Oracle Database 11g Release 1 continues to improve upon
the massive paradigm shift in Oracle Database 10g toward self-managed,
self-tuning, and self-healing databases. Though I believe I’ve thoroughly
reviewed what I consider to be the major features of this massive new release,
I’m sure there are areas I’ve yet to probe. These automatic database management
features will be especially valuable to IT organizations that continue to
struggle with ever-larger databases and ever-increasing computing workloads
while attempting to answer the demand for lowered costs and value-added
service.

References and Additional Reading

Even though I’ve hopefully provided enough technical
information in this article to encourage you to explore with these features, I
also strongly suggest that you first review the corresponding detailed Oracle
documentation before proceeding with any experiments. Actual implementation of
these features should commence only after a crystal-clear understanding exists.
Please note that I’ve drawn upon the following Oracle Database 11gR1
documentation for the deeper technical details of this article:

B28279-01 Oracle Database
11gR1 New Features

B28286-01 Oracle Database
11gR1 SQL Language Reference

B28294-01 Oracle Database
11gR1 DataGuard Concepts and Administration

B28310-01 Oracle Database
11gR1 Administrator’s Guide

B28318-01 Oracle Database
11gR1 Concepts

B28320-01 Oracle Database
11gR1 Reference

B31107-01 Oracle Database
11gR1 Storage Administrator’s Guide

B32024-01 Oracle Database
11gR1 VLDB and Partitioning Guide

»


See All Articles by Columnist
Jim Czuprynski

Jim Czuprynski
Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Latest Articles