OraNA :: Oracle News Aggregator

We are currently evaluating whether Microsoft Office can be replaced with StarOffice 8. For this one of the steps is to define how E-Business Suite will be affected. The first thing which comes to mind is export to excel functionality. E-Business Suite has this functionality in XML publisher, Discoverer and Web ADI. I have logged an SR to find out whether Oracle certifies this officially. Will update more when I find out.

For past few months, there was an issue of too much swapping on a shared box in our environment. After the high SGAs were size down, the problem was still at large. One of the experts monitored and saw that JVM sessions were very high on OS level. He inquired about any known issues after RUP6. Metalink Note 459353.1 which describes post ATG RUP 5/6 issues, shows a bug 6841295 which has a similar behavior. It says:

After RUP6 Patch 5903765 is applied there are a large number of idle JDBC connections owned by the APPLSYSPUB user. These connections build up and cause performance issues.

AppsLocalLogin.jsp is leaking connections when the initial login is not successful.

Note: Although the title of the bug and patch suggests that this issue appears after RUP6 + JDK 1.6 the problem will manifest after RUP6 and does not require JDK 1.6 to be triggered

There are lots of idle database connections coming from the APPLSYSPUB user.

Use this script to identify if you are experiencing this problem:
select s.sql_hash_value, t.sql_text, s.last_call_et
from v$session s , v$sqltext t
where s.username = 'APPLSYSPUB'
and s.sql_hash_value= t.hash_value
and t.sql_text like '%fnd_security_pkg.fnd_encrypted_pwd%';


These connection leaks are NOT reported in ""AOL/J Database connection pool status" page.

Last SQL executed shows :

BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5,:6,:7,:8); END;
or
BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;

The fix can be obtained via Patch 6841295 "DB SESSIONS FROM JVMS ARE VERY VERY HIGH AFTER ATG RUP6 + JDK 1.6 UPGRADE"

This patch is password protected - please contact Oracle Support

We executed the SQL:

SQL> select s.sql_hash_value, t.sql_text, s.last_call_et
from v$session s , v$sqltext t
where s.username = 'APPLSYSPUB'
and s.sql_hash_value= t.hash_value
and t.sql_text like '%fnd_security_pkg.fnd_encrypted_pwd%'; 2 3 4 5

SQL_HASH_VALUE SQL_TEXT
-------------- ----------------------------------------------------------------
LAST_CALL_ET
------------
2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
6712

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472085

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
363139


SQL_HASH_VALUE SQL_TEXT
-------------- ----------------------------------------------------------------
LAST_CALL_ET
------------
2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472085

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
1140226

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472095


SQL_HASH_VALUE SQL_TEXT
-------------- ----------------------------------------------------------------
LAST_CALL_ET
------------
2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
471800

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
1140221

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
1140219


SQL_HASH_VALUE SQL_TEXT
-------------- ----------------------------------------------------------------
LAST_CALL_ET
------------
2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
1133010

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472089

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472094


SQL_HASH_VALUE SQL_TEXT
-------------- ----------------------------------------------------------------
LAST_CALL_ET
------------
2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
1140216

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472090

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472090


SQL_HASH_VALUE SQL_TEXT
-------------- ----------------------------------------------------------------
LAST_CALL_ET
------------
2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472082


16 rows selected.

That matches roughly with:

$ top -b 5000 -s size | grep applmgr
25809 applmgr 73 59 0 1140M 356M sleep 39:01 0.00% java
15528 applmgr 65 59 0 361M 187M sleep 28:49 0.00% java
15548 applmgr 62 59 0 1051M 275M sleep 22:02 0.00% java
15770 applmgr 62 59 0 1051M 269M sleep 21:59 0.00% java
15538 applmgr 62 59 0 1051M 283M sleep 21:23 0.00% java
19372 applmgr 62 59 0 1136M 345M sleep 12:28 0.00% java
19435 applmgr 62 59 0 1140M 338M sleep 11:53 0.00% java
19409 applmgr 62 59 0 1112M 306M sleep 9:39 0.00% java
19395 applmgr 62 59 0 1111M 315M sleep 9:31 0.00% java
19399 applmgr 62 59 0 1111M 313M sleep 9:29 0.00% java
19414 applmgr 62 59 0 1111M 304M sleep 9:11 0.00% java
19421 applmgr 60 59 0 1144M 343M sleep 8:59 0.00% java
19452 applmgr 61 59 0 1114M 307M sleep 8:55 0.00% java
19384 applmgr 59 59 0 1114M 307M sleep 8:40 0.00% java

Strangely this instance was not on RUP6 but on RUP4. Maybe this issue occurs on RUP4 too. Since this patch is password protected, I have logged an SR with Oracle to get the password. We'll be applying this in one of the affected instances and check if it solves the issue. Will update this post once it is done.

During the application of a merged functional patch which had FIN_PF.G, PJ_PF.M and 300 other patches, worker running arhlstg1.sql failed:

FAILED: file arhlstg1.sql on worker 4.

Examining the worker log showed this error:

Start time for file is: Thu Jul 03 2008 16:33:09

