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
