fnd_concurrent_queues table control_code column meaning

Concurrent Manger queues Status code meaning

SQL> select lookup_code,meaning from apps.fnd_lookups where lookup_type = 'CP_CONTROL_CODE' order by lookup_code;

LOOKUP_CODE                    MEANING
------------------------------ -------------------------------------------------
A                              Activating
B                              Activated
D                              Deactivating
E                              Deactivated
N                              Target node/queue unavailable
O                              Suspending concurrent manager
P                              Suspended
Q                              Resuming concurrent manager
R                              Restarting
T                              Terminating
U                              Updating environment information

LOOKUP_CODE                    MEANING
------------------------------ -------------------------------------------------
V                              Verifying
X                              Terminated

13 rows selected.

fnd_concurrent_requests status_code and phase_code meanings

fnd_concurrent_requests status_code and phase_code meanings

SQL> select lookup_code "lookup Code" ,meaning from apps.fnd_lookups where lookup_type='CP_STATUS_CODE';
lookup Code MEANING
 -------------------
 A          Waiting
 B          Resuming
 C          Normal
 D          Cancelled
 E          Error
 G          Warning
 H          On Hold
 I          Normal
 M          No Manager
 P          Scheduled
 Q          Standby
 R          Normal
 S          Suspended
 T          Terminating
 U          Disabled
 W          Paused
 X          Terminated
 Z          Waiting
18 rows selected.

SQL> select lookup_code "lookup Code" ,meaning from apps.fnd_lookups where lookup_type='CP_PHASE_CODE';
lookup Code MEANING
 ------------------
 C          Completed
 I          Inactive
 P          Pending
 R          Running

Scripts order by Category

      Database:

Alertlog Tail AWR Info Blocking Sessions Blocking Sessions1 DB Links DBA Directories DBA Directories Upd High Water Mark GSS Run Locked Objects Tablespace Free Database Info Database Consistency Resetlogs Time RMAN Sofar RMAN Sofar1 RMAN Status Sync Status on Prim Sync Status on DR User Undo Utl File Dir Wait Events DBA Files All DBA Tablespaces DML Locks DML Locks1 Long Operations Long Operations1 Resource Limit Role Privs DBMS Jobs Table Size Tablespace Usage Temp Extent Mapping Temp Free Temp Free1 Temp IO Temp Segments Temp Usage Temp Seg Usage Top CPU Tablespace Free TS Info Health of DB Temp Usage Invalids Jobs Total Jobs Running Max Extents PID Redo Per Hour Sessions Sessions1 Sessions User Sessions BG Sessions by CPU Sessions by Undo Sessions by Undo1 Sid Sid1 Undo UndoStatus User hit ratio User Privs User Temp Space

Concurrent Request (CR):

CR Details from Request ID CR Duration CRs Running Currently CRs Running Currently1

CR Pedning CR Pednig1 CR Pending on CRM CR Pending on CRM1 CR Pending on Internal Manager CR Pending on Stdmgr

CR Error in 24Hrs CRs Errorred Scheduled CRs

Oracle Applications Scripts:

cmclean

Oracle Applications Scripts

Description,Explanation, Execution and Sample Output	Download Script
Oracle Application Products Version	product_status.sql
Applications Relese/Version	release.sql
Patch applied or not?	patch.sql

Locks/Blocking Session Scripts

Description,Explanation, Execution and Sample Output	Download Script
Finding Blocking sessions	locks.sql
Objects currently Locked	locked_obj.sql

User Management Scripts

Description,Explanation, Execution and Sample Output	Download Script
Privilege associated with a User	user_privs.sql

Session Management Scripts

Description,Explanation, Execution and Sample Output	Download Script
Sql text running that is running by a Session with an SID	sql_text.sql
Session information from Process ID	pid.sql
Sessions currently connected to Database	sess_runnig.sql
Session information from Session ID (SID)	sid.sql

Database Info Scripts

