Clearing cache for specific Component/Application in R12
Login as sysadmin
Select “Functional Administrator” responsibility
Go to Core Services ==> Caching Framework ==> Tuning
Query the application (for example iStore) or by Name or code.
Select the component and clear the cache.
Note: For some specific configuration changes, we need to bounce Apache also.
Clearing Global Cache in R12
a) Go to Functional Administrator Responsibility
b) Go to Core Services ==> Caching Framework
c) Go to Global configuration and clear all the cache from there.(Only If you want to clear all the cache)
http://forums.oracle.com/forums/thread.jspa?threadID=527239
R12 Clear Cache is Required to See Supplie Site Updates [ID 1203685.1]
Tuesday, January 11, 2011
Tuesday, August 10, 2010
Compile Forms and Reports in 11.5.10.2
1) How to compile a form?
A) Open the source code in the client PC using X-Server
---------------------------------------------------------
i) Configure X-Server to access the GUI of the server.
ii) Open the form from the X-Server by using f60desm
iii) Make the changes in the forms according to your needs and save the form. The form will be saved in the server itself.
iv) Compile and generate the forms by using f60gen from the Putty / telnet session by using the following syntax.
f60gen module=form_name.fmb userid=apps/apps
or
f60gen module=form_name.fmb userid=apps/apps output_file=form_name.fmx
module_type=form batch=yes compile_all=special
v) f60gen will generate the run time executable .fmx file, move the file to the appropriate product top.
2) How To Compile Reports for Release 11i Applications?
A)
1. cd to where the .rdf files reside.
$cd/reports
2. Logon as applmgr
3. Use rwcon60 syntax for converting a .rdf file to a .rex file:
$ rwcon60 userid=/ source=< report name>.rdf
stype=rdffile dtype=rexfile dest=.rex overwrite=yes batch=yes
4. Use rwcon60 syntax for converting a .rex file back to a .rdf file:
$ rwcon60 userid=/ source=< report name>.rex
stype=rexfile dtype=rdffile dest=.rdf overwrite=yes batch=yes
5. Refer to the following example:
*Note: userid is the schema owner;
If the report is a GL report then the userid will be gl/gl
If the report is a FND report then the userid will be apps/apps
For the FNDSCURS.rdf example the report reside in:
cd $FND_TOP/reports
Logon as applmgr
The command line syntax is:
rwcon60 userid=apps/apps source=FNDSCURS.rdf stype=rdffile dtype=rexfile
dest=FNDSCURS.rex overwrite=yes batch=yes
rwcon60 userid=apps/apps source=FNDSCURS.rex stype=rexfile dtype=rdffile
dest=FNDSCURS.rdf overwrite=yes batch=yes
****************************************************************************
How To Generate A Report ( .RDF File) From The Unix Command on Release 11.5.X [ID 132638.1]
How To Customize And Compile An Application Seeded Form (FMB) Or Library (PLL)? [ID 427879.1]
A) Open the source code in the client PC using X-Server
---------------------------------------------------------
i) Configure X-Server to access the GUI of the server.
ii) Open the form from the X-Server by using f60desm
iii) Make the changes in the forms according to your needs and save the form. The form will be saved in the server itself.
iv) Compile and generate the forms by using f60gen from the Putty / telnet session by using the following syntax.
f60gen module=form_name.fmb userid=apps/apps
or
f60gen module=form_name.fmb userid=apps/apps output_file=form_name.fmx
module_type=form batch=yes compile_all=special
v) f60gen will generate the run time executable .fmx file, move the file to the appropriate product top.
2) How To Compile Reports for Release 11i Applications?
A)
1. cd to where the .rdf files reside.
$cd
2. Logon as applmgr
3. Use rwcon60 syntax for converting a .rdf file to a .rex file:
$ rwcon60 userid=
stype=rdffile dtype=rexfile dest=
4. Use rwcon60 syntax for converting a .rex file back to a .rdf file:
$ rwcon60 userid=
stype=rexfile dtype=rdffile dest=
5. Refer to the following example:
*Note: userid is the schema owner;
If the report is a GL report then the userid will be gl/gl
If the report is a FND report then the userid will be apps/apps
For the FNDSCURS.rdf example the report reside in:
cd $FND_TOP/reports
Logon as applmgr
The command line syntax is:
rwcon60 userid=apps/apps source=FNDSCURS.rdf stype=rdffile dtype=rexfile
dest=FNDSCURS.rex overwrite=yes batch=yes
rwcon60 userid=apps/apps source=FNDSCURS.rex stype=rexfile dtype=rdffile
dest=FNDSCURS.rdf overwrite=yes batch=yes
****************************************************************************
How To Generate A Report ( .RDF File) From The Unix Command on Release 11.5.X [ID 132638.1]
Monday, July 5, 2010
Temp usage SQL's
Script to find the temp usage
SELECT S.sid,S.serial# sid_serial,S.INST_ID, S.username, S.osuser, P.spid, S.module,S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,S.INST_ID,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
To get the SQL running in the temp tablespace
SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;
How Do You Find Who And What SQL Is Using Temp Segments (Doc ID 317441.1)
SELECT a.username, a.sid, a.serial#, a.osuser, a.program, a.module, d.spid, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c, v$process d
WHERE a.saddr = b.session_addr
AND a.paddr= d.addr
AND c.sql_id = b.SQL_ID_TEMPSEG
ORDER BY b.tablespace, b.blocks;
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;
SELECT S.sid,S.serial# sid_serial,S.INST_ID, S.username, S.osuser, P.spid, S.module,S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,S.INST_ID,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
To get the SQL running in the temp tablespace
SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;
How Do You Find Who And What SQL Is Using Temp Segments (Doc ID 317441.1)
SELECT a.username, a.sid, a.serial#, a.osuser, a.program, a.module, d.spid, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c, v$process d
WHERE a.saddr = b.session_addr
AND a.paddr= d.addr
AND c.sql_id = b.SQL_ID_TEMPSEG
ORDER BY b.tablespace, b.blocks;
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;
Friday, April 9, 2010
Tracing
10046 Trace Level Description
1 Basic trace level. Like the standard SQL_TRACE trace file. Provides statistics for parse, execute, fetch, commit and rollback database calls.
4 Displays bind variables
8 Displays wait statistics
12 Displays wait statistics and bind variables
ALTER SESSION SET
EVENTs=’10046 trace name context forever, level 12’;
Concurren tprogram trace--level 8
How To Trace a Concurrent Request And Generate TKPROF File [ID 453527.1]
TKProf Interpretation (9i and below) [ID 32951.1]
**************************************************************************
Login as Sysadmin -> System Administrator -> Concurrent : Program -> Define
Press F11 -> Enter the COncurrent Program Name (Eg. Active Users) -> Press Ctrl + F11
Enable the check box “Enable Trace” above “Copy To” button.
Click on Save.
Close the window.
Select Requests -> Run
Submit the concurrent request.
After completion or during execution of the request, you should see a trace file generated on the database server under udump directory.
SQL to identify the trace file
select oracle_process_id from fnd_concurrent_requests where request_id=[request_id];
DB Node
cd $ORACLE_HOME/admin/[SID]_[hostname]/udump
ls -latr *[oracle_process_id]*.*
1 Basic trace level. Like the standard SQL_TRACE trace file. Provides statistics for parse, execute, fetch, commit and rollback database calls.
4 Displays bind variables
8 Displays wait statistics
12 Displays wait statistics and bind variables
ALTER SESSION SET
EVENTs=’10046 trace name context forever, level 12’;
Concurren tprogram trace--level 8
How To Trace a Concurrent Request And Generate TKPROF File [ID 453527.1]
TKProf Interpretation (9i and below) [ID 32951.1]
**************************************************************************
Login as Sysadmin -> System Administrator -> Concurrent : Program -> Define
Press F11 -> Enter the COncurrent Program Name (Eg. Active Users) -> Press Ctrl + F11
Enable the check box “Enable Trace” above “Copy To” button.
Click on Save.
Close the window.
Select Requests -> Run
Submit the concurrent request.
After completion or during execution of the request, you should see a trace file generated on the database server under udump directory.
SQL to identify the trace file
select oracle_process_id from fnd_concurrent_requests where request_id=[request_id];
DB Node
cd $ORACLE_HOME/admin/[SID]_[hostname]/udump
ls -latr *[oracle_process_id]*.*
Thursday, April 8, 2010
What is enq: TX - row lock contention
Enqueues are locks that coordinate access to database resources. enq: wait event indicates that the session is waiting for a lock that is held by another session. The name of the enqueue is as part of the form enq: enqueue_type - related_details.
The V$EVENT_NAME view provides a complete list of all the enq: wait events.
TX enqueue are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.
Several Situation of TX enqueue:
--------------------------------------
1) Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.
2) Waits for TX in mode 4 can occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.
3)Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each ‘entry’ in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
Troubleshooting:
for which SQL currently is waiting to,
select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));
The blocking session is,
SQL> select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;
select a.sid, a.event, a.total_waits, a.time_waited, a.average_wait
from v$session_event a, v$session b
where time_waited > 0
and a.sid=b.sid
and b.username is not NULL
and a.event='enq: TX - row lock contention';
Select blocking_session, sid, serial#, wait_class,seconds_in_wait
From v$session where blocking_session is not NULL
order by blocking_session;
The V$EVENT_NAME view provides a complete list of all the enq: wait events.
TX enqueue are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.
Several Situation of TX enqueue:
--------------------------------------
1) Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.
2) Waits for TX in mode 4 can occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.
3)Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each ‘entry’ in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
Troubleshooting:
for which SQL currently is waiting to,
select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));
The blocking session is,
SQL> select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;
select a.sid, a.event, a.total_waits, a.time_waited, a.average_wait
from v$session_event a, v$session b
where time_waited > 0
and a.sid=b.sid
and b.username is not NULL
and a.event='enq: TX - row lock contention';
Select blocking_session, sid, serial#, wait_class,seconds_in_wait
From v$session where blocking_session is not NULL
order by blocking_session;
Saturday, January 2, 2010
CM scripts
CMCLEAN.sql Non Destructive Script to Clean Concurrent Manager Tables
ICMLOG.sql This Script will provide the location for Retrieving the Internal
Manager log file.
CMLOGS.txt Script for Retrieving Log files associated to a Concurrent Request.
CCM.sql This script will diagnose Concurrent Managers and Concurrent
Requests.
REQCHECK.sql Script for diagnosing problematic Concurrent Requests.
ANALYZEPENDING.sql Script for diagnosing Pending Concurrent Requests.
WHOCANRUN.sql Script that lists Responsibilities that can Run a Given
Concurrent Program.
ICMLOG.sql This Script will provide the location for Retrieving the Internal
Manager log file.
CMLOGS.txt Script for Retrieving Log files associated to a Concurrent Request.
CCM.sql This script will diagnose Concurrent Managers and Concurrent
Requests.
REQCHECK.sql Script for diagnosing problematic Concurrent Requests.
ANALYZEPENDING.sql Script for diagnosing Pending Concurrent Requests.
WHOCANRUN.sql Script that lists Responsibilities that can Run a Given
Concurrent Program.
Friday, January 1, 2010
How to find the versions
Q. How to find Apps Version (11i/R12/12i).
A. Connect to database as user apps
SQL> select release_name from apps.fnd_product_groups;
Output like 12.0.4 or 11.5.10.2
Q. Web Server/Apache or Application Server in Apps 11i/R12
A. Log in as Application user, set environment variable and run below query $IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version
Output for 11i should be like
Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built: Jan 26 2005 11:06:44 (iAS 1.0.2.2.2 rollup 5)
Output for R12 should be like
Server version: Oracle-Application-Server-10g/10.1.3.0.0Oracle-HTTP-Server
Server built: Dec 4 2006 14:44:38
Q. Forms & Report version (aka developer 6i) in 11i
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/f60run | grep Version | grep Forms
output like
Forms 6.0 (Forms Runtime) Version 6.0.8.25.2 (Production)
Check fourth character in version 25 which means Forms 6i patchset 16 (25-9)
Q. Forms & Report version in R12/12i
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/rwrun | grep Release
Output should be like
Report Builder: Release 10.1.2.2.0
You can safely ignore warnings
Q. Database Version in 11i/R12/12i
A. Go to database section below.
Q. Oracle Jinitiator in 11i/R12/12i
A. Log in as Application user, set environment variable and run below query
grep jinit_ver_comma $CONTEXT_FILE
(Default is Java Plug-In for R12/12i )
Q. Oracle Java Plug-in in 11i/R12/12i
A. Log in as Application user, set environment variable and run below query
grep plugin $CONTEXT_FILE
Q. File Version on file system
adident Header
or
strings | grep Header
Here adident is AD Utility (Oracle Apps) and strings is Unix utility
Q. Version of pld file
*.pld are source code of *.pll which are inturn source of *.plx. *.pll is in $AU_TOP/resource and to find its version check
adident Header $AU_TOP/resource/.pll
IGSAU012.pll:
$Header IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $
or
strings $AU_TOP/resource/.pll | grep -i header
FDRCSID(’$Header: IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $’);
Q. OA Framework Version
A.http:// hostname.domainName:port/OA_HTML/OAInfo.jsp (Only for 11i)
A. Log in as Application user, set environment variable and run below query
adident Header $FND_TOP/html/OA.jsp
adident Header $OA_HTML/OA.jsp
output for both should look like
$Header OA.jsp 115.60 2006/03/31 00:47:28 atgops1 noship $
120.21 means OA Framework Version (coming soon..)
115.60 means OA Framework Version (coming soon..)
115.56 means OA Framework Version (coming soon..)
115.36 means OA Framework Version 5.7
115.27 means OA Framework Version 5.6E
115.26 means OA Framework Version 5.5.2E
Q. Discoverer Version for 11i (3i or 4i)
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/disc4ws | grep -i Version
Q. Discoverer Version for 11i or R12 (10g AS)
Check under Application Server Section as 10g AS Discoverer is on standalone
Q. Workflow Version with Apps
A. Connect to Database as apps user
SQL> select TEXT Version from WF_RESOURCES where NAME = ‘WF_VERSION’;
Output like 2.6.0 means workflow version 2.6.0
Oracle Single Sign On
Connect to database which holds SSO repository
SQL>select version from orasso.wwc_version$;
Oracle Internet Directory
There are two component in OID (Software/binaries & Schema/database)
>>> To find software/binary version
$ORACLE_HOME/bin/oidldapd -version
output should look like
oidldapd: Release 10.1.4.0.1 - Production on mon jul 14 14:14:21 2008
Copyright (c) 1982, 2006 Oracle. All rights reserved.
To find Schema Version/ database use
ldapsearch -h -p -D “cn=orcladmin” -w “” -b “” \
-s base “objectclass=*” orcldirectoryversion
and output should be like
version: 1
dn:
orcldirectoryversion: OID 10.1.4.0.1
or run following query in database
SQL> select attrval from ods.ds_attrstore where entryid = 1 and attrname = ‘orcldirectoryversion’;
Output should be like OID 10.1.4.0.1
C. Application Server
1. Oracle Application Server 10g Rel 3 (10.1.3.X)
cat $ORACLE_HOME/config/ias.properties | grep Version
Version=10.1.3.0.0
2. For Oracle Application Server 10.1.2 (Prior to Oracle WebLogic Server)
If application server is registered in database (Portal, Discoverer) check from database
SQL> select * from ias_versions;
or
SQL>select * from INTERNET_APPSERVER_REGISTRY.SCHEMA_VERSIONS;
AOC4J (Oracle Container for J2EE)
Set ORACLE_HOME
cd $ORACLE_HOME/j2ee/home
java -jar oc4j.jar -version
Oracle Portal
SQL> select version from portal.wwc_version$;
Database Component
I) Oracle Database
To find database version
SQL> select * from v$version;
or
All component version in database
$ORACLE_HOME/OPatch/opatch lsinventory -detail
Oracle Enterprise Manager
Metalink Note 605398.1 How to to find the version of the main EM components
Unix Operating System
Solaris -> cat /etc/release
Red Hat Linux -> cat /etc/redhat-release
A. Connect to database as user apps
SQL> select release_name from apps.fnd_product_groups;
Output like 12.0.4 or 11.5.10.2
Q. Web Server/Apache or Application Server in Apps 11i/R12
A. Log in as Application user, set environment variable and run below query $IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version
Output for 11i should be like
Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built: Jan 26 2005 11:06:44 (iAS 1.0.2.2.2 rollup 5)
Output for R12 should be like
Server version: Oracle-Application-Server-10g/10.1.3.0.0Oracle-HTTP-Server
Server built: Dec 4 2006 14:44:38
Q. Forms & Report version (aka developer 6i) in 11i
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/f60run | grep Version | grep Forms
output like
Forms 6.0 (Forms Runtime) Version 6.0.8.25.2 (Production)
Check fourth character in version 25 which means Forms 6i patchset 16 (25-9)
Q. Forms & Report version in R12/12i
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/rwrun | grep Release
Output should be like
Report Builder: Release 10.1.2.2.0
You can safely ignore warnings
Q. Database Version in 11i/R12/12i
A. Go to database section below.
Q. Oracle Jinitiator in 11i/R12/12i
A. Log in as Application user, set environment variable and run below query
grep jinit_ver_comma $CONTEXT_FILE
(Default is Java Plug-In for R12/12i )
Q. Oracle Java Plug-in in 11i/R12/12i
A. Log in as Application user, set environment variable and run below query
grep plugin $CONTEXT_FILE
Q. File Version on file system
adident Header
or
strings
Here adident is AD Utility (Oracle Apps) and strings is Unix utility
Q. Version of pld file
*.pld are source code of *.pll which are inturn source of *.plx. *.pll is in $AU_TOP/resource and to find its version check
adident Header $AU_TOP/resource/
IGSAU012.pll:
$Header IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $
or
strings $AU_TOP/resource/
FDRCSID(’$Header: IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $’);
Q. OA Framework Version
A.http:// hostname.domainName:port/OA_HTML/OAInfo.jsp (Only for 11i)
A. Log in as Application user, set environment variable and run below query
adident Header $FND_TOP/html/OA.jsp
adident Header $OA_HTML/OA.jsp
output for both should look like
$Header OA.jsp 115.60 2006/03/31 00:47:28 atgops1 noship $
120.21 means OA Framework Version (coming soon..)
115.60 means OA Framework Version (coming soon..)
115.56 means OA Framework Version (coming soon..)
115.36 means OA Framework Version 5.7
115.27 means OA Framework Version 5.6E
115.26 means OA Framework Version 5.5.2E
Q. Discoverer Version for 11i (3i or 4i)
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/disc4ws | grep -i Version
Q. Discoverer Version for 11i or R12 (10g AS)
Check under Application Server Section as 10g AS Discoverer is on standalone
Q. Workflow Version with Apps
A. Connect to Database as apps user
SQL> select TEXT Version from WF_RESOURCES where NAME = ‘WF_VERSION’;
Output like 2.6.0 means workflow version 2.6.0
Oracle Single Sign On
Connect to database which holds SSO repository
SQL>select version from orasso.wwc_version$;
Oracle Internet Directory
There are two component in OID (Software/binaries & Schema/database)
>>> To find software/binary version
$ORACLE_HOME/bin/oidldapd -version
output should look like
oidldapd: Release 10.1.4.0.1 - Production on mon jul 14 14:14:21 2008
Copyright (c) 1982, 2006 Oracle. All rights reserved.
To find Schema Version/ database use
ldapsearch -h
-s base “objectclass=*” orcldirectoryversion
and output should be like
version: 1
dn:
orcldirectoryversion: OID 10.1.4.0.1
or run following query in database
SQL> select attrval from ods.ds_attrstore where entryid = 1 and attrname = ‘orcldirectoryversion’;
Output should be like OID 10.1.4.0.1
C. Application Server
1. Oracle Application Server 10g Rel 3 (10.1.3.X)
cat $ORACLE_HOME/config/ias.properties | grep Version
Version=10.1.3.0.0
2. For Oracle Application Server 10.1.2 (Prior to Oracle WebLogic Server)
If application server is registered in database (Portal, Discoverer) check from database
SQL> select * from ias_versions;
or
SQL>select * from INTERNET_APPSERVER_REGISTRY.SCHEMA_VERSIONS;
AOC4J (Oracle Container for J2EE)
Set ORACLE_HOME
cd $ORACLE_HOME/j2ee/home
java -jar oc4j.jar -version
Oracle Portal
SQL> select version from portal.wwc_version$;
Database Component
I) Oracle Database
To find database version
SQL> select * from v$version;
or
All component version in database
$ORACLE_HOME/OPatch/opatch lsinventory -detail
Oracle Enterprise Manager
Metalink Note 605398.1 How to to find the version of the main EM components
Unix Operating System
Solaris -> cat /etc/release
Red Hat Linux -> cat /etc/redhat-release
Wednesday, December 30, 2009
General Issues
1)Assigned responsibilities to that user are reflecting.
Solution :
1) Bounce the apache
and check it and other solution is
2)You need to run the following concurrent programs if you have any missing responsibilities
- "Sync responsibility role data into the WF table."
- "Synchronize WF LOCAL tables"
- "Workflow Directory Services User/Role Validation"
3) bounce the Apache after running the above programs
*********************************************************************************
Error while lauching the forms
APP-FND-01542 THe application server is not authorized to acess the database
And the modplsql log says
[Wed Sep 2 17:21:59 2009] [error] mod_plsql: /pls/VIS02/fnd_icx_launch.launch HTTP-403 ORA-1017
[Wed Sep 2 17:22:33 2006] [error] mod_plsql: /pls/VIS02/fnd_icx_launch.launch HTTP-403 ORA-1017
[Wed Sep 2 17:22:41 2006] [error] mod_plsql: /pls/VIS02/fnd_icx_launch.launch HTTP-403 ORA-1017
[Wed Sep 2 17:39:03 2006] [error] mod_plsql: /pls/VIS02/fnd_icx_launch.launch HTTP-403 ORA-1017
Solution:
Change the parameter "appserverid_authentication oa_var="s_appserverid_authentication">SECURE/appserverid_authentication"
To
"appserverid_authentication oa_var="s_appserverid_authentication">OFF/appserverid_authentication"
In the xml file
and run the autoconfig.
************************************************************************************
3) Forms not coming up
Solution
check the enteries of hostname and virtual name
"oacore_trusted_oproc_nodes oa_var="s_oacore_trusted_oproc_nodes">vis02,hostname.abcd.comhostname ************************************************************************************
Solution :
1) Bounce the apache
and check it and other solution is
2)You need to run the following concurrent programs if you have any missing responsibilities
- "Sync responsibility role data into the WF table."
- "Synchronize WF LOCAL tables"
- "Workflow Directory Services User/Role Validation"
3) bounce the Apache after running the above programs
*********************************************************************************
Error while lauching the forms
APP-FND-01542 THe application server is not authorized to acess the database
And the modplsql log says
[Wed Sep 2 17:21:59 2009] [error] mod_plsql: /pls/VIS02/fnd_icx_launch.launch HTTP-403 ORA-1017
[Wed Sep 2 17:22:33 2006] [error] mod_plsql: /pls/VIS02/fnd_icx_launch.launch HTTP-403 ORA-1017
[Wed Sep 2 17:22:41 2006] [error] mod_plsql: /pls/VIS02/fnd_icx_launch.launch HTTP-403 ORA-1017
[Wed Sep 2 17:39:03 2006] [error] mod_plsql: /pls/VIS02/fnd_icx_launch.launch HTTP-403 ORA-1017
Solution:
Change the parameter "appserverid_authentication oa_var="s_appserverid_authentication">SECURE/appserverid_authentication"
To
"appserverid_authentication oa_var="s_appserverid_authentication">OFF/appserverid_authentication"
In the xml file
and run the autoconfig.
************************************************************************************
3) Forms not coming up
Solution
check the enteries of hostname and virtual name
"oacore_trusted_oproc_nodes oa_var="s_oacore_trusted_oproc_nodes">vis02,hostname.abcd.comhostname ************************************************************************************
Thursday, October 29, 2009
How to Enable the Concurrent Program Trace
How to find the Trace file generated for a concurrent program? (Doc ID 967966.1)
Enabling Concurrent Program Traces (Doc ID 123222.1)
Login as Sysadmin -> System Administrator -> Concurrent : Program -> Define
Press F11 -> Enter the COncurrent Program Name (Eg. Active Users) -> Press Ctrl + F11
Enable the check box “Enable Trace” above “Copy To” button.
Click on Save.
Close the window.
Select Requests -> Run
Submit the concurrent request.
After completion or during execution of the request, you should see a trace file generated on the database server under udump directory.
SQL to identify the trace file
select oracle_process_id from fnd_concurrent_requests where request_id=[request_id];
or
select value from v$parameter where name = 'user_dump_dest';
select value from v$diag_info where name = 'Diag Trace'
SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name: '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
--'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module
from
fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;
DB Node
cd $ORACLE_HOME/admin/[SID]_[hostname]/udump
ls -latr *[oracle_process_id]*.*
P.S: Dont forget to disable the trace
Query to find the child requests from parent requests
SELECT req.* FROM (SELECT con.user_concurrent_program_name ,req.request_id ,parent_request_id ,req.phase_code ,req.status_code FROM apps.fnd_concurrent_requests req ,apps.fnd_concurrent_programs_vl con WHERE 1 = 1 AND con.concurrent_program_id = req.concurrent_program_id ) req_set, (SELECT con.user_concurrent_program_name ,req.request_id ,req.phase_code ,req.request_date ,req.requested_start_date ,req.actual_completion_date ,req.status_code, parent_request_id FROM apps.fnd_concurrent_requests req ,apps.fnd_concurrent_programs_vl con WHERE 1 = 1 AND con.concurrent_program_id = req.concurrent_program_id ) req WHERE req_set.parent_request_id = 5337352 -- Request id of the submitted request set. AND req.parent_request_id = req_set.request_id order by requested_start_date desc;
Enabling Concurrent Program Traces (Doc ID 123222.1)
Login as Sysadmin -> System Administrator -> Concurrent : Program -> Define
Press F11 -> Enter the COncurrent Program Name (Eg. Active Users) -> Press Ctrl + F11
Enable the check box “Enable Trace” above “Copy To” button.
Click on Save.
Close the window.
Select Requests -> Run
Submit the concurrent request.
After completion or during execution of the request, you should see a trace file generated on the database server under udump directory.
SQL to identify the trace file
select oracle_process_id from fnd_concurrent_requests where request_id=[request_id];
or
select value from v$parameter where name = 'user_dump_dest';
select value from v$diag_info where name = 'Diag Trace'
SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name: '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
--'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module
from
fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;
DB Node
cd $ORACLE_HOME/admin/[SID]_[hostname]/udump
ls -latr *[oracle_process_id]*.*
P.S: Dont forget to disable the trace
Query to find the child requests from parent requests
SELECT req.* FROM (SELECT con.user_concurrent_program_name ,req.request_id ,parent_request_id ,req.phase_code ,req.status_code FROM apps.fnd_concurrent_requests req ,apps.fnd_concurrent_programs_vl con WHERE 1 = 1 AND con.concurrent_program_id = req.concurrent_program_id ) req_set, (SELECT con.user_concurrent_program_name ,req.request_id ,req.phase_code ,req.request_date ,req.requested_start_date ,req.actual_completion_date ,req.status_code, parent_request_id FROM apps.fnd_concurrent_requests req ,apps.fnd_concurrent_programs_vl con WHERE 1 = 1 AND con.concurrent_program_id = req.concurrent_program_id ) req WHERE req_set.parent_request_id = 5337352 -- Request id of the submitted request set. AND req.parent_request_id = req_set.request_id order by requested_start_date desc;
Wednesday, July 29, 2009
CPU Utilization check
1) % ulimit -Sa ,SVMON,
2)CPU utilization
OS Command
~~~~~~~~~~ ~~~~~~~~~
Sun Solaris: sar -u
IBM: ps av (or) iostat 3 20
Digital: /usr/sbin/pset_info
Linux: xosview
In addition of monitoring the CPU usage you should monitor the runqueue to determine if
processes are waiting for an available processor.
You can use "sar -q" to monitor the runqueue.
3)CPU usage by processor
To see how much CPU time is being used by each processor on a multiprocessor machine:
OS Command
~~~~~~~~~~ ~~~~~~~~~
Sun Solaris: /usr/bin/mpstat
HP: /usr/sbin/sar -M 5 5
IBM: vmstat -> Under cpu, if "us" is a very high number you have a cpu intensive process
4)Number of CPU's
To see the number of CPUs there are in the machine, and their status:
OS Command
~~~~~~~~~~ ~~~~~~~~~
Sun Solaris: /usr/sbin/mpstat
HP: /usr/sbin/sar -M 2 2
IBM: /usr/sbin/bindprocessor -q
Digital: /usr/sbin/psrinfo -v
Linux: xosview
5)Swap space
To see the amount of swap space is on the machine and the usage:
OS Command
~~~~~~~~~~ ~~~~~~~~~
Sun Solaris: /etc/swap -l and /etc/swap -s
HP: /etc/swapinfo -m ( must be root Unix id)
IBM: lsps -a
Digital: /usr/sbin/swapon -s
Linux: free -t
6)Shared memory
To see the current usage of shared memory & semaphores:
"ipcs -b"
2)CPU utilization
OS Command
~~~~~~~~~~ ~~~~~~~~~
Sun Solaris: sar -u
IBM: ps av (or) iostat 3 20
Digital: /usr/sbin/pset_info
Linux: xosview
In addition of monitoring the CPU usage you should monitor the runqueue to determine if
processes are waiting for an available processor.
You can use "sar -q" to monitor the runqueue.
3)CPU usage by processor
To see how much CPU time is being used by each processor on a multiprocessor machine:
OS Command
~~~~~~~~~~ ~~~~~~~~~
Sun Solaris: /usr/bin/mpstat
HP: /usr/sbin/sar -M 5 5
IBM: vmstat -> Under cpu, if "us" is a very high number you have a cpu intensive process
4)Number of CPU's
To see the number of CPUs there are in the machine, and their status:
OS Command
~~~~~~~~~~ ~~~~~~~~~
Sun Solaris: /usr/sbin/mpstat
HP: /usr/sbin/sar -M 2 2
IBM: /usr/sbin/bindprocessor -q
Digital: /usr/sbin/psrinfo -v
Linux: xosview
5)Swap space
To see the amount of swap space is on the machine and the usage:
OS Command
~~~~~~~~~~ ~~~~~~~~~
Sun Solaris: /etc/swap -l and /etc/swap -s
HP: /etc/swapinfo -m ( must be root Unix id)
IBM: lsps -a
Digital: /usr/sbin/swapon -s
Linux: free -t
6)Shared memory
To see the current usage of shared memory & semaphores:
"ipcs -b"
Tuesday, June 16, 2009
ADPATCH Part-2
1. Using the adctrl utility, shutdown the workers. a. adctrl
b. Select option 3 "Tell worker to shutdown/quit"
2. Backup the FND_INSTALL_PROCESSES/AD_DEFERRED_JOBS tables which is owned by the
APPLSYS schema
sqlplus applsys/apps
create table fnd_Install_processes_back as select * from
fnd_Install_processes;
select count(*) from fnd_Install_processes_back;
select count(*) from fnd_Install_processes;
create table AD_DEFERRED_JOBS_back as select * from AD_DEFERRED_JOBS;
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;
3. Backup the .rf9 files located in $APPL_TOP/admin//restart directory. At this point, the
adpatch session should have ended and the cursor should be back at the UNIX prompt.
cd $APPL_TOP/admin/DEVL
mv restart restart_back
mkdir restart
4. Drop the FND_INSTALL_PROCESSES table and the AD_DEFFERED_JOBS table.
sqlplus applsys/apps
drop table FND_INSTALL_PROCESSES;
drop table AD_DEFERRED_JOBS;
5. Apply the new patch.
6. Restore the .rf9 files located in $APPL_TOP/admin//restart_back
cd $APPL_TOP/admin/DEVL
mv restart restart_3263588
mv restart_back restart
7. Restore the FND_INSTALL_PROCESSES/AD_DEFERRED_JOBS tables which is owned by the
APPLSYS schema.
sqlplus applsys/apps
create table fnd_Install_processes as select * from
fnd_Install_processes_back;
select count(*) from fnd_Install_processes;
select count(*) from fnd_Install_processes_back;
create table AD_DEFERRED_JOBS as select * from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;
8. Re-create synonyms
sqlplus apps/apps
create synonym AD_DEFERRED_JOBS for APPLSYS.AD_DEFERRED_JOBS;
create synonym FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES;
9. Start adpatch, it will resume where it stopped previously.
How do you determine what patch set you are currently utilizing?
select pa_server_pkg.get_patch_level from dual;
b. Select option 3 "Tell worker to shutdown/quit"
2. Backup the FND_INSTALL_PROCESSES/AD_DEFERRED_JOBS tables which is owned by the
APPLSYS schema
sqlplus applsys/apps
create table fnd_Install_processes_back as select * from
fnd_Install_processes;
select count(*) from fnd_Install_processes_back;
select count(*) from fnd_Install_processes;
create table AD_DEFERRED_JOBS_back as select * from AD_DEFERRED_JOBS;
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;
3. Backup the .rf9 files located in $APPL_TOP/admin/
adpatch session should have ended and the cursor should be back at the UNIX prompt.
cd $APPL_TOP/admin/DEVL
mv restart restart_back
mkdir restart
4. Drop the FND_INSTALL_PROCESSES table and the AD_DEFFERED_JOBS table.
sqlplus applsys/apps
drop table FND_INSTALL_PROCESSES;
drop table AD_DEFERRED_JOBS;
5. Apply the new patch.
6. Restore the .rf9 files located in $APPL_TOP/admin/
cd $APPL_TOP/admin/DEVL
mv restart restart_3263588
mv restart_back restart
7. Restore the FND_INSTALL_PROCESSES/AD_DEFERRED_JOBS tables which is owned by the
APPLSYS schema.
sqlplus applsys/apps
create table fnd_Install_processes as select * from
fnd_Install_processes_back;
select count(*) from fnd_Install_processes;
select count(*) from fnd_Install_processes_back;
create table AD_DEFERRED_JOBS as select * from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;
8. Re-create synonyms
sqlplus apps/apps
create synonym AD_DEFERRED_JOBS for APPLSYS.AD_DEFERRED_JOBS;
create synonym FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES;
9. Start adpatch, it will resume where it stopped previously.
How do you determine what patch set you are currently utilizing?
select pa_server_pkg.get_patch_level from dual;
- 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,PA,… - Script to find the patch timeings
SELECT to_char(drivers.LAST_UPDATE_DATE, 'yyyy-mm-dd') install_date,
bug.BUG_NUMBER,
patches.patch_type,
drivers.PLATFORM,
langs.language,
drivers.DRIVER_FILE_NAME,
to_char(runs.start_date, 'HH24":"MI":"SS') start_time,
to_char(runs.end_date, 'HH24":"MI":"SS') end_time,
to_char(to_date('00:00:00','HH24:MI:SS') + (runs.end_date - runs.start_date), 'HH24:MI:SS') time_difference,
(to_number(to_char(to_date('00','HH24') + (runs.end_date - runs.start_date), 'HH24'))*60) +
(to_number(to_char(to_date('00','MI') + (runs.end_date - runs.start_date), 'MI'))) +
(to_number(to_char(to_date('00','SS') + (runs.end_date - runs.start_date), 'SS')) / 60) time_dif_min
FROM apps.ad_bugs bug,
apps.ad_patch_drivers drivers,
apps.ad_patch_driver_langs langs,
apps.ad_applied_patches patches,
apps.ad_patch_runs runs
WHERE langs.patch_driver_id = drivers.patch_driver_id AND
runs.patch_driver_id=drivers.patch_driver_id AND
patches.APPLIED_PATCH_ID=drivers.APPLIED_PATCH_ID AND
patches.patch_name=bug.bug_number AND
drivers.orig_patch_name = 'bug_' || bug.bug_number
and bug_number like '%patch_number%'
ORDER BY install_date ASC, start_time ASC;
to merge the patch
admrgpch -s /patches/madhu/M2/source -d /patches/madhu/M2/dest -merge_name M2P
Wednesday, April 1, 2009
Script to find long running requests
#!/bin/ksh
echo "Program Start Date: " `date`
if [ `whoami` != 'oracle' ]
then
echo "Error: You must be oracle to execute the script. Exiting."
exit
fi
########################################################################
# Get the list of programs running for more than 4 hours
########################################################################
export ORACLE_HOME=/opt/oracle/products/9.2.0.x
export ORACLE_SID=ERPPROD
export PATH=$ORACLE_HOME/bin:$PATH
ORA_TEST=`sqlplus "/as sysdba" <
set pagesize 60
set feed off
set head off
SELECT 'Concurrent Program: '||t.user_concurrent_program_name||' ,With Req ID: '||r.REQUEST_ID||' ,Started at: '||to_char(v.actual_start_date,'dd/mm/yy hh24:mi:ss')||' ,With Parameters: '||v.argument_text||' ,Running for: '||round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24))||'Hours'
FROM apps.fnd_concurrent_requests r , apps.fnd_concurrent_programs p , apps.fnd_concurrent_programs_tl t, apps.fnd_conc_req_summary_v v
WHERE r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
AND r.actual_start_date >= (sysdate -1)
AND r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID
AND t.concurrent_program_id=r.concurrent_program_id
AND r.status_Code='R' and r.phase_code='R'
AND v.request_id=r.request_id
AND round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24)) > 4
order by v.actual_start_date
.
spool $ORACLE_HOME/rdbms/log/LongRunningJobs.lst
/
spool off
exit
EOF`
JobExists=`grep "Concurrent Program" $ORACLE_HOME/rdbms/log/LongRunningJobs.lst | wc -l `
##########################################################################
# Send mail if there are programs which are running for more than 4 hours
##########################################################################
if [ $JobExists -ne 0 ]
then
cat $ORACLE_HOME/rdbms/log/LongRunningJobs.lst | /usr/bin/mailx -s "Long Running Jobs in ERPPROD" ...
fi
echo "Program Start Date: " `date`
if [ `whoami` != 'oracle' ]
then
echo "Error: You must be oracle to execute the script. Exiting."
exit
fi
########################################################################
# Get the list of programs running for more than 4 hours
########################################################################
export ORACLE_HOME=/opt/oracle/products/9.2.0.x
export ORACLE_SID=ERPPROD
export PATH=$ORACLE_HOME/bin:$PATH
ORA_TEST=`sqlplus "/as sysdba" <
set pagesize 60
set feed off
set head off
SELECT 'Concurrent Program: '||t.user_concurrent_program_name||' ,With Req ID: '||r.REQUEST_ID||' ,Started at: '||to_char(v.actual_start_date,'dd/mm/yy hh24:mi:ss')||' ,With Parameters: '||v.argument_text||' ,Running for: '||round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24))||'Hours'
FROM apps.fnd_concurrent_requests r , apps.fnd_concurrent_programs p , apps.fnd_concurrent_programs_tl t, apps.fnd_conc_req_summary_v v
WHERE r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
AND r.actual_start_date >= (sysdate -1)
AND r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID
AND t.concurrent_program_id=r.concurrent_program_id
AND r.status_Code='R' and r.phase_code='R'
AND v.request_id=r.request_id
AND round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24)) > 4
order by v.actual_start_date
.
spool $ORACLE_HOME/rdbms/log/LongRunningJobs.lst
/
spool off
exit
EOF`
JobExists=`grep "Concurrent Program" $ORACLE_HOME/rdbms/log/LongRunningJobs.lst | wc -l `
##########################################################################
# Send mail if there are programs which are running for more than 4 hours
##########################################################################
if [ $JobExists -ne 0 ]
then
cat $ORACLE_HOME/rdbms/log/LongRunningJobs.lst | /usr/bin/mailx -s "Long Running Jobs in ERPPROD"
fi
Monday, February 16, 2009
STATS PACK
•Statspack collects more data, including high-resource SQL.
•Statspack precalculates many ratios useful when performance tuning, such as cache hit ratios, rates, and transaction statistics. (Many of these ratios must be calculated manually when using BSTAT/ESTAT).
•Permanent tables owned by PERFSTAT store performance statistics. Instead of creating/dropping tables each time, data is inserted into the preexisting tables. This makes it easier to compare historical data.
•Statspack separates data collection from report generation. Data is collected when a snapshot is taken. The performance engineer then runs the performance report and views the data collected.
•Data collection is easy to automate using either DBMS_JOB or an operating system utility.
•Statspack considers a transaction to finish either with a COMMIT or a ROLLBACK, and so calculates the number of transactions as 'user commits' + 'user rollbacks.' BSTAT/ESTAT considers a transaction to complete with a COMMIT only, and so assumes that transactions = 'user commits.' For this reason, comparing statistics for each transaction between Statspack and BSTAT/ESTAT can result in significantly different ratios.
The installation steps for STATSPACK are relatively simple and straightforward. This chapter will walk you, step by step, through the installation and configuration of STATSPACK. We will be including a complete description of the following steps:
•Creating the perfstat tablespace
•Creating the perfstat user
•Creating the tables
•Installing the packages
•Collecting data
•Scheduling data collection snapshots with dbms_job
•Testing your installation
Let's begin by reviewing the functions of each of these files. Several of the files call subfiles, so it helps if we organize the files as a hierarchy:
•spcreate.sql This is the first install script run after you create the tablespace. It calls several subscripts:
•spcsr.sql This script creates a user called PERFSTAT with the required permissions.
•spctab.sql This creates the STATSPACK tables and indexes, owned by the PERFSTAT user.
•spcpkg.sql This creates the PL/SQL package called STATSPACK with the STATSPACK procedures.
•spauto.sql This script contains the dbms_job.submit commands that will execute a STATSPACK snapshot every hour.
•spdrop.sql This script is used to drop all STATSPACK entities. This script calls these subscripts:
•spdusr.sql This script drops the PERFSTAT user.
•spdoc.txt This is a generic read-me file explaining the installation and operation of the STATSPACK utility.
•spreport.sql This is the shell for the only report provided in STATSPACK. It prompts you for the start and end snapshots, and then produces an elapsed-time report.
•sprepins.sql This is the actual SQL that produces the STATSPACK report.
•sppurge.sql This is a script to delete older unwanted snapshots.
•spuexp.par This is a export parameter file to export all of the STATSPACK data.
•sptrunc.sql This is a script to truncate all STATSPACK tables.
•spup816.sql This is a script to upgrade pre-8.1.7 STATSPACK tables to use the latest schema. Note that you must export the STATSPACK schema before running this script.
•spup817.sql This is a script to upgrade to Oracle 8.1.7 from Oracle 8.1.6
How to run the report
sql >variable snapno number; (eg :run the script at 6 clk )
sql > begin :snapno:=statspack.snap; end;
sql>print snapno; ( this may give the value like 1 if you are executing the
statspack procedure in the database for the first time..)
runit again as per time required time ( run it at 7 clk)
sql > begin :snapno:=statspack.snap; end;
sql>print snapno; ( this may give the value like 2 )
Now we have two snap numbers (these are called as begin and end snap numbers
which you will be entering when you are running the spreport.sql.)
sql >oracle_home/ora90/rdbms/admin/spreport.sql;
this will ask you to enter the begin and end snap numbers.(enter as 1 and 2
for the begin and end snap numbers..)
•Statspack precalculates many ratios useful when performance tuning, such as cache hit ratios, rates, and transaction statistics. (Many of these ratios must be calculated manually when using BSTAT/ESTAT).
•Permanent tables owned by PERFSTAT store performance statistics. Instead of creating/dropping tables each time, data is inserted into the preexisting tables. This makes it easier to compare historical data.
•Statspack separates data collection from report generation. Data is collected when a snapshot is taken. The performance engineer then runs the performance report and views the data collected.
•Data collection is easy to automate using either DBMS_JOB or an operating system utility.
•Statspack considers a transaction to finish either with a COMMIT or a ROLLBACK, and so calculates the number of transactions as 'user commits' + 'user rollbacks.' BSTAT/ESTAT considers a transaction to complete with a COMMIT only, and so assumes that transactions = 'user commits.' For this reason, comparing statistics for each transaction between Statspack and BSTAT/ESTAT can result in significantly different ratios.
The installation steps for STATSPACK are relatively simple and straightforward. This chapter will walk you, step by step, through the installation and configuration of STATSPACK. We will be including a complete description of the following steps:
•Creating the perfstat tablespace
•Creating the perfstat user
•Creating the tables
•Installing the packages
•Collecting data
•Scheduling data collection snapshots with dbms_job
•Testing your installation
Let's begin by reviewing the functions of each of these files. Several of the files call subfiles, so it helps if we organize the files as a hierarchy:
•spcreate.sql This is the first install script run after you create the tablespace. It calls several subscripts:
•spcsr.sql This script creates a user called PERFSTAT with the required permissions.
•spctab.sql This creates the STATSPACK tables and indexes, owned by the PERFSTAT user.
•spcpkg.sql This creates the PL/SQL package called STATSPACK with the STATSPACK procedures.
•spauto.sql This script contains the dbms_job.submit commands that will execute a STATSPACK snapshot every hour.
•spdrop.sql This script is used to drop all STATSPACK entities. This script calls these subscripts:
•spdusr.sql This script drops the PERFSTAT user.
•spdoc.txt This is a generic read-me file explaining the installation and operation of the STATSPACK utility.
•spreport.sql This is the shell for the only report provided in STATSPACK. It prompts you for the start and end snapshots, and then produces an elapsed-time report.
•sprepins.sql This is the actual SQL that produces the STATSPACK report.
•sppurge.sql This is a script to delete older unwanted snapshots.
•spuexp.par This is a export parameter file to export all of the STATSPACK data.
•sptrunc.sql This is a script to truncate all STATSPACK tables.
•spup816.sql This is a script to upgrade pre-8.1.7 STATSPACK tables to use the latest schema. Note that you must export the STATSPACK schema before running this script.
•spup817.sql This is a script to upgrade to Oracle 8.1.7 from Oracle 8.1.6
How to run the report
sql >variable snapno number; (eg :run the script at 6 clk )
sql > begin :snapno:=statspack.snap; end;
sql>print snapno; ( this may give the value like 1 if you are executing the
statspack procedure in the database for the first time..)
runit again as per time required time ( run it at 7 clk)
sql > begin :snapno:=statspack.snap; end;
sql>print snapno; ( this may give the value like 2 )
Now we have two snap numbers (these are called as begin and end snap numbers
which you will be entering when you are running the spreport.sql.)
sql >oracle_home/ora90/rdbms/admin/spreport.sql;
this will ask you to enter the begin and end snap numbers.(enter as 1 and 2
for the begin and end snap numbers..)
Tuesday, February 3, 2009
How does one determine the table and column name for a field displayed in a form
1. Use Help > Record History
- Query the records in a given form
- Place the cursor on a field that you are interested in
- Choose Help > Record History
- The name of the base view used displays as Table Name:
- Go to the Sql*Plus
- Type desc , for example, desc mtl_system_items_fvl
- The columns of the table are displayed
- Review the columns and see if one matches the field that you are reviewing
2. Use Help > Diagnostics > Examine
- Query the records in a given form
- Place the cursor on a field that you are interested in
- Choose Help > Diagnostics > Examine
- The block and field name that contain the data in the form are displayed as
well as the value in the field.
- Many times the block name is similar to the table name in the database
- Many times the field name is similar to the column name in the database
- Also you can go back to the view in #1 and see if the column can be found
3. Use the eTRM
- Find the table name from the record history in #1
- You may still require additional information about the columns
- Goto http://metalink.oracle.com/ > Top Tech Docs > Applications Electronic
Technical Reference Manuals
- Choose your version (ie., 11.5.9)
- Type the view or table name as the search criteria
- Press search
- The view or table appears
- Press the link and get more details and descriptions about the columns in the table
4. Research the view
- Sometimes the steps in #1 and #2 lead you to a view
- Get more details about the view as well as the underlying tables and columns
with the following SQL:
set long 100000
col text format a70
set pages 100
spool view.lst
SELECT VIEW_NAME, TEXT
FROM DBA_VIEWS WHERE VIEW_NAME = UPPER('&VIEW_NAME')
and owner = 'APPS';
spool off
5. Use SYSTEM.LAST_QUERY
- Query the records in a given form
- Choose Help > Diagnostics > Examine > Block = SYSTEM, Field= LAST_QUERY
- The query run to get the data shown is displayed.
- You can look at the various columns and tables used and research further
6. Run a trace
- Open the form that you are interested in but do not run a query
- Choose Help > Diagnostics > Trace > Trace with binds
- The trace file name and location are listed. Note them down
- Query the records
- Immediately shut trace off with Help > Diagnostics > Trace > No Trace
- The trace file name and location are listed
- Go to the database machine and get the trace file
- Create a tkprof of the trace file with the command, tkprof sys=no
- The various queries run are displayed.
- You can look at the various columns and tables used and research further
7. Open the form in Oracle Developer
- Open the form that you are interested in the Oracle Applications
- Choose Help > About
- Note the form name
- You have to first configure Oracle Developer to open forms with their
associated libraries
- Once this is done Open Developer
- Open the form in question
- Review the block, pl/sql procedures and libraries associated to the form
- Query the records in a given form
- Place the cursor on a field that you are interested in
- Choose Help > Record History
- The name of the base view used displays as Table Name:
- Go to the Sql*Plus
- Type desc , for example, desc mtl_system_items_fvl
- The columns of the table are displayed
- Review the columns and see if one matches the field that you are reviewing
2. Use Help > Diagnostics > Examine
- Query the records in a given form
- Place the cursor on a field that you are interested in
- Choose Help > Diagnostics > Examine
- The block and field name that contain the data in the form are displayed as
well as the value in the field.
- Many times the block name is similar to the table name in the database
- Many times the field name is similar to the column name in the database
- Also you can go back to the view in #1 and see if the column can be found
3. Use the eTRM
- Find the table name from the record history in #1
- You may still require additional information about the columns
- Goto http://metalink.oracle.com/ > Top Tech Docs > Applications Electronic
Technical Reference Manuals
- Choose your version (ie., 11.5.9)
- Type the view or table name as the search criteria
- Press search
- The view or table appears
- Press the link and get more details and descriptions about the columns in the table
4. Research the view
- Sometimes the steps in #1 and #2 lead you to a view
- Get more details about the view as well as the underlying tables and columns
with the following SQL:
set long 100000
col text format a70
set pages 100
spool view.lst
SELECT VIEW_NAME, TEXT
FROM DBA_VIEWS WHERE VIEW_NAME = UPPER('&VIEW_NAME')
and owner = 'APPS';
spool off
5. Use SYSTEM.LAST_QUERY
- Query the records in a given form
- Choose Help > Diagnostics > Examine > Block = SYSTEM, Field= LAST_QUERY
- The query run to get the data shown is displayed.
- You can look at the various columns and tables used and research further
6. Run a trace
- Open the form that you are interested in but do not run a query
- Choose Help > Diagnostics > Trace > Trace with binds
- The trace file name and location are listed. Note them down
- Query the records
- Immediately shut trace off with Help > Diagnostics > Trace > No Trace
- The trace file name and location are listed
- Go to the database machine and get the trace file
- Create a tkprof of the trace file with the command, tkprof sys=no
- The various queries run are displayed.
- You can look at the various columns and tables used and research further
7. Open the form in Oracle Developer
- Open the form that you are interested in the Oracle Applications
- Choose Help > About
- Note the form name
- You have to first configure Oracle Developer to open forms with their
associated libraries
- Once this is done Open Developer
- Open the form in question
- Review the block, pl/sql procedures and libraries associated to the form
Profile option for change of settings like color etc
Change the "Corporate Branding Image for Oracle Applications / FND_CORPORATE_BRANDING_IMAGE" profile option to achieve this.
Refer to the following notes for more details:
Note: 551795.1 - How to change the default branding on the homepage which shown as "E-Business Suite" ?
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=551795.1
Note: 395445.1 - Oracle Application Framework Profile Options Release 12
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=395445.1
Note: 421636.1 - How to replace the default Oracle Logo with a Customized Logo?
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=421636.1
Note: 459350.1 - R12 - Image Size Problem On Login Page
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=459350.1
Refer to the following notes for more details:
Note: 551795.1 - How to change the default branding on the homepage which shown as "E-Business Suite" ?
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=551795.1
Note: 395445.1 - Oracle Application Framework Profile Options Release 12
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=395445.1
Note: 421636.1 - How to replace the default Oracle Logo with a Customized Logo?
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=421636.1
Note: 459350.1 - R12 - Image Size Problem On Login Page
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=459350.1
Wednesday, January 21, 2009
Session Trace
How to enable trace at database level?
Ans : set init.ora parameter sql_trace
How to enable trace for a session?
Ans: Alter system set sql_trace=true;
Execute the sql query
Alter system set sql_trace=false;
This will create a trace file at $RDBMS_ORACLE_HOME/admin/contextname/udump with the spid of the current sql session.
How to enable trace for other session?
Ans : exec sys.dbms_system.set_sql_trace_in_session(sid,serial#,true/false)
Eg: To enable trace for sql session with sid 8
SQL> exec sys.dbms_system.set_sql_trace_in_session(8,121,true);
PL/SQL procedure successfully completed.
To disable trace
SQL> exec sys.dbms_system.set_sql_trace_in_session(8,121,false);
What is the location of inint.ora ?
Ans : $RDBMS_ORACLE_HOME/dbs
What is that trace files contains and the utiliy used to read them?
Ans : Trace file contains the detail diagnostics of a sql statement like explain plan, physical reads, logical reads, buffer gets etc. Tkprof utility is used to convert trace file into readable format.
How to find trace file for a given concurrent request id?
Ans : Go to $RDBMS_ORACLE_HOME/admin//udump
Grep “ “ *
How does one enable trace in the Oracle Application screens / forms?
One can enable trace through the forms by using the Help menu, choosing the daignostics menu,
trace and then selecting the appropriate trace for your needs. Most commonly if debugging an error,
you should at least provide trace with binds.
When debugging a performance issue,
you may consider using trace with binds and waits.
For example, the following is the navigation to enable trace in a form:
Goto the Oracle Applications
Login
Open the form where the error occurs but do not yet cause the error.
Enable SQL*Trace by choosing Help > Diagnostics > Trace > Trace with binds
A message appears indicating that the trace will be recorded
Note the file name and location of the file
Now reproduce the error.
Once the error occurs, disable trace as soon as possible.
Disable SQL*Trace by choosing Help > Diagnostics > Trace > Trace off
The same file name and location will be noted in case you need it again.
Retrieve the trace file.
How does one enable trace for a concurrent program?
A simple way to enable trace in a concurrent program is
to review the concurrent program definition and select trace enabled.
This will enable trace without binds for all users that run the program.
For example, the following steps could be used.
Goto Sysadmin > Concurrent > Program > Define
Query the concurrent program
Check the trace box to enable trace
Ans : set init.ora parameter sql_trace
How to enable trace for a session?
Ans: Alter system set sql_trace=true;
Execute the sql query
Alter system set sql_trace=false;
This will create a trace file at $RDBMS_ORACLE_HOME/admin/contextname/udump with the spid of the current sql session.
How to enable trace for other session?
Ans : exec sys.dbms_system.set_sql_trace_in_session(sid,serial#,true/false)
Eg: To enable trace for sql session with sid 8
SQL> exec sys.dbms_system.set_sql_trace_in_session(8,121,true);
PL/SQL procedure successfully completed.
To disable trace
SQL> exec sys.dbms_system.set_sql_trace_in_session(8,121,false);
What is the location of inint.ora ?
Ans : $RDBMS_ORACLE_HOME/dbs
What is that trace files contains and the utiliy used to read them?
Ans : Trace file contains the detail diagnostics of a sql statement like explain plan, physical reads, logical reads, buffer gets etc. Tkprof utility is used to convert trace file into readable format.
How to find trace file for a given concurrent request id?
Ans : Go to $RDBMS_ORACLE_HOME/admin/
Grep “
How does one enable trace in the Oracle Application screens / forms?
One can enable trace through the forms by using the Help menu, choosing the daignostics menu,
trace and then selecting the appropriate trace for your needs. Most commonly if debugging an error,
you should at least provide trace with binds.
When debugging a performance issue,
you may consider using trace with binds and waits.
For example, the following is the navigation to enable trace in a form:
Goto the Oracle Applications
Login
Open the form where the error occurs but do not yet cause the error.
Enable SQL*Trace by choosing Help > Diagnostics > Trace > Trace with binds
A message appears indicating that the trace will be recorded
Note the file name and location of the file
Now reproduce the error.
Once the error occurs, disable trace as soon as possible.
Disable SQL*Trace by choosing Help > Diagnostics > Trace > Trace off
The same file name and location will be noted in case you need it again.
Retrieve the trace file.
How does one enable trace for a concurrent program?
A simple way to enable trace in a concurrent program is
to review the concurrent program definition and select trace enabled.
This will enable trace without binds for all users that run the program.
For example, the following steps could be used.
Goto Sysadmin > Concurrent > Program > Define
Query the concurrent program
Check the trace box to enable trace
Tuesday, January 20, 2009
unix level performane check
vmstat
$ vmstat 5 3
Displays system statistics (5 seconds apart; 3 times):
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr s0 s1 s2 s3 in sy cs us sy id
0 0 0 28872 8792 8 5 172 142 210 0 24 3 11 17 2 289 1081 201 14 6 80
0 0 0 102920 1936 1 95 1936 302 1264 235 12 1 0 3 240 459 211 0 2 97
0 0 0 102800 1960 0 0 0 0 0 464 0 0 0 0 0 107 146 29 0 0 100
Having any processes in the b or w columns is a sign of a problem system.
Having an id of 0 is a sign that the cpu is overburdoned.
Having high values in pi and po show excessive paging.
• procs (Reports the number of processes in each of the following states)
• r : in run queue b : blocked for resources (I/O, paging etc.)
• w : runnable but swapped
• memory (Reports on usage of virtual and real memory)
• swap : swap space currently available (Kbytes)
• free : size of free list (Kbytes)
• page (Reports information about page faults and paging activity (units per second)
• re : page reclaims mf : minor faults pi : Kbytes paged in po : Kbytes paged out
• fr : Kbytes freed de : anticipated short-term memory shortfall (Kbytes)
• sr : pages scanned by clock algorith
• disk (Reports the number of disk operations per second for up to 4 disks
• faults (Reports the trap/interupt rates (per second)
• in : (non clock) device interupts si : system calls
• cs : CPU context switches
• cpu (Reports the breakdown of percentage usage of CPU time (averaged across all CPUs)
• us : user time si : system time cs : idle time
CPU Usage
sar
$ sar -u 10 8
Reports CPU Utilization (10 seconds apart; 8 times):
Time %usr %sys %wio %idle
11:57:31 72 28 0 0
11:57:41 70 30 0 0
11:57:51 70 30 0 0
11:58:01 68 32 0 0
11:58:11 67 33 0 0
11:58:21 65 28 0 7
11:58:31 73 27 0 0
11:58:41 69 31 0 0
Average 69 30 0 1
%usr: Percent of CPU in user mode
%sys: Percent of CPU in system mode
%wio: Percent of CPU running idle with a process waiting for block I/O
%idle: Percent of CPU that is idle
mpstat
$ mpstat 10 2
Reports per-processor statistics on Sun Solaris (10 seconds apart; 8 times):
CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl
0 6 8 0 438 237 246 85 0 0 21 8542 23 9 9 59
0 0 29 0 744 544 494 206 0 0 95 110911 65 29 6 0
ps
$ ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -21r
Displays the top 20 CPU users on the system.
%CPU PID USER COMMAND
78.1 4789 oracle ora_dbwr_DDDS2
8.5 4793 oracle ora_lgwr_DDDS2
2.4 6206 oracle oracleDDDS2 (LOCAL=NO)
0.1 4797 oracle ora_smon_DDDS2
0.1 6207 oracle oracleDDDS2 (LOCAL=NO)
etc. etc. etc. etc.
The PID column can then be matched with the SPID column on the V$PROCESS view to provide more information on the process:
SELECT a.username,a.osuser,a.program,spid,sid,a.serial#
FROM v$session a,v$process b
WHERE a.paddr = b.addr AND spid ='&pid';
tar
The tar command can be used to backup and restore files to another filesystem or an offile storage device:
# Create archive.
cd /u01/app/oracle
tar -cvf /tmp/admin.tar admin
# Restore archive.
cd /tmp
tar -xvf admin.tar
If a full path is used during the archive creation the extract locations are fixed rather than relative. The process is similar when accessing a tape device except the destination is the mounted device:
# Mount and rewind the tape.
mt -f /dev/rmt/2m rew
# Create archive.
tar -cvf /dev/rmt/2m /u01/*
# Restore archive.
tar -xvf /dev/rmt/2m
dd
The dd command is similar to the tar command:
# Mount and rewind the tape.
mt -f /dev/rmt/2m rew
# Create archive.
dd if=/u01/app/oracle/* of=/dev/rmt/2m BS=32K
# Restore archive.
dd if=/dev/rmt/2m of=/u01/app/oracle BS=32K
cpio
The cpio command deals with the standard input so filesystem paths must be piped to it:
# Create archive.
cd /u01/app/oracle
find admin | cpio -oc > /tmp/admin.cpio
# Restore archive.
cd /tmp
cpio -idmv < admin.cpio
If a full path is used during the archive creation the extract locations are fixed rather than relative:
find /u01/app/oracle/admin | cpio -oc > /tmp/admin.cpio
vdump, rvdump, vrestore and rvrestore
Full level 0 backup of a local filesystem (/u01) to a local device (/dev/tape/tape1_d6):
/sbin/vdump -0 -u -f /dev/tape/tape1_d6 /u01
Full level 0 backup of a local filesystem (/u01) to a remote device (server2:/dev/tape/tape1_d6):
/sbin/rvdump -0 -u -f server2:/dev/tape/tape1_d6 /u01
Restore a vdump or rvdump archive from a local device (/dev/tape/tape1_d6) to a local filesystem (/u01):
/sbin/vrestore -xf /dev/tape/tape1_d6 -D /u01
Restore a vdump or rvdump archive from a remote device (server2:/dev/tape/tape1_d6) to a local filesystem (/u01):
/sbin/rvrestore -xf server2:/dev/tape/tape1_d6 -D /u01
$ vmstat 5 3
Displays system statistics (5 seconds apart; 3 times):
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr s0 s1 s2 s3 in sy cs us sy id
0 0 0 28872 8792 8 5 172 142 210 0 24 3 11 17 2 289 1081 201 14 6 80
0 0 0 102920 1936 1 95 1936 302 1264 235 12 1 0 3 240 459 211 0 2 97
0 0 0 102800 1960 0 0 0 0 0 464 0 0 0 0 0 107 146 29 0 0 100
Having any processes in the b or w columns is a sign of a problem system.
Having an id of 0 is a sign that the cpu is overburdoned.
Having high values in pi and po show excessive paging.
• procs (Reports the number of processes in each of the following states)
• r : in run queue b : blocked for resources (I/O, paging etc.)
• w : runnable but swapped
• memory (Reports on usage of virtual and real memory)
• swap : swap space currently available (Kbytes)
• free : size of free list (Kbytes)
• page (Reports information about page faults and paging activity (units per second)
• re : page reclaims mf : minor faults pi : Kbytes paged in po : Kbytes paged out
• fr : Kbytes freed de : anticipated short-term memory shortfall (Kbytes)
• sr : pages scanned by clock algorith
• disk (Reports the number of disk operations per second for up to 4 disks
• faults (Reports the trap/interupt rates (per second)
• in : (non clock) device interupts si : system calls
• cs : CPU context switches
• cpu (Reports the breakdown of percentage usage of CPU time (averaged across all CPUs)
• us : user time si : system time cs : idle time
CPU Usage
sar
$ sar -u 10 8
Reports CPU Utilization (10 seconds apart; 8 times):
Time %usr %sys %wio %idle
11:57:31 72 28 0 0
11:57:41 70 30 0 0
11:57:51 70 30 0 0
11:58:01 68 32 0 0
11:58:11 67 33 0 0
11:58:21 65 28 0 7
11:58:31 73 27 0 0
11:58:41 69 31 0 0
Average 69 30 0 1
%usr: Percent of CPU in user mode
%sys: Percent of CPU in system mode
%wio: Percent of CPU running idle with a process waiting for block I/O
%idle: Percent of CPU that is idle
mpstat
$ mpstat 10 2
Reports per-processor statistics on Sun Solaris (10 seconds apart; 8 times):
CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl
0 6 8 0 438 237 246 85 0 0 21 8542 23 9 9 59
0 0 29 0 744 544 494 206 0 0 95 110911 65 29 6 0
ps
$ ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -21r
Displays the top 20 CPU users on the system.
%CPU PID USER COMMAND
78.1 4789 oracle ora_dbwr_DDDS2
8.5 4793 oracle ora_lgwr_DDDS2
2.4 6206 oracle oracleDDDS2 (LOCAL=NO)
0.1 4797 oracle ora_smon_DDDS2
0.1 6207 oracle oracleDDDS2 (LOCAL=NO)
etc. etc. etc. etc.
The PID column can then be matched with the SPID column on the V$PROCESS view to provide more information on the process:
SELECT a.username,a.osuser,a.program,spid,sid,a.serial#
FROM v$session a,v$process b
WHERE a.paddr = b.addr AND spid ='&pid';
tar
The tar command can be used to backup and restore files to another filesystem or an offile storage device:
# Create archive.
cd /u01/app/oracle
tar -cvf /tmp/admin.tar admin
# Restore archive.
cd /tmp
tar -xvf admin.tar
If a full path is used during the archive creation the extract locations are fixed rather than relative. The process is similar when accessing a tape device except the destination is the mounted device:
# Mount and rewind the tape.
mt -f /dev/rmt/2m rew
# Create archive.
tar -cvf /dev/rmt/2m /u01/*
# Restore archive.
tar -xvf /dev/rmt/2m
dd
The dd command is similar to the tar command:
# Mount and rewind the tape.
mt -f /dev/rmt/2m rew
# Create archive.
dd if=/u01/app/oracle/* of=/dev/rmt/2m BS=32K
# Restore archive.
dd if=/dev/rmt/2m of=/u01/app/oracle BS=32K
cpio
The cpio command deals with the standard input so filesystem paths must be piped to it:
# Create archive.
cd /u01/app/oracle
find admin | cpio -oc > /tmp/admin.cpio
# Restore archive.
cd /tmp
cpio -idmv < admin.cpio
If a full path is used during the archive creation the extract locations are fixed rather than relative:
find /u01/app/oracle/admin | cpio -oc > /tmp/admin.cpio
vdump, rvdump, vrestore and rvrestore
Full level 0 backup of a local filesystem (/u01) to a local device (/dev/tape/tape1_d6):
/sbin/vdump -0 -u -f /dev/tape/tape1_d6 /u01
Full level 0 backup of a local filesystem (/u01) to a remote device (server2:/dev/tape/tape1_d6):
/sbin/rvdump -0 -u -f server2:/dev/tape/tape1_d6 /u01
Restore a vdump or rvdump archive from a local device (/dev/tape/tape1_d6) to a local filesystem (/u01):
/sbin/vrestore -xf /dev/tape/tape1_d6 -D /u01
Restore a vdump or rvdump archive from a remote device (server2:/dev/tape/tape1_d6) to a local filesystem (/u01):
/sbin/rvrestore -xf server2:/dev/tape/tape1_d6 -D /u01
Monday, January 12, 2009
Important metalink
1. How to run OATM migration utility ---- Note:404954.1
2. 11.5.10 Oracle E-Business Suite Consolidated Update 2 (CU2)---- Note:316366.1
3. Upgrading Oracle Applications ---- Note:289788.1
4. E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1
5. Step by Step Troubleshooting Guide to Solve APPS Invalid Objects ---- Note:113947.1
6. Compatibility Matrix for Export And Import Between Different Oracle Versions ----Note:132904.1
7. ORA-06550 Running Sys.Utl_recomp.Recomp_parallel(1) During ADPATCH or ADADMIN Session ---- Note:362727.1
8. Now Available: Oracle E-Business Suite Release 12.0.4 ---- Note:556312.1
9. Cloning Oracle Applications Release 11i with Rapid Clone ---- Note:230672.1
10.Upgrading Developer 6i with Oracle Applications 11i ---- Note:125767.1
11.The Basics About Report Review Agent (FNDFS) on 11i ---- Note:111383.1
12.How To Use FNDCPASS to Change The Oracle Users, APPS, APPLSYS and Application Module Passwords (INV, AR, AP, etc.) For Applications 11.5 in Unix ---- Note:159244.1
13.USAGE OF INDIA LOCALIZATION PATCH TOOL (INDPATCH) ---- Note:376756.1
14.Sharing the Application Tier File System in Oracle Applications 11i ----Note:233428.1
15.Shared APPL_TOP FAQ ---- Note:243880.1
16.Using a Staged Applications 11i System to Reduce Patching Downtime ---- Note:242480.1
17.Summary of Possible Reasons and Solutions for the Problem Where All Concurrent Requests Stuck in Pending Phase ---- Note:182154.1
18.Release 11i Adpatch Basics ---- Note:181665.1
19.How to Apply an 11i Patch When adpatch is Already Running ---- Note:175485.1
20.How to Create a Custom Concurrent Manager ---- Note:170524.1
21.Concurrent Manager Questions and Answers Relating to Generic Platform ----Note:105133.1
22.E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1
23.**** Oracle Applications DBA SQL utilites ------------ 108207.1
2. 11.5.10 Oracle E-Business Suite Consolidated Update 2 (CU2)---- Note:316366.1
3. Upgrading Oracle Applications ---- Note:289788.1
4. E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1
5. Step by Step Troubleshooting Guide to Solve APPS Invalid Objects ---- Note:113947.1
6. Compatibility Matrix for Export And Import Between Different Oracle Versions ----Note:132904.1
7. ORA-06550 Running Sys.Utl_recomp.Recomp_parallel(1) During ADPATCH or ADADMIN Session ---- Note:362727.1
8. Now Available: Oracle E-Business Suite Release 12.0.4 ---- Note:556312.1
9. Cloning Oracle Applications Release 11i with Rapid Clone ---- Note:230672.1
10.Upgrading Developer 6i with Oracle Applications 11i ---- Note:125767.1
11.The Basics About Report Review Agent (FNDFS) on 11i ---- Note:111383.1
12.How To Use FNDCPASS to Change The Oracle Users, APPS, APPLSYS and Application Module Passwords (INV, AR, AP, etc.) For Applications 11.5 in Unix ---- Note:159244.1
13.USAGE OF INDIA LOCALIZATION PATCH TOOL (INDPATCH) ---- Note:376756.1
14.Sharing the Application Tier File System in Oracle Applications 11i ----Note:233428.1
15.Shared APPL_TOP FAQ ---- Note:243880.1
16.Using a Staged Applications 11i System to Reduce Patching Downtime ---- Note:242480.1
17.Summary of Possible Reasons and Solutions for the Problem Where All Concurrent Requests Stuck in Pending Phase ---- Note:182154.1
18.Release 11i Adpatch Basics ---- Note:181665.1
19.How to Apply an 11i Patch When adpatch is Already Running ---- Note:175485.1
20.How to Create a Custom Concurrent Manager ---- Note:170524.1
21.Concurrent Manager Questions and Answers Relating to Generic Platform ----Note:105133.1
22.E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1
23.**** Oracle Applications DBA SQL utilites ------------ 108207.1
Wednesday, December 10, 2008
Unix commands
How do you find files that are greater than 3GB in HP unix?
find / -size +3000000000c -exec ls -l {} \;
Check all logs for ORA- errors
grep ^ORA- *log |cut -f2 -d"-"|cut -f1 -d:|awk '{print "ORA-" $1}'|sort –u
How to Find out Top 10 Memory consuming processes?
UNIX95=1 ps -eo vsz,pid,args | sort +0n -1 | grep oracle | tail -10
Pass Database Name in places of SID
Find out mount points starts with oracle which exceeds 80%?
bdf|grep -e oracle grep '[7-9].%
How do you delete 3 days old log files?
find /location -name "*.log" -mtime +3 -exec rm -rf{} \;
Eg : find ./ -name "*.req" -mtime +4 -exec ls -ltr {} \;
Display latest 20 largest files/directories in current directory?
du -ka sort -n tail -20
How do you display/remove Specifice Month files in Unix?
rm `ls -l grep Jun awk '{print $9}'`
How do you find the files which contains a specific Word?
find /home/ganesh \( -type f \) -exec grep -l test {} \;
how to find all the soft links?
find . -type l -exec ll {} \; | grep ERPPROD
soft link
ln -s
eg : ln -s /u01/app/oracle/monitor.sql /u01/app/oracle/test.sql
monitor.sql orginal file
eg: ln -s /u01/app/oracle/monitor.sql /u01/app/oracle/test.sql
monitor.sql orginal file
How to get the OS is 32 bit or 64 bit
getconf HARDWARE_BITMODE
How to get the version of the os
Uname –vr
Eg: O/P--- 3 5
3-release
5- version
find / -size +3000000000c -exec ls -l {} \;
Check all logs for ORA- errors
grep ^ORA- *log |cut -f2 -d"-"|cut -f1 -d:|awk '{print "ORA-" $1}'|sort –u
How to Find out Top 10 Memory consuming processes?
UNIX95=1 ps -eo vsz,pid,args | sort +0n -1 | grep oracle | tail -10
Pass Database Name in places of SID
Find out mount points starts with oracle which exceeds 80%?
bdf|grep -e oracle grep '[7-9].%
How do you delete 3 days old log files?
find /location -name "*.log" -mtime +3 -exec rm -rf{} \;
Eg : find ./ -name "*.req" -mtime +4 -exec ls -ltr {} \;
Display latest 20 largest files/directories in current directory?
du -ka sort -n tail -20
How do you display/remove Specifice Month files in Unix?
rm `ls -l grep Jun awk '{print $9}'`
How do you find the files which contains a specific Word?
find /home/ganesh \( -type f \) -exec grep -l test {} \;
how to find all the soft links?
find . -type l -exec ll {} \; | grep ERPPROD
soft link
ln -s
eg : ln -s /u01/app/oracle/monitor.sql /u01/app/oracle/test.sql
monitor.sql orginal file
eg: ln -s /u01/app/oracle/monitor.sql /u01/app/oracle/test.sql
monitor.sql orginal file
How to get the OS is 32 bit or 64 bit
getconf HARDWARE_BITMODE
How to get the version of the os
Uname –vr
Eg: O/P--- 3 5
3-release
5- version
Friday, September 5, 2008
Important Tables
System Table Description
ALL_ARGUMENTS Arguments in object accessible to the user
ALL_CATALOG All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS Comments on columns of accessible tables and views
ALL_CONSTRAINTS Constraint definitions on accessible tables
ALL_CONS_COLUMNS Information about accessible columns in constraint definitions
ALL_DB_LINKS Database links accessible to the user
ALL_ERRORS Current errors on stored objects that user is allowed to create
ALL_INDEXES Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS COLUMNs comprising INDEXes on accessible TABLES
ALL_LOBS Description of LOBs contained in tables accessible to the user
ALL_OBJECTS Objects accessible to the user
ALL_OBJECT_TABLES Description of all object tables accessible to the user
ALL_SEQUENCES Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS Snapshots the user can access
ALL_SOURCE Current source on stored objects that user is allowed to create
ALL_SYNONYMS All synonyms accessible to the user
ALL_TABLES Description of relational tables accessible to the user
ALL_TAB_COLUMNS Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS Columns of user's tables, views and clusters
ALL_TAB_COMMENTS Comments on tables and views accessible to the user
ALL_TRIGGERS Triggers accessible to the current user
ALL_TRIGGER_COLS Column usage in user's triggers or in triggers on user's tables
ALL_TYPES Description of types accessible to the user
ALL_UPDATABLE_COLUMNS Description of all updatable columns
ALL_USERS Information about all users of the database
ALL_VIEWS Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL Database compatible parameter set via init.ora
DBA_DB_LINKS All database links in the database
DBA_ERRORS Current errors on all stored objects in the database
DBA_OBJECTS All objects in the database
DBA_ROLES All Roles which exist in the database
DBA_ROLE_PRIVS Roles granted to users and roles
DBA_SOURCE Source of all stored objects in the database
DBA_TABLESPACES Description of all tablespaces
DBA_TAB_PRIVS All grants on objects in the database
DBA_TRIGGERS All triggers in the database
DBA_TS_QUOTAS Tablespace quotas for all users
DBA_USERS Information about all users of the database
DBA_VIEWS Description of all views in the database
DICTIONARY Description of data dictionary tables and views
DICT_COLUMNS Description of columns in data dictionary tables and views
GLOBAL_NAME global database name
NLS_DATABASE_PARAMETERS Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS NLS parameters of the instance
NLS_SESSION_PARAMETERS NLS parameters of the user session
PRODUCT_COMPONENT_VERSION version and status information for component products
ROLE_TAB_PRIVS Table privileges granted to roles
SESSION_PRIVS Privileges which the user currently has set
SESSION_ROLES Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP Description table for privilege type codes. Maps privilege type numbers to type names
TABLE_PRIVILEGES Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names
ad_patch_driver_langs to find what all languages patch has ben applied
fnd_web_sec....
AD_APPL_TOPS
AD_APPLIED_PATCHES
AD_BUGS
AD_PATCH_DRIVERS
AD_FILE_VERSIONS
AD_FILES
AD_PATCH_DRIVER_LANGS
AD_PATCH_DRIVER_MINIPKS
AD_PATCH_RUN_BUG_ACTIONS
AD_PATCH_RUN_BUGS
AD_PATCH_RUNS
AD_RELEASES
AD_PATCH_COMMON_ACTIONS ad_program_run_task_jobs
ALL_ARGUMENTS Arguments in object accessible to the user
ALL_CATALOG All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS Comments on columns of accessible tables and views
ALL_CONSTRAINTS Constraint definitions on accessible tables
ALL_CONS_COLUMNS Information about accessible columns in constraint definitions
ALL_DB_LINKS Database links accessible to the user
ALL_ERRORS Current errors on stored objects that user is allowed to create
ALL_INDEXES Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS COLUMNs comprising INDEXes on accessible TABLES
ALL_LOBS Description of LOBs contained in tables accessible to the user
ALL_OBJECTS Objects accessible to the user
ALL_OBJECT_TABLES Description of all object tables accessible to the user
ALL_SEQUENCES Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS Snapshots the user can access
ALL_SOURCE Current source on stored objects that user is allowed to create
ALL_SYNONYMS All synonyms accessible to the user
ALL_TABLES Description of relational tables accessible to the user
ALL_TAB_COLUMNS Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS Columns of user's tables, views and clusters
ALL_TAB_COMMENTS Comments on tables and views accessible to the user
ALL_TRIGGERS Triggers accessible to the current user
ALL_TRIGGER_COLS Column usage in user's triggers or in triggers on user's tables
ALL_TYPES Description of types accessible to the user
ALL_UPDATABLE_COLUMNS Description of all updatable columns
ALL_USERS Information about all users of the database
ALL_VIEWS Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL Database compatible parameter set via init.ora
DBA_DB_LINKS All database links in the database
DBA_ERRORS Current errors on all stored objects in the database
DBA_OBJECTS All objects in the database
DBA_ROLES All Roles which exist in the database
DBA_ROLE_PRIVS Roles granted to users and roles
DBA_SOURCE Source of all stored objects in the database
DBA_TABLESPACES Description of all tablespaces
DBA_TAB_PRIVS All grants on objects in the database
DBA_TRIGGERS All triggers in the database
DBA_TS_QUOTAS Tablespace quotas for all users
DBA_USERS Information about all users of the database
DBA_VIEWS Description of all views in the database
DICTIONARY Description of data dictionary tables and views
DICT_COLUMNS Description of columns in data dictionary tables and views
GLOBAL_NAME global database name
NLS_DATABASE_PARAMETERS Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS NLS parameters of the instance
NLS_SESSION_PARAMETERS NLS parameters of the user session
PRODUCT_COMPONENT_VERSION version and status information for component products
ROLE_TAB_PRIVS Table privileges granted to roles
SESSION_PRIVS Privileges which the user currently has set
SESSION_ROLES Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP Description table for privilege type codes. Maps privilege type numbers to type names
TABLE_PRIVILEGES Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names
ad_patch_driver_langs to find what all languages patch has ben applied
fnd_web_sec....
AD_APPL_TOPS
AD_APPLIED_PATCHES
AD_BUGS
AD_PATCH_DRIVERS
AD_FILE_VERSIONS
AD_FILES
AD_PATCH_DRIVER_LANGS
AD_PATCH_DRIVER_MINIPKS
AD_PATCH_RUN_BUG_ACTIONS
AD_PATCH_RUN_BUGS
AD_PATCH_RUNS
AD_RELEASES
AD_PATCH_COMMON_ACTIONS ad_program_run_task_jobs
Subscribe to:
Posts (Atom)