Oracle

Introduction

When you are dealing with a product the size, complexity, and flexibility of the Oracle® E-Business Suite, Version 11i, the ideas of where to start, what to do (and what not to do), and where to turn for answers can be a daunting prospect. Through the process of bringing this book to life, I have come to realize that much of what you deal with in the new versions of what used to be loosely thought of as Oracle Financials (Financials) is somewhat like dealing with a desert or the surroundings; Oracle E-Business Suite 11i can look like a bleak, frightening, and desolate place. But to those with a trained eye and an open mind, it can be seen for the thing of efficiency and beauty that it is. All of the shapes, textures, pieces, and parts work together to create an environment that is perfectly suited to what it was created for. And if you are patient enough, you will be able to see the bright shining beauty of the desert in springtime, in flower, in all its glory (sometimes you really have to look for it, but it will be there). Those are the times when you question less the sanity of the decision to exist in this environment and enjoy your surroundings.

That is not to say that things do not happen to radically change the landscape. The winds of change blow endlessly in both environments. Both are acted upon by their surroundings, and both are robust enough to be able to survive even the cruelest circumstances and come back to thrive. Sometimes storms will alter the landscape in such a way that you are left trying to rediscover your navigation path. Other times the path is clear and easily navigable.

This book is designed to help you to find your path through the sometimes bleak, sometimes beautiful, always inconsistent desert landscape that can be Oracle E-Business Suite 11i (11i). It will help you through your day-to-day adventures in administration and includes tips, techniques, and practical advice from someone who has had her share of cactus spines and exposure along the trail. Included is information on AD (Active Directory) Utilities, patching, cloning, and several of the newer features that 11i brings to market. It will assist you in finding your own path through the sometimes painful world that you now are walking through and hopefully will help you find the elegance in your surroundings.

Audience and Scope

This book is intended for anyone facing the task of administering Oracle E-Business Suite 11i. It is written primarily for those with limited experience with Oracle Applications but with some background in Oracle Database Administration. It is, however, a good reference for anyone wanting to learn more about the technical ins and outs of Oracle Applications (Applications), how it runs and how to live the life of an Apps DBA (database administrator). From the newest person whose management just asked her if she thought she could take over as the administrator for the new Oracle Apps installation to the veteran of several years, we all have things we can learn, things that we can have refreshed in our minds. Sometimes we just need a handy central reference to look to when a situation arises. Much of the information will be of particular interest to those administrators who have some background in older versions of Financials in helping to bring their existing knowledge base the added depth of how things have changed in 11i.

What are ERP and CRM?

To begin to understand more about the animal that you are going to be dealing with, and potentially be responsible for, it would help to have a basic understanding of ERP (Enterprise Resource Planning) and CRM (Customer Relationship Management) — the two main components in Oracle's E-Business Suite.

ERP is a process that helps you put any and all resources involved with an organization to the best possible use. ERP has had other names in its past iterations: Materials Resource Planning and Manufacturing Resource Planning. Manufacturing Resource Planning shows that, at its roots, it was used as a tool most often in a manufacturing environment. Typically, it was used in reference to a process with several discrete operations or discrete objects, many of which can be broken down further into atomic level objects or processes. An example would be a simple wooden bar stool. A bar stool with three legs, three dowels connecting those legs at a predefined space interval, and a round wooden seat. A process might be to drill the hole for leg one into the bottom of the seat piece. There would be three similar processes like that one, one for each leg into the seat. Each leg might have a process assigned to it of drilling two holes, each hole has a depth and a diameter and an angle in reference to the leg and an angle in reference to the other legs. The finished product (bar stool) as a whole has a demand for each component (e.g., legs, screws, seat) and you have a predefined amount that is allocated to waste. Tracking all of this information, as well as tracking those times when the projected numbers fall outside of the expected ranges are all things that historically were tracked by a MRP system either in a spreadsheet, in a notebook, or in early databases (usually with homegrown applications built as a front end).

ERP methodology has grown significantly from its manufacturing roots, although many times MRP is still the basis from which the implementation of an ERP system grows. Today the concept of ERP often refers to a broad set of activities that a company or an enterprise performs, both internally and externally. The computerized system that is often referred to when discussing the management of planning of an enterprise's resources (all resources, including money, physical, and people) is an integrated solution. Such a software system is typically made up of multiple modules that interact together, share information amongst themselves and each other, and provide management with a broad, all-encompassing picture of the entire enterprise. These systems can now be used to meet needs in any industry.

Within the software is stored the information that management needs to operate its business day to day. ERP software systems break down the departmental barriers that sometimes still exist in organizations and allow the information that may have been in silos before to be shared across the enterprise. Further, it takes a process-oriented view of the organization and uses that view to allow the organization to meet its goals by tightly integrating all aspects of the organization. With ERP software, a company can better integrate its entire supply chain, automate many of its processes, and reduce its lead times and exceptions to the process along the way.

CRM is the process of finding, getting, and retaining customers. It encompasses the methodologies, strategies, and other capabilities that help a company or enterprise organize and manage its customer relationships, as well as the software tools to help achieve those ends. Today, many companies focus on the wants and needs of the customer, so the ability to track information about the customer, learn from that information, and use that information to better serve the customer is crucial. CRM helps a company learn what works and what does not. It helps the company identify the profile of the most profitable customers, gain a deeper understanding of the most and least profitable customers, and will allow the company to target the most profitable customer profile when it is searching for new business. For companies that are forming alliances with business partners, CRM is centralizing information on the customer base in a way that can be shared between partners to help to create products to better serve the end user. Before, customer-centric information was likely already stored within the company. It was unlikely, however, that this information was stored in a central location or that it was easily accessible by multiple departments therefore reporting on customer information in an enterprisewide manner was nearly impossible. If it is difficult to report on, it is likely nearly impossible to perform analysis on.

CRM will help your customer base, and your reputation within that base, by allowing faster response to customer's inquiries because the information is centrally stored and accessible by the people who are interfacing with the customer.

