Oracle 11g New Features for Administrators - …

1 Oracle 11g new features for administrators summary Sheets Version Editor: Ahmed Baraka Page 1 Oracle 11g new features for administrators - summary Sheets Usage Terms Anyone is can copy this document to any means of storage and present it in any format to any individual or organization for non-commercial purpose free. No individual or organization may use this document for commercial purpose without a written permission from the editor. There is no warranty of any type for the code or information presented in this document. The editor is not responsible for any loses or damage resulted from using the information or executing the code in this document. If any one wishes to correct a statement or a typing error or add a new piece of information, please send an email message to Version History Version Date Updates 23-Mar-2008 Initial document.

2 29-Apr-2008 - Mapping exam objectives to the document topics - Fixing caught bugs in the code examples. - Fixing caught errata and mistakes in some statements. - Adding new topics. 12-Sep-2008 - Further explanation on using the new PIVOT operator. - Adding minor details in some sections. 10-Oct-2008 - Adding the section "Adaptive Cursor Sharing" - Adding minor details in some sections. - Fixing caught errata and mistakes in some statements. 01-Mar-2010 - Adding minor details in some sections. 23-Dec-2010 - Adding Oracle 11g Release 2 New Features - Removing the statement "that virtual column cannot be a part of partitioning key column". Thanks to Arshad Taqvi. 22-Jan-2010 - Removing the information about FIXED_DATE parameter as it is not a new feature .

3 Thanks to Bhalla Ravinder. 3-Feb-2010 Fixing the following statements: - CONNECT role has only CREATE SESSION privilege: this starts from 10g R2. - "Restoring Previous Versions of Statistics" is a 10g feature . It was not removed though to maintain the informative value of the document. Thanks to Bhalla Ravinder. 11-Jun-2012 - info about DBMS_HPROF added - Correction made: Difference between PLS_INTEGER and SIMPLE_INTEGER corrected. Thanks to Saurabh Gupta. - Correction made: Results of the flashback queries are not cached in Server Result Cache. Thanks to Mark J. Cippel. - Correction made: slight correction in the code demonstrating Subprogram Inlining. Thanks for Ibrahim. Document Purpose This document aims at briefly explaining Oracle Database 11g New Features with concentration on the practical code.

4 It discusses new Features related to database administration and application development. The document is edited so that it concentrates on the following: Brief description of the concepts. This includes: o New terms and definitions. o Significant advantages and/or disadvantages Page 2 Oracle 11g new features for administrators - summary Sheets o Concept limitations and precautions Code examples On the other hand, the document avoids the following: Detailed explanation of the concepts Details of using Oracle Grid Control in implementing the concepts. What is not covered? The document discussed new Features in Oracle 11g in only the topics as stated in the contents section. New Features introduced in other areas are not covered. To mention some of them, this document does not cover the new Features in the following areas: Oracle Streams Data Guard Oracle RAC Oracle XML DB Prerequisite To be able to get advantage of reading this document, you must have solid knowledge on Oracle database 10g administration.

5 Terms and Acronyms Used Following are list of the terms and acronyms used in this document: Term / Acronym Meaning Oracle 11g Whenever Oracle 11g is mentioned in this document, it is meant to be Oracle Database 11g. ADR Automatic Diagnostic Repository Oracle Grid Control Oracle Grid Control, Database Control or Oracle Enterprise Manager (OEM) are used interchangeably in this document. Conventions in the Document When you are asked to click on several links in the Enterprise Manager, the term "follow the links" is used. For example, consider the following statement: To manage created SQL patches, follow the links Database Home page> Server tab> Query Optimizer section> SQL Plan Control> SQL Patch tab This statement means, in the Database Home page you click Server tab where you will see Query Optimizer section.

6 Then you click on SQL Plan Control and then on the SQL Patch tab. Resources S/N Resource Type Resource Name 1 Book Oracle Database 11g New Features for DBAs and Developers, by Sam R. Alapati and Charles Kim, Apress, ISBN: 978-1-59059-910-5 2 Book Oracle Database 11g New Features by Rober G. Freeman, Oracle Press 3 Oracle Official Documentation Oracle Database New Features Guide 11g Release 2 ( ) E17128-03 Oracle Database New Features Guide 11g Release 1 ( ) B28279-02 Oracle Database administrator 's Guide 11g Release 1 ( ) B28310-03 Oracle Database Performance Tuning Guide 11g Release 1 ( ) B28274-01 Page 3 Oracle 11g new features for administrators - summary Sheets Oracle Database Backup and Recovery User's Guide 11g Release 1 ( ) B28270-02 Oracle Database PL/SQL Packages and Types Reference 11g Release 1 ( ) B28419-02 Oracle Database High Availability Overview 11g Release 1 ( ) B28281-01 Oracle Database Storage administrator 's Guide 11g Release 1 ( )

