Oracle 11gR1

Oracle 11g is a version of the Oracle database. As for 10g, the g stands for "grid" to indicate that 11g is "grid-computing ready". The common theme for this release is "Growing the Grid" (focus on Fusion Middleware, RAC and ASM).

Also see Oracle 9i, Oracle 10g and Oracle 12c..

History and Support Status

Oracle Corporation started beta testing Oracle database 11g in September 2006 and announced the new release on 11 July 2007 in New York City (Equitable Auditorium, 787 Seventh Avenue, NY-10019).

The first release of Oracle 11g (Oracle 11g Release 1) was released for Linux on 9 August 2007. The Windows port was released on 23 October. Unix versions (Solaris, AIX and HP-UX) were released on 12 November.

Base release: 11.1.0.6.0

Latest patch-set: 11.1.0.7.0 (announced at the 2008 Oracle OpenWorld)

Premier Support Ends: Aug 2012

Extended Support Ends: Aug 2015

New options

Database options are not part of the "free" upgrade, but will require additional licenses. The new options in 11g are:

• Oracle Total Recall with Flashback Data Archive
• Oracle Advanced Compression
• Real Application Testing (RAT)

New features

Apparently Oracle added about 482 new features in the Oracle Database 11g version. Some of the new features:

New data types

The new data types available in Oracle 11g are:

• Binary XML type - up to 15 x faster over XML LOBs.
• DICOM Medical Images.
• 3D Spatial Support.
• RFID tag data types.

SQL

• Automatic SQL tuning with self-learning capabilities.
• Tables can have Virtual columns (calculated from other columns).
• Virtual Columns Indexes on VC and Partitioning on VC
• Fast "alter table ... add column" with default values.
• Online rebuilding of indexes with no pause on DML activity.
• Ability to mark a table as "read only". See Read Only Tables.
• New PIVOT and UNPIVOT operations

PL/SQL

• Native Compilation no longer requires a C-compiler.
• New "SIMPLE_INTEGER" data type - always NOT NULL, wraps instead of overflows and is faster than PLS_INTEGER.
• SQL and PL/SQL result caching (in SGA)
• Can specify trigger firing order (FOLLOWS-clause)
• Compound triggers - a trigger can be before, after, row and statement all in one
• New CONTINUE statement - starts the next iteration of the loop
• DML triggers are up to 25% faster - in particular, row level triggers doing updates against other tables
• Finer grained dependency tracking
• Dynamic SQL enhancements
• Ability to reference sequences (no need to select seq.nextval into :var from dual)

ASM

• Support for rolling upgrades
• Automatic bad block detection and repair
• Fast mirror resync after temporary connectivity lost

Information Life-cycle Management

Apparently the volume of data is tripling every two years. The cost of storage can be significantly reduced by using compression and cheaper storage for less active data (partitioning).

Partitioning

• Partition advisor - figure out what partitions to create
• Automated partitioning by interval (new partitions are added automatically)
• Automated reference partitioning by Parent/Child reference (as partitions are created, partitions are created in tables that reference them)
• Partitioning by virtual columns
• New composite partition types: Range/Range, List/Range, List/Hash, and List/List
• Support for transportable partitions - for moving partitions between different operating systems

Compression

• Support compression on INSERT, UPDATE and DELETE operations. 10g only supported compression for bulk data-loading operations.
• Advanced compression allows for an 2-3 x compression rate of structured and unstructured data.
• Datapump export dump files can be compressed

Performance improvements

• RAC - 70% faster (ADDM has a better global view of the RAC cluster)
• Streams - 30-50% faster
• Optimizer stats collection - 10x faster
• Query results caching - 25% faster
• OLAP-based Materialized Views for fast OLAP CUBE building
• Result caches - new memory areas in the SGA for storing SQL query results, PL/SQL function results and OCI call results.
• Invisible indexes - indexes that are ignored by the optimizer. Handy for testing without dropping.
• Oracle secure files - 5x faster than normal file systems.
• No need to have a C-compiler installed to use Native PL/SQL Compilation.

Availability improvements

• Ability to apply many patches on-line without downtime (RAC and single instance databases)
• Data recovery advisor - quickly identify the root cause of failures; auto fix or present recovery options to the DBA
• ASM Preferred Mirror Read/ Faster Mirror Resync
• XA transactions spanning multiple servers
• Improved runtime connection load balancing
• Flashback Transaction/ Oracle Total Recall

Security improvements

• Support for case sensitive and multi-byte passwords (disable by setting SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE). See Case sensitive passwords.
• Transparent Data Encryption - support for tablespace level encryption
• Hardware based master key protection
• Encrypt backups
• Kerberos authentication - strong passwords
• Add Multi-factor DBA controls with Data Vault

Manageability improvements

• New MEMORY_TARGET parameter. See Automatic memory tuning

Data Guard improvements

• Standby databases can now simultaneously be in read and recovery mode - so use it for running reports 24x7.
• Online upgrades: Test on standby and "roll" to production
• Snapshot standby (create test databases)
• Incremental Backup on Physical Readable Physical Standby
• Offload: Complete database and fast incremental backups
• Logical standby databases now supports XML and CLOB datatypes as well as transparent data encryption

Oracle SecureFiles

SecureFiles (known as "FastFiles" in the 11g beta release) provide faster access to unstructured data than normal file systems. For example, write access to SecureFiles is faster than a standard Linux file system, while read access is about the same. In addition, it provides compression, encryption and data deduplication.

Real Application Testing

Real Application Testing or RAT will make it easier to do upgrades, hardware replacements and operating system changes. RAT consists of two components:

• Database Replay - capture production workload and replay on different (test) environment.
• SQL Performance Analyzer - identifies SQL execution plan changes and performance regressions

Other features

• Online application upgrades and "hot" patching (Hot patching is described in educational and marketing materials, but no actual hot patches exist, to date.)
• Online table and index redefinition
• Improved data compression
• "duality" between SQL and XML - users can embed XML within PL/SQL and vice versa
• New binary XML data type, a new XML index & better XQuery support
• Automated capture of fault diagnostics for faster fault resolution (see ADR)
• Repair advisers to guide DBAs through the fault diagnosis and resolution process
• SQL Developer is installed with the database server software (all editions). The Windows SQL*Plus GUI is deprecated.
• APEX shipped with the DB

Desupported features

The following features are desupported in 11g:

• Oracle export utility (exp). Imp is still supported for backwards compatibility.
• iSQLPlus not shipped anymore. Use SQL Developer instead.

Upgrade path

Oracle database versions at or above 9.2.0.4 can be upgraded directly to Oracle 11g. Versions previous to 9.2.0.4 must first be upgraded to Oracle 9i or 10g before they can be upgraded to Oracle 11g.

External links

• Oracle Database 11g Launch Webcast
• Oracle 11g Manuals
• Oracle Database 11g Technical Information
• Arup Nanda's Top New Features for DBAs and Developers
• Oracle Database 11g New Features Guide
• Oracle New Features Blog