Scripts

Description,Explanation, Execution and Sample Output	Script
Sync status query to be used on Standby Database	sync_stdby.sql
Objects currently Locked	locked_obj.sql
Tablespace Free Space	ts_free.sql
Session information from Process ID	pid.sql
Database Information	db.sql
Patch applied or not?	patch.sql
RMAN backup information	rman_bkp.sql
Check invalid objects	invalids.sql
Finding Blocking sessions	locks.sql
Sql text running that is running by a Session with an SID	sql_text.sql
Particular Table Size	tab_size.sql
Applications Relese/Version	release.sql
RMAN Backup Status	rman_status.sql
Tablespaces Information	tsinfo.sql
Sync status query to be used on Primary Database	sync.sql
Session information from Session ID (SID)	sid.sql
Sessing currently connected to Database	sess_runnig.sql
Privilege associated with a User	user_privs.sql
Oracle Application Products Version	product_status.sql
RMAN Progress Sofar	rman_sofar.sql
Concurrent Manager Status from Back-end	cm_status.sql
Concurrent Request information from Request ID	cr_info.sql
Concurrent Requests Currently Running	crs.sql
Pending Concurrent Requests Count	pend_crs_cnt.sql
Pending Concurrent Requests Count with Conflict Resolution Manager(CRM)	pend_crs_cnt_crm.sql
Pending Concurrent Requests Count with Standard Manager	pend_crs_cnt_stdm.sql
Pending Concurrent Requests Information with all the Managers	pend_cr.sql
Concurrent Requests Information Pending with Conlict Resolution Manager	pend_crs_crm.sql
Concurrent Requests Information Pending with Standard Manager	pend_crs_stdm.sql

Workflow Notification Mailer Status Check

Check workflow mailer service current status

select running_processes from fnd_concurrent_queues
where concurrent_queue_name = 'WFMLRSVC';
Please Note : Number of running processes should be greater than 0

Find current mailer status

To check the status of notification mailer, we need to do the following.

select component_status
from fnd_svc_components
where component_id =
(select component_id
from fnd_svc_components
where component_name = 'Workflow Notification Mailer');
Possible values are :
RUNNING
STARTING
STOPPED_ERROR
DEACTIVATED_USER
DEACTIVATED_SYSTEM

Stop notification mailer

To stop notification mailer , we need to execute the following.

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;
/
-------------------------+
Start notification mailer|
-------------------------+
To start notification mailer, we need to execute the following.
sqlplus apps/
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;
/
-------------------------------+
All WF Mailer Service Stausses`|
-------------------------------+
select component_name,COMPONENT_STATUS,COMPONENT_TYPE,STARTUP_MODE,INBOUND_AGENT_NAME,OUTBOUND_AGENT_NAME from apps.fnd_svc_components;

SET PAGESIZE 400
SET LINESIZE 120
SET PAGESIZE 50
COLUMN COMPONENT_NAME FORMAT A45
COLUMN STARTUP_MODE FORMAT A15
COLUMN COMPONENT_STATUS FORMAT A15
SELECT FSC.COMPONENT_NAME,FSC.STARTUP_MODE,FSC.COMPONENT_STATUS
FROM APPS.FND_CONCURRENT_QUEUES_VL FCQ, apps.FND_SVC_COMPONENTS FSC
WHERE FSC.CONCURRENT_QUEUE_ID = FCQ.CONCURRENT_QUEUE_ID(+)
ORDER BY COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;
=====================

1) Identify the concurrent tiers node where mailer runs 
by running script below:
select target_node from fnd_concurrent_queues where concurrent_queue_name like 'WFMLRSVC%'; 

It will return for example:
TARGET_NODE
------------------------------
ebiz1                     

In this example ebiz1 is the node where java mailer runs.

2) Gather other parameters values necessary for the SMTP telnet test:
To perform the SMTP telnet test, in addition to mailer node, you will also need to know on which node is the SMTP server (this is mailer “outbound server” parameter), and what is the reply to address that is set up for the java mailer (this is mailer “reply to” parameter).

To get these values run the following:
SELECT b.component_name,
c.parameter_name,
a.parameter_value
FROM fnd_svc_comp_param_vals a,
fnd_svc_components b,
fnd_svc_comp_params_b c
WHERE b.component_id = a.component_id
AND b.component_type = c.component_type
AND c.parameter_id = a.parameter_id
AND c.encrypted_flag = 'N'
AND b.component_name like '%Mailer%'
AND c.parameter_name in ('OUTBOUND_SERVER', 'REPLYTO')
ORDER BY c.parameter_name;

DBMS Jobs

How to enable/disable a scheduled job? 

 Using the package DBMS_SCHEDULER one can enable/disable jobs.

 To disable job: This disables the job from running

SQL> exec dbms_scheduler.disable('GATHER_STATS_JOB');

PL/SQL procedure successfully completed.

check job status

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name ='GATHER_STATS_JOB';

JOB_NAME   ENABL
--------------- ------
GATHER_STATS_JOB FALSE

To enable job:

SQL> exec dbms_scheduler.enable(‘GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

check job status

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;

JOB_NAME   ENABL
--------------- -----
GATHER_STATS_JOB TRUE

You can run the job manually via DBMS_SCHEDULER

BEGIN
DBMS_SCHEDULER.RUN_JOB(
job_name => 'GATHER_STATS_JOB'
);
END;
/

Job Run Detailed History

select owner,job_name,status,error#,run_duration,actual_start_date from dba_scheduler_job_run_details where job_name like '%GATHER%' order by actual_start_date;

RMAN Backup Information

------------------------------------------
-This script will report on all backups 
– full, incremental and archivelog backups
-Inputs : 
-File Name: rman_bkp.sql
-Usage: @rman_bkp
-----------------------------------------

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

--------------------------------------------------------
-This script will report all 
-on full and incremental backups, not archivelog backups
--------------------------------------------------------
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;