Type Your Title Here - Oracle



The Invisible Oracle: Deploying Oracle Database in Embedded Environments

Mughees A. Minhas, Oracle Corporation

Anuj Goel, Oracle Corporation

Introduction

Many businesses lack the necessary technical expertise for directly managing the database software on which their business applications run. Yet, these applications are critical to their business success. Small- and midsize businesses, in particular, have little to no IT or DBA staff, and typically lack the resources to acquire such expertise. As a result, they need business applications that can be easily installed, are self-managing, and are easy to use by non-DBAs. Often, these businesses buy an integrated, turnkey solution featuring an embedded database.

Embedded installations allow ISVs to build critical technology directly into their products to develop a solution targeted to the needs of this market. In these solutions, the database software is fully integrated into the business application or device such that the end-user has little or no knowledge that the database software exists. In essence, the database is completely invisible to the application user. This allows the ISV to gain greater control of the complete application — from installation, to management and support.

With Oracle 10g, Oracle has made large strides towards creating a truly embeddable database. This paper reviews the technical challenges associated with embedding a database and how Oracle 10g addresses those challenges. This paper also includes step-by-step instructions on how to embed Oracle 10g into a typical application.

Why Embed?

Embedded databases have many benefits, both for the ISV developing a business application and to the end-user or customer.

ISV Benefits

ISVs who embed a database in their application can:

• Gain complete control and knowledge of their customer’s database environment, as the database becomes an integral component of their application or device.

• Test their best-suited database configuration for their applications, and then use that configuration to configure their customer’s database.

• Manage and upgrade their customer’s databases. Since embedded database is installed, configured and managed by the ISV application, its installation and configuration is easy, fast and less error prone, thus significantly reducing support costs.

• Purchase software at a discount. ISVs can use the discount to maintain their margins or pass along the savings to better address the needs of small- and midsize businesses.

Customer Benefits

Most of the benefits to ISVs are directly transferable to customers. With an embedded solution, customers can:

• Reduce support issues hassles by working with one vendor and, conceptually, one product.

• Reduce IT costs by avoiding the need for DBAs or specialists to manage their databases.

• Increase application installation speed; they install single application software instead of installing database software and application software separately.

• Enjoy high performance as a result of a well-configured database environment for their application.

These are just some of the benefits that ISV and end-customers can hope to gain from embedding Oracle Database 10g in their applications.

Embedded Database Challenges

There are some key technical challenges involved with embedding a database into an application. These challenges can be classified in the following categories:

Deployment

Installation and configuration of an embedded database must be completely invisible to the user and fully integrated with their application. The database has to be packaged as a component of the application package, and must be installed as part of the application installation.

Management

An embedded database must be self-managing. All routine administrative tasks must be automated. In addition, the database must be able to adapt to changes in system environment and it should be able to address common problems automatically without requiring any outside intervention. In rare eventuality of errors or problems, the database should provide an easy way to diagnose the problem and recommend possible remedies.

Software Maintenance & Support

Software maintenance is a necessary evil that all software applications must deal with. For embedded databases, it is essential that tasks such as upgrades and software patching must be made very simple and as automated as possible.

We will now discuss the various Oracle Database 10g solutions that make it possible to embed an Oracle database in business applications.

Embedded Deployment

Oracle 10g Installation Enhancements

Oracle 10g has significantly simplified installation and configuration. The install has been made much faster and lightweight. It is planned to be installable from a single CD, which contains binaries for the Personal, Standard and Enterprise Editions along with a pre-created seed database. Oracle installation has been extended to perform pre-requisites check to make sure the target system has the requisite OS patches, memory, CPU etc.

Oracle 10g installation can be run in a true silent mode for installing and de-installing the software. Along with silent installation Oracle 10g also supports a silent mode deinstallation. Any program can call the Oracle installer in a silent mode, and provide the list of components via response file that it wants to deinstall. Oracle 10g provides record-and-play functionality for building response files for silent installs. This mode captures the user responses to Oracle installer and Database Creation Assistant (DBCA) questions, which can then be used as input for silent mode installation. The Oracle Universal Installer is capable of taking command line arguments to accept the install time value by overwriting the stored value in response file. This is really important because the application installation process at install time can now easily control what Oracle components get installed, where they get installed and how they get configured.

Another important enhancement in Oracle 10g is that it is no longer necessary to install Oracle client (drivers only) into an Oracle Home. An application can package Oracle drivers and copy them anywhere they want and then just set the necessary environment variables on Unix and/or registry entries in Windows platform.

Oracle 10g installation also configures the Oracle software, listener, and a database all in a single integrated process. You can replace Oracle provided pre-created seed database with your own application specific database. This capability enables you to speed up application installation. ISVs can create and configure a database and load it with its application specific meta data in its own lab and then package and ship it on media as integrated component of its application. For those who would like to have more customized database creation, such as end-customer chosen location of data files, Oracle database also provide flexibility to create and configure a database based on command line arguments.

Embedding Method

Embedding Oracle Database can be divided in two phases. The first phase, which we will refer to as packaging, is where you, as an ISV, create a integrated solution to be delivered to your customer for installation. The second phase, is when your customer uses your package to install the application with the embedded database on their machine. This phase is referred to as installation. Each customer installation has its own unique challenges, as every environment is different.

Packaging

The following five components need to be packaged:

1. Application software

2. Oracle Universal Installer (OUI)

3. Oracle software/binaries

4. Pre-created seed database (with or without data files)

5. Installation scripts (optional)

You can provide any of these components through a variety of delivery mechanisms. You can choose to ship an entire package on a distribution media such as a CD, host it on your website for download, or make both options available.

1. Application software

Any installation application software may be used, including Install Shield, Microsoft Installer, or scripts to package your application software. Oracle Universal Installer (OUI) is Oracle’s installation engine. It is platform independent and works the same on any supported platform. OUI provides an OS level command line interface to perform Oracle installation in silent mode. Therefore it is easy to call OUI from any application installation package via an OS callout function or directly from scripts.

2. Oracle Universal Installer (OUI)

OUI is packaged with Oracle standard media. In most cases, you won’t need to package it separately. However, for web-based installations, a customer machine needs to run OUI from local machine to download and install Oracle binaries directly from web server. The OUI engine is available for download from Oracle Technology Network (OTN) at .

3. Oracle software/binaries

