Oracle Applications Tablespace Model Release 11i

Introduction

In Oracle Applications Release 11i, each Applications product has been allocated two tablespaces, one for tables and one for indexes. With 191 products in the 11.5.8 release, this has meant 382 tablespaces, in addition to the temporary tablespace, system tablespaces, and rollback segments.

The Oracle Applications Tablespace Model (OATM) introduces a new, consolidated tablespace model in Oracle Applications, that uses 12 locally managed tablespaces for all products, including the temporary tablespace, system tablespace, and undo segments. In this revised Oracle Applications Tablespace Model, each Oracle Applications database object is mapped to a tablespace based on its Input/Output characteristics. This new model allows for easier maintenance, reduces space usage, and allows for potential run-time performance gains.

The Oracle Applications Tablespace Migration Utility is an interactive menu-based PERL program including a set of sizing estimate reports. It enables customers to convert their Oracle Applications databases to the new tablespace model (OATM) for either all schemas at once or partially for select schema(s) at a time, depending on acceptable down time and available disk space.

Benefits

The Oracle Applications Tablespace Model (OATM) provides a number of benefits over the previous tablespace model.

OATM takes into account the following object I/O characteristics of an object:

• Its size
• Its lifespan
• Its access methods
• Its locking granularity

OATM facilitates:

• Administration and configuration ease for Oracle Applications tablespaces
• Locally managed tablespace extent and segment management features
• Improved space utilization after the migration
• Increased block-packing, which will reduce the overall number of buffer gets
• Taking advantage of wide stripe configurations

Fewer, Consolidated Tablespaces

The most obvious benefit is the reduction in the sheer number of tablespaces(from 300+ to twelve), which results in easier maintenance.

Real Application Cluster Support

OATM also facilitates Real Application Cluster (RAC) support. RAC is an Oracle9i Database feature that harnesses the processing power of multiple interconnected computers where all active instances can concurrently execute transactions against a shared database disk system. OATM is critical when implementing RAC on Linux, where currently there is a limitation of 255 raw devices.

Locally Mananged Tablespaces

OATM uses locally managed tablespaces and provides support for either UNIFORM or AUTOALLOCATE extent management, available with locally managed tablespaces. Locally managed tablespaces have benefits over dictionary-managed tablespaces in the previous model and allow for the sizes of extents to be determined automatically by the system (AUTOALLOCATE). Alternatively, all extents can have the same size (UNIFORM) and override object storage options, with uniform extent size.

User Extent Management, another configuration supported with Locally Managed Tablespaces, is of relevance only in case of Dictionary Tablespaces that have been migrated over to Locally Managed Tablespaces . Since the migration to OATM is performed at the object level and not at the tablespace level, User Extent Management is not relevant. The default recommended configuration in OATM is Uniform Extent Management. The migration utility recommends the default of 128k uniform extents which can be changed to suit the customer database. Note however that if you use another extent size, that size must still comply with the minimum extent size required for your Oracle Database version. If you encounter issues when using a lower size than the default, increasing the uniform extent size may help resolve those issues.

Improved Space Utilization

The tablespace migration utility migrates objects from the existing dictionary-managed tablespaces to locally managed tablespaces with automatic segment management and either uniform or autoallocated extent management. This results in improved space utilization. While migrating data and index segments from the old tablespaces to OATM, the OATM Migration Utility reclaims unused space, especially in case of fragmentation.

Product Tablespaces

In the previous tablespace model, there were two tablespaces for each Oracle Applications product, one for the tables and one for the indexes. The standard naming convention for tablespaces was to suffix the product's Oracle schema name with "D" for "Data" tablespaces and "X" for "Index" tablespaces. For example, the tablespaces APD and APX were the default tablespaces for Oracle Payables tables and indexes, respectively.

OATM, as planned to be introduced in Release 11.5.10, comprises nine default tablespaces for applications objects in addition to Undo, Temp, and System database tablespaces. Indexes on transaction tables are held in a separate tablespace dedicated for transaction table indexes, whereas all other indexes are held in the same tablespace as the parent/base table.

Following are the tablespace types available out of the box with OATM:

Tablespace Type	Tablespace Name	Contents
Transaction Tables	APPS_TS_TX_DATA	Tables that contain transactional data.
Transaction Indexes	APPS_TS_TX_IDX	Indexes on transaction tables.
Reference	APPS_TS_SEED	Reference and setup data and indexes.
Interface	APPS_TS_INTERFACE	Interface and temporary data and indexes.
Summary	APPS_TS_SUMMARY	Summary management objects, such as materialized views, and other objects that record summary information.
Nologging	APPS_TS_NOLOGGING	Materialized views not used for summary management and temporary objects.
Advanced Queuing/AQ	APPS_TS_QUEUES	Advanced Queuing and dependent tables and indexes.
Media	APPS_TS_MEDIA	Multimedia objects, such as text, video, sound, graphics, and spatial data.
Archive	APPS_TS_ARCHIVE	Archive-purge-related objects.
Undo	UNDO	Automatic Undo Management (AUM) tablespace. UNDO segments are identical to ROLLBACK segments when AUM is enabled.
Temp	TEMP	Temporary tablespace for global temporary table, sorts, and hash joins.
System	SYSTEM	System tablespace used by the Oracle Database.

