How to check apps HTTP_Server,OC4J status:-
[oracle@r0469
scripts]$ ./adopmnctl.sh status
You are running
adopmnctl.sh version 120.6
Checking status of
OPMN managed processes...
Processes in Instance:
VIS_r0469.r0469.oracle.com
---------------------------------+--------------------+---------+---------
ias-component | process-type |
pid | status
---------------------------------+--------------------+---------+---------
OC4JGroup:default_group | OC4J:oafm |
12248 | Alive
OC4JGroup:default_group | OC4J:forms |
12182 | Alive
OC4JGroup:default_group | OC4J:oacore |
12100 | Alive
HTTP_Server | HTTP_Server |
12048 | Alive
adopmnctl.sh: exiting
with status 0
adopmnctl.sh: check
the logfile
/oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adopmnctl.txt for more
information ...
How to check apps Concurrent
Manager status:-
[oracle@r0469
scripts]$ ./adcmctl.sh status
You are running
adcmctl.sh version 120.17.12010000.3
Enter the APPS
username : apps
Enter the APPS
password :apps
Internal Concurrent
Manager is Active.
adcmctl.sh: exiting
with status 0
adcmctl.sh: check
the logfile
/oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adcmctl.txt for more
information ...
R12 Apps Scripts Locations:-
[oracle@r0469 ]$ cd /oracle/VIS/inst/apps/VIS_r0469/admin/scripts
[oracle@r0469
scripts]$ ll
total 176
-rwx------ 1 oracle
dba 7301 Aug 31 12:33 adalnctl.sh
-rwx------ 1 oracle
dba 8243 Aug 31 12:34 adapcctl.sh
-rwx------ 1 oracle
dba 1540 Aug 31 12:33 adautocfg.sh
-rwx------ 1 oracle
dba 16497 Aug 31 12:33 adcmctl.sh
-rwx------ 1 oracle
dba 7215 Aug 31 12:33 adexecsql.pl
-rwx------ 1 oracle
dba 8728 Aug 31 12:34 adformsctl.sh
-rwx------ 1 oracle
dba 10208 Aug 31 12:34 adformsrvctl.sh
-rwx------ 1 oracle dba 7996 Aug 31 12:34 adoacorectl.sh
-rwx------ 1 oracle
dba 8004 Aug 31 12:34 adoafmctl.sh
-rwx------ 1 oracle
dba 7282 Aug 31 12:34 adopmnctl.sh
-rwx------ 1 oracle
dba 15298 Aug 31 12:33 adpreclone.pl
-rwx------ 1 oracle
dba 5801 Aug 31 12:33 adstpall.sh
-rwx------ 1 oracle
dba 5945 Aug 31 12:33 adstrtal.sh
-rwx------ 1 oracle
dba 2240 Aug 31 12:34 gsmstart.sh
drwxrwxrwx 2 oracle
dba 4096 May 5 14:53 ieo
-rwx------ 1 oracle
dba 2277 Aug 31 12:34 java.sh
-rwx------ 1 oracle
dba 6388 Aug 31 12:36 jtffmctl.sh
drwxrwxrwx 2 oracle
dba 4096 May 5 14:53 msc
-rwxrwxr-- 1 oracle
dba 6632 Aug 31 12:36 mwactl.sh
-rwxrwxr-- 1 oracle
dba 6645 Aug 31 12:36 mwactlwrpr.sh
-rwxrwxrwx 1 oracle
dba 1438 May 16 11:56 sqlnet.log
How to check apps Listener status:-
[oracle@r0469
scripts]$ ./adalnctl.sh status
adalnctl.sh version
120.3
Checking status for
listener process APPS_VIS.
LSNRCTL for Linux:
Version 10.1.0.5.0 - Production on 20-SEP-2012 12:52:08
Copyright (c) 1991,
2004, Oracle. All rights reserved.
Connecting to
(ADDRESS=(PROTOCOL=TCP)(Host=r0469)(Port=1626))
STATUS of the LISTENER
------------------------
Alias APPS_VIS
Version TNSLSNR for Linux: Version
10.1.0.5.0 - Production
Start Date 20-SEP-2012 09:47:09
Uptime 0 days 3 hr. 4 min. 58 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter
File /oracle/VIS/inst/apps/VIS_r0469/ora/10.1.2/network/admin/listener.ora
Listener Log File
/oracle/VIS/inst/apps/VIS_r0469/logs/ora/10.1.2/network/apps_vis.log
Listening Endpoints
Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=r0469.oracle.com)(PORT=1626)))
Services Summary...
Service
"FNDFS" has 1 instance(s).
Instance "FNDFS", status UNKNOWN,
has 1 handler(s) for this service...
Service
"FNDSM" has 1 instance(s).
Instance "FNDSM", status UNKNOWN,
has 1 handler(s) for this service...
The command completed
successfully
adalnctl.sh: exiting
with status 0
adalnctl.sh:
check the logfile
/oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adalnctl.txt
for more information ...
Database
and Application Information
Below are some of the
queries that can be used to get the database and Application information.
1)
Get Product Version
SQL> SELECT product
, VERSION
, status
FROM product_component_version;
2)
Get Applications Version and Patch Information
SQL> SELECT SUBSTR (a.application_name, 1, 60)
Application_Name
, SUBSTR (i.product_version, 1, 4)
Version
, i.patch_level
, i.application_id
, i.last_update_date
FROM apps.fnd_product_installations i
, apps.fnd_application_all_view a
WHERE i.application_id = a.application_id
ORDER BY
a.application_name;
3)
Patch Information AD_APPLIED_PATCHES table stores information about all the
patches installed in the system.
SQL> SELECT
applied_patch_id
, patch_name
, patch_type
, source_code
, creation_date
, last_update_date
FROM ad_applied_patches;
4)
Check if the application is setup for Multi-Org
SQL> SELECT
multi_org_flag
FROM fnd_product_groups;
M
-
Y
Add
Responsibility using Sqlplus:
BEGIN
FND_USER_RESP_GROUPS_API.LOAD_ROW
(X_USER_NAME =>
'ANATESH',
X_RESP_KEY =>
'APPLICATION_DEVELOPER',
X_APP_SHORT_NAME =>
'FND',
X_SECURITY_GROUP =>
'STANDARD',
X_OWNER =>
'SYSADMIN',
X_START_DATE =>
Trunc(sysdate),
X_END_DATE => NULL,
X_DESCRIPTION =>
NULL,
X_LAST_UPDATE_DATE =>
Trunc(sysdate));
COMMIT;
END
Query to List all the responsibilities attached to a User
He needed query that can list all the responsibilities attached to a user.
select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('LANG');
select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('LANG');
Oracle Database Links (DB Link)
Type of DB Links
There are 3 types of DB links. They are as follows:
1. PRIVATE: When the DB links is created, it is created under Private mode as default. The Private DBLINK is only available to the user who has created it. It is not possible for a user to grant access on a private DBLINK to other users.
2. PUBLIC: The Public DBLINK is available to all the users and all users can have the access without any restrictions.
3. SHARED: Shared database link uses share the server connection to support database link connection. If there are multiple concurrent database link access into a remote database, shared database link can be used to reduce the number of server connections required. Without the shared clause each database link connection requires a separate connection to the remote database.
Types of Logins:
In dblink we can use 2 types of login. They are as follows:
1. DEFAULT LOGIN: The User name and Password is same in both the databases.
Syntax
======
CREATE [PUBLIC] DATABASE LINK CONNECT TO CURRENT_USER USING
Code: (Text)
Create public database link daslink connect to current_user using ‘ORCL’
2. EXPLICIT LOGIN: The User Name and Password is different in both the databases.
Syntax
======
CREATE [PUBLIC|SHARED] DATABASE LINK CONNECT TOIDENTIFIED BY USING
Code: (text)
CREATE PUBLIC DATABASE LINK DDLNK CONNECT TO SCOTT IDENTIFIED BY TIGER USING ‘ORCL’
Note: To create the public DBLINK the user must have create public database link system privileges.
Script to
delete the concurrent program from Oracle
fnd_program.delete_program('program short name','schema');
fnd_program.delete_executable('program short name','schema');
commit;
End;
fnd_program.delete_executable('program short name','schema');
commit;
End;
Find out the URL of the
Application in the database
If you want to find out the
URL of the Application in the database in which table you can check ?
Option 1:
SQL> select * from icx_parameters;
Option 2:
SELECT PROFILE_OPTION_VALUE
FROM FND_PROFILE_OPTION_VALUES
WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
FROM FND_PROFILE_OPTIONS
WHERE PROFILE_OPTION_NAME ='APPS_FRAMEWORK_AGENT')
AND LEVEL_VALUE=0;
Option 1:
SQL> select * from icx_parameters;
Option 2:
SELECT PROFILE_OPTION_VALUE
FROM FND_PROFILE_OPTION_VALUES
WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
FROM FND_PROFILE_OPTIONS
WHERE PROFILE_OPTION_NAME ='APPS_FRAMEWORK_AGENT')
AND LEVEL_VALUE=0;
Script to display status of all the Concurrent Managers
SELECT DISTINCT concurrent_process_id "Concurrent
Process ID",
pid "System Process
ID", os_process_id "Oracle Process ID",
q.concurrent_queue_name
"Concurrent Manager Name",
p.process_status_code
"Status of Concurrent Manager",
TO_CHAR(p.process_start_date,'MM-DD-YYYY HH:MI:SSAM') "Concurrent Manager
Started at"
FROM fnd_concurrent_processes p,
fnd_concurrent_queues q,
fnd_v$process
WHERE q.application_id = queue_application_id
AND q.concurrent_queue_id =
p.concurrent_queue_id
AND spid = os_process_id
AND process_status_code NOT IN ('K', 'S')
ORDER BY concurrent_process_id, os_process_id,
q.concurrent_queue_name;
Query to find who and when update an Oracle Application user's profile
SELECT t.user_profile_option_name,
profile_option_value,
v.creation_date,
v.last_update_date,
v.creation_date v.
last_update_date "Change Date",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;
v.creation_date,
v.last_update_date,
v.creation_date v.
last_update_date "Change Date",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;
Query to fetch
Responsibilities attached to a user:
The below query will
fetch the responsibilities assigned to a particular user.
fu.user_id,
fu.user_name,
fr.responsibility_name,
fr.description,
fa.application_name
FROM fnd_user fu,
fnd_user_resp_groups g,
fnd_application_tl fa,
fnd_responsibility_tl fr
WHERE
g.user_id(+) = fu.user_id
AND g.responsibility_application_id =
fa.application_id
AND fa.application_id = fr.application_id
AND g.responsibility_id =
fr.responsibility_id
AND fu.user_name =UPPER('User_Name');
How to get Login Passwords for application & Data Base instances
Most
often, oracle apps developers have to work in different DB instances for
development as well as for testing purposes. In such situations we need to get
access to different oracle application instances and data base (dev/test/crp
etc.,) instances. So we have to request & follow a lengthy approval process
to get those login details, some times you may end up in loosing your delivery
schedules.
There is
a simple way by which you can get the logins/passwords of your DB &
Application with out chasing the DBAs.
Oracle follows an encryption algorithm to encrypt
user passwords. Most references to the encryption algorithm point
to either the PL/SQL package APPS.FND_WEB_SEC or the Java class
"oracle.apps.fnd.security.WebSessionManagerProc".
For
decryption and encryption, the following calls are made:
APPS.FND_WEB_SEC
>
oracle.apps.fnd.security.WebSessionManagerProc>
oracle.apps.fnd.security.AolSecurity>
oracle.apps.fnd.security.AolSecurityPrivate
The
actual encryption and decryption routines are in the
"oracle.apps.fnd.security.AolSecurityPrivate" Java class. This Java
class is stored both in the database as a Java Stored Procedure and in the
operating system directory $COMMON_TOP/java.
Create a package specification: get_pwd
-- Package Specification
CREATE OR REPLACE PAGE get_pwd AS
FUNCTION decrypt (KEY IN VARCHAR2,VALUE
IN VARCHAR2)RETURN VARCHAR2;
END get_pwd;
Create the package body: get_pwd
-- Package Body
CREATE OR REPLACE PAGE BODY get_pwd AS
FUNCTION
decrypt (KEY IN VARCHAR2,VALUE IN VARCHAR2)RETURN VARCHAR2 AS
LANGUAGE
JAVA
NAME
'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String)
return java.lang.String';
END get_pwd;
Call the package function as shown below:
/** Run this on toad, Get the DB apps
password */
SELECT (SELECT get_pwd.decrypt (UPPER
((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))
FROM DUAL)), usertable.encrypted_foundation_password)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER
((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD')
,1
, INSTR (fnd_profile.VALUE
('GUEST_USER_PWD'), '/')
- 1
)
FROM DUAL))
Call the package function as shown below:
/** Run this on toad, Get the
application usernames and passwords */
SELECT usertable.user_name
, (SELECT get_pwd.decrypt (UPPER
((SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE
('GUEST_USER_PWD'))
FROM DUAL)),
usertable.encrypted_foundation_password)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE
UPPER ((SELECT SUBSTR
(fnd_profile.VALUE ('GUEST_USER_PWD')
,1
, INSTR (fnd_profile.VALUE
('GUEST_USER_PWD'), '/')
- 1
)
FROM DUAL))))
,usertable.encrypted_user_password)
FROM DUAL) AS encrypted_user_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER
('username') -- Here username is application login such as 'OPERATIONS'
Concurrent Program Monitoring Scripts
Most
frequently in support projects we need to monitor and track the concurrent
programs. Here are some the important queries to track the status of concurrent
program. These are also very useful for the oracle apps system administrator
--------- Terminating requests----------
SELECT Request_Id Reqst,
user_concurrent_program_name
concurrent_program,
User_Name
Requestor,
Oracle_Username
Orcl,
Fcr.Logfile_Name LN,
Concurrent_Queue_Name Manager,
TO_CHAR(Actual_Start_Date, 'Mm-Dd-Yy
Hh24:Mi') Started,
Single_Thread_Flag
FROM Fnd_Concurrent_Requests Fcr,
Fnd_Concurrent_Programs_tl Fcp,
Fnd_Oracle_Userid O,
Fnd_Concurrent_Processes P,
Fnd_Concurrent_Processes P,
Fnd_Concurrent_Queues Q,
Fnd_User
Fnd_User
WHERE
Controlling_Manager
= Concurrent_Process_ID
AND ( P.Concurrent_Queue_ID =
Q.Concurrent_Queue_ID
AND P.Queue_Application_ID
= Q.Application_ID )
AND O.Oracle_Id = Fcr.Oracle_Id
AND ( Fcr.Program_Application_Id =
Fcp.Application_Id
AND Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id
)
AND Requested_By = User_Id
AND Phase_Code = 'R' AND Status_Code =
'T'
ORDER BY Actual_Start_Date,
Request_Id
------ Currently running requests
------------
SELECT Request_Id Reqst,
User_Name
Requestor,
Oracle_Username
Orcl,
Concurrent_Queue_Name
Manager,
Concurrent_Program_Name
Program,
user_concurrent_program_name conc_program,
user_concurrent_program_name conc_program,
TO_CHAR(Actual_Start_Date,
'Mm-Dd-Yy Hh24:Mi') Started,
Fcr.Logfile_Name LN,
Run_Alone_Flag,
Single_Thread_Flag
FROM Fnd_Concurrent_Requests Fcr,
fnd_concurrent_programs_tl fcpt,
Fnd_Concurrent_Programs Fcp,
Fnd_Oracle_Userid
O,
Fnd_Concurrent_Processes P,
Fnd_Concurrent_Queues
Q, Fnd_User
WHERE
Controlling_Manager =
Concurrent_Process_ID
AND ( P.Concurrent_Queue_ID
= Q.Concurrent_Queue_ID
AND P.Queue_Application_ID =
Q.Application_ID )
AND O.Oracle_Id = Fcr.Oracle_Id
AND ( Fcr.Program_Application_Id =
Fcp.Application_Id
AND Fcr.Concurrent_Program_Id =
Fcp.Concurrent_Program_Id )
AND (Fcpt.concurrent_program_id =
Fcp.Concurrent_program_id)
AND Requested_By = User_Id
AND Phase_Code = 'R' AND
Status_Code = 'R'
ORDER BY Actual_Start_Date,
Request_Id
--- Summary of concurrent request
execution since Date ---
SELECT L1.Meaning Request_Type,
L2.Meaning Status,
COUNT(Status_Code) NR
FROM
Fnd_Concurrent_Requests R,
Fnd_Concurrent_Programs P,
Fnd_Lookups L1,
Fnd_Lookups L2
WHERE
L1.Lookup_Code =
P.Execution_Method_Code
AND L1.Lookup_Type =
'CP_EXECUTION_METHOD_CODE'
AND (R.Program_Application_ID =
P.Application_ID
AND R.Concurrent_Program_ID = P.Concurrent_Program_ID )
AND R.Concurrent_Program_ID = P.Concurrent_Program_ID )
AND (R.Status_Code =
L2.Lookup_Code
AND L2.Lookup_Type = 'CP_STATUS_CODE' )
AND R.Phase_Code = 'C'
AND R.Actual_Completion_Date >
SYSDATE - 5
GROUP BY
L1.Meaning, L2.Meaning
Errored programs on a day
SELECT fcp.user_concurrent_program_name
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl
fcp
WHERE status_code = 'E'
AND fcr.concurrent_program_id =
fcp.concurrent_program_id
AND TRUNC(requested_start_date) =
TRUNC(SYSDATE)
STATUS_CODE
A Waiting
B Resuming
C
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I
M No Manager
Q Standby
R
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting
Managers that are currently
running a request
SELECT Concurrent_Queue_Name Manager,
Request_Id Request,
User_name,
Concurrent_Program_Name Program,
Run_Alone_Flag,
TO_CHAR(Actual_Start_Date, 'DD-MON-YY
HH24:MI') Started
FROM Fnd_Concurrent_Queues Fcq,
Fnd_Concurrent_Requests
Fcr,
Fnd_Concurrent_Programs
Fcp,
Fnd_User
Fu,
Fnd_Concurrent_Processes
Fpro
WHERE
Phase_Code = 'R' AND
Fcr.Controlling_Manager = Concurrent_Process_Id
AND (Fcq.Concurrent_Queue_Id =
Fpro.Concurrent_Queue_Id AND
Fcq.Application_Id
= Fpro.Queue_Application_Id ) AND (Fcr.Concurrent_Program_Id =
Fcp.Concurrent_Program_Id AND Fcr.Program_Application_Id = Fcp.Application_Id
) AND
Fcr.Requested_By = User_Id;
Oracle Performance Tuning
related Queries
SQL to identify the query which takes long time
Step1 : Run the first query
, this will list all the programs that currently running in Application.
Take the SID and use it in the second query.
SELECT
f.user_name
,a.request_id "Req Id"
,a.concurrent_program_id "Prg Id"
,a.RESPONSIBILITY_ID Responsibility
,a.phase_code,a.status_code
,b.os_process_id "OS"
,vs.sid
,vs.serial# "Serial#"
,vp.spid
,TO_CHAR(request_date,'DD-MON-YY hh24:mi:ss') request_date
,(NVL(a.actual_completion_date,SYSDATE)-a.actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||c2.user_concurrent_program_name "Program"
FROM ASYS.fnd_Concurrent_requests a
,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_user f
,v$session vs
,v$process vp
WHERE
a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND c2.concurrent_program_id = c.concurrent_program_id
AND c2.application_id = c.application_id
AND a.phase_code IN ('I','P','R','T')
AND a.requested_by = f.user_id
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND c2.LANGUAGE = 'US'
AND a.oracle_process_id = vp.spid
AND vs.paddr = vp.addr
ORDER BY 9
f.user_name
,a.request_id "Req Id"
,a.concurrent_program_id "Prg Id"
,a.RESPONSIBILITY_ID Responsibility
,a.phase_code,a.status_code
,b.os_process_id "OS"
,vs.sid
,vs.serial# "Serial#"
,vp.spid
,TO_CHAR(request_date,'DD-MON-YY hh24:mi:ss') request_date
,(NVL(a.actual_completion_date,SYSDATE)-a.actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||c2.user_concurrent_program_name "Program"
FROM ASYS.fnd_Concurrent_requests a
,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_user f
,v$session vs
,v$process vp
WHERE
a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND c2.concurrent_program_id = c.concurrent_program_id
AND c2.application_id = c.application_id
AND a.phase_code IN ('I','P','R','T')
AND a.requested_by = f.user_id
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND c2.LANGUAGE = 'US'
AND a.oracle_process_id = vp.spid
AND vs.paddr = vp.addr
ORDER BY 9
Step 2 : Get Sid from step1 and Keep on executing this query in SQL. This query will show the currently running SQL in the DB, as your concurrent is submitted and running. You can now find out the exact query ( select / insert / update ) which is actually taking time in your concurrent program.
SELECT sql_text FROM v$sqltext
t,v$session s
WHERE t.ADDRESS = s.SQL_ADDRESS
AND t.HASH_VALUE = s.SQL_HASH_VALUE
AND s.sid = 100 – Get this value from step1
ORDER BY PIECE
WHERE t.ADDRESS = s.SQL_ADDRESS
AND t.HASH_VALUE = s.SQL_HASH_VALUE
AND s.sid = 100 – Get this value from step1
ORDER BY PIECE
Currently running sessions in database
Often it requires keeping track of the currently running sessions
and the Sql executed in the sessions. So here is the query by which
you can find out.
Get all the active sessions in the
database
select username, sid, serial#, process,
status, module, action, osuser
from v$session
where username is not null
and status =
'ACTIVE'
-- Get the sqls executed by the active
sessions
declare
x number;
begin
for x in
( select
username||'('||sid||','||serial#||
')
ospid = ' || process ||
'
program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI')
logon_time,
to_char(sysdate,' Day HH24:MI')
current_time,
sql_address,
LAST_CALL_ET,
osuser,
machine
from v$session
where status = 'ACTIVE'
rawtohex(sql_address) <> '00'
and username is not null order by
last_call_et )
loop
for y in ( select
max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like
'%listener.get_cmd%' and
y.sql_text not like
'%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line(
'--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || '
' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(x.osuser||':'||x.machine);
dbms_output.put_line(substr(
y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
Get all
the views that refer a particular table:
SELECT
o.object_name, o.status, o.last_ddl_time
FROM
SYS.dba_objects o
WHERE
o.object_type = 'VIEW'
AND
EXISTS (
SELECT 'x'
FROM SYS.dba_dependencies d
WHERE
d.referenced_type = 'TABLE'
AND
d.TYPE = 'VIEW'
AND
d.owner = 'APPS'
AND
d.NAME = o.object_name
Get all the tables with the
column name:
SELECT
TABLE_NAME
FROM
all_tab_columns
WHERE
column_name
= '<COLUMN_NAME>'
Get SQL help
from the Data base itself:
If you
need any syntax of a sql command or its details, you don’t need to search for
it in google. what you just need to do is use the below sql command from the
data base
In iSQL*Plus, click the Help button to display iSQL*Plus
help. Or Use the below command line help system. In some DB system schema may
not be available, then use sys.help
instead.
select *
from
system.help
where
upper(topic)=upper('string')
Find from which responsibility a
concurrent program can be run:
SELECT
distinct
a.user_concurrent_program_name,
a.description,
request_group_name,
e.responsibility_name
FROM
fnd_concurrent_programs_tl
a,
fnd_request_groups
b,
fnd_request_group_units
c,
fnd_responsibility
d,
fnd_responsibility_tl
e
WHERE
a.concurrent_program_id
= c.request_unit_id
and
b.request_group_id = c.request_group_id
and
b.request_group_id = d.request_group_id
and
d.responsibility_id = e.responsibility_id
and
a.application_id = b.application_id
and
b.application_id = c.application_id
and
d.application_id = e.application_id
and
a.concurrent_program_id = :p_conc_program_id
Find all running concurrent
requests with their run times:
The
below query is useful for analyzing concurrent program run times and
identifying their performance
select
request_id,
fcpt.user_concurrent_program_name,
completion_text,
actual_start_date,
actual_completion_date,
to_date((actual_completion_date
- actual_start_date), 'HH:MM:SS') duration
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs
fcp,
fnd_concurrent_programs_tl
fcpt
where
fcr.concurrent_program_id = fcp.concurrent_program_id
and
fcp.concurrent_program_id = fcpt.concurrent_program_id
and
fcr.actual_start_date > sysdate - 1
order
by actual_completion_date - actual_start_date desc
Search all packages for a line
of code:
SELECT
*
FROM
ALL_SOURCE
WHERE
TYPE IN ('PACKAGE', 'PAGE BODY')
AND
TEXT LIKE '%XXXX%'
The below table shows all the
data base object information:
select *
from dba_objects where object_name like '%HEADER%' and object_type = 'TABLE'
Sequal to get the concurrent
program file name (procedure/pkg name) based on the concurrent program name
select fct.user_concurrent_program_name,
fcp.concurrent_program_name,
fe.execution_file_name,
fl.meaning execution_method
from fnd_concurrent_programs_tl fct,
fnd_concurrent_programs fcp,
fnd_executables fe,
fnd_lookups fl
where
upper(fct.user_concurrent_program_name) = upper('concurrent program')
and fct.concurrent_program_id = fcp.concurrent_program_id
and fe.executable_id = fcp.executable_id
and fl.lookup_code = fe.execution_method_code
and fl.lookup_type = 'CP_EXECUTION_OD_CODE'
fcp.concurrent_program_name,
fe.execution_file_name,
fl.meaning execution_method
from fnd_concurrent_programs_tl fct,
fnd_concurrent_programs fcp,
fnd_executables fe,
fnd_lookups fl
where
upper(fct.user_concurrent_program_name) = upper('concurrent program')
and fct.concurrent_program_id = fcp.concurrent_program_id
and fe.executable_id = fcp.executable_id
and fl.lookup_code = fe.execution_method_code
and fl.lookup_type = 'CP_EXECUTION_OD_CODE'
List of all active workflow
users and their roles
select wu.name user_name,
wr.name role_name
from wf_users wu,
wf_user_roles wur,
wf_roles wr
where wu.name = wur.user_name
and wur.role_name = wr.name
and wu.status = 'ACTIVE'
and wr.status = 'ACTIVE'
and wr.orig_system = 'WF_LOCAL_ROLES'
order by wu.name,
wr.name
Concurrent requests
raised in the last day
select request_id,
decode(parent_request_id, -1, 'None') as parent_request_id,
fcpt.user_concurrent_program_name,
decode( fcr.status_code,
'A', 'Waiting',
'B', 'Resuming',
'C', '',
'D', 'Cancelled',
'E', 'Error',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', '',
'M', 'No Manager',
'Q', 'Standby',
'R', '',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting') as
status,
decode( fcr.phase_code,
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running') as phase,
fu.user_name,
fr.responsibility_name,
completion_text,
argument_text,
request_date,
requested_start_date,
actual_start_date,
actual_completion_date,
fcp.concurrent_program_name,
fe.executable_name,
fe.execution_file_name
from fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_executables fe,
fnd_responsibility_vl fr,
fnd_user fu
where fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_id =
fcpt.concurrent_program_id
and fcp.executable_id = fe.executable_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and fcr.request_date > sysdate - 1
--and fcr.concurrent_program_id = XXXX
order by request_id desc
How
to find the Trace file generated for a concurrent program
You can use the following query to locate the trace file, it takes
as input the ' Concurrent Request id'
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',
'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;
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name: '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'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;
The output would be 'directory'/'filename.trc'
eg: 'directory'/ora_3465_act.trc.
In the above directory, do 'ls -l' for '*3465*trc' and any latest file that you see with 3465 as a part of the file name would be the trace file.
eg: 'directory'/ora_3465_act.trc.
In the above directory, do 'ls -l' for '*3465*trc' and any latest file that you see with 3465 as a part of the file name would be the trace file.
Removing
Table Lock
Login to database as SYS or SYSTEM user. Find out the SESSION_ID
by running the following SELECT statement
SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name;
SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name;
Use this session id to find SERIAL# by using following SELECT
statment
SELECT SID,SERIAL#
FROM V$SESSION
WHERE SID IN (SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name)
SELECT SID,SERIAL#
FROM V$SESSION
WHERE SID IN (SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name)
Use ALTER SYSTEM command to KILL SESSION and this will release the
lock.
ALTER SYSTEM KILL SESSION 'SID,SERIALl#';
ALTER SYSTEM KILL SESSION 'SID,SERIALl#';
This script will
report the SQL text of some of the locks currently being held in the database
select s.username username,
a.sid sid,
a.owner||'.'||a.object object,
s.lockwait,
t.sql_text SQL
from v$sqltext t,
v$session s,
v$access a
where t.address=s.sql_address
and t.hash_value=s.sql_hash_value
and s.sid = a.sid
and a.owner != 'SYS'
and upper(substr(a.object,1,2)) != 'V$'
Script to check
Responsibilities assigned to a particular user or users assigned for particular
responsibility or all users and their responsibilities:
SELECT fu.user_id,
fu.user_name,
fur.responsibility_id,
fr.responsibility_name
FROM fnd_user fu,
fr.responsibility_name
FROM fnd_user fu,
fnd_user_resp_groups fur,
fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
AND user_name like ‘OPERATIONS’ --- for all user or for particular user
AND fur.responsibility_application_id = 155 –- Check particular resp.
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
AND user_name like ‘OPERATIONS’ --- for all user or for particular user
AND fur.responsibility_application_id = 155 –- Check particular resp.
order by user_name
Script to find
Oracle API's for any module:
select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'AR_%API%' –- Checking for AR Related APIs
order by
a.owner, a.name
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'AR_%API%' –- Checking for AR Related APIs
order by
a.owner, a.name
Query used for audit
point of view i.e. when a profile is changed and by which user
SELECT t.user_profile_option_name,
profile_option_value,
v.creation_date,
v.last_update_date,
v.creation_date - v.last_update_date "Change Date",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = :p_value
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;
:p_value is (10001 -> site level, 10002 -> application
level, 10003 -> responsibility level, 10004 – user level)
Check Current
Applied Patch
SELECT patch_name, patch_type,
maint_pack_level,
creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date
Query used to view
the patch level status of all modules
SELECT a.application_name,
DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
patch_level
FROM apps.fnd_application_vl a,
apps.fnd_product_installations b
WHERE a.application_id = b.application_id;
This query will
shows concurrent program processing time
SELECT f.request_id ,
pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date,
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600
-
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60)
-
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60)
))
|| ' SECS ' time_difference,
DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.descriptio
n||']',p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code,'R','Running','C','Complete',f.phase_code)
Phase
, f.status_code
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;
View all types of
request Application wise
SELECT fa.application_short_name,
fcpv.user_concurrent_program_name,
description,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
output_file_type,
program_type,
printer_name,
minimum_width,
minimum_length,
concurrent_program_name,
concurrent_program_id
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
ORDER BY description
Script to display status of all the
Concurrent Managers
select
distinct Concurrent_Process_Id CpId, PID Opid,
Os_Process_ID
Osid,
Q.Concurrent_Queue_Name
Manager,
P.process_status_code
Status,
To_Char(P.Process_Start_Date,
'MM-DD-YYYY HH:MI:SSAM') Started_At
from Fnd_Concurrent_Processes P,
Fnd_Concurrent_Queues Q,
FND_V$Process
where Q.Application_Id = Queue_Application_ID
and
Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID
and
Spid = Os_Process_ID
and Process_Status_Code not in
('K','S')
order
by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name
Print Oracle Apps versions
SELECT
substr(a.application_short_name, 1, 5) code,
substr(t.application_name,
1, 50) application_name,
p.product_version version
FROM
fnd_application a,
fnd_application_tl t,
fnd_product_installations
p
WHERE
a.application_id = p.application_id
AND
a.application_id = t.application_id
AND
t.language = USERENV('LANG');
************************************************END***************************
No comments:
Post a Comment