Here is the list of Important oracle Apps Scripts

Here is the list of few important tables.
Concurrent Manager


FND_CONCURRENT_QUEUES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUE_SIZE

FND

FND_APPL_TOPS
FND_LOGINS
FND_USER
FND_DM_NODES
FND_TNS_ALIASES
FND_NODES
FND_RESPONSIBILITY
FND_DATABASES
FND_UNSUCCESSFUL_LOGINS
FND_LANGUAGES
FND_APPLICATION
FND_PROFILE_OPTION_VALUES

AD / Patches

AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_BUGS
AD_INSTALL_PROCESSES
AD_SESSIONS
AD_APPL_TOPS

/* To find the latest application version */

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE updated,ROW_SOURCE_COMMENTS "how it is done",BASE_RELEASE_FLAG "Base version" FROM AD_RELEASES where END_DATE_ACTIVE IS NULL

/* Query to find out if any patch except localisation patch is applied or not, if applied, that what all drivers it contain and time of it's application*/

select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE, B.PATCH_DRVIER_ID, B.DRIVER_FILE_NAME, B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE, B.CREATIONG_DATE, B.FILE_SIZE, B.MERGED_DRIVER_FLAG, B.MERGE_DATE from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID and A.PATCH_NAME = ''

/* To know that if the patch is applied successfully, applied on both node or not, start time of patch application and end time of patch application, patch top location , session id ... patch run id */

select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, B.INSTANCE_NAME, B.NAME, C.DRIVER_FILE_NAME, A.PATCH_DRIVER_ID, A.PATCH_RUN_ID, A.SESSION_ID, A.PATCH_TOP, A.START_DATE, A.END_DATE, A.SUCCESS_FLAG, A.FAILURE_COMMENTS from AD_PATCH_RUNS A, AD_APPL_TOPS B, AD_PATCH_DRVIERS C, AD_APPLIED_PATCHES D where A.APPL_TOP_ID = B.APPL_TOP_ID AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID and C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID and A.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = '')) ORDER BY 3;

/* to find the base application version */

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES where BASE_RELEASE_FLAG = 'Y'


/* To find all available application version */

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, END_DATE_ACTIVE "when lasted", CASE WHEN BASE_RELEASE_FLAG = 'Y' Then 'BASE VERSION' ELSE 'Upgrade' END "BASE/UPGRADE", ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES


/* To get file version of any application file which is changed through patch application */

select A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME, max(B.VERSION) from AD_FILES A, AD_FILE_VERSIONS B where A.FILE_ID = B.FILE_ID and B.FILE_ID = 86291 group by A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME


/* To get information related to how many time driver file is applied for bugs */

select * from AD_PATCH_RUN_BUGS where BUG_ID in (select BUG_ID from AD_BUGS where BUG_NUMBER = ''

/* To find latest patchset level for module installed */

select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME

/* To find what is being done by the patch */

select A.BUG_NUMBER "Patch Number", B. PATCh_RUN_BUG_ID "Run Id",D.APP_SHORT_NAME appl_top, D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F where A.BUG_ID = B.BUG_ID and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and D.FILE_ID = F.FILE_ID and A.BUG_NUMBER = '' and B.PATCH_RUN_BUG_ID = ' < > ' and C.EXECUTED_FLAG = 'Y' GROUP BY A.BUG_NUMBER, B.PATCH_RUN_BUG_ID, D. APP_SHORT_NAME, D>SUBDIR, D.FILENAME, E.ACTION_CODE


/* To find Merged patch Information from database in Oracle Applications */

select bug_number from ad_bugs where bug_id in ( select bug_id from ad_comprising_patches where patch_driver_id =(select patch_driver_id from ad_patch_drivers where applied_patch_id =&n) );

/* Second Query to know, what all has been done during application of PATCH */

Select J.PATCh_NAME, H.APPLICATIONS_SYSTEM_NAME Instance_Name, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME appl_top,D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B,AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F, AD_PATCH_RUNS G,
AD_APPL_TOPS H, AD_PATCH_DRIVERS I, AD_APPLIED_PATCHES J where A.BUG_ID = B.BUG_ID and
B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID
and D.FILE_ID = F.FILE_ID and G.APPL_TOP_ID = H.APPL_TOP_ID and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and
I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and B.PATCH_RUN_ID = G.PATCH_RUN_ID and C.EXECUTED_FLAG = 'Y' and
G.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID
in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = 'merged'))
GROUP BY J.PATCH_NAME, H.APPLICATIONS_SYSTEM_NAME, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME, D.SUBDIR,
D.FILENAME, E.ACTION_CODE


