Workflow Mailer Script to start notification mailer

 
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;
/

Workflow Mailer Script to stop notification mailer

 
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;
/

HA: Scripts to check if archive logs are being shipped and applied to standby

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;

RAC: List info of all long operations in whole RAC

 
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#;

RAC: List all current sessions in whole RAC

 
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:

RAC: Displays memory allocations for all current sessions in whole RAC

 
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 ;

RAC: List details for sessions wait from all instances in RAC.

 
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 ;

ASM: Disks performance metric

 
SET ECHO off
SET FEEDBACK 8
SET HEADING ON
SET LINESIZE 230
SET PAGESIZE 300
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'
COLUMN disk_path FORMAT a20 HEAD 'Disk Path'
COLUMN reads FORMAT 999,999,999,999 HEAD 'Reads'
COLUMN writes FORMAT 999,999,999,999 HEAD 'Writes'
COLUMN read_errs FORMAT 999,999,999 HEAD 'Read|Errors'
COLUMN write_errs FORMAT 999,999,999 HEAD 'Write|Errors'
COLUMN read_time FORMAT 999,999,999,999 HEAD 'Read|Time'
COLUMN write_time FORMAT 999,999,999,999 HEAD 'Write|Time'
COLUMN bytes_read FORMAT 999,999,999,999,999 HEAD 'Bytes|Read'
COLUMN bytes_written FORMAT 999,999,999,999,999 HEAD 'Bytes|Written'
BREAK ON report ON disk_group_name SKIP 2
COMPUTE sum LABEL "" OF reads writes read_errs write_errs read_time write_time bytes_read bytes_written ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF reads writes read_errs write_errs read_time write_time bytes_read bytes_written ON report
SELECT
a.name disk_group_name
, b.path disk_path
, b.reads reads
, b.writes writes
, b.read_errs read_errs
, b.write_errs write_errs
, b.read_time read_time
, b.write_time write_time
, b.bytes_read bytes_read
, b.bytes_written bytes_written
FROM v$asm_diskgroup a JOIN v$asm_disk b USING (group_number)
ORDER BY a.name ;