Thursday, December 10, 2009

Understanding Oracle Trace Utility - Oracle Database 10g

Overview:



Some times the situation demands more information about an event. Example if we take a case of some error coming in our database because of some application. In this case its the job of developer to drill down into the application and get the exact queries those are failing. I have seen many situations where we get ORA-0600 error or ORA-07445 errors. In such situation, if one wants to debug the issue, then he need to know the exact query which is failing so that he should be able to reproduce the issue from command prompt and another thing is when he can reproduce the issue form command prompt, then he can get detailed level of information about the running query by setting the tracing levels.

This post is all about setting the tracing at session level (either own session or some other session) and getting detailed information about the query. I will show you, what are the different levels of tracing events that are present and what are the various levels of tracing.



I will discuss 2 types of tracing here



1. SQL tracing

2. Event based tracing



SQL Tracing:



Session Level:



One of the simplest method to trace SQL is to use “alter session set SQL_TRACE=true” command and oracle will generate the trace file for the SQL statment that you run in that session.



System Level:



Also you can set the SQL trace at system level using “alter system set SQL_TRACE=true” command. But be careful about this, because this is going to generate huge trace file and especially when the number of users are large, this will degrade database performance.



The pre-reqs for enabling the SQL trace either at session level or at system level is ‘TIMED_STATISTICS’ parameter should be set to “TRUE” and “STATISTICS_LEVEL” should be set minimum to “TYPICAL”. It can be set to “ALL” as well, which is the higest level of tracing information. While setting the statistics level to ALL, you have to be careful.



Important *** If possible do not set the statistics level to all at system level, set only at session level, else the database performance will be impacted as database has to produce a very detail level of tracing. Also do not set SQL trace at system level, set only at session level. Setting SQL trace at system level will generate huge trace file and database performance will degrade.



For other sessions:



You can also set SQL_TRACE for some other session as well using DBMS_SYSTEM package.



procedure DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION will enable you to do this.



login as sysdba and execute



exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(,,true) - for enabling the tracing



exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(,,false) - for disabling the tracing



Event Based Tracing



Session Level:



Event level tracing is a way to create detail information about some kind of event. Not what exactly we mean by event.



“An event is simply a flag to the Oracle kernel to tell it to emit some trace messages or to add some additional processing or to activate some new functionality.”



Some events are used by support analysts and developers to force certain conditions to occur for testing purposes.



For example the event 10046 will enable SQL statement timings. So setting this event tells oracle kernel to through timing information about SQLs in its trace files. Like wise we have several events.



The details of all the events and actions are provided in $ORACLE_HOME/rdbms/mesg/oraus.msg file.



For each of these events mentioned in oraus.msg file, we can set the level. These are the level of information that kernel should put in the trace files. Below are the various trace levels present in Oracle tracing utility.



* Level 0 = No statistics generated

* Level 1 = standard trace output including parsing, executes and fetches plus more.

* Level 2 = Same as level 1.

* Level 4 = Same as level 1 but includes bind information

* Level 8 = Same as level 1 but includes wait’s information

* Level 12 = Same as level 1 but includes binds and waits



Also setting any event other that trace (10046) should not be done without the guidance of Oracle support.



There are many ways in which we can enable event level tracing. Below are some of the methods to do that.



Method 1: Setting the trace for event 10046 level 8



It is better to make a new connection before you start a trace each time, if not information will be appended if any trace has already been started.



alter session set max_dump_file_size=unlimited;

alter session SET timed_statistics = true;

alter session set STATISTICS_LEVEL = ALL ;



We should be able to distinctly identify our trace file from other trace files. For that we can set tracefile_identifier parameter.



Example:



alter session set tracefile_identifier = index_trace;



So setting this will set the name of our trace files as %index_trace.trc



A simple example of analyzing the index by setting the trace event 10236 is shown below. Event no 10236 is - dump redo on object no. or block type mismatch errors 1410/8103.



SQL> alter session set max_dump_file_size=unlimited;



Session altered.



SQL> alter session SET timed_statistics = true;



Session altered.



SQL> alter session set STATISTICS_LEVEL = ALL ;



Session altered.



SQL> alter session set tracefile_identifier = index_trace;



Session altered.



SQL> alter session set events ‘10236 trace name context forever, level 8′;



Session altered.



SQL> analyze index msc.test_idx validate structure online;

analyze index msc.test_idx validate structure online

*

ERROR at line 1:

ORA-01410: invalid ROWID



This is to check some kind of corruption.



SQL> ALTER SESSION SET EVENTS ‘10236 trace name context off’;



Session altered.



Method 2: Using DBMS_SYSTEM package.



Another way to set the event trace is to use the package DBMS_SYSTEM as given below.



