OPP is not getting UP and All XML report is giving Error in apps R12

Issue: OPP is not getting UP. XML Reports are going in Error.

Solutions:

This is process how to purge the FND_CP_GSM_OPP_AQTBL manually to clean up the table:

1. Shut down the concurrent managers cleanly.

2. Check the number of records in the table :

SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

Output: Millions of rows.

3. Run the following as SYS:

DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table => 'APPLSYS.FND_CP_GSM_OPP_AQTBL',
purge_condition => NULL,
purge_options => po);
END;
/

4. Re-check again the number of records in the table:

SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

5. Run the script $FND_TOP/patch/115/sql/afopp001.sql as the SYSTEM user.

SQL> @$FND_TOP/patch/115/sql/afopp001.sql
Enter value for 1: SYSTEM
Enter value for 2: manager
Connected.

PL/SQL procedure successfully completed.

Grant succeeded.

Commit complete.

6. Run the script $FND_TOP/patch/115/sql/afopp002.sql as the APPLSYS user.

SQL> @$FND_TOP/patch/115/sql/afopp002.sql
Enter value for 1: applsys
Enter value for 2: appssup
Connected.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

7. Restart the concurrent manager and retest.

In case the purge did not complete successfully after the second time, or did not purge all the queues,
then you would have to recreate the queue. Recreate the queue using $FND_TOP/patch/115/sql/afopp002.sql file as 'APPLSYS' user.
On running the script you will be prompted for username and password. Please note that this may take a longer length of time to complete.

8. Run cmclean twice ensuring commit after each run - the script is available in note 134007.1

9. Start up the application services and retest.

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

Check Locked Session and Concurrent Request in EBS R12

                  /* Use the below query to check Locked Sessions and Concurrent Request */

SQL>select
b.process " Process id ",
b.osuser,
b.machine,
b.sid,
b.serial#,
b.schemaname,
b.module,
b.action,
trunc(b.SECONDS_IN_WAIT/60/60,3) " Waiting Time in H ",
blocking_session,
'*** KILL ONLY IF BLOCKING_SESSION IS NULL, otherwise trace that blocking session till has null *** -- alter system kill session '''||b.sid||','||b.SERIAL#||''' immediate;' " SQL kill session statement" ,
'*** KILL ONLY IF BLOCKING_SESSION IS NULL, otherwise trace that blocking session till has null --- kill -9 '||b.process " OS kill process command",
b.PADDR,
p.SPID,
b.PROCESS
from v$session b , v$process p
where b.PADDR=p.ADDR and b.sid in (select BLOCKING_SESSION from v$session s where  BLOCKING_SESSION is not null);

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

Check FND User Password in EBS R12

      /*Use the below query to check FND password in ERP R12*/

SQL>SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = '&USER_NAME';

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

How to ADD DISK GROUP in ORACLE ASM

How to ADD DISK GROUP in ORACLE ASM

Step.1  System Admin will add the disk on the server which should be visible on all the database nodes.

DBA can check using the below command on the path.

Example:  DISK PATH

/dev/oracleasmdisk

bash-4.3# ll

crwxrwxrwx   1 oragrid    oinstall    13 0x000011 Mar 29 11:31 archivdisk

crwxrwxrwx   1 oragrid    oinstall    13 0x000012 Mar 27 17:05 prddata1disk

crwxrwxrwx   1 oragrid    oinstall    13 0x000027 Mar 23 07:11 prddata2disk

crwxrwxrwx   1 oragrid    oinstall    13 0x00002f Mar 29 08:24 prddata3disk   >>> New Added Disk

crwxrwxrwx   1 oragrid    oinstall    13 0x000026 May  1  2014 prdflash

crwxrwxrwx   1 oragrid    oinstall    13 0x000014 Mar 20 21:02 prdinfradisk

crwxrwxrwx   1 oragrid    oinstall    13 0x000013 Mar 27 17:05 proddatadisk

We check the same disk on all database instance.
Step.2 Login on any database instance using grid user and connect in ASM using SQLPLUS.

Example:-

bash-4.3$ id

uid=112(oragrid) gid=107(oinstall) groups=503(asmadmin),504(asmdba),505(asmoper)

bash-4.3$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 29 11:56:33 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options              

SQL> select name, path, header_status from v$asm_disk;

NAME	PATH	HEADER_STATUS
PRODARCH_0000	/dev/oracleasmdisk/prddata3disk	CANDIDATE	After add disk Header status
PRODDATA1_0000	/dev/oracleasmdisk/archivdisk	MEMBER
PRODINFRA_0000	/dev/oracleasmdisk/prddata1disk	MEMBER
PRODDATA_0000	/dev/oracleasmdisk/prdinfradisk	MEMBER
PRODFLASH1	/dev/oracleasmdisk/prdflash	MEMBER
PRODDATA_0001	/dev/oracleasmdisk/prddata2disk	MEMBER

Step.3 Login using oragrid user and connect through sqlplus / as sysasm . Please follow the below steps.

