Oracle 11g Database New Features & Enhancements


Home Page

Oracle 11g TOP New Features for DBA

Oracle 11g Beta New Features[With Examples]

Oracle Database 11g PL/SQL New Features

Oracle 11g Focus Area WP [OTN]

Oracle 11g New Features[OTN]

Oracle 11g collection ( by Tonguc)

Oracle 11g TOP New Features for Developers

New Feature in 11g for .NET developers

The Best 11g New Features(BY TUSC Very Good PPT on 11g)

Oracle 11g Database Links

Sharing SQL by TOM

Oracle Database 11g Manageability Podcast(MP3)

Oracle 11g and changes in RDF support

Oracle Database 11g Download

Oracle Database 11g Documentation Library  

Yesterday there was Oracle Database 11g launch party , and after launch part published New Feature of Oracle Database 11g

You can check my site for Summary of Oracle 11g New Features [Click Here]

For  Oracle Database Tips & Tricks [ Click Here ]
Also published lots of Oracle Database 11g Focus Area Whitepapers
As a all other DBA , I am intersted in on following Oracle 11g Database new features
Check/Click here to see following New Feature of Oracle Database 11g


Oracle Openworld 2006, Oracle announced some exciting new features of Oracle 11g database and has 482 new features.  Just wait-n-watch to see all features, i.e once oracle release this product we will able to know all new feature. currently Beta test in underway for 11g  How ever you can see  and Burleson Consulting already booking order for New Feature Book/s and some of them have full TOC. So Check these sites you might get more new features.

Oracle Lunch party is over( on 11-JUL-07 New York), but product Oracle database 11g is not released. Most of the DBA where very much excited and ready to download 11g on 11-JUL-07 but....

I like DBA stuff, like RAC and DG( My favorite topics) , Performance, Backup & Recovery and SQL/PL-SQL new features. So writing/learing those Oracle database 11g new features only. I am also very much interested to write "Top 10 Oracle database 11g new features for DBA"[Click Here] ( Page Update on weekend only , When get little time to read :-) something new from below given source sites and It is intended for information purposes only)

Oracle Database 11g New Features Top 11g New Feature for DBAs

Oracle Database 11g Top New Features for DBAs

1) Automatic Diagnostic Repository{Click Here for Details] New

2) Database Replay [Click Here for details]

3) Automatic Memory Tuning {Click Here for details] 

4) Case sensitive password

5) Virtual columns and indexes [Click Here for details]

6) Interval Partition and System Partition

7) The Result Cache

8) ADDM RAC Enhancements

9) SQL Plan Management (SPM) and SQL Plan Baselines {Click Here pdf file]

10) SQL Access Advisor & Partition Advisor

11) SQL Performance Analyzer (SPA) [Click Here PDF] New

12) DBMS_STATS Enhancements