Oracle 10g software can be downloaded from OTN (). Or, you may order the software CDs from Oracle Store (). All Oracle partners get regular shipments of Oracle software as part of their Oracle Partner Program (OPP) Membership.

The first step in packaging Oracle software involves creating a responseFile. This file allows the installation of Oracle software unsupervised in silent mode. Using OUI in record mode, a customer’s typical installation must first be simulated. To run OUI in record mode use following commands:

setup.exe -record -destinationFile (for Windows platform)

runInstaller -record -destinationFile (for Unix and Linux platforms)

Continue to record responses till the “Summary” page, and then click “Cancel” to exit from record mode.

Figure 1: Summary page of OUI

[pic]

This will create a responseFile (also referred to as destinationFile) that will need to be included in the final package to replay recorded responses as customer responses. This ensures that customers don’t have to respond to Oracle installer questions, and all customer installations have the same Oracle software components.

Note: Before attempting to create a responseFile as described above, please review the following section “Pre-created database (with or without datafiles)” to understand application requirements correctly, and how a database will be created at install time.

4. Pre-created seed database (with or without datafiles)

You have two options:

i. Use the Oracle pre-configured seed (General Purpose) database. You can use this database for your application. This is the simplest way to package and install Oracle database. All you need to do is to choose the option to create the Oracle Seed (General Purpose) database while recoding your responseFile in “Oracle Software” section described above.

ii. Create your own custom database, configure it for your application requirements, and then package that database to be installed at the customer’s machines. This option provides two further choices for packaging and installing a pre-created database.

c. Replace Oracle seed (General Purpose) database with your own custom database

Use this database to replace the Oracle supplied database with your own custom database, and install it along with the Oracle software in a single call. Similar to option (i), here also you just need to make sure that you choose the option to create the Oracle Seed (General Purpose) database while recording your responseFile described earlier in the “Oracle Software” section.

d. Package the database for customized installation

ISVs can provide some customization of the database during install time. For example, take inputs for different locations for data files from end-customer and configure the database using those values to layout the database. In this case OUI needs to only install Oracle software and not create any database. The database creation is done in a separate call by your application installation software to Oracle Database Configuration Assistant (DBCA).

For either of the options of (iia) or (iib), first we need to package the existing database. This is done using the Oracle Database Configuration Assistant (DBCA). DBCA is Oracle’s GUI tool for creating, configuring, and packaging an Oracle database. For packaging DBCA has the concept of templates. Using the DBCA tool, a database can be packaged into a DBCA template for transportation to any machine. On the target machine DBCA can create a new database based on the template. A DBCA template consists of two types of files, a template definition file and, optionally, database files compressed into a single file. An existing database has to be first packaged into a DBCA template. The steps for packaging a database using DBCA are described in detail in Appendix A.

If the database template was created without datafiles, an ISV can only use option (iib) and not (iia). To implement option (iib), the database has to be packaged for custom installation. The exact steps to implement this option are described in Appendix B. However, if the datafiles were included in the template, then the next step is to replace the Oracle provided seed database with the custom packaged seed database template. The exact steps for replacing the Oracle provided seed database are described in detail in Appendix C.

Installation scripts (optional)

You can include any optional scripts you might have to perform pre or post installation tasks. For example you will need to create pre-installation script on UNIX and Linux platforms to create required UNIX group, users, kernel parameter setting etc.

This completes the discussion on the first part of embedded deployment — packaging. We will now discuss the second part, which is installation.

Installation

The second part of the embedding process involves the customer installing the embedded database from your application package. Installation steps depend on what was packaged and the choices made during packaging. Installation deals with the same five components that were packaged. They are:

1. Application software

2. Oracle Universal Installer (OUI)

3. Oracle software/binaries

4. Pre-created seed database (with or without datafiles)

5. Installation scripts

1 Application software

Depending upon what installation software was used for packaging the application software, the installation process can vary. However, all installation software(s) provide call-out functions to make OS calls, while the systems waits for the call to complete. Hence use your application installation software’s call-out function to make a call to OUI to launch an Oracle software installation. You also need to make sure that you collect any user input (such as ORACLE_HOME) into your installation software variables, so you can eventually pass those to OUI.

2. Oracle Universal Installer (OUI)

OUI is a runtime engine that reads the Oracle package and installs it on the target machine. You don’t need to install OUI. You just need to make sure your application installer can make a call to this program usually from the media itself.

3. Oracle software/binaries

To install Oracle software for embedded installation, you need to call OUI in silent mode and pass it your responseFile as argument. You can also pass any other arguments to overwrite the values those are hard-coded in the responseFile. You are likely to do this to provide values for installation specific variables such as ORACLE_HOME, and FROM_LOCATION. FROM_LOCATION is the location from where Oracle software package is accessible such as a file system or Web server via HTTP.

setup.exe -nowait -silent -responseFile ORACLE_HOME="F:\OraHome10g" ORACLE_HOME_NAME="OracleHome10g" (for Windows platform)

setup.exe -nowait -silent -responseFile ORACLE_HOME="F:\OraHome10g" ORACLE_HOME_NAME="OracleHome10g" (for Unix and Linux platforms)

If you have chosen to install a pre-created seed database then this call will take care of installing all Oracle binaries and creating an Oracle database.

4. Pre-created seed database (with or without datafiles)

This step is only needed if you have selected to custom install your database. To install your database, first you need to copy your template files to target machine’s “ORACLE_HOME/assistants/dbca/template” directory. Then make another call to launch dbca using following command.

dbca -silent -createDatabase -templateName “" -gdbname "" -sid “" -sysPassword -systemPassword

Example:

dbca -silent -createDatabase -templateName “embedded_db.dbc" -gdbname "my10db1.us." -sid my10db1" -sysPassword sysoracle -systemPassword systemoracle

This completes the discussion on how to address the first and the most important challenge of an embedded database — the ability to deploy Oracle database silently by integrating it completely with the application installation.

Designing Self-managing Database

The second challenge that a database must meet is that it should be self-managing such that no onsite maintenance is required for day-to-day operations. This is because typically embedded environments do not have onsite DBAs or technical expertise to actively manage the database systems. In most cases, users have some technical knowledge of the application they are using but none of the database system on which the application is running. For such environments the database, once deployed, must be self-managing where all routine administrative tasks are fully automated. Second, it should also be able to adapt to environment changes without requiring any outside intervention. Third, when problems do happen, it should be able to repair the problem itself, once again without requiring any outside intervention.

