Workflow Mailer not working after configuring SSL in Oracle EBS R12.2

Issue Description:

We enabled TLS in Oracle EBS R12.2 and configured the Workflow Mailer (SMTP), the services started but the email was not sending. Workflow Mailer log file recorded SSL certificate not valid error.

Solution:

Step 1: Take a backup of cacerts file
$ cd $OA_JRE_TOP/lib/security/
$ cp cacerts cacerts_bkp


Step 2: Import the ca.crt
$ keytool -import -alias email_cert -file ca.crt -trustcacerts -v -
keystore cacerts  

Enter keystore password: [default password changeit]

Trust this certificate? [no]:  Yes
Certificate was added to keystore
[Storing cacerts]


Step 3: Verify the certificate listed in cacerts (OPTIONAL STEP)
keytool -list -v -keystore $OA_JRE_TOP/lib/security/cacerts -storepass changeit > cacertslist01.txt 


Step 4: Update MAILER_SSL_TRUSTSTORE using the below script
sqlplus apps @$FND_TOP/sql/afsvcpup.sql
>> 10010
>> 10223
>> $OA_JRE_TOP/lib/security/cacerts 

Maintenance Scripts - Oracle Database

### Check the current Edition 

select ad_zd.get_edition_type from dual; 


### Changing the current Edition
exec ad_zd.set_edition('PATCH');


### Edition Details 
select
    aed.edition_name "Edition Name"
  , ad_zd.get_edition_type(aed.edition_name) "Type"
  , decode(use.privilege, 'USE', 'ACTIVE', 'RETIRED') "Status"
  , decode(aed.edition_name, sys_context('userenv', 'current_edition_name'),
'CURRENT', '') "Current?"
from
    all_editions aed
  , database_properties prop
  , dba_tab_privs use
where prop.property_name = 'DEFAULT_EDITION'
  and use.privilege(+)   = 'USE'
  and use.owner(+)       = 'SYS'
  and use.grantee(+)     = 'PUBLIC'
  and use.table_name(+)  = aed.edition_name
order by 1
/


### Display Edition level used objects & sessions
select
    obj.object_name  EDITION_NAME
  , ses.STATUS       STATUS
  , ses.USERNAME     USERNAME
  , substr(ses.CLIENT_IDENTIFIER, 1, 10)  CLIENT_ID
  , ses.OSUSER       OSUSER
  , ses.SID||','||ses.serial#   "SID,SERIAL#"
  , ses.MACHINE 
  , substr(ses.PROGRAM, 1, 10) PROGRAM
  , ses.TYPE 
  , substr(ses.MODULE, 1, 30) MODULE
  , ses.ACTION 
  , ses.CLIENT_INFO 
  , ses.LOGON_TIME 
  , ses.SERVICE_NAME
from
    v$session ses
  , dba_objects obj
where obj.object_id = ses.SESSION_EDITION_ID
order by EDITION_NAME, STATUS, SQL_EXEC_START
/


### Dependencies of the Object
select
    dep.OWNER OWNER
  , dep.NAME OBJECT_NAME
  , dep.TYPE OBJECT_TYPE
  , dep.REFERENCED_OWNER
  , dep.REFERENCED_NAME
  , dep.REFERENCED_TYPE
from   DBA_DEPENDENCIES dep
where dep.NAME = upper('&OBJECT_NAME')
  and dep.referenced_type <> 'NON-EXISTENT'
  and (dep.referenced_owner, dep.referenced_name) not in
        (('SYS', 'STANDARD'))
order by 1, 2, 3, 4, 5
/


### Check the Edition view of the Object
select
    evc.view_column_name view_column
  , decode(evc.view_column_name, evc.table_column_name, '=', '===>')  "->"
  , evc.table_column_name table_column
from
    user_synonyms syn
  , dba_editioning_views ev
  , dba_editioning_view_cols evc
where syn.synonym_name = upper('&OBJECT_NAME')
  and ev.owner      = syn.table_owner
  and ev.view_name  = syn.table_name
  and evc.owner     = ev.owner
  and evc.view_name = ev.view_name
order by evc.owner, evc.view_name, evc.view_column_id
/


### To List all editioning views of custom table

select * from dba_editioning_views where table_name like 'XX%';


