Workflow Notification Mailer (Oracle E-Business Suite) Status, Stop and Start from backend.

Run all scripts as 'APPS' user.

1) Workflow Notification Mailer Status:

select component_status
from fnd_svc_components
where component_id =
(select component_id
from fnd_svc_components
where component_name = 'Workflow Notification Mailer');

2) Workflow Mailer Services / Service components Status:

select fcq.USER_CONCURRENT_QUEUE_NAME Container_Name, DECODE(fcp.OS_PROCESS_ID,NULL,'NotRunning',fcp.OS_PROCESS_ID) PROCID,
fcq.MAX_PROCESSES TARGET,
fcq.RUNNING_PROCESSES ACTUAL,
fcq.ENABLED_FLAG ENABLED,
fsc.COMPONENT_NAME,
fsc.STARTUP_MODE,
fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, APPS.FND_CP_SERVICES fcs, APPS.FND_CONCURRENT_PROCESSES
fcp, fnd_svc_components fsc
where fcq.MANAGER_TYPE = fcs.SERVICE_ID
and fcs.SERVICE_HANDLE = 'FNDCPGSC'
and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
and fcq.concurrent_queue_id = fcp.concurrent_queue_id(+)
and fcq.application_id = fcp.queue_application_id(+)
and fcp.process_status_code(+) = 'A'
order by fcp.OS_PROCESS_ID, fsc.STARTUP_MODE;

3) Workflow Mailes Status:

select  *
from    wf_notifications
where   1=1
and     begin_Date >= sysdate-1
--and     mail_status='FAILED'
order by begin_Date desc;

4) Stop Notification Mailer from backend:
This will deactivate notification mailer.  Run this script as apps user.

declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
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);
commit;
end;
/

5) Start Notification Mailer from backend:

Run this script as apps user.

declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
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);
commit;
end;
/

Oracle Enterprise Manager Cloud Control 13c SYSMAN Password Reset

Steps to reset the password for SYSMAN in Oracle Enterprise Manager Cloud Control 13c:

The following information must be specified when the command is executed.

1. sys password(sys_pwd)
2. New password for sysman(new_pwd)

Steps to reset,

1. Stop all OMS process
cd $OMS_HOME/bin
emctl stop oms

2. Change the SYSMAN password
cd $OMS_HOME/bin
emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd <sys password> -new_pwd <new sysman password>

Example Output:

$ ./emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd ******* -new_pwd *********

Oracle Enterprise Manager Cloud Control 13c Release 2

Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.

Changing passwords in backend ...

Passwords changed in backend successfully.

Updating repository password in Credential Store...

Successfully updated Repository password in Credential Store.

Restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.

Successfully changed repository password.

3. Stop Admin Server on the primary OMS and restart OMS.
cd <oms home>/bin
emctl stop oms -all
emctl start oms

4. Check that OMS is up and running.

cd <oms home>/bin
emctl status oms -details

How To Retrive APPS Password – R12

Steps to find apps password in R12:

    1)    Connect as SYSTEM or SYS USER
    2)    Create Function to decrypt the encrypt password
    3)    Query for the encrypted password
    4)    Query for decrypt the password
    5)    validate the apps password 


STEP 1:
[oracle@localhost]$sqlplus [system or sys]/<password>

STEP 2:
$SQL>create FUNCTION apps.decrypt_get_pwd(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2)
RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
/

Function created.

STEP 3:
$SQL>select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';
ENCRYPTED_FOUNDATION_PASSWORD
——————————————————————————–
FDSFJKSFSDIO43345NFDF983TRFI3R3OFVFDJVOERGF3REFNOEFEFEWFDSCSDCDSFDS

STEP 4:
$SQL>SELECT apps.decrypt_get_pwd('GUEST/ORACLE',FDSFJKSFSDIO43345NFDF983TRFI3R3OFVFDJVOERGF3REFNOEFEFEWFDSCSDCDSFDS) from dual;

Output:
APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','FDSFJKSFSDIO43345NFDF983TRFI3R3OFVFDJVOERGF3REFNOEFEFEWFDSCSDCDSFDS')
..........................................................................................................
welcome

STEP 5:
$SQL> conn apps/welcome
Connected.

Find inactive FORMS Sessions older than 2hours

Script to find inactive forms sessions older than 2hours:

SELECT
p.spid,
s.process,
s.status,
s.machine,
to_char(s.logon_time,'mm-dd-yy hh24:mi:ss') Logon_Time,
s.last_call_et/3600 Last_Call_ET,
s.action,
s.program,
s.module,
s.sid,
s.serial#,
s.CLIENT_IDENTIFIER
FROM
V$SESSION s
, V$PROCESS p
WHERE
s.paddr = p.addr
AND
s.username IS NOT NULL
AND
s.username = 'APPS'
AND
s.osuser = 'oracle'
AND
s.last_call_et/3600 > 2
and
s.program like '%frmweb%'
and
s.status='INACTIVE' order by logon_time;

Tar / Untar

1) To compress an entire directory
tar -czvf <filename>.tar.gz /home/oracle/data/ 

2) To compress multiple directories and files, execute:
tar -czvf <filename>.tar.gz /home/oracle/data/ /home/oracle/pics/ /home/oracle/<some filename> 

3) To use bzip2 compression instead of gzip by passing the -j option to the tar command:
tar -cjvf <filename>.tar.bz2 /home/oracle/data/

4) To exclude certain files when creating a tarball. The syntax is:
tar -zcvf <filename>.tar.gz --exclude='dir1' --exclude='regex' dir1

 For example, exclude ~/Downloads/ directory:
 tar -czvf /share/backup.tar.gz --exclude="Downloads" /home/oracle/

5) To view files stored in an archive:
tar -ztvf <filename>.tar.gz
tar -jtvf <filename>.tar.bz2

6) To extracting an archive / tar:
tar -xzvf <filename>.tar.gz
tar -xjvf <filename>.tar.bz2

7) To extract the contents of the archive/tar into a specific directory such as /home/oracle/backups/? Try passing the -C DIR option:
tar -xzvf <filename>.tar.gz -C /home/oracle/backups/
tar -xjvf <filename>.tar.bz2 -C /tmp

8) Other Options:
tar cvf - 11.2.0.3    | gzip -c > 11.2.0.3.tar.gz  # To tar a directory
gunzip -c /ora/11.2.0.3.tar.gz   | tar xvf -  ## To untar a directory
tar -zxf /share/apps.tar.gz --directory /appl/   ## Extract to a directory