Automation of Routine Administrative Tasks

1. Backup & Recovery Management

PERFORMING REGULAR BACKUPS TO RECOVER FROM DATA LOSS IS PERHAPS THE MOST CRITICAL ADMINISTRATIVE TASK. ITS AUTOMATION IS ESSENTIAL FOR AN EMBEDDED DATABASE. ORACLE RECOMMENDS THAT A BACKUP WITH THE FOLLOWING CHARACTERISTICS BE CREATED:

• Uses Recovery Manager (RMAN) for performing any backup and recovery operations. RMAN is Oracle’s utility to manage the backup, and more importantly the recovery, of the database. It eliminates operational complexity while providing superior performance and availability of the database. RMAN determines the most efficient method of executing the requested backup, restore, or recovery operation and then executes these operations in concert with the Oracle database server. RMAN provides capabilities such as incremental backup, corrupt block detection while taking backup, block media recovery and others to ease the backup and recovery administration of an Oracle database

• Performs on-disk backups using the new Flash Recovery Area feature. The Flash Recovery Area is a unified storage location for all recovery related files and activities in an Oracle database. By defining one initialization parameter, all RMAN backups, archive logs, control file auto-backups, and datafile copies are automatically written to a specified disk location. The Flash Recovery Area is completely self-managing. Oracle server will automatically age out old backup and archive log files and will maintain only the current ones. Users no longer need to worry about keeping the Flash Recovery Area size small by deleting old backups, nor do they need to keep track of the archive logs that are needed for recovery. This speeds up the recovery process since all files needed for any recovery activity are located in one place and the database is aware of that location. Backup and recovery activity in the Flash Recovery Area is very fast since disk access relative to tape access is extremely fast and efficient. On current disks, data anywhere on the disk can be accessed in a few milliseconds.

Flash Recovery Area can be defined by setting the following two initialization parameters:

DB_RECOVERY_FILE_DEST: Sets the location of the Recovery Area

DB_RECOVERY_FILE_DEST_SIZE: Sets the size in bytes of the Recovery Area

Both these parameters are dynamic and can altered or disabled using the ALTER SYSTEM SET command with the database online.

• Perform full database backup once and then perform incremental nightly backups. Incremental backups in Oracle 10g are even more efficient. Incremental backups only backup changed blocks, and unlike Oracle9i the datafiles do not have to be scanned to identify the changed blocks. Oracle 10g keeps track of the changed blocks in each datafile in a special change tracking file (for a 1 TB database, the approximate size of this file is 4 MB), so that when incremental backups are performed, database automatically uses the change tracking file and backs up changed blocks only without the overhead of datafile scanning. This makes incremental backups fast and space efficient. By default, Oracle does not keep track of changed blocks. Hence, this feature should be enabled to benefit from incremental backup enhancements. The command to enable change block tracking is:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Another new enhancements in Oracle 10g is the ability to merge an incremental backup to the image copy of the database. This extremely powerful feature rolls forward the image backup copy, thereby reducing the time needed for media recovery, and it also saves space since the incremental backup that has been merged can now be deleted. This means that a full database backup only has to be performed once in the life of a database and that the size of the Flash Recovery Area will stabilize and will not grow much beyond the actual database size. On a weekly basis incremental backups should be merged in the image copy of the database backup. The command for merging the incremental backup into the image copy by rolling it forward is:

RECOVER COPY OF DATAFILE

Starting with Oracle 10g, when a database is created using Database Configuration Assistant (DBCA), a backup job is created automatically that performs nightly incremental backups to the Flash Recovery Area. By default, this job is created as an Enterprise Manager (EM) scheduler job. Users can use this predefined job or if they like, they can define the same job manually using the server based Unified Scheduler. Note that for the Unified Scheduler, users have to create the backup script themselves with the afore-mentioned recommended settings.

2. Space Management

WHEREAS BACKUP MANAGEMENT IS THE MOST CRITICAL OF ROUTINE ADMINISTRATIVE FUNCTIONS, SPACE MANAGEMENT IS PERHAPS THE MOST COMMON. IN A DBA-LESS ENVIRONMENT, A DATABASE MUST BE CONFIGURED TO MINIMIZE, IF NOT ELIMINATE, ANY SPACE MANAGEMENT RELATED TASKS. STARTING WITH ENHANCEMENTS FROM AS FAR BACK AS ORACLE8I, ORACLE HAS STEADILY AUTOMATED MORE AND MORE SPACE MANAGEMENT FUNCTIONS, CULMINATING IN ORACLE 10G WHERE THE SOLUTION IS FINALLY COMPLETE. THERE ARE FIVE CRITICAL SPACE MANAGEMENT SOLUTIONS THAT OUGHT TO BE UTILIZED IN AN EMBEDDED DATABASE:

1. Oracle Managed Files (OMF)

OMF was introduced in Oracle9i. It eliminates the need to manually administer database files. In this feature, a default location is specified for datafiles, control files, and online logs files. When adding or creating new files, a user does not need to specify the location or size of the file. Oracle will automatically generate a unique name for the file and create it. Oracle will also delete these files from the OS when the corresponding object is dropped from the database. This feature is enabled by specifying the following initialization parameters:

DB_CREATE_FILE_DEST: Specifies the default location of datafiles

DB_CREATE_ONLINE_LOG_DEST_: Specifies default location for copies of online logs and control files

OMF datafiles, created by default, will be 100 MB in size and will be auto extensible with unlimited maximum size. The default size of OMF online logs is also 100MB.

OMF has many benefits. It facilitates creation and deletion of database files by eliminating the need to put OS specific file names and sizes in scripts. It reduces possibility of human error, e.g., specifying incorrect file names, and reduces disk space wasted in obsolete files. All these make it very suitable for automation of many tasks related to file creation and management, for instance, adding a datafile when a tablespace encounters an out-of-space condition.

2. Automatic Undo Management (AUM)

