Month: April 2019

 
sqlplus apps/apps_pwd 

 
declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
-- Find mailer Id
-----------------
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
--------------
-- Start Mailer
--------------
fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
commit;
end;
/ 

 
sqlplus apps/apps_wpd 

 
declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
-- Find mailer Id
-----------------
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
--------------
-- Stop Mailer
--------------
fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);
commit;
end;
/ 

 
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'); 

Note: Possible values:
RUNNING
STARTING
STOPPED_ERROR
DEACTIVATED_USER
DEACTIVATED_SYSTEM

 
sqlplus apps/apps_pwd 

Note: Number of running processes should be greater than 0


 
select running_processes
from apps.fnd_concurrent_queues
where concurrent_queue_name = 'WFMLRSVC'; 

1. Steps to check archive logs are being shipped and applied to standby.

AT PRIMARY:

A.  Check the status.

Note: Status should return valid for standby destination

 
set lines 500
column destination format a35 wrap
column process format a7
column archiver format a8
column ID format 99
select dest_id "ID",destination,status,target,archiver,schedule,process,mountid from v$archive_dest ;
 

Sample Output:

B. Check if any error is reported for archive dest.

 Note: Should not return any error messages

 
column error format a55 tru
select dest_id,status,error from v$archive_dest; 

Sample Output:

C. Check the errors from dataguard status.
Note: Should not return any row.


 
column message format a80
select message, timestamp from v$dataguard_status where severity in ('Error','Fatal') order by timestamp; 

AT STANBDY:

A. Check MRP status and which block is being applied at standby database.

 
set linesize 230
select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby ; 

Sample Output:

B. Check the error for dataguard status
Note: Should not return any rows.


 
column message format a80
select message, timestamp from v$dataguard_status where severity in ('Error','Fatal') order by timestamp; 

 
SET LINESIZE 230
COLUMN sid FORMAT 9999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A25
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A20
COLUMN remaining FORMAT A20
SELECT s.inst_id,
s.sid,
s.serial#,
s.username,
s.module,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.inst_id = sl.inst_id
AND s.serial# = sl.serial#; 

 
SET LINESIZE 230
SET PAGESIZE 2300
COLUMN username FORMAT A25
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A25
SELECT NVL(s.username, '(oracle)') AS username,
s.inst_id,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM gv$session s,
gv$process p
WHERE s.paddr = p.addr
AND s.inst_id = p.inst_id
ORDER BY s.username, s.osuser ; 

Sample Output:

 
SET LINESIZE 230
COLUMN username FORMAT A30
COLUMN module FORMAT A25
SELECT a.inst_id,
NVL(a.username,'(oracle)') AS username,
a.module,
a.program,
Trunc(b.value/1024) AS memory_kb
FROM gv$session a,
gv$sesstat b,
gv$statname c
WHERE a.sid = b.sid
AND a.inst_id = b.inst_id
AND b.statistic# = c.statistic#
AND b.inst_id = c.inst_id
AND c.name = 'session pga memory'
AND a.program IS NOT NULL
ORDER BY b.value DESC ; 

 
SET LINESIZE 230
SET PAGESIZE 300
COLUMN username FORMAT A30
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A20
SELECT s.inst_id,
NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_class,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM gv$session_wait sw, gv$session s
WHERE s.sid = sw.sid
AND s.inst_id = sw.inst_id
ORDER BY sw.seconds_in_wait DESC ;