With the advent of Oracle E-Business Suite 11i, Oracle made use of some of the newer features of its 8i database and built new functionality into its 11i product suite. You should have some understanding of many of the ones that you may not necessarily be using or dealing with directly on a daily basis, because their functioning will impact you at some point.

Oracle 8i New Features

Cost Based Optimizer

While cost based optimization was released in Oracle 7, Oracle 8i brought with it significant improvements including many bug fixes and extended features. Oracle strongly suggests that everyone over Release 8i use the Cost Based Optimizer (CBO). They support Rule Based Optimizer (RBO) only for compatibility with existing applications. With the CBO, query optimization is calculated differently and the queries run more efficiently than under the RBO in past releases. In a non-Apps installation of the 8i database, you have the option to choose which optimization to use. In Apps 11i's Version 8i database, the preconfigured database installs CBO as the enabled optimization method and you have to allow it to use cost based optimization. The code that is written into this release of Apps was written to take advantage of the new algorithm and it is necessary that you allow it this freedom.

But what is the difference between the RBO and the CBO? The CBO is an expert system that figures all possible execution plans for a query and decides what each one's relative cost is dependent on the gathered statistics. This algorithm determines all execution plans based on available access paths and any hints that may have been used in the query. The execution plan with the lowest relative cost (based on estimated cost proportional to the resources potentially used by the query) is the one that is processed. The weighted costs include network, throughput CPU, and disk input and output (I/O). CBO execution plans may not be reliable over different releases and Oracle makes no guarantees or apologies for any potential differences. The execution plan is only as good as the statistics on which it is based; therefore, it is vital that statistics be gathered regularly on all schema objects. There is a Concurrent Program that does this for you. A Concurrent Program is a program that runs as a batch job in the background (either on command or on a set schedule) while not impacting the end users' ability to work on transaction processing by a Concurrent Manager (a service that resides either on your database tier or on a middle tier). It can be run either manually on demand, or as a scheduled job and should be run on at least a weekly basis. Even with new, valid statistics, the CBO often still makes bad decisions. On the whole, however, it is a vast improvement over having to have all of the rules coded into the program logic.

The CBO has several relevant init<SID>.ora parameters. The parameters and their required (relative to Apps) and current values can be gathered by running a script that can be found under the <FND_TOP>/sql directory called AFCHKCBO.sql (this script comes in patch 1245516 if it is not already in your directory tree). A sample of the output can be seen in Table 2.1.

Table 2.1: AFCHKCBO.sql Sample Output

Parameter Name	Current Value	Required Value
_sort_elimination_cost_ratio	5	5
_optimizer_mode_force	TRUE	TRUE
_fast_full_scan_enabled	FALSE	FALSE
_ordered_nested_loop	TRUE	TRUE
_complex_view_merging	TRUE	TRUE
_push_join_predicate	TRUE	TRUE
_use_column_stats_for_function	TRUE	TRUE
_push_join_union_view	TRUE	TRUE
_like_with_bind_as_equality	TRUE	TRUE
_or_expand_nvl_predicate	TRUE	TRUE
_table_scan_cost_plus_one	TRUE	TRUE
_optimizer_undo_changes	FALSE	FALSE
db_file_multiblock_read_count	8	8
optimizer_max_permutations	79000	79000
optimizer_mode	CHOOSE	CHOOSE
optimizer_percent_parallel	0	0
optimizer_features_enable	8.1.6	8.1.6
query_rewrite_enabled	TRUE	TRUE
compatible	8.1.6	8.1.6
always_anti_join	NESTED_LOOPS	NESTED_LOOPS
always_semi_join	NESTED_LOOPS	NESTED_LOOPS
sort_area_size	5120000	512000 (**)
hash_area_size	10240000	1024000 (**)
Legend:
(**): Required value

Often, the results of this query will have current values set to something other than the required value. If they are significantly different, you can enhance performance by changing the values.

One parameter that needs to be set is optimizer_max_permutations (maximum join permutations of tables in queries with join conditions). It has a default value of 80,000 and a range from 4 to nearly 4.3 billion. Leaving the value at the default, in effect, tells the CBO to consider that there are virtually no limits on join conditions. Setting the value to the Apps required value of 79,000 allows the optimizer to consider more than just the starting table in the query. By regularly maintaining statistics, you can better manage lower parse times for queries in the database.

Optimizer_features_enabled and compatible are compatibility parameters and the required values are considered lower limits of what can be expected. If your database is a release higher than this and all of the required parameters are compatible with the release you are on, it is safe (and often better) to make them higher than required (e.g., if required is 8.1.6 and you are set at 8.1.7, you should be safe in your parameters).

For the CBO to do its job efficiently and effectively, statistics need to be gathered regularly. How frequently statistics are gathered is open to some debate, some suggest that monthly is sufficient, others weekly. I gather statistics immediately after our company closes their accounting books. Further, I have scheduled a concurrent job to run every week, early on Monday morning before anyone gets into the system, to gather schema statistics for all schemas (other than sys). Fortunately, there are Concurrent Programs that you can schedule that call the FND_STATS package to gather statistics for you. Analyze all index columns, gather table statistics, back up table statistics, restore table statistics, gather column statistics, gather all column statistics, and gather schema statistics can be set to run on a defined schedule so that you do not have to worry about them getting done (more on Concurrent Managers and their jobs in Chapter 12).

Materialized Views

Materialized views (MVs) are a materialization of frequently run, usually expensive queries. They are similar to standard views in that they are based on a predefined query and are similar to regular tables in that they take up storage, are queriable directly, and can be indexed on their own. MVs are an improved version of what used to be called Snapshots. There are some restrictions on what can and cannot be materialized. Typically MVs are used to precompute joins and to precalculate expensive functions on single or multiple tables like summarizing or aggregating data to allow queries to run faster. Oracle 11i has the ability to take advantage of this new 8i functionality. A few of the schemas (e.g., APPS, BIX, IBE, and the MSC) are installed with predefined MVs. These predefined MVs are not on a set refresh schedule; you will have to set that on your own. Table 2.2 is an example of a Create Materialized View statement and can give you a better idea on what you are dealing with. In particular, the Create Materialized View scripts in Oracle E-Business Suite create a MV based on a table with the same name. This registers the existing table as a MV.