exec DBMS_SYSTEM.SET_EV(SID,SESSION#,EVENT#,EVENT LEVEL,”);



Example: exec DBMS_SYSTEM.SET_EV(10,20,10046,8,”);



This will set the tracing for any session.



Method 3: Using DBMS_SUPPORT package.



Another method to set tracing is to use DBMS_SUPPORT package. This package is not installed by default and is in fact undocumented and indeed on some platforms and versions its not even shipped and you will need to talk to Oracle support and get it from metalink.



Installaing the package first:



SQL> @?/rdbms/admin/dbmssupp.sql



Package created.



Package body created.

SQL>



Using DBMS_SUPPORT package for tracing.



exec DBMS_SUPPORT.START_TRACE_IN_SESSION(SID,SERIAL#,WAITS,BINDS);



Example: exec dbms_support.start_trace_in_session(10,20,waits=>true,binds=>false);



To stop tracing: exec dbms_support.stop_trace_in_session(10,20);



Start tracing in your own session:



exec dbms_support.start_trace(waits=>true,binds=>false);



Stop tracing in your session: exec dbms_support.stop_trace;



System Level:



If you want to set the event level tracing at system level, then we can either use alter system instead of alter session or we can use init.ora parameter to set the events.



Method 1: Using Alter System



SQL> alter system set events ‘10046 trace name context forever, level 1′;



System altered.



To turn off the tracing at system level again we can use the below command.



SQL> alter system set events ‘10046 trace name context off’;



System altered.



Method 2: Using init.ora parameter



init.ora parameter name is “event” and it can be set as given below. But be careful while setting up this as this will generate huge trace files.



event = “10046 trace name context forever, level 12″

Identifying Locks on Objects using V$LOCKS

Ever wondered when a user comes to you saying that he/she cannot run a DML statement, which one should be able to run with in a second.

We suspect 2 reason for the same

1) Database is terribely slow (Which cannot be the case for processing a simple update)

2) Some one is holding an exclusive lock on object which user is trying to update (quite possible).



There can be many more reasons we can find upon further investigations. In this post we will see how to investigate reason 2 - If some other user is holding lock on the object which this user is trying to modify.



lets take a simple scenario.



session 1:



SQL> create table test (col1 number, col2 varchar2(5));



Table created.



