Concurrent managers in Oracle Application with Applicable output

The following queries and scripts are useful to fulfill any requirement regarding Concurrent managers in Oracle Application with Applicable output.

spool ccm.lst
prompt Step 1 Checking how many rows in FND_CONCURRENT_REQUEST.

select count(*) from fnd_concurrent_requests;
prompt
-----------------------------------------
Output

COUNT(*)
21845


prompt Step 2 Checking how many rows in FND_CONCURRENT_PROCESSES table.

select count(*) from fnd_concurrent_processes;
prompt
-----------------------------------------------
COUNT(*)
213

prompt Step 3 Checking sys.dual table which should have one and only one row.
select count(*) from sys.dual;

prompt If you have more than one row in sys.dual, please delete it

prompt sql> delete rownum from SYS.DUAL;
Prompt rownum= the row number to delete
prompt
prompt
prompt
---------------------------------------------


prompt Step 4 Checking fnd_dual. There must be at lest one row:

select count(*) from fnd_dual;

prompt If there are no record selected,
prompt Update fnd_dual table to have at lest one record
prompt
----------------------------------------------

prompt Step 5 Checking the Internal Manager queue name "FNDICM" which should be=1

select concurrent_queue_id from fnd_concurrent_queues
where concurrent_queue_name='FNDICM';

prompt
----------------------------------------------

prompt Step 6 Checking for Active processes under the Internal Manager queue
prompt in fnd_concurrent_proceses table:
prompt

select a.concurrent_queue_name
, substr(b.os_process_id,0,10) "OS Proc"
, b.oracle_process_id "Oracle ID"
, b.process_status_code
from fnd_concurrent_queues a
, fnd_concurrent_processes b
where a.concurrent_queue_id=b.concurrent_queue_id
and a.concurrent_queue_name='FNDICM'
and b.process_status_code='A'
order by b.process_status_code;

prompt If any rows found with process_status_code with value = 'A' (= Active)
prompt The internal Manager will not start up ,so to avoide this issue
prompt update these rows to have process_status_code value ='K'(terminated)
prompt
prompt

Output
CONCURRENT_QUEUE_NAME OS Proc Oracle ID PROCESS_STATUS_CODE
FNDICM 14234 43 A

-----------------------------------------
prompt Step 7 Checking for Active processes under the Standard Manager queue
prompt in fnd_concurrent_proceses table:
prompt

select a.concurrent_queue_name
, substr(b.os_process_id,0,10) "OS Proc"
, b.oracle_process_id "Oracle ID"
, b.process_status_code
from fnd_concurrent_queues a
, fnd_concurrent_processes b
where a.concurrent_queue_id=b.concurrent_queue_id
and a.concurrent_queue_name='STANDARD'
and b.process_status_code='A'
order by b.process_status_code;

prompt If any rows found with process_status_code with value = 'A' (= Active)
prompt The internal Manager will not start up ,so to avoide this issue
prompt update these rows to have process_status_code value ='K'(terminated)
prompt
prompt

CONCURRENT_QUEUE_NAME OS Proc Oracle ID PROCESS_STATUS_CODE
1 STANDARD 839 21 A
2 STANDARD 842 35 A
3 STANDARD 845 38 A
4 STANDARD 848 67 A
5 STANDARD 849 22 A
6 STANDARD 850 65 A
7 STANDARD 853 36 A
8 STANDARD 854 57 A
9 STANDARD 855 41 A
10 STANDARD 862 24 A
------------------------------------------
prompt Step 8 Checking for Active processes under the Conflict Manager queue
prompt in fnd_concurrent_proceses table:
prompt

select a.concurrent_queue_name
, substr(b.os_process_id,0,10) "OS Proc"
, b.oracle_process_id "Oracle ID"
, b.process_status_code
from fnd_concurrent_queues a
, fnd_concurrent_processes b
where a.concurrent_queue_id=b.concurrent_queue_id
and a.concurrent_queue_name='FNDCRM'
and b.process_status_code='A'
order by b.process_status_code;

prompt If any rows found with process_status_code with value = 'A' (= Active)
prompt The internal Manager will not start up ,so to avoide this issue
prompt update these rows to have process_status_code value ='K'(terminated)
prompt
prompt
---------------------------------------------------
prompt Step 9 Checking Actual and Target Processes for Internal Manager:

select MAX_PROCESSES,RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME='FNDICM';

