This chapter contains descriptions of all of the features that are new to Oracle Database 10g Release 2 (10.2). This chapter contains the following sections:
The following sections describe Application Development features for Oracle Database 10g Release 2.
These features provide API and functionality improvements to enable ISVs and developers to build more powerful applications with higher levels of availability.
This support enables ODBC applications to run on the Linux platform, encouraging wider Oracle Database adoption by allowing Linux clients to access Oracle databases.
This allows applications to be compiled for multiple versions of the Oracle client from a single source code environment. This leads to easier development of products with customizations based on versions.
This enables ISVs and testers to start up and shut down databases without using SQL*Plus or other tools. This streamlines the testing process for application developers.
Oracle Call Interface Programmer's Guide for details
This enables more robustness with TAF. If a server node goes down, applications automatically reconnect to another surviving node, replaying transactions if possible.
Oracle C++ Call Interface Programmer's Guide for details
This feature allows Instant Client to be used in a wider variety of environments, especially where storage space constraints are prominent.
Oracle C++ Call Interface Programmer's Guide for details
The Instant Client SDK packages the OCI/OCCI header files necessary for developing Oracle client applications outside of an Oracle installation. Now, application developers can develop, test, and deploy Oracle client applications from an Instant Client deployment without the need for an ORACLE_HOME or full client installation.
Globalization and Unicode support have been enhanced in Oracle Database 10g Release 2. Consistent linguistic ordering support has been added for all SQL functions, enhancing the ability to write internationalized applications without regard to locale. The Globalization Development Kit (GDK) for PL/SQL provides new locale mapping tables and Japanese transliteration support. SQL Regular Expression support has been updated to support key Perl conventions used in the Life Science community.
This release also supports the latest version of Unicode 4.0. Identification of potential problems when migrating to another database character set has also been enhanced. The Character Set Scanner (CSSCAN) now offers selective scanning and provides support for new data types. The Language and Character Set Scanner (LCSSCAN) has improved detection quality of shorter text strings and now supports HTML files.
Unicode 4.0 is the latest release of the Unicode standard. Oracle's Unicode character sets, AL32UTF8 and AL16UTF16, have been updated to support Unicode 4.0 in Oracle Database 10g Release 2. Unicode 4.0 has 1,226 new characters.
This feature provides greater support of the Unicode character set for Oracle Database users.
This feature supports SQL string literal INSERT and UPDATE operations on NCHAR without data loss regardless of what the database character set happens to be. Multilingual Unicode data can now be added without restrictions such as having to provide hex Unicode values. The support for this feature is available in SQL, PL/SQL, OCI, and JDBC.
This feature prevents data loss when performing INSERT and UPDATE operations on NCHAR.
Among other things, the mappings allow for picking the most appropriate Oracle Linguistic sort when ordering different users' language data.
Two new parameters, QUERY and COLUMN, have been added in CSSCAN in Oracle Database 10g Release 2. These parameters offer finer control in performing selective analysis on the user data. Support for some remaining data types that CSSCAN does not handle has been added. They are multilevel VARRAY and multilevel nested tables.
The enhancements in CSSCAN provide more flexibility in defining the criteria for data scanning, which in turn helps to more accurately detect the effect of migrating the database to a new character set.
Oracle's Language and Character Set Detection (LCSD) is a high-performance, statistically-based technology for determining the language and the character set for unknown text in a file. It can automatically identify a wide variety of language and character set pairs. Introduced in Oracle Database 10g Release 1, LCSD is offered as part of the Migration Utilities and called the Language and Character Set File Scanner (LCSSCAN). LCSD is also offered as part of the Globalization Development Kit through Java API's.
This feature ensures all SQL string comparisons are consistent, and that they follow the linguistic convention as specified in the NLS_SORT parameter.
This feature provides a transliteration mechanism to support various Japanese Kana conversions in different forms. The TRANSLITERATE function for the UTL_I18N PL/SQL package is provided for transliteration in this release.
This feature performs Japanese Kana conversions.
Oracle Database 10g Release 1 brought new major application development and deployment capabilities to Java and Web Services developers. Oracle Database 10g Release 2 goes further in terms of JDBC standards support, dynamic load balancing of connections in Real Application Clusters (RAC) and GRID environments, and many performance improvements such as caching XA connections, and DML in the RETURNING clause.
This feature improves JDBC support for caching XAConnections on OracleXADataSources. Applications can now enable Implicit Connection Caching and Fast Connection Caching (in Real Application Clusters environments) on OracleXADataSources. When XAConnection caching is not enabled, applications obtain XAConnections directly from OracleXADataSource, and then obtain the associated XAResource objects.
This feature improves the performance of distributed transactions through the caching of XA connections in a single instance or multiple instance database environments.
This feature provides dynamic routing of connection requests to the least loaded instance. The JDBC connection cache manager uses Real Application Clusters (RAC) workload metrics and the specified load balancing policy to choose the best instance to process the connection request.
The benefit is efficient RAC and Grid database resource utilization by even distribution of the workload across RAC instances based on workload metrics and distribution policies.
This feature increases performance by saving a client-to-database round-trip.
By default, the database automatically closes cursors and result sets upon transaction commit. A holdable cursor, or holdable resultset, is not closed when the transaction that contains the cursor is committed thereby reducing hard parses and library latches. JDBC 3.0 ResultSet holdability is specified using the createStatement(), prepareStatement(), or prepareCall() methods and one of the following two constants: HOLD_CURSORS_OVER_COMMIT or CLOSE_CURSORS_AT_COMMIT. Oracle Database supports HOLD_CURSORS_OVER_COMMIT. Any other value passed as an argument is ignored.
Keeping cursors open when a transaction is committed increases the performance of JDBC applications or J2EE components.
This feature allows JDBC applications and J2EE components to retrieve the key (ROWID) of the newly inserted row during the same database operation (INSERT). As a result, a network round-trip to the database is saved and the performance is increased.
In this release, the Oracle HTML DB user interface has been enhanced and made more consistent to simplify development and deployment. Additional enhancements include:
The Create Application wizard has been simplified and the generated application made more feature rich.
The Chart wizard now provides additional chart types, including stacked bar, cluster bar, and dial charts.
Incorporating web services into your HTML DB application has been made easier through support for UDDI and additional wizards.
More than ten complete new themes have been added to the built-in themes repository.
A new Form wizard enables creation of a master detail form without coding.
Loading of text and spreadsheet data has been enhanced to support larger data sets and loading performance has been improved.
Tabular forms can now be easily extended to support selecting of rows with check boxes and adding of blank rows.
The new Cluster Bar charts provide a method of visualizing data in a side-by-side comparison series.
The new Dial chart provides a method of visualizing data commonly used in executive dashboards.
SVG charts now include a Percent Bar chart used to show cumulative percentages.
The addition of Stacked Bar charts allow visualizing of cumulative data in bar charts that was previously not possible.
Every SVG chart used in an HTML DB application can now automatically refresh itself at regular intervals based on the most up-to-date data.
Lost update detection
Developer-defined navigation through master rows with Next and Previous buttons
Editing of detail rows on a separate page or on a combined master detail form page
Single click adding of detail rows in tabular form
The Master Detail wizard automates the manual steps previously required to build a master detail form.
The Create Application wizard has been enhanced in two ways. First, it now enables you to create an application on spreadsheet data. Previously, importing of spreadsheet data had to be done in a separate step using the Data Workshop. This task has now been integrated into the wizard. Secondly, the generated application both on imported spreadsheet data and on an existing table has more features. Those features include:
Optional read-only version of the generated application
More analysis screens with summary reports and charts
The enhanced Create Application wizard simplifies the process of creating an application based on spreadsheet data and generates a more feature rich application.
The enhancements to the Web Services wizard allow you to incorporate web services in an application without any programming.
A Universal Description, Discovery, and Integration (UDDI) registry is a directory where businesses register their web services. Through UDDI, the new Web Services wizard can search for registered web services by name.
The UDDI feature of the new Web Services wizard simplifies the process of registering a web service for consumption by an Oracle HTML DB application.
The user interface for Oracle HTML DB has been streamlined to improve usability. Specifically, a large number of options have been removed or reorganized to eliminate confusion. Additionally, commonly used tasks such as Run Application or Edit Application have moved to improve consistency.
Oracle HTML DB's simplified and more consistent user interface makes HTML DB easier to navigate and reduces the number of clicks to create and edit applications and components.
PL/SQL in Oracle Database 10g Release 2 adds these new features:
Conditional compilation to allow automatic selection of source code according to the version of Oracle Database.
A new Web Gateway to allow the Oracle instance itself to respond directly to an HTTP request with a response generated by a stored procedure.
A package to support matrix math.
Conditional compilation enables PL/SQL programmers to use new language features in a PL/SQL compilation unit without sacrificing the ability to compile the unit in an older database version that does not support these features. Other typical uses include embedding conditionally compiled debugging and tracing support code in PL/SQL programs.
The benefit is that one body of source code can be deployed in a range of Oracle Database versions to take advantage of new features where they are available and to use fallback approaches where they are not. Debugging and tracing code can be left in place without penalizing the size of the executable or runtime efficiency. Such code can be easily activated by a simple recompilation. This provides a feature in PL/SQL that programmers who use other languages have come to expect.
Oracle Database PL/SQL Language Reference for details
The existing DBMS_DDL supplied package is enhanced with two new subprograms. The command-line WRAP utility takes a plain text CREATE or REPLACE command that specifies creation of a PL/SQL unit and returns a CREATE or REPLACE command where the text of the PL/SQL unit has been obfuscated. The obfuscated representation is identical to what is produced if the same CREATE or REPLACE command is processed with the command-line WRAP utility. The CREATE_WRAPPED command is a shortcut for and has the same semantics as the EXECUTE IMMEDIATE DBMS_DDL.WRAP (input) command.
This feature allows the text of a PL/SQL unit that is created dynamically to be obfuscated. This is critical when an installed product generates new PL/SQL units, for example as part of a customization process, and when the generated code embodies methods that the product vendor considers to be valuable intellectual property. The command-line WRAP utility, available in Oracle Database 10g Release 1 and earlier, allowed the code that a vendor shipped to be obfuscated. This new feature allows custom code generated by shipped vendor code to be obfuscated.
Oracle Database PL/SQL Language Reference for details
The declaration of the package type DBMS_OUTPUT.CHARARR is enhanced to support the increased maximum of 32,767 bytes. Previously, the VARCHAR2 constraint was 255 bytes. This determines the maximum line size that can be written by the DBMS_OUTPUT.PUT and DBMS_OUTPUT.PUT_LINE procedures. The procedure DBMS_OUTPUT.ENABLE (buffer_size integer defaults to 20000) has a new allowed value of NULL for the buffer_size. In this case, NULL means "unlimited." SQL*Plus, through the SET SERVEROUTPUT ON command, enables you to take advantage of the new "unlimited" meaning.
PL/SQL programmers frequently use DBMS_OUTPUT and, in Oracle Database 10g Release 1 and earlier, were constrained by the 255 byte limit. When using SQL*Plus, most programmers are regularly caught by the small default overall limit and sometimes by the current 1,000,000 maximum overall limit. In Release 2, the line length limit is increased to 32,767 bytes and the overall limit is removed altogether.
C and Fortran APIs for matrix math are freely available. Moreover, efficient platform-specific implementations have been written for many of the most popular platforms. The new UTL_NLA package exposes a matrix PL/SQL data type and wrapper PL/SQL subprograms for two of the most popular of these C APIs; namely BLAS and LAPACK.
To do matrix manipulation in PL/SQL in Oracle Database 10g Release 1 and earlier, it was necessary to invent a matrix representation based on PL/SQL native data types and then write matrix manipulation routines from scratch. This required substantial programming effort and the performance of the resulting implementation was limited. Oracle Database 10g Release 2, through the new UTL_NLA package, removes the programming effort and delivers a fast implementation.
SQL Language improvements in Oracle Database 10g Release 2 include:
Performance improvements for collection operators.
Added support for accessing remote LOBs.
Usability and performance enhancements to Expression Filter.
Perl compatibility in Regular Expression.
Rules Manager for Expression Filter can manage and evaluate an unlimited number of rules, provide efficient synchronization of multiple events and threads, and support a single management environment for rules and application data.
Rules Manager is a new feature of Oracle Database 10g Release 2. It enables developers to create applications that process and respond to events of any complexity using rules and policies defined in the database. It can evaluate events using data from the application and from database tables. It stores intermediate results to quickly evaluate the next event in a long running composite event (an event made up of two or more simple events). Rules are defined using XML and SQL and can have complex conditions using conjunctions and disjunctions, and specify a set of events, time, and non-occurrence of events with or without a deadline. Event policies control how rules are processed and the duration of an event. Rules can trigger actions that are user-defined procedures running inside an Oracle Database or actions in another application.
The benefit of this feature is that rules that are managed in Oracle Database keep pace with changing business conditions and are always up-to-date; rules are easily changed with SQL and are not included in your application or loaded into a memory-based rules repository. Rules can be evaluated efficiently with the complete business context stored in your Oracle Database and data provided by your application. Event response is flexible; rules can trigger actions in Oracle Database or your application.
Additional advantages of Oracle Database over other approaches include:
Manageability, by storing rules and event policies with your application data.
Performance, by evaluating rules and coordinating multiple events and application threads with the full capabilities of Oracle Database.
Scalability, by evaluating sets of rules of any size.
In Oracle Database 10g Release 2, Regular Expression is enhanced for Perl compatibility. Support for the common Perl REGEXP extensions that are not included and do not conflict with the POSIX standard are added.
Oracle's SQL Regular Expression provides a simple yet powerful mechanism for rapidly describing patterns and greatly simplifies the way in which you search, extract, format, and otherwise manipulate text in the database.
These enhancements have improved the performance for collection operators.
Distributed LOBs support provides easy-to-use and efficient support for accessing unstructured data in a distributed environment. The data interface for LOBs can now INSERT, UPDATE, and SELECT LOBs across dblinks.
The benefit of this new support is the ability to access remote LOBs.
Providing extensible optimizer support for the EVALUATE operator.
Allowing users to specify default values for a subset of attributes in an attribute set.
Allowing users to specify name spaces in stored XPath expressions.
Indexing XPath range predicates.
Allowing users to collect statistics on a set of XPath expressions stored in a table to create an optimal Expression Filter index structure.
Expression Filter features for Oracle Database 10g Release 2 include better usability and improved performance. You can specify default values for expression attributes and specify name spaces in XPath expressions.
New for this release is the Enterprise XML Developer's Kit, which brings full XML support for XMLType in Java, C, and C++. This enables developers to use existing XDK-based applications directly with the database thus improving performance.
Also included for Java is XSLT 2.0 support with XPath functions and operators, bringing new power and efficiency to style sheets.
Finally, application developers can incorporate XML support into applications using the JAXB compiler. Its customization feature enables modification of Java classes to speed development and reduce support costs.
This feature provides a native implementation of XQuery, the World Wide Web Consortium's (W3C) emerging standard for querying XML, integrated into Oracle Database. It introduces two new functions, XMLQUERY and XMLTABLE.
You can use the XQuery language from an SQL client to query Oracle XML DB repository documents, XML views over relational data, and XMLType data, whether persistent or generated.
XMLTABLE maps the result of an XQuery to relational rows and columns so that results can be queried using SQL.
The benefit of this feature is that Oracle now supports querying of XML, relational, object-relational and repository data using XQuery, the W3C emerging standard for querying XML.
Oracle Database 10g Release 2 extends the XPath Rewrite feature of XML DB to handle additional operations on more complex Schema Constructs. This feature reduces the number of conditions under which XPath Rewrite does not occur. The net effect is that applications using these constructs run faster without any recoding.
This feature allows faster querying and updating of schema-based XMLType data by extending the number of Schema Constructs and Xpath expressions that can be processed by XPath Rewrite.
The current UpdateXML() function can only manipulate the content of existing nodes within an XML document. It cannot be used to add or remove nodes from a document. The only way to add or remove nodes from a document is to use the methods provided by the DOM API.
This feature resolves this issue by introducing a family of SQL functions similar to UpdateXML() that make it possible to add and remove nodes from a document.
It also enables XPath Rewrite in the cases where the target of the function is a schema-based XMLType with structured storage. This means that operations can be executed in a more efficient manner than would be possible with the DOM API.
This feature improves programmer productivity by providing functions that make it possible to perform complex operations on the content of XML documents directly from SQL. Typically, these functions reduce the number of statements required to perform a particular operation when compared to typical DOM-based operations.
This new set of functions also improves application performance by allowing more complex manipulation of XML content to be performed by XPath Rewrite. XPath Rewrite makes it possible to perform operations on XML documents faster than can be achieved with conventional DOM or SAX-based programming techniques. It is now possible to do in one statement what previously took 10 to manipulate XML eliminating the need to use the DOM API to manipulate XML documents. These new functions improve application performance by allowing complex operations on XML documents to be performed using XPath Rewrite.
Developers can build C or C++ SOAP Services to consume and generate SOAP messages using the Oracle XML stack.
This feature extends Oracle's support for the SQL:2003 Standard, adding support for comments, processing instructions, and CDATA and XML prologues. This helps ensure that the SQL/XML operators become the defacto approach to generated XML from a relational query, eventually replacing both XSU and the DBMS_XMLGEN package.
SQL/XML provides an intuitive, standards-based method for creating XML directly from a SQL query. The SQL/XML specification is Part 14 of the SQL:2003 Standard. This feature extends Oracle's support for the SQL/XML operators to bring it in compliance with the SQL:2003 Standard.
SQL/XML lets you query XML using SQL in a standard way and defines ways of importing and storing XML data in a SQL database, manipulating it within the database, and publishing both XML and conventional SQL data in XML form.
Oracle Database 10g Release 2 includes support for all the functions in SQL/XML 2003 (for example, XMLSERIALIZE and XMLPARSE), and some Oracle-proposed extension functions.
The Java Architecture for XML Binding (JAXB) customization feature permits the XML-generated class names to be replaced with application or Java-specific names to ease manipulation of Java objects making development and maintenance more efficient and straightforward.
Application developers can more easily incorporate XML support into applications using JAXB. The customization feature enables modification of Java classes to speed development and reduce support costs.
This feature completes the work required to make it possible to deprecate the XDK PL/SQL packages and replace them with synonyms equivalent to Oracle XML DB PL/SQL packages without having a negative effect on applications that were originally developed using the XDK implementation.
This feature allows applications developed with the legacy XDK PL/SQL DOM API package (XMLDOM) to leverage the improved performance and functionality offered by the Oracle XML DB PL/SQL DOM API (DBMS_XMLDOM) without requiring significant application changes.
The World Wide Web Consortium (W3C) XPath 2.0 and XQuery 1.0 Functions and Operators adds support for the XML Schema data types along with a rich set of functions and operators to XPath that can be used in advance XSLT 2.0 style sheets and XQuerys.
This feature improves the performance and range of applications for style sheets and XQuerys. This reduces the need for compiled code, easing development and maintenance.
This feature allows an application developer to define an XML Schema that defines the custom metadata and associate it with a particular XML DB resource. Once the XML Schema has been defined, application developers can create richer content management applications by adding customer metadata to the standard metadata recorded by the Oracle XML DB repository.
This feature makes better use of information contained in documents by making it possible to associate custom metadata with documents stored in the Oracle XML DB repository. This metadata improves your ability to classify, organize, manage, process, and search the vital corporate information stored in documents.
SSL adds support for security standards to the Oracle XML protocols servers and allows the HTTPS protocol to be used to secure the transport of information between client and server.
The benefit of this new feature is that it allows the Transportable Tablespace feature of Oracle Database to be used to move tablespaces containing schema-based XMLType tables and columns from one database to another. This method moving XML between different Oracle databases is faster than alternatives such as FTP and Import/Export.
XML DB parameters
XML DB resources
XML DB Access Control Lists (ACL)
XMLType tables and columns
It is now convenient to manage Oracle XML DB in the same place where Oracle Database is managed, which is in the Enterprise Manager Web console.
The following sections describe Availability features for Oracle Database 10g Release 2.
In Oracle Database 10g Release 2, Oracle Data Guard offers a compelling set of capabilities that provide unparalleled levels of data availability, data protection, and data recovery. For example, on an outage of the primary server, site, or network, the new Fast-Start Failover feature allows Data Guard to rapidly and automatically fail over to the standby database, without requiring manual intervention. This significantly improves the degree of high availability as well as the disaster resilience for the system infrastructure. This capability makes Data Guard particularly attractive as a high availability/disaster recovery solution for 24x7 applications demanding the highest levels of uptime.
On a failure or outage of the primary server or site, or of the network, Data Guard can now rapidly and automatically fail over to a previously chosen standby database, without requiring manual intervention.
The benefit of fast-start failover is that it increases the degree of high availability, as well the robustness of disaster recovery, for the system infrastructure. It also reduces manual operations, thereby reducing management costs. This makes Data Guard particularly attractive as a high availability/disaster recovery solution for 24x7 applications where it is extremely critical to maintain uptime transparently, despite failures and outages.
Oracle Data Guard Broker for details
After a failover, this feature allows the old primary database to be automatically reinstated as a standby database, without requiring re-creation of the primary, or manually performing the Flashback Database operation. This feature is supported only for synchronous redo transports that enabled zero data loss (that is, Maximum Protection and Maximum Availability modes). It also requires Flashback Database to be enabled on both the primary and target standby databases. It is also possible to do this conversion using a single command or a simple push-button interface through Enterprise Manager.
The benefit of this feature is that it enables the Data Guard configuration to easily restore disaster protection in the configuration, without complex manual steps, improving the robustness of the disaster recovery features of Data Guard, as well as improving Data Guard manageability.
Oracle Data Guard Broker for details
Asynchronous redo transmission using the log writer process is no longer limited by the size of the network buffer. The log writer process is no longer blocked on network time outs during asynchronous redo transmission. This allows a greater number of archiver processes to archive redo logs remotely.
The benefit of this feature is that it reduces any effect on the primary database during asynchronous redo transmission. It allows faster redo transmission to standby databases for bulk batch updates on the primary database.
The benefit of faster Redo Apply failover is that it enables customers to recover from a failure or outage much faster, increasing the degree of high availability of the system.
The benefit of faster SQL Apply failover is that it enables recovery from a failure or outage much faster, increasing the degree of high availability of the system.
Oracle Database 10g Release 2 introduces database transport across the same Endian platforms which provides a fast and easy way to transport the whole database across platforms with the same Endian format. This feature uses RMAN to quickly convert the datafiles and create the new database. The newly created database is a copy of the source database but on a different operating system/hardware platform; for example, fast Windows to Linux or Solaris to HP-UX migration.
The ability to migrate databases from one platform to another has become more important for Oracle customers. Oracle Data Pump can export the entire database, but it may be time consuming because it uses the SQL layer to process data. Database transport across the same Endian platforms provides a faster and easier way to move Oracle databases from one platform to another.
Oracle Database 10g Release 2 continues to enhance the built-in database features for recovery and testing operations to be expeditious, simple, and automatic. Employing Recovery Manager (RMAN) and Flashback Database further reduces the DBA's time spent on the important task of database backup and recovery and creation of test databases. In this release, Data Guard has also optimized the network transmission of redo data, enabling standby databases to be synchronized with the primary database at a faster rate than in previous releases, with minimal effect on the primary database. Similarly, Data Guard has considerably improved its manageability and ease-of-use compared to previous releases, allowing administrators to monitor the state and performance of the disaster recovery configuration at a higher granular level.
Generate a tablespace set from the source database and plug it into the target database, essentially automating movement of tablespaces between the databases.
Generate a tablespace set which can be used to transfer a tablespace to any compatible database.
Plug-in the tablespace set (provided by the user) into the target database and perform an import operation.
Using this tool, database administrators can automate the process of transporting tablespaces across databases. Tablespaces can be transported between databases running on different platforms using different supported versions. This user-friendly tool performs checks and validations, which are needed to ensure valid and compatible tablespace sets for plugging into the target databases.
This feature allows Data Guard SQL Apply and LogMiner to be used for protection of a broader range of data types. Similarly, Streams can be used for replication of a broader range of data types.
This feature reduces storage consumption on the logical standby database and improves Data Guard manageability.
This feature deprecates unnecessary initialization parameters and attributes for Oracle Database 10g Release 2.
This feature simplifies the usage of initialization parameters and attributes, as well as certain SQL statements.
By configuring parallelism when backing up or recovering a RAC database, RMAN channels are dynamically allocated across all RAC instances. This is similar to dynamic RMAN channel allocation for a single instance.
Channel failover allows a failed operation on one node to be continued on another node.
This feature eliminates manual allocation of RMAN channels for each RAC node. It also provides better tolerance of media failures (that is, when one node fails, another node can continue backup work).
Dual-mode encryption using either transparent or password encryption
Backup encryption is a regulatory requirement for many customers. The presence of this capability in Oracle obviates the need to purchase it elsewhere.
A physical standby database may be activated to be a primary database, opened read/write for reporting purposes, then flashed back in the past to be easily converted back to a physical standby, upon which Data Guard will automatically synchronize the standby database with the primary database. This mechanism also allows a physical standby database to be used as part of a clone, test, or development solution. Note that while this database is open for reporting, it will not be able to receive any redo data from the primary database. Therefore, such a configuration may be more suitable for customers with less demanding disaster recovery requirements.
This feature allows the physical standby database to be utilized for reporting, read/write, and cloning activities, enabling customers to extract more value out of their Data Guard investment.
With the control file enhancements, there is no longer a requirement to re-create the control file when changes in the configuration parameters are made. These include the MAXLOGFILE, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES parameters.
This feature eliminates database downtime when a configuration parameter change is made in the control file.
This feature enhances the ability to validate the block when it is in memory and not just on disk.
The new RMAN views correlate and simplify the metadata of an RMAN backup job. For example, a backup job can be associated with its backup sets, and a backup set with its composite control file, archived logs, or datafiles. Additional details such as I/O file sizes and compression ratio can be found in the new views.
This features enhances the manageability of RMAN backups by providing a SQL interface to find additional statistics on the backup job and files.
Also, when issuing the STARTUP FORCE command, in previous versions, "Starting ORACLE instance (FORCE)" was written to the alert log. In Oracle Database 10g Release 2 and higher, "Shutting down instance (ABORT)" followed by "Starting ORACLE instance (NORMAL)" are now written to the alert log.
Transparently handled both in the server and the client, all processes will be cleaned up after a SHUTDOWN is issued. This allows a DBA to startup in the same session.
Additional checking has been added to the DBMS_SPACE_ADMIN package to provide better tools to debug space management problems. Extended syntax is added to the ALTER SYSTEM command to dump the space headers of locally managed tablespace files for added analysis.
This feature enhances the tools used to debug space management-related problems to detect corrupt metadata.
The benefit of this feature is reduced administration and development time because Oracle manages the distributed transaction, therefore, there is no need to write manual procedures.
Accelerated instance startup makes Oracle Database available sooner when databases have multigigabyte shared memory configurations.
Several additional views have been provided, and existing views have been enhanced to enable administrators to dynamically track the progress and performance of SQL Apply at a granular level. Enterprise Manager also provides historical trend analysis of the progress and performance data.
This feature makes it considerably easier to maintain and tune SQL Apply configurations and improves Data Guard manageability.
It is now possible to flashback the primary and standby databases to an SCN or a point in time prior to the switchover operation. Using flashback in this way on a physical standby database preserves the standby role. Using flashback in this way on a logical standby database changes the role of the standby database to what it was at the target SCN/time.
This extends the flashback window; that is, the possible SCN/time in the past to which any database can be flashed back to. This provides administrators more flexibility to detect and correct human error situations.
This feature supports flexible data recovery that enables movement of the database back and forth in time even after you perform a resetlogs operation.
If you ever need to do a resetlogs operation for recovery purposes, you can take the database back to a point in time before the resetlogs if you find that you made a mistake. This provides administrators more flexibility to detect and correct human error situations.
This prevents corruptions in RMAN backup pieces from being written to permanent storage.
This feature provides enhanced support and flexibility for Grid Management.
This feature can be used to create an RMAN incremental backup from a specified SCN. This incremental backup can be used to quickly move a standby database forward in time or load new data into a clone database for testing.
This feature minimizes the time data is unprotected by creating an incremental backup that can be applied to a standby database to quickly make the standby database transactionally consistent with the primary database. This also eliminates the need to continuously re-create a clone database with newer data values.
Online redefinition can now be performed on clustered tables, tables containing Abstract Data Types (ADT), Advanced Queuing (AQ) tables, Materialized View (MV) logs, and it retains all statistics on a table. It also clones statistics, checks and non-NULL constraints, and dependent objects on nested tables. PL/SQL packages do not have to be recompiled if the number and ordering of columns and data types in the table remain unchanged.
This feature means less downtime as more tables are now supported for online redefinition. Analysis of the table after reorganization is no longer required and PL/SQL packages remain available following an online redefinition, thus maintaining application availability. These enhancements are especially beneficial to customers using Oracle Applications.
Individual partitions may now be reorganized online.
Creation of a logical standby database no longer requires the creation of a specialized logical standby control file, which could not be used by RMAN. Logical standby databases can now be created easily from a physical standby database.
This feature reduces specialized manual operations for creating a logical standby database and improves Data Guard manageability.
By optimizing database backups, only blocks currently used by the database are backed up. In previous releases, NULL compression was utilized, whereby only never-used blocks were excluded from backup. With this new feature, all unused blocks, whether they have been used or not in the past, will be excluded from backup.
This feature supports reorganization of additional database object types that are currently not supported (for example, nested tables), enhances capabilities like launch-in-context, and improves usability of the Re-org wizard.
Currently if a tablespace contains objects like nested tables, the tablespace cannot be reorganized. This new feature enhances support for more object types for tablespace reorganization. In addition, this feature provides enhanced support for launch-in-context so that applications such as Policy Violations can pass recommendations to the wizard directly and do not depend on the user to enter the data manually.
This feature provides the ability to associate a user-defined name, Restore Point, with a point-in-time. The Restore Point can be used with a Flashback Database or Flashback Table during recovery operations. Restore Point can be specified such that it guarantees the database can be recovered to a particular point-in-time.
Restore Point eliminates the need to manually record an SCN or timestamp to use for Flashback Database and Flashback Table operations. In addition, a special 'guaranteed' restore point ensures that the database can be flashed back to that particular point-in-time. Using guaranteed restore points can achieve efficient disk space usage, as only those flashback logs necessary to meet the guaranteed restore point are required to be retained.
Restore points can also be created as 'guaranteed', meaning it provides enhanced Flashback Database efficiency in recovery and disk storage requirements. And, it guarantees the database can be rewound to a specified point-in-time.
This eliminates the need to manually create temporary tablespaces after recovery.
The following sections describe new features for Oracle Database 10g Release 2 in the Business Intelligence area.
In this release, Oracle extends the SQL language to support more powerful analytics. The MODEL clause of the SELECT statement, which provides a powerful new approach to analytic calculations, adds to its expressiveness and scalability in this release. Bitmap indexes, a vital structure for efficient Business Intelligence query processing, also gain enhanced scalability.
The benefit is more expressive rules and greater productivity in specifying the MODEL clause.
This feature moves evaluation of MODEL clause rules using FOR loops from compile time to runtime. This permits rules with FOR loops to use reference spreadsheets, nested references, and the ITERATION_NUMBER variable. In certain situations, subqueries within FOR loops can have shared cursors and refer to tables defined in the WITH clause.
The benefit of this feature is that MODEL clauses with certain types of rules perform better, handle larger model sizes, and include more powerful rule expressions.
Native support of analytic functions is significantly faster than alternative techniques and simplifies expression of complex calculations, such as allocations and forecasts, in the MODEL clause.
Oracle is designed to meet all data requirements for the largest databases. VLDB environments, such as data warehouses, depend on partitioned tables. This release enables more:
Partitions for each table
More efficient partition management
More partition-based query optimization
Increasing the maximum number of possible partitions to objects enables finer granularities for partitioned objects and even broader flexibility in choosing the right partitioning approach for a business scenario.
This feature provides enhanced performance for a broader set of complex queries.
Dropping large partitioned tables can affect tens of thousands of partitions that all have to be logically removed from the system. The capability of transparently dropping such an object in an incremental fashion optimizes the resource consumption and positively affects the run-time behavior.
A key requirement for business intelligence is to be able to analyze data more rapidly after an event occurs. Oracle provides a wide range of features to support efficient ETL (extraction, transformation, load). This release reduces the information cycle time with enhancements ranging from data loading to query processing. New features minimize rollbacks during bulk DML, enable more Change Data Capture environments, simplify materialized view refresh, and enhance query rewrite.
The internal Change Data Capture (CDC) tasks of Streams Capture and Apply are decoupled, thereby enabling a heterogeneous CDC setup with different operating systems and Oracle versions. The propagation between source and target database is a pure Streams implementation, enabling asynchronous CDC to leverage any existing Oracle9i Release 2 system as a source.
Asynchronous Change Data Capture (CDC) no longer requires the same operating system for source and target. Furthermore, distributed asynchronous CDC capabilities expands the reach of CDC source systems to include Oracle9i Release 2.
This feature allows bulk DML operations to continue processing, when a DML error occurs, with the ability to log the errors in a DML error logging table. On a DML statement level, you specify whether to log errors or not, the level of detail information, as well as a maximum error threshold, similar to external table and SQL*Loader.
This feature combines the power and speed of bulk processing with the functionality of row level error handling.
Oracle Database Concepts for details
Partition Change Tracking Refresh (PCT) no longer requires materialized view logs. It can be combined with conventional fast refresh to enable a refresh, where one table is using PCT and the other table uses materialized view logs.
This feature improves performance because materialized views can now be created in situations where they were previously not used due to the overhead of materialized view logs. This feature also provides faster refresh due to the mix of PCT and conventional refresh.
This feature improves performance as more queries are eligible for query rewrite and reduced maintenance costs because fewer materialized views are required.
The benefit is more complete modeling of the dimensions and performance benefits, because improved query optimizations are now possible.
Oracle OLAP provides a complete OLAP server that is fully integrated into Oracle Database. In this release, Oracle OLAP makes cube aggregation more flexible and enhances the OLAP API, enabling an even broader set of analytic applications than in earlier versions. Optimized OLAP cube refresh supports applications demanding the very latest data.
Incremental aggregation of compressed cubes occurs automatically within the multidimensional engine. The multidimensional engine tracks detail level changes to the cube and aggregates only the summary level data that would be affected by the newly loaded data.
This new feature improves performance for the process of aggregating an OLAP cube that has been stored using cube compression technology. This performance improvement is seen when the compressed cube has been incrementally updated with new data (for example, when adding a new time period to the cube).
Oracle OLAP DML Reference for details
Within the OLAP API model, application developers can define calculated (virtual) dimension members. For example, a calculated dimension member such as 'New England' in a geography dimension could be created as the sum of stored dimension members 'ME + VT + NH + MA + RI + CT'.
Calculated member support in the OLAP API allows application developers using the OLAP API to create calculated (virtual) dimension members within an OLAP API query. This extends the analytic capabilities of applications that use the OLAP API.
Oracle OLAP Java API Developer's Guide for details
Semiadditive aggregations are those where the aggregation method on one or more dimensions is something other than sum. Examples include first, last, hierarchical sum, and hierachical weighted average. In Oracle Database 10g Release 1, compressed cubes could not be used with semiadditive aggregation. In Oracle Database 10g Release 2, semiadditive measures can be used with compressed OLAP cubes.
Support for semiadditive aggregation methods with compressed cubes provides the opportunity for significant aggregation performance improvements.
Oracle OLAP DML Reference for details
Oracle Data Mining provides integrated mining capabilities directly in Oracle Database. Oracle enriches its data mining for the full spectrum of application developers with enhancements targeted to Java specialists, PL/SQL developers, and data mining newcomers. Highlights include decision tree models, simplified model development, and a new SQL function for predictive data mining.
The benefit of this feature is a transparent, rule-producing algorithm. The presentation of a decision tree in the form of human-interpretable rules increases the confidence, acceptance, and value of the algorithm. The decision tree is optimized to allow fast and scalable data scoring.
The Predictive Analytics Package makes reasonable assumptions about the data and the mining process and greatly simplifies the use of data mining for end users. This feature makes data mining more accessible to non-data analysts.
Oracle Data Mining Concepts for details
This capability allows customers and applications to:
Ease deployment of data mining models within the context of existing applications
Dramatically improve scoring performance
Enable pipelining of results, which can accelerate the presentation of findings to end users
Oracle Database 10g Release 2 provides a Java Data Mining (JDM) JSR-73 compliant Java API. Oracle Data Mining 10g Release 2 requires a Java API implemented on top of the 10.1 PL/SQL API. This unifies the overall product, enabling interoperability between models.
This feature provides vendor neutral, open standard data-mining application development.
The following sections describe new features for Oracle Database 10g Release 2 in the clustering area.
This release provides session management capabilities that improve the ability to balance workloads across instances in a clustered environment. Workload policies are defined which enable connection pools to route work requests to the appropriate instance based on real-time information.
Real Application Clusters includes a Load Balancing Advisory that monitors the work for each service in an instance. The advisory creates Fast Application Notification (FAN) events to provide feedback that enables routing by the connection pool to respond quickly to changing conditions in the system. In steady state, the system approaches equilibrium with optimal service times across all Oracle Database 10g RAC instances. JDBC and ODP.NET Connection Pools are integrated to provide the best connection to an application request instead of a random connection from the pool. Oracle has integrated its connection pool clients (OCI, ODP.NET, and JDBC) with the Load Balancing Advisory. Runtime connection load balancing enables connection pools to route work requests to the connections that can best serve the work based on the FAN events received from RAC. Requests can be routed according to different policies.
The benefit is improved performance and use of resources.
Oracle Cluster File System (OCFS) will be made available for Sun Solaris environments. This allows for easy management of data that needs to be shared across nodes in a clustered environment. OCFS is designed to support all Oracle Database files and ORACLE_HOME binaries. Note that OCFS for Sun Solaris environments will be released after the release of Oracle Database 10g Release 2.
This feature provides enhanced cluster manageability for clusters in Sun Solaris environments.
This release improves the RAC experience by verifying the environment before installation, providing tools to assist in diagnosing problems that may occur, and ensuring there are redundant copies of Clusterware Files.
There are 2 files, the Oracle Cluster Registry (OCR) and the Voting Disk, that are critical to running a cluster using Oracle Clusterware. Oracle Clusterware automatically maintains two copies of the Oracle Cluster Registry (OCR) file and three or more copies of the Voting Disk files. Mechanisms are also provided for the online replacement of failed OCR devices.
The benefit is that redundant copies of critical clusterware management files are automatically maintained to protect against single points of failure and eliminate the need for third-party storage redundancy solutions.
Oracle Clusterware provides a Cluster Verification Utility that verifies the availability and integrity of a wide range of cluster elements in Oracle Clusters and Oracle Database 10g RAC environments. These facilities include both a standalone Cluster Verification Tool for use by DBAs and callable routines invoked automatically by Oracle installation, configuration, and system management tools. The Cluster Verification Utility can be invoked at any stage of a cluster configuration.
Cluster verification makes the installation, configuration, and overall management of Oracle Clusterware environments including Oracle Database 10g RAC environments simpler by quickly and easily identifying problems in complex cluster systems.
New facilities in Oracle Database 10g Release 2 provide improved diagnostics of cluster wait/hang conditions and more effective logging of Oracle Clusterware error/alert messages. In addition, diagnostic information for faster debugging and problem analysis is provided.
The benefit of these enhancements is greater code reliability and faster problem resolution by Oracle Support Services and the development community.
This release extends Oracle Clusterware by providing an open API which allows additional (non-Oracle) components running on the database server to be managed by Oracle Clusterware.
An open API is provided to Oracle Clusterware to enable customers and partners to integrate their application system components into Oracle high availability environments. Application system components registered with Oracle Clusterware are managed along with Oracle system components to ensure that failures of any component can be detected and recovery procedures initiated to quickly restore the overall system to an operational state.
This feature ensures that complex cluster systems involving Oracle components and components from customers and partners can be managed together by Oracle Clusterware to provide high overall system availability.
The following sections describe Content Management features for Oracle Database 10g Release 2.
Improvements for Text include a new set of document filters that provide better quality and performance, Asian language support for CTXRULE index type, and better manageability of text indexes. Also, the new KWIC feature (also called snippet) returns query keywords marked up in their surrounding text, allowing the user to evaluate them in context.
CTXRULE is a filtering model for Asian language search and rules and enables rule-based classification.
Oracle Text Application Developer's Guide for details
Oracle Text's automatic filtering technology, licensed from Verity, Inc., enables you to index most document formats. This technology also enables you to convert documents to HTML for document presentation.
The Key Word in Context (KWIC) feature has been enhanced in this release with two new procedures, CTX_DOC.SNIPPET and CTX_DOC.POLICY_SNIPPET, that return text fragments containing keywords found in documents.
These new procedures enable you to see the keywords in their surrounding text, allowing you to evaluate them in context.
This features improved the manageability and scalability for text indexing.
interMedia now includes support for the Digital Imaging and Communications in Medicine (DICOM) standard, the most common medical imaging format. Also provided are:
APIs that extract embedded content metadata in IPTC, EXIF, and XMP formats from a number of popular image formats and return this metadata as a collection of XML documents.
APIs that embed XMP formatted metadata into images.
interMedia now supports the most common medical imaging format, DICOM version 3. Applications can now use interMedia Java and PL/SQL APIs to extract metadata about patients, physicians, diagnoses, treatments, tests and procedures, and other relevant information included in the DICOM format. Metadata extracted from DICOM images is represented as an XML document returned in an XMLType object. Each returned document conforms to an XML schema that is registered with the database. Once this metadata has been extracted from the image, it can be stored in an Oracle Database, indexed, searched, and made available to applications using the standard mechanisms of Oracle Database. Not all metadata is extracted, but metadata generally present in all DICOM images will be extracted.
This new interMedia capability allows Oracle Database 10g Release 2 to serve as the repository for shared multiterabyte medical imaging archives used by HIMMS and PACS systems, life sciences and medical research centers, and government agencies.
Oracle Multimedia User's Guide for details
In addition to the current support for format metadata (height, width, mimetype, etc.), interMedia now includes Java and PL/SQL APIs that extract embedded content metadata (in IPTC or IIM, EXIF, and XMP format) from TIFF, GIF, and JPEG image formats. Information supplied by digital cameras, image editing software programs, news and wire services, such as photographer, location, date, time, and other application data can now be extracted and represented as a collection of XML documents returned in XMLType. Once metadata is extracted, it can be stored in an Oracle Database, indexed, searched and made available to applications using the standard mechanisms of Oracle Database.
interMedia Java and PL/SQL methods also allow any Adobe XMP (tm) formatted, user-defined metadata to be embedded into TIFF, GIF, and JPEG formats.
By placing the metadata and the image data in the same containing binary image, both types of data can be shared and exchanged reliably as a unit.
Oracle Multimedia User's Guide for details
Oracle supports the latest production release of the Sun Microsystem Java Advanced Imaging (JAI) package for interMedia applications that use Oracle to store and process image content.
The following sections describe new features for Oracle Database 10g Release 2 for the database in general.
The following sections describe new Database Utilities features.
Manual database upgrades are now easier to do. For any database version being upgraded to Oracle Database 10g Release 2, only one common SQL script has to be invoked when performing a database upgrade. Specifying a version-specific script is no longer required, because Oracle now automatically determines what version is being upgraded and runs the appropriate upgrade scripts for that database and all of its included components.
The advantage is that less time is required to do manual database upgrades.
Oracle Database Upgrade Guide for details
This feature improves the manageability of Data Pump jobs and allows users to get accurate information about possible issues with their specific database upgrade prior to running DBUA or performing a manual upgrade.
This feature creates a default DATA_PUMP_DIR directory object. This simplifies the use of Data Pump, because the DBA does not have to manually create a directory object before trying to use Data Pump Export.
This allows users to quickly start using Data Pump because a default directory object is automatically created.
Data Pump Export dump files can contain database metadata objects in a compressed format, allowing dump files to consume less operating system disk space. This metadata is automatically uncompressed during Import.
This feature reduces the amount of disk space consumed by dump files.
This provides improved manageability of Data Pump jobs.
This feature allows users to indicate the size of the dumpfile being created when using the Export command line interactive ADD_FILE command by specifying a FILESIZE value. In previous releases, the size of the file was unlimited.
With this feature, if there are storage restrictions, users can control the size of dumpfiles.
Monitor Data Pump jobs started outside of Enterprise Manager
Support Data Pump Export from a read-only database
Move to new server APIs
In previous releases, Enterprise Manager only monitored Data Pump jobs that had been created using Enterprise Manager. However, it is a common activity for a DBA to create Data Pump jobs without using Enterprise Manager and it is possible for these jobs to take a very long time (hours or days) to complete. Enterprise Manager is a database monitoring tool and has been enhanced to monitor all Data Pump jobs, whether created by Enterprise Manager or not.
The WAIT_FOR_JOB API provides the simplest mechanism for waiting for the completion of a Data Pump job. The GET_DUMPFILE_INFO API analyzes a specified dumpfile and returns dumpfile information such as version, creation date, and character set.
This new features improves the manageability of Data Pump jobs. You can get more information about Data Pump job completion and specific dumpfiles.
With this feature, you can reduce the amount of space required for tablespaces by shrinking tablespace storage allocation. Large datafiles in production systems can be reduced so that smaller datafiles are created during Import on test systems.
The benefit of this feature is the ability to reduce the amount of tablespace storage required for datafiles.
This is useful for duplicating schemas across databases, using Export and Import, when you cannot guarantee that object types will have identical OID values in those databases.
This feature provides a single Oracle standard for EXPLAIN PLAN_TABLE output.
The benefit of this feature is increased usability of SQL*Plus.
This feature allows XQueys to be created and tested in a highly available tool (SQL*Plus).
The following sections describe new features for Oracle Database 10g Release 2 in the Security and Directory areas.
Privacy, confidentiality, and the protection of personally identifiable information (PII) is a global concern in today's economy. Oracle provides a wealth of server-enforced protections for private and confidential information. Oracle Database 10g Release 2 introduces Transparent Data Encryption that quickly and easily encrypts sensitive information. Existing applications generally continue working without any modification because the data is transparently decrypted when accessed through SQL.
This feature provides transparent data encryption and key management. Businesses can protect sensitive data in Oracle databases without any changes to their application. SQL interfaces to Key Management hide the complexity of encryption. This feature mitigates liability due to storage theft.
This feature provides a user interface to the transparent data encryption feature.
The benefit is transparent key management for data encryption.
The ability to focus more on security policies and less on low level implementation details is key to securing the enterprise. Oracle Database 10g Release 2 Transparent Data Encryption manages the encryption keys for the application administrator, simplifying the implementation and management of encrypted data. In addition, Oracle Database 10g Release 2 auditing enhancements include writing audit data in XML format to the operating system and providing additional protections for audit data at the operating system level.
Proxy authentication in Oracle Database has always been available using OCI or JDBC programming. This feature extends this capability to the CONNECT command and enhances the current OCI/JDBC interface. For example, an application user/schema could be managed as a directory user, improving manageability of application schemas across the enterprise.
This feature extends proxy authentication capability to tools such as SQL*Plus which are used by applications for running batch jobs. Application security is improved as the application schema password is not exposed at connection time. It also enhances the existing proxy capability by allowing proxy users to be directory users.
Audit trail can be written to the operating system files in standard XML format. These logs can also be viewed using dynamic views providing better access to information in the operating system audit logs.
This feature enhances database operating system audit capability to support XML audit trail format. This allows the DBA to build custom tools to trace abnormal activities and view and mine audit logs. It also enhances the ability of the DBA to view operating system audit information when authenticated to the database.
Oracle Database Security Guide for details
A major objective for Oracle Database 10g Release 2 is to ensure you can easily install and configure Oracle Database in a hardened configuration. This includes locking default accounts, providing database roles for privilege management, and protection of sensitive information. Oracle Database 10g Release 2 provides additional security for passwords used in fixed database links. In addition, the much used CONNECT role has been modified to include only those privileges required to connect to the database, namely the CREATE SESSION privilege.
CREATE DATABASE LINK
This feature helps you deploy secure configurations by helping enforce the least privileged principle.
The ability to leverage security with Oracle features, such as Real Application Clusters, is key to deploying secure and scalable solutions. Oracle Label Security has been enhanced in Oracle Database 10g Release 2 to support Oracle Real Application Clusters. This provides customers additional capabilities for protecting sensitive information.
In addition, Oracle Wallets have been enhanced to provide the ability to store username and passwords for command line operations. For example, batch jobs using Oracle SQL*Plus can use a wallet to connect to the database versus having to supply a username and password on the command line.
In addition, Oracle Database 10g Release 2 introduces a new command line proxy capability that allows authorized users to proxy to different accounts upon authentication to the database.
Oracle Wallet functionality has been extended beyond PKI credential store to include username and password storage. This enables a user to store his or her username and password in an Oracle Wallet and have the password picked up automatically at connection time. For example, batch jobs using SQL*Plus could type
sqlplus / and authenticate using the credentials in the wallet.
Administrators and application developers now have a secure alternative to hardcoding usernames and passwords into scripts and other programs.
Oracle Database Security Guide for details
This feature allows security administrators to enforce centralized password policies for enterprise users when accessing one or more Oracle databases. It provides for reduced administrative costs as there is a single point of administration. This enhancement allows organizations to better manage security across the enterprise.
Oracle customers using RAC can now leverage Oracle Label Security to meet organizational security requirements for protection of sensitive data.
Oracle Label Security Administrator's Guide for details
The following sections describe new features for Oracle Database 10g Release 2 in the Grid Computing area.
This release provides features to simplify data provisioning within a Grid environment, making it easier and faster to archive, move, and copy large sets of database data. These new features enable performing these bulk provisioning operations with no effect on any production database.
Groups of files (for example, tablespaces) can be copied to the repository, or from the repository to another database or file system, without effecting access to production data.
This enables a DBA to copy or move a tablespace of data using the transportable tablespaces feature without making the tablespace read-only in the source database.
The Oracle Scheduler has been enhanced to provide additional critical functionality to meet the scheduling needs of enterprise customers. In addition to functionality and usability improvements, jobs can now be chained and scheduled based on external events.
This feature addresses the need to perform an action based on the occurrence of an event. The Scheduler can perform actions based on external events or actions can be performed based on the Scheduler generated events.
The benefit of this feature is that you can schedule jobs based on events.
This feature enables you to define dependencies between jobs.
This feature enables you to define a custom calendar such as a business fiscal year. Jobs can be scheduled to run on the last work day of every fiscal quarter. This feature also enables the use of rules (for example, Memorial Day is the last Monday of the month of May) to identify dates and save them as a schedule. Existing schedules can be combined to create composite schedules. For example, a HOLIDAYS schedule can be excluded from a DAILY schedule to create a WORKDAYS schedule.
The Scheduler enhancements enable customers to define complex schedules such as the last working day of every fiscal quarter.
The following sections describe new features for Oracle Database 10g Release 2 in the Grid Management area.
This release includes enhancements to configuration assistants or plug-ins and post-installation configuration.
This feature helps configure Enterprise Manager Database Control with Oracle Database 10g Release 2 Grid Control. EMCA is an executable within the ORACLE_HOME but can also be called as a plug-in from other configuration assistants (for example, DBCA).
The Enterprise Manager Configuration plug-in (EMCA) helps configure Enterprise Manager Database Control under various conditions:
Out-of-box configuration using a seed database
Out-of-box configuration with a custom database
Post-install configuration using DBCA
Post-install configuration using custom scripts
Migration to Oracle Database 10g Release 2 from earlier versions
Automatic Storage Management (ASM) provides simplified storage management within Oracle Database 10g. ASM is integrated with DBCA and Enterprise Manager. DBCA offers functionality to create an ASM instance and configure the disk groups for database creation. Enterprise Manager offers functionality to manage and monitor ASM and disk groups as well as automating the various stages involved in converting an existing non-ASM database onto ASM-based storage.
Enterprise Manager provides an interface to help customers migrate their existing databases onto ASM from non-ASM databases.
In Oracle Database 10g Release 1, an ASM configuration using DBCA was supported only as part of database creation. In previous releases, setting up ASM required cancelling out of DBCA after ASM setup was done. In Oracle Database 10g Release 2, ASM Disk Group Management has made creating an ASM instance and configuring disk groups easier.
In Oracle Database 10g Release 2 (10.2), you can create an Automatic Storage Management (ASM) instance using an option in the Database Configuration Assistant (DBCA). You can, therefore, prepare an ASM environment for an existing database using the DBCA without concurrently creating a new and unneeded database. However, you can continue to use the DBCA to create a new database and build an ASM instance, if one does not already exist, to manage the new database's storage requirements.
The Database Upgrade Assistant guides the DBA through the upgrade process from supported releases of Oracle Database 10g Release 1 to Oracle Database 10g Release 2.
Oracle Database Upgrade Guide for details
This release provides new and improved features to Enterprise Manager that facilitate easier management of the database.
Currently you cannot manually add cluster targets in Enterprise Manager. This feature provides a way to create and configure cluster targets in case cluster discovery fails during installation.
Quick tune (tune a single statement)
Recommendation implementation status
Action implementation status
The Access Advisor user interface in Enterprise Manager has been enhanced to improve the user experience and expose more powerful features of the Access Advisor.
RMAN script jobs
Backup job notifications
Recovery catalog configuration
The Backup screens in Enterprise Manager have been enhanced to improve the user experience and expose more powerful RMAN features.
External object cloning enhancement in the Enterprise Manager database cloning feature ensures that external objects, which are tables stored as external files, get copied over to the destination systems during the clone operation. This ensures that no data is left behind.
The ability to clone external objects extends the database cloning to ensure that all data is cloned, not just objects residing within the database.
Redo log file size advisory
All possible actions for an object are provided on the View/Edit object page
Search for PL/SQL objects based on object status
Delete multiple objects simultaneously
Compile multiple PL/SQL objects simultaneously
Enable or disable multiple triggers simultaneously
The database administration area has been enhanced through the addition of some minor features that increase the DBA's productivity.
Configuration of audit-related initialization parameters
Administration of audited objects: statement auditing and schema object auditing
This feature makes it convenient for the DBA to manage database auditing in the Enterprise Manager Web console, where all other aspects of Oracle Database are managed.
Oracle Database 10g lets you guarantee undo retention. In Enterprise Manager 10g Release 2, you can enable and disable undo retention through the Enterprise Manager user interface. When you enable this option, the database never overwrites unexpired undo data, that is, undo data whose age is less than the undo retention period. This option is disabled by default, which means that the database can overwrite the unexpired undo data to avoid failure of DML operations if there is not enough free space left in the undo tablespace.
By enabling the guarantee option, you instruct the database not to overwrite unexpired undo data even if it means risking failure of currently active DML operations. Therefore, use caution when enabling this feature.
A typical use of the guarantee option is when you want to ensure deterministic and predictable behavior of Flashback Query by guaranteeing the availability of the required undo data.
Enterprise Manager has added new functionality to Backup Management that provides a single view of all backups run against the databases in a domain. The details of the backup are available by clicking on the specific backup for the database.
By providing a combined view of all database backups and their status, a DBA can quickly pinpoint when a backup job did not complete. A drill down to the log allows the DBA to fix the problem and restart the backup.
Oracle Database 2 Day DBA for details
Enhanced diagnostics for cache coherency helps users do more diagnostics around the cache coherency statistics.
This feature provides better integration between the Segment Advisor and segment findings which provides a more unified interface with consistent findings and recommendations.
This release supports the new server features provided by the 10.2 Segment Advisor. This feature helps integrate the Segment Advisor with the segment findings. These enhancements help support non-ASSM tablespaces and detection of chained rows in segments.
In previous releases, lock statistics would lock the state of statistics based on object usage in the application. When statistics on a table were locked, all the statistics depending on the table, including table statistics, column statistics, histograms, and statistics on all the dependent indexes were locked.
For Oracle Database 10g Release 2, the database server provides the lock and unlock feature in the optimizer statistic area. Enterprise Manager provides support for these features with user-friendly wizards to help assist DBAs with the operations.
You can now lock the statistics to a state where you know the statistic is good and will not change. For example, you can collect statistics for a volatile table while it is fully populated and freeze the statistics as a set of representative statistics.
You can also lock the state of the statistic when there are no statistics. This is useful to keep the statistics on a table uncollected in order to use Dynamic Sampling.
When you no longer want the statistics to be locked, use the Unlock wizard to unlock the statistics.
The DIRECTORY object facilitates administering access and usage of BFILE data types. It is convenient for the DBA to manage Directory Objects in the Enterprise Manager Web console where all other aspects of Oracle Database are managed.
The required undo tablespace size is computed as the maximum of the auto tuned value of undo retention and the low threshold value.
In the Undo Advisor page, the field name "New Undo Retention" has been changed to "New Low Threshold Undo Retention."
"Undo Retention (minutes) No Recommendation" is displayed if auto tuning is disabled. It is not displayed if auto tuning is enabled (the default).
In the Undo Management page under the System Activity and Tablespace Usage, the default heading has changed to "Last One Hour" from "Last Seven Days".
This feature provides some user interface-related enhancements to the Undo Advisor pages.
Storage Policy: Database default temporary tablespace not set
Storage Policy: Database permanent tablespace not set
Storage Policy: Tablespace not using Automatic Segment Space Management
Config Policy: Not using Dynamic SGA and PGA
Config Policy: Not using the latest optimizer version
Config Policy: STATISTICS_LEVEL parameter not TYPICAL
This feature enhances the existing storage and configuration policies by adding new database storage and configuration policies and consolidating any overlap in the existing policies.
This feature also enhances some existing metrics. The storage policies help the end user ensure that their tablespaces follow best practice rules. For example, they help the user identify dictionary-managed tablespace or tablespace that contains mixed data segments. This helps the end user in correctly setting up tablespace storage, thus avoiding space and performance problems.
The configuration policies check basic database settings to ensure the end user is taking advantage of the latest database features. These policies also help ensure that the databases are configured in the manner recommended by Oracle.
By adding this policy support, Enterprise Manager becomes more competitive in the marketplace.
Oracle Spatial provides a SQL schema and functions that facilitate the storage, retrieval, update, and query of collections of spatial features in an Oracle Database. It is convenient for the DBA to manage Oracle Spatial in the Enterprise Manager Web console where all other aspects of Oracle Database are managed.
The performance monitoring capabilities have been expanded for RAC environments. RAC DB Home pages scale for 64-128 nodes/instances. There is additional monitoring of cache coherency for RAC instances.
The presented performance data is more meaningful, performance problems are easier to detect, and helps identify the root cause of cluster database performance issues.
For Oracle Database 10g Release 2, the database server provides the restore statistics feature in the optimizer statistic area. Enterprise Manager provides support with a user-friendly wizard to help assist DBAs with the operations.
If the statistics collected in the management window have caused a sub-optimal plan to be generated, you can revert to past statistics using the Restore Statistics wizard.
Recover in context of new Oracle home
Enhanced Guided Recovery wizard
Restore and Recovery operations have been further automated in Enterprise Manager.
This feature can be scheduled in a maintenance window.
For a selected tablespace, this feature displays the number of segments, segment names, segment types, number of extents, total size of extents for each segment (in blocks), the maximum number of extents that can be allocated to each segment, and the percent of the tablespace used.
The ability to display more details on tablespace segments will ease administration.
For Oracle Database 10g Release 2, the database server provides the capability to gather statistics for fixed and dictionary objects in the optimizer statistic area. Enterprise Manager provides a user-friendly wizard to help assist DBAs with the operations through the Gather Statistics wizard.
If your database suffers from performance issues due to out-of-date statistics for fixed and dictionary objects, use the 10.2 Enterprise Manager Gather Statistics wizard to allow statistics collection on these objects.
The benefit of this feature is an improved display of tablespace segments and the ability to sort into a tabular format.
This feature allows Enterprise Manager to monitor the interconnect on a RAC instance.
The ability to use RMAN compression during database clone operations reduces the network bandwidth requirements for database cloning operations. This benefits anyone who is cloning over low bandwidth network links, and saves disk space when the database clones are saved for later reuse.
The Enterprise Manager Load Data wizard helps you specify a data file and then steps through some simple screens where you can provide information about the structure of the file (both character delimited and fixed length files are supported). Enterprise Manager uses the supplied information to automatically generate a SQL*Loader control file, and subsequently creates a SQL*Loader job for loading the data file into the database.
SQL*Loader is a powerful and scalable tool for importing data from files into Oracle Database. However, the process of creating SQL*Loader control files is time-consuming and error-prone. The Enterprise Manager Load Data wizard automates this process, thereby making the data loading capabilities of SQL*Loader more accessible.
Oracle Database 2 Day DBA for details
These features include enhancements to the patching tool used to patch databases and application server software.
OPatch is a tool for patching Oracle Database that is available from Oracle9i Release 2 and later and Application Server (starting 18.104.22.168) ORACLE_HOMEs. It also serves as a backend of Enterprise Manager patching.
This feature helps you apply interim patches to your environments. OPatch serves as the backend for Enterprise Manager patching and can therefore achieve large scale patching in Grid environments.
This release includes enhancements to Oracle Universal Installer (OUI) framework and individual product installs that are used for deploying Oracle software.
The improved install process with Database Configuration Assistant (DBCA) and Installer can now place ASM in its own ORACLE_HOME and then configure ASM disk groups. Upon completion, you can run additional Oracle Universal Installer (OUI) sessions to install and configure one or more Oracle databases in separate ORACLE_HOMEs (or database environments) that would then use ASM.
Using the new installation process, you can set up the recommended configuration when more than one database instance runs on a single server or node (rather than having the ASM instance and a database instance share a single ORACLE_HOME). The improved installation process makes it easier to manage multiple database environments on a single server.
The RAC deployment model supports simple cloning mechanisms for extending RAC horizontally. The ability to clone and extend RAC horizontally is the key element of the Grid message.
The RAC install framework includes framework application programming and command-line interfaces that are needed for Oracle Clusterware and RAC installations on a new cluster or onto a newly added node to an existing cluster.
This feature helps you to provision RAC software and add nodes to an existing RAC environment with minimal manual effort.
Silent operations enable you to use non-interactive installs. The add node feature is critical for increasing and decreasing capacity on demand. In summary, RAC provides a simple, scalable, and stable deployment framework.
The following sections describe new features for Oracle Database 10g Release 2 in the Information Integration area.
These features provide functionality and performance enhancements, as well as usability improvements, to make Oracle Streams the most powerful, highest performing, and easiest-to-use information sharing solution for Oracle data.
This feature improves the performance of Streams.
Downstream capture, the ability to off load the capture process from the production source database, can now be configured for near real-time processing (hot mining) of the primary source redo logs at the alternate database.
A capture process can run on a database other than the source database. The redo log files from the source database are copied to the other database, called a downstream database, and the capture process captures changes in these redo log files at the downstream database. To improve the performance of certain configurations, downstream capture can now be configured for near real-time processing (hot-mining) of the primary source redo logs at the alternate database.
This feature provides a faster, less resource-intensive queuing implementation.
Oracle Streams Advanced Queuing User's Guide for details
Commit-time queues are useful when your environment needs to support transactional dependency ordering during dequeue, or when you need to support consistent browsing of events in a queue.
Oracle Streams can apply changes made at the source site in parallel at the destination site while ensuring that dependent changes are applied in the same order as they were done at the source site. Occasionally, there are complex dependencies among tables that cannot be specified through database objects (such as referential constraints).
This feature enhances Oracle Streams to allow for the specification of these complex dependencies and the enforcement of these dependencies when applying the changes in parallel.
This feature improves ease-of-use by simplifying access to LOBs within a customized Streams apply process.
The ability to rename columns of a table, tables, or the table owner (schemas), as well as adding or removing columns from the LCR, is enabled external to user-supplied PL/SQL functions.
This feature extends the notification capabilities available with Oracle Streams Advanced Queuing.
Oracle Streams Advanced Queuing User's Guide for details
The benefit is improved performance and functional correctness for OJMS nonpersistent messaging.
This feature improves ease-of-use by providing simplified API's for common tasks such as performing an application upgrade or database migration. Also, it completes RAC transparency by providing queue-to-queue propagation.
This feature makes it convenient for the DBA to manage Oracle Streams in the Enterprise Manager Web console, where all other aspects of Oracle Database are managed.
Client/server and server/server communications have been improved in this release. Notification of failures will speed application failover providing better availability. Other improvements provide streamlined and more robust communications.
The benefit of this feature is that JavaNet performs name lookups the same as the C Net stack. This makes the system administrator's work easier and more consistent when configuring both JavaNet and C Net stack.
Support will continue for the current LDAP resolution that requires you to specify the LDAP server URL and DN in the CONNECT string. However, now you can use an alias for LDAP support which is the same as for the C Net stack.
Manage queue tables
Oracle Streams AQ provides database-integrated message queuing functionality. It is now convenient for the DBA to manage Oracle Streams AQ in the Enterprise Manager Web console where he manages all other aspects of Oracle Database.
In the event of a failure of a database instance, the server will send a notification to clients to initiate failover.
Oracle Call Interface Programmer's Guide for details
The following sections describe new features for Oracle Database 10g Release 2 in the Location Services area.
This release extends the Spatial routing engine and geocoder to work with European countries and improve the quality and robustness of these features (including Spatial Analysis and Mining).
The Spatial routing engine introduced in Oracle Database 10g Release 1 has now been extended to provide driving distances, times, and directions between addresses for over a dozen Western European countries, including (but not limited to) Germany, United Kingdom, and France.
The Spatial routing engine enhancements enable logistics, transportation, and location-based services applications to build driving direction services for over a dozen Western European countries.
Oracle Spatial Developer's Guide for details
This release supports the EPSG-based coordinate systems model for the Oil & Gas Industry.
Oracle Spatial coordinate systems support the European Petroleum Survey Group (EPSG) data model and data set that is essential for oil and gas exploration and production systems. EPSG support provides benefits of standardization, expanded support, and flexibility for oil and gas companies, georaster data vendors, and GIS users in general.
Oracle Spatial Developer's Guide for details
This release supports industry standard compression techniques for Spatial GeoRaster.
Spatial GeoRaster supports industry standard compression techniques for raster image data, including the JPEG baseline (lossy) and DEFLATE (lossless) standards. All GeoRaster functions now work on both compressed and uncompressed images.
Remote sensing imagery results in very large data sets, growing at the rate of a terabyte or more for each day. The capability to store and manage these images in compressed form is key for users and DBAs alike. You save money on storage costs when image sizes are reduced by up to 80 percent. This is essential for defense/security, agricultural, and environmental monitoring applications.
Oracle Spatial GeoRaster Developer's Guide for details
This release provides performance and scalability improvements to support analysis on very large networks.
The size of a network analyzed using Oracle Spatial network data model is no longer limited by the amount of physical memory available. This release includes graph partitioning, which divides a large network into logical partitions based on partition ID. Logical partitions can be incrementally loaded into memory for analysis.
Oracle Spatial network data model graph partitioning creates scalability that is independent of physical memory. Applications with large networks such as utility networks, street networks, and biological pathway data in life sciences will benefit from this important enhancement.
This release improves the performance, quality, and robustness of Spatial Topology according to the requirements of US CENSUS.
The Spatial topology data model has been extended to support feature-level spatial transactions against persistent topology in the database. In the previous release, multiple operations were required to insert or update all of the node, edge, and face elements of a feature. Now, a feature insert or update occurs as a single operation.
Whole feature-level inserts and updates dramatically reduce the code required for updating and maintaining topology data sets. This meets the need of major mapping agencies and land management applications.
The following sections describe new features for Oracle Database 10g Release 2 in the Performance and Scalability areas.
Oracle Database 10g Release 2 includes performance optimizations for commonly used features such as LOBs, PL/SQL, and Index Organized Tables. Performance gains are obtained by redesign and code streamlining and do not require changes in customer applications using these features, unless explicitly stated otherwise.
Dynamic performance views are essential tools for database administrators to monitor and tune database performance. With the additional LOB access statistics in dynamic performance views, database administrators are offered a clear picture of LOB access activities.
This feature provides LOB access statistics in dynamic performance views.
The benefit is reduced maintenance time required to convert to the LOB data type. This is especially beneficial to users of SAP and Oracle Applications.
Oracle Database 10g Release 2 includes a number of additional performance optimizations for long running queries, background activities, and other commonly used features.
This feature improves transaction throughput by reducing database commit time.
This feature offers better performance and easier configuration of checkpointing.
This feature enables any cache or object holding query results to ensure that it contains the very latest data.
The SPLIT command is used to split a single partition of an index organized table into two partitions. In earlier releases, the SPLIT command moved each of the rows from the existing partition into one of the two new partitions.
Index organized table performance is improved when a partition of an index organized table is split into two partitions. Rows are not moved if all of the rows in the existing partition fall into one of the new partitions.
The benefit is faster execution of SQL operations containing aggregations.
The following sections describe new features for Oracle Database 10g Release 2 in the Server Manageability area.
Before making changes, such as hardware or software upgrades, companies typically conduct extensive testing to validate the changes. However, when the change is made on the production system, unexpected problems are often encountered because the testing was not performed with a workload that was a good representation of the production environment. Tools are available in the market to construct "synthetic workloads" involving multiple users. However, they are not able to adequately simulate the complexities of a real-world workload, such as interactions between concurrent activities and unpredictability of the workload profile.
The Database Replay feature addresses this need by enabling users to perform real-world testing by capturing the actual database workload on the production system and replaying it on the test system. It also provides analysis and reporting to highlight potential problems (for example, errors encountered and divergence in performance) and recommend ways to remedy the problems. In this release, you can begin to perform real-world testing by capturing the actual database workload on the production system. The ability to replay the workload, however, is only available in Oracle Database 11g (11.1). This has been done so that when you upgrade from Oracle Database release 10.2.0.4 to 11.1, you can use the Database Replay feature to help with the upgrade.
A number of new features in database and Enterprise Manager make initial database creation and setup even easier. Building on the work done in Oracle Database 10g Release 1, Oracle Database 10g Release 2 automates the configuration of more initialization parameters, such as multiblock read count. In addition, Enterprise Manager can now easily configure and manage a wide variety of advanced net services components, such as advanced security, network data encryption, and Transparent Application Failover (TAF).
This feature tunes the system to provide maximum undo retention for the fixed size undo tablespace. This further simplifies Automatic Undo Management, as you no longer need to configure the UNDO_RETENTION parameter even for flashback purposes.
Configuration of Oracle advanced security strong authentication.
Configuration of network data encryption and integrity for Oracle servers and clients.
Configuration of secure socket layer authentication.
Transparent Application Failover (TAF) configuration.
Search functionality for Listeners and Net Service names.
Editable location of tnsnames.ora for import to Oracle Internet Directory.
Listener target support for IMAP and POP presentation.
Rename Net Services names.
Oracle Net Services provides enterprise-wide connectivity solutions in distributed, heterogeneous computing environments. It is convenient for the DBA to manage the various components of Oracle Net Services in the Enterprise Manager Web console, where all other aspects of Oracle Database are managed.
The DB_FILE_MULTIBLOCK_READ_COUNT parameter controls the amount of block prefetching done in the buffer cache during scan operations, such as full table scan and index fast full scan. The value of this parameter can have a significant impact on the overall database performance. This feature enables Oracle Database to automatically select the appropriate value for this parameter depending on the operating system optimal I/O size and the size of the buffer cache.
This feature simplifies manageability by automating the tuning of DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter.
Enhancements to Intelligent Self-Management Infrastructure enable more accurate collection of workload and performance statistics, allow quick detection of causes behind the change in system behavior across two comparable time intervals, and make it possible to easily diagnose transient performance problems. Additionally, Enterprise Manager now allows monitoring and troubleshooting of slow or "hung" systems by retrieving necessary information directly from SGA whenever a normal connection is not possible. The alert thresholds can now be configured to automatically adapt to changes in system behavior thanks to Enterprise Manager's new dynamic baselining capability.
Dynamic statistical baselines can significantly improve the accuracy of performance alerting while also reducing exposure to the false positives commonly incurred under fixed threshold schemes.
System-level activity for any duration in real-time, recent real-time, and historical time
CPU-level and Wait Class-level activity for any duration in real-time, recent real-time and historical time
Ability to run Automatic Database Diagnostic Monitor (ADDM) on demand
Display of Alert thresholds in the Performance pages
These enhancements greatly increase the ability of a DBA to quickly detect performance problems both proactively (using alerts, real-time data, and ADDM) and reactively (using recent real-time, historical data, and ADDM).
Enterprise Manager provides screens for monitoring slow systems. These screens are populated using the Direct SGA Attach mechanism. An intelligent processing layer transforms the collected raw statistics into useful information that can be used by DBAs to efficiently perform root-cause analysis and take appropriate action.
This feature makes it easier for DBAs to manage databases. In particular, it facilitates analysis of slow systems, and detection and analysis of hung systems. In both cases, the feature provides access to diagnostic information that would otherwise be impossible or extremely difficult to retrieve.
This feature greatly increases the ability of a DBA to diagnose problems related to a particular database session, as it permits the DBA to go back in time and view the history of the session activity.
When performance information is normalized by the baseline context, it makes it easier for the DBA to detect if something unusual is happening. The definition of "unusual" depends on using a baseline as the measure of "normal."
Enterprise Manager can now collect key performance statistics of the database without making SQL queries. This involves attaching to the SGA directly and reading statistics from the shared memory. The data collected by these direct SGA reads is used to populate the Enterprise Manager database performance screens.
When a database is hung or slow, it is typically facing severe contention in resources. Using SQL to collect performance statistics at such a time is not acceptable.
This feature rationalizes some of the system statistics especially those concerning I/Os.
This feature simplifies manageability by providing a more accurate view of recent system activity. This information helps DBAs investigate transient problems due to waiters or blockers that are not detected during a periodic performance analysis.
The intelligent self-management infrastructure has been enhanced to make Automatic Database Diagnostic Monitor (ADDM) analysis more accurate in the areas related to CPU, paging, and integrated cache. In addition, the scope of ADDM has itself been broadened to include more server components such as Streams, AQs, RMAN, and RAC. These enhancements allow ADDM to detect performance bottlenecks more accurately and provide better recommendations to resolve them.
This feature enhances manageability by making the database resident, self-diagnostic engine more powerful and accurate.
You can now externally monitor the progress of advisor tasks execution by querying the V$ADVISOR_PROGRESS view.
This feature enhances the End-to-End Performance Monitoring functionality introduced in Oracle Database 10g Release 1. The usability of SQL tracing has been improved by allowing a complete programmatic access at the session level. In addition, the sessions with tracing enabled can now be identified from V$SESSION view and a new dynamic view DBA_ENABLED_TRACES has been introduced to view the database and instance level settings. Also, a new procedure has been added to DBMS_MONITOR package to provide a single switch for enabling and disabling SQL tracing for all sessions.
This feature enhances manageability by adding ease-of-use functionality to the End-to-End Performance Monitoring feature introduced in Oracle Database 10g Release 1.
This feature enhances manageability by making it easier to diagnose transient performance problems. This feature is intended to help administrators diagnose transient problems that are not detected during a period performance analysis.
This feature allows users to generate Automatic Workload Repository (AWR) reports identifying differences between two pairs of snapshots. This report is helpful to users in better understanding changes in workload and correlating it to performance problems pointed out by ADDM or validating tuning actions as a result of implementing ADDM's recommendations.
This feature enhances manageability by enabling simpler and more accurate performance diagnosis.
This feature allows users to customize how the top resource consuming SQL (TOP N SQL) statements are collected in the Automatic Workload Repository (AWR). By default, Oracle uses a set of predefined values to determine how many SQL statements should be collected for different measurement criteria. You can now change these values or direct AWR to capture all the statements in the cursor cache.
This feature enhances manageability by providing a more flexible mechanism to track the top resource-consuming SQL statements. Using this feature, administrators can capture a large number of SQL statements for historical performance trending and analysis purposes. This feature is also useful for SQL Workload performance comparison across major change activities such as database or application upgrade.
This release expands the manageability capabilities for Automatic Storage Management (ASM) to both the GUI and command-line interface to provide the database administrator with greater flexibility.
Adding an additional means by which to access and manage files within ASM makes it simpler to manage. The ASMCMD interface provides easy manipulation of files within Automatic Storage Management (ASM) disk groups.
ASM ALTER DISKGROUP command, which results in a rebalance, now has the option of specifying the option to WAIT. This enables multiple actions to be done at one time with the rebalance process not starting until a later time. The Power_Limit can also be set to a value of 0 which will result in a wait before rebalance is started.
This makes it easier to manage the disks and rebalance process in ASM. The WAIT keyword allows a script that adds or removes disks to wait for the disk group to be rebalanced before proceeding.
Additional ASM enhancements provide information about the status and space usage of ASM to the DBA. Adding two new fixed V$ASM views provides more access for Enterprise Manager to also reflect status and usage of disk space.
These enhancements make it easier to monitor and manage ASM disks. Enhancements to existing views and Enterprise Manager make information about check disk group and free space more concise and easier to understand.
The batch DELETE operation prevents the unnecessary rebalancing that occurs when disks are deleted one by one. It also improves usability and enhances the existing support in Enterprise Manager for ASM.
This release expands the capabilities of ASM to support mixed Oracle environments. For example, support for multiple Oracle Database versions and support for multiple database-to-ASM connection types.
When mixing software releases, Automatic Storage Management (ASM) functionality reverts to the functionality of the lowest release being used. For example, a 10.1.0.2 database talking to a 10.1.0.3 ASM instance will not be able to exploit any new features in 10.1.0.3 and behaves as if it were talking to a 10.1.0.2 ASM instance. Conversely, a 10.1.0.3 database talking to a 10.1.0.2 ASM instance behaves as if it were a 10.1.0.2 database.
Two new text columns, SOFTWARE_VERSION and COMPATIBLE_VERSION, have been added to V$ASM_CLIENT.
This new support provides more flexibility for ASM in a multirelease database environment. To ensure that ASM transparently supports both older and newer software releases of the database, both forward-compatibility and backward-compatibility are maintained between all Oracle Database 10g Release 1 and all Oracle Database 10g Release 2 releases, and for as long as possible with later releases.
This release provides enhanced protocol for connections between the database and Automatic Storage Management (ASM) for greater connectivity and compatibility. This also allows one clusterwide ASM instance to manage storage for many databases using several types of connection mechanisms.
The benefit is greater flexibility for ASM to manage a clustered pool of storage. Allowing more compatibility for database to ASM connections enables easier consolidation and compatibility of shared storage with a cluster.
This release provides a user interface that allows the upload and extraction of files into or out of an ASM managed storage pool.
This feature extends ASM to support environments that require movement of application data.
The self-diagnostic engine of Oracle Database 10g, Automatic Database Diagnostic Monitor (ADDM), has been enhanced to make its analysis more accurate in the areas related to CPU, Paging, and Integrated Cache. In addition, the scope of ADDM has itself been broadened to include more server components such as Streams, AQ, RMAN, and RAC.
When Streams is used in a single database, memory is allocated from a pool in the System Global Area (SGA) called the Streams pool. The Streams pool contains buffered queues and is used for internal communications during parallel capture and apply. The automatic shared memory management feature is enabled by setting the SGA_TARGET parameter.
The Streams pool is now an automatically sized SGA component to improve manageability.
Automatic Instance Tuning capabilities of Oracle Database 10g have been further strengthened by enhancements made to Automatic Shared Memory Management and Database Resource Manager features. The Automatic Shared Memory Management feature now automatically manages the Streams pools component of the System Global Area (SGA). Also, the Database Resource Manager is now enabled out-of-the-box and new enhancements have been made to simplify its monitoring and tuning.
Starting with this release, the Database Resource Manager will be enabled by default.
Oracle Database provides a memory manager to internal subsystems for their dynamic memory allocation needs. Virtually all allocation and deallocation requests include a comment which can be used to maintain statistics, and which Oracle Support Services can use to match memory usage to source code. This feature improves the ability to diagnose memory leaks and out-of-memory errors by maintaining more information, making component usage visible to DBAs and developers, and allowing Oracle Support Services to see memory usage in a more understandable format.
This feature enhances the information needed and improves the ability to diagnose memory allocation by Oracle Support Services.
The Automatic Shared Memory Management feature now automatically sizes the Streams pool based on workload demand. The internal algorithm of the feature has also been enhanced to make the transfer of memory across SGA components more effective.
Enhancements to the Automatic Shared Memory Management feature further strengthen the manageability enhancements introduced in Oracle Database 10g Release 1.
A number of new enhancements are being introduced to help assess the effectiveness of Database Resource Manager and tune Resource Plans if required. The Database Resource Manager is now fully integrated with the database self-management infrastructure (for example, ADDM, Automatic Workload Repository and Active Session History, Server Generated Alerts) in order to benefit from the self-management capabilities of Oracle Database 10g. In addition, a number of new statistics have been introduced and it is now possible to monitor the functioning of Database Resource Manager at the session level.
These features enhance manageability by making it easier to monitor and tune the functioning of the Database Resource Manager.
Oracle Database 10g Release 2 further enhances the Automatic SQL Tuning capability introduced in Release 1. SQL Profiles can now be shared among SQL statements having different literal values and SQL Tuning Sets can now be transported across different databases to enable application performance comparison during upgrade testing. SQL Access Advisor has also been enhanced to make it interruptible and provide recommendations about function-based indexes.
This feature allows the transfer of SQL workloads from one database to another for remote performance diagnostics and tuning. When poorly performing SQL statements are encountered on a production system, you may not want to give developers direct access to it for the purpose of investigation and tuning. This feature allows the DBA to transport the offending SQL statements to a test system where they can be analyzed and tuned.
This feature greatly increases the ability of a DBA to diagnose problems related to a particular SQL statement, as it permits the DBA to go back in time and view the history of the SQL statement's execution plans.
The benefit is that recommendations can be viewed immediately rather than having to wait for the process to complete, which could be long for a complex database and workload.
This enhancement provides more indexing types recommended by SQL Access Advisor.
SQL Profile has been enhanced to allow applications to use literal values rather than bind variables.
This view has been added for improved performance, monitoring, and diagnostics.
Space management operations have been further simplified in Oracle Database 10g Release 2 as it can automatically identify tables and indexes that can be compressed to reclaim wasted space. It is now possible to drop empty datafiles and the Automatic Segment Space Management (ASSM) feature is now enabled by default.
All new tablespaces created using default attributes benefit from the manageability and performance advantages of ASSM over Freelist-based space management.
The key benefit is that the database administrators are proactively made aware of segments that need reorganization. They do not need to run Segment Advisor to determine what segments have data fragmentation. The advice is already available on the Enterprise Manager home page for immediate implementation.
Online Segments Shrink capability is now extended to all types of segments. This will further reduce the need to perform other more advanced reorganization methods such as Online Redefinition.
This feature provides the ability to specify space pressure alerts based on the number of free bytes in the tablespace. Oracle Database 10g Release 1 provided alerts based on the percentage of the tablespace that was full; with the default being 85% for warning and 97% for critical alerts. For large tablespaces, you may want to specify thresholds in absolute values since a tablespace that is 97% full may still have substantial free space available which does not warrant an alert.
With this feature, DBAs can exactly specify how much free space must be available in the tablespace before they get an alert.
If a DBA adds a datafile in the wrong location or to the wrong tablespace, this feature provides a simple way to correct the error by dropping the datafile.
The following sections describe new features for Oracle Database 10g Release 2 in the Windows area.
ODP.NET has been enhanced with greater ability to intelligently use RAC. When optimizing their application, administrators can allocate the percentage of database connections for each instance, as well as have Oracle automatically detect downed instances and clean up bad connections. Additionally, Oracle clients provide RAC instance affinity for distributed transactions to make managing these transactions easier, especially during failover.
ODP.NET connection pool subscribes to notifications that indicate whether nodes and services are available or unavailable. Based on these notifications, the ODP.NET connection pool can free idle connections that were connected to bad nodes and create new ones to healthy nodes, if possible.
The fast connection failover feature is a proactive way of migrating connections from downed services or downed nodes to responsive ones, leading to better availability with a minimal amount of application management needed.
The ODP.NET connection pool subscribes to notifications provided by the RAC Load Balancing Advisory. These events provide information on the current service level provided by each instance and a recommendation on how connections are to be directed in a RAC database for a service that is enabled on multiple instances in the cluster. Connections are dispensed or removed from the connection pool based on the current service level.
The load balancing feature is a proactive way of routing work to responsive nodes based on the performance data gathered by the database. This feature allows efficient utilization of database resources to ensure optimum performance by limiting bottlenecks.
Integration with Microsoft Development Tools and implementation of MS Data Access specifications make it very easy for Windows Developers to develop with Oracle Database.
Enhancements in this area include:
Integration with Visual Studio
Added .NET Framework 1.1 functionality
Database change notification
Support for derived parameters
Enhanced connection pool administration
Control of query cancellation
Support for server-side ODP.NET
This release provides a wizard in Visual Studio that automatically handles the data type mapping and PL/SQL wrapper code generation. This feature provides a way to deploy the built assembly into the database.
Integration with Visual Studio makes it easy for developers to build and deploy CLR stored procedures into an Oracle Database.
The HasRows property indicates whether a result set is empty unless the application requests a row. The EnlistDistributedTransction method can explicitly decide when and in which transaction it wishes to participate. In .NET Framework 1.0, connections were only allowed to enlist at connection time to the transaction context that was associated with the component and thread. In .NET Framework 1.1, the transaction context can be passed around components (through the ITransaction interface) and have connections enlist in them explicitly.
The HasRows property and EnlistDistributedTransction method that were added to the .NET Framework in its 1.1 release are now supported by ODP.NET, allowing developers to use this additional functionality in an easy-to-use manner.
Oracle Database Change Notification Service enables applications to receive notifications when there is a change in the query result set of a statement. Using the Database Change Notification Service, the application can specify a notification request for a statement execution, and create a notification registration using the notification framework. When there is a change in the query result set, the notification framework notifies the application about the change. Based on the information provided by the notification framework, the application can then respond to the notification accordingly. For example, the application might need to refresh its own copy of the data for the query result set that is stored locally in the application.
An application can utilize Oracle Database Change Notification Service to be alerted when a database change occurs that affects the results of a query. The changes are propagated to the client to ensure that an accurate, up-to-date data set is used by the user.
ODP.NET supports the DeriveParameters method on the OracleCommandBuilder which populates the parameter collection for a command, given the stored procedure or function name. This feature is intended to be used at design time, not runtime, as it incurs a database round trip to execute.
The DeriveParameters method automatically populates metadata information needed to bind parameters to a stored procedure or function, saving the user from binding this information explicitly.
This feature provides a way for applications to clear connections in one pool or all pools in an application domain. It provides the facility to refresh the connections and clear the pool of any invalid connections. It also allows applications to destroy the pool all together.
This feature allows developers to explicitly create or destroy connection pools or reset the attributes of existing connection pools to provide greater control in managing ODP.NET connection resources.
Developers can control how long to wait for a command to complete before canceling the call, providing greater control over an application's service level.
A .NET stored procedure or function residing in Oracle may need to access the database. The usual interface to Oracle from .NET is the Oracle Data Provider for .NET. But if the .NET procedure or function is running on the server, there are certain optimizations and limitations that apply. For example, the implicit database session can be used to improve performance. Other features, like connection pooling are not supported.
ODP.NET includes a number of improvements that make queries and LOB data retrieval performance faster. Query metadata is now cached so that repeated queries can be executed faster because statements only need to be parsed once. When retrieving LOBs, Oracle has reduced the number of database round trips necessary to retrieve the data to the client.
ODP.NET has been improved to make fewer round trips when retrieving LOB data:
To ensure full LOB functionality when InitialLOBFetchSize is used.
To allow InitialLOBFetchSize to fetch greater than 32 KB of data in the first round-trip.
LOB data retrieval now requires fewer database round trips, which improves performance. Additionally, LOBs now have fewer limitations making them easier to use and tune for performance.
This feature provides and manages a cache of statements for each session. In the server, it means that cursors are ready to be used without the need to parse the statement again. Statement caching can be used with connection pooling and with session pooling, and improves performance and scalability. It can also be used without session pooling.
When identical SQL statements are executed, this feature improves performance by preventing parsing of the SQL statement repeatedly. The SQL statement is parsed just once.
This feature improves and enhances the security of Oracle Database and other components on windows.
ODP.NET developers can more easily use Oracle's VPD support by allowing the Client Identifier, which acts like an application user proxy, to be set at the connection string level.
This feature creates a secure Win32 session environment for the Oracle instance so the process and it's internal state are not accessible from an external environment including sessions with the same privileges.
This enhancement prevents older clients from connecting when they are running on the same machine as the database.
This feature improves security for Oracle on Windows.
This section describes new and changed Workspace Manager features for Oracle Database 10g Release 2 (10.2). See the Oracle Database Workspace Manager Developer's Guide for additional information.
Note:The title of Oracle Database Application Developer's Guide - Workspace Manager has changed to Oracle Database Workspace Manager Developer's Guide.
The following are new Workspace Manager system parameters for this release:
The following new options are available for the
alter_option parameter of the
REBUILD_INDEX, which has the following related new
parameter_options parameter keywords:
USE_WM_PERIOD_FOR_VALIDTIME (only one of which can be specified in each call to the procedure)
validTimeRange parameter (
WM_PERIOD DEFAULT NULL) has been added for the
EnableVersioning procedure. With this parameter, if you enable valid time support when you version-enable a table, you can specify an initial valid time range.
USER_REMOVED_WORKSPACES views contain information about workspaces that have been removed during a
RemoveWorkspace operation or a
MergeWorkspace operation in which the
remove_workspace parameter value was
true, and while the value of the Workspace Manager system parameter
You can use the
MergeTable procedure to apply changes to multiple tables (all rows or as specified in the
WHERE clause) in a workspace to its parent workspace. To specify multiple tables, specify them in the
table_id parameter and separate the names with commas (for example,
'table1, table2'). In previous releases, you could specify only a single table name.
WORKSPACE_ID column has been added to the
You can specify user-defined hints, which modify (and thus override) default optimizer hints, with the goal of improving the performance of SQL statements executed by the
DBMS_WM package on a specified version-enabled table or all version-enabled tables. To add a user-defined hint, use the new
AddUserDefinedHint procedure; to remove a user-defined hint, use the new
You can add, merge, and split table partitions in a version-enabled table by using the
alter_option parameter to the
SET NULL foreign key constraints are now supported, and any such constraint is reflected in the
ALL_WM_RIC_INFO metadata view with a row for which the
DELETE_RULE column is set to
N. (The value in this column does not affect behavior in any way; it only displays metadata about the table.)
If you need to perform DDL operations on a version-enabled table in an Oracle Label Security (OLS) environment, you can use the
disable_table_policy procedures of the SA_POLICY_ADMIN package on the skeleton (_LTS) table, and the changes will be transferred to the version-enabled table.