AUM was in introduced in Oracle9i. It enables the database server to self-manage undo segments freeing up the user from worrying about creating and managing rollback segments. Users no longer need to assign certain transactions to specific rollback segments nor do they need to worry about determining the right size and number of rollback segments. All that is needed is that an UNDO tablespace be created and the database will do the rest. There are many additional benefits of AUM. It essentially eliminates the possibility of undo block and consistent read contention as the server dynamically adjusts the space allocated to the undo segments to meet the current workload requirement. Furthermore, by sizing Undo tablespace properly, users can completely avoid the occurrence of the dreaded ORA-1555 (snapshot too old) error. The only configuration decision that must be taken is about the size of the Undo tablespace. Oracle 10g provides an Undo Advisor, which guides in sizing the tablespace. However, for embedded databases it is advisable to make Undo tablespace auto-extensible. This is an attribute of the tablespaces and should be set to ‘YES’. This will allow the Undo tablespace to grow automatically as needed based on application workload.

3. Locally Managed Tablespace

Locally Managed Tablespaces were introduced in Oracle8i. They perform better than dictionary managed tablespaces, are easier to manage, and eliminate space fragmentation related problems. For operations like space allocation and deallocation, they use bitmaps stored in datafile headers and unlike dictionary managed tablespaces do not contend for centrally managed resources. They eliminate the need for many recursive operations that are sometimes required for dictionary managed space allocation such as allocating a new extent. Locally managed tablespaces offer two options for extent management, Auto Allocate and Uniform. In the Auto Allocate option, Oracle server itself determines the size of each extent allocated, whereas in the Uniform option, all extents in that tablespaces are of the size specified by the UNIFORM clause of the CREATE TABLESPACE statement. We recommend using the Auto Allocate option because even though it might result in objects having multiple extents, the user need not worry about it since locally managed tablespaces can handle a large number of extents (over 1000 per object) without any noticeable performance impact. We therefore suggest that database administrators not worry about extent sizes and use the Auto Allocate option.

Locally managed tablespaces address two important issues that are critical for embedded environments. First, they eliminate external fragmentation of a tablespace. Second, they handle extent sizing and management internally. This means that tablespaces do not have to be defragmented to recover lost space or to enhance performance. This very costly maintenance operation is no longer required when locally managed tablespace are used.

4. Automatic Segment Space Management (ASSM)

ASSM was introduced in Oracle9i. It handles space management within a segment automatically. Prior to Oracle9i segment attributes such as FREELISTS, FREELIST GROUPS, PCTUSED had to configured manually. As database environment changes over time, such as data growth or increase in concurrency, these attributes may have to be reconfigured manually by recreating the segment. With ASSM, no manual configuration or reconfiguration is needed. ASSM makes space management within a segment completely transparent by using bitmaps instead of FREELISTS for managing space utilization within each data block. ASSM also improves space utilization of a data block. This is because the bitmaps are much better equipped to track and manage free space at the data block level than FREELISTS. This enables better reuse of the available free space particularly for segments with rows of varying size. Additionally, ASSM improves the performance of concurrent DML operations significantly since different parts of the bitmap can be used simultaneously, eliminating serialization for free space lookups.

5. Online Segment Shrink

Online Segment Shrink is a new Oracle 10g feature. Segments that undergo frequent insert and delete DML activity can become internally fragmented and develop unusable free space in the data blocks. This results in wasted space in the segment and it takes up much more space than is really needed. Moreover, this wasted space within a data block also has negative performance impact as it causes unnecessary row chaining. With Online Segment Shrink, segments with such wasted space, can have this space reclaimed by shrinking the segment. Segment shrink involves the moving around of row pieces within a segment to fill up the wasted space. It will also remove row chaining wherever possible thus improving performance of read operations on the segment. This operation is online and in-place. This means that activity on the segment can continue when the shrink operation is being performed and that it does not need additional space for storing temporary data, something that is needed for other online operations such as Online Data Redefinition and Reorganization.

Oracle 10g provides a Segment Advisor that recommends which segments are good candidates for shrinking based on the amount of wasted space. It takes a tablespace name as an input and makes recommendations for all the segments within that tablespace. A job should be created using the Unified Scheduler that runs weekly, calling the Segment Advisor on all the application tablespaces that undergo DML activity, and implements the advisor recommendations. This will ensure more efficient space utilization within a segment, and will also reduce the possibility of encountering out-of-space conditions. The SQL*Plus script below gives an example of how the Segment Advisor can be called using PL/SQL interface and its recommendations implemented automatically. This script runs the Segment Advisor on the tablespace ‘USERS’ and then shrinks all segments in it that are good candidates for shrinking.

Locally managed tablespaces handle tablespace space fragmentation, or external fragmentation, problem, and now by creating a regular job that performs Online Segment Shrink, the problem of segment space fragmentation, or internal fragmentation, is also eliminated. Thus, by using both locally managed tablespaces and Online Segment Shrink, all space related fragmentation issues are now comprehensively addressed.

The above-mentioned solutions together provide a comprehensive solution for the automation of all space management functions. For embedded database environments, they are a necessary prerequisite for eliminating the need of onsite database maintenance.

3. Performance Management: Optimizer Statistics Collection

APPLICATION PERFORMANCE IS ENTIRELY DEPENDANT ON THE ABILITY OF THE ORACLE COST BASED OPTIMIZER (CBO) TO PRODUCE OPTIMAL EXECUTION PLANS. THE CBO RELIES ON THE AVAILABILITY OF VALID STATISTICS ON THE OBJECTS AND THEIR DATA, KNOWN AS OPTIMIZER STATISTICS, TO GENERATE EXECUTION PLANS THAT ARE FAST AND EFFICIENT. INVALID OR STALE OPTIMIZER STATISTICS WOULD LEAD TO THE OPTIMIZER PRODUCING SUB-OPTIMAL OR EVEN BAD EXECUTION PLANS RESULTING IN POOR APPLICATION PERFORMANCE. OVER TIME AS OBJECTS UNDERGO DML ACTIVITY OPTIMIZER STATISTICS CAN GET UNREPRESENTATIVE OR STALE. THIS MEANS THAT OPTIMIZER STATISTICS HAVE TO BE REFRESHED ON A REGULAR BASIS TO ENSURE THAT THE CBO ALWAYS HAS VALID STATISTICS AVAILABLE. TO DO THIS TASK EFFICIENTLY, ESPECIALLY WHEN LARGE VOLUMES OF DATA IS INVOLVED, CAN BE A NON-TRIVIAL TASK. ONE HAS TO KEEP TRACK OF OBJECTS WHOSE DATA HAS UNDERGONE SIGNIFICANT CHANGE TO WARRANT RECOLLECTION OF STATISTICS, DETERMINE APPROPRIATE SAMPLE SIZE FOR STATISTICS COLLECTION, ASCERTAIN WHICH TABLE COLUMNS REQUIRE HISTOGRAMS, DETERMINE THE APPROPRIATE DEGREE OF PARALLELISM, ETC., BEFORE STATISTICS CAN BE COLLECTED. IN AN EMBEDDED DATABASE ENVIRONMENT WHERE THERE ARE NO DBAS TO PERFORM THIS VERY ESSENTIAL ROUTINE FUNCTION, A JOB MUST BE SCHEDULED THAT RUNS NIGHTLY AND EFFICIENTLY COLLECTS AND/OR REFRESHES THE NECESSARY OPTIMIZER STATISTICS.