SQL> insert into test values (1,’a');



1 row created.



SQL> insert into test values (2,’b');



1 row created.



SQL> commit;



Commit complete.



SQL> select * from test for update;



COL1 COL2

———- —–

1 a

2 b



Session 2:



SQL> update test set col2=’a’ where col1 = 1;



The above session 2 will hang !!!

view plaincopy to clipboardprint



1. SQL> select * from v$lock;

2.

3. ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

4. ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

5. 00000003BFD5D868 00000003BFD5D888 54 CF 0 0 2 0 669613 0

6. 00000003BFD5D900 00000003BFD5D920 54 XR 4 0 1 0 669625 0

7. 00000003BFD5DA30 00000003BFD5DA50 54 RS 25 1 2 0 669613 0

8. 00000003BFD5DAC8 00000003BFD5DAE8 35 TX 196652 882 0 6 344 0

9. 00000003BFD5DB60 00000003BFD5DB80 53 TS 3 1 3 0 669599 0

10. 00000003BFD5DBF8 00000003BFD5DC18 55 RT 1 0 6 0 669613 0

11. 00000003BFD5DDC0 00000003BFD5DDE0 57 MR 1 0 4 0 669601 0

12. 00000003BFD5DE58 00000003BFD5DE78 57 MR 2 0 4 0 669601 0

13. 00000003BFD5DEF0 00000003BFD5DF10 57 MR 3 0 4 0 669601 0

14. 00000003BFD5DF88 00000003BFD5DFA8 57 MR 4 0 4 0 669601 0

15. 00000003BFD5E020 00000003BFD5E040 57 PW 1 0 3 0 669599 0

16.

17. ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

18. ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

19. 00000003BFD5E3C8 00000003BFD5E3E8 57 MR 81 0 4 0 669593 0

20. 00000003BE50B9B8 00000003BE50B9E0 49 TM 21837 0 3 0 374 0

21. 00000003BE50BAB8 00000003BE50BAE0 35 TM 21837 0 3 0 344 0

22. 00000003BDC81138 00000003BDC812C0 49 TX 196652 882 6 0 374 1

23.

24. 15 rows selected.



SQL> select * from v$lock;



ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

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

00000003BFD5D868 00000003BFD5D888 54 CF 0 0 2 0 669613 0

00000003BFD5D900 00000003BFD5D920 54 XR 4 0 1 0 669625 0

00000003BFD5DA30 00000003BFD5DA50 54 RS 25 1 2 0 669613 0

00000003BFD5DAC8 00000003BFD5DAE8 35 TX 196652 882 0 6 344 0

00000003BFD5DB60 00000003BFD5DB80 53 TS 3 1 3 0 669599 0

00000003BFD5DBF8 00000003BFD5DC18 55 RT 1 0 6 0 669613 0

00000003BFD5DDC0 00000003BFD5DDE0 57 MR 1 0 4 0 669601 0

00000003BFD5DE58 00000003BFD5DE78 57 MR 2 0 4 0 669601 0

00000003BFD5DEF0 00000003BFD5DF10 57 MR 3 0 4 0 669601 0

00000003BFD5DF88 00000003BFD5DFA8 57 MR 4 0 4 0 669601 0

00000003BFD5E020 00000003BFD5E040 57 PW 1 0 3 0 669599 0



ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

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

00000003BFD5E3C8 00000003BFD5E3E8 57 MR 81 0 4 0 669593 0

00000003BE50B9B8 00000003BE50B9E0 49 TM 21837 0 3 0 374 0

00000003BE50BAB8 00000003BE50BAE0 35 TM 21837 0 3 0 344 0

00000003BDC81138 00000003BDC812C0 49 TX 196652 882 6 0 374 1



15 rows selected.



If a session is blocking another session, you will see block = 1 for that session. So in out case SID=49 is blocking some other session. We can also find the session which got blocked because of SID=49.

There are 2 columns ID1 and ID2. The values of ID1 and ID2 for SID=49 will match with some other SID in v$lock table. If you see carefully in our case its matching with SID=35. So SID 35 is the session which got blocked because of SID=49. Also the session which gets blocked will not be able to get its request processed. So you will see REQUEST column will have a value > 0.

So from the above analysis we can say that SID 49 is blocking SID 35.



We can directly write a query which will give the required output.



SQL> select a.SID “Blocking Session”, b.SID “Blocked Session”

2 from v$lock a, v$lock b

3 where a.SID != b.SID

4 and a.ID1 = b.ID1

5 and a.ID2 = b.ID2

6 and b.request > 0

7 and a.block = 1;



Blocking Session Blocked Session

—————- —————

49 35



Lets understand rest of the columns in v$lock tables here.



ID1 and ID2 -> There represents the rollback segment and transaction table entries for that transaction. So when session 1 acquired the lock it got rollback segment and transaction table entry. When another session requested the same block, Oracle tried to generate a CR “Consistent read” image for the same by applying the rollback segment. But since there was exclusive lock it was not able to proceed. Unless first session relievs the lock, second session cannot proceed. So for second session its showing the value of ID1 and ID2 same as session 1 as session 2 was using the same values from rollback segment to make a CR copy.



TYPE -> This column gives the type of lock obtained on that table in which the data was locked. For more information on lock type check defination of v$lock table in Oracle references. For a session to change the data in a table it has to acquire a TX lock. This is the transaction enqueue lock.



LMODE -> This is the mode of lock. The is having values from 0 to 6, 6 being the most restrictive and 0 being least restrictive. When a session obtains lock in mode 6 that mean it has obtained exclusive lock and no other session is allowed to update the data. So for SID=49 we can see here that lock mode is exclusive (6). For more information on lock mode, you can check the v$lock table definition in Oracle references



REQUEST -> This column represent the lock mode requested by a blocking session. The value in this column gets updated only when the session is blocked. For example in our case SID=35 is being blocked so LMODE column shows a value of 0, but REQUEST column shows a value of 6. That means that SID 35 has requested lock mode 6 which has not yet assigned to it.



Some time if a session request for exclusive lock (mode 6), it might not get it because there are no Interested Transaction List (ITL) available in the block in which a user want to change data. For this user session will wait on mode 4 (shared mode) for some time and as soon as ITL is available, it will obtain the lock in mode 6.



Also in the above rows, you can see that for SID 49 and 35, there are 2 more rows and the TYPE column shows ‘TM’. There are the DML level enqueues and are acquired in lock mode 3 (Shared Row Exclusive). The lock will prevent any DDL activity on this table.

We can find the object name by getting the ID1 column value from these rows containing TM lock. 21837 in our case.



SQL> select object_name from dba_objects where object_id=21837;



OBJECT_NAME

————–

TEST



We can even get the row which is being blocked by transaction using v$session.



SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#

2 from v$session where sid=35;



ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#

————- ————– ————— ————-

21837 1 45082 0



The above 4 components are the components of ROWID and we can generate ROWID number from there components using DBMS_ROWID package.



SQL> select dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)

2 from v$session where sid=35;



DBMS_ROWID.ROWID_C

——————

AAAFVNAABAAALAaAAA



Now we can check if this was the row which blocking session was trying to update



SQL> select * from test where rowid = ‘AAAFVNAABAAALAaAAA’;



COL1 COL2

———- —–

1 a



this was the row blocking session was trying to update.

Delete Files Older Than x Days on Linux

Delete Files Older Than x Days on Linux



find /path/to/files* -mtime +5 -exec rm {} \;



find . -name "*.trc" -mtime +30 -exec rm {} \;

find . -name "*.trw" -mtime +30 -exec rm {} \;





Just be aware that this will also remove any old files that exist in subdirectories of $FTPDIR as well, not just $FTPDIR itself, which may be an undesirable effect.

Licence Product from backend

sqlplus apps/ @$AD_TOP/sql/adlicmgr.sql apps/ license.log LICENSE_PROD 160 I

Session Information SQL

Session details when SPID is known
=============================
select a.machine,a.process,a.last_call_et,a.module,to_char(a.logon_time,'dd-mm-yyyy hh24:mi:ss'),a.sid,a.serial#,status,
substr(osuser,1,20) osuser,
b.program,
schemaname, pid, spid
from v$session a, v$process b
where b.spid in( )
and a.paddr = b.addr;


Session details when CLIENT PROCESS is known
========================================
select a.machine,a.process,a.last_call_et,a.module,to_char(a.logon_time,'dd-mm-yyyy hh24:mi:ss'),a.sid,a.serial#,status,
substr(osuser,1,20) osuser,
b.program,
schemaname, pid, spid
from v$session a, v$process b
where a.process like '%%'
and a.paddr = b.addr;




Forms stuck session
================

set lines 150

col MACHINE format a20


select s.sid,s.serial#, s.action,s.process clientprocess,s.machine, a.seconds_in_wait
from v$session_wait a, v$session s, v$process p
where a.sid=s.sid
and s.paddr = p.addr
and a.seconds_in_wait > 3000
and a.event = 'SQL*Net message from client'
and s.status = 'INACTIVE'
and s.action like 'FRM%'
order by a.SECONDS_IN_WAIT;

Wait Event details when SPID is known
=================================

select a.sid,a.serial#,a.status,(sysdate-logon_time)*24*60 MINSDURATN,b.event,b.p1,b.p2,b.p3
from v$session a ,v$session_wait b
where
a.spid= and
a.sid=b.sid ;



Find OS process Id for a session
select spid, pid
from v$process
where addr = (select paddr from v$session where sid = );

Find the most recent sql by a session
select
sid, username, command, lockwait, status,
osuser, sql_text
from v$session s, v$sqlarea q
where s.sql_hash_value = q.hash_value
and s.sql_address = q.address
and s.sid = &usrsid;

Who is blocking whome
select
(select username from v$session where sid=a.sid) blocker,
a.sid,’ is blocking ‘,
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;

Identify corrupted objects using file# and block#

select segment_name from dba_segments where file_id= and between BLOCK_ID AND BLOCK_ID+BLOCKS-1;

Session information and tracing

 

Trace My session ID

select sys_context(’USERENV’,'SID’) from dual;
Oracle Session information user
set heading on;

select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type=’USER’
order by spid;

SQL statement executed by a session

select sesion.sid,
       sesion.username,
       optimizer_mode,
       hash_value,
       address,
       cpu_time,
       elapsed_time,
       sql_text
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username is not null;

Find Objects locked by a session and the sql locking it

select c.owner,c.object_name,c.object_type,
b.sid,b.serial#,b.status,b.osuser,b.machine,s.sql_text from v$locked_object a ,v$session b,dba_objects c,v$sqltext_with_newlines s
where b.sid = a.session_id
and a.object_id = c.object_id and b.sql_address=s.address and b.sql_hash_value=s.hash_value;

Which session/sql is blocking what session/sql

select s1.sid||’,'||s1.serial# Blocker ,sql1.sql_text Blocking_sql,
s2.sid||’,'||s2.serial# Waiting ,sql2.sql_text Waiting_sql
from v$lock l1, v$session s1, v$lock l2, v$session s2,
v$sqltext_with_newlines sql1,v$sqltext_with_newlines sql2
where s1.sid=l1.sid and s2.sid=l2.sid
and sql1.hash_value=s1.sql_hash_value
and sql1.address=s1.sql_address
and sql2.hash_value=s2.sql_hash_value
and sql2.address=s2.sql_address
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;

Enable sql trace with bind before executing a sql

ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 4′;

Enable sql trace with binds for a running session

alter session set TRACEFILE_IDENTIFIER = ‘APPS_MYTAG’;

EXEC dbms_monitor.session_trace_enable (sid, serial#, waits=>FALSE, binds=>TRUE);
Other options

 exec dbms_system.set_int_param_in_session(&&sid,&&serial,’MAX_DUMP_FILE_SIZE’,2147483647);

exec sys.dbms_system.set_BOOL_param_in_session(94,239,’sql_trace’,true);
exec dbms_system.set_ev(94,239,EV=>10046,LE=>8,NM=>”);

To disable trace

exec dbms_system.set_ev(94,239,EV=>10046,LE=>8,NM=>”);

10 G onwards
SELECT   b.name, b.value_string, sq.sql_text
FROM     v$sql_bind_capture b, v$sql sq, v$session s
WHERE    sq.sql_id = b.sql_id
AND      sq.address = b.address
AND      sq.child_address = b.child_address
AND      sq.parsing_user_id = s.user#
AND      s.username = USER
ORDER BY sq.sql_id, sq.address, sq.child_address, b.position;



Forms stuck session
================

set lines 150

col MACHINE format a20


select s.sid,s.serial#, s.action,s.process clientprocess,s.machine, a.seconds_in_wait
from v$session_wait a, v$session s, v$process p
where a.sid=s.sid
and s.paddr = p.addr
and a.seconds_in_wait > 3000
and a.event = 'SQL*Net message from client'
and s.status = 'INACTIVE'
and s.action like 'FRM%'
order by a.SECONDS_IN_WAIT;

FNDLOAD

In this article I wish to give real working examples of Oracle's FNDLOAD utility.

Besides that, I have included some useful notes on FNDLOAD utility



I have used FNDLOAD successfully in past for several different entities/data types within Oracle 11i for almost all my previous clients, ever since this utility became available.

Some of the examples in this FNDLOAD article include:-

FNDLOAD to transfer Request Groups

FNDLOAD for moving Concurrent Programs

FNDLOAD to download and upload Forms Personalizations ( or Personalisations depending on where you are located )



To FNDLOAD Web ADI, visit the link Web ADI FNDLOAD



Use FNDLOAD for transferring value set definitions.

-->Please note that when transferring Key Flex Fields and Descriptive flex fields the respective value sets against each segment will be extracted and loaded automatically.



Also, FNDLOAD can be used to migrate Key FlexFields, Descriptive Flexfields, Responsibilities and almost every other FND entity.



Please note that the text written down here could get wrapped in the browser.

Hence you may have to use \ to continue the single line command on Unix, in case you find the lines wrapping

In my case I am ensuring that $CLIENT_APPS_PWD has the apps password before running the scripts





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





##To FNDLOAD Request groups

FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_MY_REPORT_GROUP_NAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME="XX_MY_REPORT_GROUP_NAME" APPLICATION_SHORT_NAME="XXGMS"

##Note that

##---------

## <> will be your Application Shortname where request group is registered

## XX_MY_REPORT_GROUP_NAME

Will be the name of your request group

## ##To upload this Request Group in other environment after having transferred the ldt file



FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct





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





##To FNDLOAD Concurrent Programs

FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt PROGRAM APPLICATION_SHORT_NAME="XXGMS" CONCURRENT_PROGRAM_NAME="XX_CUSTOM_ORACLE_INTERFACE_PROG"

##Note that

##---------

## XXGMS will be your custom GMS Application Shortname where concurrent program is registered

## XX_CUSTOM_ORACLE_INTERFACE_PROG

Will be the name of your request group

## XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt is the file where concurrent program definition will be extracted

## ##To upload

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt





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





##To FNDLOAD Oracle Descriptive Flexfields

$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_PO_REQ_HEADERS_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=PO DESCRIPTIVE_FLEXFIELD_NAME='PO_REQUISITION_HEADERS'

##Note that

##---------

## PO is the Application Shortname against which descriptive flexfield against PO Headers is registered

## PO_REQUISITION_HEADERS

is the name of Descriptive Flexfield against PO Requisition Headers

## Use the SQL below to find the name of DFF, rather than logging into the screen (ooops via jinitiator)

########----->SELECT

########----->application_id, DESCRIPTIVE_FLEXFIELD_NAME, application_table_name

########----->FROM

########-----> fnd_descriptive_flexs_vl

########----->WHERE

########-----> APPLICATION_TABLE_NAME like '%'

upper('&tab_name')

'%'

########----->ORDER BY APPLICATION_TABLE_NAME

########----->/

## To upload into another environment

$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_PO_REQ_HEADERS_DFF.ldt



## OK another example for DFF against FND_LOOKUPS

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_FND_COMMON_LOOKUPS_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=FND DESCRIPTIVE_FLEXFIELD_NAME='FND_COMMON_LOOKUPS'

## OK another example for DFF against Project Accounting Expenditure Types

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_PA_EXPENDITURE_TYPES_DESC_FLEX_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=PA DESCRIPTIVE_FLEXFIELD_NAME='PA_EXPENDITURE_TYPES_DESC_FLEX'







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





##To FNDLOAD Oracle Menus

$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct ICX_POR_SSP_HOME.ldt MENU MENU_NAME="ICX_POR_SSP_HOME"

##Note that

##---------

## Oracle Menus are not attached to applications. Hence no need to include application short name

## ICX_POR_SSP_HOME is the menu name. This can be validated via below SQL

## select user_menu_name from fnd_menus_vl where menu_name = 'ICX_POR_SSP_HOME' ;

## Also note that we do not pass in the User_menu_name in this example

## OK, now to upload this file

$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct ICX_POR_SSP_HOME.ldt





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



## Well, now for FND Messages to download a single message

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct \

XX_ICX_POR_LIFECYCLE_PAY_TIP.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='ICX' MESSAGE_NAME=XX_ICX_POR_LIFECYCLE_PAY_TIP



## Or you may as well download all the messages within an application

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct \

XX_ALL_GMS_MESSAGES_00.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='XXGMS'



## now to upload using FNDLOAD

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_ICX_POR_LIFECYCLE_PAY_TIP.ldt





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





## Now it's the turn of Lookup values. Again, its not a rocket science

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD aflvmlu.lct XX_TRX_BATCH_STATUS.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME ='XXGMS' LOOKUP_TYPE="XX_TRX_BATCH_STATUS"

## Note that

## XX_TRX_BATCH_STATUS is the name of FND Lookup Type in this example

## This will download all the lookup codes within the defined lookup

## To upload

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD aflvmlu.lct XX_TRX_BATCH_STATUS.ldt



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



## You can also move the User definitions from FND_USER

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct ./XX_FND_USER_PASSI.ldt FND_USER USER_NAME='ANILPASSI'

#Do not worry about your password being extracted, it will be encrypted as below in ldt file

#BEGIN FND_USER "ANILPASSI"

# OWNER = "PASSIA"

# LAST_UPDATE_DATE = "2005/10/19"

# ENCRYPTED_USER_PASSWORD = "ZGE45A8A9BE5CF4339596C625B99CAEDF136C34FEA244DC7A"

# SESSION_NUMBER = "0"

To upload the FND_USER using FNDLOAD command use

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct ./XX_FND_USER_PASSI.ldt

Notes for using FNDLOAD against FND_USER:-

1. After uploading using FNDLOAD, user will be promoted to change their password again during their next signon attempt.

2. All the responsibilities will be extracted by FNDLOAD alongwith User Definition in FND_USER

3. In the Target Environment , make sure that you have done FNDLOAD for new responsibilities prior to running FNDLOAD on users.



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





## Now lets have a look at the profile option using oracle's FNDLOAD

FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct POR_ENABLE_REQ_HEADER_CUST.ldt PROFILE PROFILE_NAME="POR_ENABLE_REQ_HEADER_CUST" APPLICATION_SHORT_NAME="ICX"

## Note that

## POR_ENABLE_REQ_HEADER_CUST is the short name of profile option

## We aren't passing the user profile option name in this case. Validate using ...

########----->select application_id, PROFILE_OPTION_NAME

'==>'

profile_option_id

'==>'



########----->USER_PROFILE_OPTION_NAME

########----->from FND_PROFILE_OPTIONS_VL

########----->where PROFILE_OPTION_NAME like '%'

upper('&profile_option_name')

'%'

########----->order by PROFILE_OPTION_NAME

########----->/

## Now to upload

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct POR_ENABLE_REQ_HEADER_CUST.ldt





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





## Now for the request sets that contain the stages and links for underlying concurrent programs

## For this you will be firstly required to download the request set definition.

## Next you will be required to download the Sets Linkage definition

## Well, lets be clear here, the above sequence is more important while uploading

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET.ldt REQ_SET REQUEST_SET_NAME="FNDRSSUB4610101_Will_look_like_this"

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET_LINK.ldt REQ_SET_LINKS REQUEST_SET_NAME="FNDRSSUB4610101_Will_look_like_this"

## Note that FNDRSSUB4610101 can be found by doing an examine on the

########----->select request_set_name from fnd_request_sets_vl

########----->where user_request_set_name = 'User visible name for the request set here'

## Now for uploading the request set, execute the below commands

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET.ldt

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET_LINK.ldt





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





## Now for the responsibility

FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_PERSON_RESPY.ldt FND_RESPONSIBILITY RESP_KEY="XX_PERSON_RESPY"

## note that XX_PERSON_RESPY is the responsibility key

## Now to upload

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XX_PERSON_RESPY.ldt





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

## OK, now for the forms personalizations

## For the forms personalizations, I have given three examples as below.

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_PERWSHRG.ldt FND_FORM_CUSTOM_RULES function_name="PERWSHRG-404"

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_HZ_ARXCUDCI_STD.ldt FND_FORM_CUSTOM_RULES function_name="HZ_ARXCUDCI_STD"

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_AP_APXVDMVD.ldt FND_FORM_CUSTOM_RULES function_name="AP_APXVDMVD"

## Note that the function name above is the function short name as seen in the Function Definition Screen

## Now to upload the forms personalizations that are defined against these forms functions....

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_PERWSHRG.ldt

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_HZ_ARXCUDCI_STD.ldt

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_AP_APXVDMVD.ldt





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





Notes :

1. Give special attention when downloading Menus or Responsibilities.

In case your client has several developers modifying Responsibilities and Menus, then be ultra carefull. Not being carefull will mean that untested Forms and Functions will become available in your clients Production environment besides your tested forms, functions and menus.



2. Be very careful when downloading flexfields that reference value sets with independent values for GL Segment Codes.

By doing so, you will download and extract all the test data in GL Codes that might not be applicable for production.



3. There are several variations possible for FNDLOAD, for example you can restrict the download and uploads to specific segments within Descriptive Flex Fields. Please amend the above examples as desired for applying appropriate filterations.



4. The list of examples by no mean cover all possible FNDLOAD entities.



5. FNDLOAD is very reliable and stable, if used properly. This happens to by one of my favourite Oracle utilities.



4. Last but not the least, please test your FNDLOAD properly, so as to ensure that you do not get any unexpected data. In past I have noticed undesired results when the Lookup gets modified manually directly on production, and then the FNDLOAD is run for similar changes. If possible, try to follow a good practice of modifying FNDLOADable data only by FNDLOAD on production environment.



5. As the name suggests, FNDLOAD is useful for FND Related objects. However in any implementation, you will be required to migrate the Setups in Financials and Oracle HRMS from one environment to another. For this you can use iSetup. "Oracle iSetup".

Some of the things that can be migrated using Oracle iSetup are

GL Set of Books, HR Organization Structures, HRMS Employees, Profile Options Setup, Suppliers, Customers, Tax Codes

& Tax Rates, Financials Setup, Accounting Calendars, Chart of Accounts, GL Currencies.

Gettting sql output in exl format

Gettting sql output in exl format

Excel format:-

set pagesize 10000

set feedback off

set echo off

set colsep ','

set linesize 10000





spool fnd_log_messages_output.csv

query here......

spool off

Sunday, December 6, 2009

resolve invalid java objects

Follow the action plan given below and update the status


1.select name, text from user_errors where type = 'JAVA CLASS&';


The output for the ORA-29521 should be in the form of ORA-29521: referenced name
';oracle/apps//...'; could not found.


2.With the value of in the above message, go to the
$_TOP/java/make/jar.dep file and search for the referenced name .
The file is separated into sections for each jar file and the classes (names) it contains.


3.Once you find the name of the jar file where the class is contained ,Source the environmental
file that is present in


4.Go to the $_TOP/java/jar/ and load it to the database via loadjava.  The syntax
for ';loadjava'; is as follows:


loadjava -user apps/ -r .jar


5.Execute the same action plan and check whether invalids is getting reduced by executing the
following query


Select count(1),owner,object_type from dba_objects where status=';INVALID'; group by
object_type,owner;

java_invalids_root_cause.sql

set serveroutput on
set feedback off

declare

    cr varchar2(2) := '
';
      tab varchar2(8) := '+-----> ';

    cursor messages1 is
    select b.text, count(*) total
    from dba_objects a, user_errors b
    where a.status = 'INVALID'
    and a.object_type like 'JAVA%'
    and b.text like 'ORA-29521:%'
    and a.object_name = b.name
    and a.owner='APPS'
    group by b.text
    order by 2 desc;

    cursor messages2 is
    select b.text, count(*) total
    from dba_objects a, user_errors b
    where a.status = 'INVALID'
    and a.object_type like 'JAVA%'
    and b.text like 'ORA-29535:%'
    and a.object_name = b.name
    and a.owner='APPS'
    group by b.text
    order by 2 desc;


    counter number := 0;
    
    cursor objects(message varchar2) is
    select name
    from user_errors
    where text = message;

    function get_object_name(msg varchar2) return varchar2 is
    
       tmpstr varchar2(200);
    
    begin
    
      tmpstr := substr(msg,27);
   tmpstr := replace(tmpstr,'could not be found','');
   return trim(tmpstr);     
    end;
    
    
begin

    dbms_output.enable(100000);    
    dbms_output.put_line('Executing');
    for i in messages1 loop
        counter := counter + 1;
     for o in objects(i.text) loop
         dbms_output.put_line(tab || dbms_java.longname(o.name));
         --fnd_aolj_util.getClassVersionFromDB(dbms_java.longname(o.name));
     end loop;
        dbms_output.put_line('Number of invalid objects : ' || i.total);
        dbms_output.put_line('Dependency : ' || get_object_name(i.text));
     dbms_output.put_line(cr);    
    end loop;

    for i in messages2 loop
        counter := counter + 1;
     for o in objects(i.text) loop
         dbms_output.put_line(tab || dbms_java.longname(o.name));
     end loop;
        dbms_output.put_line('Number of invalid objects : ' || i.total);
        dbms_output.put_line('Cause : ' || i.text);
     dbms_output.put_line(cr);
    end loop;
    dbms_output.put_line('Done. Processed ' || to_char(counter) || ' invalid java objects.');
end;
/

Compile invalid java objects

Comp_java.sql


set echo off
set feedback off
set pagesize 0

spool alter_java.sql

select 'ALTER JAVA SOURCE "' || object_name || '" COMPILE;'
from user_objects
where object_type = 'JAVA SOURCE'
and status = 'INVALID'
/

select 'ALTER JAVA CLASS "' || object_name || '" RESOLVE;'
from user_objects
where object_type = 'JAVA CLASS'
and status = 'INVALID'
/

spool off
set feedback on
set pagesize 120
set echo on
@alter_java.sql

Rem This Classes are still INVALID:
set linesize 120
set pagesize 60
col java_class format a80 wrap
select dbms_java.longname(object_name) as "JAVA_CLASS", status
from user_objects
where object_type = 'JAVA CLASS'
and status = 'INVALID'
/

Loadjava

Loadjava
How to run:
There are 2 ways to invoke loadjava.
Method 1
The first way (and easiest) is by invoking the loadjava executable located in the DB Tier oracle home. However, in certain cases, specially when loading applications jar or class files, the DB Tier might not have access to the application classes. In this case, you either FTP the jar/class file to the DB tier, or use method 2.
Syntax for this method:
[orar12@ap9056pc ~]$ $ORACLE_HOME/bin/loadjava
loadjava: Usage: loadjava [-definer] [-encoding encoding] [-force] [-genmissing] [-genmissingjar jar] 
[-grant grants] [-help] [-nousage] [-noverify] [-oci8] [-order] [-resolve] [-resolver resolver] 
[-schema schema] [-synonym] [-thin] [-tableschema schema] [-user user/password@database] 
[-verbose] classes..jars..resources..properties...
Usually:
loadjava -u apps/@ -force -resolve -verbose 
Note that in this case, the connect string can be specified as a standard TNS alias as loadjava uses the OCI jdbc drivers to connect to the database.
Method 2
This way can be invoked as the applmgr user. The syntax for this method is
[applmgr@ap9056pc java]$ java -ms128m -mx256m oracle.aurora.server.tools.loadjava.LoadJavaMain
loadjava: Usage: loadjava [-definer] [-encoding encoding] [-force] [-grant grants] [-help] [-noverify] 
[-oci8] [-order] [-resolve] [-resolver resolver] [-schema schema] [-synonym] [-thin] 
[-user user/password@database] [-verbose] classes..jars..resources..properties...
Usually:
- Sample 1: using THIN JDBC drivers
java -ms128m -mx256m oracle.aurora.server.tools.loadjava.LoadJavaMain \
-force -thin -verbose -resolve -user "APPS/@::" \

- Sample 2: using OCI JDBC drivers
java -ms128m -mx256m oracle.aurora.server.tools.loadjava.LoadJavaMain \
-force -verbose -resolve -user "APPS/@" \

For sample 1, you need to know the username, password, hostname, port where the listener is listening and the instance name. For sample 2, you only need to know the username, password and the TNS alias for the instance you desire to connect to. For clarification purposes, it doesn’t make a difference on the end result which method you use.

Dropjava example
java -ms128m -mx256m oracle.aurora.server.tools.loadjava.DropJavaMain -thin -verbose \
-user "APPS/@::" 
NOTE: Never drop any java object from the database unless instructed by the development team that owns the class. This entry exists just for documentation completion purposes.

Invalid Java Object

We need to upload the JAVA CLASS again into database at that
Cause
After applying the patches , number of invalid increases.
All the new invalids are of type "JAVA CLASS".
We need to check as to which class the JAVA CLASS object belongs.

SQL> SELECT dbms_java.longname(object_name) from dba_objects where status ='INVALID' and object_Type like '%JAVA%';


DBMS_JAVA.LONGNAME(OBJECT_NAME)
--------------------------------------------------------------------------------
oracle/apps/per/util/gfmUpload
oracle/apps/pay/proc/extract/DocumentGenerator
oracle/apps/pay/proc/extract/DirectDepositConcurrentProgram

The result of this query shows that these java classes need to be uploaded to database.
Need to start loading with any of java class and continue the loading until no error comes.
Solution
1) Go to $IAS_ORACLE_HOME and source the enviroment file.
2) Check for "loadjava" in environment.

$ which loadjava
~/iAS/bin/loadjava

3) Go to $JAVA_TOP and run the following loadjava command for any invalid java class :-
SYNTAX:-
loadjava -force -verbose -resolve -user apps/@

Example:-
loadjava -force -resolve -user apps/***@connect_string oracle/apps/per/util/gfmUpload.class


4) On running load java, if error ORA-29521 comes like an example below:-
ORA-29521: referenced name oracle/ewt/util/WindowUtils could not be found

Run same loadjava command for that class type:-
loadjava -force -resolve -user apps/***@connect_string oracle/ewt/util/WindowUtils.class

Repeat the step 4, until load java completed successfully , without errors.
On successful completion, go to step 5.

5) Run adadmin , compile apps schema.

Above is the work around to fix increased number of JAVA CLASS type invalid objects after patching.
References
Note 206511.1 - How to Find a JAR File Which Contains a Particular JAVA CLASS Object?