### To Create editioning views 
exec ad_zd_table.upgrade('OWNER', 'TABLE_NAME') 
Note: Above command creates an APPS synonym as well


 ### RMAN
select ctime "Date", decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type, bsize "Size MB"
from (select trunc(bp.completion_time) ctime, backup_type, round(sum(bp.bytes/1024/1024),2) bsize 
from v$backup_set bs, v$backup_piece bp
where bs.set_stamp = bp.set_stamp and bs.set_count  = bp.set_count and bp.status = 'A'
group by trunc(bp.completion_time), backup_type) order by 1, 2;
  
select TAG, STATUS, START_TIME, round(ELAPSED_SECONDS,2)in_Min, DELETED, BYTES/1024/1024 Size_MB from  v$backup_piece;


### SQL Tuning Advisor using SQL ID
DECLARE
  l_sql_tune_task_id VARCHAR2(1000);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
  sql_id => '9zy3b5xvj1y2t',
  scope => DBMS_SQLTUNE.scope_comprehensive,
  time_limit => 120,
  task_name => '9zy3b5xvj1y2t',
  description => '9zy3b5xvj1y2t');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

begin
DBMS_SQLTUNE.execute_tuning_task(task_name => '9zy3b5xvj1y2t');
end;
/

SELECT DBMS_SQLTUNE.report_tuning_task('9zy3b5xvj1y2t') AS recommendations FROM dual;

### Generate trace for running session
SQL> exec dbms_monitor.session_trace_enable(session_id=>684, serial_num=>36892, waits=>TRUE, binds=>TRUE);
SQL> exec dbms_monitor.session_trace_disable(session_id=>684, serial_num=>36892);

SQL> oradebug setorapid 121 (v$process.PID)[OR]
SQL> oradebug setospid 17179 (v$process.SPID)
Oracle pid: 62, Unix process pid: 17179, image: oracle@test.example.com
SQL> oradebug unlimit
Statement processed.
 
SQL> oradebug event 10046 trace name context forever,level 12;
SQL> oradebug Event 10046 trace name context forever,level 8; -- Recommended for Performance

SQL> oradebug TRACEFILE_NAME;  -- to show trace file name
SQL> oradebug event 10046 trace name context off
Statement processed.

tkprof DEV_ora_113386.trc DEV_ora_113386_tkp.txt explain=apps/apps sort=prsela,exeela,fchela

tkprof <file.trc> <file.prf> explain=apps/<password> sys=no sort='(prsela,exeela,fchela)'

Miscellaneous Scripts - Oracle EBS

### Running Requests
SELECT b.request_id, a.user_concurrent_program_name, b.cancel_or_hold,b.argument_text,
TO_CHAR (b.actual_start_date, 'MM/DD/YY HH24:MI:SS') starttime,
TO_CHAR (b.actual_completion_date, 'MM/DD/YY HH24:MI:SS') endtime,
ROUND ((b.actual_completion_date - b.actual_start_date) * (60 * 24),2) runtime,
b.outcome_code, b.completion_text
FROM fnd_concurrent_programs_tl a, fnd_concurrent_requests b
WHERE a.concurrent_program_id = b.concurrent_program_id
AND b.actual_start_date > SYSDATE - 3
and b.actual_completion_date is null
AND a.LANGUAGE ='US';


### Request Last Run
select fcr.REQUEST_ID, fcp.USER_CONCURRENT_PROGRAM_NAME,fcr.REQUEST_DATE,
fcr.argument_text,fcr.status_code "status",fcr.phase_code "phase",
to_char(fcr.ACTUAL_START_DATE, 'DD-MON-YY HH24:MI:SS') "ACTUAL_START_DATE",
to_char(fcr.ACTUAL_COMPLETION_DATE, 'DD-MON-YY HH24:MI:SS') "ACTUAL_COMPLETION_DATE",
ROUND ((fcr.actual_completion_date - fcr.actual_start_date) * (60 * 24),2) runtime
from fnd_concurrent_requests fcr, fnd_concurrent_programs_tl fcp
where
fcr.CONCURRENT_PROGRAM_ID=fcp.CONCURRENT_PROGRAM_ID
AND fcp.USER_CONCURRENT_PROGRAM_NAME Like '%Gather%'
and ACTUAL_START_DATE between trunc(sysdate) - 10 and sysdate order by REQUESTED_START_DATE;