In Oracle 10g this task is completely automated. In all Oracle 10g databases, a job is created by default that refreshes statistics on objects with stale or no statistics. It does the following:

• Uses modification monitoring feature to track which objects haven been modified and to what degree, to identify candidates for statistics collection.

• Analyzes objects in the order of their modifications. Objects with most rows modified will be analyzed first, then next most modified, and so on.

• Automatically determines

o The sample size for statistics collection.

o Which table columns require histograms

o Degree of parallelism for the statistics collection job.

o Granularity for partitioned tables, i.e., whether to collect statistics at global level, partition or sub-partition level.

• Gathers statistics on dictionary objects. This is very important because starting with Oracle 10g, data dictionary statements will use the CBO.

No special configuration is needed in Oracle 10g to turn on automatic collection of optimizer statistics. The job is scheduled by the Unified Scheduler to run nightly out-of-the-box in a pre-defined maintenance window. The parameters of this window such as time, frequency, etc., are all modifiable. Moreover, using Resource Manager, the CPU consumption in the maintenance window can also be managed so that its jobs do not consume excessive CPU resources when some important batch job or transaction may be running on the system. One cautionary note in this regard is that like any other Unified Scheduler job, the optimizer statistics collection job can be disabled. For this reason, it should be ensured that the job is always enabled, as it is an essential requirement for embedded database installations.

Configuring Adaptive Systems

An embedded database should be configured so that it can adapt to basic fluctuations in system workload. Otherwise, system performance can become unstable and the possibility of encountering exception conditions, e.g., ORA-4031 “out of shared memory error” or ORA-1555 “snapshot too old” can increase. In this section we will discuss configurations of two key areas that will allow the database to be more adaptive so that it can seamlessly handle workload variations.

1. Automatic Memory Tuning

1. AUTOMATIC SQL EXECUTION MEMORY TUNING

In Oracle9i, Automatic SQL Execution Memory feature was introduced. This feature allows the database server to automatically distribute SQL execution memory among various active processes in an intelligent manner so as to ensure maximum performance benefits and the most efficient utilization of memory. This means that when workload on a system changes, the server will automatically adapt to it by reallocating memory among various processes as needed. This behavior is very different than the old behavior when SQL execution memory allocation was static. Initialization parameters e.g., SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE, etc., were specified to control the maximum amount of memory allocated to SQL work areas for performing operations such as a sort, hash join, etc. The memory allocated to each process was static and could not be redistributed to other SQL work areas as the workload changed. This resulted in poor performance for systems with varying workloads, since in this type of SQL execution memory management, the system was really configured for a single workload. Systems with onsite DBAs worked around this problem by having different configuration settings for different workloads. As the workload changed, e.g., from OLTP to batch, they would modify the initialization parameter settings accordingly. In an embedded environment where there are no onsite DBA, this is not possible. Hence, using Automatic SQL Execution Memory Management feature is very essential for such environments. This feature is enabled by simply setting one initialization parameter, PGA_AGGREGATE_TARGET to the maximum amount of PGA memory available to the database. Oracle server will then take over and will manage the SQL execution memory automatically.

2. Automatic Shared Memory Tuning

Automatic Shared Memory Tuning is a new feature in Oracle 10g. It solves exactly the same problem for shared memory that Automatic SQL Execution Memory Tuning solves for private or SQL execution memory. It allows for dynamic redistribution of shared memory between various memory pools, such as buffer cache, shared pool, large pool, and java pool, based on workload changes. For example, a batch workload may need a bigger large pool for parallel queries while an OLTP workload might perform better with a larger buffer cache. With Automatic Shared Memory Tuning, Oracle will adjust the size of the memory pools by allocating a bigger large pool when a batch workload is running, and when the workload switches to OLTP, it will deallocate memory from large pool and allocate it to buffer cache to get optimal OLTP performance without any external intervention. Prior to Oracle 10g, each shared memory pool had to be tuned for different workloads separately. In non-DBA environments, this meant that the system could at best be tuned for a single workload and when workloads changed, the database would perform below par and possibly even encounter exception conditions such as ORA-4031: “unable to allocate xxx bytes of shared memory”. Automatic Shared Memory Tuning not only adapts to different workloads dynamically and without any outside intervention, it uses shared memory more efficiently and enhances the overall performance of the system. This feature is essential for embedded environments. It is enabled by simply setting the initialization parameter SGA_TARGET to the total amount of shared memory available to the database instance.

2. Automatic Undo Retention Tuning