prompt If the MAX_PROCESSES=RUNNING_PROCESSES that means the manager is UP.
prompt
prompt
--------------------------------------------------------

prompt Step 10 Checking Actual and Target Processes for the Standard Manager:

select MAX_PROCESSES,RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME='STANDARD';

prompt If the MAX_PROCESSES=RUNNING_PROCESSES that means the manager is UP.
prompt
prompt
---------------------------------------------------------
prompt Step 11 Checking Actual and Target Processes for Conflict Resolution Manager:

select MAX_PROCESSES,RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME='FNDCRM';

prompt If the MAX_PROCESSES=RUNNING_PROCESSES that means the manager is UP.
prompt
prompt
---------------------------------------------------------

Prompt Step 12 Checking if the control_code set to 'N':

select control_code from fnd_concurrent_queues
where control_code='N';
prompt
prompt If any rows selected, please update the table fnd_concurrent_queues:
prompt Update fnd_concurrent_queues set control_code = null
prompt where control_code ='N';
PROMPT Update fnd_concurrent_queues set target_node = null;
PROMPT commit;
prompt
prompt
--------------------------------

PROMPT Step 13 Checking terminated processes:
PROMPT
select count (*) from fnd_concurrent_requests
where status_code='T';
prompt
prompt If you have terminated processes run the following sql statement:
prompt
prompt SQL> Update fnd_concurrent_requests
prompt set status_code = 'E', phase_code = 'C'
prompt where status_code = 'T';
prompt
------------------------------------------


prompt Step 14 Checking pending requests:

select count(*) from fnd_concurrent_requests
where status_code='P';
prompt If any rows selected please run the following sql statement:

prompt SQL> Update fnd_concurrent_requests
prompt set status_code = 'E', phase_code = 'C'
prompt where status_code = 'P';
prompt
------------------------------------------------------
prompt Step 15 Checking Running processes:
prompt
select count (*) from fnd_concurrent_requests
where status_code='R';
prompt
prompt If you have Running processes run the following sql statement
prompt SQL> Update fnd_concurrent_requests
prompt set status_code = 'E', phase_code = 'C'
prompt where status_code = 'R';
prompt
------------------------------------------

prompt Step 16 Checking the PMON method, which should be set to LOCK:
prompt
select profile_option_id , profile_option_value
from FND_PROFILE_OPTION_VALUES
where profile_option_id= (select profile_option_id
from FND_PROFILE_OPTIONS
where profile_option_name='CONC_PMON_METHOD');
prompt
prompt If the PROFILE_OPTION_VALUE was't LOCK please
prompt Reset PMON to LOCK by running afimpmon.sql script(The manager should be down)

prompt 1-At UNIX command prompt:

prompt 2-cd $FND_TOP/sql

prompt 3-Log into SQLPLUS as apps/

prompt SQL> @afimpmon.sql
prompt prompt1:dual
prompt prompt2:LOCK (LOCK MUST BE ALL UPPERCASE)

prompt For Oracle Applications Release 11.5, when you check the PMON
prompt Method you may get no rows selected which is normal,
prompt because in apps 11.5 the Pmon Method is hard coded to Lock at
prompt the Operating System level.
prompt
prompt
-------------------------------------------------------

prompt Step-17 Checking how many FNDLIBR processes are running:
prompt -For Unix :From unix command prompt $ ps -ef grep -i fndlibr
prompt If you have any FNDLIBR processes running,please kill them before
prompt starting or shuting down the internal manager
prompt
prompt
prompt -For NT, through Task Manager, check the entries under the Processes tab
for FNDLIBR.exe processes.
prompt If there are any, Highlight and click [End Process] button to kill processes

prompt
----------------------------------------------------------

prompt Step-18 Checking how many "FND_%"invalid objects:

select substr(owner,1, 12) owner, substr(object_type,1,12) type,
substr(status,1,8) status, substr(object_name, 1, 25) name
from dba_objects
where object_name like 'FND_%'
and status='INVALID';

prompt If you have any invalied objects please see note#113947.1 via Metalink

prompt
--------------------------------------------------------------

prompt Step-19-How to find the PID in the O/S for request_id:
prompt If you do not like to check this enter any number then click Enter to Exit

select r.request_id, p.os_process_id
from FND_CONCURRENT_REQUESTS r,FND_CONCURRENT_PROCESSES p
where r.controlling_manager = p.concurrent_process_id
and request_id=&request_id;