sqlplus -s APPS/***** @$AR_TOP/patch/115/sql/arhlstg1.sql &un_ar
DECLARE
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
ORA-06512: at line 306

A search on metalink revealed bug 6762542, which advises dropping the tables created by arhlstg1.sql

Instead of dropping the tables, we first dropped the synonyms in APPS with the same name as the tables:

DROP SYNONYM APPS.HZ_IMP_PARTIES_SG;
DROP SYNONYM APPS.HZ_IMP_ADDRESSES_SG;
DROP SYNONYM APPS.HZ_IMP_CONTACTPTS_SG;
DROP SYNONYM APPS.HZ_IMP_CREDITRTNGS_SG;
DROP SYNONYM APPS.HZ_IMP_CLASSIFICS_SG;
DROP SYNONYM APPS.HZ_IMP_FINREPORTS_SG;
DROP SYNONYM APPS.HZ_IMP_FINNUMBERS_SG;
DROP SYNONYM APPS.HZ_IMP_RELSHIPS_SG;
DROP SYNONYM APPS.HZ_IMP_CONTACTS_SG;
DROP SYNONYM APPS.HZ_IMP_CONTACTROLES_SG;
DROP SYNONYM APPS.HZ_IMP_ADDRESSUSES_SG;

Restarted the worker but the error persisted. Then we checked if these tables had any data:

SELECT COUNT(*) FROM AR.HZ_IMP_PARTIES_SG
SELECT COUNT(*) FROM AR.HZ_IMP_ADDRESSES_SG
SELECT COUNT(*) FROM AR.HZ_IMP_CONTACTPTS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_CREDITRTNGS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_CLASSIFICS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_FINREPORTS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_FINNUMBERS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_RELSHIPS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_CONTACTS_SG
SELECT COUNT(*) FROM AR.HZ_IMP_CONTACTROLES_SG
SELECT COUNT(*) FROM AR.HZ_IMP_ADDRESSUSES_SG

All of them returned zero rows except HZ_IMP_CONTACTPTS_SG

select count(*) from AR.HZ_IMP_CONTACTPTS_SG returned

ORA-00600: internal error code, arguments: [20445], [6892510], [6901726], [], [], [], [], []

Trying to drop table AR.HZ_IMP_CONTACTPTS_SG returned

ORA-00600: internal error code, arguments: [15264], [], [], [], [], [], [], []

A seach through ORA-600 lookup tool in metalink showed up metalink note 456974.1 which advises:

Symptoms

You encounter this error while running a query against a partitioned table.:

ORA-00600: internal error code, arguments: [20445], [222112], [417107], [], [], [], [], []

If you search your trace file, you may find some of these codes.:

kkdl1ck kkdlack kkmfcbbt kkmfcblo kkmpfcb qcsprfro

Changes

This may be triggered by moving datafiles or making other changes to your database.

Cause

This is caused by unpublished Bug 3738431.

Solution

Unfortunately, there is currently no fix available for this bug.

However, the following workaround should resolve the issue.:

1. purge recyclebin;

2. Bounce the database and retry the statement again.

If above does not help, then try

3. Set init.ora parameter "_recyclebin" = false, bounce the database and drop
the table.

References

@ Bug 3738431 - ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [20445], [43290] IN OBJECT EVOLUTION

So we gave the command
SQL> purge recyclebin;
SQL> shutdown immediate;
SQL> startup

Once the database came up, we could do a select coun(*) on the table without triggering ORA-600. So we just resumed the patch, which was able to successfully drop the tables without failing.

Metalink Note 265253.1 describes 10g recyclebin features and methods to disable it:

The Recycle Bin is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$. You can continue to access the data in a
dropped table or even use Flashback Query against it. Each user has the same rights and privileges on Recycle Bin objects before it was dropped. You can view your dropped tables by querying the new RECYCLEBIN view. Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides to permanently remove them using the new PURGE command. The Recycle Bin objects are counted against a user's quota. But Flashback Drop is a non-intrusive feature. Objects in the Recycle Bin will be automatically purged by the space reclamation process if

o A user creates a new table or adds data that causes their quota to be exceeded.
o The tablespace needs to extend its file size to accommodate create/insert operations.


There is no issues with DROPping the table, behaviour wise. It is the same as in 8i / 9i. The space is not released immediately and is accounted for within the same tablespace / schema after the drop.

When we drop a tablespace or a user there is NO recycling of the objects.

o Recyclebin does not work for SYS objects

We have standardized on DBUA for all DB upgrades of E-Business Suite instances. While DBUA was on the Upgrading Oracle Server stage, Anand asked me a question. How to know whether DBUA is proceeding well. The progress bar and the DBUA logs tell you only so much. So I checked the contents of $ORACLE_HOME/rdbms/admin/catupgrd.sql which is the upgrade script called by DBUA internally. Here's what it says:

NAME
catupgrd.sql - CATalog UPGraDe to the new release

DESCRIPTION
This script is to be used for upgrading an 8.1.7, 9.0.1, 9.2
or 10.1 database to the new release. This script provides a direct
upgrade path from these releases to the new Oracle release.

The upgrade is partitioned into the following 5 stages:
STAGE 1: call the "i" script for the oldest supported release:
This loads all tables that are necessary
to perform basic DDL commands for the new release
STAGE 2: call utlip.sql to invalidate PL/SQL objects
STAGE 3: Determine the original release and call the
c0x0x0x0.sql for the release. This performs all
necessary dictionary upgrade actions to bring the
database from the original release to new release.
STAGE 4: call the a0x0x0x0.sql for the original release:
This performs all necessary upgrade using
anonymous blocks.
STAGE 5: call cmpdbmig.sql
This calls the upgrade scripts for all of the
components that have been loaded into the database

NOTES

* This script needs to be run in the new release's environment
(after installing the release to which you want to upgrade).
* You must be connected AS SYSDBA to run this script.

This gave me an idea that catupgrd.sql is first invalidating all objects through utlip.sql. Once the catalog is created some objects are recreated. So we can check the count of invalid objects to see if DBUA is proceeding well.

select count(*) from dba_objects
where status='INVALID';

During the Upgrading Oracle Server stage, if you run the above query periodically, you'll notice that the number goes on increasing. This is a good indicator that DBUA is proceeding well. Later on the number will decrease when catalog is created. The number will again increase when the components like Intermedia and Spatial are getting upgraded. Finally during post upgrade step when utlrp.sql is called by DBUA, the number of invalids will start reducing.

ontjup09.sql is one of the slow running sqls during 11.5.10.2 upgrade. For this Oracle has given patch 4480056 in Metalink Note 331790.1. However this doesn't solve the issue completely. This is described in Bug 6673695. We faced the same issue during upgrade in an environment in which we merged 4480056 with 3480000 (maintenance pack patch). Even though the ontjup09.sql being called was the latest, the ontjup09.sql took more than 2 hours.

$ adident Header $ONT_TOP/patch/115/sql/ontjup09.sql
$ONT_TOP/patch/115/sql/ontjup09.sql:
$Header ontjup09.sql 115.0.11510.2 2005/07/28 19:45:08 jvicenti noship $

$ adident Header $ONT_TOP/patch/115/sql/ontjup07.sql
$ONT_TOP/patch/115/sql/ontjup07.sql:
$Header ontjup07.sql 115.4.11510.2 2005/07/21 00:54:30 jvicenti ship $

This could be due to the fact that oe_order_history table was 12 GB in size.

Makes me wonder why updates to history table can't be done in advance before the actual downtime. I am logging an SR with Oracle to find out if this can be done for future upgrades.

Some of the workers were waiting indefinitely during an 11.5.10.2 upgrade as the DB connection could not be made. The worker error log showed:

Start time for file is: Wed Jul 02 2008 06:49:18

sqlplus -s APPS/***** @$PA_TOP/patch/115/sql/paupg007.sql &un_pa &batchsize 4 48
ERROR:
ORA-12203: TNS:unable to connect to destination


Some of the workers failed with the same error

HIDEPW: $FND_TOP/bin/FNDGFU APPS/TIB5C1US 0 Y PROGRAM_NAME=FND_HELP CONTENT_MAP=@FND:admin/import/fndgfu.txt PROGRAM_TAG
=OKS:10 LANGUAGE=US @OKS:help/US/oks11511084417.htm
$FND_TOP/bin/FNDGFU &ui_apps 0 Y PROGRAM_NAME=FND_HELP CONTENT_MAP=@FND:admin/import/fndgfu.txt PROGRAM_TAG=OKS:10 LANGU
AGE=US @OKS:help/US/oks11511084417.htm
APP-FND-01564: ORACLE error 12203 in AFPCOA

Cause: AFPCOA failed due to ORA-12203: TNS:unable to connect to destination.

The SQL statement being executed at the time of the error was: and was executed
from the file .

AD Worker error:
The above program failed with error code 1.
See the AD Worker log file and/or the program log file for details.


We stopped all workers. Bounced DB listener.

update fnd_install_processes
set status='W', control_code='W';

commit;

Restarted adpatch. It went fine after that.

We are still trying to find the root cause of this issue.

One of the simplest scripts is based on addition of datafiles and is described in metalink note 135294.1

=======
Script:
=======

select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024
"Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR
Month');


==============================
Sample Output from the script:
==============================

Month
Growth in
-------------------------------- ----------
2000 December 1068.625
2001 February 70

All our Production instances are load balanced on web tier. We have multiple app tiers for each instance on which Apache and forms is running. The user point of entry is the load balancer URL which further redirects the traffic to the individual app servers. Oracle uses BigIP load balancer on their internal Global Single Instance also. Here are some very good metalink notes which give you a good overview:


380489.1 Using Load-Balancers with Oracle E-Business Suite Release 12 (Relevant for 11i too)
217368.1 Advanced Configurations and Topologies for Enterprise Deployments of E-Biz Suite 11i
601694.1 How To Check Session Persistence On a BigIP F5 Load Balancer
456906.1 11i/R12 How to Debug "Transaction Context Is Lost"
387306.1 Random error Your login session has expired when using Load Balancing

When a hardware load balancer is used, the context file variables which need to be changed are:

s_webentryurlprotocol http or https
s_webentryhost load balancer hostname
s_webentrydomain load balancer domain name
s_active_webport load balancer port
s_login_page load balancer url
s_external_url load balancer url

Load balancer settings:

persistence timeout = 1 day
persistence type = cookie based persistence

Autoconfig or adconfig does not modify the database initialization parameters. Metalink Note 377398.1 says:

QUESTION 2: Is Autoconfig modifying init.ora?
ANSWER 2: No. Autoconfig is not modifying the init.ora file. Autoconfig will generate a new init.ora just in the case it is not finding the init.ora in the expected location. So all the changes in init.ora shoud be made by the DBA.

Automatic collection of statistics is a new feature introduced from 10g. However, this should be disabled in case of Apps instances as FND_STATS is used to collect statistics instead of DBMS_STATS. This is stated in Metalink Note 368252.1:

6) 10g has a new feature that gather statistics automatically, using the GATHER_STATS_JOB. Can the automatic statistics gathering job be used in 10G databases for Apps 11i?

No. The GATHER_STATS_JOB job collects stats using DBMS_STATS and should be disabled for Apps 11i.

If it is currently running, the automatic statistics gathering job should be disabled by running the following command:
dbms_scheduler.disable(''GATHER_STATS_JOB'');

Note: Normally, it is not necessary to disable this job, because this should be done by the script adstats.sql, which runs during the DB upgrade process.

I went through the code inside FND_STATS and DBMS_STATS. DBMS_STATS code is encrypted and can't be read. FND_STATS code is readable. In many metalink notes, FND_STATS is described as a wrapper over DBMS_STATS package.

I gave this query to cross check:

1 select line,text
2 from dba_source
3 where text like '%DBMS_STATS%'
4* and name='FND_STATS'
SQL> /
249
DBMS_STATS.CREATE_STAT_TABLE(fnd_statown,fnd_stattab);

267
DBMS_STATS.CREATE_STAT_TABLE(schemaname,tabname,tblspcname);

359
DBMS_STATS.EXPORT_SCHEMA_STATS(schemaname, fnd_stattab, statid,

363
DBMS_STATS.EXPORT_SCHEMA_STATS(c_schema.sname, fnd_stattab, statid,

429
DBMS_STATS.EXPORT_TABLE_STATS(schemaname,

449
DBMS_STATS.IMPORT_SCHEMA_STATS(schemaname, fnd_stattab, statid,

453
DBMS_STATS.IMPORT_SCHEMA_STATS(c_schema.sname, fnd_stattab, statid,

517
DBMS_STATS.IMPORT_TABLE_STATS(ownname,tabname,partname,

532
DBMS_STATS.IMPORT_INDEX_STATS(ownname,indname,partname,fnd_stattab,

548
DBMS_STATS.IMPORT_COLUMN_STATS(ownname, tabname, colname, partname,

587
DBMS_STATS.IMPORT_COLUMN_STATS(c_rec.ownname,c_rec.tabname,

640
DBMS_STATS.GATHER_TABLE_STATS( ownname => ownname ,

655
l_tmp_str:= 'BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => :ownname ,'||

712
DBMS_STATS.GATHER_INDEX_STATS( ownname => ownname ,

718
l_tmp_str:= 'BEGIN DBMS_STATS.GATHER_INDEX_STATS( ownname => :ownname ,'||

1030
EXECUTE IMMEDIATE 'BEGIN DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; END;' ;

1226
dlog('Please use DBMS_STATS package to gather stats on SYS objects.');

1294
DBMS_STATS.EXPORT_INDEX_STATS( ownname, indname, null,

1465
DBMS_STATS.EXPORT_TABLE_STATS(ownname, tabname, partname,

1544
-- Due to the limitations of in DBMS_STATS in 8i we need to call

1707
DBMS_STATS.EXPORT_COLUMN_STATS(list_ownname(i),

2048
DBMS_STATS.EXPORT_COLUMN_STATS ( ownname,

2099
DBMS_STATS.SET_TABLE_STATS(ownname,

2143
DBMS_STATS.SET_INDEX_STATS(ownname,


24 rows selected.


As per eTRM:

APPS.FND_STATS references the following:

APPLSYS

FND_HISTOGRAM_COLS

FND_ORACLE_USERID

FND_PRODUCT_INSTALLATIONS

FND_STATS_HIST

FND_STATS_HIST_S

APPS

FND_FILE - show dependent code

FND_GLOBAL - show dependent code

FND_HISTOGRAM_COLS

FND_INSTALLATION - show dependent code

FND_ORACLE_USERID

FND_PRODUCT_INSTALLATIONS

FND_STATS - show dependent code

FND_STATS_HIST

FND_STATS_HIST_S

PUBLIC

DBA_HISTOGRAMS

DBA_INDEXES

DBA_IND_COLUMNS

DBA_TABLES

DBA_TAB_COLUMNS

DBA_TAB_PARTITIONS

DBMS_OUTPUT

DBMS_SPACE

DBMS_STATS

DUAL

PLITBLM

V$INSTANCE

V$PARAMETER

SYS

DBA_INDEXES

DBA_IND_COLUMNS

DBA_TABLES

DBA_TAB_COLUMNS

DBA_TAB_HISTOGRAMS

DBA_TAB_MODIFICATIONS

DBA_TAB_PARTITIONS

DBMS_OUTPUT - show dependent code

DBMS_SPACE - show dependent code

DBMS_STANDARD - show dependent code

DBMS_STATS - show dependent code

DUAL

PLITBLM - show dependent code

STANDARD - show dependent code

V_$INSTANCE

V_$PARAMETER

I'll update more when I find out.

APPS_TS_TOOLS is a new tablespace added in OATM for 11i. If you search for APPS_TS_TOOLS in Metalink, you'll find references to it in all ATG_PF.H RUPs after RUP4:

Customers who have migrated to the new Oracle Applications Tablespace Model and have applied Oracle Applications Technology Family Pack 11i.ATG_PF.H Rollup 4 may encounter the following error when running the AD Splicer to introduce a new product schema:

alter user xxx quota unlimited on APPS_TS_TOOLS

AD Splicer error:
The following ORACLE error:

ORA-00959: tablespace 'APPS_TS_TOOLS' does not exist

To correct this error, create the APPS_TS_TOOLS tablespace, either manually through SQL*Plus or by invoking the Tablespace Migration Utility menu. For more information, see: Understanding the Tablespace Migration Utility Main Menu, Oracle Applications System Administrator's Guide - Configuration.

The default initial size for the APPS_TS_TOOLS tablespace is 500 MB, with auto segment management enabled. If you create the APPS_TS_TOOLS tablespace through SQL*Plus, outside of the OATM utility, you can use the following statement:

CREATE TABLESPACE APPS_TS_TOOLS DATAFILE
'datafile_name.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

You can override these storage recommendations with storage parameters to suit the expected size required for objects that will be in the Tools tablespace at your site.

APPS_TS_TOOLS is used to store objects from several tools components like SSO , OID , LDAP, Portal , Discoverer, etc.

This tablespace must exist before using the Release 12 patching tools like adpatch.

Originally there were 12 tablespaces in OATM as per OATM FAQ:

How many tablespaces are introduced in OATM? ?

OATM contains twelve consolidated tablespaces for all products, including temporary tablespace, system tablespace, and undo segments:

Tablespace Type	Tablespace Name	Contents
Transaction Tables	APPS_TS_TX_DATA	Tables that contain transaction data.
Transaction Indexes	APPS_TS_TX_IDX	Indexes on transaction tables.
Reference	APPS_TS_SEED	Reference and set-up 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.

How are objects classified in OATM?

OATM relies on specific explicit and implicit classification rules. These rules are deternined based on storage considerations for the object type in question. Quite obviously, Tablespace classifications are not relevant for code objects and other objects without storage implications such as View, Policies, Context, triggers, etc. Objects like tables are explicitly classified based on their I/0 characteristics.

What object typesare implicitly classified in OATM?

OATM classifes the following object types:

Object type	Tablespace Type
Index Organized Tables	Transaction_tables
Global Temporary Tables	The database automatically always creates these objects in the TEMP tablespace.
Advanced Queuing tables	Advanced Queuing/AQ
Materialized View	Summary
Materialized View Logs	Transaction_tables
Domain Indexes	Transaction_indexes
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/mv).

Recently I came across this error at the end of an adconfig session in $APPL_TOP/admin/$CONTEXT_NAME/log/timestamp/adconfig.log:

ERROR
Verifying existance of the table: ADX_PRE_AUTOCONFIG
Table does not exist. Exiting RestoreProfile...

-------------------ADX Database Utility Finished---------------

Verifying connection to the Database : Established
Restore Profile Feature : Disabled

No Restore Profile file created.

This is due to unpublished Bug 4604710. They are trying to test a new feature for restoring profiles which requires ADX_PRE_AUTOCONFIG table. However the warning message looks like an error. This was fixed in ADX.F (Patch 3453499) which has been superseded by TXK Rollup S (patch 6372396). So if you apply the latest TXK Rollup patch the message changes to :


Restore Profile utility ran successfully
===========================================================================

AutoConfig is exiting with status 0

There was one more offshoot of the problem described in my previous post. After applying patches, when autoconfig was running it gave these errors:


Uploading Metadata file $AD_TOP/admin/template/adctxinf.tmp
Metadata file $AD_TOP/admin/template/adctxinf.tmp upload failed.
Check log file created by FNDCPUCF program.
ERRORCODE = 1 ERRORCODE_END
.end std out.
Oracle error -6508: ORA-04065: not executed, altered or dropped stored procedure
"PUBLIC.PLITBLM"
ORA-06508: PL/SQL: could not find program unit being called: "PUBLIC.PLITBLM"
has been detected in FND_GLOBAL.INITIALIZE.
Log filename : Lxxxxxx.log

OR jtfictx.sh started at ....

SQL*Plus: Release 8.0.6.0.0 - Production on ...

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Connected.
DECLARE
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "PUBLIC.PLITBLM"
ORA-06508: PL/SQL: could not find program unit being called: "PUBLIC.PLITBLM"
ORA-06512: at "CTXSYS.DRVUTL", line 51
ORA-06512: at "CTXSYS.CTX_DDL", line 53
ORA-06512: at line 60


Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
jtfictx.sh exiting with status 1
ERRORCODE = 1 ERRORCODE_END
.end std out.

.end err out.

This error is well described in metalink notes
370137.1
392470.1
469792.1

However it is Metalink Note 443706.1 which tells us that this is a bug and we need to apply a DB patch to fix it.

Cause

The issue is related to unpublished Bug 4882839.

The root-cause is a mismatch in the timestamp of depended objects.
In the error situation on PLITBLM, the following query will reported rows of objects which
have a mismatch.

Solution

Unpublished Bug 4882839 is fixed in Oracle11g and 10.2.0.4 and a one-off patches are available for 10.2.0.2 and 10.2.0.3

An upgrade is recommended, but the following procedure helped as well :

1. Download the patch for unpublished Bug 4882839
and install it according the README

2. Shutdown and restart the instance and run utlip Running 'utlip' is required for 'activating' the fix.

After doing the above you need to

sqlplus /nolog
connect / as sysdba
@?/rdbms/admin/utlip.sql (This will invalidate all objects)
alter session set job_queue_processes=24 (or the no. of processors on the server)
@?/rdmbs/admin/utlrp.sql


The cause of this error is

An offshoot of the issue in DBA which I reported in my previous post was invalid objects in ORDSYS schema. Two objects ORDAUDIO and ORDVIDEO were reported as invalid. Even after running catupgrd.sql manually, these did not get resolved. A search in metalink revealed bug 6830609:

DESCRIPTION
===========

When an initial attempt to upgrade intermedia from 9.2 to 10.2 fails, subsequent upgrade attempts will fail due to the ORDIMAGE, ORDAUDIO and ORDVIDEO types being invalid. Attempts to compile these types individually also fail.

DIAGNOSTIC ANALYSIS
===================

Could not duplicate the initial failure. After Catupgrd.sql completes interMedia is listed as invalid.

After running imchk.sql there are the following invalid interMedia objects.

ORDAUDIO Type Body INVALID

ORDIMAGE Type Body INVALID

ORDVIDEO Type Body INVALID


WORKAROUND
==========

Since intermedia was not used in this case, a drop and rebuild of the objects was sufficient.

Connected as a sysdba user:

drop type ordsys.ordaudio force;

drop type ordsys.ordvideo force;

drop type ordsys.ordimage force;

connect sys as sysdba

alter session set current_schema=ORDSYS;

@$ORACLE_HOME/ord/im/admin/iminst.sql

The above steps resolved those invalids.

We got ORA-4030 error during DBUA again. I thought we had solved it in previous runs by setting these parameters in 9i init.ora which is used to create 10g init.ora by DBUA:

sga_max_size = 2G
shared_pool_size = 1G
java_pool_size = 500M

DBUA uses the parameters in the old 9i home to automatically create spfile for Oracle 10g. Unless the above parameters are set to the given values, ORA-04031: unable to allocate n bytes of shared memory error may occur.

ORA-4030 is related to the PGA and not SGA. But our pga_aggregate_target was also set to 1G. So why did the issue occur in the first place ?

The DBAs ran a manual upgrade and faced the very same error again. Suspecting resource issues on the OS, two instances not in use on the shared box were brought down. After this the upgrade went smooth without any errors. It looks like swap was the culprit here. I think it would be wise if we check if the swap available is 2.5 * (sga_max_size+pga_aggregate_target) before begining DBUA on a shared box.

Solaris 10 has some great proc tools which give process information. Good ones among them are pfiles, pldd and pwdx

pfiles reports all the files which are opened by a given pid
pldd lists all the dynamic libraries linked to the process
pwdx gives the directory from which the process is running

I did a pfiles on Apache process:

$ pfiles 13789
13789: /apps11i/erpdev/10GAS/Apache/Apache/bin/httpd -d /apps11i/erpdev/10G
Current rlimit: 1024 file descriptors
0: S_IFIFO mode:0000 dev:350,0 ino:114723 uid:65060 gid:54032 size:301
O_RDWR
1: S_IFREG mode:0640 dev:307,28001 ino:612208 uid:65060 gid:54032 size:386
O_WRONLY|O_APPEND|O_CREAT
/apps11i/erpdev/10GAS/opmn/logs/HTTP_Server~1
2: S_IFIFO mode:0000 dev:350,0 ino:143956 uid:65060 gid:54032 size:0
O_RDWR
3: S_IFREG mode:0600 dev:307,28001 ino:606387 uid:65060 gid:54032 size:1056768
O_RDWR|O_CREAT
/apps11i/erpdev/10GAS/Apache/Apache/logs/mm.19389.mem
4: S_IFREG mode:0600 dev:307,28001 ino:606383 uid:65060 gid:54032 size:0
O_RDWR|O_CREAT
5: S_IFREG mode:0600 dev:307,28001 ino:621827 uid:65060 gid:54032 size:1056768
O_RDWR|O_CREAT
6: S_IFDOOR mode:0444 dev:351,0 ino:58 uid:0 gid:0 size:0
O_RDONLY|O_LARGEFILE FD_CLOEXEC door to nscd[421]
/var/run/name_service_door
7: S_IFIFO mode:0000 dev:350,0 ino:143956 uid:65060 gid:54032 size:0
O_RDWR
8: S_IFCHR mode:0666 dev:342,0 ino:47185924 uid:0 gid:3 rdev:90,0
O_RDONLY
/devices/pseudo/kstat@0:kstat
9: S_IFREG mode:0600 dev:307,28001 ino:621828 uid:65060 gid:54032 size:0
O_RDWR|O_CREAT
/apps11i/erpdev/10GAS/Apache/Apache/logs/mod_oc4j.19389.shm.sem
10: S_IFREG mode:0755 dev:307,28001 ino:621829 uid:65060 gid:54032 size:0
O_RDWR|O_CREAT
11: S_IFREG mode:0600 dev:307,28001 ino:603445 uid:65060 gid:54032 size:17408
O_RDONLY FD_CLOEXEC
/apps11i/erpdev/10GAS/rdbms/mesg/ocius.msb
12: S_IFIFO mode:0000 dev:350,0 ino:143958 uid:65060 gid:54032 size:0
O_RDWR FD_CLOEXEC
13: S_IFIFO mode:0000 dev:350,0 ino:143957 uid:65060 gid:54032 size:0
O_RDWR
14: S_IFIFO mode:0000 dev:350,0 ino:143958 uid:65060 gid:54032 size:0
O_RDWR FD_CLOEXEC
15: S_IFSOCK mode:0666 dev:348,0 ino:5866 uid:0 gid:0 size:0
O_RDWR|O_NONBLOCK FD_CLOEXEC
SOCK_STREAM
SO_REUSEADDR,SO_KEEPALIVE,SO_SNDBUF(49152),SO_RCVBUF(49152),IP_NEXTHOP(0.0.192.0)
sockname: AF_INET 127.0.0.1 port: 7201
16: S_IFSOCK mode:0666 dev:348,0 ino:63613 uid:0 gid:0 size:0
O_RDWR|O_NONBLOCK FD_CLOEXEC
SOCK_STREAM
SO_REUSEADDR,SO_KEEPALIVE,SO_SNDBUF(49152),SO_RCVBUF(49152),IP_NEXTHOP(0.0.192.0)
sockname: AF_INET 0.0.0.0 port: 7779
17: S_IFCHR mode:0666 dev:342,0 ino:6815752 uid:0 gid:3 rdev:13,2
O_WRONLY|O_APPEND|O_CREAT FD_CLOEXEC
/devices/pseudo/mm@0:null
18: S_IFREG mode:0644 dev:307,28001 ino:621830 uid:65060 gid:54032 size:0
O_WRONLY|O_CREAT|O_EXCL FD_CLOEXEC
19: S_IFREG mode:0600 dev:307,28001 ino:606420 uid:65060 gid:54032 size:2285568
O_RDWR|O_CREAT
/apps11i/erpdev/10GAS/Apache/Apache/logs/dms_metrics.19389.shm.mem
20: S_IFREG mode:0600 dev:307,28001 ino:606421 uid:65060 gid:54032 size:0
O_RDWR|O_CREAT
/apps11i/erpdev/10GAS/Apache/Apache/logs/dms_metrics.19389.shm.sem
21: S_IFREG mode:0600 dev:307,28001 ino:603445 uid:65060 gid:54032 size:17408
O_RDONLY FD_CLOEXEC
/apps11i/erpdev/10GAS/rdbms/mesg/ocius.msb
23: S_IFSOCK mode:0666 dev:348,0 ino:60339 uid:0 gid:0 size:0
O_RDWR
SOCK_STREAM
SO_SNDBUF(49152),SO_RCVBUF(49152),IP_NEXTHOP(0.0.192.0)
sockname: AF_INET 3.56.189.4 port: 45395
peername: AF_INET 3.56.189.4 port: 12501
256: S_IFREG mode:0444 dev:85,0 ino:234504 uid:0 gid:3 size:1616
O_RDONLY|O_LARGEFILE
/etc/inet/hosts

A pldd on the same process gave this result:

applmpd1@tsgsd1007 # pldd 13789
13789: /apps11i/erpdev/10GAS/Apache/Apache/bin/httpd -d /apps11i/erpdev/10G
/apps11i/erpdev/10GAS/lib32/libdms2.so
/lib/libpthread.so.1
/lib/libsocket.so.1
/lib/libnsl.so.1
/lib/libdl.so.1
/lib/libc.so.1
/platform/sun4u-us3/lib/libc_psr.so.1
/lib/libmd5.so.1
/platform/sun4u/lib/libmd5_psr.so.1
/lib/libscf.so.1
/lib/libdoor.so.1
/lib/libuutil.so.1
/lib/libgen.so.1
/lib/libmp.so.2
/lib/libm.so.2
/lib/libresolv.so.2
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_onsint.so
/lib/librt.so.1
/apps11i/erpdev/10GAS/lib32/libons.so
/lib/libkstat.so.1
/lib/libaio.so.1
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_mmap_static.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_vhost_alias.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_env.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_define.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_log_config.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_log_agent.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_log_referer.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_mime_magic.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_mime.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_negotiation.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_status.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_info.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_include.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_autoindex.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_dir.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_cgi.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_asis.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_imap.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_actions.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_speling.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_alias.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_access.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_auth.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_auth_anon.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_auth_dbm.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_digest.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/libproxy.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_cern_meta.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_expires.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_headers.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_usertrack.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_unique_id.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_setenvif.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/libperl.so
/lib/libm.so.1
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_fastcgi.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/libphp4.so
/usr/lib/libsched.so.1
/apps11i/erpdev/10GAS/lib32/libclntsh.so.10.1
/apps11i/erpdev/10GAS/lib32/libnnz10.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_wchandshake.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_oc4j.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_dms.so
/apps11i/erpdev/10GAS/Apache/Apache/libexec/mod_rewrite.so
/apps11i/erpdev/10GAS/Apache/oradav/lib/mod_oradav.so
/apps11i/erpdev/10GAS/Apache/modplsql/bin/modplsql.so

The plimit command tells us what are the resource limits of running processes

For example if I check process id 6161 it shows:

$ plimit -km 6161
6161: oracle (LOCAL=NO)
resource current maximum
time(seconds) unlimited unlimited
file(mbytes) unlimited unlimited
data(mbytes) unlimited unlimited
stack(mbytes) 32 unlimited
coredump(mbytes) unlimited unlimited
nofiles(descriptors) 65536 65536
vmemory(mbytes) unlimited unlimited

I came across Oracle Beehive while searching for something completely different. The term raised my curiosity and I googled for it. This is how Oracle defines it:

Oracle Beehive provides an integrated set of collaboration services built on a single, scalable, open, and enterprise-class collaboration platform. Beehive allows users to access their collaborative information through familiar clients while enabling IT to consolidate collaborative infrastructure and implement people-centric applications with a centrally managed, secure, and compliant environment built on Oracle technology.

The above description is so broad you don't get a feel of what it is. So I went ahead and read their white paper. Here's what I could extract from it:

Beehive provides:
• An integrated collaborative application with Microsoft Windows and Microsoft Office integration
• An integrated user experience accessed through familiar desktop tools
• A unified collaborative platform
• An enterprise-ready infrastructure

That still doesn't give me a feel to what is Beehive. So I read on further. This link tells me that Beehive has:

1. Events and Policies : 400 business events, such as delivering an email to an inbox
2. Workflow: integrated with Oracle BPEL Process Manager
3. Web Services:
4. Standards Support: IMAP and SMTP to access email, WebDAV and JCR to access documents

They have touted Beehive allowing "True Collaboration". This is what they say in the white paper:

True Collaboration is not about individual tools, a wiki, a forum, a central store for data, a portal or even building a complicated social network. True Collaboration goes beyond even building enterprise or group knowledge. True Collaboration is about collaborative knowledge – the knowledge that is a result of teams working together in context.

I still don't have the information an Architect would like to have to understand what exactly is Beehive, but I am reading on. I finally turned to the installation guide. This is where they have the kind of information I am seeking. I found the Beehive high level Arch diagram:

I can now make out what they have built. Oracle has cleverly used its pet technologies of Database and Application Servers to connect various other technologies in the collaboration space. This link will give you more details about the above architecture.

In terms of features, Oracle Beehive provides the full range of collaborative services for enterprise users, including:

• Time management
• Content and document management
• Task management
• Instant messaging
• E-mail
• Voicemail
• Discussion forums
• Online presence
• Contact management
• Mobile device support

LogicalApps is a bolt-on to Oracle Apps in one of our instances. After upgrading this instance from Database version 9.2.0.8 to 10.2.0.3, none of the forms would open. An error about invalid package body LA_ENHNCMTMGR_PKG was being displayed. Recently Oracle acquired LogicalApps, so it was easy to log an SR in metalink for this issue. Based on Metalink searches we found that the product name is now Oracle Preventive Controls Governor.

This is how it went:

SQL> alter package LA_ENHNCMTMGR_PKG compile body;

Warning: Package Body altered with compilation errors.

SQL> show error
Errors for PACKAGE BODY LA_ENHNCMTMGR_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4708/1 PL/SQL: Item ignored
4708/1 PLS-00999: implementation restriction (may be temporary) Java
methods not permitted here

4735/1 PL/SQL: Item ignored
4735/1 PLS-00999: implementation restriction (may be temporary) Java
methods not permitted here

Following query was given by Oracle to identify the version of LogicalApps:

select * from la_install_history;

Release 6.5.3 is not compatible with 10g database. But in past we did provide one off fix for 6.5.3 to resolve the issue you are getting. So now to fix the issue we have following two options:-
1. upgrade to release 6581 which is 10g compatible
2. Apply the one off fix which will resolve this issue

1. Down load the file 10g_la_appsform_pkg_body.plb from below location
ftp://ftp.oracle.com/support/outgoing/6931884.994 (The file is removed by Oracle after 7 days, so mail me or leave a message in the google group if you need this file)
2.Compile the downloaded file through SQL prompt
3. Once done, check the status of LA_ENHNCMTMGR_PKG
4. If valid, test the system by logging in ERP applcation
5. If Invalid, update the SR accordingly

I just got a call from the DBA team informing me that TXK Rollup Patch S (Patch 6372396) removed IZU_TOP from the context file. After examining the instance, I found that it is true. IZU_TOP is the Oracle Diagnostics top. As a workaround we have reinserted the s_izutop line in the new context file. For a permanent solution, I logged an SR with Oracle for this issue. Oracle released patch 7126196 which is available on metalink now.



Readme of patch TXK Rollup Patch S (Patch 6372396) is updated too.

From readme of patch 7126196
---------------------------------------------
Those customers who have already applied TXK AUTOCONFIG AND TEMPLATES ROLLUP
PATCH S (6372396) and face the above issue should manually add the product top
entry into topfile.txt and then apply this patch.

Update on SR:

UPDATE
=======
Hi Vikram,

Please perform the following:

ACTION PLAN
============
1. add entry to $APPL_TOP/admin/topfile.txt
izu

2. Apply patch 7126196