Table 2.2: Create Materialized View Example

CREATE MATERIALIZED VIEW ibe_sct_search_mv ON PREBUILT TABLE WITHOUT REDUCED PRECISION REFRESH complete on demand ENABLE QUERY REWRITE AS SELECT * FROM ibe_sct_search_mv;

This solution provides performance benefits, but does not permit query rewrite in all cases or support the ability to fast refresh the MV. Fast refresh means that, on a specific schedule, ON COMMIT of a transaction against the base table or on demand when the MV administrator invokes the refresh function, all changed rows and information are migrated via an interim MV log table to the materialized view. Because these underlying existing tables can be quite large and expensive to rebuild, if a table exists that provides the functionality desired and the end goal is to improve performance, registering MVs on existing tables should be used whenever possible. The single caveat is that the base table should exactly reflect the defining definition of the MV query at the time you register the view.

Because it shares similar attributes to a regular table, you have the option of partitioning the MV. This can be an extremely attractive option when the underlying tables are extremely large, and the resulting MV becomes large enough that queries on the MV become longer and longer. Not only does the MV benefit from the same features as the base table (including improved scalability, simplified administration, efficient use of local indexes), but it has the added benefit of being able to fast refresh on the partition boundary when the base table is updated on those same boundary lines.

When you create a MV, you have the option of specifying that the view be refreshed on demand or on commit. ON COMMIT refresh allows the MV to be refreshed every time a transaction commits on the base table, ensuring that the view always contains the most recent view of the data. The DBMS_MVIEW package provides three different types of refresh operations for dealing with MVs built with the on demand manner of refresh:

• DBMS_MVIEW.REFRESH allows for the refresh of one or more MVs.
• DBMS_MVIEW.REFRESH_ALL_MVIEWS refreshes all of the MVs that are owned by the calling schema.
• DBMS_MVIEW.REFRESH_DEPENDENT allows for the refresh of all table-based MVs that depend on a specific detail table or a specific list of detail tables.

An ON DEMAND refresh can only ever be refreshed by calling one of this package's procedures.

Refreshing a MV involves considerable sorting and requires temporary space to rebuild itself and its indexes. Because of this, it may not be wise to schedule your refreshes to occur at the same time. As these are database stored procedures, they can easily be scheduled to run via a Concurrent Manager job on a predefined schedule and can have a different schedule for each refresh job that you choose to define. The choice of when to refresh has to be based in part on business decisions on the required freshness of the Mview data compared with that of the base table. Remember that a full refresh truncates the MV table before inserting a new set of the full data volume of not only what was there before, but also what is there that has been added as well.

Complete Refresh

A complete refresh, likely the option that would be best employed in the Apps instance, is also loosely defined as build immediate unless, as in the case of most canned Apps MVs, it is registered as being built on a prebuilt table. A complete refresh involves the complete rereading of the detail tables and reprocessing of all of the data from those tables. You should fully consider the resource ramifications (i.e., time and physical resources) involved before requesting it.

Fast Refresh

Fast refresh is a more efficient means of refresh because it does not need to recompute the entire query used to create it to begin with. Fast refresh relies on the changes to the underlying data triggering the refresh of the MVs. Often this is accomplished by means of a third structure, a MV log, which is a trade-off between added maintenance of objects and the speed of refresh.

ON COMMIT Refresh

The advantage of having a MV refresh ON COMMIT of a transaction on the base table is that the changes are automatically and immediately reflected in the MV. Further, this means that you never have to remember to refresh the view and your users will never access stale data. Depending on how busy your system is, one disadvantage is that it can add significant time to commit data.

When a MV is refreshed ON DEMAND, one of three refresh methods can be specified as shown in Table 2.3.

Table 2.3: Refresh Options for MVs

Refresh Option	Description
Refresh Complete	Refreshes the data by recalculating the MV's defining query every time refresh is run.
Refresh Fast	Refreshes the MV by incrementally applying all changes from the base table to the MV.
Refresh Force	Attempts a fast refresh. If that is not possible or if it fails, a complete refresh is done. Frequently this is the refresh option of choice since it allows Oracle to decide. Oracle attempts the fastest first.

Query Rewrite

Along with the ability to store the results of predefined queries in a physical structure and automate the maintenance of these structures, MVs allow you to take advantage of another Oracle 8i and newer feature — query rewrite. This feature checks the statistics that have been gathered in the database (e.g., tables and MVs) and the CBO uses those statistics to determine what queries can be rewritten to use the MVs, thus saving time. If you have any queries that make use of aggregation or summaries, you can store them in a MV and the optimizer will use them transparently. No end user or programmer need ever know that they exist. It is a tradeoff between speed and storage, with the end user being the ultimate winner.

One major benefit of creating and going through the maintenance process of defining MVs is the ability to allow queries to take advantage of this rewrite capability. Query rewrite transforms a SQL statement that is written against a particular table, a set of tables, or views to be transformed into a statement accessing one or more MVs based upon the detail tables of the initial query. Because the transformation is transparent to the end user query, the MV can be dropped, added, refreshed, or altered at any time without adversely affecting the query in any way other than performance and without invalidating indexes or application code.

When the determination on whether to rewrite or not is made, several checks are performed on the query to decide if it is a candidate for rewrite or not. If the query fails a single check, it is not a candidate. The check and subsequent redirection can at times be costly in terms of response time and CPU utilization.

The CBO uses two different metrics to determine the rewritablity of a SQL statement. First, it checks to see if the query exactly matches the MV definition query. If it does match, it uses query rewrite. This is the only test that it can fail and still be a candidate for rewrite. If it does not exactly match the query, the CBO will compare joins, selections, data columns, groupings, and aggregate functions between the query and the MV. If it can in any portion of the query run faster using the MV, it will allow for the rewrite.

Ordinarily, the CBO optimizes each given query with and without attempting to rewrite the selects and chooses the least costly alternative to allow it to perform. When the CBO chooses to rewrite the query, it can rewrite the entire query or one or more of the query blocks within the query. The evaluation is done one block at a time. If it has to choose between two MVs on which to rewrite, it will choose the one that will cause the query to read the smallest amount of data to return the required information.

