Error running SQL and EXEC commands in parallel

Problem:
i got error while performing patch application with hotpatch option.
metalink said to shutdown application process and re-run the patch but it was not possible.
Solution:
I did

ps -eaf | grep adpatch

there were lots of adpatch session ; i killed them one by one and restarted adpatch it was successful.

UNZIP all shell script

Below tiny script saved a lot of effort in last production cycle.

#to unzip files in a given folder.
FILES="*.zip"
for f in $FILES
do
echo "Processing $f file..."
unzip -o $f
done

Oracle apps JRE plug in update.

download jre plugin from java website and rename it as "j2se16013.exe" {in my case it was update 13 for any other update it should be "j2se160xx.exe" where 'xx' is update number}

uploade "j2se160xx.exe" to web node's $COMMON_TOP/fnd/util/jiit* directory; ftp mode should be bin

Following patch should be applie,
----


5884875
6863618 Mar 28 17:35:27 Mar 28 18:09:22

----------------
=============Patch 5884875===============
cp -r $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class.PRE_BUG5884875
cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class.PRE_BUG5884875
---
cd $ORACLE_HOME/patch/5884875/oracle/forms/engine
cp Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class
cd $ORACLE_HOME/patch/5884875/oracle/forms/handler
cp AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/engine/AlertDialog.class
---
Generate JAR Files using Adadmin [Option 1 and 4]


=============Patch 6863618===============
Apply on both tier using adpatch
Run the txkSetPlugin.sh script from the //fnd/bin/
directory, against the web node of your middle tier, where is the directory
in which you unzipped this patch.

Form 6i patchset 18 update

Patchset 18 update
Plan
We have multinode apps arch, db and concurrent tier is on HP UX and Web and form tier is on Linux X86

I have applied following patches, I am giving timing information as well to make estimation easy.
--------------------------------------------------------------------------
Patch StartTime EndTime
4888294
4948577 Mar 27 20:00:09 Mar 27 21:06:14 - This is the main patch
5713544 Mar 28 04:24:00 Mar 28 04:48:02
4261542 Mar 28 04:48:02 Mar 28 05:08:51
5216496 Mar 28 05:52:45 Mar 28 12:23:41
5753922 Mar 28 12:23:41 Mar 28 12:31:46
6195758 Mar 28 12:31:46 Mar 28 12:46:02
5938515 Mar 28 12:46:02 Mar 28 12:57:27 - only linux
3830807 Mar 28 12:57:27 Mar 28 13:12:14- only linux
5092277 Mar 28 13:12:14 Mar 28 13:37:33
7362389 Mar 28 13:37:33 Mar 28 15:25:38

Some patch do have manual steps they are as below.

==============Patch 4261542==============
cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class.PRE_BUG4261542
cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class.PRE_BUG4261542
cp -r $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class.PRE_BUG4261542
--------
cd $ORACLE_HOME/patch/4261542/oracle/forms/engine;cp Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class
cd $ORACLE_HOME/patch/4261542/oracle/forms/handler;cp AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class

Generate JAR Files using Adadmin [Option 1 and 4]

=============Patch 6195758===============
cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class.PRE_BUG6195758
cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class.PRE_BUG6195758
-------
cd $ORACLE_HOME/patch/6195758/oracle/forms/handler
cp UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class
cp ComponentItem.class $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class

Generate JAR Files using Adadmin [Option 1 and 4]

=============Patch 3830807===============
cd 3830807
chmod u+x patch.sh
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/network/jre11/lib/i686/native_threads
./patch.sh

Relink Applications programs" using Adadmin [Option 2 and 1]

=============Patch 5092277===============
cd $ORACLE_HOME/forms60/java;cp -r oracle/ewt oracle/ewt.pre5092277
--
jar xvf $ORACLE_HOME/patch/5092277/ewt3.jar
rm META-INF/MANIFEST.MF
rmdir META-INF
--
Verify ::::: that the EWT version has been updated
cd $ORACLE_HOME/forms60/java directory;ls -la oracle/ewt | grep 3_4_44

