Wednesday, September 17, 2008

How to stop concurrent requests

How to Terminate running Concurrent Requests- in Oracle Apps>

Set Terminating or Running to phase_code- Completed and status_code-Terminated 

Many times, we get request from development team, that a request is running from long time and doing nothing or to terminate request from back end. In this case, we can use below sql query to update the request to Terminated.

 
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code ='T'
OR phase_code = 'R';

Remember to commit it.

commit;

How to set pending jobs on Hold->

Manytimes, during ERP downtime- like patching etc, we want to place the pending concurrent requests on hold. We can use the below sql query for accomplishing the same.

Place Pending/(Normal/Standby) to On Hold
UPDATE fnd_concurrent_requests
SET hold_flag = 'Y'
WHERE phase_code = 'P'
AND status_code in ('Q','I');

commit;

The above two queries can also be used if you don't want to run the requests which are copied from source system to target system during cloning. The above can be executed before making the CM up.

Also, to remove the hold from concurrent request after the downtime, we can use following query-

UPDATE fnd_concurrent_requests 
SET hold_flag = 'N' 
WHERE phase_code = 'P' 
AND status_code in ('Q','I');

commit;

How to find os process id,sid of a concurrent request

select sid,serial#,process,module,action from v$session where process=(SELECT p.os_process_id FROM FND_CONCURRENT_REQUESTS r, FND_CONCURRENT_PROCESSES p where r.controlling_manager = p.concurrent_process_id and request_id=2759835)

Here process is the os process id for the concurrent request.

Status code and Phase code for Concurrent requests

Here is what the abbreviation for status code and phase code means for Concurrent Requests in Oracle Apps

STATUS_CODE Column:

A Waiting
B Resuming
C Normal
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting


PHASE_CODE column

C Completed
I Inactive
P Pending
R Running

 

Thus a request in "Running" "Normal" state will have phase_code "R" and status_code"R"

Saturday, September 13, 2008

What is Autoconfig

AutoConfig is a tool that automates the configuration of an Oracle Applications system. In Oracle Apps 11i,the whole of system information is stored in 2 repository-one database context file located at $ORACLE_HOME/appsutil/<context_name>.xml  and other applications context file located at APPL_TOP/admin/context_name>.xml. When we run autoconfig, the information in context files is used to instantiate the template files and create the configuration files and set the profile options.

How do i run Autoconfig?

The database services should be up but the application services should be down while running autoconfig.

On the Dbtier->login as owner of db-oracle user

cd $ORACLE_HOME/appsutil/scripts/<context_name>

./adautocfg.sh

On the Apps Tier->login as owner of applications-applmgr user

cd $COMMON_TOP/admin/scripts/<CONTEXT_NAME>

./adautocfg.sh

The log files created by AutoConfig is located at:

On the application tier:
<APPL_TOP>/admin/<Context_name>/log/<MMDDhhmm>/adconfig.log

On the database tier:
<RDBMS_ORACLE_HOME>/appsutil/log/<Context_name>/<MMDDhhmm>/adconfig.log

where: <MMDDhhmm> = (month, day, hour, and minute of the AutoConfig run)

<context_name>=<SID>_<Hostname>

Technorati Tags: ,

Matalink Note:218089.1 "Frequently Asked Questions About Using AutoConfig With Oracle Applications" is a good note regarding Autonfig

Friday, September 12, 2008

CM not starting after Cloning

Once after doing Rapid cloning some of concurrent managers were not starting. The issue was that the node was wrongly defined as source node in Administer Manager screen. I then checked the FND_NODES table which wrongly had the source nodes as well apart from the target nodes.

I performed the following action plan which resolved the issue->

  • Shutdown the apps services
  • EXEC FND_CONC_CLONE.SETUP_CLEAN;
    COMMIT;
    EXIT;
  • Ran AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers.
  • Start the apps services

Actually FND_CONC_CLONE.SETUP_CLEAN clears up the tables with nodes information and when we run autoconfig, it repopulates these tables with correct node information.

The metalink note-Note:732709.1 describes this issue.

Wednesday, September 10, 2008

Oracle Apps Architecture

Oracle Apps has a 3 tier architecture.

First tier is that database server which stores whole of data.

Second tier is the application tier-also called Middle tier where forms server, web server, reports server, concurrent processing server, admin server, discoverer server resides.

Third tier is the client desktop which should be java enabled.

Now we can have a single node configuration or multi-node configuration. In single node we have the database and all middle tier components on a single node(i.e single physical server).

However in multi-node we can distribute the middle tier components, in the way we like. Typically, we can have database, admin server, reports server,concurrent managers on 1st node and forms & web server on 2nd node. Another possible configuration is having database on first node, Admin server, reports server, concurrent managers on second node and forms and web server on 3rd node. So this is a 3 node configuration.