Since the optimization is based on the total cost of the statement, it is critical that statistics be gathered regularly to allow the CBO to make an informed decision.

Database Resource Manager

Before Oracle introduced the Database Resource Manager (DRM), typically resource management and allocation (e.g., disk, memory, CPU, etc.) was left to the OS on which the database was running. This led to issues as the OS did not possess the ability to partition the machine's resources among tasks on priority bases and the Oracle RDBMS interactions with the OS were not always very symbiotic.

There can be significant overhead if there are several RDBMS servers on the box. Running each on its own Logical Partition, if even possible, would reduce some of the overhead associated with several engines running on one box, but would likely result in CPU contention in the end.

Scheduling and descheduling of servers is inefficient if left to the OS, particularly if the RDBMS is holding any latches servers.

Further, the OS is not built to efficiently manage database-specific resources. That is not its function; while it can do so to a significant extent, the efficiency and knowledge should be built into the database to manage this.

Oracle's DRM allows the DBA to have more control over how resources are managed than has typically been possible before. This improved control of resources enables you to provide better application performance and availability to the end users. DRM enables this by allowing you to guarantee a group of users a minimum amount of processing resources regardless of what others on the system are doing, to distribute processing by allotting different users or applications different percentages of CPU time based on their needs, to limit the degree of parallelism that one group is permitted to use over the permissions given to another group, and to set up the instance to allocate resources differently depending on what time of day it is. For instance, batch jobs are a lower priority during the day, but become a higher priority at night when there are less active users on the system.

The DRM comprises the following components, presented here with their basic functionality:

• Resource Consumer Group
  • Groups users' sessions with similar resource requirements
  • Controls the whole group's consumption
  • Allocates CPU among consumer group sessions
• Resource Allocation Plans
  • Allocates resources among consumer groups or plans
  • Contains directives that specify each consumer group's resource allocation
  • Groups the groups or plans together
• Resource Allocation Methods
  • Maintains the policies for each resource's allocation
  • Determines what method is used when allocating to a group or a plan
• Resource Plan Directives
  • Assigns groups or subplans to a resource plan
  • Allocates resources to groups in a plan by specifying parameters for each method
  • Allows exactly one plan directive for each entry in the plan

A resource plan can reference other plans (i.e., subplans) and can be referenced by others. Figure 2.1 shows one possible configuration of plans and subplans.

Figure 2.1: Resource Manager Plan — Configuration of Plans and Subplans

Resource_manager_plan, the init.ora parameter that enables the resource manager, indicates what master resource plan and its associated subplans is to be used for the given instance at database startup. The alter system command can change the resource plan that is needed to be used at any time after startup. This means that you can dynamically change plans to meet batch and transactional processing demand without resorting to restarting the database.

The DBMS packages, DBMS_RESOURCE_MANAGER and DBMS_RESOURCE_MANAGER_PRIVS, can be called to facilitate the care and management of Resource Objects.

Partitioning

Partitioning of tables has been available in previous releases of the database, but until release 11i, Apps did not take advantage of it. Partitioning breaks data in large tables and indexes down into smaller, more manageable chunks, logically grouped so that SQL statements can access the data in a single partition rather than having to parse the entire table. For very large tables, this can be a significant savings in both query processing and batch processing time.

There are two basic ways to partition in Oracle 8i: range or hash or a combination of these two. Range partitioning puts all of the data that fits between two boundaries into the same partition. Frequently the data is grouped by date, location, or other logical boundary. An example of range partitioning is every purchase order for the year 2001 or every widget that is in the Pittsburgh warehouse. Hash partitioning uses a hash function to determine where to store the data. Subpartitioning, or partitioning already partitioned data further, will speed data access up further and help evenly distribute data within the partition (thus helping to avoid hot spots). Table 2.4, Table 2.5, and Table 2.6 show examples of using range partitioning. Table 2.4 is raw data, unpartitioned.

Table 2.4: Partitioning Raw Data

PO_NUMBER	PO_DATE	PO_VENDOR	PO_LOCATION
1234	10-Jun-02	Smith's Office Supply	Pennsylvania
2345	8-Jun-02	Smith's Office Supply	Pennsylvania
5678	7-Jul-02	Smith's Office Supply	Ohio
7891	4-Jul-01	Smith's Office Supply	Ohio
7410	8-Jun-02	Smith's Office Supply	Colorado
8520	28-Jun-02	Smith's Office Supply	Mississippi
9630	20-Jun-01	Smith's Office Supply	Mississippi
1478	14-Jan-02	Smith's Office Supply	Colorado
2589	23-Jan-03	Smith's Office Supply	Tennessee
3697	7-Sep-02	Smith's Office Supply	Tennessee

Table 2.5: Partition by Date

PO_NUMBER	PO_DATE	PO_VENDOR	PO_LOCATION
9630	20-Jun-01	Smith's Office Supply	Mississippi
7891	4-Jul-01	Smith's Office Supply	Ohio
1478	14-Jan-02	Smith's Office Supply	Colorado
2345	8-Jun-02	Smith's Office Supply	Pennsylvania
7410	8-Jun-02	Smith's Office Supply	Colorado
1234	10-Jun-02	Smith's Office Supply	Pennsylvania
8520	28-Jun-02	Smith's Office Supply	Mississippi
5678	7-Jul-02	Smith's Office Supply	Ohio
3697	7-Sep-02	Smith's Office Supply	Tennessee
2589	23-Jan-03	Smith's Office Supply	Tennessee

Table 2.6: Partition by Date, Subpartition by Location

PO_NUMBER	PO_DATE	PO_VENDOR	PO_LOCATION
9630	20-Jun-01	Smith's Office Supply	Mississippi
7891	4-Jul-01	Smith's Office Supply	Ohio
1478	14-Jan-02	Smith's Office Supply	Colorado
7410	8-Jun-02	Smith's Office Supply	Colorado
2345	8-Jun-02	Smith's Office Supply	Pennsylvania
1234	10-Jun-02	Smith's Office Supply	Pennsylvania
8520	28-Jun-02	Smith's Office Supply	Mississippi
5678	7-Jul-02	Smith's Office Supply	Ohio
3697	7-Sep-02	Smith's Office Supply	Tennessee
2589	23-Jan-03	Smith's Office Supply	Tennessee