AUTOMATIC UNDO RETENTION TUNING IS A NEW ORACLE 10G FEATURE. IT IS AVAILABLE ONLY WHEN AUTOMATIC UNDO MANAGEMENT (AUM) IS ENABLED. THIS FEATURE DYNAMICALLY ADJUSTS UNDO RETENTION FOR A GIVEN SIZE OF THE UNDO TABLESPACE AND THE WORKLOAD ON THE SYSTEM. IN MANUAL UNDO RETENTION TUNING, A USER WOULD MANUALLY SET THE INITIALIZATION PARAMETER UNDO_RETENTON TO THE LENGTH OF TIME HE OR SHE EXPECTS THE LONGEST RUNNING QUERY TO TAKE. EVEN IF THIS PARAMETER IS SET CORRECTLY INITIALLY, AS THE DATA DISTRIBUTION CHANGES OVER TIME, THE TIME TAKEN BY THE LONGEST RUNNING QUERY ON THE SYSTEM MAY CHANGE. IF THIS TIME INCREASES, USERS CAN ENCOUNTER ORA-1555 “SNAPSHOT TOO OLD” ERROR AND IF THE TIME DECREASES, THEN THE SPACE IN THE UNDO TABLESPACE IS UNDER UTILIZED. THE FORMER SCENARIO, I.E., TIME TAKEN BY LONGEST QUERY INCREASING, IS MORE TYPICAL AND ITS CONSEQUENCES MORE SERIOUS. IT RESULTS IN EXCEPTIONS CAUSING QUERIES AND/OR TRANSACTIONS TO FAIL. THE ONLY WAY TO FIX THE PROBLEM IN MANUAL UNDO RETENTION MODE IS BY MANUALLY CHANGING THE UNDO_RETENTION PARAMETER. ONCE AGAIN, FOR EMBEDDED DATABASES THIS IS NOT ACCEPTABLE. FOR THIS REASON, EMBEDDED DATABASES SHOULD BE CONFIGURED TO USE AUTOMATIC UNDO RETENTION TUNING. IT WILL DYNAMICALLY CHANGE UNDO RETENTION TO KEEP IT AHEAD OF THE LONGEST QUERY ON THE SYSTEM. WITH THIS FEATURE ENABLED AND AN AUTO-EXTENSIBLE UNDO TABLESPACE (RECOMMENDED EARLIER), THE LIKELIHOOD OF ENCOUNTERING ERRORS LIKE ORA-1555 IS ALMOST ZERO.

Design Self-reliant Systems

Automation of routine administrative tasks and configuring the database to be adaptive fulfills a major requirement for systems with no onsite DBAs. However, even in a perfect setup, errors or exceptions will eventually occur. It is, therefore, imperative that the database be designed to handle such exceptions gracefully and be able to recover and self-correct them. Oracle provides a number of solutions to facilitate the creation of such self-reliant systems.

A self-reliant system must do two things. First, it must be able to trap or identify the exception, and second, it should be able to take corrective action to remedy the problem. Oracle 10g provides a new infrastructure, Server Generated Alerts, for alerting to some of the most common types of exceptions encountered. These include space alerts when a tablespace is running out of space, ORA-1555 “snapshot too old” message alert, alerts associates with space problems in the Recovery Area, and a host of performance metrics alerts. This takes care of the first requirement for a self-reliant system — the ability to trap or identify exception conditions. The second requirement of providing the ability to take corrective actions is also provided by Oracle. In Enterprise Manager (EM) for each kind of alert generated, a response action can be defined in the form of an OS command or a script. This basic framework can be used to setup a self-reliant embedded database. Figure 2 below shows the EM interface where this can be done.

Figure 2. Enterprise Manager interface for setting response actions

[pic]

1. Space related exception handling

The most common space problems are with tablespaces running out of space. In Oracle9i, the Resumable Space Allocation feature was introduced. This feature prevents space related problems from aborting transactions by instructing the database to suspend any operation that encounters an out of space condition. This provides an opportunity for corrective action to be taken while the operation is suspended and automatically resume its execution once the problem has been resolved. In Oracle 10g, a warning alert is generated when a tablespace is 85% full and a critical alert is generated when it is 97% full (these are the default values for the thresholds and can be modified by the user). Thus, anytime a session marked as resumable (this is done by issuing the ALTER SESSION ENABLE RESUMABLE command in the session) encounters an out of space condition, the session will go into a suspend mode, while a space alert is automatically generated. A response action can be defined for the particular tablespace in EM that runs a script to remedy the problem. The remedy can include one or more of the following actions:

• Use Online Segment Shrink to recover wasted space from the tablespace in question. This could fix the problem without requiring addition of new datafile. The following SQL command shrinks the segment specified:

ALTER TABLE SHRINK SPACE;

• Add new datafile to the tablespace. By using the Oracle Managed Files (OMF) feature discussed earlier, this script can be made fairly generic without having to hard code the actual name, location or size of the datafile.

4. ORA-1555 “Snapshot tool old” error handling

Oracle 10g will also generate alerts when ORA-1555 is encountered. For this alert, the corrective action would involve enabling Automatic Undo Retention Tuning and increasing size of the Undo tablespace. For more sophisticated handling, the Undo Advisor can be called using PL/SQL package, DBMS_UNDO_ADV, and its output used to determine the exact amount of space needed. The anonymous PL/SQL block below shows a very simple example of how the Undo Advisor can be used to determine the correct size of the Undo tablespace.

In this way a script can be created that determines the actual size of Undo tablespace needed to remedy ORA-1555, and the Undo tablespace can then be altered to the new size by adding a new datafile. This would correct the exception condition and the transaction or query can then be resubmitted for successful completion, thus obviating the need for external intervention.

5. Recovery Area Alert handling

This alert is generated in Oracle 10g when the Flash Recovery Area encounters space pressure because there are no more files that can be deleted and the archiver or RMAN needs to write something to it. The appropriate response action for this can include one or more of the following remedies:

• Allocate more space quota to the Flash Recovery Area.

• Backup files to tape or some other tertiary device.

• Reduce the retention policy. This can be done using the RMAN configure command:

RMAN CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF DAYS;

• Merging incremental backups into the image copies using the command

RECOVER COPY OF DATAFILE

This will allow the deletion of incremental backup files.

These actions would remedy problems like database hanging because redo logs cannot be archived or regular backups failing because of lack of space in the Recovery Area.

Oracle 10g provides the basic infrastructure for designing self-reliant systems. With minimal effort some of the more common type of problems can easily be handled. Some of them have been mentioned above. This infrastructure is key to making the database truly self-reliant such that it is virtually invisible to the application user.

Easy Problem Diagnostics and Resolution

An embedded database must provide an easy way to diagnose and resolve problems when they do occur. Eventually even a flawless embedded database installation will encounter problems that require administrative intervention. Granted these cases should be rare in an embedded database, nevertheless the database must provide an easy way to detect, diagnose, and resolve the problems if and when they do happen. Vast majority of embedded installations do not possess great technical expertise in-house in database systems and hence it is even more essential that the process of diagnosing and fixing problems be very straightforward, such that even a novice user can do it easily. To this end, Oracle 10g has introduced new self-diagnostic capability that allows the database to proactively detect and diagnose problems.

Automatic Database Diagnostic Monitor (ADDM)