### Session details of running requests

select USER_CONCURRENT_PROGRAM_NAME,c.request_id,s.sid,p.spid,s.process,s.osuser,s.username,

s.program,s.status,logon_time,last_call_et
from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurrent_programs_tl ct
where oracle_process_id=p.spid
and s.paddr=p.addr and
ct.concurrent_program_id=c.concurrent_program_id
and request_id=&creq_id;


### Client tools connected session
select -- 'alter system disconnect session ''' ||sid|| ',' ||serial#||'''  immediate;',
SID, SERIAL#, STATUS, SERVER, OSUSER, MACHINE, PROGRAM, SQL_ID, MODULE, LOGON_TIME, LAST_CALL_ET/3600
from v$session where
osuser NOT IN ('&osuser')
order by logon_time ASC;


### Object Lock session details
select --'alter system kill session ''' ||v.sid|| ',' || v.serial#||''' immediate;',
--'alter system kill session ''' ||v.sid|| ',' || v.serial#|| ',@' ||v.inst_id|| ''' immediate;'
,do.object_name,do.object_type,v.sid,v.serial#,v.program
,v.machine,v.osuser ,v.client_identifier,v.service_name
,TO_CHAR (v.logon_time, 'MM/DD/YY HH24:MI:SS') starttime
,dl.lock_type
from dba_locks dl,dba_objects do,gv$session v where
do.object_name like 'XX%' and
--v.client_identifier like '%%' and
do.object_type='TABLE'
and dl.lock_id1 = do.object_id
and v.sid = dl.session_id;


### Find Details of SPID
select  --'alter system kill session ''' ||s.sid|| ',' || s.serial#|| ',@' ||s.inst_id|| ''' immediate;',
s.INST_ID, s.SID, s.SERIAL#, s.CLIENT_IDENTIFIER,TO_CHAR (s.logon_time, 'MM/DD/YY HH24:MI:SS') starttime,s.MODULE,s.sql_id ,Sa.Sql_Fulltext
from gv$session s, gv$process pr,v$sqlarea sa where s.paddr = pr.addr
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and pr.spid=18811;

### Find Session Lock
SELECT s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE s1.sid=l1.sid AND s2.sid=l2.sid
AND l1.BLOCK=1 AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2;

select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;


### Gather Stats from back-end
exec dbms_stats.gather_fixed_objects_stats;
exec dbms_stats.gather_dictionary_stats;
exec FND_STATS.GATHER_SCHEMA_STATISTICS('ALL', 10, 3,'NOBACKUP', NULL, 'LASTRUN', 'GATHER AUTO', 10, 'N');
exec FND_STATS.gather_table_stats('ONT','OE_ORDER_LINES_ALL',PERCENT => 100,DEGREE => 4 );
exec fnd_stats.gather_schema_statistics('ONT',PERCENT => 10,DEGREE => 4);


### Current Running SQLs
select s.SID,s.SERIAL#, s.STATUS, s.SERVER, s.OSUSER, sm.SQL_ID, s.MODULE, s.LOGON_TIME,s.MACHINE,s.PROGRAM, round(sm.elapsed_time/60000000,2) elapsed_time, sm.sql_text, sm.cpu_time, sm.buffer_gets, sm.disk_reads
from v$sql_monitor sm, v$session s
where sm.SID=s.SID
and sm.status = 'EXECUTING';


### Recently Changed Profile Options
select p.profile_option_name SHORT_NAME, n.user_profile_option_name "PROFILE NAME",
decode(v.level_id, 10001, 'Site', 10002, 'Application',
10003, 'Responsibility', 10004, 'User', 10005, 'Server',
10007, 'SERVRESP', 'UnDef') LEVEL_SET,
decode(to_char(v.level_id), '10001', '',
'10002', app.application_short_name, '10003', rsp.responsibility_key,
'10005', svr.node_name, '10006', org.name, '10004', usr.user_name,
'10007', 'Serv/resp', 'UnDef') "CONTEXT", v.profile_option_value VALUE, v.LAST_UPDATE_DATE 
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
--and upper(n.user_profile_option_name) like upper('BNE%')
--and trunc(v.LAST_UPDATE_DATE) > trunc(sysdate-170)
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
and v.LAST_UPDATE_DATE is not null
order by last_update_date desc, short_name, level_set;


