Daily Health Checkup Oracle R12
Alert log location in R12 - /oracle/FIN/db/tech_st/11.1.0/admin/FIN_ebs1/diag/rdbms/fin/FIN/trace
===========================================================================
Startup Time:
select name,status,to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time",created from v$instance,v$database;
Last Good Backup
RMAN>crosscheck backup; --> in backup what are information whether expired or not
>crosscheck backup of database; --> what are datafiles expried or not(is there in os level)
>crosscheck backup of archivelog all;
>crosscheck backup of controlfile;
> delete expired backup; > delete all expried backup including archivelog &controlfile
RMAN>list backup;
>list expried backup;
> list expired backup of controlfile;
> list expired backup of database archivelog all;
>list backuppiece <piece_number>;
----
>crosscheck archivelog all; <Os level archivelog generation files maintained> so os level delete unwanrted archivelog file and delete in RMAN LEVEL.
> delete expired archivelog all;
==============================================================
select value from v$parameter where name='background_dump_dest';
spartan a :/oracle/PROD/db/tech_st/11.1.0/admin/PROD_sptnerp/diag/rdbms/prod/PROD/trace
==========================================================================
checking concurrent manager
$ ps -ef | grep FNDLIBR | wc -l
============================================================================
Actual concurrent running on the month:
select count(*), trunc(ACTUAL_START_DATE) from apps.fnd_concurrent_requests
group by trunc(ACTUAL_START_DATE)order by trunc(ACTUAL_START_DATE);
SQL> select count(*), trunc(ACTUAL_START_DATE)
from apps.fnd_concurrent_requests
group by trunc(ACTUAL_START_DATE)
order by trunc(ACTUAL_START_DATE); 213398 rows, 77690 rows,77700
===================================================================
What are concurrent program currrenlty runing:
select * From fnd_concurrent_requests where phase_code='R' and Status_code='R';
concurrent history for daily base
SELECT TO_CHAR(TRUNC(ACTUAL_START_DATE),'DD-MON-YY DY') STARTDATE,
COUNT(*) COUNT, ROUND(SUM(ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE) * 24, 2) RUNNING_HOURS,
ROUND(AVG(ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE) * 24, 2) AVG_RUNNING_HOURS,
ROUND(SUM(ACTUAL_START_DATE - REQUESTED_START_DATE) * 24, 2) PENDING_HOURS,
ROUND(AVG(ACTUAL_START_DATE - REQUESTED_START_DATE) * 24, 2) AVG_PENDING_HOURS
FROM APPLSYS.FND_CONCURRENT_PROGRAMS P,APPLSYS.FND_CONCURRENT_REQUESTS R
WHERE R.PROGRAM_APPLICATION_ID = P.APPLICATION_ID
AND R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID
AND R.STATUS_CODE IN ('C','G')
AND TRUNC(ACTUAL_COMPLETION_DATE) > TRUNC(SYSDATE-6)
AND TO_CHAR(TRUNC(ACTUAL_START_DATE),'DD-MON-YY DY') IS NOT NULL
GROUP BY TRUNC(ACTUAL_START_DATE)
ORDER BY TRUNC(ACTUAL_START_DATE) ASC;
==========================================================================
Full Database Size:
SELECT ROUND (SUM (used.bytes) / 1024 / 1024 / 1024) || ' GB'
"Database Size",ROUND (SUM (used.bytes) / 1024 / 1024 / 1024)
- ROUND (free.p / 1024 / 1024 / 1024)|| ' GB' "Used space",
ROUND (free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
FROM (SELECT bytes FROM v$datafile UNION ALL
SELECT bytes FROM v$tempfile UNION ALL SELECT bytes FROM v$log) used,
(SELECT SUM (bytes) AS p FROM dba_free_space)free GROUP BY free.p;
===========================================================================
datafile size
select sum(bytes)/1024/1024/1024 from dba_data_files;
select file_name,sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='APPS_TS_MEDIA' group by file_name order by file_name;
==========================================================================================
- list tablespaces, size, free space, and percent free
SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB, fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+) ORDER BY fs.tablespace_name;
=========================================================================
Purging Concurrent requests and Workflow data
select ARGUMENT1,ARGUMENT2,ARGUMENT3,actual_start_date from apps.fnd_concurrent_requests where CONCURRENT_PROGRAM_ID='32263' order by actual_start_date;
============================================================================
Invalid objects:
select owner,object_name,object_type,created from dba_objects where status<>'VALID';
select OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED,LAST_DDL_TIME, STATUS from dba_objects where status='INVALID';
==========================================================================================
Gather schema statistics latestly completed:
select fcr.request_id, fcr.phase_code, fcr.status_code,fcr.argument_text,
to_char(fcr.requested_start_date,'YYYY/MM/DD HH24:MI:SS') REQUESTED_START_DATE,
to_char(fcr.actual_start_date,'YYYY/MM/DD HH24:MI:SS') ACTUAL_START_DATE,
to_char(fcr.actual_completion_date,'YYYY/MM/DD HH24:MI:SS') ACTUAL_COMPLETION_DATE
from fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp
where fcp.concurrent_program_name in ('FNDGSCST','FNDGTST')
and fcr.program_application_id = fcp.application_id
and fcr.concurrent_program_id = fcp.concurrent_program_id
and trunc(fcr.actual_completion_date) > to_date('2016/01/03','YYYY/MM/DD')
order by fcr.request_id desc;
gather schema statistics:
select count(*)
from apps.fnd_concurrent_programs_vl p , apps.fnd_concurrent_requests r
where r.concurrent_program_id = p.concurrent_program_id
and r.program_application_id = p.application_id
and p.user_concurrent_program_name in (
'OnDemand Gather Schema Statistics','Gather Schema Statistics',
'Gather Schema Statistics (IT_ANALYZE)')
and (r.phase_code = 'C' and r.status_code= 'C' and r.actual_start_date >= sysdate-7);
gather schema statistics all columns
select *
from apps.fnd_concurrent_programs_vl p , apps.fnd_concurrent_requests r
where r.concurrent_program_id = p.concurrent_program_id
and r.program_application_id = p.application_id
and p.user_concurrent_program_name in (
'OnDemand Gather Schema Statistics','Gather Schema Statistics',
'Gather Schema Statistics (IT_ANALYZE)')
and (r.phase_code = 'C' and r.status_code= 'C' and r.actual_start_date >= sysdate-7);
==========================================================================
Active Users
SELECT USER_NAME,
CREATION_DATE,
CREATED_BY,LAST_UPDATE_LOGIN,LAST_UPDATE_LOGIN
FROM APPS.fnd_user
WHERE user_name <> 'SYS'
AND user_name <> 'SYSTEM'
AND user_name NOT LIKE '%ORACLE%'
AND user_name <> 'GUEST'
AND (creation_date) > ('01-JUN-2013')
--AND TO_CHAR (creation_date) > ('30-JUN-2014')
ORDER BY CREATION_DATE DESC ;
(or)
SELECT fu.User_name,
fu.User_id,
fu.employee_id,
fu.Email_address,
fu.description,
TO_CHAR(fu.creation_date,'DD-MON-YYYY HH:MM:SS') Creation_Date,
fu.created_by,
Lower(b.user_name) User_Created_By,
fu.last_logon_date,
fu.end_date
FROM fnd_user fu ,
fnd_user b
WHERE (fu.end_date IS NULL
OR fu.end_date >= TRUNC(SYSDATE))
AND fu.created_by =b.user_id
AND fu.user_id NOT BETWEEN 1 AND 1090
ORDER BY fu.creation_date DESC;
==========================================================================
Trace enable concurrent:
select a.CONCURRENT_PROGRAM_ID, b.USER_CONCURRENT_PROGRAM_NAME "Program_Name",a.ENABLE_TRACE, a.CONCURRENT_PROGRAM_NAME "Short_Name", a.APPLICATION_ID from apps.fnd_concurrent_programs a, apps.fnd_concurrent_programs_tl b
where a.CONCURRENT_PROGRAM_ID=b.CONCURRENT_PROGRAM_ID and a.ENABLE_TRACE='Y';
========================================================================================
Workflow mailer:
select count(*) from apps.wf_notifications
where mail_status='MAIL' and status='OPEN'
and trunc(begin_date)=trunc(sysdate); select * from apps.wf_notifications where mail_status='MAIL';
Workflow mailer: working
SELECT component_name as Component, component_status as Status FROM apps.fnd_svc_components WHERE component_type = 'WF_MAILER';
Workflow manager send last mail:
select notification_id,Message_type,message_name,Status,mail_status,begin_date,End_date,Original_recipient,From_user,To_user,subject From wf_notifications where trunc(begin_date)=trunc(sysdate)-1;
==========================================================================================
long running concurrent:
SELECT a.request_id,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
, fu.user_name,a.ARGUMENT_TEXT--,a.logfile_name --,a.phase_code,a.status_code
,DECODE(a.phase_code,
'C','Completed','I','Incactive','P','Pending','R','Running') phase,
DECODE(a.status_code,
'D','Cancelled','U','Disabled','E','Error','M','No Manager',
'R','Normal','I','Normal','C','Normal','H','On Hold','W','Paused',
'B','Resuming','P','Scheduled','Q','Standby','S','Suspended',
'X','Terminated','T','Terminating','A','Waiting','Z','Waiting',
'G','Warning','N/A') status
,TO_CHAR(actual_start_date, 'DD-MON-YY HH24:MI') actual_start_date
,TO_CHAR(actual_completion_date, 'DD-MON-YY HH24:MI') actual_completion_date
--,actual_start_date,actual_completion_date
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
/*,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins */
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
--AND a.phase_code = 'C'--OR a.phase_code = 'R' OR a.phase_code = 'E'
----AND a.status_code = 'C'--OR a.status_code = 'R'
--and a.status_code = 'E'
AND a.actual_start_date >= sysdate-100 and a.actual_start_date <= sysdate
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
--and a.request_id='5035452';
--and (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 > 20
and ctl.user_concurrent_program_name='PFC Bundle Invoices for Training Services Customers Program'
--and ctl.user_concurrent_program_name like 'PFC%Active%Users'
--and ctl.user_concurrent_program_name like 'PFC%'
--and ctl.user_concurrent_program_name='PFC DH BudgetHolder Commitments By Tasks'
--and ctl.user_concurrent_program_name='PFC DH BudgetHolder Commitments By Tasks'
--ORDER BY duration_in_hours DESC;
--and ctl.user_concurrent_program_name='PFC Populate Timesheet Exception'
--and ctl.user_concurrent_program_name='PFC UAE EMployee Assignments Conversion'
--ORDER BY actual_completion_date DESC;
order by a.request_id desc;
What are concurrent program completed lastday:
==============================================
SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date,
DECODE (cr.status_code,
'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Error',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting'
) status,
decode(cr.phase_code, 'C', 'Completed', 'I', 'Inactive', 'P', 'Pending', 'R', 'Running') phase
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
/*AND cr.phase_code <> 'P'*/
AND TRUNC (cr.requested_start_date) = TRUNC (SYSDATE-1)
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id
AND cp.USER_CONCURRENT_PROGRAM_NAME not in 'Allocate checklists and tasks process'
AND cr.release_class_id IS NOT NULL
ORDER BY status, NAME;
===============================================================
Last Successful completed Gather Schema Stats Request
exec dbms_stats.gather_table_stats(ownname => 'ALLOC12',tabname=>'ALC_ITEM_
LOC_TEMP',estimate_percent => 100,cascade => TRUE,method_opt => 'FOR ALL COLUMNS
SIZE 1' );
PL/SQL procedure successfully completed.
Output:
Last_analyzed Table_name
9/22/2010 10:59:03 AM ALC_ITEM_LOC_TEMP
Clearing Run away database and application Process
ps -ef | grep frm
OUTPUT-----
27079 28901 99 Aug06 ?
Clearing Run away database and application Process
result:------- oracle 27079 28901 98 Aug06
Load Balancer
ps -ef | grep frm | wc -lOUTPUT === Apps1=177 Apps2=115
Tablespace Space in MB
SELECT df.tablespace_name TABLESPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB, fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+) ORDER BY fs.tablespace_name;
escorts alert.log
/ORADATA/PROD/db/tech_st/11.1.0/admin/PROD_db-1/diag/rdbms/prod_prim/PROD/trace
COL "Database Size" FORMAT A20
COL "Free Space" FORMAT A20
COL "Used Space" FORMAT A20
SELECT ROUND(SUM(USED.BYTES) / 1024 / 1024 / 1024 )
|| ' GB' "Database Size" ,
ROUND(SUM(USED.BYTES) / 1024 / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 / 1024)
|| ' GB' "Used Space" ,
ROUND(FREE.P / 1024 / 1024 / 1024)
|| ' GB' "Free Space"
FROM
(SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG
) USED ,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE
) FREE
GROUP BY FREE.P
/
output
Total=158 GB Used= 76 GB Free= 82 GB
RMAN CONFIFURATION FOR SPARTAN SERVER :- IP: 192.168.1.213
=======================================================
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/test/rmanbackup/control_ramnbk_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/test/rmanbackup/RMAN_%d_%u_%s_%p' MAXPIECESIZE 4 G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/PROD/db/tech_st/11.1.0/dbs/snapcf_PROD.f'; # default
THERE IS NO ACCESS FOR ESCORTS DB SERVER.
TO FIND GAP DIFFERENT:
======================
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
========================************************============================
Schedule concuttent program: Finding query
SELECT fcr.request_id
, fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog
, fu.user_name requestor
, fu.description requested_by
, fu.email_address
, frt.responsibility_name requested_by_resp
, trim(fl.meaning) status
, fcr.phase_code
, fcr.status_code
, fcr.argument_text "PARAMETERS"
, TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested
, TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start
, TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
, DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
, CASE
WHEN fcr.hold_flag = 'Y'
Then Substr(
fu.description
, 0
, 40
)
END last_update_by
, CASE
WHEN fcr.hold_flag = 'Y'
THEN fcr.last_update_date
END last_update_date
, fcr.increment_dates
, CASE WHEN fcrc.CLASS_INFO IS NULL THEN
'Yes: ' || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
ELSE
'n/a'
END run_once
, CASE WHEN fcrc.class_type = 'P' THEN
'Repeat every ' ||
substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
ELSE
'n/a'
END set_days_of_week
, CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 33),'1',1) > 0 THEN
'Days of week: ' ||
decode(substr(fcrc.class_info, 33, 1), '1', 'Sun, ') ||
decode(substr(fcrc.class_info, 34, 1), '1', 'Mon, ') ||
decode(substr(fcrc.class_info, 35, 1), '1', 'Tue, ') ||
decode(substr(fcrc.class_info, 36, 1), '1', 'Wed, ') ||
decode(substr(fcrc.class_info, 37, 1), '1', 'Thu, ') ||
decode(substr(fcrc.class_info, 38, 1), '1', 'Fri, ') ||
decode(substr(fcrc.class_info, 39, 1), '1', 'Sat ')
ELSE
'n/a'
end days_of_week
FROM fnd_concurrent_requests fcr
, fnd_user fu
, fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpt
, fnd_printer_styles_tl fpst
, fnd_conc_release_classes fcrc
, fnd_responsibility_tl frt
, fnd_lookups fl
WHERE fcp.application_id = fcpt.application_id
AND fcr.requested_by = fu.user_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.print_style = fpst.printer_style_name(+)
AND fcr.release_class_id = fcrc.release_class_id(+)
AND fcr.status_code = fl.lookup_code
AND fl.lookup_type = 'CP_STATUS_CODE'
AND fcr.phase_code = 'P'
AND 1=1
Order By Fu.Description, Fcr.Requested_Start_Date Asc;
Running Concurrent-Program:
====================================
SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,(a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 AS lag_time,d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.FND_CONCURRENT_PROGRAMS_TL c,apps.fnd_user d
WHERE a.concurrent_program_id=b.concurrent_program_id AND b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by=d.user_id AND status_code='R' order by Process_time desc;
Currently Running status Concurrent-Program:
===========================================
SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,
(a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b ,
apps.FND_CONCURRENT_PROGRAMS_TL c,
apps.fnd_user d
WHERE a.concurrent_program_id=b.concurrent_program_id AND
b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by=d.user_id AND
status_code='R' order by Process_time desc;
======================
concurrent program process runing:
==================================
select User_Concurrent_Queue_Name,'....... : '||Target_Node||' ...... : '||Running_Processes
from fnd_concurrent_queues_vl
where Running_Processes = Max_Processes
and Running_Processes > 0;
concurent program still pernding:
================================
select user_concurrent_program_name||'........ : '||request_id
from fnd_concurrent_requests r, fnd_concurrent_programs_vl p, fnd_lookups s, fnd_lookups ph
where r.concurrent_program_id = p.concurrent_program_id
and r.phase_code = ph.lookup_code
and ph.lookup_type = 'CP_PHASE_CODE'
and r.status_code = s.lookup_code
and s.lookup_type = 'CP_STATUS_CODE'
and ph.meaning ='Pending'
and rownum < 10 order by to_date(actual_start_date, 'dd-MON-yy hh24:mi');
Workflow related concurrent program status:
select fsc.COMPONENT_NAME,
fsc.STARTUP_MODE,
fsc.COMPONENT_STATUS,
fcq.MAX_PROCESSES TARGET,
fcq.RUNNING_PROCESSES ACTUAL
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;
Current login application:
============================
select distinct (user_id) "users" from icx_sessions where last_connect > sysdate - 1/24 and user_id != '-1';
select distinct (user_id) "users" from icx_sessions where last_Connect=sysdate;
5345
6
5212
1342
select * From fnd_user where user_id=5345;
select * From v$session where status='ACTIVE';
Contractor acive data information:
==================================
select user_name,created_by from fnd_user where user_name not like 'TKM%' and end_date is null and created_by not in (0,1,2);
select user_name || ' last login date ' || last_logon_date from fnd_user where user_name not like 'TKM%' and end_date is null and created_by not in (0,1,2);
Here is TKM_HELPDESK also inclduing by contractor in list:
select * from fnd_user where user_name LIKE 'TKM%HELP%';
1.LFS_SRIKANTHlast login date 03-JUL-15
2.LFS_DEVAPRASAD Plast login date 06-JUL-15
3.TF_SUSHMAlast login date 30-JUN-15
4.CS_VIJAYANlast login date 05-JUL-15
5.LFS_NAGESHlast login date 03-JUL-15
6.CS_KUMARESANlast login date 06-JUL-15
7.LFS_RAGHUlast login date 06-JUL-15
8.CS_MAREESWARANlast login date 06-JUL-15
9.TTNI_SHILPAlast login date 06-JUL-15
10.TKM_HELPDESKlast login date
1.LFS_SRIKANTH last login date 30-JUL-15
2.LFS_DEVAPRASAD P last login date 31-JUL-15
3.TF_SUSHMA last login date 21-JUL-15
4.CS_VIJAYAN last login date 31-JUL-15
5.LFS_NAGESH last login date 31-JUL-15
6.CS_KUMARESAN last login date 31-JUL-15
7.LFS_RAGHU last login date 31-JUL-15
8.TTNI_SHILPA last login date 27-JUL-15
1.SR120324
2.SR120619
3.SR120667
4.SR120741
5.SR120931
6.SR121047
7.SR122584
=========================================================
User resposbility checking:
> Select b.user_name,b.creation_date,b.end_date,b.password_lifespan_days, c.responsibility_name, a.START_DATE, a.END_DATE,d.full_name
from fnd_user_resp_groups_direct a, fnd_user b, fnd_responsibility_tl c, per_all_people_f d
where a.user_id = b.user_id
and a.responsibility_id = c.responsibility_id
and b.user_name in('TKMA8401');
>SELECT fu.user_name, fu.description, fu.start_date, fu.end_date,
fu.creation_date, fu.employee_id,
(SELECT full_name
FROM apps.per_all_people_f
WHERE person_id = fu.employee_id
AND ROWNUM=1) NAME,
(SELECT frt.responsibility_name
FROM apps.fnd_user_resp_groups_direct fur,
apps.fnd_responsibility_tl frt
WHERE fur.user_id = fu.user_id
AND frt.responsibility_id = fur.responsibility_id) resp_name
FROM apps.fnd_user fu
WHERE fu.user_name IN
('TKM08628', 'TKM08627', 'TKM08626', 'TKM08625', 'TKM08624',
'TKM08623', 'TKM08622', 'TKM08621', 'TKM08620', 'TKM08619',
'TKM08618', 'TKM08617', 'TKM08616', 'TKM08603', 'TKM08604',
'TKM08615');
==============================================================
Licesnse Porudct:
select a.application_short_name,a.APPLICATION_NAME,decode(fpi.status,'I','Installed','S','Shared','N','Inactive',fpi.status) status
from apps.fnd_application_vl a, apps.fnd_product_installations fpi where
fpi.application_id = a.application_id and
fpi.status in ('I','S')
order by 3,1;
===============================
how many user deactivated in April:
>select user_name,last_update_date,description from fnd_user where end_Date between ('01-JUL-15') and ('30-JUL-15');
Active users :
SELECT COUNT(USER_ID) FROM APPS.FND_USER
WHERE END_DATE>SYSDATE
OR END_DATE IS NULL;
2592
SELECT COUNT(USER_ID) FROM APPS.FND_USER
where user_name LIKE 'TKM%' AND END_DATE IS null;
2303
SELECT COUNT(USER_ID) FROM APPS.FND_USER
where user_name not LIKE 'TKM%' AND END_DATE IS null;
36
=========================================
Current running concurrent Program:
SELECT a.request_id request_id,a.actual_start_date,a.actual_completion_date
, SUBSTR(a.user_concurrent_program_name,1,50) name
, TO_CHAR(a.actual_start_date,'Hh24:MI') st_time
, TO_CHAR(a.actual_completion_date,'Hh24:MI') end_time
, requestor
, DECODE(a.phase_code, 'R'
,'Running', 'P'
,'Inactive', 'C'
,'Completed', a.phase_code) phase_code
, DECODE(a.status_code, 'E'
,'Error', 'C'
,'Normal', 'X'
,'Terminated', 'Q'
,'On Hold', 'D'
,'Cancelled', 'G'
,'Warning', 'R'
,'Normal', 'W'
,'Paused', a.status_code) status_code
FROM apps.fnd_conc_req_summary_v a
WHERE TRUNC(actual_completion_date) >= TRUNC(SYSDATE)
AND a.status_code IN ('C','R')
And user_concurrent_program_name='Employee Annual Tax Returns (India)'
ORDER BY actual_start_date
/
Concurrent Program Completion less than sysdate:
================================================
SELECT a.request_id request_id,a.actual_start_date,a.actual_completion_date
, SUBSTR(a.user_concurrent_program_name,1,50) name
, TO_CHAR(a.actual_start_date,'Hh24:MI') st_time
, TO_CHAR(a.actual_completion_date,'Hh24:MI') end_time
, requestor
, DECODE(a.phase_code, 'R'
,'Running', 'P'
,'Inactive', 'C'
,'Completed', a.phase_code) phase_code
, DECODE(a.status_code, 'E'
,'Error', 'C'
,'Normal', 'X'
,'Terminated', 'Q'
,'On Hold', 'D'
,'Cancelled', 'G'
,'Warning', 'R'
,'Normal', 'W'
,'Paused', a.status_code) status_code
FROM apps.fnd_conc_req_summary_v a
WHERE TRUNC(actual_completion_date) <= TRUNC(SYSDATE)
AND a.status_code IN ('C','R')
And user_concurrent_program_name='Payroll Run (India)'
ORDER BY actual_start_date
/
Concurrent Program completed with Error:
=====================================
SELECT a.request_id request_id
, SUBSTR(a.user_concurrent_program_name,1,50) name
, TO_CHAR(a.actual_start_date,'Hh34:MI') st_time
, TO_CHAR(a.actual_completion_date,'Hh34:MI') end_time
, requestor
, DECODE(a.phase_code, 'R'
,'Running', 'P'
,'Inactive', 'C'
,'Completed', a.phase_code) phase_code
, DECODE(a.status_code, 'E'
,'Error', 'C'
,'Normal', 'X'
,'Terminated', 'Q'
,'On Hold', 'D'
,'Cancelled', 'G'
,'Warning', 'R'
,'Normal', 'W'
,'Paused', a.status_code) status_code
FROM apps.fnd_conc_req_summary_v a
WHERE TRUNC(actual_completion_date) >= TRUNC(SYSDATE)
AND a.status_code IN ('E','X','D')
AND user_concurrent_program_name='Employee Annual Tax Returns (India)'
ORDER BY actual_start_date
/
WEP2DBA
(0r)
SELECT a.request_id request_id,a.actual_start_date,a.actual_completion_date
, SUBSTR(a.user_concurrent_program_name,1,50) name
, TO_CHAR(a.actual_start_date,'Hh24:MI') st_time
, TO_CHAR(a.actual_completion_date,'Hh24:MI') end_time
, requestor
, DECODE(a.phase_code, 'R'
,'Running', 'P'
,'Inactive', 'C'
,'Completed', a.phase_code) phase_code
, DECODE(a.status_code, 'E'
,'Error', 'C'
,'Normal', 'X'
,'Terminated', 'Q'
,'On Hold', 'D'
,'Cancelled', 'G'
,'Warning', 'R'
,'Normal', 'W'
,'Paused', a.status_code) status_code
,USER_CONCURRENT_PROGRAM_NAME
,PRIORITY
,DESCRIPTION
FROM apps.fnd_conc_req_summary_v a
WHERE TRUNC(actual_completion_date) = TRUNC(SYSDATE)-1
AND a.status_code IN ('E','X','D')
ORDER BY actual_start_date;
==============================================================
Monthly Base user Creation:
> select user_name,description,creation_Date,end_Date From fnd_user where creation_date between '01-APR-15' AND '30-APR-15';
No comments:
Post a Comment