1) Login to application server.
[oracle@Ranesh logs]$ grep -i "wls_admin" $CONTEXT_FILE
<s_wls_admin_console_access_nodes oa_var="s_wls_admin_console_access_nodes">0.0.0.0/0</s_wls_admin_console_access_nodes>
<wls_admin_host oa_var="s_wls_admin_host">Ranesh</wls_admin_host>
<wls_admin_sslEnabled oa_var="s_wls_admin_sslEnabled">false</wls_admin_sslEnabled>
<wls_admin_domain oa_var="s_wls_admin_domain">ranesh.oraclevcn.com</wls_admin_domain>
<wls_admin_user oa_var="s_wls_admin_user">weblogic</wls_admin_user>
<wls_adminport oa_var="s_wls_adminport" oa_type="PORT" base="7001" step="1" range="-1" label="WLS Admin Server Port">7001</wls_adminport>
<wls_admin_sslport oa_var="s_wls_admin_sslport" oa_type="PORT" base="17001" step="1" range="-1" label="WLS Admin Server SSL Port">17001</wls_admin_sslport>
We can also find out the url using command
[oracle@Ranesh logs]$ echo "http://"$(cat $CONTEXT_FILE | grep s_webhost | cut -d '>' -f2 | cut -d '<' -f1)"."$(cat $CONTEXT_FILE | grep s_wls_admin_domain | cut -d '>' -f2 | cut -d '<' -f1)":"$(cat $CONTEXT_FILE | grep s_wls_adminport | cut -d '>' -f2 | cut -d '<' -f1)"/console"
http://Ranesh.ranesh.oraclevcn.com:7001/console
cURL is a command-line tool and application library used to transfer data to or from a server.
curl -T /u01/app/oracle/ranesh.zip -o output.log -u "ranesh@oracle.com" "https://transport.oracle.com/upload/issue/SR NUMBER/"
curl -T AdminServer.log -o xplorer -u "ranesh@oracle.com" "https://transport.oracle.com/upload/issue/3-26712853041/"
SELECT DBMS_METADATA.GET_DDL('DB_LINK',db.db_link,db.owner) from dba_db_links db;
Steps to start/stop
Workflow Notification Mailer
·
Check workflow mailer service current status
SQL> select running_processes
from fnd_concurrent_queues
where concurrent_queue_name = 'WFMLRSVC';
RUNNING_PROCESSES
-----------------
1
Number of running processes should be
greater than 0
·
Find current mailer status
SQL> select component_status
from fnd_svc_components
where component_id =
(select component_id
from fnd_svc_components
where component_name = 'Workflow Notification
Mailer');
COMPONENT_STATUS
------------------------------
DEACTIVATED_USER
Possible values are :
RUNNING STARTING,
STOPPED_ERROR,
DEACTIVATED_USER
DEACTIVATED_SYSTEM.
·
Stop Workflow mailer
declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
fnd_svc_component.stop_component(m_mailerid, p_retcode,
p_errbuf);
commit;
end;
/
·
Start the Notification Mailer
declarep_retcode number;p_errbuf varchar2(100);m_mailerid fnd_svc_components.component_id%TYPE;beginselect component_idinto m_maileridfrom fnd_svc_componentswhere component_name = 'Workflow Notification Mailer';fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);commit;end;/
TKPROF Overview.
TKPROF is used for diagnosing
performance issues. It formats a trace file into readable format for
performance analysis.
Syntax
tkprof tracefile_name.trc tracefileoutput.txt
sys=no sort='(prsela,exeela,fchela)' explain=apps/apps
passwd.
sys – Enables or disables the inclusion of SQL statements
executed by the SYS user, including recursive SQL statements.
Default=enable.
explain – Executes an Explain Plan for each statement in the trace
file and displays the output. Explain Plan provides the predicted optimizer
execution path without actually executing the statement. tkprof shows you
the actual execution path and statistics after the statement is executed.
sort – Sorts the SQL statements in the
trace file by the criteria required.It provides SQL statements that consume the
most resources at the top of the file, rather than searching the entire file
contents for the poor performers.
prsela – The elapsed time spent
parsing the SQL.
exeela – The elapsed time spent
executing the SQL.
fchela – The
elapsed time spent fetching rows.
To check
the tablespace in GB
select
b.tablespace_name, tbs_size SizeGB, a.free_space FreeGB from
(select
tablespace_name, round(sum(bytes)/1024/1024/1024,1) as free_space
from
dba_free_space group by tablespace_name UNION
select
tablespace_name, round((free_space)/1024/1024/1024,1) as free_space from
dba_temp_free_space) a,
(select
tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size
from
dba_data_files group by tablespace_name UNION
select
tablespace_name, sum(bytes)/1024/1024/1024 tbs_size
from
dba_temp_files group by tablespace_name ) b where
a.tablespace_name(+)=b.tablespace_name;
Output
TABLESPACE_NAME
SIZEGB
FREEGB
------------------------------ ---------- ----------
APEX
6 1.8
APPS_TS_ARCHIVE
89
24.8
APPS_TS_INTERFACE
101.742188 38.3
APPS_TS_MEDIA
1025 54
To check
the tablespace in MB
SELECT
df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace)
"Free MB",
df.totalspace "Total MB",
ROUND(100 * ( (df.totalspace -
tu.totalusedspace)/ df.totalspace)) "% Free"
FROM
(SELECT tablespace_name,
ROUND(SUM(bytes) / 1048576) TotalSpace
FROM dba_data_files
GROUP BY tablespace_name
) df,
(SELECT ROUND(SUM(bytes)/(1024*1024))
totalusedspace,
2
3 4 tablespace_name
FROM dba_segments
GROUP BY tablespace_name
) tu
WHERE
df.tablespace_name = tu.tablespace_name;
Output
Tablespace Used MB Free MB
Total MB % Free
------------------------------
---------- ---------- ---------- ----------
APEX 4351 1793 6144 29
APPS_TS_ARCHIVE 65696 25440
91136 28
APPS_TS_INTERFACE 64938 39246
104184 38
APPS_TS_MEDIA 994267 55333
1049600 5
APPS_TS_NOLOGGING 18935 727661
746596 97
Checking datafile location
set lines 1000
set pages 1000
col file_name for
a60
col
tablespace_name for a25
col creation_time
for a20
select
ddf.file_id, ddf.file_name, ddf.tablespace_name, to_char(df.CREATION_TIME,'dd-MON-yyyy
hh24:mi:ss') creation_time,ddf.bytes/1024/1024/1024 "Size GB"
from
dba_data_files ddf, v$datafile df
where
ddf.file_id=df.file#
and tablespace_name='ranesh_DI_DATA'
order by
df.creation_time;
Output
FILE_ID FILE_NAME
TABLESPACE_NAME
CREATION_TIME Size GB
----------
------------------------------------------------------------
------------------------- -------------------- ----------
40 datafile/xxecs_dist_data01.dbf ranesh_DI_DATA 06-SEP-2007 19:03:23 30
65 /datafile/xxecs_dist_data02.dbf ranesh_DI_DATA 27-APR-2008 23:57:36 30
68 datafile/xxecs_dist_data03.dbf ranesh_DI_DATA 11-AUG-2008 13:31:06 30
Check the size of
particular tablespace.
set linesize 400;
set lines 200
col
"Name" format a20
col GB for 9999999
col file_name for
a100
select a.tbl
"Name",a.tsz "Total Size",b.fsz "Free Space",
round((1-(b.fsz/a.tsz))*100)
"Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
(select
tablespace_name tbl,sum(bytes)/1024/1024/1024 TSZ from dba_data_files
where tablespace_name = 'ranesh_DI_DATA' group by tablespace_name) a,
(select
tablespace_name tblsp,sum(bytes)/1024/1024/1024 FSZ from dba_free_space
where tablespace_name = 'ranesh_DI_DATA' group by tablespace_name) b
Where
a.tbl=b.tblsp;
Output
Name Total Size Free Space Pct Used
Pct Free
--------------------
---------- ---------- ---------- ----------
ranesh_DI_DATA 3608.07031 24.3586426 99 1
The status of the concurrent manager
SQL>select control_code,running_processes,MAX_PROCESSES from
fnd_concurrent_queues where concurrent_queue_name=‘Standard Manager’;
Deactivating the concurrent
manager:When you deactivate a manager all requests (concurrent programs)
currently running are allowed to complete before the manager(s) shut down.
SQL>update fnd_concurrent_queues set control_code='D' where
concurrent_queue_name=‘Standard Manager’;
Set max and target processes to 0
SQL>update fnd_concurrent_queues set
running_processes=0,MAX_PROCESSES=0 where concurrent_queue_name=‘Standard Manager’;
commit;
see the status of the Manager
SQL>select control_code,running_processes,MAX_PROCESSES from
fnd_concurrent_queues where concurrent_queue_name=‘Standard Manager’;
Activating the concurrent manager:
SQL>update fnd_concurrent_queues set control_code='R' where
concurrent_queue_name=‘Standard Manager’;
commit;
See the status of the Manager
SQL>select control_code,running_processes,MAX_PROCESSES from fnd_concurrent_queues where concurrent_queue_name=‘Standard Manager’;
SQL
to find Pending Concurrent Requests & Status of Conncurrent Request
Count of pending concurrent requests:
select COUNT (distinct
cwr.request_id) Peding_Requests FROM
apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq,
apps.fnd_user fu WHERE (cwr.phase_code = 'P' OR cwr.phase_code =
'R') AND cwr.hold_flag != 'Y' AND
cwr.requested_start_date <= SYSDATE AND
cwr.concurrent_queue_id = cq.concurrent_queue_id AND
cwr.queue_application_id = cq.application_id and cq.LANGUAGE='US'
AND cwr.requested_by =
fu.user_id and cq.user_concurrent_queue_name
in ( select unique
user_concurrent_queue_name from apps.fnd_concurrent_queues_tl);
Pending with CRM
select count(cwr.request_id)
"Pending with CRM" FROM apps.fnd_concurrent_worker_requests cwr,
apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu
WHERE (cwr.phase_code = 'P'
OR cwr.phase_code = 'R') AND cwr.hold_flag != 'Y' AND
cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id
= cq.concurrent_queue_id AND cwr.queue_application_id =
cq.application_id and cq.LANGUAGE='US'
AND cwr.requested_by =
fu.user_id and cq.user_concurrent_queue_name in ( 'Conflict Resolution
Manager');
Pending with Standard Manager
select count(cwr.request_id)
"pending with Standard Manager" FROM
apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq,
apps.fnd_user fu
WHERE (cwr.phase_code = 'P'
OR cwr.phase_code = 'R') AND cwr.hold_flag != 'Y' AND
cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id
= cq.concurrent_queue_id AND cwr.queue_application_id =
cq.application_id and cq.LANGUAGE='US'
AND cwr.requested_by =
fu.user_id and cq.user_concurrent_queue_name in ( 'Standard Manager');
Pending with
Internal Manager
select COUNT (distinct cwr.request_id) "Pending with Internal Manager" FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R') AND cwr.hold_flag != 'Y' AND cwr.requested_start_date <= SYSDATE AND cwr.concurrent_queue_id = cq.concurrent_queue_id AND cwr.queue_application_id = cq.application_id and cq.LANGUAGE='US' AND cwr.requested_by = fu.user_id and cq.user_concurrent_queue_name in ('Internal Manager');
Pending with Any other Manager
this sql Prompts for the Manager Name
select COUNT (distinct cwr.request_id) Peding_Requests FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R') AND cwr.hold_flag != 'Y' AND cwr.requested_start_date <= SYSDATE AND cwr.concurrent_queue_id = cq.concurrent_queue_id AND cwr.queue_application_id = cq.application_id and cq.LANGUAGE='US' AND cwr.requested_by = fu.user_id and cq.user_concurrent_queue_name in ('&Concurrent_Manager_Name');
User Concurrent Queue Status
select MAX_PROCESSES,RUNNING_PROCESSES from apps.fnd_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAMEin(select CONCURRENT_QUEUE_NAME from apps.fnd_CONCURRENT_QUEUES_TL where USER_CONCURRENT_QUEUE_NAME='&User_Concurrent_Queue_name');
Concurrent
queue name and its user concurrent queue name
col CONCURRENT_QUEUE_NAME for a30col USER_CONCURRENT_QUEUE_NAME for a60set line 200 select UNIQUE FCQ.CONCURRENT_QUEUE_NAME,FCQT.USER_CONCURRENT_QUEUE_NAME,fcq.max_processes,fcq.running_processesfrom apps.fnd_concurrent_queues fcq,apps.fnd_concurrent_queues_tl fcqt where fcq.concurrent_queue_id=fcqt.concurrent_queue_id and fcq.application_id=fcqt.application_id order by fcq.running_processes desc;
Councurrent–>Manager–>Administer Screen
from back-end
select cq.user_concurrent_queue_name,fcq.max_processes,fcq.running_processes,count(cwr.request_id)FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu,apps.fnd_concurrent_queues fcqWHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R') AND cwr.hold_flag != 'Y' AND cwr.requested_start_date <= SYSDATEAND cwr.concurrent_queue_id = cq.concurrent_queue_id AND cwr.queue_application_id = cq.application_id and cq.LANGUAGE='US'and fcq.concurrent_queue_id=cq.concurrent_queue_id and fcq.application_id=cq.application_idAND cwr.requested_by = fu.user_id and cq.user_concurrent_queue_namein (select unique user_concurrent_queue_name from apps.fnd_concurrent_queues_tl)group by cq.user_concurrent_queue_name,fcq.max_processes,fcq.running_processes;