/* Script to find out Patch level of mini Pack */

Select product_version,patch_level from FND_PROUDCT_INSTALLATIONS where patch_level like '%&shortname%';

Replace short name by name of Oracle Apps Minipack for which you want to find out Patch level . ex.
AD - for Applications DBA
GL - for General Ledger
PO - Purchase Order



Where is Concurrent Manager Node ? If you don't know where is your CM here are few ways to find your it .

SQLPLUS> select node_name from apps.fnd_nodes where support_cp='Y';


Here I am listing Unix/Linux commnads which might be useful while troubleshooting Oracle Apps.


Enable Trace on any Executable to find out whats happening at O.S. Level

truss -eafl -o output.trc -o truss.txt
for example for Apache
truss -eafl -o output.trc -o truss.txt apachectl

This command will trace any system calls and will help you to find out errors.



How to find a "word" or pattern in all files in a directory & subdirectories

find . -name "*" -exec grep -l {} \; -print
for example I want to search for word oracle
find . -name "*" -exec grep -l oracle {} \; -print



How to delete files older than N number of days , Useful to delete old log files

find . -name '*.*' -mtime + -exec rm {} \;
for example if I want to delete all files older than 7 days
find . -name '*.*' -mtime +7 -exec rm {} \;
*Check carefully & run it first from logs or dump directory


How to find a class inside a set of Jar files


for i in 'find .-name *.jar'
do
if test 'jar -tvf $i|grep QualityObject.class'
then
ls $i
fi
done


How to find the symbolic links that point to the old path in your oracle_home and appl_top.

This command is useful in cloning after restore from source to target that symbolic link are not pointing to source.

ls -al `find . -type l` | grep $OLD_PATH



To find all the text files that contains the old path at the UNIX level.

find . -type f -print|grep -v ":"|xargs file|grep "text"|awk '{FS=":"}{print $1}'|xargs grep -il $OLD_PATH



How to Sort files based on Size of file in a Directory
Useful in finding out spaces issues


ls -l | sort -nrk 5 | more


How to check if a Port is listening for any Service

netstat -an | grep $PORTNO

How to schedule a Job in Unix

Use cronjob
crontab -l ( list current jobs in cron)
crontab -e ( edit current jobs in cron )
_1_ _2_ _3_ _4_ _5_ $Job_Name
1 - Minutes (0-59)
2 - Hours ( 0-24)
3 - day of month ( 1- 31 )
4 - Month ( 1-12)
5 - A day of week ( 0- 6 ) 0 -> sunday 1-> monday
e.g. 0 0 1 * 5 Means run job at Midnight on 1st of month & every friday



Profile Options Useful for Oracle Apps DBA

Here is the list of few profile options which Apps DBA use frequently. It is not necessary that you as Apps DBA must know all profile options, it depends on your implemnetation. I am going going to update more about Profile Options.

Applications Help Web Agent
Applications Servlet Agent
Applications Web Agent
Concurrent: Active Request Limit
Concurrent: Hold Requests
Concurrent: Multiple Time Zones
Concurrent: Report Access Level
Concurrent: Report Copies
Concurrent: Request priority
Database Instance
Enable Security Group
FND: Debug Log Filename
FND: Debug Log Level
Forms Runtime Parameters
Gateway User ID
ICX: Discoverer Launcher
ICX: Forms Launcher
ICX: Report Launcher
ICX: Limit Connect
ICX: Limit time
ICX: Session Timeout
MO Operating Unit
Node Trust Level
RRA: Delete Temporary Files
RRA: Enabled
RRA: Service Prefix
RRA: Maximum Transfer Size
Self Service Personal Home Page Mode
Sign-On: Audit Level
Signon Password Failure Limit
Signon Password Hard to Guess
Signon Password Length
Signon Password No Reuse
Site Name
Socket Listener Port
TCF: Host
TCF: Port
TWO TASK
Viewer: Text


