Posts Tagged ‘Application DBA’

Oracle EBS Find Where Debug Is On

May 15th, 2018, posted in Oracle Queries
Share

Oracle EBS Find Where Debug Is On

If you have Debug is on at Site level then it will Impact the performance of the Instance, here is the script which will help you to quickly check your EBS Instance if ‘Debug’ is on (all level) of the EBS.

 

set linesize 160
col USER_PROFILE_OPTION_NAME for a30
col CONTEXT for a30
col VALUE for a30
col NAME for a30
 
SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
FROM FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE po.user_profile_option_name like '%Debug%'
AND pov.profile_option_value='Y'
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value;

Sample ouptut:

 

NAME                           USER_PROFILE_OPTION_NAME       LEV      CONTEXT                        VALUE
---------------------------    ----------------------------   ------   ---------------------------    ---------------
AFLOG_ENABLED                  FND: Debug Log Enabled         USER     SA-USER1                       Y
FND_CONC_ALLOW_DEBUG           Concurrent: Allow Debugging    RESP     XXXX_CE_SUP_MY-MYR-GE          Y
PRINT_DEBUG                    FA: Print Debug                USER     SA-USER2                       Y
PO_SET_DEBUG_WORKFLOW_ON       PO: Set Debug Workflow ON      USER     SA-USER3                       Y
PA_DEBUG_MODE                  PA: Debug Mode                 USER     SA-USER4                       Y
SO_DEBUG                       OE: Debug                      SITE                                    Y
MSC_JAVA_DEBUG                 MSC: Enable Java Debug         SITE                                    Y
 
7 rows selected.
Share

Find Concurrent Manager Jobs By Query

May 6th, 2018, posted in Oracle Queries
Share

Query :

 

select sess.sid,
 sess.sql_hash_value,
 oracle_process_id OS_PROCESS_ID,
 fusr.description user_name ,
 fcp.user_concurrent_program_name progName,
 to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate,
 request_id RequestId,
 (sysdate - actual_start_date)*24*60 ETime_in_Mins
from apps.fnd_concurrent_requests fcr,
 apps.fnd_concurrent_programs_tl fcp,
 apps.fnd_user fusr,
 v$session sess
where fcp.concurrent_program_id = fcr.concurrent_program_id
 and fcr.program_application_id = fcp.application_id
 and fcp.language = 'US'
 and fcr.phase_code = 'R'
 and fcr.status_code = 'R'
 and fcr.requested_by = fusr.user_id
 and fcr.oracle_session_id = sess.audsid (+)
-- and p.addr = sess.paddr
 order by 6 DESC
Share

Installation Steps Of OAF or JDeveloper

May 7th, 2017, posted in Oracle, Windows
Share

If you want to do some OAF Development in JDeveloper for the first time, you will need to do the following things.

1] Download JDeveloper Patch

Based on your instance release level, check out the version of JDeveloper to use using below link.

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=416708.1

You can identify the OA Framework version in your instance by activating diagnostics and click the “About This Page” from any OAF page. Click the “Technology Components” tab. The OA Framework version in the top row of the table can then be matched to the JDeveloper Patch.

Download the JDeveloper Patch.

You can also use this query to find about your version :
SELECT release_name FROM fnd_product_groups;


2] Extract the JDeveloper patch in a directory say D:\DevSuiteHome_1\jdev

The patch actually contains below three directories

  • jdevbin – Includes an extended version of the Oracle JDeveloper 10g executable and OA Framework class libraries.
  • jdevhome – Includes the OA Framework Toolbox Tutorial source and developer working area.
  • jdevdoc – Contains documentation.

Installation Steps Of OAF or JDeveloper,Oracle DBA,Apps DBA,Application DBA,Oracle Installation,Installation of OAF,Installation JDeveloper

3] Define an environment variable

Define an environment variable JDEV_USER_HOME for your local machine. This has to be set to jdevhome\jdev directory. In above example it has to be D:\DevSuiteHome_1\jdev\jdevhome\jdev.

My Computer Properties Advanced tab Environment Variables New

Variable: JDEV_USER_HOME

Value: D:\DevSuiteHome_1\jdev\jdevhome\jdev

Installation Steps Of OAF or JDeveloper,Oracle DBA,Apps DBA,Application DBA,Oracle Installation,Installation of OAF,Installation JDeveloper,environment file,windows environment file