Generate JAR Files using Adadmin [Option 1 and 4]

Varify :::::time stamp it should be current.
ls -la $JAVA_TOP/oracle/apps/fnd/jar/fndewt.jar

=============Patch 7362389===============
cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class.PRE_BUG7362389
cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class.PRE_BUG7362389
cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/FormWindow.class $ORACLE_HOME/forms60/java/oracle/forms/handler/FormWindow.class.PRE_BUG7362389
cp -r $ORACLE_HOME/forms60/java/oracle/forms/ui/VBean.class $ORACLE_HOME/forms60/java/oracle/forms/ui/VBean.class.PRE_BUG7362389
-----
cd $ORACLE_HOME/patch/7362389/oracle/forms/handler
cp UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class
cp ComponentItem.class $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class
cp FormWindow.class $ORACLE_HOME/forms60/java/oracle/forms/handler/FormWindow.class
----
cd $ORACLE_HOME/patch/7362389/oracle/forms/ui
cp VBean.class $ORACLE_HOME/forms60/java/oracle/forms/ui/VBean.class
----
cd $ORACLE_HOME/forms60/java
cp -r oracle/ewt oracle/ewt.PREBUG7362389
jar xvf $ORACLE_HOME/patch/7362389/ewt3.jar
rm META-INF/MANIFEST.MF
rmdir META-INF
----
Verify :::::that the EWT version has been updated
cd $ORACLE_HOME/forms60/java directory;ls -la oracle/ewt | grep 3_4_48

Generate JAR Files using Adadmin [Option 1 and 4]

---------------------------------------------------------------------------

After all above steps ; Run a relink all and make application up.

Shell script to analyze selected tables (in APPS).

Problem:
When we run gather_ schema_states concurrent program. it takes a lot of time and resources and do not provide immediate relive to development instance (as it takes long time to run)

Solution:
I found that analyzing a small number of tables from different schema will improve performance. So I created below shell script. It reads table from a text file stored at the same location and analyze tables. This script can be scheduled in cron tab in unix.


Script:
(1)
#/usr/bin

#This script will read table name from a list file and analyze them.
#diffrent analyze commands can be selected.
#if a table is commentd in the script. it will not be considered.

SCRIPT="`basename $0`"
DT_FMT="%d%m%y-%H:%M"
DATE=`date +${DT_FMT}`
LOG_DIR=${APPLCSF}/${APPLLOG}
LOG_FILE=${LOG_DIR}/${SCRIPT}_${DATE}.log
ORACLE_SID=
TBL_LST=table_list.lst

DB_CHK() {

##function to check if oracle is up.

oracle_up=`ps -eaf | grep -w "ora_pmon_t201" |grep -v grep |wc -l`
oracle_num=`expr ${oracle_up}`

if [ $oracle_num -lt 0 ] ; then
echo "Oracle instance is NOT up. Please start Oracle and retry." | tee -a ${LOG_FILE}
exit 1
fi

echo "OK \t Oracle is up SID:${ORACLE_SID}" | tee -a ${LOG_FILE}

}

if [ ! -f ${TBL_LST} ]; then
echo "Failed: ${DATE} ${SCRIPT}: ${TBL_LST} file cannot be read" | tee -a ${LOG_FILE}
exit 1
else
if [ -f ANA_TB.sql ]; then
rm ANA_TB.sql ANA_TB_COL.sql ANA_TB_IND.sql ANA_TB_IND_COL.sql
touch ANA_TB.sql ANA_TB_COL.sql ANA_TB_IND.sql ANA_TB_IND_COL.sql
fi
fi

