Trace Analyzer, also known as TRCANLZR or TRCA,

From the below note-id download the trace Analyzer.

TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer - Tool for Interpreting Raw SQL Traces [ID 224270.1]

Unzip the trca on db node:
Install the trace Analyzer : follow the installation doc in the trca directory

Run trace Analyzer:

$cd /home/oracrp/trca/run
login as apps user
sql > start trcanlzr.sql CRP2_ora_29564.trc

provide the trace file to be analyzed as given above
once the trace analyzer is completed you will get the trca_*.zip file in the same directory.

Terminate the concurrent Request

Login to db as apps user

update applsys.fnd_concurrent_requests
   set phase_code = 'C',
       status_code = 'X',
    actual_completion_date = sysdate
  where request_id = '&R';

Enter the Request-id.

commit;

exit

Enable Trace for Application User

Login to ebs as sysadmin

Navigate to profile option
Profile value : Initialization SQL Statement - Custom
Username :  Application user for whom you want to generate Trace
click on Find

Under username column put this..

BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'username_instance' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;

Note : Level cane be 4,8,12 depends on your requirement

Example :

BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'Waseem_CRP1' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;

Save it. Inform the User logout and login back.

tracefile name will be : waseem_CRP1.trc

trace will be generated under--udump

Invalid Objects for applying Patch

Below command can be used to obtain Invalid objects before and after applying patches:

 spool invalid_before_patch.log

col owner for a20
col obj for a30
col typ for a20
set lines 120
set pages 500
compute sum of cnt on report
compute sum of cnt on owner
break on owner on report
select owner,object_name obj,object_type typ,1 cnt from dba_objects where status!='VALID' order by 1,2;

spool off;

Same way do after applying patch. e.g spool invalid_after_patch.log

Once Patching is complete:-
use diff command to check any new invalid got generated with patch Application.

e.g diff invalid_before_patch.log invalid_after_patch.log -- Hit enter key.

Start / Stop Mobile Application Services in Oracle Apps R12

Login as applmgr user :
Go to $ADMIN_SCRIPTS_HOME

Stop Mobile Application Services :

./mwactl.sh -login apps/apps_password stop_force
./mwactlwrpr.sh stop apps/apps_password

Start Mobile Application Services :

./mwactl.sh -login apps/apps_password start
./mwactlwrpr.sh start apps/apps_password

Check Mobile Services are up and Runing:

ps -ef | grep mwa

Find port Number of Mobile Services :

grep mwa $CONTEXT_FILE

Connect to Mobile Services :

telnet hostname.domainname portnumber(mobile application service port_number)

Find Concurrent Request picked up by which Concurrent Manager or Find the Manager Assignment for Concurrent Request

select a.user_concurrent_queue_name

from fnd_concurrent_queues_vl a,

FND_CONCURRENT_QUEUE_CONTENT b,

fnd_concurrent_programs_vl c

where a.concurrent_queue_id=b.concurrent_queue_id

and b.type_id = c.concurrent_program_id

and c.user_concurrent_program_name='& Concurrent Program Name';

Get the Req-id from the user.
get the program name from Req-id

sqlt tool (create sqltxplain plan using sqlt tool)

First Login to the server
Go to directory where sqlt tool is installed
There u will find one directory name sqlt.
Inside sqlt directory you will find directories like RUN,INPUT etc...
Go to INPUT directory
Create one sql file.. e.g. q1.sql
Just paste the Query for which you want the sqltxtplain to be generated and save it in q1.sql
Come back to sqlt directory. sqltNow connect as apps user (sqlplus apps/apps)
Just run the following command.
Command to generate sqltxplan plan for given or provided sql
sql > start run/sqltxplain.sql input/q1.sqlIt will prompt for Enter value for sqlt_method: just give XPLAINThen it will prompt for SQLTXPLAIN password (required)
Password for user SQLTXPLAIN: Give SQLTXPLAIN as password
Then one Zip file will get created.
That is what SQLTXTPLAIN!!!

Create Application users across instances with same responsibilities