Table 2.5 is partitioned by date alone.

Table 2.6 shows the data partitioned by date and within date by location. The query to get all purchase orders in 2001 would run as effectively on either Table 2.4 or Table 2.5, however, the performance for all purchase orders for 2001 for the state of Pennsylvania would be much better on Table 2.6.

Partitioning can help you to balance I/O (depending on your disk configuration), separate different parts of the data or index for access and maintenance speed, and help provide the ability to restore parts of tables independently (leading to potentially shorter mean time to recover).

Several schemas in the predefined E-Business Suite database have tables that are partitioned and several more are large enough that they could be. You should keep this in mind when looking at ongoing, longterm maintenance, as you do not want one partition to hold the majority of your data, which defeats the purpose of partitioning.

Parallel Server

Oracle's Parallel Server harnesses the massive processing power of multiple interconnected (or clustered) computers. In a cluster, the united processing capability of each server combines to becomes a single computing environment. Each computer is considered a node and each node processes transactions against a single database. Parallel Server coordinates the access to the data from the nodes and provides for consistency and data integrity, providing a solution to speed access (several small processes running against the database is more efficient than one huge transaction), while helping to achieve high availability. With several nodes contributing to the whole, the failure tolerance is higher. If one node goes down, the slack gets picked up by the remaining nodes and redistributed. This lessened chance of hardware downtime implies higher availability of the database and of Applications to the end user.

A clustered environment is highly scalable. Throwing more users onto a system that has the processing power of several larger servers will show less of an impact than if those same users were added to a single server environment.

Temporary Tables

Apps 11i now makes use of Oracle 8i's temporary tables feature. Temporary tables exist only for the duration of the session that owns them or for the duration of the transaction that creates them. They hold data privately, locked so only that session can update or modify it. These tables live only in memory and, because they are never written to disk, there is no redo associated with the Data Manipulation Language (DML) that is associated with the temporary table's data. Because there is no redo associated and because they are only accessible by the session that owns them, there are no DML locks required when doing data manipulation within them.

Applications Manager for Oracle Enterprise Manager

The new Oracle Applications Manager (OAM) module for Oracle Enterprise Manager (OEM) allows administrators to better manage Oracle E-Business Suite systems from a central console. This console can be used for a wide variety of tasks including monitoring and tuning the running processes on the system. With the familiar look and feel of the other OEM products, it allows a DBA to make the leap between just DBA and Applications DBA.

Invoker's Rights

In past releases, the addition of Multiple Reporting Currencies (MRC) and Multiple Sets of Books to the Oracle Applications architecture brought with it interesting challenges. In companies where these features were implemented, many of the packages that Apps makes use of had to be stored several times each in the database: once for each currency and once for each set of books. This led to longer upgrades and maintenance times as each of the copies had to be taken into account every time an upgrade was performed and every time a minipack or family pack patch was applied. Extra space was also required to store each additional copy of these in the database. This model, that of the user executing a stored procedure and having it execute under the authority of the owner of the package, was known as definer's rights (still the default execution method). The model assumed that an entire application would likely be built within one single Oracle account. Oracle Applications now has over 150 different schemas and owners, so this is not a realistic expectation.

Oracle 8i brings with it a functionality called invoker's rights. Invoker's rights allow each package to be stored once and accessed by different users, each user using their own synonym for the package or procedure and they are only permitted to perform those functions that their role or granted privileges allow them to perform and not the actions that the owner has the rights to perform. This is particularly powerful in the Oracle E-Business Suite, where the Apps schema may own packages that, if run as Apps, can see anything in the system. But if the purchasing department is allowed to see only the relevant personal information about a purchase order requester (e.g., name, department), but not update or delete any of it and that information is stored in the human resources (HR) tables, under definer's rights, purchasing would be able to change the values. Invoker's rights say that the package that queries the values from that table can be created with select, insert, update, and delete privileges, but only HR can insert, update, or delete and purchasing can select and only select, those columns from those tables that are relevant to a purchase order. The added safety of having a procedure or package defined with invoker's rights is that any unqualified call to the procedure will attempt to perform on the schema of the user calling the procedure. One package can now meet these needs, provide a more secure and efficient application, and have one central place for reusable code.

Locally Managed Tablespaces

Until recent releases of the database, the space allocation for all objects was handled in the data dictionary. This could lead to performance problems as new extent allocation requires several single threaded recursive queries to be run on the System tables to find the free space, allocate it to the new extent, and then mark that space as used. The employee table needs another extent? Go check for free space in the free extents table. If there is free space, go ahead and allocate it to the employee table, but now you have to tell the used extents table that you now have one more used extent. These statements generated rollback, themselves causing further single threaded recursive statements to be generated: one row of redo for each insert, one row of redo for each delete, and a row of rollback for the row that is deleted. If you truncate a table (freeing all used extents) or drop a table (again, freeing all used extents), there would now be multiple rows inserted as available and deleted from being marked as used. These inserts and deletes would be occurring on the data dictionary and the corresponding redo would be happening at the same time. Locally managed tablespaces are tablespaces that use bitmaps to manage their own extents, rather than the data dictionary to do the management for them. These bitmaps keep track of what datafile blocks are used or free; each block of each datafile has its own bit in the bitmap that is switched on or off to show the allocation of each given block. Allocation of a block means flipping one bit from a 0 to a 1; deallocation means changing back from a 1 to a 0, with no rollback and no redo associated. Temporary Tablespaces and Rollback Tablespaces have significant amounts of allocation and deallocation and are therefore prime candidates for being locally managed. There is less chance for contention on these data dictionary tables if you do not have to go to them on a regular basis. Because they are self-managed and no Table updates are done, nearly no rollback information is generated. Further, the management of the extents tracks adjacent free space automatically. This means that there is no longer any need to manually coalesce free space (this is now automatic by design) and significantly reduces fragmentation (this is particularly true if you use uniform allocation).

