|Oracle9i Database New Features
Release 1 (9.0.1)
Part Number A90120-02
This chapter discusses Oracle9i Database new features and describes their relevance to the Oracle9i Application Server and Oracle9i Development Suite. New features are organized to report advancements in the categories listed below.
The Oracle9i Database contains important new features that optimize traditional business applications, facilitate critical advancement for internet-based business, and stimulate the emerging hosted application market. New Oracle9i Database features deliver the performance, scalability, and availability essential to hosted service software made available to anyone, anywhere.
The Oracle9i Database offers new transparent, rapid growth clustering capabilities, along with powerful and cost-effective security measures, zero-data-loss safeguards, and real-time intelligence to deliver the power needed in today's dynamic marketplace.
Oracle9i extends Oracle's leadership in internet database availability, which is critical for any e-business application. Key areas in Oracle9i include the following:
Oracle9i sets a new standard for high availability with the introduction of four powerful new features that protect against the most disruptive event an e-business can encounter: downtime. New features provide protection in the following areas:
Oracle9i offers many new features for disaster recovery. Key areas include the following:
LogMiner provides the information necessary for performing recovery operations, tuning, and capacity planning. The Oracle9i LogMiner utility makes it possible to query redo log files, both online and archived, through a SQL interface. LogMiner provides optional tracking of DDL statements, the ability to limit queries to committed transactions, and the ability to perform queries based on actual data values.
LogMiner has also been enhanced in Oracle9i to provide comprehensive log analysis for additional datatypes. LogMiner now supports the following:
LogMiner also displays the primary key and supports queries on the logs based on the content of changes (for example, show all changes to an employee named Smith).
A new graphical user interface as well as other database features make the product easier to learn and use.
The Oracle LogMiner Viewer provides an easy-to-use graphical user interface to the Oracle9i LogMiner. The LogMiner GUI may be used to query online and archived redo log files to analyze the activity that has taken place in a database. Users can select redo log files, specify filters to be applied to the data, view query results and save the query and results for future use. Oracle LogMiner View is a component of Oracle Enterprise Manager.
Oracle9i Database Administrator's Guide for more information about LogMiner
The Oracle9i Data Guard gives customers new tools for fast recovery in disaster situations through complete, simple, and fully automated "graceful switchover," which makes the former primary usable as the new standby and allows the production processing to be switched back at any time. Data Guard is developed to address real-world failure scenarios, minimizing the need for administrative intervention.
The Oracle9i Data Guard Broker introduces an important advancement in physical standby database management through automated monitoring and control features. Previously, switching to the standby database had been a highly complex administrative task, and switch-back had been very difficult. Data Guard now presents a primary and its standby system as one environment, thus unifying configuration, monitoring and control. Oracle9i Data Guard continually monitors both the primary and standby databases.
Oracle9i Data Guard maintains a physical standby database that assures no loss of data during log transport. Log file updates are synchronously written directly from the primary database to the physically identical standby database, making it fully up-to-date at the point of failure in any disaster recovery situation. During log file updates, only log entries for the current transaction are stopped, instead of the entire log file. This "no-loss" disaster recovery solution makes third-party products that mirror online redo logs unnecessary.
Delayed mode guards against database administrator mistakes by enabling a time lag that protects against the application of corrupted or erroneous data from the primary to the standby database. Under most circumstances, the standby database automatically applies achieved redo logs by default when they arrive from the primary database. Delayed mode bypasses this default to protect data in the standby database.
Oracle9i provides fast recovery with products improved by the following new features:
Clustered architecture provides better availability than a single-node configuration by diminishing the server as a single point of failure. In a two-node clustered configuration, a system crash on one node allows the application to continue running on the surviving node. Using Oracle9i Real Application Clusters, failover of operations from a failed primary node to a secondary node occurs automatically within seconds, minimizing impact on application and data availability.
Oracle Application Clusters Guard, formerly Oracle Parallel Failsafe, is an enhanced configuration of Oracle9i Real Application Clusters. It tightly integrates enhanced recovery features with the cluster framework of the platform to provide a configuration that leverages the best high-availability technology each partner has to offer.
The Oracle Application Clusters Guard architecture is designed to build on the strengths of traditional high-availability solutions and provide the following functions:
Oracle9i Fail Safe Configuration for Windows provides the high availability and protection from system failures e-businesses demand on Windows NT and Windows 2000 clustered architectures. Oracle9i Fail Safe provides failover processes for database and application servers in both 2- and 4-node Windows NT and Windows 2000 clusters.
Oracle9i introduces an enhancement to Fast-Start Fault Recovery that allows database administrators to specify the expected mean time to recover (MTTR), which is the expected amount of time Oracle takes to recover a single instance.
A common challenge facing database administrators is the trade-off between identifying the cause of a failure and ensuring that normal service is resumed as soon as possible. By invoking Flash Freeze, the database administrator can take a diagnostic snapshot of the entire system at the time of failure, quickly restart the database, then make a diagnostic analysis offline.
Oracle9i eases the challenge of failures or downtime due to human errors, including erroneous or out-of-sequence updates.
Oracle9i Flashback Query lets users and applications query data as it appeared in the past. Using this flashback parameter, a user can specify a date and time, then issue standard queries on the data as it appeared on the specified time. Once the errors are identified, undoing the updates is a straightforward process that can be done without intervention from the database administrator. More importantly, the restoration can be achieved with no database downtime.
Identification of updates is also achieved by analyzing of the database log files. Oracle9i LogMiner is a utility that allows log files, online or archived, to be read, analyzed, and interpreted using a SQL interface. By using Oracle9i LogMiner, database administrators can now examine all updates to the database, including all data manipulation, definition, and administration commands.
Certain large, long-running operations such as data uploads and complex update processes may run out of resources (disk space, for example) before completion. Unsuccessful completion of long-running operations can be time-consuming to resolve. Oracle9i addresses this challenge by enabling database administrators to suspend operations that cannot be completed. Once the operation has been suspended, the database administrator can resolve the resource issue, then allow the statement to resume from the point of interruption.
Oracle9i allows comprehensive planned maintenance operations, which previously required downtime, to run during normal usage.
Oracle9i provides the mechanism to redefine the table structures while keeping them online and fully accessible to users and applications.
Oracle9i also provides a simple mechanism to reorganize and redefine tables while keeping them online and available to application users. Oracle9i online table and index reorganization reduces planned downtime, increases application availability, reduces disk fragmentation, and improves application performance.
With Oracle9i, all table indexes can be created and re-created online. With this capability, users can continue to run their applications during table index creation.
Oracle9i database parameter settings that affect database server memory use can now be reset online. This allows the database administrator to take databases offline and then restart them for parameter settings to take effect.
Oracle9i contains a new online reorganization and redefinition architecture that allows much more powerful reorganization capabilities. Administrators can now perform a variety of online operations to table definitions, including online reorganization of heap-organized tables. This makes it possible to reorganize a table while users have full access to it.
Oracle9i now allows an online
SELECT operation. In this new architecture, contents of a table are copied into a new table. While the contents are copied, updates to the original table are tracked by the database. After the copy is made, updates are applied to the new table.
After the updates are applied, indexes can be created on the new table. After indexes are created, any additional updates are applied and the result table replaces the original table. The table is only locked in exclusive mode, very briefly, at the beginning and end of the operation, when the dictionary data is updated.
This new online architecture provides the following capabilities:
Administrators can also rapidly quiesce the database to perform operations that demand no active transactions. Also, with Oracle9i, the buffer cache and the shared pool can be resized dynamically. Oracle9i can also validate the structure of an object (
VALIDATE) while the object is online and accessed by users.
Oracle9i includes better prevention and improved handling of log file corruption, reducing the risk of extended downtimes due to these failures. It is able to restore the database to a consistent state after log corruption is detected during recovery. If corruption needs to be repaired through media recovery, a new block media recovery feature allows only the corrupt blocks to be recovered while the remainder of the table is online.
Oracle9i can also recover from crashes more quickly using a new two-pass recovery algorithm that ensures that only the blocks that need to be processed are read from and written to the datafiles. A new time-based mean time to recover (MTTR) parameter also makes it much easier to set a limit on crash recovery time.
Oracle9i also includes improved diagnosis of a failed instance and allows diagnostics to be read after recovery on the failed state. This helps diagnose the cause of the failure after its first occurrence, rather than requiring users to set events that capture data in future failures.
For multi-node systems, Oracle9i provides faster failure detection, for instance, node and network failures, and reconfiguration for Oracle9i Real Application Clusters, reducing downtime due to a system fault.
Oracle Fail Safe for Windows has been enhanced to take advantage of multi-node clusters, using the enhanced functionality of Windows 2000. This allows for configurations where multiple databases on multiple nodes share a common backup node, reducing the cost of providing redundancy to multiple applications.
Oracle9i makes it possible to scale the most demanding e-business applications with intensive transaction loads, and thus support large populations of Internet users. Key areas of advancement include the following:
The new phase of Cache Fusion, as well as a new clustered file system, allows customers to take advantage of the scalability provided by Oracle9i Real Application Clusters with little or no performance overhead. Oracle9i Real Application Clusters replaces Oracle Parallel Server.
Because the full Cache Fusion implementation in Oracle9i eliminates the latencies associated with disk-based cache coordination, applications can scale effectively without having to be cluster-aware. By using the collective caches of all the nodes in the cluster to satisfy database requests, Oracle9i Real Application Clusters provide these unique capabilities:
In the Cache Fusion architecture, read requests can be served by any of the memory caches in the cluster database. When data is being updated, coordination between the caches of each server becomes necessary so that both the data being read and the data being updated are consistent and correct.
If the query request is served by a remote cache, then the block is transferred across the high speed cluster connection from one node's cache to another. This "fusing of the caches" happens automatically and is transparent to the application. This process is the key technology that provides fast, efficient scaling of Real Application Clusters.
Query requests can now be satisfied by the local cache or any of the other caches. This reduces disk I/O. Update operations do not require disk I/O for synchronization because the local node can obtain the needed block directly from any of the cluster database node caches. Expensive disk I/Os are performed only when none of the collective caches contain the necessary data and when an update transaction performs a
COMMIT operation that requires disk write guarantees. This implementation effectively expands the working set of the database cache and reduces disk I/O to dramatically speed up database operation.
Oracle9i Cache Fusion directly ships data blocks from one node's cache to another node's cache when there is read/read, write/read, and write/write contention. This builds on the previous Oracle8i Cache Fusion implementation that handled read/write contention. This is the first time in an off-the-shelf application that a cluster can be treated as a truly scalable single system.
Oracle9i Real Application Clusters has the following additional new features and improvements:
New shared memory capabilities, improvements in Java session support, and networking and Oracle shared server improvements substantially reduce the footprint required for each user on Oracle9i. This allows more users to be hosted on the same or larger hardware platforms.
Oracle9i includes several new features that enhance resource management.
The Database Resource Manager has been significantly enhanced in Oracle9i to allow for more granular control over resources. It adds features such as automatic consumer group switching, maximum active sessions control, query execution time estimation, and undo pool quotas for consumer groups. Administrators are able to specify the maximum number of concurrently active sessions in each consumer group. Once this limit is reached, Database Resource Manager queues all subsequent requests and runs them only after existing active sessions complete.
The automatic consumer group switching feature of Oracle9i allows the administrator to specify criteria which, if met, will cause the Database Resource Manager to automatically switch the consumer group of a long-running session: for instance, from a consumer group set up for OLTP operations, to one more suited for batch reporting.
Administrators are also able to set a maximum estimated execution time for each consumer group. The Database Resource Manager then estimates the approximate query execution time for each operation before it begins, and will abort the operation if it exceeds the limit specified.
With the undo pool quota feature, administrators can specify a maximum on the total amount of rollback data generated per resource consumer group. This prevents a rogue transaction from consuming excessive rollback space and thus impacting system operation.
Oracle9i continues to demonstrate leading performance in all relevant areas. Various performance measurement and tuning projects continue to measure, tune, and improve Oracle9i runtime performance in all areas.
A major focus of Oracle9i is improving performance in areas critical to e-business solutions. Support for native compilation and improved optimization of PL/SQL improves the performance of many of today's business applications, often significantly. The addition of memory and CPU costs to the cost-based optimizer results in better optimization plans, less resource usage, and faster overall performance.
in several areas has been eliminated or reduced, which improves performance on highly active systems. General I/O improvements, including self-tuning direct I/O, prefetching, and skip/scan row source operations on indexes also improve performance in data warehouse and OLTP environments.
For Java, improved garbage collection, better native compilation, increased object sharing, and session pinning have all improved the performance of built-in Java executing inside the database. JDBC and SQLJ performance improvements have also improved the performance of Java in the middle tier or on the client.
has been improved by the rework of database-to-database communication using OCI. In addition, specific network interface optimization, new improved virtual circuit I/O, and a unified event/wait model all substantially improve client/server communication performance. Improved distributed query optimization has also been built into the optimizer.
Oracle9i provides the most secure internet platform for protecting company information by minimizing risks through enhanced security for data, users and for companies.
The best way to minimize security risk is to provide multiple layers of security mechanisms, so that failure of a single mechanism does not compromise critical information. This concept is referred to as deep data protection, or security for data.
The Oracle9i database provides deep data protection through enhancements to the Virtual Private Database (VPD) and Selective Data Encryption capabilities, as well as through new technologies called Oracle Label Security and fine-grained auditing.
Virtual Private Database offers partitioned fine-grained access control whereby each user can only access rows of data that pertain to them. Oracle Label Security extends VPD functionality by offering label-based data access. By attaching access control directly to the data, security cannot be bypassed. This technology is ideal for application service providers who host multiple companies' data in the same database and therefore need to separate the data securely.
Selective data encryption ensures that very sensitive data in the database is hidden from database administrators, from privileged database users who may abuse their privileges, and from malicious users attempting to read data files from the operating system.
Fine-grained auditing keeps track of all database activity, including what statements users execute and the information that was returned. This hinders users from abusing their privileges since auditing tracks illegal actions. All of these technologies provide deep data protection, so that if one security mechanism fails, there are subsequent lines of defense.
Security mechanisms must be large enough to support thousands or millions of users over the Internet, yet still be practical to administer. Oracle9i offers a number of security features tailored to building internet-scale applications to provide security for users. These include enhancements to the database's proxy authentication, Public Key Infrastructure (PKI) support, and the new applications server feature for Web Single Sign-On. Together, these features allow enterprises to identify users throughout all tiers of the network. No longer does the middle tier establish a single connection to the database on behalf of the Web user. Proxy authentication creates multiple, scalable lightweight database sessions to carry the identity of the Web user, enabling fine-grained access control and fine-grained auditing of the Web user.
Web Single Sign-On is offered with the Login Server component of the application server's portal services. With Single Sign-On, users need to only maintain a single user name and password account for accessing all Web applications throughout the enterprise. System administrators have a single LDAP directory, the Oracle Internet Directory, to manage all access control information. By centralizing user access information, Oracle Internet Directory not only provides better security for the enterprise, but also lowers total cost of ownership.
Oracle also supplies PKI integration for easier deployment and management of PKI in the enterprise. For example, digital certificates issued by Entrust can be used to authenticate to the Oracle environment.
All of these technologies allow companies to increase access management without increasing administrative complexity.
A critical security requirement confronting the hosting environment is keeping data from different hosted user communities separate. One way of doing this is to create physically separate systems for each hosted community; however, this approach is costly.
The Oracle9i database greatly reduces cost for a hosting provider by offering mechanisms to allow multiple user communities to share a single hardware and software instance. This scheme ensures each user community's data is kept separate by using Oracle9i's Virtual Private Database and Oracle Label Security technologies.
Oracle9i continues to provide the most secure application development and deployment platform in the industry. Key areas include the following:
Three-tier security is enhanced by proxy authentication, including:
An extensible, secure application role can force a user to access the database through a middle tier. Another feature assures that user identities are maintained securely through all tiers of an application, with centralized user and privilege management in LDAP-based directories.
To aid the developer in using LDAP server functionality, enhancements in several APIs have been made. The PL/SQL API to LDAP (known as
DBMS LDAP) permits any PL/SQL code to perform any LDAP operation. This API is now supported through all database operation modes (Oracle Shared Server and dedicated server). In addition, a new API set has been added to the PL/SQL
UTL_HTTP package. New API functions have also been added to provide asynchronous operations.
Other new packages include
UTL_ENCODE, which is used to encode email messages, and the
UTL_URL package which performs exit and return functions on URLs.
UTL_SMTP packages have been enhanced to support transfer time-out in Oracle9i. The existing
UTL_INADDR package has been enhanced to support reverse domain name resolution (DNS) in Oracle9i.
Additional utilities to access directory structures have been added to the C API to LDAP. JNDI standard protocol extensions can now be recognized by Oracle Internet Directory.
Proxy authentication allows users without schemas to access the database through the middle tier.
Hosting security is provided through the following:
Virtual Private Database enhancements include partitioned fine-grained access control (security enforcement depending on which application accesses data) and connection pooling through a global or shared application context.
Fine-grained auditing offers selective audit of
SELECT statements, with bind variables, based on relevant column access, significantly enhancing per-user accountability.
Login Server (included with Oracle Portal 3.0) provides web-based Single Sign-On and integration with legacy applications. With Single Sign-On, users are able to authenticate (log in) once and gain access to multiple Web services, without having to remember credentials and authenticate again for each service.
Standards-based public key infrastructure (PKI) includes support for PKCS#12 certificates. They enable existing PKI credentials to be shared by an Oracle Wallet, thus reducing PKI deployment costs and increasing interoperability. Wallets can be downloaded from LDAP directories, supporting mobile users. The SSL libraries used in Oracle9i now also support hardware acceleration for improved performance. Oracle9i supports enhanced wallet password management.
Oracle9i supports LDAP technology to centrally manage network naming, easing deployments whether customers have one or hundreds of databases with tens of thousands or millions of users. Oracle9i supports Oracle Internet Directory, Novell Directory Services, and Microsoft Active Directory. Oracle9i supports native authentication using Microsoft Active Directory.
Improved user and security policy management is provided through Enterprise User Management enhancements, include management of password-based users in LDAP directories and a management tool for VPD policies. Security policies can be organized into groups. By referring to the application context, the Oracle server determines which group of policies should be in effect at runtime. The server enforces all the policies which belong to that policy group.
User passwords can also be encrypted using either standard or custom crypto schemes. Oracle Internet Directory supports an IETF LDAP standard for representing prefixed user passwords where the prefixes identify the crypto scheme used for hashing the password values. A default hashing mechanism may be chosen from a variety of standard schemes, including
SHA-1, and Unix "crypt." Values hashed by external agents may also be stored. This is useful when external authentication service agents want to use custom crypto schemes.
DBMS_OBFUSCATION_TOOLKIT now includes a secure random number generator,
GetKey. Secure random number generation is a very important aspect of cryptography; predictable cryptographic keys can easily be decrypted by a person or machine performing crypto analysis.
Administration of Oracle Internet Directory replication server has also been improved with the provision of new replication queue management and reconciliation tools. The replication queue management tool allows administrators object-by-object control over the elements in the human intervention queue, for the purposes of retrying object processing at will and deletion of objects from the queue. The replication reconciliation tool permits administrators to detect and correct inconsistencies among directory replicas.
A new web-based Oracle Internet Directory Self-Service Administration servlet enables users to administer their own personalized data over the Web. Directory administrators can restrict the set of attributes users are allowed to self-administer, including group memberships. Extended support for
ACLs governing user self-administration of membership allows authenticated users to add their own Distinguished Names (DNs) to membership of a LDAP group object or any object type that holds membership information, including roles and proprietary subscriber lists.
Several enhancements have also been made to increase the availability of directory services. Certification with certain limited Oracle9i Real Application Cluster configurations improves availability both for the front end, where the LDAP directory service and replication processes reside, and the back end Oracle RDBMS, where the directory data is stored. Support for logical hosts in clusters allows failover to a different physical host within the same cluster and also transparently supports continued availability of directory replication. New procedures allow for multi-node topology reconfiguration and upgrade with no directory service downtime.
Several key enhancements have further increased LDAP server scalability and performance. Multi-process support for higher-concurrency LDAP access has been improved through a more scalable directory metadata cache coherency protocol.
IETF-compliant support for LDAP referral objects enables partitioned LDAP directories. This allows delegated administration of physical directory segments and is critical for service providers and enterprises hosting large directories for a federation of autonomous organizations. By employing parallelism, the capacity of bulk-load, bulk-delete, and bulk-modify tools have been enhanced to handle much larger data sets.
Optimization of server-side caching at startup on group objects reduces LDAP server startup latency and improves performance of access control evaluations. Finally, the ability of Oracle Internet Directory to consult access control information has been enhanced significantly such that the evaluation decisions are made efficiently even when there are very large numbers of
ACL policies to be consulted.
Encryption enhancements include Java Cryptographic Architecture (JCA) and Java Cryptographic Extensions (JCE), supporting most popular algorithms for encryption and data integrity.
Oracle Label Security is a fine-grained access control product. It adds a special label to data rows, providing sophisticated and flexible row label security. It is built on the Oracle9i Virtual Private Database technology. Oracle Label Security is based on labeling concepts used by government and defense organizations to protect sensitive information and provide data separation. Application hosting, health care and other industries can also take advantage of data labeling to help solve security requirements in the Internet Age. For example, in application hosting, a subscriber label can be used to separate data among subscribers in the same application.
Oracle Label Security is enforced within the database, providing security even if the application is bypassed. Label provides a dimension of access control that is not easily achieved using existing application data. Oracle Label Security also includes a sophisticated policy management tool, to manage policies, labels, and user label authorizations. Oracle Label Security is an out-of-the-box fine-grained access control solution.
Oracle Policy Manager is an extension to Oracle Enterprise Manager that administers Oracle Label Security. The Oracle Policy Manager graphical user interface contains a tree structure that lists policies, along with their labels, authorizations, and protected objects.
Management is one of the key areas of improvement for Oracle9i. One of the major objectives of Oracle9i has been to make the database server inherently self managing/tuning. Features such as Automatic Undo Management, Automatic SQL Execution Memory Management and Automatic Segment-Space Management are some of the features that enable database administrators to delegate many day-to-day administrative tasks to the server.
Oracle9i's integrated system management tools create a complete view of all processes critical to the database and host, making it possible to quickly and completely assesses the overall health of an e-business infrastructure.
Oracle9i management advancements include the following features:
Oracle9i includes several new features that make the database server more autonomous and self-managing.
Oracle9i databases are capable of managing their own undo (rollback) segments. No longer will administrators need to carefully plan and tune the number and sizes of rollback segments or decide how to strategically assign transactions to a particular rollback segment. Oracle9i also allows administrators to allocate their undo space in a single undo tablespace with the database taking care of issues such as undo block contention, consistent read retention, and space utilization.
Memory management is another area which has been given significant attention in Oracle9i. Traditionally, administrators have needed to shut down the instance in order to grow or shrink System Global Area (SGA) components. Oracle9i introduces a dynamic memory management feature which allows for dynamically resizing the buffer cache and shared pool. It also includes a buffer cache size advice mechanism that predicts the performance of running with different sizes for the buffer cache.
Oracle9i provides transparent management of working memory for SQL execution by self-tuning the initialization runtime parameters that control allocation of private memory. This feature helps low-end users to reduce the time and effort required to tune memory parameters for their data warehouse and reporting applications, while high-end users are able to avoid memory tuning for individual work loads.
Several new features simply administration of the Oracle9i database.
Other traditional management areas of the database are also improved. With the introduction of a persistent parameter initialization feature in Oracle9i, parameter changes persist across database shutdowns and startups. This feature also allows the administrator to start the database from remote machines without a local copy of the parameter initialization file. This helps database performance tuning because parameter changes made by performance management tools, such as Oracle Enterprise Manager and changes made by internal self-tuning, now persist across shutdowns.
The Oracle Database Configuration Assistant has been redesigned to include saved definitions of databases in the form of templates. The templates may be used to generate databases. Oracle provides templates. In addition, users can create their own by modifying existing templates, defining new ones, or by capturing the definition of an existing database.
When creating a database with the Database Configuration Assistant, users can include Oracle's new Sample Schemas, or they may be added later add as an option. These schemas are the basis for many of the examples used in Oracle documentation.
Oracle9i introduces also the concept of Oracle-managed files. This simplifies database administration because it is not necessary for administrators to directly manage files comprising an Oracle database. Instead, Oracle9i uses standard file system interfaces internally to create and delete files as needed. While administrators still must be involved in space planning and administration, Oracle-managed files automates the routine task of creation and deletion of database files. Operating system files associated with a temporary file can be deleted. With the introduction of default temporary tablespace, the
SYSTEM tablespace is no longer used as the default storage location for temporary data.
Resumable Space Allocation, another feature introduce in Oracle9i, allows an administrator to temporarily suspend a large operation, such as a batch update or data load, if they start to encounter out-of-space errors. This allows the administrator to fix the problem, and resume the operation from the point of interruption without disrupting normal database operation.
Oracle9i also supports databases created with multiple block sizes and allows administrators to configure corresponding sub-caches within each alternative block size. This capability allows administrators to locate objects in tablespaces of appropriate block size in order to maximize I/O performance. It also allows tablespaces more easily to be transported between different databases, for example, from an OLTP environment to a data warehousing environment.
Oracle9i also allows for better control over database downtime by enabling administrators to specify the mean time to recover (MTTR) from system failures in numbers of seconds. This feature, coupled with more dynamic initialization parameters, helps administrators further improve database availability.
The execution plan of a SQL statement in the shared pool is now queryable through the view
V$SQL_PLAN. The data contained within this view is similar to that of
PLAN; the difference is
PLAN shows a theoretical plan should the statement be executed, whereas
V$SQL_PLAN shows the actual plan used to execute the statement.
Database administration is simplified because parameter changes made through performance management tools, such as Oracle Enterprise Manager and changes made by internal self-tuning parameters, now persist across shutdowns.
To ease backup and recovery operations, Recovery Manager in Oracle9i provides the following new features:
Oracle9i introduces many new features and enhancements that increase manageability and greatly expand functionality. Persistent RMAN settings can be created for automatic channels, channel parallelism, retention policies, backup options, and auxiliary filenames, and applied to any session. Thus, channel settings no longer have to be manually allocated.
Recovery Manager implements a recovery window, which is a new policy that controls when backups expire. Recovery Manager also automatically marks as obsolete all backups and archived logs no longer required to restore the database to a point in time during the recovery window. These features are designed to reduce the time and effort administrators spend in performing routine backup activities tasks through automation of the most commonly performed tasks.
Block media recovery can perform media recovery on individual blocks in a datafile while the datafile remains online. The block media recovery feature is only available with RMAN.
The new control file auto backup feature allows for restoring or recovering a database even when a Recovery Manager repository is not available. Recovery Manager in Oracle9i also features enhanced reporting and a more user-friendly interface.
Improvements in user-managed backup and recovery include the following:
In Oracle9i, Oracle Enterprise Manager (OEM) continues to provide easy-to-use management tools that support the new capabilities of the database and the entire e-business platform.
The Oracle Enterprise Manager graphical interface makes it simple to adopt and manage new components such as Oracle 9iFS, Oracle Internet Directory, Oracle Express, and Oracle iAS.
In Oracle9i, Oracle Enterprise Manager has the ability to connect to multiple target databases without having started the Oracle Management Server.
To further simplify management tasks, Oracle Enterprise Manager has been enhanced to include guided, expert diagnostics and problem resolution, as well as greatly enhanced reporting capabilities. Oracle has consolidated the wealth of expert knowledge and experience of its development and consulting teams into Oracle Enterprise Manager.
Advice and recommendations about properly configuring an Oracle environment, effectively monitoring its performance, and quickly resolving problems has been incorporated directly into the Oracle Enterprise Manager management tools. For example, administrators can instantly display a set of overview charts that show the overall health of their system, with indicators that automatically alert administrators to potential problem areas. Drilldowns from these problem areas then quickly guide administrators through the proper steps required to diagnose problems.
All essential management functions are also Web-based, so administrators can manage their systems directly from a Web browser. Tools such as DBA*Studio are consolidated into the integrated management console. Oracle Enterprise Manager can also publish detailed reports to a Web site, allowing administrators easy access to any systems management information they wish to publish.
In Oracle9i, Oracle Enterprise Manager also allows administrators to go beyond monitoring the performance of single systems like a database. In this release, administrators are able to monitor the response of their entire Oracle-based system and ensure that they are meeting the required business service level agreements. This capability is critical to users such as application service providers, e-business sites, or any business whose success depends on maintaining superior response time, performance, and availability of their IT systems.
Oracle Enterprise Manager allows administrators to monitor service levels and automatically alert to any degradation in performance. Extensive service level reports are also available, giving a complete picture of the performance of the system.
In addition to service level reports, reporting capabilities throughout all of Oracle Enterprise Manager have been significantly enhanced. A comprehensive set of predefined reports are included that document the configuration and health of the entire Oracle environment. Reports can be generated, for example, on the configuration of databases, the performance of applications over the last week, or the current load on the system. Customized reports can also be generated using a site's own data or by mixing and matching the predefined report topics that Oracle Enterprise Manager provides. These reports can be automatically generated and posted to a Web site for convenient access across the organization.
Oracle9i continues to offer the best development platform for e-business and traditional application development. Key areas include the following:
Oracle9i JVM (previously JServer) extends its support for Java 2 Enterprise Edition APIs and containers through the following features:
The Oracle9i JVM that is embedded in both Oracle9i and Oracle iAS allows reliable, flexible, scalable, and secure e-business applications deployment.
Oracle9i introduces the following new XML features in the server:
XMLType stores XML content natively and allows XML operations to be run from SQL.
XMLType enables non-native XML data to be treated as XML by allowing users to create an XML View over standard database tables, documents, or web content. Thus, the same high-performance access to XML data is available whether data is natively XML, or an artifact generated from existing data.
In response to the challenge of generating XML in bulk from a database, XML generation capabilities have been moved into the database and application server kernels and made available as built-in SQL operators. The kernel proximity of these operators ensures massively sustainable throughputs, enough for the largest content repositories or the busiest exchanges.
A universal content model for all kinds of data and documents can be created through a set of native Arrive data types, which can hold references to XML documents or fragments (inside or outside the database). Just as applications locate HTML files using URL, a set of native Arrive data types can locate XML content, native or generated, inside the database or outside, using Arrive. URI-Refs play a major role in creating database-backed content repositories, which can be used to be feed portals, archives, or other content management systems.
Oracle9i SQL Reference for information about datatypes
Oracle9i features several enhanced database operations to store XML through SQL and render traditional database data as XML. These functionalities are required to support business-to-business and business-to-customer e-business, packaged applications, and internet content management. The main area of XML support in Oracle9i is built-in XML Developer Kits (XDKs).
With Java pre-loaded and the C XDK linked into Oracle9i, developers are able to easily access World Wide Web Consortium (W3C) functionalities that generate, manipulate, render, and store XML-formatted data in Oracle9i. Also available in PL/SQL and C++, the XDKs offer XML/XSLT parsers, XML schema processors, XML Class Generators, XML Transviewer Beans, and the XSQL Servlet, providing basic building block features that allow developers to quickly enable their applications for XML.
SQL and PL/SQL have continued to be improved in Oracle9i to meet current development requirements.
Multi-language server side debugging has been added, allowing integrated development environments to debug both Java and PL/SQL within the same framework.
Support for inheritance and multilevel collections completes the modeling capabilities of the object-relational subsystem in Oracle9i. This makes it possible to build complex models inside the database. In addition, Oracle9i supports type evolution: certain changes may be made to object types even if instances of the types exist in the database. Both types of support make it easier to deploy complex applications in real-life environments.
New ANSI requirements are also supported, including support for the CASE statement, ANSI-compliant joins, and reserved name versioning. To aid migration to Oracle9i from other databases, scrolling cursor support has been added, and stored procedures can now return result sets that can be easily passed and pipelined between both database and client side processes.
The following datatypes are new for Oracle9i:
TIMESTAMP WITH [LOCAL]TIME ZONE INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
XMLtype,native XML datatype
SYS.ANYType, SYS.AnyData, SYS.AnyDataSet
MDSYS.SDO_GEOMETRY,new spatial datatype
ORDSYS.ORDImage -media type
ORDSYS.ORDVideo -media type
ORDSYS.ORDAudio -media type
The following built-in SQL functions are new for Oracle9i:
INTERVAL YEAR to MONTH
INTERVAL DAY TO SECOND
The following built-in SQL expressions are new for Oracle9i:
The following built-in SQL condition is new for Oracle9i:
The following top-level SQL statements are new for Oracle9i:
Oracle9i SQL Reference for new datatypes and SQL definitions.
Oracle9i includes a PL/SQL package,
DBMS_METADATA, which provides interfaces for extracting complete definitions of database objects. The definitions can be expressed either as XML or as SQL DDL. Two styles of interface are provided:
Other improvements to PL/SQL include:
CASEstatements and expressions
Oracle9i reinforces Oracle's lead as the platform of choice for organizations deploying on Windows 2000. Key areas include the following:
Oracle9i supports several versions of Microsoft Windows, including Windows 2000 and Windows NT.
Oracle9i supports enhanced integration with Microsoft Transaction Services and Internet Information Services. The PKI infrastructure and Single Sign-On capabilities in Oracle9i have also been well integrated with Windows 2000, Active Directory, and Microsoft Certificate Store.
Oracle9i Development and Deployment Improvements are as follow:
Oracle9i allows customers to store, manage and aggregate all types of multimedia content into a single database. Oracle9i significantly enhances the capabilities of the database to serve as a platform to create, manage, and deliver internet content. Key areas are:
Oracle9i includes the version 1.2 release of the Oracle Internet File System (Oracle 9iFS). Providing the best of both the relational database and file system worlds, Oracle 9iFS delivers an out-of-the-box file system with built-in capabilities unavailable in other file systems.
All of this functionality has the ease of the file systems' interface that users already know how to use.
Oracle 9iFS exposes all of its capabilities through Java, making it an excellent platform for building applications with content management applications.
With Oracle9i, 9iFS adds more content management features, such as WebDAV, an emerging standard for Internet collaboration.
Oracle 9iFS also will be incorporated into the file system interMedia's capabilities to index, search, and manipulate graphics, audio, and video.
Oracle9i includes enhancements to interMedia image, audio, and video support. Oracle9i greatly simplifies the ability to add multimedia formats, processing, and rendering by incorporating Java Advanced Imaging (JAI) into the database and providing support for the Java Media Framework (JMF) in interMedia. interMedia now supports PNG and EXIF image formats.
A new browser-based version of the clipboard supports insert, retrieve and annotate media objects in Oracle9i. Improvements to the image search capabilities and support for storage and delivery of streaming media with new streaming formats and plug-ins are also part of Oracle9i. In addition, interMedia's audio, video, and image media processing services are now accessible in native form through relational PL/SQL and JAVA interfaces.
Oracle9i also adds Internet search, powerful facilities to extract and index metadata from rich content, and the ability to search XML and catalog structures. With this release, all content in Oracle9i can be location-enabled and mobile-ready to allow it to be searched and delivered based on where the request is coming from (such as mobile phone and internet personalization criteria) or its location association.
New in the Oracle9i release, Oracle Ultra Search is able to search inside a database, as well as static HTML pages. Other search engines cannot see content inside a database, and would not be able to find documents, newspaper articles, etc., stored inside a database. Oracle Ultra Search unifies search areas across heterogeneous corporate repositories, Web sites, and groupware content. Oracle Ultra Search includes a Web interface, Web crawling, and search administration facilities to provide a unified interface for enterprise and vertical portal search applications.
In order to meet the demands of e-business applications, Oracle9i Text indexing has been improved in Oracle9i with a new indextype designed to perform very fast searches across volumes of short textual descriptions. This is ideal for catalog and metadata search as well as for searching of auction data and resumes. With Oracle9i, text search of nested XML elements, search attribute values, XPath query syntax, and other advanced XML structures are also all supported.
For e-business and mobile applications, the location capabilities in Oracle9i and Oracle Spatial have been greatly enhanced. Content stored in Oracle9i can now be associated with related location criteria and services. New support for online mapping, yellow pages, driving directions, traffic, and geocoding services allow online content to be merged with database content. Support for mobile devices and formats, such as WBMP, interactive voice response, microbrowsers and delivery through Oracle iAS Wireless, make Oracle9i a complete platform for the delivery of content for mobile applications.
Oracle9i adds the ability to create shared workspaces to support collaborative, long-duration projects. Workspaces support in-place, existing content and allow existing applications to run against the workspace view of the database transparently. Database content can be associated into a workspace and used for a specific application while the underlying transaction database continues to run unaffected. Multiple, concurrent database-backed projects can coexist simultaneously against different versions of content.
For e-business and mobile applications, Oracle9i and Oracle Spatial offer the ability to search, index, and deliver data based on the location attributes of the content or the proximity.
Support for mobile devices, protocols (WAP) and formats (such as WBMP) and delivery through Oracle iAS Wireless (formerly Portal-to-Go), make Oracle9i a complete platform for the delivery of content for mobile applications. Content stored in Oracle9i can now be associated with related location criteria and services.
Interactive Voice Response (IVR) formats used in mobile applications are now supported in interMedia. New support for online mapping, yellow pages, driving directions, traffic, and geocoding services allow online content to be merged with database content.
Oracle9i introduces new products and technologies essential to a successful e-business environment. Key advancements include the following:
Oracle9i includes a set of pre-developed, pre-tested, and pre-integrated business service objects, developed in Java and compliant with J2EE, that provide faster time-to-market for customers to build and integrate Web-based storefronts, exchanges and hosted applications.
The e-business service objects also provide a flexible architecture for building multitier Internet applications. Components built with the framework can reside within the Oracle9i Java Virtual Machine (JVM) or within the Oracle Internet Application Server (iAS) that supports Java technologies, such as Java Server Pages (JSPs) and Java Servlets.
The collection of Java-based services provided includes:
Oracle9i also provides an enhanced infrastructure for support of Internet service aggregation and syndication for portals, exchanges, and other Internet applications through Dynamic Services. Dynamic Services integrates the Oracle Internet Directory, XML services, and Advanced Queuing features with a policy and service management engine.
Queue information can be stored on an Oracle Internet Directory server, thus providing a single point of contact to locate the required topic or queue, without needing to know in which database the queue is located. The Oracle Internet Directory can also be used as the repository for event registration. Clients can register for database events even when the database is down.
Dynamic Services allows developers to easily manage, compose, reuse, and deploy local and remote Internet services (such as quotes, news feeds, exchange rates, or credit card processing) and database services (such as employee lookup, payroll, location services, or any PL/SQL procedure).
Oracle9i also provides a standard infrastructure that makes it easier for customers to integrate these storefronts, exchanges, and portals with other backend and external systems.
XML-based messaging over HTTP is supported, allowing external, across-firewall systems to be more easily integrated. In addition, non-database, Internet based consumers are also supported, allowing for greater flexibility in homogeneous environments. To enable messaging operations such as enqueue and dequeue to be performed across the Internet, a new XML-based Internet Document Access Protocol (iDAP) is provided that allows message operations to be requested across firewalls. Message security itself has been improved with digitally signed messages, and message non-repudiation is supported both for messages and iDAP requests.
Advanced Queuing agents can now also be defined in Oracle Internet Directory, providing a centralized, easy-to-manage, secure infrastructure for global messaging. In addition, global topic information can also be externalized in Oracle Internet Directory, providing a single place for the configuration and management for Advanced Queuing operations that span one or more systems.
Oracle Workflow now provides the Business Event System, a new application service the leverages the Oracle Advanced Queuing infrastructure to communicate business events among systems within an enterprise as well as between enterprises. The Business Event System includes the Event Manager, for registering subscriptions to significant events, and event activities for modeling business events within workflow processes. This support allows Oracle Workflow users to deal with business objects and e-business integration flows powerfully and flexibly, with minimal intrusion into core applications.
To support messaging between heterogeneous environments, the Message Gateway is provided in Oracle9i, that supports propagation of messages from Oracle9i to other proprietary message systems.
Oracle9i packaged applications enhancements provide new capabilities and new economies.
Oracle9i also significantly reduces the cost of developing and deploying applications globally on a single database instance.
Requirements for multi-geographical applications include named time zones and multi-language support through Unicode. The datetime datatypes
TSTZ are time-zone-aware. Datetime values can be specified as local time in a particular region (rather than a particular offset). Using the time zone rules tables for a given region, the time zone offset for a local time is calculated, taking into consideration Daylight Savings time adjustments, and used in further operations.
Unicode support has been greatly expanded in Oracle9i so that developers can easily find the right Unicode solution for their application needs. Developers can now develop fully globalized applications by setting up or migrating their database character set to UTF8 to support multiple languages simultaneously. National Character fields can be used to define columns that support one or more new languages for an existing monolingual database. Application developers can also use the Character Set Scanner utility to quickly identify potential issues in migrating an Oracle database to a new character set, thus easing migration of existing applications.
Oracle9i supports ISO 14651/Unicode Collation, and extended locale Unicode support including:
Linguistic collation capability in Oracle9i is greatly enhanced based on the new proposed ISO 14651 standard for multilingual collation. In addition, Oracle9i adds a set of new pre-defined linguistic sorts for Asian languages including Chinese, Japanese, and Korean. If customers have special needs that go beyond the extensive set of linguistic sorts provided Oracle9i, then they also have the flexibility of defining or customizing their own linguistic sorts by using a new easy-to-use graphical interface, Oracle Locale Builder.
Oracle9i provides an extensive set of locale definitions including 57 languages, 88 territories and approximately 200 character sets. If customers need to customize existing locale definitions, or create new definitions, the new Oracle Locale Builder provides an easy-to-use graphical user interface through which one can easily view, modify, and define the various locale-specific data.
Oracle9i continues to be the best platform for independent software vendor development, deployment, hosting, and migration. Features such as updatable scrollable cursors as well as ANSI -compliant
CASE statements, datetime data types, and join syntax facilitate migration of applications developed on other databases to Oracle9i. In addition,
LONG data types can be easily converted to
LOB data types by a simple
ALTER TABLE statement.
Enhancements to stored outlines and default column values allow improved deployment and hosting of packaged applications. With stored outline editing, queries can be tuned without having to change the packaged application code. For the customer whose environment has unique characteristics that might cause an outline to yield a less-than-optimal execution plan, the ability to make adjustments to the outline enhances the ability to support specific customer needs.
In this sense, stored outlines are made more adaptive as users can make finely tuned adjustments to the saved plan. Use of the
SYS_CONTEXT function to generate default column values simplifies implementation of Virtual Private Database security feature, providing a more scalable infrastructure for managing hosted applications.
Packaged applications also benefit from the numerous development, availability, scalability and security features provided in Oracle9i.
Oracle9i broadens the footprint of the relational database in a data warehouse by becoming a scalable data engine for all operations on data warehousing data, and not just in loading and basic query operations. As such, it is the first true data warehouse platform. Oracle9i provides new server functionality in analytic capabilities, ETL (Extraction, Transformation, Loading), and data mining.
As growth occurs in data volume and in numbers of end-users accessing the data warehouse, Oracle9i's data server scales to handle larger volumes of data and/or more users by the addition of new hardware resources. Oracle9i new features keep the data-warehouse simple to maintain as volume and activity increase.
Oracle9i exceeds the requirements of a relational database for data warehousing through:
Index-organized tables include these advancements:
Oracle9i introduces bitmap join indexes, which provide further improved performance for a specific class of join queries. A `join index' is an index structure which spans multiple tables and improves the performance of the joins of those tables.
Oracle's materialized views, which provide a mechanism for improving the performance of almost any type of query, have been enhanced in Oracle9i in important ways:
Oracle9i provides an automated mechanism for dynamically allocating runtime memory to each query. Up to 70% or more of the data warehouse server's physical memory is commonly allocated for runtime memory.
By automating the allocation of runtime memory, Oracle9i improves the overall throughput of the data warehouse and makes it possible to support larger numbers of users at the same levels of performance.
The automatic memory tuning feature ensures that memory-intensive queries receive sufficient memory, while memory-light queries are not given too much memory. By making more effective use of memory, Oracle9i increases overall query performance.
Oracle Partitioning delivers significant improvements in the manageability, availability, and query performance of large tables and indexes. Partitioning is a key technology for data warehousing, where large tables are commonplace. Oracle's partitioning capabilities have been enhanced in Oracle9i with the addition of a new partitioning scheme, list partitioning.
List partitioning gives data warehouse administrators precise control over which data belongs in each partition. For each partition, the data warehouse administrator can specify a list of possible values for the partitioning key of the rows in that partition. Each partition in a list partitioning scheme corresponds to a list of discrete values.
To increase efficiency and reduce the time taken to load and refresh critical data warehouses, Oracle9i provides support for external tables, which allow data from external systems to be quickly loaded into the database. External tables do not reside in the database and may be in an format, for which a driver is provided. The
CREATE TABLE... ORGANIZATION EXTERNAL statement specifies metadata describing the external table.
Other data load capabilities provided to increase data load scalability and reduce complexity include multitable
MERGE semantics. Both of these SQL enhancements allow more complex data loading to be processed within a SQL single statement, unlike the old way, where several process steps were required. With multitable inserts, data can be inserted into more than one table using a single SQL statement; this is more efficient than using multiple, separate SQL statements for each table.
Multitable inserts make SQL more useful for data transformations and conditional handling. An incremental refresh, also known as a
MERGE, of a table requires two tasks: new records will be inserted and existing records will be updated. Rather than requiring two separate steps, the new
MERGE statement allows both steps to be processing simultaneously within a single SQL statement. This new ETL functionality is also leveraged by Oracle Warehouse Builder.
Oracle9i Database Administrator's Guide for information about database administrator utilities
Oracle9i addresses growing workloads of established data warehouses through enhanced capabilities for managing larger numbers of users, and ensuring that:
Oracle9i introduces several new features to support these requirements.
The Database Resource Manager provides a mechanism for allocating the resources of a data warehouse among multiple populations of end-users. These groups, called Resource Consumer Groups, are specified by the database administrator, and then the database administrator can control how resources are allocated to each group.
The Oracle9i, the mechanism for allocating data warehouse resources among multiple populations of end-users allows the number of active sessions for each Resource Consumer Groups to be limited.
In addition, a simple query-governing capability allows the database administrator to specify the maximum estimated execution time for Resource Consumer Group.
Another significant enhancement allows the resource manager to automatically change the Resource Consumer Group of a given session based on criteria specified by the database administrator.
Oracle's query optimizer uses statistics about the objects in the database (such as the number of rows in each table). These statistics are gathered by database administrator's using the
In Oracle9i, the
DBMS_STATS package has been enhanced to make it easier for database administrators to gather the appropriate sets of statistics. It is now possible to automatically determine the appropriate sampling percentage as well as the appropriate columns for histograms. These enhancements simplify to database administrator's task in gathering accurate statistics.
The summary advisor has been enhanced in Oracle9i to enable it to make recommendations based on schema characteristics and previous workload history. It now supports a broader class of schemas, so database administrators can specify workloads as input to the summary advisor. Other enhancements have been made to materialized views so that it is now easier to manage environments.
Oracle9i permits an override of a default behavior, which permits an update of global indexes when partition maintenance is performed. Many table maintenance operations on partitioned tables invalidate (mark
UNUSABLE) global indexes. Without the override, the entire global index must be built, or, if partitioned, all of its partitions. Global indexes are now updated when partition maintenance is performed, thus minimizing normal maintenance.
Oracle9i for business intelligence goes beyond the primary requirements of performance, scalability, and manageability.
Oracle9i is designed to be a full data warehouse platform, leveraging the Oracle database as the scalable data engine for all operations on data warehousing data.
Oracle9i provides ground-breaking new functionality in Online Analytical Processing (OLAP) and Data Mining. Oracle9i OLAP is a scalable, high-performance OLAP calculation engine with fully integrated management and administration. Leveraging Oracle Express Server technology and Oracle9i's analytic SQL capabilities, Oracle9i OLAP provides a robust platform for delivering analytic applications.
Oracle has substantially enhanced both the functionality and performance of SQL to address the requirements of typical OLAP operations. Specifically, Oracle9i provides an extensive set of SQL capabilities for new types of analytic functions, as well as substantial enhancements for aggregation. Likewise, enhancements to materialized views includes optimizations for OLAP.
Oracle9i also includes new data mining capabilities. Based on Oracle's data mining (Darwin) product, Oracle9i provides personalization capabilities, which enable customers to implement accurate, real-time recommendations and personalization capabilities into their online operations. Oracle9i is the next step in tighter integration of data mining and the relational database, and includes in-database scoring along with the ability to manage data mining operations.
Oracle9i includes a number of miscellaneous features that increase the power and usability of the database server.
FETCHof long columns
Oracle Net supports the Virtual Interface (VI) protocol
CHARcolumn length semantics and Unicode
SDO_GTYPEelement of the
SDO_GEOMETRYtype has a new format that identifies the linear referencing dimension (if any).
SDO_AGGR_UNION, SDO_AGGR_BUFFER, and
SDO_GEOMETRYtype can be embedded in a user-defined data type.
UNITSsupport (for example,
"UNITS=mile") is provided for relevant Spatial functions and operators.
Oracle Text, formerly interMedia Text, includes new features that provide greater flexibility in building text query applications.
CTX_OUTPUTPL/SQL Package has procedures that allow logging of index file with rowid information, which is useful in debugging an index operation.