Find responsibilities attached to a user
SELECT frt.RESPONSIBILITY_NAME, furg.end_date
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,fnd_responsibility_tl frt,fnd_user fu
WHERE fu.user_name = '&&username'
AND   fu.user_id = furg.user_id
AND   furg.responsibility_id = fr.RESPONSIBILITY_ID
AND   frt.responsibility_id = fr.RESPONSIBILITY_ID
ORDER BY 1;

SIMPLEST WAY TO Create application users with same responsibilties  across instances

Downloading Application User Responsibilities

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

Uploading Application user Responsibilites in another instance

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

Create another Application user with same Responsibilities as the existing application user.

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

Uploading Application user Responsibilites to another Applicatioon user in same instance

do vi *.ldt file
Change username to new username
vi XX_FND_USER_PASSI.ldt

In this ldt file change USER_NAME to new user - then upload the ldt file

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

Note : If Application user does not exists it will create it and if it exists it will add the responsibilities its missing.

CUSTOM.pll not picking from Custom_top/resource.

By default CUSTOM.pll is in AU_TOP/resource.

If customisation is there some clients will create CUSTOM_TOP and place it in CUSTOM_TOP/resource directory

solution :

Modify the below files as give in 1 and 2.

1)
login to apps as applmgr user

In R12 edit default.env ($INST_TOP/ora/10.1.2/forms/server/)
Backup the existing default.env file first before modifying it.
change form_path
From FORMS_PATH = '/../../AU_TOP/resource to
FORMS_PATH=/u002/oracle/ERP12XYZ/apps/apps_st/appl/abc_custom/au_g/1.0.0/forms/US:/u002/oracle/ERP12XYZ/apps/apps_st/appl/abc_custom/au_g/1.0.0/resource:/u002/oracle/ERP12XYZ/apps/apps_st/appl/au/12.0.0/resource:/u002/oracle/ERP12XYZ/apps/apps_st/appl/au/12.0.0/resource/stub:/u002/oracle/ERP12XYZ/apps/apps_st/appl/au/forms/US:/u002/oracle/ERP12XYZ/apps/apps_st/appl/abc_custom/abcx/1.0.0/forms/US
comment the existing FORMS_PATH and write the new FORMS_PATH as given above.

**************
# Begin Customization
# APPLFULL="${APPLFULL}abcX INFX IEXX TRNX MIGX"
# export APPLFULL
# Mention CUSTOM_TOP also as given below
CUSTOM_TOP=/u002/oracle/ERP12XYZ/apps/apps_st/appl/abc_custom
abcX_TOP=/u002/oracle/ERP12XYZ/apps/apps_st/appl/abc_custom/abcx/1.0.0
AUG_TOP=/u002/oracle/ERP12XYZ/apps/apps_st/appl/abc_custom/au_g/1.0.0

#END Customization

2)

GO to APPL_TOP backup the existing $TWO_TASK.env file starting with SID_HOSTNAME.env
comment the existing FORMS_PATH

Change FORMS_PATH what you have mentioned in default.env
export FORMS_PATH
bounce opmctl.sh
logout and login back
check echo $FORMS_PATH

Now you will be able to see the new FORMS_PATH

Create Zipped TARBALL

nohup tar cEf - apps | gzip -c > /u806/oracle/ERP12XYZ_051211/apps.tar.gz &
nohup tar cEf - inst | gzip -c > /u806/oracle/ERP12XYZ_051211/inst.tar.gz &

Check :

ps -ef | grep tar

Create Compressed Zipped Tar ball in one step:

nohup tar -czvf /backup/EBSapps_date.tgz EBSapps > EBSappstar.log &

the job will run in background.

Unzip the compressed tar ball

tar -xzvf EBSapps_date.tgz

Find Missing Libraries / Find which libraries progam is using

Error : ld.so.1: item_main: fatal: libclntsh.so.1.0: open failed
$ ldd program_name
$ ldd item_main
ldd item_main
        libclntsh.so.1.0 =>     not found
        libnsl.so.1 =>   /lib/libnsl.so.1
        libsocket.so.1 =>        /lib/libsocket.so.1
        libgen.so.1 =>   /lib/libgen.so.1
        libdl.so.1 =>    /lib/libdl.so.1
        libc.so.1 =>     /lib/libc.so.1
        libaio.so.1 =>   /lib/libaio.so.1
        libm.so.2 =>     /lib/libm.so.2
        libmp.so.2 =>    /lib/libmp.so.2
        libmd.so.1 =>    /lib/libmd.so.1
        libscf.so.1 =>   /lib/libscf.so.1
        libdoor.so.1 =>  /lib/libdoor.so.1
        libuutil.so.1 =>         /lib/libuutil.so.1
        /platform/SUNW,SPARC-Enterprise/lib/libc_psr.so.1
