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;