Tuesday, September 9, 2008

How to determine if database is using spfile

If we want to determine whether by database is using an spfile or pfile then we can use the following to determine it->

show parameter spfile;

or

select value from v$parameter where name='spfile';

NAME              TYPE        VALUE
------------------------------------ ----------- ---------------
spfile              string      ?/dbs/spfile@.ora

The above shows that it is using spfile.

If value returned is null then system is using init.ora file otherwise the mentioned spile is being used.

select isspecified, count(*) from v$spparameter group by isspecified;



ISSPEC   COUNT(*)

------ ----------


FALSE         214


TRUE           61



If you get isspecified is 'TRUE' for some parameters, it means it is using spfile. However if you get only 'FALSE' without any 'TRUE' then it uses init.ora file. The above shows that it is using spfile.



To get more information about pfile and spfile, please follow my earlier post->



http://oracleappsdba1.blogspot.com/2008/09/how-to-use-spfile-for-database-startup.html



Technorati Tags: ,,

How to use spfile for database startup

First of all, I would clarify what is the difference between pfile and spfile. Pfile is the text file -database initialization parameter file which is read at the time of database startup. The dynamic parameters can be changed using alter system command but they are not persistent and to make them permanent we have to make changes in init.ora file and then we  need to bounce the database for the effect to take place.

However spfile is the server parameter file which is a binary file, resides on the server and whatever changes we do using 'alter system' are persistent (depending on scope we define) and hence we don't have to manually edit the file and no need to bounce the db. Please note that it is a binary file and we can't edit it manually.

When the database is started it will read the initialization parameter file in the following order from $ORACLE_HOME/dbs -unix or $ORACLE_HOME/database-windows

1 spfile<SID>.ora

2 spfile.ora

3 init<SID>.ora

How to create spfile->

connect as sysdba

create spfile from pfile;

how to create pfile from spfile->

alter database create pfile from spfile.

Please not that you have correct permissions of this file in unix or else database wont start using spfile and it will five the following error, if pfile is not there->

LRM-00109: could not open parameter file '/u01/oradb/9.2.0/dbs/initSID.ora'
ORA-01078: failure in processing system parameters

Thursday, September 4, 2008

Query to find what all users have a particular responsibility

In Oracle Apps 11i, if we want to find which all users have a particular responsibility or which all responsibility does a user have,responsibility is end dated for the user or not,user is end dates or not then we can use the following query->

select fu.user_name,fu.description,fu.user_id,frtl.responsibility_name,furgd.start_date user_resp_start_date,furgd.end_date user_resp_end_date,fu.start_date USER_start_date,fu.end_date USER_end_date
from
fnd_user fu,
fnd_responsibility_tl frtl,
fnd_user_resp_groups_direct furgd
where
fu.user_id=furgd.user_id and
frtl.responsibility_id=furgd.responsibility_id and
(fu.user_name=:user_name or frtl.responsibility_name=:responsibility_name)

Wednesday, September 3, 2008

Query to find responsibility for a concurrent request

In oracle apps it often happens that we want to run a concurrent request but we don't know that from which responsibility we have to run it. The following query takes the concurrent program name as input and gives the responsibility name from which it can be run->

SELECT
C.CONCURRENT_PROGRAM_NAME,USER_CONCURRENT_PROGRAM_NAME,REQUEST_GROUP_NAME,fr.responsibility_name
FROM
FND_REQUEST_GROUPS A,
FND_REQUEST_GROUP_UNITS B,
FND_CONCURRENT_PROGRAMS_VL C,
fnd_responsibility_vl fr
WHERE
A.REQUEST_GROUP_ID = B.REQUEST_GROUP_ID
AND A.APPLICATION_ID = B.APPLICATION_ID
AND C.CONCURRENT_PROGRAM_ID = b.REQUEST_UNIT_ID
and a.request_group_id=fr.request_group_id
AND USER_CONCURRENT_PROGRAM_NAME like  :a

Oracle Application Server Release Version

The following information will help you identify the version of Application server:

->

Oracle9i Application Server Release 1 Version 1.0.2.2.2
Oracle9i Application Server Release 2 Version 9.0.2
Oracle9i Application Server Release 2 Version 9.0.3


Oracle Application Server 10g refers to all subsequent application server (middle-tier) releases, including:->

Oracle Application Server 10g Release 1 Version 9.0.4
Oracle Application Server 10g Release 1 Version 9.0.4.1
Oracle Application Server 10g Release 2 Version 10.1.2.0.0
Oracle Application Server 10g Release 2 Version 10.1.2.0.1
Oracle Application Server 10g Release 2 Version 10.1.2.0.2
Oracle Application Server 10g Release 3 Version 10.1.3