In some Applications 11i installations, locally managed is the default way that tablespaces are built. In others, running dbms_space_admin.tablespace_migrate_to_local and passing it, the tablespace, as a parameter will allow you to migrate existing dictionary managed tablespace to locally managed ones.

Diagnosing and Repairing Locally Managed Tablespace Problems

DBMS_SPACE_ADMIN is the package that provides database administrators with detection, diagnosis, and repair functionality for locally managed tablespaces. It cannot, under any circumstances, be used for dictionary managed tablespaces. This is also the procedure that you use when you are migrating from dictionary managed to locally managed and back again. The DBMS_SPACE_ADMIN package contains the following procedures:

• Segment_verify: This procedure verifies the consistency of the extent map for the segment. Does this tablespace currently require repair?
• Segment_corrupt: This procedure marks a segment as either corrupt or valid so that the appropriate recovery can be performed on it. This procedure can never be used on the system tablespace, as any corruptions found in that tablespace cannot be dropped.
• Segment_drop_corrupt: This procedure provides the ability to drop a segment currently marked as corrupt without reclaiming the space that was marked as corrupt. This can never be used on a locally managed system tablespace.
• Segment_dump: This procedure dumps the segment headers and extent map of any given segment.
• Tablespace_verify: This procedure verifies that the bitmaps and the extent maps for the segments in the tablespace are in sync.
• Tablespace_rebuild_bitmaps: If Tablespace_verify shows that the bitmaps are not in sync, use this procedure to rebuild the appropriate bitmaps. This procedure cannot be used on the system tablespace.
• Tablespace_fix_bitmaps: This procedure marks the appropriate data block address ranges to reflect their true status. They will be marked as free or used within the bitmap. This procedure cannot be used on a locally managed system tablespace.
• Tablespace_migrate_from_local: This procedure migrates the passed in locally managed tablespace to a dictionary managed tablespace. You cannot use this procedure to change a locally managed system tablespace to a dictionary managed system tablespace.
• Tablespace_migrate_to_local: This procedure migrates the passed dictionary managed tablespace to system managed tablespace. This procedure can be used on the system tablespace.
• Tablespace_relocate_bitmaps: This procedure relocates the bitmap portion of the specified tablespace (not the system tablespace) to the destination specified.
• Tablespace_fix_segment_states: If the migration of a tablespace gets aborted, from system failure, error condition, or because someone closed the session in which the migration was running, this procedure fixes the state of the segments in that tablespace.

None of these procedures should be used without a backup from which you can restore your system. Some of these procedures, if not used correctly, can result in lost and totally unrecoverable data. If you are not comfortable with what they are doing or exactly sure how to use them, do not hesitate to get the assistance of Oracle Support. It will be quicker to get help in not corrupting your data than it will in dealing with a severity 1 iTAR needed to fix the corruption.

How would you use these procedures? Let us look at some situations under which you might be able to use these. This is where you will find the drawbacks to having your tablespaces migrated to locally managed. No longer will all maintenance of the tablespace be taken care of by the system. No longer can you simply assume that there are no issues with your database just because there are no apparent problems. You will now have to run these package procedures to ensure the status of the tablespaces, identify any issues that may arise, and fix the issues after they are uncovered. It is a good administrative practice to attempt different scenarios with the dbms_space_admin and your own set of test locally managed tablespaces so there is not a panic situation if you have to run any of these procedures in a real problem situation.

1. You have decided to run the tablespace_verify procedure to find out if the bitmap and extent maps for the IBED tablespace have any issues. The results indicate that there are several blocks that show up as allocated in the bitmap, but that have no overlapping allocated segments in the extent map. What do you do?
   1. Call dbms_space_admin.segment_dump to dump the ranges that have been allocated to that segment.
   2. For each of the dumped ranges, call dbms_space_admin.tablespace_fix_bitmaps with the tablespace_extent_make_used option to mark the space as used.
   3. Call dbms_space_admin.tablespace_rebuild_quotas to fix the quotas for that particular tablespace.
2. You are trying to unallocate a segment in the ARD tablespace and you are having problems dropping those segments. You discover that you cannot drop the segment because the segment blocks were marked as free and now the system has remarked them as corrupted. What can you do other than export the data from the tablespace, drop and recreate the tablespace, and reimport the information into the rebuilt tablespace?
   1. Run dbms_space_admin.segment_verify with the optional parameter segment_verify_extents_global. If the results returned show no overlaps, proceed; otherwise, contact Oracle Support for assistance before proceeding.
   2. Call dbms_space_admin.segment_dump to dump the ranges that are allocated to the segments.
   3. For each of the ranges returned, call dbms_space_admin.tablespace_fix_bitmaps using the tablespace_extent_make_free parameter to mark all appropriate spaces free.
   4. Call dbms_space_admin.segment_drop_corrupt to drop the corrupt segment entries from the seg$ sys table.
   5. Call dbms_space_admin.tablespace_rebuild_quotas to fix the quotas associated with the tablespace.
3. You have run dbms_space_admin.tablespace_verify and the results show that there is overlapping in the tablespace. Internal errors that you have become aware of indicate that there has to be some data sacrificed (e.g., there have been ORA-26082 errors when attempting to direct path load to one of the interim tables). You have to determine what objects overlap and which of these objects that you can afford to sacrifice.
   1. Drop the table that you have chosen to sacrifice.
   2. Optionally, run dbms_space_admin.segment_drop_corrupt.
   3. Call dbms_space_admin.segment_verify on all of the objects that t1 may have overlapped.
   4. If necessary, run dbms_space_admin.tablespace_fix_bitmaps to mark the appropriate blocks as used.
   5. Rerun dbms_space_admin.tablespace_verify to make sure that any overlap is gone.
4. You have determined that there is media corruption causing problems with bitmap blocks in your GLD tablespace.
   1. Call dbms_space_admin.tablespace_rebuild_bitmaps on either all bitmaps in the tablespace or only on the particular corrupt ones.
   2. Call dbms_space_admin.tablespace_rebuild_quotas.
   3. Call dbms_space_admin.tablespace_verify to make sure that the bitmaps in the tablespace are now in a consistent state.