Description,Explanation, Execution and Sample Output	Download Script
Database Information	db.sql
Check invalid objects	invalids.sql
Particular Table Size	tab_size.sql

Dataguard/Standby Info Scripts

Description,Explanation, Execution and Sample Output	Download Script
Sync status query to be used on Standby Database	sync_stdby.sql
Sync status query to be used on Primary Database	sync.sql

Tablespace/Datafiles Scripts

Description,Explanation, Execution and Sample Output	Download Script
Tablespaces Information	tsinfo.sql
Tablespace Free Space	ts_free.sql

RMAN Info Scripts

Description,Explanation, Execution and Sample Output	Download Script
RMAN Progress Sofar	rman_sofar.sql
RMAN backup information	rman_bkp.sql
RMAN Backup Status	rman_status.sql

Concurrent Request Scripts

Description,Explanation, Execution and Sample Output	Download Script
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

Actual Objects Size in DB

Description	Actual size occupied by objects
Download Script	db_obj_size.sql
Date	20-Sep-2013

In this example we can find the actual size of the objects i.e., the space occupied by all the physical objects not the space occupied by datafiles.

The objects include the below list

• LOBINDEX
• INDEX PARTITION
• TABLE SUBPARTITION
• ROLLBACK
• TABLE PARTITION
• NESTED TABLE
• LOB PARTITION
• INDEX SUBPARTITION
• CACHE
• LOBSEGMENT
• INDEX
• TABLE
• TYPE2 UNDO
• CLUSTER
• LOB SUBPARTITION

SELECT sum(bytes)/1024/1024 "Used in MB" FROM dba_segments
/

Size occupied by each type of object

select unique segment_type,sum(bytes)/1024/1024 from dba_segments group by segment_type
/

select unique segment_type,sum(bytes)/1024/1024 "Size in MB"from dba_segments group by segment_type /

Sample Execution and Output

SQL> SELECT sum(bytes)/1024/1024 "Size in MB" FROM dba_segments
/
Size in MB
----------
435925.375

SQL> select unique segment_type,sum(bytes)/1024/1024 "Size in MB"from dba_segments group by segment_type
/

SEGMENT_TYPE                 Size in MB
------------------ --------------------
LOBINDEX                      1513.6875
INDEX PARTITION              24582.5625
TABLE SUBPARTITION                  136
ROLLBACK                           .375
TABLE PARTITION              24811.8125
NESTED TABLE                      4.375
LOB PARTITION                   12.5625
INDEX SUBPARTITION                   83
CACHE                             .0625
LOBSEGMENT                   56456.3125
INDEX                        136582.688
TABLE                            186685
TYPE2 UNDO                    4482.5625
CLUSTER                         310.375
LOB SUBPARTITION                    269

15 rows selected.

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;

RMAN Backup sofar

----------------------------------
-RMAN Backup sofar
-Inputs : None
-File Name: rman_sofar.sql
-Usage: @rman_sofar
-----------------------------------

set line 200
select
      sid,
      to_char(start_time,'dd-Mon-yy hh24:mi:ss') Start_Time,
   to_char(sysdate,'dd-Mon-yy hh24:mi:ss') Time_Now,
      totalwork
      sofar,
     (sofar/totalwork) * 100 pct_done
    from
       v$session_longops
    where
       totalwork > sofar
    AND
       opname NOT LIKE '%aggregate%'
    AND
       opname like 'RMAN%'
    /
    

RMAN Backup Status

----------------------------------
-RMAN Status Check for Specified Days
-Inputs : RMAN backup status for no.of days
-File Name: rman_status.sql
-Usage: @rman_status
-----------------------------------

set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from v$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     v$BACKUP_SET_DETAILS d
                     join v$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from Gv$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time
/

Privileges Associated with a Role

----------------------------------
-Privileges Associated with a Role
-File Name: role_privs.sql
-Usage: @role_privs
-----------------------------------

select PRIVILEGE from role_sys_privs where ROLE='&role'
/