Example:

SQL> CREATE DISKGROUP PRODDATA2 EXTERNAL REDUNDANCY DISK '/dev/oracleasmdisk/prddata3disk';

 >>>>>.. Execute it on one node

SQL>ALTER DISKGROUP PRODDATA2 mount;

<< Run the mount command in the other remaining two nodes.

Step.4 After add the disk in ASM check the header status result should be like below example.

SQL> select name, path, header_status from v$asm_disk;

NAME	PATH	HEADER_STATUS
PRODARCH_0000	/dev/oracleasmdisk/prddata3disk	MEMBER	Now header status should be member
PRODDATA1_0000	/dev/oracleasmdisk/archivdisk	MEMBER
PRODINFRA_0000	/dev/oracleasmdisk/prddata1disk	MEMBER
PRODDATA_0000	/dev/oracleasmdisk/prdinfradisk	MEMBER
PRODFLASH1	/dev/oracleasmdisk/prdflash	MEMBER
PRODDATA_0001	/dev/oracleasmdisk/prddata2disk	MEMBER

Step.5 Check the added disk in ASM is it mounted or not. You need to check on all 3 nodes.

bash-4.3$ asmcmd lsdg

State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name

MOUNTED  EXTERN  N        1024   4096  1048576    819200   353910                0          353910              0             N  PRODARCH/

MOUNTED  EXTERN  N        1024   4096  1048576   2099200    63706                0           63706              0             N  PRODDATA/

MOUNTED  EXTERN  N        1024   4096  1048576   1740800     5276                0            5276              0             N  PRODDATA1/

MOUNTED  EXTERN  N        1024   4096  1048576    307200   228184                0          228184              0             N  PRODDATA2/

MOUNTED  EXTERN  N        1024   4096  1048576    512000   511852                0          511852              0             N  PRODFLASH/

MOUNTED  EXTERN  N        1024   4096  4194304    204800   204248                0          204248              0             Y  PRODINFRA/

Now you can add new datafiles or tablespace according your requirements.

If your database connected with other OEM application then disk group status will be show mounted until you do not add datafiles in the added disk.

Check the FND Sessions In Oracle APPS R12

Please use the below query to check Number of sessions in Oracle APPS R12.


SQL>select icx.node_id,fnd.node_name,'Number of user sessions : ' || count( distinct icx.session_id) How_many_user_sessions
from icx_sessions icx, fnd_nodes fnd where icx.disabled_flag != 'Y'
and icx.PSEUDO_FLAG = 'N'
and icx.node_id=fnd.node_id
and (icx.last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,icx.limit_time, 0,
icx.limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate and counter < icx.limit_connects
group by icx.node_id,fnd.node_name;

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

Check Long Running Request in Oracle ERP R12

Please use the below query to check long running request in Oracle ERP R12.

SELECT a.request_id
,a.oracle_process_id "SPID"
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.logfile_name
,a.logfile_node_name
,a.outfile_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
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 a.phase_code = 'R'
AND a.status_code = 'R'
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC

START and STOP Local Listener and SCAN Listener in RAC Database.

How to Start and Stop Local Listener and SCAN Listener

First check which services are running from which nodes and users.

oragrid$ps -eaf|grep tns


\*-----How to set environment -----*/

bash-4.3$ which srvctl
/u02/grid/product/11.2.0/bin/srvctl
bash-4.3$ which lsnrctl


bash-4.3$ export PATH=$ORACLE_HOME/bin:$PATH


oragrid$/u02/grid/product/11.2.0/bin/lsnrctl

\*--How to STOP Local Listener----*/    :-Run this command from one by one from all nodes

oragrid$srvctl stop LISTENER -n adupgdb1
oragrid$srvctl stop LISTENER -n oracledb1
oragrid$srvctl stop LISTENER -n oracledb2

\*--How to STOP SCAN Listener----*/  :-Run from any one node

oragrid$srvctl stop scan_listener

\*----- How to START Local Listener------*/

oragrid$srvctl start LISTENER -n adupgdb1
oragrid$srvctl start LISTENER -n oracledb1
oragrid$srvctl start LISTENER -n oracledb2


\*-----How to start SCAN Listener------*/
oragrid$srvctl start scan_listener

********************************************************
\*******How to bounced Single SCAN Listener one by one on nodes*******/

Run from the grid user.

bash-4.3$ srvctl status scan_listener -i 3
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node adupgdb1

bash-4.3$ srvctl stop scan_listener -i 3

bash-4.3$ srvctl status scan_listener -i 3
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is not running

bash-4.3$ srvctl start scan_listener -i 3

bash-4.3$ srvctl status scan_listener -i 3
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node adupgdb1
*************************************************************

Listener Issue in RAC Environment (Services are not registerd in Listener)

If you are facing connection issue in RAC environment then check the both SID and service name is registered in database. Please follow the below steps.

Solution:

Node1 

alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=<vip of node1>)(PORT=1521))' scope=both SID='TEST1';
alter system register;