### Check any debug enabled in profile
select  distinct
        a.application_short_name app_short,
        user_profile_option_name optname,
        decode(level_id,
        10001,'SITE',
        10002,'APP : '||a2.application_short_name,
        10003,'RESP: '||r.responsibility_key,
        10004,'USER: '||u.user_name,
        'Unknown') d_level,
        profile_option_value optval,
        v.last_update_date updated
from fnd_profile_options_vl o,
        fnd_profile_option_values v,
        fnd_application a,
        fnd_application a2,
        fnd_responsibility r,
        fnd_user u
where (
        upper(o.user_profile_option_name) like '%DEBUG%' or
        upper(o.user_profile_option_name) like '%TRACE%'
  or upper(o.user_profile_option_name) like '%LOGG%'
        )
and a.application_id = v.application_id
and o.application_id = v.application_id
and o.profile_option_id = v.profile_option_id
-- Find the associate level for profile
and r.application_id (+) = v.level_value_application_id
and r.responsibility_id (+) = v.level_value
and a2.application_id (+) = v.level_value
and u.user_id (+) = v.level_value
and profile_option_value = 'Y'
order by 2,1,3,4;


### Check any debug enabled in concurrent program
SELECT concurrent_program_name prog_name
     , enable_trace
     , last_update_date
FROM apps.fnd_concurrent_programs fcp
WHERE NVL(enable_trace,'N') = 'Y'
/

### Hold/UnHold the concurrent Requests
SELECT fcr.request_id,fcp.USER_CONCURRENT_PROGRAM_NAME,fcr.phase_code,fcr.status_code,
fcr.HOLD_FLAG,fcr.RESUBMIT_INTERVAL,fcr.RESUBMIT_INTERVAL_UNIT_CODE,fcr.ARGUMENT_TEXT
FROM fnd_concurrent_programs_tl fcp, fnd_concurrent_requests fcr
where fcp.concurrent_program_id=fcr.concurrent_program_id 
and fcr.phase_code = 'P' and fcr.status_code IN ('I','Q')
and fcr.HOLD_FLAG='N';

-- To Hold the Scheduled Requests
update fnd_concurrent_requests set HOLD_FLAG='Y' 
where phase_code = 'P' AND status_code IN ('I','Q') and HOLD_FLAG='N';

-- To REMOVE HOLD on Requests
update fnd_concurrent_requests set HOLD_FLAG='N' 
where phase_code = 'P' AND status_code IN ('I','Q') and HOLD_FLAG='Y';

-- To cancel the pending/scheduled requests
update fnd_concurrent_requests set PHASE_CODE='C', STATUS_CODE='D' 
where phase_code = 'P' AND status_code IN ('I','Q') and HOLD_FLAG='N';


### TEMP Usage Monitor
SELECT * FROM   dba_temp_free_space;


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


### To find how many users connected in EBS
SELECT DISTINCT icx.session_id, icx.user_id, fu.user_name
,TO_CHAR (icx.creation_date, 'MM/DD/YY HH24:MI:SS') creation_date,
TO_CHAR (Icx.Last_connect, 'MM/DD/YY HH24:MI:SS') Last_connect,fu.description
FROM icx_sessions icx, fnd_user fu
WHERE disabled_flag != 'Y'
AND icx.pseudo_flag = 'N'
AND ( last_connect+ DECODE (fnd_profile.VALUE ('ICX_SESSION_TIMEOUT'),NULL, limit_time,0, limit_time,
fnd_profile.VALUE ('ICX_SESSION_TIMEOUT') / 60)/ 24) > SYSDATE
AND icx.counter < limit_connects
AND icx.user_id = fu.user_id;

### To Find Workflow Mailer Status and Log file name
select fl.meaning,fcp.process_status_code,
decode(fcq.concurrent_queue_name,'WFMLRSVC','mailer container','WFALSNRSVC','listener container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and fl.lookup_type='CP_PROCESS_STATUS_CODE' and
fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
order by fcp.logfile_name;