ADDM is a new database solution that provides a holistic, real-time performance picture of the entire database. It runs proactively every 30 minutes and looks for any performance related problem. It can also be run on-demand any time. After every run, it generates report containing all its findings. The findings highlight sources of problems as well as non-problem areas. If a problem is detected, it will identify its root causes and make appropriate recommendations for solving problem. Any time a performance problem is encountered with a database that requires user intervention, the first and only place the user needs to look at is the ADDM report. This report will list the problem, recommend a solution along with its performance benefit. ADDM has a PL/SQL and Oracle EM interface. ISVs can use PL/SQL interface to embed the ADDM capabilities in their application or use EM interface to remotely diagnose their customer databases. ISVs can also use the ADDM PL/SQL interface to automate the shipping of ADDM reports to them for proactive support. To use the PL/SQL interface a new package, DBMS_ADVISOR, is provided in Oracle 10g. Below are three examples of how the DBMS_ADVISOR package can be used from the command line. Example 1 below shows how to manually run ADDM from SQL*Plus.

Example 1: Manually running ADDM.

Example 2 below shows a SQL*Plus script to see the latest ADDM report.

Example 2: ADDM report for the latest run.

The third example shows a SQL*Plus script to see ADDM report for a specific ADDM task. The script is invoked from SQL*Plus as "@ ".

Example 3: ADDM repot for a specific task.

ADDM provides a very easy and straightforward way for diagnosing and resolving all performance related problems, such that even novice users can easily use it. This power and ease of use of this feature makes it very essential for embedded database installations when those rare performance problems requiring user intervention do occur.

Server Generated Alerts

ADDM addresses the area of performance related problems. For all non-performance related issues the new Server Generated Alerts framework provides a solution. These alerts offer a mechanism for capturing a number of serious problems, immediate or potential, encountered by the database. By default, the following alerts are enabled:

1. Tablespace space usage: When a tablespace reaches 85%, a warning alert is issued and at 97% a critical alert is issues. These are default values and can be modified.

2. ORA-1555 Snapshot tool old: An alert is generated any time this error occurs.

These alerts capture most of the common non-performance related exceptions that a database may encounter. By providing a single framework for generating, managing, and analyzing these alerts, the task of detecting non-performance related problems has been greatly simplified. A user can simply query the views DBA_ALERT_HISTORY and DBA_OUTSTANDING_ALERTS to identify any non-performance related problems.

Between ADDM and Server Generated Alerts, a very comprehensive and easy method for detecting, diagnosing, and resolving database problems exists. The interface and usage model for the two features is simple and uncomplicated. Embedded environments with little or no database expertise should be able use them for holistic problem diagnostic and resolution.

Software Maintenance & Support

The third challenge that must be addressed for an embedded environment is that of software maintenance and support. Software patches have to be applied from time to time to fix some critical issues. Oracle provides mainly three types of software updates:

• Patches,

• Patchsets, and

• Release upgrades.

Your embedded application may need to apply any of these types of updates for your customers. Oracle provides different tools and approaches for each of them.

Patches and Patchsets

Patches and Patchsets are simple replacement of existing binaries with new binaries. Patches are applied by using Oracle utility called OPATCH and Patchsets are applied using Oracle Universal Installer (OUI). Both of these tools provide a silent mode update of Oracle binaries. You may need to apply software patches to fix some critical issues, or apply a patchset to stay current with the latest binaries of the same release. Additionally, you may also need to apply patchset to get known bug fixes as part of the patchset. To facilitate this operation, you must have a way to easily identify relevant patches and patchsets that need to be applied. Oracle 10g has a completely new Oracle Enterprise Manager (EM). The new EM is designed to work across firewalls at any level, and has a lightweight HTML interface. EM has a component called Enterprise Configuration Management (ECM), which tracks all configuration information related to the hardware, software, and network of the system such as Oracle platform versions, OS type and versions, patch levels, dependencies, etc., and also directly integrates with Oracle support’s (Metalink) to provide automatic updates for any patches or patchsets. ECM has a Patch Wizard that can be used for the following:

• To automatically identify customers where a particular patch is applicable.

• To automatically download and stage the patch for application.

ISVs can setup ECM with the different types of database configurations they support for their customers so that when a particular patch is available they will know the customers that need it. ISVs can then download the patch and ship it to the appropriate customer or they can put it on their own website from where their customers can download it. Moreover, Oracle 10g also supports silent mode patchset and patch installation. With this capability ISVs can package patchset and patches as part of the application patching mechanism and ship it to their customer for installation.

Release Upgrade

Upgrading a major database release requires installation of new release into a separate Oracle Home. However, upgrade of a maintenance release can be performed on the same Oracle Home. To upgrade a database release you can simply install the Oracle binaries in silent mode. To upgrade an existing Oracle database to a new release of Oracle database, Oracle provides a tool called Database Upgrade Assistant (DBUA). This tool has a command line interface to completely upgrade the database in silent mode. DBUA automatically performs all the database upgrade tasks such as pre-upgrade database backup, replacing obsolete parameters, and verifying successful completion.

Tools Available for Embedding

Oracle provides an Oracle Embedded Installation (OEI) Kit that provides the detailed instructions for embedding Oracle Oracle9iR2 and Oracle 10g databases. This kit is only supported for Oracle partners. Oracle partners may download the kit from Oracle Partner Network at .

Conclusion

Oracle Database 10g is the first database to comprehensively address all embedded databases challenges. It can be deployed and run in a way that is completely invisible to the end-user. Its silent deployment, flexible packaging, self-managing capabilities, along with its traditional strengths in performance, scalability, and reliability, make it the ideal database for embedded environments.

Appendix A

Packaging a database using DBCA (with or without data files)

The step-by-step instructions on how to package an existing database into DBCA templates is described below:

1. Run DBCA from Program Manager (windows only) or command line as “dbca” on the machine where database resides.

2. Click “Next” to continue.

3. Select Manage Templates.

4. At this screen you have three different choices to create a database template.

e. From an existing template: This option creates another database template from an existing template by making changes to it. Ignore this choice!

f. From an existing database (structure only): This option will create a template but will not package database contents (data files). Choose this option to only package database creation scripts to create a similar database. This option saves space because it does not package the datafiles. This approach can take considerably longer to create the database at installation time, and the ISV also needs to load schema objects after the installation.

g. From an existing database (structure as well as data): This is the preferred option. This option not only packages the database structure but also packages the contents of database. This option enables an ISV to pre-install its application schema into the database and save a great deal of installation time.

