EBS : Find Concurrent Request id from SID

Solution:

SELECT s.inst_id,a.request_id,s.sid,s.serial#,c.spid
  FROM apps.fnd_concurrent_requests a, gv$process c, gv$session s
   WHERE s.sid in ('&sid')
  AND s.paddr = c.addr
  AND a.oracle_process_id = c.spid
  AND a.phase_code = UPPER ('R');

EBS : Update Workflow mailer details from backend

Use the below script to update Workflow mailers details from backend:

sqlplus apps/apps @$FND_TOP/sql/afsvcpup.sql 

R12.2 : fs_clone failed with "s_atName" and "s_shared_file_system" Context Variables Multinode configuration

Error:

 [WARNING]: There could be issues while validating the ports used for E-Business Suite instance against ports used in /etc/services. Refer the log file for more details.
        [WARNING]: Either some of the required entries in /etc/hosts file might be missing (e.g. localhost or hostname) OR the file /etc/hosts could not be read.

Solution:

Note: appnode1 is primary, appnode2 is secondary

1. Check for the below in $CONTEXT_FILES both RUN & PATCH file system

• s_shared_file_system is set to true for SHARED_APPL_TOP if not change it to true 
• s_atName  is set to appnode1 hostname in CONTEXT_FILE of appnode2, if not set it appnode1 hostname

2. Check the values using below command sourcing patch/run:

• grep -i "s_atName" $CONTEX_FILE
• grep -i "s_shared_file_system" $CONTEX_FILE

3. Shut down application services
4. Run EXEC FND_CONC_CLONE.SETUP_CLEAN;
5. Run Autoconfig in dbTier, appnode1 run&patch , appnode2 run&patch
6. Run the fs_clone now.

Reference:
adop prepare fails with "s_atName And s_shared_file_system Context Variables Are Not In SYNC" (Doc ID 2176137.1)

APPS : XML reports completing with warning

In cloned instance, all the reports are completeing with Warning state

Alert Log Error:
Errors in file /data/UAT/db/tech_st/11.2.0.3/admin/UAT_bctuat/diag/rdbms/uat/UAT/trace/UAT_ora_17385.trc  (incident=9206):
ORA-00600: internal error code, arguments: [kqludp2], [0x096D99A48], [0], [], [], [], [], [], [], [], [], []

Solution:
1. Shut everything down (Concurrent Manager ).
IMPORTANT: Verify NO Concurrent Manager processes are running on OS. This includes FNDLIBR, FNDSM, etc.

2. Run $FND_TOP/patch/115/sql/afopp001.sql as SYSTEM.
- Provide system username and password

3. Run $FND_TOP/patch/115/sql/afopp002.sql as APPLSYS.
- Provide apps username and password

4. Restart managers

5. Test the Reports.


Reference : (Document-id 1394729.1)

EBS : To find version of JDBC thin driver in iAS

Solution:

Create a file with name JDBCVersion.java in your middle tier (Application Tier)
---------
import java.sql.*;
import oracle.jdbc.driver.*;
class JDBCVersion
{
public static void main (String args[])
throws SQLException
{
// Load the Oracle JDBC driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// Get a connection to a database
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=YES)(FAILOVER=YES)
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST= <hostname>) (PORT=
<portno>)))(CONNECT_DATA=(SID=<yoursid>)))" ,"apps","<appspaswd>");
// Create Oracle DatabaseMetaData object
DatabaseMetaData meta = conn.getMetaData();
// gets driver info:
System.out.println("JDBC driver version is " + meta.getDriverVersion());
}
}
-----------

Replace following parameters
1. <hostname> with your database hostname or IP address
2. <portno> with your database port no.
3. <yoursid> with SID for your database
4. <appspaswd> with your apps password
After changing save it with name JDBCVersion.java in your middle tier & execute command
[Server@oracle]$ javac JDBCVersion.java
This will create class file in your workign directory. Include your current directory into your
classpath like
export CLASSPATH=/<location where JDBCVersion.class created by above:$CLASSPATH
program>
then execute
[Server@oracle]$ java JDBCVersion 
you should see output like
JDBC driver version is 11.2.0.1.0
Which means you are using jdbc thin driver version 11.2.0.1.0

Linux : Get the process id of deleted file but existing in the directory

Solution:

[root@testenv]$ lsof | grep deleted | grep maillog_1
chrome     3446       user  128u      REG              253,2              16400       2364626 /var/tmp/maillog_1 (deleted)
[root@testenv]$ kill -9 3446

EBS : Worflow related queries

1. To check workflow mailer service current status

  sqlplus apps/<apps_pwd>
  select running_processes from apps.fnd_concurrent_queues where concurrent_queue_name = 'WFMLRSVC';

  Number of running processes should be greater than 0

2. Find current mailer status

  sqlplus apps/<apps_pwd>
  select component_status
    from apps.fnd_svc_components
   where component_id =
    (select component_id
     from apps.fnd_svc_components
     where component_name = 'Workflow Notification Mailer');

  Values:
  RUNNING
  STARTING
  STOPPED_ERROR
  DEACTIVATED_USER
  DEACTIVATED_SYSTEM

3. Starting Workflow notification mailer

  sqlplus apps/<apps_pwd>
  declare
       p_retcode number;
       p_errbuf varchar2(100);
       m_mailerid fnd_svc_components.component_id%TYPE;
  begin -- To find mailer Id
       select component_id
      into m_mailerid
      from fnd_svc_components
      where component_name = 'Workflow Notification Mailer';
       fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);        -- Starting the Workflow Mailer
      commit;
  end;
  /


4. Stopping Workflow notification mailer

  sqlplus apps/<apps_pwd>
  declare
       p_retcode number;
       p_errbuf varchar2(100);
       m_mailerid fnd_svc_components.component_id%TYPE;
  begin -- To find mailer Id
       select component_id
      into m_mailerid
      from fnd_svc_components
      where component_name = 'Workflow Notification Mailer';
       fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);        -- Stopping the Workflow Mailer
      commit;
  end;
  /


5. To find the open notifications count by message_type

select message_type, mail_status, count(*) from wf_notifications where status = 'OPEN' GROUP BY MESSAGE_TYPE, MAIL_STATUS

6. To find the alerts notifications we need to query WF_NOTIFICATION_OUT queue

select corr_id, retry_count, msg_state, count(*) from applsys.aq$wf_notification_out group by corr_id, msg_state, retry_count order by count(*) desc;