Tablespace Classification

The new Oracle Applications Tablespace Model (OATM) relies on certain explicit and implicit tablespace classification rules. These implicit and explicit tablespace classification rules are determined based on storage considerations for the object type in question. The following implicit classification rules, based on object types, apply in OATM.

Object Type	Tablespace Type
Advanced Queuing (AQ) Tables	Advanced Queuing /AQ
Global Temporary Tables	The database always creates these objects in the Temporary (TEMP) Tablespace..
IOTs (Index Organized Tables)	Transaction_Tables
Materialized Views	Summary
Materialized View Logs	Summary
Domain Indexes	Transaction_Indexes
All Other Indexes	With the exception of indexes on tables classified as "Transaction_Tables", all indexes will share the same tablespace type as the base object (Table/Materialized View). Indexes on tables classified as "Transaction_Tables" will reside in the "Transaction_Indexes" tablespace type.

Explicit tablespace classification rules pertaining to other objects, such as tables, are defined internally in Oracle Applications.

Changing datatype of column from varchar2 to number when data present in that column

You can use the Oracle “alter table” syntax to change the data type for an existing column, as shown in this example:

alter table
   table_name
modify
(
   column_name    varchar2(30)
);

Of course, you must deal with existing column data.  When modifying a tables column datatype you may want to export the rows, redefine the table and then re-import you data.

Following is the one scenario where we get ORA-01722 error while changing existing columns datatype from varchar2 to number, here table contains the lacks of records.

Column that we are going to modify datatype contains only numeric records  but that data was inserted when its datatype was varchar2. Now it is challenge to modify this columns datatype. 

select * from xyz 
Suppose above table contains some records.

Create the backup table for above table by executing following command

create table xyz_bkp as select * from xyz

Modify the column using following command

alter table xyz_bkp modify abc number(20,4)

You may get the ORA-01722 error

Then check by adding new column to that table and then try to update all the records from existing column.

alter table xyz_bkp add abc_1(15,2);

update xyz_bkp

set abc_1=abc;

Here also we received the ORA-01722 error.

Now lets know about this error message.

ORA-01722 is thrown because a particular string was not able o be converted into a specific valid number when a user attempted to convert a character string. 

There are several possible resolutions to Oracle ORA-01722 in this context:

• If you are attempting an " INSERT INTO ... VALUES (...) " you need to find out which data item is invalid
• If you are trying to supply the values in a sub query which is intended to INSERT or UPDATE, you have to dig a little beeper because the character that is throwing ORA-01722 is actually hidden.  You would then need to find the row that contains a non-numeric string.  Then, you should fix or add data to resolve ORA-01722
• Instead of an INSERT or UPDATE, you attempt a SELECT.  Here, ORA-01722 is thrown most likely because of some implicit conversation in WHERE.  To resolve ORA-01722 in this context, find a numeric and character column which are being compared.

I used the following method to remove character string or any other characters other than number from that column.

update xyz_bkp

set abc_1= RTRIM

          ((LTRIM

               (TRANSLATE

                   (REPLACE (abc, 'A', ''),

                       'ABCDEFGHIJKLMNOPQRSTUVWXYZ

abcdefghijklmnopqrstuvwxyz@#$%^&*()_+-=,!\`~{}./?:";''[]'

                    || CHR (160)

                    || CHR (32)

                    || CHR (0)

                    || CHR (253),

                    'A'

                   )

               )

           )

          )      

This statement will remove the special characters or character string from abc column.

Now remove or drop the existing column 

alter table xyz_bkp drop column abc;

Rename new column with existing column's name

alter table xyz_bkp rename column abc_1 to abc

If all the above steps are successful the proceed to do same in your main table.

Steps to import data from 11g to 9i database

Steps to import data from 11g to 9i database


In 11g database Change the definition of view EXU9DEFPSWITCHES from:

CREATE OR REPLACE VIEW exu9defpswitches (
compflgs, nlslensem ) AS
SELECT a.value, b.value
FROM sys.v$parameter a, sys.v$parameter b
WHERE a.name = 'plsql_compiler_flags' AND
b.name = 'nls_length_semantics'

to