7 B31107-03 Oracle Database PL/SQL Language Reference 11g Release 1 ( ) B28370-02 Oracle Database VLDB and Partitioning Guide 11g Release 1 ( ) B32024-01 Oracle Database SecureFiles and Large Objects Developer's Guide 11g Release 1 ( ) B28393-02 4 Oracle Metalink The following documents were referenced: Title: 11g New Features : Top 5 Features In 11g Title: 11g DBCA New Features / Enhancements Title: Using the workload capture and replay in 11G Title: 11g feature : Flashback Data Archive Guide Title: New Background Processes In 11g 11g New feature : Transparent Data Encryption at Tablespace Level 5 Web Site 6 Web Site 7 Web Site 8 Web Site 9 Web Site 10 Web Site 11 Articles by Arup Nanda 12 Blog Site 13 Blog Site 14 Blog Site 15 Blog Site Page 4 Oracle 11g new features for administrators - summary Sheets Contents Installation.

8 Database Upgrades and Change Management 9 Installation New Features Support _____9 Role and Privilege Changes _____9 Deprecated Components _____10 New Initialization Parameters Affecting Database Creation _____10 DBCA Enhancements _____11 Upgrading to Oracle Database 11g_____11 Database Replay_____13 The SQL Performance Analyzer _____17 Patching in Oracle Database Control _____21 Database Diagnosis and Repair _____ 22 Introducing Automatic Diagnostic Repository (ADR) _____22 Configuring the ADR _____22 Using adrci Tool _____22 Using The Support Workbench in the OEM_____24 Database Health Monitor _____25 Data Recovery Advisor _____26 SQL Test Case Builder _____27 Data Block Corruption Parameters _____28 Database Administration _____ 29 Automatic Memory Management _____29 Automatic Maintenance Tasks _____30 Oracle Flashback-Related New Features_____32 LogMiner Interface in Oracle Enterprise Manager_____32 Oracle Flashback Transaction Backout _____32 Flashback Data Archive _____33 Virtual Columns _____35 New Data Partitioning Schemes _____36 DDL Lock Timeout _____36 Explicit Locking of Tables _____36 Invisible Indexes _____36 Read-Only Tables _____37 Deferred Segment Creation _____37 Shrinking Temporary Tablespaces and

9 Tempfiles_____37 Creating an Initialization Parameter File from Memory _____38 Restore Point Enhancements _____38 Database Resident Connection Pooling_____38 Comparing and Synchronizing Database Objects _____40 Page 5 Oracle 11g new features for administrators - summary Sheets SQL*Plus New Features _____41 Online Application Maintenance _____42 Oracle Advanced Compression Option _____42 Oracle Scheduler New Features _____43 Lightweight Jobs _____44 Remote External Jobs _____44 Monitoring Job State with Email Notifications_____45 File Watcher _____47 Finer-grained Dependency Management _____50 Enhancements in Oracle Database Resource Manager _____50 Enhanced TRUNCATE Statement _____51 Dropping Unused Object Storage _____52 Performance Tuning _____ 53 PL/SQL Native Compilation_____53 Server Result Cache _____54 Client Side Result Cache_____56 Enhanced Oracle Process Monitoring _____57 Subprogram Inlining_____58 SQL Tuning Automation _____59 SQL Access Advisor Enhancements _____60 Changing Statistics Preferences _____63 Enhanced Statistics Maintenance _____64 SQL Plan Management _____67 ADDM New Features _____70 AWR New Features _____73 Setting Metric Thresholds for Baselines _____75 Performance-Related Changes in Database Control _____75 Miscellaneous New Performance Tuning Features _____75 Real-Time SQL Monitoring _____76 Adaptive Cursor Sharing_____77 Database Security _____ 78 Stronger Password Hash Algorithm _____78 Security Out of the Box _____78 Anti Network Attacks Parameters _____79 Tablespace Encryption

10 _____80 Fine-Grained Access Control for UTL_* Packages _____81 Further Security New Features _____82 Backup and Recovery New Features ____ 84 Enhanced Block Media Recovery _____84 RMAN Substitution Variables _____84 Page 6 Oracle 11g new features for administrators - summary Sheets New RMAN Configuration Parameters_____85 The Multisection Backups _____85 Creating Archival Backups _____86 VALIDATE Command _____86 Configuring an Archived Redo Log Deletion Policy _____87 Active Database Duplication _____87 Importing and Moving Recovery Catalogs _____89 Virtual Private Catalogs _____90 Miscellaneous New Features in RMAN_____91 Data Pump Utilities _____ 92 Compression Enhancement _____92 Encryption Enhancements _____92 Reusing a Dump File_____93 Remapping Data _____93 Renaming Tables During Export or Import _____93 Data Pump and Partitioned Tables _____93 Ignoring Nondeferred Constraints _____94 External Tables Based on Data Pump Driver _____94 Enhancement in the Transportable Parameter _____94 Automatic Storage Management (ASM)