8. Select source database and connect as user ‘SYS’.

9. Enter a template name and description. Leave the location for the compressed file that will hold the data files to default. This template name will be used as argument at install time to create the database, so don’t use space in template name and keep it short.

10. On this screen select “Convert the file location to use OFA” and click “Finish”. This option enables template definition to hold relative location for database files instead of hard coded paths of current database files.

11. After this, a “Summary” screen is displayed. This screen shows the operation that will be performed and the template files that will be created. Click “OK” and template creation will start.

12. A “template creation complete” message will be displayed. Exit by selecting “No” to “Do you want to perform any other operations?”

13. At this point either two or one template files will be created under current “ORACLE_HOME/assistants/dbca/template” directory. They will be .dbc (template definition) and .dfb (compressed datafiles). However, if option b. was selected in step 4 there will be only one .dbt file, which will have database structure definition.

Appendix B

Packaging database for custom installation

If you decided to package your own database instead of replacing Oracle’s seed (General Purpose) database, it is useful to remove the Oracle supplied seed (General Purpose) database from the package to conserve space. Here are the step-by-step instructions to remove the Oracle provided seed (General Purpose) database from the package:

1. Copy the entire ORACLE CD to your hard disk. We will refer to this as ShipHome.

2. Under your ShipHome replace the data file "Data_Warehouse.dfb" under “\Disk1\stage\Components\oracle.rdbms.seeddb\10.1.0.1.0\1\DataFiles\Expanded\seed\templates” with a 0 byte file.

The above process gives you a new Oracle ShipHome that can be packaged and shipped to install your packaged database. Make sure you don’t attempt to create an Oracle’s seed (General Purpose) database from this ShipHome, or you will get errors.

After you have removed the Oracle’s seed (General Purpose) database from the package, you can simple copy your database template file into your package anywhere you like. However, at the time of installation these files need to be copied to your target machines “ORACLE_HOME/assistants/dbca/template” directory.

Appendix C

Replacing Oracle seed (General Purpose) database with custom database

Here are the step-by-step instructions for replacing and Oracle provides seed (general Purpose) database with your packaged database to be installed in a single process:

1. Obtain the database template, which contains your database. (e.g. MySeedDBTmpl.dbc and MySeedDBTmpl.dfb)

2. Copy the entire ORACLE CD to your hard disk. We will refer to this as ShipHome.

3. Backup the directory "DataFiles" from “\Disk1\stage\Components\oracle.rdbms.seeddb\10.1.0.1.0\1\” to a temporary location. (Let’s call it component temp)

4. Extract the files in dbc.1.1.jar to another temporary location (let’s call it template temp), using this command.

jar –xvf dbc.1.1.jar

Note: jar is utility included as part of JDK from JavaSoft.

5. From within this “template temp” directory structure, delete the file General_Purpose.dbc0 or whatever the General_Purpose template is called.

6. Copy your database template definition file previously created (e.g. MySeedDBTmpl.dbc) to this location and rename it to General_Purpose.dbc0 or whatever it was called before.

7. Edit this file and modify the element as below

Change from

{ORACLE_HOME}\assistants\dbca\templates\MySeedDBTmpl.dfb

...

Change to this:

{ORACLE_HOME}\assistants\dbca\templates\Data_Warehouse.dfb

...

8. Package all the files from “template temp” back into the jar using this command.

jar -cvf dbc.1.1.jar *

Note: Make sure you only package exact same files, as you extracted from it.

9. Copy this dbc.1.1.jat back into the “component temp” location by replacing the existing file.

10. Delete the original data file "Data_Warehouse.dfb" under DataFiles\Expanded\seed\templates from “component temp” directory.

11. Now copy your template compressed data file (e.g. MySeedDBTmpl.dfb) to this location and rename it to Data_Warehouse.dfb.

12. Copy back the entire contents of your “component temp” directory to “\Disk1\stage\Components\oracle.rdbms.seeddb\10.1.0.1.0\1\”

The above process gives you new Oracle ShipHome that can be packaged and shipped to install your packaged database as Oracle’s seed (General Purpose) database.

-----------------------

variable id number;

begin

declare

name varchar2(100) ; descr varchar2(500) ; objid number;

begin

name := '' ;

descr := 'Segment Advisor Demo';

dbms_advisor.create_task('Segment Advisor', :id, name, descr, NULL) ;

dbms_advisor.create_object(name, 'TABLESPACE','USERS',NULL, NULL, NULL, objid);

dbms_advisor.set_task_parameter(name, 'RECOMMEND_ALL', 'TRUE') ;

dbms_advisor.execute_task(name) ;

end ;

end ;

/

set echo off

spool shrink.sql

select attr1 || ';' from dba_advisor_actions where task_id=:id ;

spool off

@shrink

declare

retention number ; undo_ts_size number ;

begin

retention := DBMS_UNDO_ADV.REQUIRED_RETENTION ;

undo_ts_size := DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(retention) ;

dbms_output.put_line('Required tablespace size is ' || undo_ts_size);

end ;

/

set serveroutput on;

begin

declare

id number; name varchar2(100); descr varchar2(500);

BEGIN

name := '' ; descr := 'manual addm run';

dbms_advisor.create_task('ADDM',id,name,descr,null);

dbms_advisor.set_task_parameter(name, 'START_SNAPSHOT', 420);

dbms_advisor.set_task_parameter(name, 'END_SNAPSHOT', 421);

dbms_advisor.set_task_parameter(name, 'INSTANCE', 1);

-- execute task

dbms_advisor.execute_task(name);

end;

end;

/

SET LONG 1000000

SET PAGESIZE 0

SET LONGCHUNKSIZE 1000

SET VERIFY OFF

COLUMN get_clob format a80

select dbms_advisor.get_task_report(dal.task_name, 'TEXT', 'ALL')

from dba_advisor_log dal

where dal.task_id = (select max(t.task_id)

from dba_advisor_tasks t, dba_advisor_log l

where t.task_id = l.task_id

and t.advisor_name = 'ADDM'

and l.status = 'COMPLETED');

SET LONG 1000000

SET PAGESIZE 0

SET LONGCHUNKSIZE 1000

SET VERIFY OFF

COLUMN get_clob format a80

select dbms_advisor.get_task_report('&1', 'TEXT', 'ALL') from dual;

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download