prompt
prompt Please upload the "ccm.lst" output to Support, Thanks.
prompt
spool off

How to apply India Localization patches?

1. We have to install India localization Patch Application Tool by downloading patch 6491231
2. Copy the downloaded patch to $JA_TOP and unzip the same there
3. A directory inpatch will be created afer unzipping. In which india localization patch tool avaialable
4. Go to india localization patch directory
5. use following command
perl $JA_TOP/inpatch/indpatch.pl drvr_file=6355941.drv fnd_patchset=H appspwd=apps japwd=ja logfile=6355941.log systempwd=manager
fnd_patchset= FND PATCHSET LEVEL
japwd = ja_top password
drv_file=patchnumber.drv file
2. How do you know what are india localization patches applied? Solution : using JAI_APPLIED_PATCHES

Note# If you don't know ja top password reset it using FNDCPASS command and once patching is completed reset it back to original password.

Recover root password in Linux

It happens sometime that you can't remember root password.
On Linux, recovering root password can be done by booting Linux under a specific mode:
single user mode.
This tutorial will show how to boot Linux in single user mode when
using GRUB and finally how to change root password.

During normal usage, a Linux OS runs under runlevels between 2 and 5 which corresponds
to various multi-user modes.

Booting Linux under runlevel 1 will allow one to enter into a specific mode,
single user mode. Under such a level, you directly get a root prompt.

Steps Involved are
==================
1. Entering runlevel 1
Some Linux distribution, such as Ubuntu for instance, offer a specific boot menu entry where it is stated "Recovery Mode" or "Single-User Mode". If this is your case, selecting this menu entry will boot your machine into single user mode, you can carry on with the next part. If not, you might want to read this part.

Using GRUB, you can manually edit the proposed menu entry at boot time. To do so, when GRUB is presenting the menu list (you might need to press ESC first), follow those instructions:

use the arrows to select the boot entry you want to modify.
press e to edit the entry
use the arrows to go to kernel line
press e to edit this entry
at the end of the line add the word single
press ESC to go back to the parent menu
press b to boot this kernel
The kernel should be booting as usual (except for the graphical splash screen you might be used to), and you will finally get a root prompt (sh#).

Here we are, we have gained root access to the filesystem, let's finally change the password.

2. Changing root password
As root, changing password does not ask for your old password, therefore running the command: #passwd

Using pipe to Export/Import large files of > 2gb

Source:-
http://www.idevelopment.info/data/Oracle/DBA_tips/Export_Import/EXP_2.shtml
Example: (Using Compress and Split)

Export
#!/bin/ksh

# +---------------------------------------+
# | Change directory to the EXPORT_DIR. |
# +---------------------------------------+
cd /u03/app/oradata/TESTDB/export
pwd

# +---------------------------------------+
# | Remove previous pipes (if any) |
# +---------------------------------------+
rm -f compress_pipe
rm -f export_pipe

# +---------------------------------------+
# | Make two new pipes (Compress / Split) |
# +---------------------------------------+
mknod compress_pipe p
mknod export_pipe p
chmod 666 export_pipe compress_pipe

# +---------------------------------------+
# | Start both the Split and Compress |
# | backgroud processes. |
# +---------------------------------------+
nohup split -b 1024m < export_pipe &
nohup compress < compress_pipe > export_pipe &

# +---------------------------------------+
# | Finally, start the export to both |
# | pipes. |
# +---------------------------------------+
exp userid=system/manager file=compress_pipe full=yes log=exportTESTDB.log

# +---------------------------------------+
# | Remove the pipes. |
# +---------------------------------------+
rm -f compress_pipe
rm -f export_pipe
Import
#!/bin/ksh
# +---------------------------------------+
# | Change directory to the EXPORT_DIR. |
# +---------------------------------------+
cd /u03/app/oradata/TESTDB/export
pwd

# +---------------------------------------+
# | Remove previous pipe (if any) |
# +---------------------------------------+
rm -f import_pipe

# +---------------------------------------+
# | Make two new pipes (Compress / Split) |
# +---------------------------------------+
mknod import_pipe p
chmod 666 import_pipe

# +---------------------------------------+
# | Start both the Uncompress |
# | backgroud processes. |
# | This example assumes the export script|
# | (above) created three dump files xaa, |
# | xab and xac. |
# +---------------------------------------+
nohup cat xaa xab xac | uncompress - > import_pipe &

imp userid=system/manager file=import_pipe full=yes ignore=yes log=importTESTDB.log

# +---------------------------------------+
# | Remove the pipe. |
# +---------------------------------------+
rm -f import_pipe

Example: (Using only Split)

Export
#!/bin/ksh

# +---------------------------------------+
# | Change directory to the EXPORT_DIR. |
# +---------------------------------------+
cd /u03/app/oradata/TESTDB/export
pwd

# +---------------------------------------+
# | Remove previous pipes (if any) |
# +---------------------------------------+
rm -f export_pipe

# +---------------------------------------+
# | Make new pipe (Split) |
# +---------------------------------------+
mknod export_pipe p
chmod 666 export_pipe

# +---------------------------------------+
# | Start the Split backgroud process. |
# +---------------------------------------+
nohup split -b 1024m < export_pipe &

# +---------------------------------------+
# | Finally, start the export to the pipe.|
# +---------------------------------------+
exp userid=system/manager file=export_pipe full=yes log=exportTESTDB.log

# +---------------------------------------+
# | Remove the pipe. |
# +---------------------------------------+
rm -f export_pipe
Import
#!/bin/ksh
# +---------------------------------------+
# | Change directory to the EXPORT_DIR. |
# +---------------------------------------+
cd /u03/app/oradata/TESTDB/export
pwd

# +---------------------------------------+
# | Remove previous pipe (if any) |
# +---------------------------------------+
rm -f import_pipe

# +---------------------------------------+
# | Make new pipe (Split) |
# +---------------------------------------+
mknod import_pipe p
chmod 666 import_pipe

# +---------------------------------------+
# | Start the Split backgroud processes. |
# | This example assumes the export script|
# | (above) created three dump files xaa, |
# | xab and xac. |
# +---------------------------------------+
nohup cat xaa xab xac > import_pipe &

imp userid=system/manager file=import_pipe full=yes ignore=yes log=importTESTDB.log

# +---------------------------------------+
# | Remove the pipe. |
# +---------------------------------------+
rm -f import_pipe

Example: (Using only Compress)

Export
#!/bin/ksh

# +---------------------------------------+
# | Change directory to the EXPORT_DIR. |
# +---------------------------------------+
cd /u03/app/oradata/TESTDB/export
pwd

# +---------------------------------------+
# | Remove previous pipes (if any) |
# +---------------------------------------+
rm -f export_pipe

# +---------------------------------------+
# | Make new pipe (for gzip) |
# +---------------------------------------+
mknod export_pipe p
chmod 666 export_pipe

# +---------------------------------------+
# | Start the gzip backgroud process. |
# +---------------------------------------+
nohup cat export_pipe | gzip -9 > expdat.dmp.gz &

# +---------------------------------------+
# | Finally, start the export to the pipe.|
# +---------------------------------------+
exp userid=system/manager file=export_pipe full=yes log=exportTESTDB.log

# +---------------------------------------+
# | Remove the pipe. |
# +---------------------------------------+
rm -f export_pipe
Import
#!/bin/ksh
# +---------------------------------------+
# | Change directory to the EXPORT_DIR. |
# +---------------------------------------+
cd /u03/app/oradata/TESTDB/export
pwd

# +---------------------------------------+
# | Remove previous pipe (if any) |
# +---------------------------------------+
rm -f import_pipe

# +---------------------------------------+
# | Make new pipe (for gzip) |
# +---------------------------------------+
mknod import_pipe p
chmod 666 import_pipe

# +---------------------------------------+
# | Start the gzip backgroud processes. |
# | This example assumes the export script|
# | (above) created a dump file named |
# | expdat.dmp.gz. |
# +---------------------------------------+
nohup gunzip -c expdat.dmp.gz > import_pipe &

imp userid=system/manager file=import_pipe full=yes ignore=yes log=importTESTDB.log

# +---------------------------------------+
# | Remove the pipe. |
# +---------------------------------------+
rm -f import_pipe

How to check nodenames,interconnect,subnet in RAC 10G

olsnodes -n -p -i
asdf1 1 asdf1-rac asdf1-vip
asdf2 2 asdf2-rac asdf2-vip


oifcfg getif
eth0 192.168.141.144 global public
eth2 192.168.146.128 global cluster_interconnect


srvctl config nodeapps -n asdf1 -a
VIP exists.: /asdf1-vip/192.168.141.152/255.255.255.240/eth0


This info can be matched with /etc/hosts and /sbin/ifconfig -a

Troubleshooting sqlplus "/ as sysdba" not working issues

There can be many reasons some of them listed below:
------------------------------------------------------
1)bash-3.00$ cd $ORACLE_HOME/rdbms/lib
bash-3.00$ ls -altr config*
-rw-r----- 1 oracle oracle 260 Jul 6 23:27 config.c
-rw-r----- 1 oracle oracle 1216 Jul 6 23:52 config.o
bash-3.00$ cat config.c
/* SS_DBA_GRP defines the UNIX group ID for adminstrative access. */
/* Refer to the Installation and User's Guide for further information. */