Below you will find commonly asked questions about various component Version in Oracle Applications & way to find them which might be helpful in Apps DBA Job. Most of these are for Unix / Linux.


Q: How to find Oracle Applications Web Server Version ?
Q: How to find Version of Apache used with oracle apps 11i ?


Log to Application tier as Operating System Usually called as APPLMGR ; go to location $IAS_ORACLE_HOME/Apache/Apache/bin and run command
./httpd -version
You will see output like

Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built: Dec 6 2005 14:59:13 (iAS 1.0.2.2.2 rollup 5)
Which means you are on iAS Version 1.0.2.2.2 with patchset rollup 5 with Apache Version 1.3.19


Server version: Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server

Above is output If you have installed 10g Application Server with 11i


Q: How to find Jinitiator Version ?

Check for file like appsweb_SID_HOSTNAME.cfg under $OA_HTML/bin defined by environment variable FORMS60_WEB_CONFIG_FILE & search for entry like jinit_ver_name , you will see entry like
jinit_ver_name=Version=1,3,1,23
which means Jinitiator version is 1.3.1.23 ; if your version is 1.3.1.18 you will see entry like 1,3,1,18


Q: How to find Forms Version in 11i ?

Login to forms from frontend , on top menu bar of forms click on "Help" & Select "About Oracle Applications" go to "Forms Server " section. You should see entry like below depending on your forms version
Oracle Forms Version : 6.0.8.26.0
Which mean you are on forms version 6.0.8.26 . If you want to know whats your forms patchset level then subtract 9 from fourth digit which means for above case form patchset 17 is applied.


Q: How to find Forms Version in Apps from command Line ?

Enter "f60gen" on Forms Server and check for first line in output like
Forms 6.0 (Form Compiler) Version 6.0.8.26.0 (Production)
This confirms that you are on forms server version 6.0.8.26.0 and patch set 17. ( Patch Set = Fourth Digit - 9)


Q: How to find Version of any file in Oracle Apps 11i ? or
Q: How to find any Reports Version 11i ? or


In Oracle Applications under ad utilities there is utility called as adident Used for Identification purpose or to find out file version use
adident Header
for ex. inorder to to find file version of one AR form i.e. ARXGLCOR.fmx
adident Header ARXGLCOR.fmx
You should see output like
$Header APPSTAND.fmb 115.33 2002/04/04 11:13:40 pkm ship
$ $Header ARXGLCOR.fmb 115.15 2005/01/31 13:48 mraymond ship
Which means above form executable consist of two forms whose version is 115.33 & 115.15 resp. Similarly you can use adident to find version of any report in 11i.


Q: How to find Operation System Version (Unix/Linux) ?

