DB Sesions from JVM are very very high
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.
ARHLSTG1.SQL and recyclebin
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 EVOLUTIONSo 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
How to track if your DBUA or catupgrd.sql is progressing
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.
Slow running ontjup09.sql during 11.5.10.2 maintenance pack
$ 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.
ORA-12203 during 11.5.10.2 maintenance pack
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.
Database scripts for estimating growth
=======
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
Using Big IP hardware load balancers with 11i and R12
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
Does Autoconfig modify init.ora ?
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.
FND_STATS and DBMS_STATS
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
The 13th OATM tablespace APPS_TS_TOOLS
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). |
Verifying existance of the table: ADX_PRE_AUTOCONFIG
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
ORA-04065: not executed, altered or dropped stored procedure “PUBLIC.PLITBLM”
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.
SQL> select do.obj# d_obj,do.name d_name,
po.obj# p_obj,po.name p_name,
to_char(p_timestamp,'DD-MM-YYYY HH24:MI:SS') p_timestamp,
to_char(po.stime ,'DD-MM-YYYY HH24:MI:SS') stime,
decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X,
do.type#,po.type#
from sys.obj$ do, sys.dependency$ d, sys.obj$ po
where P_OBJ#=po.obj#(+)
and D_OBJ#=do.obj#
and do.status=1 /*dependent is valid*/
and po.status=1 /*parent is valid*/
and po.stime!=p_timestamp /*parent timestamp not match*/
order by 2,1;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.3An 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.
SQL> shutdown immediate
startup migrate
SQL> select do.obj# d_obj,do.name d_name,
po.obj# p_obj,po.name p_name,
to_char(p_timestamp,'DD-MM-YYYY HH24:MI:SS') p_timestamp,
to_char(po.stime ,'DD-MM-YYYY HH24:MI:SS') stime,
decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X,
do.type#,po.type#
from sys.obj$ do, sys.dependency$ d, sys.obj$ po
where P_OBJ#=po.obj#(+)
and D_OBJ#=do.obj#
and do.status=1 /*dependent is valid*/
and po.status=1 /*parent is valid*/
and po.stime!=p_timestamp /*parent timestamp not match*/
order by 2,1;
No rows is a good value.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
Invalid ORDSYS types after 10g upgrade
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.
Prevent ORA-4030 during 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 proc tools
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
Solaris plimit command
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
Oracle Beehive
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
- Voicemail
- Discussion forums
- Online presence
- Contact management
- Mobile device support
Issues in LogicalApps after 10g upgrade
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
TXK Rollup Patch S (Patch 6372396) removes IZU_TOP from context file

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
RSS
Email