Email Concurrent Program Output

#REM ============================================================
#REM  Description: This Shell script program accepts request_id, email and Subject as argument
#REM                     and Send email with attachment of output file for the request
#REM=============================================================

echo "Current Request :" $4
echo "Output Request :"$5
echo "Email :"$6
echo "Email Subject :"$7

fname=`sqlplus -s $1 <set head off
set feedback off
set verify off
select outfile_name from fnd_concurrent_requests
where request_id = $5
/
xxxEOFxxx`

echo "File name from SQL :"$fname
fname=`ls $fname`
echo "Actual file name :" $fname
if [ -z "$fname" ]
then
        echo "No request matching id $5"
elif [ -s $fname ]
then
        echo "Getting output file: $fname"
 echo "Sending Email to :" $6
            /usr/bin/uuencode $fname "$5.out" | mailx -s "$7" $6

else
        echo "Either file $fname does not exist or it is of 0 bytes"
fi

How to find version of file on server?

strings -a $INV_TOP/forms/US/INVMWBIV.fmx | grep '$Header'

$Header: INVMWBIV.fmb 120.101.12000000.30 2008/07/17 07:07 ckrishna noship

OR

grep '$Header' $INV_TOP/patch/115/sql/INVTXGGB.pls

/* $Header: INVTXGGB.pls 120.55.12000000.15 2007/10/25 11:02:58 ksaripal ship $ */

How to create Custom Top and User?

CUSTOM TOP CREATION

cd $APPL_TOP
mkdir xxf/
mkdir xxf/1.0.0/
mkdir xxf/1.0.0/admin
mkdir xxf/1.0.0/bin
mkdir xxf/1.0.0/dll
mkdir xxf/1.0.0/forms
mkdir xxf/1.0.0/help
mkdir xxf/1.0.0/html
mkdir xxf/1.0.0/java
mkdir xxf/1.0.0/jlt
mkdir xxf/1.0.0/lib
mkdir xxf/1.0.0/log
mkdir xxf/1.0.0/mds
mkdir xxf/1.0.0/mesg
mkdir xxf/1.0.0/out
mkdir xxf/1.0.0/resource
mkdir xxf/1.0.0/scripts
mkdir xxf/1.0.0/servlets
mkdir xxf/1.0.0/share
mkdir xxf/1.0.0/sql
mkdir xxf/1.0.0/xml

CUSTOM TOP ENTRY

"export XXF_TOP=$APPL_TOP/xxf/1.0.0" added to custom environment file- D1FEMP00_phls6015.env

CUSTOM USER CREATION

CREATE USER XXF IDENTIFIED BY xxf123
DEFAULT TABLESPACE apps_ts_tx_data
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
GRANT CONNECT TO XXF;
GRANT RESOURCE TO XXF;
ALTER USER XXF DEFAULT ROLE ALL;
GRANT UNLIMITED TABLESPACE TO XXF;

Tarball Creation

While working in OOD (Oracle On Demand) Environment, developers need to provide Tarball (Zip file) to oracle for migration of components. Below are the Unix Commands for the same.

pwd
/home/c_vghaya
mkdir TECH001
Move all files (.ctl, .rdf, .install, .ldt etc..) to above directory TECH001
tar -cvf TECH001_D_1.tar TECH001
pwd
/home/c_vghaya
chmod 777 TECH001_D_1.tar
compress TECH001_D_1.tar
Note- After Compress, Zip File- (TECH001_D_1.tar.Z) file will be created in /home/c_vghaya. Upload this Zip file to oracle for migration
----------------------------------------------------------
Alternatively, you can also user below commands to explode this tarball and perform migration
pbrun impnpdb -u apdbtg7i
cd $XBOL_TOP/install
cp /home/c_vghaya/TECH058_D_1.tar.Z .
uncompress TECH058_D_1.tar.Z
Note- After Uncompress TECH001_D_1.tar file will be created in $XBOL_TOP/installchmod 777 TECH001_D_1.tar
tar -xvf TECH001_D_1.tar
Note- Above command will create TECH001 directory in $XBOL_TOP/install
cd TECH001
Now finally run .install file from TECH001 directory to transfer all files to respective folders
. XXEQ_AP_INVOICE.install BOLINF/Plin28tx APPS/(appspwd) $XBOL_TOP

Note: After running .install file, files are transferred as below. This install file is a shell script
1) .sql files --> $XBOL_TOP/admin/sql
2) .ldt files --> $XBOL_TOP/patch/115/import
3) .prog & .ctl files --> $XBOL_TOP/bin
4) .rdf files --> $XBOL_TOP/reports/US

TKPROF Utility

1) TKPROF is an utility and a unix command to format Raw trace file which is huge in size. It essentially formats a trace file into a more readable format for performance analysis.

2) Trace files are placed by Oracle in user_dump_dest directory as specified in the init.ora
Use below query to find the location of trace files
select value from v$parameter
where name = 'user_dump_dest';

3) Use the following Unix command on the server:
TKPROF tracefile exportfile [explain=username/password] [table= …] [print= ] [insert= ] [sys= ] [record=..] [sort= ]

Eg- tkprof ora_12345.trc output.txt explain=scott/tiger sort=(prsela,exeela,fchela)
Here Sort is on elapsed parse time, elapsed executed time and elapsed fetch-time.