4] Create the shortcut of jdevW

Their you will find jdev and jdevW icons. The jdev is for UNIX environment and jdevW is for Windows environment.
Create the shortcut of jdevW. And send to desktop

Installation Steps Of OAF or JDeveloper,Oracle DBA,Apps DBA,Application DBA,Oracle Installation,Installation of OAF,Installation JDeveloper

Also can be check by this :
http://hostname.com:portnumber/OA_HTML/jsp/fnd/aoljtest.jsp

4] Move the DBC file

After creating the environment veriable we have to move the DBC file from Oracle Apps fnd top to jDeveloper patch.

Here we are using WinScp which connects to the server from our desktop system.
After finding where exactly the DBC file located go to that particular path and copy the DBC file.
(/oracle/apps/r12/inst/apps/visr12_ebsr12/appl/fnd/12.0.0/secure)

After copying, in your desktop system go to
Jdevhome–> jdev–> dbc_files–> secure
Here we will not find any files or documents the folder is empty now we need to paste the DBC file which we copied from the Oracle Apps Instance Server.

5] Now Test Database

After moving the DBC file now open the JDeveloper and create the Data Base Connection.

After opening the file it will ask for Configure File Type Associations, on check box check all the available types to associate with JDeveloper.

Go to Connections tab after that Right Click on Data Base folder and then select New Database Connection…

1

2 3 4 5

6] Set The Default Project Properties

After creating the DataBase Connections, Set the default project properties.

Go to Tools -> Default Project Properties

1

 

In that select Runtime connection, adjust the runtime connection information, necessary for the database and Oracle E-Business Suite.

Brows the DBC file from the jDeveloper patch where we moved from the server.
(Jdevhome–> jdev–> dbc_files–> secure)

Username: The user name is Oracle Apps instance User Name
Password:  Password is Oracle Apps instance Password.

Application Short Name: In which application u want to run give the Application Short Name of that application.
Responsibility key: Responsibility key of that application short name.

 Application DBA, APPS DBA, Installation JDeveloper, Installation of OAF, Installation Steps Of OAF or JDeveloper, Oracle DBA, Oracle Installation

Share

Check Number Of Oracle Users With Different Queries

June 17th, 2016, posted in Oracle Queries
Share

Query # 1 :

desc v$license
Name Null? Type
----------------------------------------- -------- ----------------
SESSIONS_MAX NUMBER
SESSIONS_WARNING NUMBER
SESSIONS_CURRENT NUMBER
SESSIONS_HIGHWATER NUMBER
USERS_MAX NUMBER
CPU_COUNT_CURRENT NUMBER
CPU_CORE_COUNT_CURRENT NUMBER
CPU_SOCKET_COUNT_CURRENT NUMBER
CPU_COUNT_HIGHWATER NUMBER
CPU_CORE_COUNT_HIGHWATER NUMBER
CPU_SOCKET_COUNT_HIGHWATER NUMBER

 

Query # 2 :

select sessions_current from v$license;

 

Query # 3 :

select SESSIONS_CURRENT,SESSIONS_HIGHWATER,CPU_COUNT_CURRENT,CPU_COUNT_HIGHWATER from v$license;

 

Query # 4 :

SELECT USERNAME FROM DBA_USERS

 

Query # 5 :

SELECT distinct user_id from FND_LOGINS

 

Query # 6 :

SELECT distinct user_id from icx_sessions

 

Query # 7 :

SELECT distinct user_id from FND_USER

 

Query #8:


select application_name,responsibility_name,
security_group_name, user_name,
greatest(u.start_date, ur.start_date, r.start_date) start_date,
least(nvl(u.end_date, nvl(ur.end_date, r.end_date)),
nvl(ur.end_date, nvl(u.end_date, r.end_date)),
nvl(r.end_date, nvl(u.end_date, ur.end_date))) end_date
from fnd_user u,fnd_user_resp_groups ur,
fnd_responsibility_vl r,fnd_application_vl a, 
fnd_security_groups_vl s
where a.application_id = r.application_id
and u.user_id = ur.user_id
and r.application_id = ur.responsibility_application_id
and r.responsibility_id = ur.responsibility_id
and ur.start_date  sysdate
and u.start_date  sysdate
and r.start_date  sysdate
and ur.security_group_id = s.security_group_id
order by application_name,responsibility_name,security_group_name, user_name

 

Query # 9 :