For solaris use command
uname -a
You will see output like
For Solaris SunOS servername 5.8 Generic_117350-23 sun4u sparc SUNW,Sun-Fire-V240
For RedHat Linux use command
cat /etc/*release*
You will see output like
Red Hat Enterprise Linux AS release 3 (Taroon Update 6)

Which means you are on Solaris 5.8 or Linux AS 3 resp.

Q: How to find if your Operating System is 32 bit or 64 Bit ?

For solaris use command
isainfo -v
If you see out put like
32-bit sparc applications
That means your O.S. is only 32 bit but if you see output like


64-bit sparcv9 applications
32-bit sparc applications
above means your o.s. is 64 bit & can support both 32 & 64 bit applications


Q: How to find Operating System version ?

For solaris use command
uname -a
You will see output like
For Solaris SunOS servername 5.8 Generic_117350-23 sun4u sparc SUNW,Sun-Fire-V240
For RedHat Linux use command
cat /etc/*release*
You will see output like
Red Hat Enterprise Linux AS release 3 (Taroon Update 6)

Which means you are on Solaris 5.8 or Linux AS 3 resp.

Q: How to find if your Operating System is 32 bit or 64 Bit ?
For solaris use command
isainfo -v
If you see out put like
32-bit sparc applications
That means your O.S. is only 32 bit but if you see output like


64-bit sparcv9 applications
32-bit sparc applications
above means your o.s. is 64 bit & can support both 32 & 64 bit applications



Q: Can I run 64 bit application on 32 bit Operating system ?

You can run 32 bit application (like oracle application server, web server, all oracle application server are 32 bit ) on both 32 /64 bit operating system but a 64 bit application like 64 bit database can run only on 64 bit operating system.

Q How to find if your database is 32 bit or 64 bit(Useful in applying Patches) ?


execute "file $ORACLE_HOME/bin/oracle" , you should see output like
/u01/db/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1
which means you are on 64 bit oracle
If your oracle is 32 bit you should see output like
oracle: ELF 32-bit MSB executable SPARC Version 1


Q. How to find Version of Apps 11i ?

Run following SQL from apps user

SQL> select RELEASE_NAME from fnd_product_groups;
You should see output like
RELEASE_NAME
-----------------------
11.5.10.2
Which means you are on Apps Version 11.5.10.2

Q: Can I run 64 bit application on 32 bit Operating system ?

You can run 32 bit application (like oracle application server, web server, all oracle application server are 32 bit ) on both 32 /64 bit operating system but a 64 bit application like 64 bit database can run only on 64 bit operating system.


Q How to find if your database is 32 bit or 64 bit(Useful in applying Patches) ?

execute "file $ORACLE_HOME/bin/oracle" , you should see output like
/u01/db/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1
which means you are on 64 bit oracle
If your oracle is 32 bit you should see output like
oracle: ELF 32-bit MSB executable SPARC Version 1
Now you know what should be bit of patch to download



Q How to Discoverer Version installed with Apps ?

Discoverer with Apps installed in ORACLE_HOME same as 806 is usually 3i or 4i. To find Version login to Application Tier & go to $ORACLE_HOME/discwb4/bin and execute
strings dis4ws | grep -i 'discoverer version'
You should see output like
Discoverer Version:Session 4.1.47.09.00

Q. How to find Workflow Version embedded in Apps 11i ?

Run following SQL from apps user ;
SQL>select TEXT from WF_RESOURCES where NAME='WF_VERSION';

You should see output like
TEXT
-----------------------
2.6.0
Which means you are on Workflow Version 2.6.0


You can also use script wfver.sql in FND_TOP/sql to find version of workflow in Apps.


Q: How to find version of JDK Installed on Apps ?

There might be multiple JDK installed on Operating System . Like JDK 1.3.1, 1.4.2 or 1.5 but in order to find which Version of JDK your Apps is using
Open your Context File $SID_$HOSTNAME.xml under $APPL_TOP/admin and look for variable
JDK_TOP oa_var="s_jdktop" what so ever value assigned against that parameter go to that directory & cd bin & execute command
./java -version so lets assume entry above is /usr/jdk then cd /usr/jdk/bin & ./java -version , you will see output like

java version "1.4.2_10"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_10-b03)
Java HotSpot(TM) Client VM (build 1.4.2_10-b03, mixed mode)

2 comments:

Microsoft Office 2007 said...

NEVERTHELESS, THE CIVIL LAW is and must be neutral about who has a more noble or rewarding faith. The breakaway parishes ought to win every Office 2010facet of the lawsuit not becauseMicrosoft Office 2010 their beliefs or their politics are better, Microsoft wordbut because both lawOffice 2007and equity, along with common sense, are on Microsoft Officetheir side.Microsoft Office 2007 Not only does Virginia state law (the Division Statute)Office 2007 keyexplicitly apply to just such a Office 2007 downloadsituation as now exists, but the history Office 2007 Professionalespecially of The Falls Church argues against the claims of Outlook 2010the Virginia Diocese with which theyMicrosoft outlookhave disassociated.Microsoft outlook 2010First, The Falls Church wasWindows 7 founded, formed, and developed long before the diocese, or the national Episcopal Church, even existed.

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.