Oracle 11g Database DBA New Features 

  • Database Capture/replay database workloads :-           Allows the total database workload to be captured, transferred to a test database created from a backup or standby database, then replayed to test the affects of an upgrade or system change. Currently, they are working to a capture performance overhead of 5%, so you could conceivably capture real production workloads. Database Replay 
  • Automatic Memory Tuning:-                                          Automatic PGA tuning was introduced in Oracle 9i. Automatic SGA tuning was introduced in Oracle 10g. In 11g, all memory can be tuned automatically by setting one parameter. You literally tell Oracle how much memory it has and it determines how much to use for PGA, SGA and OS Processes. Maximum and minimum thresholds can be set.

    More details => 
  • Case sensitive password

    -- Init.ora parameter

    sec_case_sensitive_logon = (TRUE FALSE)

    -- On system level you can switch off

    alter system set sec_case_sensitive_logon = false

    - -Find users who have case sensitive or case insensitive


    -- Password file 
    orapwd file=orclpwd password=manager ignorecase=y 
  • Interval partitioning for tables : As per Robert

    "Wouldn't it be nice if you could just tell Oracle you wanted to partition every month and it would create the partitions for you? That is exactly what interval partitioning does. Here is an example:

        create table selling_stuff_daily
        ( prod_id number not null, cust_id number not null
        , sale_dt date not null, qty_sold number(3) not null
        , unit_sale_pr number(10,2) not null
        , total_sale_pr number(10,2) not null
        , total_disc number(10,2) not null)
        partition by range (sale_dt)
        interval (numtoyminterval(1,'MONTH'))
        ( partition p_before_1_jan_2007 values
        less than (to_date('01-01-2007','dd-mm-yyyy')));

        Note the interval keyword. This defines the interval that you want each partition to represent. In this case, Oracle will create the next partition for dates less than 02-01-2007 when the first record that belongs in that partition is created."

  • Faster DML triggers : Create a disabled trigger; specify trigger firing order
  • Improved NFS data file management
  • RMAN UNDO bypass : Rman backup can bypass undo. Undo tablespaces are getting huge, but contain lots of useless information. Now rman can bypass those types of tablespace. Great for exporting a tablespace from backup.:-  Source: Laurent
  • Virtual columns/indexes :-                                                User can create Virtual index on table. This Virtual index is not visible to optimizer, so it will not affect performance, Developer can user HINT and see is Index is useful or not.Invisible Indexesprevent premature use of newly created
    Check following link for more details
  • Fine grained access control for Utl_TCP: in 10g  all port are available, now it is controlled.
    Check following link for more details
  • Data Guard Enhancements , "Active Data Guard"
    1) Data Protection

        * Advanced Compression
        * Lost-write protection
        * Fast-Start Failover

    2) Increase ROI

        * Active Data Guard
        * Snapshot Standby

    3) High Availability

        * Faster Redo Apply
        * Faster failover & switchover
        * Automatic Failover using ASYNC

    Check following link for more details ,
  • New Trigger features
  • Partitioning by logical object and automated partition creation.
  • LOB's - New high-performance LOB features.
  • Incident Packaging
    To make oracle support better , oracle 11g introduce new feature / Service known IPS ( Incident Packaging Service ) . Basically it is a part/extension of Oracle 11g new feature ADR ( Automatic Diagnostic. ADR is new concept in oracle 11g and it is just a file based repository of diagnostic data. ADR gives lot flexibility to maintain / handle diagnostic data.

    When critical errors(i.e Problem) are detected, they automatically create an “incident” ( one occurrence of problem is Incident i.e relation between problem and incident is one to many).
    Information(trace ,dump ) related to the incident is automatically captured in file based repository known as ADR ( Automatic Diagnostic repository), all the incident related files are taged with Incident Number and certain health checks are run automatically. This information can be packaged to be sent to Oracle support

    Incident Packaging Service (IPS) wraps up all information about an incidentand allows you to send the whole package to Oracle Support.

    Here is steps to collect and send data to support using IPS

    $ adrci
    adrci> help ips
    adrci> show incident
    ( For example above command show incident No 9817 for ORA-600 [XYZ] )

    adrci> ips create package incident <> <= ( it will give package No.)
    adrci> ips create package incident 9817
    Created package 4 based on incident id 9817, correlation level typical

    adrci> ips add incident 9817 package 4
    Added incident 9817 to package 4


    adrci>>ips add file -
    /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log package 4

    Added file /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log to
    package 4

    adrci>> ips generate package 4 in /tmp
    Generated package 4 in file /tmp/, mode

    Send above zip file to Oracle Support :- Source
  • Feature Based Patching: All one-off patches will be classified as to which feature they affect. This allows you to easily identify which patches are necessary for the features you are using. EM will allow you to subscribe to a feature based patching service, so EM automatically scans for available patches for the features you are using. Source Tim
  • New Oracle11g Advisors
  • Table trigger firing order
  • Enhanced Index rebuild online : - Online index build with NO pause to DML. 
  • No recompilation of dependent objects:- When        

    A) Columns added to tables
    B)  Procedures added to packages
  • Improved optimizer statistics collection/  DBMS_STATS improvements

    • Published and Pending Statistics

      SQL> select name,value,isses_modifiable,issys_modifiable
      from v$parameter
      where name='optimizer_use_pending_statistics';


      NAME            VALUE           ISSES          ISSYS
      -----------------------------------             ----------       -----              ---------

      optimizer_use_pending_statistics        FALSE  TRUE IMMEDIATE
    • Expression Statistics
    • Multi Column Statistics

    • Improvement of AUTO sampling statistics gathering feature in Oracle 11g
    • Adaptive cursor sharing  to improve bind variable issue

      GET_PARAM , RESET_GLOBAL_PREFS_DEFAULTS and SET_PARAM are obsolete  in Oracle 11g.

      In place of above procedures need to use following procedures GET_PREFS ,  RESET_GLOBAL_PREF_DEFAULTS and SET_GLOBAL_PREFS

  • New default audit settings :- Oracle database where general database auditing was "off" by default, logging is intended to be enabled by default with the Oracle Database 11g beta secure configuration.  Notable performance improvements are planned to be introduced to reduce the performance degradation typically associated with auditing.
  • passwords are expected to also become case sensitive This and other changes should result in better protection against brute force attacks and password guessing scenarios.  For example, in addition to limiting the number of failed login attempts to 10 (default configuration in 10gR2), Oracle 11g beta’s planned default settings should expire passwords every 180 days, and limit to seven the number of times a user can login with an expired password before disabling access. Source
  •  Online index build with NO pause to DML
  •  Read only table :-    

    alter table t read only

     alter table t read write