select application_name,responsibility_name,
security_group_name, user_name,
greatest(u.start_date, ur.start_date, r.start_date) start_date,
least(nvl(u.end_date, nvl(ur.end_date, r.end_date)),
nvl(ur.end_date, nvl(u.end_date, r.end_date)),
nvl(r.end_date, nvl(u.end_date, ur.end_date))) end_date
from fnd_user u,fnd_user_resp_groups ur,
fnd_responsibility_vl r,fnd_application_vl a, 
fnd_security_groups_vl s
where a.application_id = r.application_id
and u.user_id = ur.user_id
and r.application_id = ur.responsibility_application_id
and r.responsibility_id = ur.responsibility_id
and ur.start_date  sysdate
and u.start_date  sysdate
and r.start_date  sysdate
and ur.security_group_id = s.security_group_id
order by application_name,responsibility_name,security_group_name, user_name

 

Share

Forms : Could not reserve record (2 tries). Keep trying

August 5th, 2015, posted in Oracle Queries
Share

Second Way : 

Locking And Unlocking Tables In Oracle,Locking Tables In Oracle,Unlocking Tables In Oracle,Tables In Oracle,Oracle DBA,DBA,APPS DBA,ORA DBA,Application DBA,Database DBA,Oracle Tricks,Locking Query in Oracle,Unlocking Query in DBA,Oracle Forms Error,Oracle Application Forms Error,Forms : Could not reserve record (2 tries). Keep trying,FRM-40501,FRM-40501: COULD NOT RESERVE RECORD [2 TRIES]; ORACLE APPS RECORD-LOCKING

One of your end users attempts to modify a record, and the user is prompted with a dialog box asking “Could not reserve record [2 tries]. Keep trying?” The user answers “yes” and after being prompted a few more times the user gives up. Ultimately the user gets a “FRM-40501: ORACLE error: unable to reserve record for update or delete.” When this happens it’s nice to have some scripts ready to go to quickly diagnose what is causing the contention, so appropriate action can be taken. The forms don’t wait to the obtain the lock for very long, so it’s a little more difficult to determine the blocking lock. This first script provides a listing of the possible locks and some relevant E-Business Suite information for digging further.

 

Query 1:

select vs.audsid audsid,
 locks.sid sid,
 vs.serial# serial#,
 vs.username oracle_user,
 vs.osuser os_user,
 vs.program program,
 vs.module module,
 vs.action action,
 vs.process process,
 decode(locks.lmode,
       1, NULL,
       2, 'Row Share',
       3, 'Row Exclusive',
       4, 'Share',
       5, 'Share Row Exclusive',
       6, 'Exclusive', 'None') lock_mode_held,
 decode(locks.request,
       1, NULL,
       2, 'Row Share',
       3, 'Row Exclusive',
       4, 'Share',
       5, 'Share Row Exclusive',
       6, 'Exclusive', 'None') lock_mode_requested,
 decode(locks.type,
       'MR', 'Media Recovery',
       'RT', 'Redo Thread',
       'UN', 'User Name',
       'TX', 'Transaction',
       'TM', 'DML',
       'UL', 'PL/SQL User Lock',
       'DX', 'Distributed Xaction',
       'CF', 'Control File',
       'IS', 'Instance State',
       'FS', 'File Set',
       'IR', 'Instance Recovery',
       'ST', 'Disk Space Transaction',
       'TS', 'Temp Segment',
       'IV', 'Library Cache Invalidation',
       'LS', 'Log Start or Log Switch',
       'RW', 'Row Wait',
       'SQ', 'Sequence Number',
       'TE', 'Extend Table',
       'TT', 'Temp Table',
       locks.type) lock_type,
 objs.owner object_owner,
 objs.object_name object_name,
 objs.object_type object_type,
 round( locks.ctime/60, 2 ) lock_time_in_minutes
from v$session vs,
         v$lock locks,
         dba_objects objs,
         dba_tables tbls
where locks.id1 = objs.object_id
 and vs.sid = locks.sid
 and objs.owner = tbls.owner
 and objs.object_name =  tbls.table_name
 and objs.owner != 'SYS'
 and locks.type = 'TM'
 order by lock_time_in_minutes;

To determine the table(s) the form is trying to lock, use the Help->Record History menu option; this provides the base table or view for the form block.