5. Your implementation was done prior to Oracle packaging the Applications database with the parameters allowing all tablespaces to be built as locally managed. You have decided to migrate your entire database to locally managed tablespaces. How do you do this? The following code will assist you in migrating nonsystem tablespaces to locally managed tablespaces.

   Set head off
   Set echo off
   Spool migrate.sql
   Select 'execute
   dbms_space_admin.tablespace_migrate_to_local('||
   tablespace_name||');'   from v$tablespace where
   tablespace_name = 'SYSTEM';
   Spool off
   Set head on
   Set echo on
   
   @migrate.sql
   

But what about migrating the sys and system tablespaces? In Oracle 9i it is not only supported but encouraged.

Execute
dbms_space_admin.tablespace_migrate_to_local
('SYSTEM');

Similar to the nonsystem tablespace, you use the dbms_space_admin procedure to do the migration. Before you start the migration procedure, there are several things that you need to take into account and make sure you have taken care of:

• Have defined a default temporary tablespace that is not in the SYSTEM tablespace.
• Have no dictionary managed rollback segment tablespace.
• Have at least one online rollback segment in a locally managed tablespace or migrated to Automatic Undo Management and that tablespace is online.
• Migrate any tablespaces to which you ever want the ability to perform write operations against in the future to locally managed. Once system tablespace has been migrated to locally managed, any tablespaces that were dictionary managed at the time of migration can no longer be opened for read/write access.
• Have all tablespaces other than SYSTEM and the undo or rollback containing tablespaces in read-only mode.
• Have a reliable cold backup of the database and have it available. This is always a safe precaution to take.
• Open the database in restricted mode for the duration of the procedure.

Once you have made sure that all of these conditions are met, you can safely proceed with the migration process. As soon as you have completed the migration of the system tablespace to locally managed, you should immediately test to make sure that your system can function with the new configuration. As soon as possible, take an additional cold backup of the database with the new configuration.

11i New Features

Internet Computing Architecture

Apps 11i is the first 100 percent fully Internet deployed version of the front end. Internet computing architecture distributes background processes across different nodes on the network. Oracle Apps 11i is typically deployed on three tiers (although the number of nodes varies). The database tier is where the database is housed. The desktop tier provides the graphical user interface (GUI). In the case of Apps 11i, this means the presentation layer via a plug-in to your standard browser.

On the middle, or application, tier is where the tools that manage the applications reside. This tier allows for a central installation location and eliminates the need to install client software on individual desktops. It also allows Apps to be scalable and limit bottlenecks in network traffic. This middle tier can reside on one or more nodes in the network.

Forms Server

The Forms Server runs on the application tier and is the intermediary between the Forms Client (a Java applet run from the desktop) and the database. The two pieces exchange messages via a normal network connection. The presentation piece runs in any Java-enabled browser and manages the downloading, startup, and execution of the Forms client, which displays Apps screens, provides field validation, coordinates multi-window interaction, and data entry and validation features (e.g., lists of values and value lookups). The Forms Server also helps run the 9iAS server. This service helps to start the client's session over the intranet or Internet. It is possible to run multiple forms servers to do load balancing among network nodes.

HTTP Server

Oracle Self-Service Web Applications

Oracle Self-Service Web Applications (SSWA) product family allows users to perform fast, simple entering, updating, and transferring of information within an organization and within the application. Through this interface, and all of its components, customers can enter orders, enter billing disputes, or perform collection inquiries. It allows employees to change their own addresses, but not their salaries. It is designed for secure, self-service business transactions across an intranet, an extranet, or over the Internet. It has several components that can be implemented in many configurations to serve different definitions of end users (i.e., Oracle Web Employees, Oracle Web Customers, and Oracle Web Suppliers).

The architecture of OSSWA consists of the following components:

• Desktop Web browser (Java enabled).
• Oracle's rendition of the Apache HTTP server with Oracle specific extensions.
  • Mod_plsql is an Oracle specific module that routes all PL/SQL requests to the Oracle Universal Server through the Database Access Descriptors (DAD) file.
  • Mod_cgi provides for the execution of Common Gateway Interface (CGI) applications.
  • Mod_ssl deals with data security via cookies, encryption, and session expiration.
  • Mod_jserv routes servlets requests to JServ server engine, which then executes through Java Virtual Machine (JVM). This is one of the key, core components that you will deal with.
  • Mod_perl.
• HTML documents.
• JavaServer Pages (JSP)
• JavaBeans are reusable Java classes that have specific naming conventions for methods and variables. They perform well-defined tasks that are reused over and over (e.g., connecting, screen rendering).
• Java Servlets are small key components of server side Java development.

Business Intelligence System

BIS is an integrated decision support system through which a manager can monitor recent business performance across multiple organizations, set tolerances, and perform some corrective actions if tolerances are exceeded. BIS works with the Discoverer™ Server and the Oracle Reports Server and runs via a Java applet that communicates with the HTTP Server, which in turn communicates with the Discoverer Server (to allow for adhoc analysis) or the Reports Server (for data analysis and queries using summary and aggregate data). To support the Discoverer/BIS interaction, it is necessary to create, in the database, a repository called the End User Layer (EUL) to house Discoverer workbooks and other metadata in the database.

Personal Home Page

The Personal Home Page (PHP) is another addition to Apps in the 11i release. Your PHP provides a customizable interface between the user and the application. It allows one central page from which to link to all of your Applications responsibilities, access forms-based pieces of the application, SSWA, and BIS products. Through the PHP, you can navigate to anywhere in the system that you are permitted to work via a single log-on. It also allows you to have the freedom to change your password, access your work area, your reports, submit your concurrent requests, set preferences (Do you need Spanish to be your preferred language? PHP will tell all html and forms-based interfaces that is your preference) and contact Oracle from one central launch point.

Figure 2.2 shows an example of the PHP of one system administrator (sysadmin). What shows up on this page depends on what roles in the application the user is permitted to have.

Figure 2.2: Personal Home Page Example

Product Global Scope

Oracle is committed to providing one product that meets global, regional, and local requirements for organizations of any size or definition. In previous releases, Oracle packaged one central product that conformed closely with U.S. standards (American English with accounting configured to closely adhere to generally accepted accounting principles, GAAP). Oracle packages extensions called localizations that could be added to the base product to provide additional languages of screens and reports, added software features, and added reports that met local statutory regulations. All of these features are now included in the single Oracle Applications product.

Multiple Language Support

Oracle E-Business Suite's Forms and Reports are now packaged in more than 30 base languages so that people can see the screens in their language without need for an American English installation and then an added piece to allow someone installing in Paris, for example, to have to add customizations to view screens in their language. It also allows a company with employees who have different natural languages to be able to have the screens presented in their languages as well.

Release 10.7 required a customization to provide you with the ability to run Oracle Applications in more than one language. Release 11 had the ability to run in more than one, but still a limited number of languages. The number of languages is limited by the character set chosen. It still required that you input data in the base language, but the form presented to the user could be presented in a language of choice. Languages other than the base language again required costly customizations. Oracle 11i brings with it the ability to run the majority of the products in a multilingual environment. Support for the Unicode UTF8 character set allows the limit on the number of supported languages on a single Apps instance to be removed. UTF8 supports inclusion of all characters commonly used in all of the modern languages found worldwide.

The languages available at the database level are still reliant, to a large extent, on what character set is chosen at installation time. Rapid Install will lay down the database with a default character set of US7ASCII if you do not tell it that you want anything different. US7ASCII supports American English. If you need English and French or you need the ability to store numbers in Euros, you need to select WE8IS08859P15 (a superset of US7ASCII). Inclusion of Japanese would require you to tell Rapid Install that you want to install in UTF8.

Careful consideration needs to be given to the decision of character set. If you choose US7ASCII and later realize that you needed the functionality of storing Euros or your company expands to the Czech Republic, it will mean an expensive and time-consuming character set conversion and an increase in the storage requirements for the data already stored in the database. For instance, US7ASCII takes up 7 bits; if German is required, the special characters native to German take up an additional bit; and if Japanese is a required language, each character requires 16 bits or 2 bytes for storage.

When the application tier is installed, Rapid Install also, by default, uses US7ASCII. Unless you tell it otherwise, you will be limited to this character set. To allow for ease of translation and lack of data loss, the character set on each tier should match, or be a superset of each other. The easiest way to ensure this is to install UTF8, as it is the only one that is a superset of all. While this may seem somewhat drastic, given your current circumstances, should it become necessary later to include data from another country, its inclusion in UTF8 will allow for a more elegant conversion. The only compatibility caveat to this is that the HTTP server and the browser of choice need to understand and support the same character set. If you are going to use UTF8, you must ensure that the desktop tier supports a font that is UTF8 compliant and make it available to all client machines. Further, the browser needs to be configured so that data inputted be able to be formatted in the required language and it needs to be able to handle any language specific display capabilities (right to left or special character shaping). The character set used is set by the HTTP server at connection time and users need to be aware that they cannot change character sets in their browser at any time during an Apps session.

One added benefit to the multiple language support of Apps 11i, is that it is possible to set up presentation and printing of external documents (e.g., purchase orders, invoices, bills of lading) to be in the preferred language. For example, you can print all French invoices at a given network printer in Paris, all Polish purchase orders to print in Poland, and all Spanish bills of lading to print at the user's printer in Madrid. The System Administrator's Guide will help you determine what is available for your release.

Multiple Organization Architecture

Oracle E-Business Suite is designed with all of the flexibility and features needed to handle commercial and nonprofit organizations alike. It can work for any organizational structure, no matter how simple or complex. Because of the nearly infinite number of ways that a company can define itself, Apps contains, at its most basic, a general organizational model that can be customized to fit any definition. An important part of an Oracle E-Business Suite implementation is determining how closely your organization meets this generic definition and to what extent it needs expanding. This definition is usually done at the functional level and the only time that it needs to impact the technology side is in the implementation of features. Multi-Org (Multiple Organizations) is an 11i enhancement that allows for the support of multiple organizations within a single company and within a single database instance.

Multi-Org allows the business to dictate how any given transaction will flow through the different organizations within the business model. The defined organizations can be as narrow or as broad as the business dictates. Within a local company, an organization can be the finance, accounting, human resources, or purchasing departments. They can be defined as legal entities, balancing entities, operating units, inventory organization, HR organizations, different lines within a manufacturing company, client companies in an Application Service Provider environment, or units within divisions of a conglomerate. Any complex corporation today can be subdivided and therefore take advantage of the Multi-Org subsetting to define roles and responsibilities and to assist in the proper flow of information within the business. These more complex organizations can create multiple sets of books and each set of books can be complete with their own subledgers. This allows you to sell and ship what is sold to different legal entities or logical business entities across many sets of books. As stated above, this can also mean that within the same legal entity (itself within a single parent conglomerate), users can be separated within different operating units (e.g., HR, purchasing, accounting) and being assigned to these different units limits what data each user can or cannot see from other operating units. This feature adds an additional level of security as well.

Although the ability to define a Multiple-Organizations organizational structure has been included in releases as far back as Release 10.6 and has been standard since Release 10.7, 11i brought with it enhancements and flexibilities that make it simpler and more elegant to implement.

A global company will rely on Multi-Org as the basis of their Multiple Reporting Currencies.

Multiple Reporting Currencies

MRC allows you to maintain your financial information in one base, functional currency and report on that information in others. This is done by defining your primary set of books and multiple other reporting sets of books. Examples of common uses for this are when you need to maintain records in the euro and report on transactions in local currency or when the parent company resides in the United States and maintains plants in the United States as well as in other countries and there is a need to report on information in different currencies for the different locations.

Additional Global Features

Oracle Applications now supports many different ways to enter mail addresses based on the national address of that recipient. The same columns store the data, regardless of where the destination address is; however, the input screens can allow for the specifics of the destination country rather than requiring the address to be fitted into the U.S. Postal Service's standard format.