RUN_SQL() {
DB_CHK
typeset SCRIPT1=$1
sqlplus -s <apps/*****
@${SCRIPT1}
EOF
}
rm ANA_TB_COL.sql ANA_TB_IND.sql ANA_TB_IND_COL.sql ANA_TB.sql

for Tbl in `cat ./${TBL_LST} |grep -v \# | grep -v \*`
do
echo $Tbl

echo "PROMPT ANALYZE TABLE $Tbl COMPUTE STATISTICS;" >> ANA_TB.sql
echo "ANALYZE TABLE $Tbl COMPUTE STATISTICS;\n" >> ANA_TB.sql

echo "PROMPT ANALYZE TABLE $Tbl COMPUTE STATISTICS FOR ALL COLUMNS;" >> ANA_TB_COL.sql
echo "ANALYZE TABLE $Tbl COMPUTE STATISTICS FOR ALL COLUMNS;\n" >> ANA_TB_COL.sql

echo "PROMPT ANALYZE TABLE $Tbl COMPUTE STATISTICS FOR ALL INDEXES;" >> ANA_TB_IND.sql
echo "ANALYZE TABLE $Tbl COMPUTE STATISTICS FOR ALL INDEXES;\n" >> ANA_TB_IND.sql

echo "PROMPT ANALYZE TABLE $Tbl COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;" >> ANA_TB_IND_COL.sql
echo "ANALYZE TABLE $Tbl COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;\n" >> ANA_TB_IND_COL.sql

done

RUN_SQL ANA_TB.sql
#RUN_SQL ANA_TB_COL.sql
#RUN_SQL ANA_TB_IND.sql
#RUN_SQL ANA_TB_IND_COL.sql
#####################################################################

(2) LIST

#this is list of table
#if table name is preceded with # or * it will not be considered.

Emp
Dept
#emp_tmp

Workflow problem 01.

Problem - A custom oracle workflow which use to take 15 min to complete; started giving ‘Page not found’ while end-user try to expedite it.

Resolution:-

It took me long time to investigate as at system level every thing was fine and other workflow were completing successfully with in time. Looking at a recent code change in the workflow, I even got old code deployed and retested but problem remain the same.

Having exhausted all the option I thought of putting a trace on oracle session which were running oracle workflow code.

I found out SID of workflow using OEM and fed it to below pl/sql…

-----------------------
DECLARE

ln_high_val NUMBER;

ln_max_val NUMBER;

ln_value NUMBER;

ln_sid NUMBER;

ln_serial NUMBER;

lv_sqltxt VARCHAR2(2000);

BEGIN

SELECT vs.SID INTO ln_sid
FROM fnd_concurrent_requests fcr, v$session vs
WHERE fcr.request_id = &Request_id
AND vs.process = fcr.os_process_id
AND fcr.oracle_session_id = vs.audsid;

dbms_output.put_line (ln_sid);

for i in 1..10000 loop

dbms_lock.sleep(2);


SELECT a.VALUE,q.sql_text,s.serial#
INTO ln_value,lv_sqltxt,ln_serial
FROM v$sesstat a, v$statname b, v$session s, v$sqlarea q
WHERE a.statistic# = b.statistic#
AND s.SID = a.SID
AND b.NAME = 'opened cursors current'
AND s.SID = ln_sid
AND s.username = 'APPS'
AND q.hash_value = s.sql_hash_value
AND s.sql_address = q.address;

dbms_output.put_line (ln_value);

INSERT INTO xxrpa.open_cursor_session
VALUES (ln_value, ln_sid,ln_serial,lv_sqltxt);


SELECT MAX (a.VALUE), p.VALUE
INTO ln_high_val, ln_max_val
FROM v$sesstat a, v$statname b, v$parameter p
WHERE a.statistic# = b.statistic#
AND b.NAME = 'opened cursors current'
AND p.NAME = 'open_cursors'
GROUP BY p.VALUE;

INSERT INTO xxrpa.open_cursor
VALUES (ln_high_val, ln_max_val);

COMMIT ;

END LOOP;
END;
/
---------------------------------

To my surprise workflow was running infinitely even after page not found at work flow status monitor, which revels that workflow status monitor was just showing a time-out.

Afterwards a deep analysis of system data told us that ; somebody have changed the flag status in a table and it was the cause wfl was fetching all the parties rather then one. ..

daily oracle: Problem of Comparing FMB (Ora Apps)

daily oracle: Problem of Comparing FMB (Ora Apps)

daily oracle: Simple Concurrent Manger Issue

daily oracle: Simple Concurrent Manger Issue

STEPS FOR UNLOCKING ORACLE PORTAL USER

Error:You have been locked out from this computer. Please wait for the lockout duration to pass before trying again. (WWC-41656)

1. Login to the required server

2. su – a

3. cd $IAS_HOME

4. cd portal30/admin/plsql/sso

5. sqlplus portal30_sso/portal30_sso

6. run ssounlck.sql

7. Provide the user name of the locked user a/c

STEPS FOR CLEARING CACHE (Oracle Apps)

Clearing Cache for Oracle Apps
a) Login to required server hosting Oracle Apps using a
b) cd $OA_HTML
c) Move the directory _pages
mv _pages _pages

Note: Actual path: /u01/RPAD17/prodcomn/html

Clearing Cache for 9iAS
a) Login to required server running 9iAS
b) cd $ORACLE_HOME/j2ee//application-deployments/echannel/echannel/persistence
c) Move the directory _pages
mv _pages _pages

Note: Actual path: /u01/iasIDC/j2ee//application-deployments/echannel/echannel/persistence


Q. Where is HTML Cache stored in Oracle Apps Server ?
Oracle HTML Cache is available at $COMMON_TOP/_pages for some previous versions you might find it in $OA_HTML/_pages
Q. Where is plssql cache stored in Oracle Apps ?
Usually two type of cache session & plssql stored under $IAS_ORACLE_HOME/Apache/modplsql/cache

Simple Concurrent Manger Issue

Below are few steps to restart concurrent manager; in order to trouble shoot simple issue in a unix based env.

1. login to form
http://:/dev60cgi/f60cgi
as sysadmin

2. select system administrator responsibility

3. concurrent -> manager -> Administer
will pop up the window with the status of the manager.

4. check for the standard manager
whether actual = target
1) if target=0, thats mean standard manager is down.
2) if target=some number, thats mean standard manager is up

whether actual <> target

if actual = 0 and target > 0

standard manager has not come up properly.
for cross verifying it. submit a reuqest for "active user". it should get completed within a min or two. wait for few min and refresh the request. if its still penfding or showning inactive manager, check the concurrent -> manager -> Administer window against the standard manager , in pending request active user req will be there.

now open an sqlplus session and execute the script ccm.sql and cm_stat.sql

statistics can be gathered for the concurrent process and FND tables.

5. now login to unix prompt as application user( a like a) and move to directory $SCRIPTS_TOP

execute adcmctl.sh stop apps/passwd

check for the concurrent processes
ps -ef grep FND grep
should continously go down.

6. meanwhile keep on checking concurrent -> manager -> Administer window
All the entry target/actual should go down to 0. pending can be there with the exception for the internal manager.

7. wait for some time. (20 min)
if its not coming down to 0 even waiting for 5 mins or so. First cancel the request for the shutdown.
and kill the process on unix box.

ps -ef grep -v grep grep FND grep awk '{print $2}' xargs kill -9

8. from the sqlplus session cm_stat.sql , should get 0 actual and 0 target for all the managers.

also from concurrent -> manager -> Administer window
All the entries should be 0. Pending can be there with the exception for the internal manager.

9. Now from sqlplus session:
Run cmclean.sql
it'll ask for typing dual. Type dual and it'll start updating the corrupted FND tables entries.
Once the script is through type commit

10. and restart the concurrent manager from the unix prompt.
adcmctl.sh start apps/passwd
check whether the no of FND process is going up by command below:

ps -ef grep FND grep RPAD11

Also from concurrent -> manager -> Administer window refresh the page.

This time no of standard manager should come up to the number mentioned in target.
if not wait for another few min and refresh the administer page.
issue should get resolved.

Note:
Concurrent Manager Log and Out files are generated at following location:

LOG file: $APPLCSF/$APPLLOG
OUT file: $APPLCSF/$APPLOUT

e.g. typical locations may be:
/u01//sqlcom/log/_
/u01//sqlcom/out/_