Oracle 11g Database SQL/PL-SQL New Features

  • Fine Grained Dependency Tracking:- In 11g we track dependencies at the level of element within unit.                       •so we know that these changes have no consequence
    •I classified this as a transparent performance improvement
    •It’s certainly transparent!
    •Unnecessary recompilation certainly consumes CPU

        create table t(a number)

        create view v as select a from t

        alter table t add(Unheard_Of number)

        select status from User_Objectswhere Object_Name = 'V'
            - -----

        No recompilation of dependent objects when Columns added to         tables OR Procedures added to packages Source Tom Kyte

  • Named and Mixed Notation from SQL:- 

    select fun(P4=>10) from DUAL

    In 10g you can't call function in select statment by passing 4th parameter, but in 11g it is possible
  • PL/SQL "continue" keyword -  It is same as we read in c/c++ loop 
  • Support for “super” : It is same "super" in Java.
  • Powerfull Regular Expression:- Now we can access data between TAGS  like data between tags  <BOOK> .........</BOOK>

    The new built-in REGEXP_COUNT returns the number of times the pattern is matched in the input string.
  • New table Data Type "simple_integer" 
  •  SQL Performance Analyzer(SPA) :-It is same as Database replay except it not capture all transaction.The SQL Performance Analyzer (SPA) leverages existing Oracle Database 10g SQL tuning components. The SPA provides the ability to capture a specific SQL workload in a SQL Tuning Set, take a performance baseline before a major database or system change, make the desired change to the system, and then replay the SQL workload against the modified database or configuration. The before and after performance of the SQL workload can then be compared with just a few clicks of the mouse. The DBA only needs to isolate any SQL statements that are now performing poorly and tune them via the SQL Tuning Advisor.  For more detail about SPA [click Here]
  •  Caching The Results with /*+ result_cache */ :- select /*+ result_cache */ * from my_table, New for Oracle 11g, the result_cache hint caches the result set of a select statement. This is similar to alter table table_name cache,but as you can adding predicates makes /*+ result_cache */ considerably more powerful by caching a subset of larger tables and common queries.

    select /*+ result_cache */ col1, col2, col3
    from my_table where colA = :B1      Source Tom Kyte PPT
  • The compound trigger :- A compound trigger lets you implement actions for each of the table DML timing points in a single trigger
  • PL/SQL unit  source can exceeds 32k characters
  • Easier to execute table DDL operations online:- Option to wait for active DML operations instead of aborting
  • Fast add column with default value:- Does not need to update all rows to default value.

