Vikram Das
Simple query to list locked objects
v$locked_object is the view which has a list of locked objects. For a long time I used this query to get a list of objects:
select object_name from
dba_objects
where object_id in
(
select object_id
from v$locked_object)
The above query never completed, and I always used to do a ctrl+c.
I rewrote the query by using the EXISTS clause instead of IN:
select owner,object_type,object_name
from dba_objects a
where exists
(
select object_id
from v$locked_object b
where a.object_id=b.object_id
)
/
The above query executes in seconds. I got hold of an old article from TechRepublic:
When coding a SQL statement with tables in master-detail relationships, it's common to have to decide whether to write the query using the WHERE EXISTS (. . .) clause or the WHERE value IN (. . .) clause. You may resist using WHERE EXISTS because it has the awkward syntax of returning a value, which you always ignore.
However, there's a difference when using rule-based optimization. You can determine the performance of a rule-based query by understanding which table is the driving table and how many rows each part returns.
When you write a query using the IN clause, you're telling the rule-based optimizer that you want the inner query to drive the outer query (think: IN = inside to outside). For example, to query the 14-row EMP table for the direct reports to the employee KING, you could write the following:
select ename from emp e
where mgr in (select empno from emp where ename = 'KING');
Here's the EXPLAIN PLAN for this query:
OBJECT OPERATION
---------- ----------------------------------------
SELECT STATEMENT()
NESTED LOOPS()
EMP TABLE ACCESS(FULL)
EMP TABLE ACCESS(BY INDEX ROWID)
PK_EMP INDEX(UNIQUE SCAN)
This query is virtually equivalent to this:
select e1.ename from emp e1,(select empno from emp where ename = 'KING') e2
where e1.mgr = e2.empno;
You can write the same query using EXISTS by moving the outer query column to a subquery condition, like this:
select ename from emp e
where exists (select 0 from emp where e.mgr = empno and ename = 'KING');
When you write EXISTS in a where clause, you're telling the optimizer that you want the outer query to be run first, using each value to fetch a value from the inner query (think: EXISTS = outside to inside).
The EXPLAIN PLAN result for the query is:
OBJECT OPERATION
---------- ----------------------------------------
SELECT STATEMENT()
FILTER()
EMP TABLE ACCESS(FULL)
EMP TABLE ACCESS(BY INDEX ROWID)
PK_EMP INDEX(UNIQUE SCAN)
This is virtually similar to the PL/SQL code:
set serveroutput on;
declare
l_count integer;
begin
for e in (select mgr,ename from emp) loop
select count(*) into l_count from emp
where e.mgr = empno and ename = 'KING';
if l_count != 0 then
dbms_output.put_line(e.ename);
end if;
end loop;
end;
To determine which clause offers better performance in rule-based optimization, consider how many rows the inner query will return in comparison to the outer query. In many cases, EXISTS is better because it requires you to specify a join condition, which can invoke an INDEX scan. However, IN is often better if the results of the subquery are very small. You usually want to run the query that returns the smaller set of results first.
Some people avoid the EXISTS clause because of the requirement to return a result from the query--even though the result is never used. Depending on personal style, people often use 'X,' 1, 0, or null. From looking at the EXPLAIN PLAN output, it appears that the optimizer throws out whatever value you enter and uses 0 all the time. Many developers get into the habit of always entering some constant value.
If you want to run your own tests, or see other examples, here are the two scripts I used:
REM -- explain.sql - view plan from PLAN_TABLE
set feedback off
set verify off
set pages 2000
column operation format a40
column object format a10
TTITLE * STATEMENT_ID = '&1' *
select object_name object,
lpad(' ',level-1)||operation||'('||options||')' operation
from plan_table
start with id = 0 and statement_id = '&1'
connect by prior id = parent_id and statement_id = '&1';
REM -- exists.sql - examples with EXPLAIN PLAN
REM -- IN vs. EXISTS
REM -- if you don't have a PLAN_TABLE, run ...
REM -- @?/rdbms/admin/xplan
alter session set optimizer_goal = rule;
truncate table plan_table;
REM -- find direct reports to KING
explain plan set statement_id = 'IN' for
select ename from emp e
where mgr in (select empno from emp where ename = 'KING');
explain plan set statement_id = 'JOIN-IN' for
select e1.ename from emp e1,(select empno from emp where ename = 'KING') e2
where e1.mgr = e2.empno;
explain plan set statement_id = 'EXISTS' for
select ename from emp e
where exists (select 0 from emp where e.mgr = empno and ename = 'KING');
explain plan set statement_id = '=' for
select ename from emp e
where mgr = (select empno from emp where ename = 'KING');
explain plan set statement_id = 'JOIN1' for
select e1.ename from emp e1,emp e2
where e1.mgr = e2.empno
and e2.ename = 'KING';
REM -- find employees with greater than average salaries
explain plan set statement_id = '>' for
select ename from emp e where e.sal > (select avg(sal) from emp);
explain plan set statement_id = 'JOIN2' for
select e1.ename from emp e1,(select avg(sal) sal from emp) e2
where e1.sal > e2.sal;
select object_name from
dba_objects
where object_id in
(
select object_id
from v$locked_object)
The above query never completed, and I always used to do a ctrl+c.
I rewrote the query by using the EXISTS clause instead of IN:
select owner,object_type,object_name
from dba_objects a
where exists
(
select object_id
from v$locked_object b
where a.object_id=b.object_id
)
/
The above query executes in seconds. I got hold of an old article from TechRepublic:
When coding a SQL statement with tables in master-detail relationships, it's common to have to decide whether to write the query using the WHERE EXISTS (. . .) clause or the WHERE value IN (. . .) clause. You may resist using WHERE EXISTS because it has the awkward syntax of returning a value, which you always ignore.
However, there's a difference when using rule-based optimization. You can determine the performance of a rule-based query by understanding which table is the driving table and how many rows each part returns.
When you write a query using the IN clause, you're telling the rule-based optimizer that you want the inner query to drive the outer query (think: IN = inside to outside). For example, to query the 14-row EMP table for the direct reports to the employee KING, you could write the following:
select ename from emp e
where mgr in (select empno from emp where ename = 'KING');
Here's the EXPLAIN PLAN for this query:
OBJECT OPERATION
---------- ----------------------------------------
SELECT STATEMENT()
NESTED LOOPS()
EMP TABLE ACCESS(FULL)
EMP TABLE ACCESS(BY INDEX ROWID)
PK_EMP INDEX(UNIQUE SCAN)
This query is virtually equivalent to this:
select e1.ename from emp e1,(select empno from emp where ename = 'KING') e2
where e1.mgr = e2.empno;
You can write the same query using EXISTS by moving the outer query column to a subquery condition, like this:
select ename from emp e
where exists (select 0 from emp where e.mgr = empno and ename = 'KING');
When you write EXISTS in a where clause, you're telling the optimizer that you want the outer query to be run first, using each value to fetch a value from the inner query (think: EXISTS = outside to inside).
The EXPLAIN PLAN result for the query is:
OBJECT OPERATION
---------- ----------------------------------------
SELECT STATEMENT()
FILTER()
EMP TABLE ACCESS(FULL)
EMP TABLE ACCESS(BY INDEX ROWID)
PK_EMP INDEX(UNIQUE SCAN)
This is virtually similar to the PL/SQL code:
set serveroutput on;
declare
l_count integer;
begin
for e in (select mgr,ename from emp) loop
select count(*) into l_count from emp
where e.mgr = empno and ename = 'KING';
if l_count != 0 then
dbms_output.put_line(e.ename);
end if;
end loop;
end;
To determine which clause offers better performance in rule-based optimization, consider how many rows the inner query will return in comparison to the outer query. In many cases, EXISTS is better because it requires you to specify a join condition, which can invoke an INDEX scan. However, IN is often better if the results of the subquery are very small. You usually want to run the query that returns the smaller set of results first.
Some people avoid the EXISTS clause because of the requirement to return a result from the query--even though the result is never used. Depending on personal style, people often use 'X,' 1, 0, or null. From looking at the EXPLAIN PLAN output, it appears that the optimizer throws out whatever value you enter and uses 0 all the time. Many developers get into the habit of always entering some constant value.
If you want to run your own tests, or see other examples, here are the two scripts I used:
REM -- explain.sql - view plan from PLAN_TABLE
set feedback off
set verify off
set pages 2000
column operation format a40
column object format a10
TTITLE * STATEMENT_ID = '&1' *
select object_name object,
lpad(' ',level-1)||operation||'('||options||')' operation
from plan_table
start with id = 0 and statement_id = '&1'
connect by prior id = parent_id and statement_id = '&1';
REM -- exists.sql - examples with EXPLAIN PLAN
REM -- IN vs. EXISTS
REM -- if you don't have a PLAN_TABLE, run ...
REM -- @?/rdbms/admin/xplan
alter session set optimizer_goal = rule;
truncate table plan_table;
REM -- find direct reports to KING
explain plan set statement_id = 'IN' for
select ename from emp e
where mgr in (select empno from emp where ename = 'KING');
explain plan set statement_id = 'JOIN-IN' for
select e1.ename from emp e1,(select empno from emp where ename = 'KING') e2
where e1.mgr = e2.empno;
explain plan set statement_id = 'EXISTS' for
select ename from emp e
where exists (select 0 from emp where e.mgr = empno and ename = 'KING');
explain plan set statement_id = '=' for
select ename from emp e
where mgr = (select empno from emp where ename = 'KING');
explain plan set statement_id = 'JOIN1' for
select e1.ename from emp e1,emp e2
where e1.mgr = e2.empno
and e2.ename = 'KING';
REM -- find employees with greater than average salaries
explain plan set statement_id = '>' for
select ename from emp e where e.sal > (select avg(sal) from emp);
explain plan set statement_id = 'JOIN2' for
select e1.ename from emp e1,(select avg(sal) sal from emp) e2
where e1.sal > e2.sal;
Categories: APPS Blogs
APPL_TOP snapshot feature
When a DBA goes to adadmin and does an Update current snapshot, what happens actually ? APPL_TOP snapshot feature was a new feature introduced in AD.G. I am pasting some good information from AD.G readme where they have explained this feature:
This feature allows you to record the current set of files and file versions in your APPL_TOP using the "Maintain snapshot information" menu entry in the AD Administration "Maintain Applications Files" menu.
There are two types of APPL_TOP snapshots: current view snapshots and named snapshots. Current view snapshots are created once and updated when appropriate to maintain a consistent view of the APPL_TOP contents. Named snapshots are created once using AD Administration and not updated. Both store information about files, file versions, and bug fixes present in an APPL_TOP.
You can create as many named snapshots of each APPL_TOP as you want. You can also just create or update the current view snapshot. When creating a named snapshot for an APPL_TOP, the snapshot feature will first create or update the current view snapshot for that APPL_TOP. The bug fix information for a named snapshot is copied from the current view snapshot for that APPL_TOP.
You must run AD Administration "Maintain snapshot information" once for each APPL_TOP before you can apply any patch that contains a "compatible feature prereq" line on that APPL_TOP. Running AD Administration "Maintain snapshot information" for an APPL_TOP completely populates the APPL_TOP's current view snapshot. This complete information is required for the automatic prerequisite patch checking feature to give correct results.
AutoPatch automatically updates the list of file versions and bug fixes in the current view snapshot for each APPL_TOP as patches are applied to that APPL_TOP. The combination of running AD Administration "Maintain snapshot information" once for an APPL_TOP and AutoPatch's incremental updates ensures that the current view snapshot for a given APPL_TOP contains an accurate picture of the current set of files and bug fixes present in that APPL_TOP.
APPL_TOP snapshot information is stored in the AD_SNAPSHOTS, AD_SNAPSHOT_FILES, and AD_SNAPSHOT_BUGFIXES tables. There is currently no user interface available for viewing snapshot information. The ability to view and compare APPL_TOP snapshots is being considered for future versions of AD and Oracle Applications Manager 11i.
This feature allows you to record the current set of files and file versions in your APPL_TOP using the "Maintain snapshot information" menu entry in the AD Administration "Maintain Applications Files" menu.
There are two types of APPL_TOP snapshots: current view snapshots and named snapshots. Current view snapshots are created once and updated when appropriate to maintain a consistent view of the APPL_TOP contents. Named snapshots are created once using AD Administration and not updated. Both store information about files, file versions, and bug fixes present in an APPL_TOP.
You can create as many named snapshots of each APPL_TOP as you want. You can also just create or update the current view snapshot. When creating a named snapshot for an APPL_TOP, the snapshot feature will first create or update the current view snapshot for that APPL_TOP. The bug fix information for a named snapshot is copied from the current view snapshot for that APPL_TOP.
You must run AD Administration "Maintain snapshot information" once for each APPL_TOP before you can apply any patch that contains a "compatible feature prereq" line on that APPL_TOP. Running AD Administration "Maintain snapshot information" for an APPL_TOP completely populates the APPL_TOP's current view snapshot. This complete information is required for the automatic prerequisite patch checking feature to give correct results.
AutoPatch automatically updates the list of file versions and bug fixes in the current view snapshot for each APPL_TOP as patches are applied to that APPL_TOP. The combination of running AD Administration "Maintain snapshot information" once for an APPL_TOP and AutoPatch's incremental updates ensures that the current view snapshot for a given APPL_TOP contains an accurate picture of the current set of files and bug fixes present in that APPL_TOP.
APPL_TOP snapshot information is stored in the AD_SNAPSHOTS, AD_SNAPSHOT_FILES, and AD_SNAPSHOT_BUGFIXES tables. There is currently no user interface available for viewing snapshot information. The ability to view and compare APPL_TOP snapshots is being considered for future versions of AD and Oracle Applications Manager 11i.
Categories: APPS Blogs
Oracle Database Lite (Olite)
In E-Business Suite, Olite is a technology component if you are using Mobile Field Services
Oracle Database Lite was developed from the ground up to be a smallfootprint, zero-administration, yet powerful mobile or embedded database.Because it requires no administration and has an extremely lightweight footprint, it is ideal for developing applications for mass deployment to thousands of mobile or disconnected end-users. Oracle Database Lite is not a full-featured Oracle Enterprise Edition database. It is specifically optimizedfor mobile and embedded environments. Despite the small footprint, Oracle Database Lite still supports most large-DBMS features, such as transactions,row-level locking, SQL-92, and Java stored procedures and triggers.
A classic implementation of Oracle Database Lite is in a field technicianapplication. For example, Company X has an Oracle Database Standard Edition in their datacenter. However, Company X field technicians are mobile sixty percent of the time without an internet connection. Oracle Database Lite allows Company X to subset the necessary field technician data and deploy the data to the technicians’ Laptop or PDA. The field technician is now able to befully functional and productive while offline and while mobile. If the Laptopor PDA is ever “connected”, Oracle Database Lite will quickly and securely synchronize the data, and changes, with the Oracle Database Standard Edition in the datacenter. Oracle Database Lite is optimized for these types of scenarios and can efficiently scale to thousands of users/devices.
Oracle Database Lite includes two main components.
1) The client component which runs on the mobile device or laptop to store the application data. 2) The server component (Mobile Server) to manage the synchronization and deployment of mobile applications.
Metalink Note 554931.1 lists the desupport notice for Olite versions upto 10.3 which is supported till Dec 2009 after which premier support ends.
Oracle Database Lite was developed from the ground up to be a smallfootprint, zero-administration, yet powerful mobile or embedded database.Because it requires no administration and has an extremely lightweight footprint, it is ideal for developing applications for mass deployment to thousands of mobile or disconnected end-users. Oracle Database Lite is not a full-featured Oracle Enterprise Edition database. It is specifically optimizedfor mobile and embedded environments. Despite the small footprint, Oracle Database Lite still supports most large-DBMS features, such as transactions,row-level locking, SQL-92, and Java stored procedures and triggers.
A classic implementation of Oracle Database Lite is in a field technicianapplication. For example, Company X has an Oracle Database Standard Edition in their datacenter. However, Company X field technicians are mobile sixty percent of the time without an internet connection. Oracle Database Lite allows Company X to subset the necessary field technician data and deploy the data to the technicians’ Laptop or PDA. The field technician is now able to befully functional and productive while offline and while mobile. If the Laptopor PDA is ever “connected”, Oracle Database Lite will quickly and securely synchronize the data, and changes, with the Oracle Database Standard Edition in the datacenter. Oracle Database Lite is optimized for these types of scenarios and can efficiently scale to thousands of users/devices.
Oracle Database Lite includes two main components.
1) The client component which runs on the mobile device or laptop to store the application data. 2) The server component (Mobile Server) to manage the synchronization and deployment of mobile applications.
Metalink Note 554931.1 lists the desupport notice for Olite versions upto 10.3 which is supported till Dec 2009 after which premier support ends.
Categories: APPS Blogs
QP_PERF_CTRL_PVT is invalid after maintenance pack
Depending on the version and patch status of your instance, you may find that QP_PERF_CTRL_PVT becomes invalid when you apply the maintenance pack patch 3480000. As per metalink note 559196.1, this object is no longer used in 11.5.0 and can be safely dropped. So go ahead and get rid of it:
drop package QP_PERF_CTRL_PVT;
drop package QP_PERF_CTRL_PVT;
Categories: APPS Blogs
64-bit binaries of ASCP
All E-Business Suite Application binaries are 32-bit. The only exception are ASCP Memory Based Snapshot programs: MSCNWS64.exe and MSONWS64.exe. These are 64-bit binaries. They have been made 64-bit because of their memory intensive nature.
I noticed that whenever we run adadmin to relink binaries of MSC and MSO, these two are never relinked. If you try to relink them manually through adrelink.sh force=y "MSC MSCNWS64.exe" it simply deletes the file and doesn't relink. I asked Oracle about this in an SR and this was their answer:
QUESTION
=========
How to relink MSCNWS64.exe and MSONWS64.exe manually and through adadmin ? We have seen that these files are not relinked if we relink all MSC and MSO binaries through adadmin
ANSWER
=======
Hi Vikram,
Recompiling the 64 bit Memory based planner executables that you are asking about is not required and doesn't work as the code lines are all self contained within that executable and it doesn't use other files in other directories like other executables for recompiling. So basically if an install or patch is applied this executable is fully self contained in similar fashion as if you were running a Netscape browser which is also an executable netscape.exe - so these executables do not need to be compiled/recompiled.
I noticed that whenever we run adadmin to relink binaries of MSC and MSO, these two are never relinked. If you try to relink them manually through adrelink.sh force=y "MSC MSCNWS64.exe" it simply deletes the file and doesn't relink. I asked Oracle about this in an SR and this was their answer:
QUESTION
=========
How to relink MSCNWS64.exe and MSONWS64.exe manually and through adadmin ? We have seen that these files are not relinked if we relink all MSC and MSO binaries through adadmin
ANSWER
=======
Hi Vikram,
Recompiling the 64 bit Memory based planner executables that you are asking about is not required and doesn't work as the code lines are all self contained within that executable and it doesn't use other files in other directories like other executables for recompiling. So basically if an install or patch is applied this executable is fully self contained in similar fashion as if you were running a Netscape browser which is also an executable netscape.exe - so these executables do not need to be compiled/recompiled.
Categories: APPS Blogs
DBUA is unable to connect to X11
In one of the iterations of 10g upgrade, dbua wouldn't launch and give this error:
Exception in thread "main" java.lang.InternalError: Can't connect to X11 window server using 'DB001:6' as the value of the DISPLAY variable.
[Loaded java.lang.StackTraceElement from /gpsess76/oracle/10.2.0/jdk/jre/lib/rt.jar]
at sun.awt.X11GraphicsEnvironment.initDisplay(Native Method)
at sun.awt.X11GraphicsEnvironment.(X11GraphicsEnvironment.java:134)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:141)
at java.awt.GraphicsEnvironment.getLocalGraphicsEnvironment(GraphicsEnvironment.java:62)
at java.awt.Font.initializeFont(Font.java:308)
at java.awt.Font.(Font.java:344)
at oracle.ewt.lwAWT.LWComponent.(Unknown Source)
at oracle.sysman.assistants.util.wizard.WizardObject.(WizardObject.java:64)
at oracle.sysman.assistants.dbma.ui.DBMAWizard.(DBMAWizard.java:184)
at oracle.sysman.assistants.dbma.ui.UICompManager.(UICompManager.java:153)
at oracle.sysman.assistants.dbma.Dbma.getCompManager(Dbma.java:154)
at oracle.sysman.assistants.dbma.Dbma.execute(Dbma.java:98)
at oracle.sysman.assistants.dbma.Dbma.statusMain(Dbma.java:199)
at oracle.sysman.assistants.dbma.Dbma.main(Dbma.java:180)
[Loaded java.lang.Shutdown from /gpsess76/oracle/10.2.0/jdk/jre/lib/rt.jar]
[Loaded java.lang.Shutdown$Lock from /gpsess76/oracle/10.2.0/jdk/jre/lib/rt.jar]
The X environment was properly set. DISPLAY variable was correct. We were able to launch xclock from the same session without issues. We were also able to start dbua of other instances in the same vnc session, we followed metalink note 443693.1 (on March 24 and 25 2008 this note was available, now this note is not visible) which suggests
modifying dbua: Remove the ‘$JRE_OPTION’ where there is a definition for this
‘–d64’ flag and it will work fine using the default –d32 data model.
We removed this block from dbua as suggested in 443693.1
JRE_OPTION=
if [ $PLATFORM = Solaris ]; then
MACH_HARDWARE=`/bin/uname -i`
case $MACH_HARDWARE in
i86pc)
LD_LIBRARY_PATH=/opt/ORCLcluster/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
;;
*)
JRE_OPTION=-d64
LD_LIBRARY_PATH_64=/opt/ORCLcluster/lib:$LIB_DIR:$ORACLE_HOME/network/lib:$L
D_LIBRARY_PATH_64
export LD_LIBRARY_PATH_64
;;
esac
fi
After this DBUA launched fine. We got errors later in the upgrade process like this one which is logged in the DBUA trace.log:
/dev11i/oracle/10.2.0/oui/lib/solaris64/liboraInstaller.so: wrong ELF class: ELFCLASS64 followed by a long list of java class errors.
DBUA jumped from 8% to 87% in 1 second after this error and after discussions with Oralce we had to follow the manual route of upgrading through catupgrd.sql.
In the morning today, we faced the same issue of DBUA refusing to launch in another instance. This time we did not hurry with a solution but waited and discussed. Subu asked us to compare the instances where it was running fine. We checked ulimit -a on working and non-working instances. On the instances dbua was working nofiles was set to 65536, on instance where dbua was throwing X11 error, nofiles was set to 2048. So we inserted this command in the .profile of the oracle user:
ulimit -n 65536
Logged in again as oracle user and checked ulimit
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 8192
coredump(blocks) unlimited
nofiles(descriptors) 65536
memory(kbytes) unlimited
When we launched dbua after this, it launched without any errors. The issue was resolved. It seems that 64 bit dbua process tries to open more than 2048 files, and unfortunately the 2049th file happens to be an X11 window, because of which we were getting those errors. After resetting nofiles to 65536, it was able to open those files.
Exception in thread "main" java.lang.InternalError: Can't connect to X11 window server using 'DB001:6' as the value of the DISPLAY variable.
[Loaded java.lang.StackTraceElement from /gpsess76/oracle/10.2.0/jdk/jre/lib/rt.jar]
at sun.awt.X11GraphicsEnvironment.initDisplay(Native Method)
at sun.awt.X11GraphicsEnvironment.(X11GraphicsEnvironment.java:134)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:141)
at java.awt.GraphicsEnvironment.getLocalGraphicsEnvironment(GraphicsEnvironment.java:62)
at java.awt.Font.initializeFont(Font.java:308)
at java.awt.Font.(Font.java:344)
at oracle.ewt.lwAWT.LWComponent.(Unknown Source)
at oracle.sysman.assistants.util.wizard.WizardObject.(WizardObject.java:64)
at oracle.sysman.assistants.dbma.ui.DBMAWizard.(DBMAWizard.java:184)
at oracle.sysman.assistants.dbma.ui.UICompManager.(UICompManager.java:153)
at oracle.sysman.assistants.dbma.Dbma.getCompManager(Dbma.java:154)
at oracle.sysman.assistants.dbma.Dbma.execute(Dbma.java:98)
at oracle.sysman.assistants.dbma.Dbma.statusMain(Dbma.java:199)
at oracle.sysman.assistants.dbma.Dbma.main(Dbma.java:180)
[Loaded java.lang.Shutdown from /gpsess76/oracle/10.2.0/jdk/jre/lib/rt.jar]
[Loaded java.lang.Shutdown$Lock from /gpsess76/oracle/10.2.0/jdk/jre/lib/rt.jar]
The X environment was properly set. DISPLAY variable was correct. We were able to launch xclock from the same session without issues. We were also able to start dbua of other instances in the same vnc session, we followed metalink note 443693.1 (on March 24 and 25 2008 this note was available, now this note is not visible) which suggests
modifying dbua: Remove the ‘$JRE_OPTION’ where there is a definition for this
‘–d64’ flag and it will work fine using the default –d32 data model.
We removed this block from dbua as suggested in 443693.1
JRE_OPTION=
if [ $PLATFORM = Solaris ]; then
MACH_HARDWARE=`/bin/uname -i`
case $MACH_HARDWARE in
i86pc)
LD_LIBRARY_PATH=/opt/ORCLcluster/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
;;
*)
JRE_OPTION=-d64
LD_LIBRARY_PATH_64=/opt/ORCLcluster/lib:$LIB_DIR:$ORACLE_HOME/network/lib:$L
D_LIBRARY_PATH_64
export LD_LIBRARY_PATH_64
;;
esac
fi
After this DBUA launched fine. We got errors later in the upgrade process like this one which is logged in the DBUA trace.log:
/dev11i/oracle/10.2.0/oui/lib/solaris64/liboraInstaller.so: wrong ELF class: ELFCLASS64 followed by a long list of java class errors.
DBUA jumped from 8% to 87% in 1 second after this error and after discussions with Oralce we had to follow the manual route of upgrading through catupgrd.sql.
In the morning today, we faced the same issue of DBUA refusing to launch in another instance. This time we did not hurry with a solution but waited and discussed. Subu asked us to compare the instances where it was running fine. We checked ulimit -a on working and non-working instances. On the instances dbua was working nofiles was set to 65536, on instance where dbua was throwing X11 error, nofiles was set to 2048. So we inserted this command in the .profile of the oracle user:
ulimit -n 65536
Logged in again as oracle user and checked ulimit
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 8192
coredump(blocks) unlimited
nofiles(descriptors) 65536
memory(kbytes) unlimited
When we launched dbua after this, it launched without any errors. The issue was resolved. It seems that 64 bit dbua process tries to open more than 2048 files, and unfortunately the 2049th file happens to be an X11 window, because of which we were getting those errors. After resetting nofiles to 65536, it was able to open those files.
Categories: APPS Blogs
Disable retention on Workflow Queues
Ahmed Alomari, the ex-head of Oracle E-Business Suite performance group suggested in his famous presentation on performance tuning of Apps to disable retention of workflow queues.
SQL> select name,retention
from dba_queues
where owner='APPLSYS' and retention != '0';
SQL> /
NAME RETENTION
----------------------------------------
WF_IN 604800
WF_OUT 604800
WF_DEFERRED 86400
WF_REPLAY_IN 604800
WF_REPLAY_OUT 604800
WF_NOTIFICATION_IN 86400
WF_NOTIFICATION_OUT 86400
WF_JAVA_DEFERRED 86400
8 rows selected.
SQL> exec DBMS_AQADM.ALTER_QUEUE(queue_name=>:b1,retention_time=>0);
A search on metalink for the string DBMS_AQADM.ALTER_QAUEUE returns three hits 273848.1, 468650.1, 358262.1. All of them are related to workflow performance
Oracle Workflow accesses several tables that can grow quite large. Workflow leaves lot of runtime data in certain workflow tables which needs to be purged on a periodic basis. Since a number of modules make use of workflow, the amount of obsolete runtime data that is retained in these tables is huge. The size of these tables and indexes can adversely affect performance.
Will update more on this.
SQL> select name,retention
from dba_queues
where owner='APPLSYS' and retention != '0';
SQL> /
NAME RETENTION
----------------------------------------
WF_IN 604800
WF_OUT 604800
WF_DEFERRED 86400
WF_REPLAY_IN 604800
WF_REPLAY_OUT 604800
WF_NOTIFICATION_IN 86400
WF_NOTIFICATION_OUT 86400
WF_JAVA_DEFERRED 86400
8 rows selected.
SQL> exec DBMS_AQADM.ALTER_QUEUE(queue_name=>:b1,retention_time=>0);
A search on metalink for the string DBMS_AQADM.ALTER_QAUEUE returns three hits 273848.1, 468650.1, 358262.1. All of them are related to workflow performance
Oracle Workflow accesses several tables that can grow quite large. Workflow leaves lot of runtime data in certain workflow tables which needs to be purged on a periodic basis. Since a number of modules make use of workflow, the amount of obsolete runtime data that is retained in these tables is huge. The size of these tables and indexes can adversely affect performance.
Will update more on this.
Categories: APPS Blogs
Extranet tier prompts for username/password twice after clone
Anand called me today and described a problem being faced:
After clone of extranet tier, when username and password are keyed in the first time, the username and password fields become blank and the user is prompted again for entering them. After the user enters the username and password the second time, the Self Service Page is shown. I asked him to tail the Apache log and see what corresponding entries were getting formed when we were doing this. This is how they looked:
192.168.15.1 - - [27/Apr/2008:10:44:14 -0400] "GET /oa_servlets/oracle.apps.fnd.sso.AppsLogin?requestUrl=http%3A%2F%2Fextdev.justanexample.com%3A8032%2FOA_HTML%2FOA.jsp%3Fpage%3D%2Foracle%2Fapps%2Ffnd%2Fframework%2Fnavigate%2Fwebui%2FHomePG%26homePage%3DY%26OAPB%3DFWK_HOMEPAGE_BRAND%26transactionid%3D1622860638%26oapc%3D2%26oas%3D5PZsF5fcdnhFfJ1R0rPWPA..&cancelUrl=http%3A%2F%2Fextdev.justanexample.com%3A8032%2Foa_servlets%2Foracle.apps.fnd.sso.AppsLogin&errText=You+have+insufficient+privileges+for+the+current+operation. HTTP/1.1" 302 705 0
192.168.15.1 - - [27/Apr/2008:10:44:15 -0400] "GET /OA_HTML/AppsLocalLogin.jsp?requestUrl=http%3A%2F%2Fextdev.justanexample.com%3A8032%2FOA_HTML%2FOA.jsp%3Fpage%3D%2Foracle%2Fapps%2Ffnd%2Fframework%2Fnavigate%2Fwebui%2FHomePG%26homePage%3DY%26OAPB%3DFWK_HOMEPAGE_BRAND%26transactionid%3D1622860638%26oapc%3D2%26oas%3D5PZsF5fcdnhFfJ1R0rPWPA..&cancelUrl=http%3A%2F%2Fextdev.justanexample.com%3A8032%2Foa_servlets%2Foracle.apps.fnd.sso.AppsLogin&langCode=US&errText=You+have+insufficient+privileges+for+the+current+operation.&s1=IinGd45sClw4CKn4cMrklw.. HTTP/1.1" 200 7261 1
192.168.15.1 - - [27/Apr/2008:10:44:52 -0400] "POST /OA_HTML/fndvald.jsp HTTP/1.1" 302 412 0
192.168.15.1 - - [27/Apr/2008:10:44:52 -0400] "GET /OA_HTML/OA.jsp?page=/oracle/apps/fnd/framework/navigate/webui/HomePG&homePage=Y&OAPB=FWK_HOMEPAGE_BRAND&transactionid=1622860638&oapc=2&oas=5PZsF5fcdnhFfJ1R0rPWPA.. HTTP/1.1" 200 11180 0
192.168.15.1 - - [27/Apr/2008:10:45:00 -0400] "GET /OA_HTML/OALogout.jsp?menu=Y HTTP/1.1" 302 270 0
192.168.15.1 - - [27/Apr/2008:10:45:00 -0400] "GET /oa_servlets/oracle.apps.fnd.sso.AppsLogout HTTP/1.1" 302 320 0
192.168.15.1 - - [27/Apr/2008:10:45:00 -0400] "GET /OA_HTML/AppsLocalLogout.jsp?returnUrl=http://extdev.justanexample.com:8000/oa_servlets/oracle.apps.fnd.sso.AppsLogin?langCode=US HTTP/1.1" 302 281 0
192.168.15.1 - - [27/Apr/2008:10:45:01 -0400] "GET /oa_servlets/oracle.apps.fnd.sso.AppsLogin?langCode=US HTTP/1.1" 302 435 0
192.168.15.1 - - [27/Apr/2008:10:45:01 -0400] "GET /OA_HTML/AppsLocalLogin.jsp?requestUrl=APPSHOMEPAGE&cancelUrl=http%3A%2F%2Fextdev.justanexample.com%3A8032%2Foa_servlets%2Foracle.apps.fnd.sso.AppsLogin&langCode=US&s2=34554EE5947F1746754F671CF150A536B02E53E4049CFB609BE4970DB131D03B HTTP/1.1" 200 8510 0
When the user was prompted for the password again, in the Apache logs it was showing this:
AppsLogin&errText=You+have+insufficient+privileges+for+the+current+operation. HTTP/1.1" 302 705 0
Seeing this error, Anand checked for the value of the profile option PON: External URL and PON: Default External User Responsibility:
SQL> select fnd_profile.value('PON_EXT_LOGIN_URL') from dual;
FND_PROFILE.VALUE('PON_EXT_LOGIN_URL')
--------------------------------------------------------------------------------
http://ext.justanexample.com:8000/OA_HTML/AppsLocalLogin.jsp
SQL> select fnd_profile.value('PON_DEFAULT_EXT_USER_RESP') from dual;
FND_PROFILE.VALUE('PON_DEFAULT_EXT_USER_RESP')
--------------------------------------------------------------------------------
PON_SOURCING_SUPPLIER
The profile option PON: External URL was still having the value of Production URL ext.justanexample.com instead of extdev.justanexample.com. He change this value to extdev.justanexample.com so that:
SQL> select fnd_profile.value('PON_EXT_LOGIN_URL') from dual;
FND_PROFILE.VALUE('PON_EXT_LOGIN_URL')
--------------------------------------------------------------------------------
http://extdev.justanexample.com:8000/OA_HTML/AppsLocalLogin.jsp
Apache was bounced.
The issue was resolved.
After clone of extranet tier, when username and password are keyed in the first time, the username and password fields become blank and the user is prompted again for entering them. After the user enters the username and password the second time, the Self Service Page is shown. I asked him to tail the Apache log and see what corresponding entries were getting formed when we were doing this. This is how they looked:
192.168.15.1 - - [27/Apr/2008:10:44:14 -0400] "GET /oa_servlets/oracle.apps.fnd.sso.AppsLogin?requestUrl=http%3A%2F%2Fextdev.justanexample.com%3A8032%2FOA_HTML%2FOA.jsp%3Fpage%3D%2Foracle%2Fapps%2Ffnd%2Fframework%2Fnavigate%2Fwebui%2FHomePG%26homePage%3DY%26OAPB%3DFWK_HOMEPAGE_BRAND%26transactionid%3D1622860638%26oapc%3D2%26oas%3D5PZsF5fcdnhFfJ1R0rPWPA..&cancelUrl=http%3A%2F%2Fextdev.justanexample.com%3A8032%2Foa_servlets%2Foracle.apps.fnd.sso.AppsLogin&errText=You+have+insufficient+privileges+for+the+current+operation. HTTP/1.1" 302 705 0
192.168.15.1 - - [27/Apr/2008:10:44:15 -0400] "GET /OA_HTML/AppsLocalLogin.jsp?requestUrl=http%3A%2F%2Fextdev.justanexample.com%3A8032%2FOA_HTML%2FOA.jsp%3Fpage%3D%2Foracle%2Fapps%2Ffnd%2Fframework%2Fnavigate%2Fwebui%2FHomePG%26homePage%3DY%26OAPB%3DFWK_HOMEPAGE_BRAND%26transactionid%3D1622860638%26oapc%3D2%26oas%3D5PZsF5fcdnhFfJ1R0rPWPA..&cancelUrl=http%3A%2F%2Fextdev.justanexample.com%3A8032%2Foa_servlets%2Foracle.apps.fnd.sso.AppsLogin&langCode=US&errText=You+have+insufficient+privileges+for+the+current+operation.&s1=IinGd45sClw4CKn4cMrklw.. HTTP/1.1" 200 7261 1
192.168.15.1 - - [27/Apr/2008:10:44:52 -0400] "POST /OA_HTML/fndvald.jsp HTTP/1.1" 302 412 0
192.168.15.1 - - [27/Apr/2008:10:44:52 -0400] "GET /OA_HTML/OA.jsp?page=/oracle/apps/fnd/framework/navigate/webui/HomePG&homePage=Y&OAPB=FWK_HOMEPAGE_BRAND&transactionid=1622860638&oapc=2&oas=5PZsF5fcdnhFfJ1R0rPWPA.. HTTP/1.1" 200 11180 0
192.168.15.1 - - [27/Apr/2008:10:45:00 -0400] "GET /OA_HTML/OALogout.jsp?menu=Y HTTP/1.1" 302 270 0
192.168.15.1 - - [27/Apr/2008:10:45:00 -0400] "GET /oa_servlets/oracle.apps.fnd.sso.AppsLogout HTTP/1.1" 302 320 0
192.168.15.1 - - [27/Apr/2008:10:45:00 -0400] "GET /OA_HTML/AppsLocalLogout.jsp?returnUrl=http://extdev.justanexample.com:8000/oa_servlets/oracle.apps.fnd.sso.AppsLogin?langCode=US HTTP/1.1" 302 281 0
192.168.15.1 - - [27/Apr/2008:10:45:01 -0400] "GET /oa_servlets/oracle.apps.fnd.sso.AppsLogin?langCode=US HTTP/1.1" 302 435 0
192.168.15.1 - - [27/Apr/2008:10:45:01 -0400] "GET /OA_HTML/AppsLocalLogin.jsp?requestUrl=APPSHOMEPAGE&cancelUrl=http%3A%2F%2Fextdev.justanexample.com%3A8032%2Foa_servlets%2Foracle.apps.fnd.sso.AppsLogin&langCode=US&s2=34554EE5947F1746754F671CF150A536B02E53E4049CFB609BE4970DB131D03B HTTP/1.1" 200 8510 0
When the user was prompted for the password again, in the Apache logs it was showing this:
AppsLogin&errText=You+have+insufficient+privileges+for+the+current+operation. HTTP/1.1" 302 705 0
Seeing this error, Anand checked for the value of the profile option PON: External URL and PON: Default External User Responsibility:
SQL> select fnd_profile.value('PON_EXT_LOGIN_URL') from dual;
FND_PROFILE.VALUE('PON_EXT_LOGIN_URL')
--------------------------------------------------------------------------------
http://ext.justanexample.com:8000/OA_HTML/AppsLocalLogin.jsp
SQL> select fnd_profile.value('PON_DEFAULT_EXT_USER_RESP') from dual;
FND_PROFILE.VALUE('PON_DEFAULT_EXT_USER_RESP')
--------------------------------------------------------------------------------
PON_SOURCING_SUPPLIER
The profile option PON: External URL was still having the value of Production URL ext.justanexample.com instead of extdev.justanexample.com. He change this value to extdev.justanexample.com so that:
SQL> select fnd_profile.value('PON_EXT_LOGIN_URL') from dual;
FND_PROFILE.VALUE('PON_EXT_LOGIN_URL')
--------------------------------------------------------------------------------
http://extdev.justanexample.com:8000/OA_HTML/AppsLocalLogin.jsp
Apache was bounced.
The issue was resolved.
Categories: APPS Blogs
Apps binaries write core files in /var/crash/cores
Ever since we migrated to Solaris 10, we have had various concurrent program binaries creating core dumps. Some of them get resolved after we relink the binaries. But some continue core dumps even after relinking them. ARGLTP or the AR to GL Transfer Program is one of the binaries which does this. I have had an SR logged with Oracle for some time. The strangest thing is that the concurrent requests calling ARGLTP complete normal. But for every run of ARGLTP a coredump with signal 11 is created. Here's a stack trace:
adb /erp11i/erp/appl/ar/11.5.0/bin/ARGLTP
1208446806-ARGLTP-15767-62984-54011-tsgsd1009-sun4u
core file = 1208446806-ARGLTP-15767-62984-54011-tsgsd1009-sun4u -- program
``/erp11i/erp/appl/ar/11.5.0/bin/ARGLTP
'' on platform SUNW,Netra-T12
SIGSEGV: Segmentation Fault
$c
libc.so.1`strcmp+0x170(1739c8, 73657364, 0, 0, 1d0634, 1d0614)
afppre+0x1cc(2a81e0, 261b88, 50, 50, ff1ea2b4, ff1f29f8)
fdpcls+0x3e4(4e, 1c2b28, 1c2b18, 1c2800, 1c2800, 1)
main+0x76e0(14f730, 14f774, ffbf9fbc, a, 2a4760, 7f80)
_start+0xdc(0, 0, 0, 0, 0, 0)
$q
A common thing in all the coring binaries is the OS library libc.so.1. Oracle has asked us to check with Sun if this is a known issue and if a new version of libc.so.1 is available. I have an SR logged with Sun also for this.
We finally got a solution. Patch 6815663 is a post ATG RUP6 one off patch which fixes this issue. Since it is not fully regression tested, it is protected by a password. You have to log an SR to get the password. After applying this patch, we have seen that none of the binaries like ARGLTP, PARGDR, PAVDVC, PASGLT etc. are coring anymore.
adb /erp11i/erp/appl/ar/11.5.0/bin/ARGLTP
1208446806-ARGLTP-15767-62984-54011-tsgsd1009-sun4u
core file = 1208446806-ARGLTP-15767-62984-54011-tsgsd1009-sun4u -- program
``/erp11i/erp/appl/ar/11.5.0/bin/ARGLTP
'' on platform SUNW,Netra-T12
SIGSEGV: Segmentation Fault
$c
libc.so.1`strcmp+0x170(1739c8, 73657364, 0, 0, 1d0634, 1d0614)
afppre+0x1cc(2a81e0, 261b88, 50, 50, ff1ea2b4, ff1f29f8)
fdpcls+0x3e4(4e, 1c2b28, 1c2b18, 1c2800, 1c2800, 1)
main+0x76e0(14f730, 14f774, ffbf9fbc, a, 2a4760, 7f80)
_start+0xdc(0, 0, 0, 0, 0, 0)
$q
A common thing in all the coring binaries is the OS library libc.so.1. Oracle has asked us to check with Sun if this is a known issue and if a new version of libc.so.1 is available. I have an SR logged with Sun also for this.
We finally got a solution. Patch 6815663 is a post ATG RUP6 one off patch which fixes this issue. Since it is not fully regression tested, it is protected by a password. You have to log an SR to get the password. After applying this patch, we have seen that none of the binaries like ARGLTP, PARGDR, PAVDVC, PASGLT etc. are coring anymore.
Categories: APPS Blogs
DBUA vs manual upgrade
While upgrading an Oracle Database, which is better DBUA or manual upgrade ?
Arguments for DBUA:
1. DBUA takes care of all the pre-install checks like kernel parameters, memory, space etc.
2. DBUA gives the option of automatic RMAN backup of the existing DB
3. DBUA upgrade is faster than manual upgrade
4. Easier to use.
Arguments against DBUA:
1. If there are errors during the upgrade, there is usually no way to resume. In many cases, where a single or multiple components of the upgrade have failed, you can restart DBUA and it takes care of the missed steps without issues.
2. Manual upgrade gives you absolute control. You know exactly where you are in the upgrade process. Tracking this in DBUA involves watching DBUA logs, Alert log at the time DBUA is running. I never liked the progress bar. It doesn't convey anything. Along with progress bar, DBUA should also show DB alert log and what script is being executed. That helps in debugging and keeps you in touch with the upgrade instead of insulating you from it.
3. DBUA takes the value of sga and other memory settings from your 9i database and creates 10g/11g init.ora based on those. Unless you pre-size your 9i init.ora, you may get ORA-4031 errors during the actual upgrade with DBUA.
Most of the time, when DBUA screws up, you have to continue your upgrade by abandoning DBUA and take a detour to manual upgrade. E-Business Suite Databases are not small, and do not provide you the luxury of restoring backup and starting with DBUA again.
Arguments for DBUA:
1. DBUA takes care of all the pre-install checks like kernel parameters, memory, space etc.
2. DBUA gives the option of automatic RMAN backup of the existing DB
3. DBUA upgrade is faster than manual upgrade
4. Easier to use.
Arguments against DBUA:
1. If there are errors during the upgrade, there is usually no way to resume. In many cases, where a single or multiple components of the upgrade have failed, you can restart DBUA and it takes care of the missed steps without issues.
2. Manual upgrade gives you absolute control. You know exactly where you are in the upgrade process. Tracking this in DBUA involves watching DBUA logs, Alert log at the time DBUA is running. I never liked the progress bar. It doesn't convey anything. Along with progress bar, DBUA should also show DB alert log and what script is being executed. That helps in debugging and keeps you in touch with the upgrade instead of insulating you from it.
3. DBUA takes the value of sga and other memory settings from your 9i database and creates 10g/11g init.ora based on those. Unless you pre-size your 9i init.ora, you may get ORA-4031 errors during the actual upgrade with DBUA.
Most of the time, when DBUA screws up, you have to continue your upgrade by abandoning DBUA and take a detour to manual upgrade. E-Business Suite Databases are not small, and do not provide you the luxury of restoring backup and starting with DBUA again.
Categories: APPS Blogs
CTXSYS and PORTAL30 invalids after upgrade
Benette asked me about CTXSYS and PORTAL30 invalids which had resulted after upgrade. I asked him for a list and this is what he gave me:
APPS Package Body AST_OFL_TERR_MISS_ASGN_REPORT
APPS Package Body FND_OID_DIAG
CTXSYS Procedure WWSBR_CORNER_CTX_202
CTXSYS Procedure WWSBR_DOC_CTX_202
CTXSYS Procedure WWSBR_PERSP_CTX_202
CTXSYS Procedure WWSBR_THING_CTX_202
CTXSYS Procedure WWSBR_TOPIC_CTX_202
PORTAL30 Package WWV_MONITOR_WEBVIEW
PORTAL30 Package WWV_QBE_TABLE
PORTAL30 Package WWV_RENDER_QBE
PORTAL30 Package WWV_SYS_RENDER_QBE
PORTAL30 Package Body DBE_TABLE_ACTION
PORTAL30 Package Body WWV_AK
PORTAL30 Package Body WWV_COMPONENT_CONTROL
PORTAL30 Package Body WWV_MENU_BUILD
PORTAL30 Package Body WWV_MONITOR_DATABASE
PORTAL30 Package Body WWV_MONITOR_WEBVIEW
PORTAL30 Package Body WWV_QBE
PORTAL30 Package Body WWV_QBE_PROC
PORTAL30 Package Body WWV_QBE_TABLE
PORTAL30 Package Body WWV_QBE_UTILITY
PORTAL30 Package Body WWV_RENDER_QBE
PORTAL30 Package Body WWV_SYS_RENDER_PAGINATE
PORTAL30 Package Body WWV_SYS_RENDER_QBE
PORTAL30 Package Body WWV_SYS_RENDER_QBEFORM
PUBLIC Synonym DEBUG_CARTX
The simple answer to all the invalids above is: "Drop them, they are not needed." The Metalink Notes which advise this are:
444348.1 for Portal30 invalids
458370.1 for CTXSYS invalids which are calling the PORTAL30 invalids
352808.1 advises dropping AST_OFL_TERR_MISS_ASGN_REPORT as it is no longer used.
380480.1, 386746.1 and 444348.1 advise dropping DEBUG_CARTX
360285.1 advises dropping FND_OID_DIAG if you are not using OID/SSO.
APPS Package Body AST_OFL_TERR_MISS_ASGN_REPORT
APPS Package Body FND_OID_DIAG
CTXSYS Procedure WWSBR_CORNER_CTX_202
CTXSYS Procedure WWSBR_DOC_CTX_202
CTXSYS Procedure WWSBR_PERSP_CTX_202
CTXSYS Procedure WWSBR_THING_CTX_202
CTXSYS Procedure WWSBR_TOPIC_CTX_202
PORTAL30 Package WWV_MONITOR_WEBVIEW
PORTAL30 Package WWV_QBE_TABLE
PORTAL30 Package WWV_RENDER_QBE
PORTAL30 Package WWV_SYS_RENDER_QBE
PORTAL30 Package Body DBE_TABLE_ACTION
PORTAL30 Package Body WWV_AK
PORTAL30 Package Body WWV_COMPONENT_CONTROL
PORTAL30 Package Body WWV_MENU_BUILD
PORTAL30 Package Body WWV_MONITOR_DATABASE
PORTAL30 Package Body WWV_MONITOR_WEBVIEW
PORTAL30 Package Body WWV_QBE
PORTAL30 Package Body WWV_QBE_PROC
PORTAL30 Package Body WWV_QBE_TABLE
PORTAL30 Package Body WWV_QBE_UTILITY
PORTAL30 Package Body WWV_RENDER_QBE
PORTAL30 Package Body WWV_SYS_RENDER_PAGINATE
PORTAL30 Package Body WWV_SYS_RENDER_QBE
PORTAL30 Package Body WWV_SYS_RENDER_QBEFORM
PUBLIC Synonym DEBUG_CARTX
The simple answer to all the invalids above is: "Drop them, they are not needed." The Metalink Notes which advise this are:
444348.1 for Portal30 invalids
458370.1 for CTXSYS invalids which are calling the PORTAL30 invalids
352808.1 advises dropping AST_OFL_TERR_MISS_ASGN_REPORT as it is no longer used.
380480.1, 386746.1 and 444348.1 advise dropping DEBUG_CARTX
360285.1 advises dropping FND_OID_DIAG if you are not using OID/SSO.
Categories: APPS Blogs
TNS issues
Recently in a fresh installation, TNS listener refused to start with these errors:
TNS-12546 / ORA-12546: TNS:permission denied
TNS-12560 / ORA-12560: TNS:protocol adapter error
TNS-00516: permission denied
Solaris Error: 13: permission denied
I checked the port on which we were trying to start:
$ telnet localhost 1521
Trying 127.0.0.1...
telnet: connect to address 127.0.0.1: Connection refused
Trying ::1...
telnet: Unable to connect to remote host: Network is unreachable
This means the port is free
I checked the directory /var/tmp/.oracle
$ ls -ld /var/tmp/.oracle
drwxrwxrwt 2 root root 2560 Apr 22 11:10 /var/tmp/.oracle
Even though the permission was 777 on this directory, it was unable to write to it. So I gave ownership of this directory to oracle user
su - root
chown oracle:dba /var/tmp/.oracle
I started listener again. It worked. ls -ltr /var/tmp/.oracle showed two new files:
srwxrwxrwx 1 oracle dba 0 Apr 23 14:26 sEXTPROCgpslmpd1
srwxrwxrwx 1 oracle dba 0 Apr 23 14:26 s#23310.1
If you issue a file command on these:
$ file sEXTPROCgpslmpd1
sEXTPROCgpslmpd1: socket
$ file s#23310.1
s#23310.1: socket
oralmpd1@tsgsd1007 #
These are not files but sockets. Because the ownership was not with oracle, it was unable to create a socket in this directory. Since listener had started and socket was created, I changed back the permission of this directory to root:
su - root
chown root:root /var/tmp/.oracle
I shutdown and restarted listener just to double check and it worked fine.
TNS-12546 / ORA-12546: TNS:permission denied
TNS-12560 / ORA-12560: TNS:protocol adapter error
TNS-00516: permission denied
Solaris Error: 13: permission denied
I checked the port on which we were trying to start:
$ telnet localhost 1521
Trying 127.0.0.1...
telnet: connect to address 127.0.0.1: Connection refused
Trying ::1...
telnet: Unable to connect to remote host: Network is unreachable
This means the port is free
I checked the directory /var/tmp/.oracle
$ ls -ld /var/tmp/.oracle
drwxrwxrwt 2 root root 2560 Apr 22 11:10 /var/tmp/.oracle
Even though the permission was 777 on this directory, it was unable to write to it. So I gave ownership of this directory to oracle user
su - root
chown oracle:dba /var/tmp/.oracle
I started listener again. It worked. ls -ltr /var/tmp/.oracle showed two new files:
srwxrwxrwx 1 oracle dba 0 Apr 23 14:26 sEXTPROCgpslmpd1
srwxrwxrwx 1 oracle dba 0 Apr 23 14:26 s#23310.1
If you issue a file command on these:
$ file sEXTPROCgpslmpd1
sEXTPROCgpslmpd1: socket
$ file s#23310.1
s#23310.1: socket
oralmpd1@tsgsd1007 #
These are not files but sockets. Because the ownership was not with oracle, it was unable to create a socket in this directory. Since listener had started and socket was created, I changed back the permission of this directory to root:
su - root
chown root:root /var/tmp/.oracle
I shutdown and restarted listener just to double check and it worked fine.
Categories: APPS Blogs
ICX_TRANSACTIONS_U1 ORA-0142
One of the DBAs got this error while doing an E-biz patch through adpatch:
The table is missing the index ICX_TRANSACTIONS_U1
or index ICX_TRANSACTIONS_U1 exists on another table.
Create it with the statement:
Start time for statement below is: Tue Apr 22 2008 18:19:38
CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS
(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE
10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS PARALLEL TABLESPACE ICXX
AD Worker error:
The following ORACLE error:
ORA-12801: error signaled in parallel query server P000
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
occurred while executing the SQL statement:
CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS
(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE
10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS PARALLEL TABLESPACE ICXX
AD Worker error:
Unable to compare or correct tables or indexes or keys
because of the error above
Solution is:
Execute the following SQL to prevent errors during Patch Application through adpatch:
SELECT TRANSACTION_ID, count(*)
FROM ICX.ICX_TRANSACTIONS
GROUP BY TRANSACTION_ID
HAVING count(*)>1
If the Above query returns any Row then Please execute the following SQL :
$ICX_TOP/sql (named ICXDLTMP.sql).
Keep in mind: This program has to be executed at least once a week to clean up ICX_TRANSACTIONS and ICX_SESSION (otherwise they will grow out of control).
The table is missing the index ICX_TRANSACTIONS_U1
or index ICX_TRANSACTIONS_U1 exists on another table.
Create it with the statement:
Start time for statement below is: Tue Apr 22 2008 18:19:38
CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS
(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE
10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS PARALLEL TABLESPACE ICXX
AD Worker error:
The following ORACLE error:
ORA-12801: error signaled in parallel query server P000
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
occurred while executing the SQL statement:
CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS
(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE
10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS PARALLEL TABLESPACE ICXX
AD Worker error:
Unable to compare or correct tables or indexes or keys
because of the error above
Solution is:
Execute the following SQL to prevent errors during Patch Application through adpatch:
SELECT TRANSACTION_ID, count(*)
FROM ICX.ICX_TRANSACTIONS
GROUP BY TRANSACTION_ID
HAVING count(*)>1
If the Above query returns any Row then Please execute the following SQL :
$ICX_TOP/sql (named ICXDLTMP.sql).
Keep in mind: This program has to be executed at least once a week to clean up ICX_TRANSACTIONS and ICX_SESSION (otherwise they will grow out of control).
Categories: APPS Blogs
Configure workflow notification mailer without IMAP
This is one my favorite interview questions, which has stumped many a great candidate. Question is "I do not need inbound processing, is there a way to configure workflow notification mailer without using IMAP ?" The candidate who is asked this question, goes blank. Details of implementing it is given in metalink note 268274.1:
How To Configure The Workflow Java Mailer Without An IMAP Account (Assumes No Inbound Processing)
Goal
How To Run The Java Workflow Mailer Without Inbound Notification Processing
If you would like to apply 11i.OWF.G you must implement the new java notification mailer,
This requires you to have an IMAP4 compliant email account for inbound notification
processing and a SMTP account for outbound processing. Unless both are configured correctly
you are unable to complete the setup of the mailer as the verification page (page 3-4) in OAM
never completes (unless both inbound and outbound email servers are configured and setup corretly.
Some Customers only use the outbound email processing from the mailer and do not require
inbound processing.
Solution
Steps to setup the WF Mailer for Outbound Processing only
Please apply patch:3409889 to install this new functionality. After applying the patch, you need to perform the following steps:
1:- Login to OAM
2:- Choose the Workflow Manager screen
3:- Choose Service Components
4:- Edit the "Workflow Mailer"
5:- Navigate to page 3 of Workflow Configuration
6:- Set Inbound Thread Count = 0 (zero)
7:- If you use outbound processing set Outbound Thread Count = 1
8:- In the Inbound EMail Account details please enter any data
(this cannot be null)
9:- In the Outbound EMail Account details add the details for your outbound server
10:- Shutdown and restart the application server and the mailer
Setting inbound thread count = 0 makes the OAM Configuration screen to not perform and verification of the IMAP server. Therefore, this allows you to continue with the setup of Workflow Mailer.
NOTE: For standalone ONLY:
You will have the code within workflow 2.6.3.5 which does not validate the IAMP server
if the Inbound Thread Count is set to Zero, if you upgrade to iAS10.1.2 and use
Content Manager 9.0.4.2.
References
Bug 3178398 - In 11.5.9 Must Use Email Processing When Responding To Notifications
Note 231286.1 - Configuring the Oracle Workflow 2.6 Java-based Notification Mailer with Oracle Applications 11i
How To Configure The Workflow Java Mailer Without An IMAP Account (Assumes No Inbound Processing)
Goal
How To Run The Java Workflow Mailer Without Inbound Notification Processing
If you would like to apply 11i.OWF.G you must implement the new java notification mailer,
This requires you to have an IMAP4 compliant email account for inbound notification
processing and a SMTP account for outbound processing. Unless both are configured correctly
you are unable to complete the setup of the mailer as the verification page (page 3-4) in OAM
never completes (unless both inbound and outbound email servers are configured and setup corretly.
Some Customers only use the outbound email processing from the mailer and do not require
inbound processing.
Solution
Steps to setup the WF Mailer for Outbound Processing only
Please apply patch:3409889 to install this new functionality. After applying the patch, you need to perform the following steps:
1:- Login to OAM
2:- Choose the Workflow Manager screen
3:- Choose Service Components
4:- Edit the "Workflow Mailer"
5:- Navigate to page 3 of Workflow Configuration
6:- Set Inbound Thread Count = 0 (zero)
7:- If you use outbound processing set Outbound Thread Count = 1
8:- In the Inbound EMail Account details please enter any data
(this cannot be null)
9:- In the Outbound EMail Account details add the details for your outbound server
10:- Shutdown and restart the application server and the mailer
Setting inbound thread count = 0 makes the OAM Configuration screen to not perform and verification of the IMAP server. Therefore, this allows you to continue with the setup of Workflow Mailer.
NOTE: For standalone ONLY:
You will have the code within workflow 2.6.3.5 which does not validate the IAMP server
if the Inbound Thread Count is set to Zero, if you upgrade to iAS10.1.2 and use
Content Manager 9.0.4.2.
References
Bug 3178398 - In 11.5.9 Must Use Email Processing When Responding To Notifications
Note 231286.1 - Configuring the Oracle Workflow 2.6 Java-based Notification Mailer with Oracle Applications 11i
Categories: APPS Blogs
Run a concurrent program from command line
It is very much possible to run a concurrent program from unix command line. Metalink Note 1031719.6 describes how to run some GL programs from command line. We can use these instructions as base for being able to run any concurrent program from command line.
Problem Description
-------------------
How do you run Posting (GLPPOS), or other concurrent programs from the
command line?
Solution Description
--------------------
You can run any GL program from the command line. Follow these steps:
1) Verify that you run this at a time when there are no incompatible
programs running. To find the incompatible programs:
Responsibility = System Administrator
GUI Navigation = Concurrent/Program/Define
Query on GLPPOS in the Short Name field.
Click on the Incompatibilities button.
2) Change the profile "Concurrent Hold Request" to Yes.
Responsibility = System Administrator
GUI Navigation = Profile/System
Query on the profile option 'Concurrent Hold Requests'.
Set it to Yes at the User level and Save.
3) Select a journal batch for posting. The request will be put on hold.
Responsibility = General Ledger Super User
GUI Navigation = Journals/Post
4) Go to the Concurrent Requests form and get the parameters for the
Posting concurrent process.
The parameters for posting are:
set of books id
chart of accounts id
posting_run_id
5) Run posting from the command line.
$GL_TOP/bin/GLPPOS 0 Y X 2>
Where: : apps username and password
: The parameters for the program,
separated by spaces. For NULL parameters
you need to specify closed double quotes ("").
X : Indicates that you want to run the program
in debug mode. This can be any character and
is optional.
2> : Use this to redirect standard errors and
output to a file. File name is the output
file name it will create. It will be created
in your current directory, if a full path
is not specified. This is optional
but recommended.
Example:
If you go to the concurrent requests form and see in the
Parameters field: 1,101,2546, you would enter the following command
at the Unix prompt:
$GL_TOP/bin/GLPPOS 0 Y 1 101 2546 X 2>
6) Change the profile option 'Concurrent Hold Requests' back to its
original setting.
Be sure to run the program from a directory for which you have write
privileges. That is where the log and out files will be created.
Also make sure that you are in the proper application environment.
Here is an example of the command and the output you will see.
FIN107>$GL_TOP/bin/GLPPOS apps/apps 0 Y 8 50128 84 2 2> njpost2.out
DEBUG: started in debug_mode ......
FIN107> ls
L54536.log L54540.log O54536.out O54540.out njpost.out post.out
L54538.logL54542.log O54538.out O54542.out njpost2.out test
FIN107> more njpost2.out
Log filename : L54542.log
Report filename : O54542.out
glpmai() fnd_user_id is -1
glpmai() fnd_user_name is ANONYMOUS
glpmai() req_id is 0
glpmai() sob_id is 8
etc.
NOTE: In Release 11.5, selected General Ledger programs can be run in debug
mode by setting the profile option 'GL: Debug Mode' to Yes at the
appropriate level.
These programs are:
- GLCRVL - Revaluation
- GLPPOS - Posting
- GLTTRN - Translation
- GLCCON - Consolidation Transfer
- GLAMAS - Run MassAllocations
See Note 232669.1: 'How To Run Selected General Ledger Programs In Debug Mode
in R11.5' for more information.
Problem Description
-------------------
How do you run Posting (GLPPOS), or other concurrent programs from the
command line?
Solution Description
--------------------
You can run any GL program from the command line. Follow these steps:
1) Verify that you run this at a time when there are no incompatible
programs running. To find the incompatible programs:
Responsibility = System Administrator
GUI Navigation = Concurrent/Program/Define
Query on GLPPOS in the Short Name field.
Click on the Incompatibilities button.
2) Change the profile "Concurrent Hold Request" to Yes.
Responsibility = System Administrator
GUI Navigation = Profile/System
Query on the profile option 'Concurrent Hold Requests'.
Set it to Yes at the User level and Save.
3) Select a journal batch for posting. The request will be put on hold.
Responsibility = General Ledger Super User
GUI Navigation = Journals/Post
4) Go to the Concurrent Requests form and get the parameters for the
Posting concurrent process.
The parameters for posting are:
set of books id
chart of accounts id
posting_run_id
5) Run posting from the command line.
$GL_TOP/bin/GLPPOS 0 Y X 2>
Where: : apps username and password
: The parameters for the program,
separated by spaces. For NULL parameters
you need to specify closed double quotes ("").
X : Indicates that you want to run the program
in debug mode. This can be any character and
is optional.
2> : Use this to redirect standard errors and
output to a file. File name is the output
file name it will create. It will be created
in your current directory, if a full path
is not specified. This is optional
but recommended.
Example:
If you go to the concurrent requests form and see in the
Parameters field: 1,101,2546, you would enter the following command
at the Unix prompt:
$GL_TOP/bin/GLPPOS 0 Y 1 101 2546 X 2>
6) Change the profile option 'Concurrent Hold Requests' back to its
original setting.
Be sure to run the program from a directory for which you have write
privileges. That is where the log and out files will be created.
Also make sure that you are in the proper application environment.
Here is an example of the command and the output you will see.
FIN107>$GL_TOP/bin/GLPPOS apps/apps 0 Y 8 50128 84 2 2> njpost2.out
DEBUG: started in debug_mode ......
FIN107> ls
L54536.log L54540.log O54536.out O54540.out njpost.out post.out
L54538.logL54542.log O54538.out O54542.out njpost2.out test
FIN107> more njpost2.out
Log filename : L54542.log
Report filename : O54542.out
glpmai() fnd_user_id is -1
glpmai() fnd_user_name is ANONYMOUS
glpmai() req_id is 0
glpmai() sob_id is 8
etc.
NOTE: In Release 11.5, selected General Ledger programs can be run in debug
mode by setting the profile option 'GL: Debug Mode' to Yes at the
appropriate level.
These programs are:
- GLCRVL - Revaluation
- GLPPOS - Posting
- GLTTRN - Translation
- GLCCON - Consolidation Transfer
- GLAMAS - Run MassAllocations
See Note 232669.1: 'How To Run Selected General Ledger Programs In Debug Mode
in R11.5' for more information.
Categories: APPS Blogs
Corrupt label, wrong magic number
I found the following in /var/adm/messages:
Apr 17 14:40:05 erpdbbox1 scsi: [ID 107833 kern.warning] WARNING: /ssm@0,0/pci@19,700000/SUNW,emlxs@1/fp@0,0/ssd@w50060482d52d43a7,0 (ssd1):
Apr 17 14:40:05 erpdbbox1 Corrupt label; wrong magic number
Apr 17 14:40:05 erpdbbox1 scsi: [ID 107833 kern.warning] WARNING: /ssm@0,0/pci@19,700000/SUNW,emlxs@1/fp@0,0/ssd@w50060482d52d43a7,0 (ssd1):
Apr 17 14:40:05 erpdbbox1 Corrupt label; wrong magic number
Apr 17 14:40:05 erpdbbox1 scsi: [ID 107833 kern.warning] WARNING: /ssm@0,0/pci@18,600000/SUNW,emlxs@1/fp@0,0/ssd@w50060482d52d43a9,0 (ssd0):
Apr 17 14:40:05 erpdbbox1 Corrupt label; wrong magic number
Apr 17 14:40:05 erpdbbox1 scsi: [ID 107833 kern.warning] WARNING: /ssm@0,0/pci@18,600000/SUNW,emlxs@1/fp@0,0/ssd@w50060482d52d43a9,0 (ssd0):
Apr 17 14:40:05 erpdbbox1 Corrupt label; wrong magic number
Apr 17 14:40:05 erpdbbox1 scsi: [ID 107833 kern.warning] WARNING: /ssm@0,0/pci@19,700000/SUNW,emlxs@2/fp@0,0/ssd@w50060482d52d43a8,0 (ssd2):
Apr 17 14:40:05 erpdbbox1 Corrupt label; wrong magic number
The "corrupt label" warning means that the disk doesn't have a Solaris label on it yet.
You must label your Solaris disk to get rid of this message.
Apr 17 14:40:05 erpdbbox1 scsi: [ID 107833 kern.warning] WARNING: /ssm@0,0/pci@19,700000/SUNW,emlxs@1/fp@0,0/ssd@w50060482d52d43a7,0 (ssd1):
Apr 17 14:40:05 erpdbbox1 Corrupt label; wrong magic number
Apr 17 14:40:05 erpdbbox1 scsi: [ID 107833 kern.warning] WARNING: /ssm@0,0/pci@19,700000/SUNW,emlxs@1/fp@0,0/ssd@w50060482d52d43a7,0 (ssd1):
Apr 17 14:40:05 erpdbbox1 Corrupt label; wrong magic number
Apr 17 14:40:05 erpdbbox1 scsi: [ID 107833 kern.warning] WARNING: /ssm@0,0/pci@18,600000/SUNW,emlxs@1/fp@0,0/ssd@w50060482d52d43a9,0 (ssd0):
Apr 17 14:40:05 erpdbbox1 Corrupt label; wrong magic number
Apr 17 14:40:05 erpdbbox1 scsi: [ID 107833 kern.warning] WARNING: /ssm@0,0/pci@18,600000/SUNW,emlxs@1/fp@0,0/ssd@w50060482d52d43a9,0 (ssd0):
Apr 17 14:40:05 erpdbbox1 Corrupt label; wrong magic number
Apr 17 14:40:05 erpdbbox1 scsi: [ID 107833 kern.warning] WARNING: /ssm@0,0/pci@19,700000/SUNW,emlxs@2/fp@0,0/ssd@w50060482d52d43a8,0 (ssd2):
Apr 17 14:40:05 erpdbbox1 Corrupt label; wrong magic number
The "corrupt label" warning means that the disk doesn't have a Solaris label on it yet.
You must label your Solaris disk to get rid of this message.
Categories: APPS Blogs
Command line diagnostic tests for notification mailers
While searching for something else on metalink, my eyes caught the phrase "command-line diagnostic" in the search results. This was present in the readme of ATG_PF.H RUP6 patch:
You can now dedicate a notification mailer to process only instances of a particular message from a particular item type by specifying both the item type and the message name within the correlation ID.You can run command-line diagnostic tests for notification mailers through the new oracle.apps.fnd.wf.mailer.Mailer program.
* Test connectivity to the IMAP mail server.
* Test connectivity to the SMTP mail server.
* Test connectivity to the Web tier, which is required for the notification mailer to generate notifications that include Oracle Application Framework content.
* Check the number of messages in an IMAP folder or the total size of the messages in the folder in bytes, which indicates how much space you can regain by purging messages from the folder.
More details about these tests are given in Metalink Note 332152.1
Java Mailer Diagnostics for HTTP Framework Region:
You need to have minimum ATG CU2 applied:
$AFJVAPRG -classpath $AF_CLASSPATH -Dnid= \
-Ddbcfile= \
[ -Dappuser= \
-Dappresp= \
-Dappid=\
-Dhtp= \
-Durltimeout= (Default 30)]\
[ -DAFLOG_ENABLED=true \
-DAFLOG_FILENAME=test5b.log \
-AFLOG_LEVEL= ] \
oracle.apps.fnd.wf.mailer.Mailer
Note: When specifying -Dhtp=https, the LD_LIBRARY_PATH environment variable should be replaced with $AF_LD_LIBRARY_PATH. Otherwise, "java.lang.UnsatisfiedLinkError: initSSLContextNative" will be encountered.
NON-SSL TEST:
Modify in a text editor to make sure the command is listed on one line as it is one long statement. You need to specify the notification_id (-Dnid) of the failing notification and the dbc filename (-Ddbcfile).
==============Begin garymailertest.sh=================
. APPSORA.env
$AFJVAPRG -classpath /tmp:${AF_CLASSPATH} -Dnid=841969 -Ddbcfile=$FND_TOP/secure/orlncatst-02_vis.dbc -Dappuser=0 -Dappresp=20420 -Dappid=1 -Durltimeout=120 -Dhtp=http -DAFLOG_FILENAME=mailerTest.log -DAFLOG_LEVEL=STATEMENT -DAFLOG_ENABLED=true oracle.apps.fnd.wf.mailer.Mailer
=============End garymailertest.sh=====================
SSL TEST:
Modify in a text editor to make sure the command is listed on one line as it is one long statement. You need to specify the notification_id (-Dnid) of the failing notification and the dbc filename (-Ddbcfile).
==============Begin garymailertestSSL.sh===============
. APPSORA.env
export LD_LIBRARY_PATH=${AF_LD_LIBRARY_PATH}
$AFJVAPRG -classpath $AF_CLASSPATH -Dnid=841969 -Ddbcfile=$FND_TOP/secure/aoldev-pc_vis.dbc -Dappuser=0 -Dappresp=20420 -Dappid=1 -Durltimeout=120 -Dhtp=https -DAFLOG_FILENAME=mailerTest.log -DAFLOG_LEVEL=STATEMENT -DAFLOG_ENABLED=true oracle.apps.fnd.wf.mailer.Mailer
============End garymailertestSSL.sh===============
Java Mailer Diagnostics for HTTP Framework Region Screen Output:
Mailer : oracle.apps.fnd.cp.gsc.Logger.Logger(String, int) : Logging to System.out until necessary parameters are retrieved for Logger to be properly started.
URL {https://aoldev-pc.us.oracle.com:8443/OA_HTML/OA.jsp?page=/oracle/apps/fnd/wf/worklist/webui/NotifMailerPG&WFRegion=NtfDetail&NtfId=841969&dbc=aoldev-pc_vis&OALAF=blaf&OARF=email}
Connection to application server was successful and retrieved
3392 characters plus 4 referenced objects.
Time taken to establish the connection and
obtain the content was 31.627 seconds
Content ID File name and Content Type
========== ==========================
24537094 /OA_HTML/cabo/images/errorl.gif image/gif
5612344 /OA_HTML/cabo/images/cache/cmbte-1.gif image/gif
8344960 /OA_HTML/cabo/images/t.gif image/gif
16625677 /OA_HTML/cabo/images/cache/cmbbn-1.gif image/gif
NOTE: Upload the information echoed to your session and the -DAFLOG_FILENAME=mailerTest.log and test.log which contains quite a bit of Statement log level data.
You can now dedicate a notification mailer to process only instances of a particular message from a particular item type by specifying both the item type and the message name within the correlation ID.You can run command-line diagnostic tests for notification mailers through the new oracle.apps.fnd.wf.mailer.Mailer program.
* Test connectivity to the IMAP mail server.
* Test connectivity to the SMTP mail server.
* Test connectivity to the Web tier, which is required for the notification mailer to generate notifications that include Oracle Application Framework content.
* Check the number of messages in an IMAP folder or the total size of the messages in the folder in bytes, which indicates how much space you can regain by purging messages from the folder.
More details about these tests are given in Metalink Note 332152.1
Java Mailer Diagnostics for HTTP Framework Region:
You need to have minimum ATG CU2 applied:
$AFJVAPRG -classpath $AF_CLASSPATH -Dnid= \
-Ddbcfile= \
[ -Dappuser= \
-Dappresp= \
-Dappid=\
-Dhtp= \
-Durltimeout= (Default 30)]\
[ -DAFLOG_ENABLED=true \
-DAFLOG_FILENAME=test5b.log \
-AFLOG_LEVEL= ] \
oracle.apps.fnd.wf.mailer.Mailer
Note: When specifying -Dhtp=https, the LD_LIBRARY_PATH environment variable should be replaced with $AF_LD_LIBRARY_PATH. Otherwise, "java.lang.UnsatisfiedLinkError: initSSLContextNative" will be encountered.
NON-SSL TEST:
Modify in a text editor to make sure the command is listed on one line as it is one long statement. You need to specify the notification_id (-Dnid) of the failing notification and the dbc filename (-Ddbcfile).
==============Begin garymailertest.sh=================
. APPSORA.env
$AFJVAPRG -classpath /tmp:${AF_CLASSPATH} -Dnid=841969 -Ddbcfile=$FND_TOP/secure/orlncatst-02_vis.dbc -Dappuser=0 -Dappresp=20420 -Dappid=1 -Durltimeout=120 -Dhtp=http -DAFLOG_FILENAME=mailerTest.log -DAFLOG_LEVEL=STATEMENT -DAFLOG_ENABLED=true oracle.apps.fnd.wf.mailer.Mailer
=============End garymailertest.sh=====================
SSL TEST:
Modify in a text editor to make sure the command is listed on one line as it is one long statement. You need to specify the notification_id (-Dnid) of the failing notification and the dbc filename (-Ddbcfile).
==============Begin garymailertestSSL.sh===============
. APPSORA.env
export LD_LIBRARY_PATH=${AF_LD_LIBRARY_PATH}
$AFJVAPRG -classpath $AF_CLASSPATH -Dnid=841969 -Ddbcfile=$FND_TOP/secure/aoldev-pc_vis.dbc -Dappuser=0 -Dappresp=20420 -Dappid=1 -Durltimeout=120 -Dhtp=https -DAFLOG_FILENAME=mailerTest.log -DAFLOG_LEVEL=STATEMENT -DAFLOG_ENABLED=true oracle.apps.fnd.wf.mailer.Mailer
============End garymailertestSSL.sh===============
Java Mailer Diagnostics for HTTP Framework Region Screen Output:
Mailer : oracle.apps.fnd.cp.gsc.Logger.Logger(String, int) : Logging to System.out until necessary parameters are retrieved for Logger to be properly started.
URL {https://aoldev-pc.us.oracle.com:8443/OA_HTML/OA.jsp?page=/oracle/apps/fnd/wf/worklist/webui/NotifMailerPG&WFRegion=NtfDetail&NtfId=841969&dbc=aoldev-pc_vis&OALAF=blaf&OARF=email}
Connection to application server was successful and retrieved
3392 characters plus 4 referenced objects.
Time taken to establish the connection and
obtain the content was 31.627 seconds
Content ID File name and Content Type
========== ==========================
24537094 /OA_HTML/cabo/images/errorl.gif image/gif
5612344 /OA_HTML/cabo/images/cache/cmbte-1.gif image/gif
8344960 /OA_HTML/cabo/images/t.gif image/gif
16625677 /OA_HTML/cabo/images/cache/cmbbn-1.gif image/gif
NOTE: Upload the information echoed to your session and the -DAFLOG_FILENAME=mailerTest.log and test.log which contains quite a bit of Statement log level data.
Categories: APPS Blogs
Mobile patches
There is no separate product called MWA (Mobile Wireless Access) though we have an MWA_TOP. All SRs logged for MWA are assigned to WMS(Warehouse Management System) or INV/RCV. To have a stable mobile server which doesn't crash often, Oracle has recommended that you should be on:
Latest Oracle Inventory & Receiving (PO) Rollup (RUP) Patches:
Rollup Patch
Patch Number
11.5.10 INV/RCV RUP6
6461517
Latest Oracle Oracle Warehouse Management System Rollup (RUP) Patches:
Rollup Patch
Patch Number
11.5.10 WMS RUP4
5855276
Categories: APPS Blogs
April 2008 Critical Patch Update
Refer to Metalink Note 557157.1 for the April 2008 Critical Patch Update.
In nutshell:
Database patch 6864068 for DB 10.2.0.3
No new patches for Application Server, Developer Suite or Jinitiator if you are on Jan 2007 CPU or higher and Jinitiator 1.3.1.29 or Sun JRE
You need to be on ATG RUP5 or 6 as a baseline on top of which you need to apply 4 E-biz patches:
Another interesting fact given is that the last CPU for Oracle 10.2.0.3 will be in Jan 2009.
In nutshell:
Database patch 6864068 for DB 10.2.0.3
No new patches for Application Server, Developer Suite or Jinitiator if you are on Jan 2007 CPU or higher and Jinitiator 1.3.1.29 or Sun JRE
You need to be on ATG RUP5 or 6 as a baseline on top of which you need to apply 4 E-biz patches:
Another interesting fact given is that the last CPU for Oracle 10.2.0.3 will be in Jan 2009.
Categories: APPS Blogs
Exception: Invalid index
Nilesh asked me for help. He was unable to launch forms from self service pages. The status bar would show: Exception invalid index. When I checked the java console, I found these errors:
Loading http://justanexample.com:8000/OA_JAVA/oracle/apps/fnd/jar/fndlist.jar from JAR cache
sun.misc.InvalidJarIndexException: Invalid index
at sun.misc.URLClassPath$JarLoader.getResource(Unknown Source)
at sun.misc.URLClassPath$JarLoader.getResource(Unknown Source)
at sun.misc.URLClassPath.getResource(Unknown Source)
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at sun.applet.AppletClassLoader.findClass(Unknown Source)
at sun.plugin.security.PluginClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.applet.AppletClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
at java.lang.ClassLoader.defineClass0(Native Method)
at java.lang.ClassLoader.defineClass(Unknown Source)
at java.security.SecureClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.access$100(Unknown Source)
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at sun.applet.AppletClassLoader.findClass(Unknown Source)
at sun.plugin.security.PluginClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.applet.AppletClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.applet.AppletClassLoader.loadCode(Unknown Source)
at sun.applet.AppletPanel.createApplet(Unknown Source)
at sun.plugin.AppletViewer.createApplet(Unknown Source)
at sun.applet.AppletPanel.runLoader(Unknown Source)
at sun.applet.AppletPanel.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
This was happening only on Nilesh's laptop. The usual suspect is JAR cache. I got him to delete his JAR cache present in "C:\Documents and Settings\Nilesh\Oracle Jar Cache" subdirectory. The problem was solved after new jar files were downloaded.
Loading http://justanexample.com:8000/OA_JAVA/oracle/apps/fnd/jar/fndlist.jar from JAR cache
sun.misc.InvalidJarIndexException: Invalid index
at sun.misc.URLClassPath$JarLoader.getResource(Unknown Source)
at sun.misc.URLClassPath$JarLoader.getResource(Unknown Source)
at sun.misc.URLClassPath.getResource(Unknown Source)
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at sun.applet.AppletClassLoader.findClass(Unknown Source)
at sun.plugin.security.PluginClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.applet.AppletClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
at java.lang.ClassLoader.defineClass0(Native Method)
at java.lang.ClassLoader.defineClass(Unknown Source)
at java.security.SecureClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.access$100(Unknown Source)
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at sun.applet.AppletClassLoader.findClass(Unknown Source)
at sun.plugin.security.PluginClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.applet.AppletClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.applet.AppletClassLoader.loadCode(Unknown Source)
at sun.applet.AppletPanel.createApplet(Unknown Source)
at sun.plugin.AppletViewer.createApplet(Unknown Source)
at sun.applet.AppletPanel.runLoader(Unknown Source)
at sun.applet.AppletPanel.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
This was happening only on Nilesh's laptop. The usual suspect is JAR cache. I got him to delete his JAR cache present in "C:\Documents and Settings\Nilesh\Oracle Jar Cache" subdirectory. The problem was solved after new jar files were downloaded.
Categories: APPS Blogs