Look through the result set from Query 1 for an object_name (typically a table or view) in the same vicinity as your table or view. If you have a form block based on a view, it may be helpful to look up the tables behind the view. If the action starts with an ‘FRM:%’, then another forms session has the lock.

One thing to note: You’ll often see the same user blocking themselves. This could be a training issue, or it could be due to a previous forms session that crashed, but the f60webmx process did not die. If this is the case, you can kill the application server OS process (based on the process value in Query 1).

Query 2 provides further details for results in Query 1 that are forms sessions – simply plop in the AUDSID from Query 1.

Query 2:

SELECT
        F.AUDSID,
        S.SID,
        S.SERIAL#,
        L.USER_ID,
        L.TERMINAL_ID,
        L.LOGIN_NAME,
        R.RESP_APPL_ID,
        R.RESPONSIBILITY_ID,
        F.FORM_ID,
        F.FORM_APPL_ID,
        L.PID,
        L.PROCESS_SPID,
        NVL(F.START_TIME, NVL(R.START_TIME, L.START_TIME)) TIME,
        USR.USER_NAME,
        a.application_name,
        RSP.RESPONSIBILITY_NAME,
        FRM.USER_FORM_NAME,
        s.program,
        s.action,
        s.module,
        s.state,
        s.event,
        s.wait_class,
        s.seconds_in_wait
FROM FND_RESPONSIBILITY_TL RSP,
        FND_FORM_TL FRM,
        FND_USER USR,
        FND_LOGINS L,
        FND_LOGIN_RESPONSIBILITIES R,
        FND_LOGIN_RESP_FORMS F,
        GV$SESSION S,
        fnd_application_tl A
WHERE F.AUDSID = &ENTER_FORM_AUDSID
AND R.LOGIN_ID = F.LOGIN_ID
AND R.LOGIN_RESP_ID = F.LOGIN_RESP_ID
AND L.LOGIN_ID = R.LOGIN_ID
AND L.END_TIME IS NULL
AND R.END_TIME IS NULL
AND F.END_TIME IS NULL
AND L.USER_ID = USR.USER_ID
AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID
AND R.RESP_APPL_ID = RSP.APPLICATION_ID
AND RSP.LANGUAGE = 'US'
AND RSP.application_id = a.application_id
AND a.language = 'US'
AND F.FORM_ID = FRM.FORM_ID
AND F.FORM_APPL_ID = FRM.APPLICATION_ID
AND FRM.LANGUAGE = 'US'
AND F.AUDSID = S.AUDSID;

If a concurrent program holds the lock, Query 3 provides a bit more information. Here we can see the user, concurrent program, how long it’s been running and log/output files.

Query 3:

select fcr.request_id,
         fcr.requested_by,
         fu.user_name,
         fcr.program_application_id,
         fcr.concurrent_program_id,
         fcr.actual_start_date,
         fat.application_name,
         fcp.concurrent_program_name,
         fcpt.user_concurrent_program_name,
         fcr.description,
         fcr.logfile_node_name,
         fcr.outfile_name,
         fcr.logfile_name,
         fcr.completion_text,
         fcr.parent_request_id,
         vs.process,
         vs.state,
         vs.event,
         vs.wait_class,
         vs.seconds_in_wait
         from v$session vs,
              fnd_concurrent_requests fcr,
              fnd_application_tl fat,
              fnd_concurrent_programs fcp,
              fnd_concurrent_programs_tl fcpt,
              fnd_user fu
         where vs.audsid =  &ENTER_CONC_PROCESS_AUDSID
         and vs.process = fcr.os_process_id
         and fcr.actual_completion_date is null
         and fcr.program_application_id = fat.application_id
         and fcr.program_application_id = fcp.application_id
         and fcr.concurrent_program_id = fcp.concurrent_program_id
         and fcr.program_application_id = fcpt.application_id
         and fcr.concurrent_program_id = fcpt.concurrent_program_id
         and fcr.requested_by = fu.user_id;

This should be enough information to chase down the offender (someone out for coffee and not save that latest change first?) or possibly even point to a process that needs attention.

 

 

*********************************************************************************************************************
Also Check : http://aliimmam.com/locking-and-unlocking-tables-in-oracle/
Also Check : http://aliimmam.com/lock-tables-and-unlock-tables-syntax/
Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as well. Source : http://dwhlaureate.blogspot.com/2014/07/how-to-unlock-locked-table-in-oracle.html

*********************************************************************************************************************

Share