Oracle 11g Database RAC and DataGuard New Features

  • Data Guard Load Balancing 
  • Oracle 11g RAC parallel upgrades :- Big WOW, In oracle 11g you need not to shutdown all CRS , in order to upgrade CRS.
  • ADDM for RAC Oracle 11g introduce new package DBMS_ADDM , which can be use generate report for all instance of RAC. DBMS_ADDM can be use is 3 diffrent mode
    1. Database Mode
    2. Instance Mode 
    3. Partial Mode 
    Click here to get more detail about DBMS_ADDM

Oracle 11g Database Performance Tuning New Features 

  • ADR (Automatic Diagnostic Repository)

    In Oracle 11g database New dimension fault diagnosability infrastructure added to Oracle self managing concept to reduce time for resolving problem/bug/SR and round trip between end-user and Oracle Support. Trace file, dump file , core file ADR, ADRCI etc are components of fault diagnosability infrastructure.


    In early version of oracle, When ever critical error such as code bug , data corruption occur end user need to collect diagnostic data such as process dump , data structure dump etc. In oracle 11g database end user need to worry about what kind of dump need to collect , need not to search trace file which need to send to Oracle Support Oracle 11g Database  proactively collect ,tagged and store diagnostic data in file based repository known as ADR. 11g used file based repository because even your database is down, you have diagnostic data to send to oracle support.

    Oracle 11g introduce new tool/utility called ADRCI known as ADR command line tool.
    This tool allow user to interact with ADR ,check alert log, check health monitor(HM) status ,

    Package incident and problem information into a zip file for send to Oracle Support. Etc.

    No username/password need to log in to ADRCI, ADRCI interact with file system and ADR data is secured only by operating system permissions on the ADR directories.

    adrci> set editor vi
    adrci> show alert ( it will open alert in vi editor )
    adrci> show alert -tail ( Similar to Unix tail command )
    adrci> show alert -tail 200 ( Similar to Unix Command tail -200 )
    adrci> show alert -tail -f ( Similar to Unix command tail -f )

    Too list all the "ORA-" error run following command

    adrci> show alert -P "MESSAGE_TEXT LIKE '%ORA-%'"

    In 11g alert file is saved in 2 location, one is in alert directory ( in XML format) and
    old style alert file in trace directory.

    Check following site for more info 

Oracle 11g Database Backup & Recovery New Features

  • Enhanced configuration of archive deletion policies
    Archive can be deleted , if it is not need DG , Streams Flashback etc
    When you CONFIGURE an archived log deletion policy applies to all archiving destinations, including the flash recovery area. BACKUP ... DELETE INPUT and DELETE... ARCHIVELOG use this configuration, as does the flash recovery area. When we back up the recovery area, RMAN can fail over to other archived redo log destinations if the flash recovery area is inaccessible. 
  • Configuring backup compression
    In 11g  can use CONFIGURE command to choose between the BZIP2 and ZLIB compression algorithms for RMAN backups.
  • Active Database Duplication
    Now DUPLICATE command is network aware i.e.we can create a duplicate or standby database over the network without taking backup or using old backup.
  • Parallel backup and restore for very large files
    RMAN Backups of large data files now use multiple parallel server processes to efficiently distribute theworkload for each file. This features improves the performance of backups.  
  • Improved block media recovery performance
    RECOVER command can recover individual data blocks.
    RMAN take  older, uncorrupted blocks from flashback and the RMAN can use these blocks, thereby speeding up block media recovery.
  • Fast incremental backups on physical standby database
    in 11g we can enable block change tracking on a physical standby database (ALTER DATABASE ENABLE/DISABLE BLOCK CHANGE TRACKING SQL statement).This new 11g  feature enables faster
    incremental backups on a physical standby database than in previous releases.because RMAN identifywe  the changed blocks sincethe last incremental backup.

More feature Coming soon  ......................

Some more link you can find of following links 

 40+ Links for Knowing the New Oracle Database 11g.