#define SS_DBA_GRP "oracle"
#define SS_OPER_GRP "oraclei"

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP};

Change: #define SS_DBA_GRP "oracle"
to: #define SS_DBA_GRP "dba"
mv config.o config.o.orig
make -f ins_rdbms.mk ioracle
===========================================================
2)Improper relinking during installation
bash-3.00$ pwd
/d1/oracle/product/102/bin
bash-3.00$ ./adapters
Installed Oracle Net transport protocols are:
IPC
BEQ
TCP/IP
SSL
RAW
SDP/IB

Installed Oracle Net naming methods are:

Local Naming (tnsnames.ora)
Oracle Directory Naming
Oracle Host Naming

How to unlock orcladmin

You should use the "oidpasswd" utility to unlock the orcladmin account. Do not confuse this account with the default realm administrator "cn=orcladmin,cn=users,dc=xxxxx,dc=yyyyy".

1. Unlock the orcladmin
$ oidpasswd connect=your-database-sid unlock_su_acct=true
OID DB user password:
OID super user account unlocked successfully.
This unlocks the OID Super User account, cn=orcladmin ONLY. They are two separate accounts. After resetting the orcladmin super user account, you will still not be able to login to SSO using the orcladmin account until you perform the next step.

2. Launch the Oracle Directory Manager:
On unix: run the oidadmin command
On Windows: Start/Programs/Oracle AS10g/Integrated Management Tools/Oracle Directory Manager
Note: the ODM tool must be a 10g client.
login as orcladmin i.e superuser
Using the left menu tree, navigate to Password Policy Management. You will see TWO entries:
cn=PwdPolicyEntry
Password Policy for Realm

3. Edit each of these and change the Password Expiry Time (pwdmaxage) to an appropriate value
5184000 = 60 days (default)
7776000 = 90 days
10368000 = 120 days
15552000 = 180 days
31536000 = 1 year
999999999 = never expire

4. Launch the Oracle Directory Manager and navigate to the realm specific orcladmin account... Entry Management > your_realm > cn=Users where all the users a
re located. Click on the + next to cn=Users. The list is in alphabetical order.
Locate the cn=orcladmin entry. Find the userpassword attribute and reset the value.

5. Try the ldapbind again.
$ldapbind -p 13061 -D cn=orcladmin,cn=Users,dc=organization,dc=gov -w xxxxxxx7orcl
bind successful
$ldapbind -p 13061 -D cn=orcladmin -w xxxxxxx7ias
bind successful

6. Know after testing all binding is successful you can add an entry to OID.

Note#

Changing the Oracle Internet Directory Database Password
===============================================================
The Oracle Internet Directory uses a password when connecting to its own designated Oracle database.
The default for this password when you install Oracle Internet Directory is the same as that for the Oracle Fusion Middleware administrator. You can change this password by using oidpasswd.

The following example shows how to change the Oracle Internet Directory database password,
assuming the database in on the same machine.

oidpasswd connect=dbs1 change_oiddb_pwd=true
current password: oldpassword
new password: newpassword
confirm password: newpassword
password set.


Resetting the Superuser Password
==================================
If you forget the Oracle Internet Directory superuser (cn=orcladmin) password, you can
use the oidpasswd tool to reset it. You must provide the Oracle Internet Directory database password.
When you first install Oracle Internet Directory, the superuser password and Oracle Internet Directory database
password are the same. After installation, however, you can change the Oracle Internet Directory superuser password using ldapmodify.
If you forget the Oracle Internet Directory superuser password, you can reset it using the oidpasswd tool separately.

The following example shows how to reset the Oracle Internet Directory superuser password.
The oidpasswd tool prompts you for the Oracle Internet Directory database password.

Example:

oidpasswd connect=dbs1 reset_su_password=true
OID DB user password: oid_db_password
password: new_su_password
confirm password: new_su_password
OID superuser password reset successfully