(Replace "<vip of node1>" with VIP of node1)

Node2

alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=<vip of node2>)(PORT=1521))' scope=both SID='TEST2';
alter system register;

(Replace "<vip of node2>" with VIP of node2)


Check the staus from one by one nodes.

Node1

$lsnrctl status LISTENER

Node2

$lsnrctl status LISTENER

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

Blank Page Issue In Oracle ERP R12

There are could be many reasons of the blank page issue. Recently, I faced below cause of this issue.

ERROR:1 -
Below error appeared in $LOG_HOME/ora/10.1.3/opmn/opmn/oafm_default_group_1/oafmstd.out

11/12/15 14:27:19.974 html: chain failed
javax.servlet.ServletException: java.lang.RuntimeException: ORA-04063: package body "APPS.FND_WEB_SEC" has errors
ORA-06508: PL/SQL: could not find program unit being called: "APPS.FND_WEB_SEC"
ORA-06512: at line 1

at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.EvermindPageContext.handlePageThrowable(EvermindPageContext.java:899)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.4.0) ].server.http.EvermindPageContext.handlePageException(EvermindPageContext.java:816)
at _AppsLocalLogin._jspService(_AppsLocalLogin.java:303)
at com.orionserver[Oracle Containers for J2EE 10g (10.1.3.4.0) ].http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)

Cause: Due to FND_WEB_SEC package was corrupted.

Action Plan:1 Try to compile the package. ( It's not resolved)

SQL> ALTER PACKAGE FND_WEB_SEC COMPILE BODY;

Warning: Package Body altered with compilation errors.

SQL> SHOW ERROR
Errors for PACKAGE BODY FND_WEB_SEC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLS-00753: malformed or corrupted wrapped unit

Solution:

Please execute the following steps:

1. Login to UNIX session of the EBS Apps Tier

2. Set the application environment file

3.  Validate the version of the package FND_WEB_SEC in database

select text from user_source
where NAME= 'FND_WEB_SEC'
and line = 2;

4. Validated the version of FND_WEB_SEC package in file system,

cd $FND_TOP/patch/115/sql

adident Header AFSCJAV8.pls
adident Header AFSCJAVB.pls
adident Header AFSCJAVS.pls

Note : FND_WEB_SEC version in DB and File system should match

5. Execute the below commands

sqlplus apps/<apps pw> $FND_TOP/patch/115/sql/AFSCJAVS.pls
sqlplus apps/<apps pw> $FND_TOP/patch/115/sql/AFSCJAVB.pls
sqlplus apps/<apps pw> $FND_TOP/patch/115/sql/AFSCJAV8.pls

4. Validate that FND_WEB_SEC the package is valid.

select OBJECT_NAME,OBJECT_TYPE,STATUS from all_objects where OBJECT_NAME='FND_WEB_SEC';


5. Retest the issue. 


Solution:2  Check your tablespace size, Might be full.

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

Rename Datafiles in Oracle ASM RAC Database

I prepared below steps one by one how can rename the datafiles in ASM database with RAC environment. Below are the steps is implemented in production environment.

\*-------OLD FILES LOCATION--------*/
+PRODDATA2/prod/datafiles/a_media220.dbf => +PRODDATA2/prod/DATAFILE/APPS_TS_MEDIA.271.909047637

\*-------FILES LOCATIONS WITH NEW NAMES ------*/
+PRODDATA2/prod/datafiles/a_media231.dbf

                                  \*---------ACTION PLAN------------*/

1. SQL>Alter database datafile '+PRODDATA2/prod/datafiles/a_media220.dbf' offline;

2. RMAN>copy datafile '+PRODDATA2/prod/datafiles/a_media220.dbf' to '+PRODDATA2/prod/datafiles/a_media231.dbf';

Go to asmcmd and verify the datafile

3. SQL>Alter Database rename file '+PRODDATA2/prod/datafiles/a_media220.dbf' to '+PRODDATA2/prod/datafiles/a_media231.dbf';

4. RMAN>switch Datafile '+PRODDATA2/prod/datafiles/a_media231.dbf' to copy;

5. SQL>recover datafile '+PRODDATA2/prod/datafiles/a_media231.dbf';

6. SQL>Alter database datafile '+PRODDATA2/prod/datafiles/a_media231.dbf' online;

7. SQL>select * from dba_data_files where file_name='+PRODDATA2/prod/datafiles/a_media231.dbf';

*********************************************************************************
Note: Make sure the OLD file name before removing.

7. Remove OLD file from +PRODDATA2 Group.
Connect with Grid user in ASM to perform below activity.
ASMCMD> ls -l      -----> Check the file which exits in ASM
ASMCMD> pwd
+PRODDATA2/prod/datafiles
ASMCMD> rm a_media220.dbf              /*.... Remove OLD datafiles...*/
ASMCMD> ls

8. Bounced the RAC Database if it required.

$srvctl stop database -d PROD
*********************************************************************************