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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jul 26, 2007

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

By Jim Czuprynski

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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