sun1287_applmgr_XYZ$
NOte : If some library is missing it will show you not found as shown above
check LD_LIBRARY_PATH
also find the missing library and place it under correct place.

Then run the above command

check for the libraries in the below path :
check in 10.1.2 Oracle_home/lib

check in /usr/lib->

Create Read Only Apps User

In ASM Instance:

SQL> create tablespace APPSREADONLY
  2  datafile '+DATA/xyz/datafile/' size 500M;
Tablespace created.

Non-ASM Instance:

SQL> create tablespace APPSREADONLY
  2  datafile '/xyz/datafile/APPSREADONLY01.dbf' size 500M;
Tablespace created.
SQL> create user APPSREADONLY identified by APPSREADONLY
  2  DEFAULT TABLESPACE APPSREADONLY
  3  TEMPORARY TABLESPACE temp
  4  QUOTA UNLIMITED ON APPSREADONLY;
User created.
SQL> grant connect to APPSREADONLY;
Grant succeeded.

sql> spool appsreadonly.log
select 'grant select on '|| 'APPS.'||object_name ||' to '||'APPSREADONLY; 'from dba_objects where owner='APPS';
sql> spool off;

Now run this appsreadonly.log
sql> @appsreadonly.log
sql> conn APPSREADONLY/APPSREADONLY
sql> select * from APPS.fnd_languages;
it will display the contents from fnd_languages tables.

adbldxml.pl hangs when retrieving information from database.

Check the adbldxml log file:

Message in logfile:

Retrieving information from database.

Solution:

Login to oracle:

Check the optimizer_mode

show parameter optimizer_mode

optimizer_mode will be all_rows.

Change it to rule:

alter system set optimizer_mode=rule;

run adbldxml.pl scripts

Once it prompts for display

Note: Change optimizer_mode back to all_rows;

alter system optimizer_mode=all_rows;

Now enter the diplay information. hit Enter xml file will generated.

Dislpay information can be obtained from database context file (xmlfile)

grep display contextfilename

Create / Drop / Check Database link or DB link

Two ways to create DB link in 11g Database

1. With TNS entry in tnsnames.ora

2. TNS entry given at the time of db link creation.

SQL> desc DBA_DB_LINKS

 Name                                      Null?                              Type

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

 OWNER                                     NOT NULL      VARCHAR2(30)

 DB_LINK                                   NOT NULL       VARCHAR2(128)

 USERNAME                                                                 VARCHAR2(30)

 HOST                                                                          VARCHAR2(2000)

 CREATED                                   NOT NULL     DATE

***FIND ALL DBLINKS created in database:

select * from dba_db_links;

Create DB link

Syntax:

Create public/private database_link linkname connect to username identified by Password using ‘Service Name’; i

Example :

1st method: Create DBLINK with tns entry made in tnsnames.ora

CREATE PUBLIC DATABASE LINK "TO_SPOT.WORLD" CONNECT TO CFS_SPOT IDENTIFIED BY CFS_Passwd_123 USING 'Spotprof.was.abc.com’;

Now make an entry in tnsnames.ora present in $TNS_ADMIN

SPTPS.WAS.ABC.COM

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = abc.xyz.was.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = SXXX

      (SERVER = DEDICATED)

    )

  )

2^nd Method:

conn / as sysdba issue this command on sqlprompt

CREATE public DATABASE LINK TO_SPOT CONNECT TO CFS_SPOT IDENTIFIED BY password USING

'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname.domainname.com)(PORT=1521)))

(CONNECT_DATA=(SID = SXXX')));

Check DB link is working or not

conn / as sysdba or conn to sql and try the below one :

syntax : select * from dual@linkname;

Example

select * from dual@TO_SPOT;

DROP DBLINK:

DROP PUBLIC DATABASE LINK "TO_SPOT.WORLD";

truncate /drop all database links

truncate table link$;