CREATE OR REPLACE VIEW exu9defpswitches (
compflgs, nlslensem ) AS
SELECT a.value, b.value
FROM sys.v$parameter a, sys.v$parameter b
WHERE a.name = 'plsql_code_type' AND
b.name = 'nls_length_semantics'

(see new parameter PLSQL_CODE_TYPE)

This need to be change because of following issue.
Export From 11g using EXP Utility Version 9iR2 Produces Corrupt Export Dump [ID 550740.1]

1- Go to 9i database server

2- Add the tns entry fro 11g database

(Export from 11.1.0.7 using export utility of 9.2.0.6)

( To minimize the number of conversation set NLS_LANG during export to the same as character set of the exported database. In this case WE8ISO8859P1.This means no conversation takes place, all data is stored in export file as it was stored in the database)

3- export NLS_LANG=american_america.WE8ISO8859P1

4- Take the export of 11g database from this server

exp system/manager@11gDB owner=scott file=test.dmp log=a.log

(Run import in 9.2.0.6 database to import the export dump created from 11.1.0.7 with 9.2.0.6 export)

5- export ORACLE_SID of 9i database

6- export NLS_LANG=american_america.WE8ISO8859P1

7- Now import the export of 11g database data which is taken from 9i export utility

imp system/manage fromuser=scott touser=new file=test.dmp log=b.log

Business Continuity for EBS Using Oracle 11g Physical Standby DB

Our Applications Technology Group database architects have released two new documents covering the use of Oracle Data Guard to create physical standby databases for Oracle E-Business Suite environments:

• Business Continuity for Oracle E-Business Release 12 Using Oracle 11g Physical Standby Database (Note 1070033.1)
• Business Continuity for Oracle E-Business Release 11i Using Oracle 11g Physical Standby Database (Note 1068913.1)

What is a Standby Database?

A standby database is a transactionally-consistent copy of the primary database. Using a backup copy of the primary database, you can create up to thirty standby databases and incorporate them in Data Guard configuration.

There are three types of standby databases:

Physical Standby
Provides a physically identical copy of the primary database, with on-disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database by recovering the redo data received from the primary database.

Logical Standby
Contains the same logical information as the primary database, although the physical organization and structure of the data can be different. It is kept synchronized with the primary database by transforming the data in the redo logs received from the primary database into SQL statements and then executing the SQL statements on the standby database.

Snapshot Standby
A fully updatable standby database. Like a physical or logical standby database, a snapshot standby database receives and archives redo data from a primary database. Unlike a physical or logical standby database, a snapshot standby database does not apply the redo data that it receives.

These new notes detail the steps for setting up the first of these types, a Physical Standby database using Oracle Data Guard.

New EBS Roadmap documentations, too

Our Applications Technology Group database architects have also released two new roadmap documents covering the latest options for High Availability, Real Application Clusters, and Maximum Availability Architectures:

• Oracle E-Business Suite Release 11i High Availability Documentation Roadmap (Note 1072807.1)
• Oracle E-Business Suite Release 12 High Availability Documentation Roadmap (Note 1072636.1)

Oracle Apps Database Administrator (Apps DBA)

Oracle Apps DBA has almost all duties of core DBA, in addition to this following are more responsibilities
- Application fundamentals such as concepts and architecture
- Application Installation and configuration
- Patching and Upgrades
- Patch Management and Version Control
- Customization Updates
- Cloning requirements. This can be frequent in an Apps environment
- Concurrent Managers
- Forms/Report Servers
- Web Servers
- all the "ad" utilities
- all the database restrictions for the applications. That is so that the installation is still supportable and conforms to all vendor (Oracle) requirements.
- All special application related regular and maintenance functions. Such as month end and period end functions.
- Understand any backup and recovery constraints. Such as for table imports (NOT) and partial recoveries.
- special requirements and restrictions for tuning. Example optimizer and index requirements.
- Printer Configuration
- User Access Administration

You can get much more findings on this , but above are the some basic things that must be know to each Apps DBA

Oracle Application EBS.

Oracle Corporation's E-Business Suite also know as Oracle Apps or EBS consists of a collection of enterprise resource planning (ERP), customer relationship management (CRM), and supply-chain management (SCM) computer applications either developed by or acquired by Oracle.
This software uses oracle's core RDBMS technology. The EBS contains following products,

• Oracle CRM
• Oracle Financials
• Oracle HRMS
• Oracle Mobile Supply Chain Applications
• Oracle Order Management
• Oracle Procurement
• Oracle Project Portfolio Management
• Oracle Quotes
• Oracle Transportation Management
• Oracle Warehouse Management Systems
• Oracle Inventory
• Oracle Enterprise Asset Management

Each product comprises several modules, each separately licensed.