DB SIZE
-----------
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual
Important Queries for Apps DBA
***************************
--Blocking Sessions--
Connect as sysdba and execute below Query to find out blocking sessions
set lines 130
set pages 200
column module format a20
column program format a20
column username format a15
select s.sid,s.serial#,s.status,p.spid,s.module,s.action,s.program
from v$session s,v$process p
where s.sid in (select session_id from dba_locks where blocking_others='Blocking')
and s.paddr=p.addr
--Note: Kill only INACTIVE blocking sessions. For Inactive blocking sessions,
--Event will be as shown below:
SQL> select EVENT from gv$session_wait where sid=&sid;
Enter value for sid: 19
old 1: select EVENT from gv$session_wait where sid=&sid
new 1: select EVENT from gv$session_wait where sid=19
EVENT
----------------------------------------------------------------
SQL*Net message from client
Locks in Specific Modules
Locks in AP Module
column object_name format a40
SELECT a.object_id, a.session_id, b.object_name
FROM v$locked_object a, dba_objects b
WHERE a.object_id = b.object_id
AND b.owner in ('AP')
--Another Query with more details
select s.sid sid_no,s.serial#,p.spid,s.action action,a.user_name user_name,
to_char(b.start_time,'DD-MON-YYYY HH24:MI') stime
from fnd_user a, fnd_logins b, v$session s, v$process p
where a.user_id = b.user_id
and p.addr = s.paddr
and s.process = b.spid
and b.end_time is null
and b.start_time > trunc(sysdate) - 1
and a.user_name = '&login_id'
and s.module like 'AP%'
--Runaway Forms Sessions
set lines 130
set pages 200
column userinfo heading "ORACLE/OS User" format a25
column terminal heading "Term" format a6
column process heading "Parent|Process ID" format a10
column "sid (audsid)" format a20
column spid heading "Shadow|Process ID" format a10
column event heading "Waiting" format a30
select s.username||' '||s.osuser||' ('||decode(s.terminal,'unknown','?','UNKNOWN','?',s.terminal) ||')' userinfo,
s.sid||','|| s.serial# ||' ('|| s.audsid ||')' "sid (audsid)", p.spid, w.event , w.p1
from v$session s, v$process p ,v$session_wait w
where p.addr = s.paddr and s.sid = w.sid and s.process = '&form_process_id' ;
--Sample Output:
Enter value for form_process_id: 19721
old 5: and s.process = '&form_process_id'
new 5: and s.process = '19721'
Shadow
ORACLE/OS User sid (audsid) Process ID Waiting P1
------------------------- -------------------- ---------- ------------------------------ ----------
APPS applmgr () 22,21485 (615657) 27872 SQL*Net message from client 1952673792
--Concurrent jobs Details
--List of Running Requests
set lines 130
set pages 200
col os form A7 head AppProc
col spid form a6 head SPID
col program form A50 trunc
set pages 38
col time form 99999.99 head Elapsed
col "Req Id" form 99999999
col "Parent" form a8
col "Prg Id" form 9999999
col serial# form 999999 head Serial#
col qname head "Manager" format a10 trunc
col sid format 9999 head SID
col user_name form A12 head User trunc
set recsep off
select
q.concurrent_queue_name qname
,f.user_name
,a.request_id "Req Id"
,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
,a.concurrent_program_id "Prg Id"
,a.phase_code,a.status_code
-- ,b.os_process_id "OS"
-- ,vs.sid
-- ,vs.serial# "Serial#"
-- ,vp.spid
,a.oracle_process_id "spid"
,(nvl(a.actual_completion_date,sysdate)-a.actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||
c2.user_concurrent_program_name||' '||a.description "Program"
from APPLSYS.fnd_Concurrent_requests a
,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_user f
-- ,v$session vs
-- ,v$process vp
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 c2.concurrent_program_id = c.concurrent_program_id
and c2.application_id = c.application_id
and a.phase_code in ('I','P','R','T')
and a.requested_by = f.user_id
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and c2.language = 'US'
-- and vs.process (+) = b.os_process_id
-- and vs.paddr = vp.addr (+)
order by 9 desc;
--List of pending Jobs waiting for managers:
set lines 130
col USER_CONCURRENT_QUEUE_NAME format a39
SELECT b.USER_CONCURRENT_QUEUE_NAME , count(*)
FROM apps.FND_CONCURRENT_WORKER_REQUESTS a,apps.FND_CONCURRENT_QUEUES_VL b
WHERE (Phase_Code = 'P' ) and a.hold_flag != 'Y'
and a.Requested_Start_Date <= SYSDATE
AND ('' IS NULL OR ('' = 'B' AND PHASE_CODE = 'R' AND
STATUS_CODE IN ('I'))) and a.CONCURRENT_QUEUE_ID!=1003
and a.CONCURRENT_QUEUE_ID=b.CONCURRENT_QUEUE_ID
group by b.USER_CONCURRENT_QUEUE_NAME ,status_code;
col program_description format a60
col user_concurrent_queue_name format a40
SELECT count(b.request_id) count, b.program_description, a.user_concurrent_queue_name
FROM apps.FND_CONCURRENT_QUEUES_VL a, apps.FND_CONCURRENT_WORKER_REQUESTS b
WHERE a.enabled_flag='Y'
AND a.concurrent_queue_id = b.concurrent_queue_id
AND (b.Phase_Code = 'P' OR b.Phase_Code = 'R') AND b.hold_flag != 'Y'
AND b.Requested_Start_Date <= SYSDATE
AND 1=1
-- and a.user_concurrent_queue_name like 'Inventory%'
group by a.user_concurrent_queue_name, b.program_description order by 3,1 asc
--Long Running Requests (over a specified amount of time hard coded as 4 hours)
set lines 130
column start_time format a15
column USER_CONCURRENT_PROGRAM_NAME format a40
select b.REQUEST_ID, a.USER_CONCURRENT_PROGRAM_NAME, b.phase_code,
(sysdate - b.actual_start_date) * 24 "running",
to_char(b.request_date, 'mm/dd/yyyy hh24:mi') "request_date",
to_char(b.actual_start_date, 'mm/dd/yyyy hh24:mi') "start_time"
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.STATUS_CODE = 'R'
and b.PHASE_CODE = 'R'
and ((sysdate - b.actual_start_date) * 24) > 4
and a.CONCURRENT_PROGRAM_ID NOT IN(36887,43393,38121,42789,31556);
--Total scheduled requests
set lines 130
select 'Scheduled requests:' schedt, count(*) schedcnt
from fnd_concurrent_requests
WHERE (requested_start_date > sysdate OR
status_code = 'P')
AND phase_code = 'P';
SELECT request_id id,
nvl(meaning, 'UNKNOWN') status,
user_concurrent_program_name pname,
to_char(request_date, 'DD-MON-RR HH24:MI:SS') submitd,
to_char(requested_start_date, 'DD-MON-RR HH24:MI:SS') requestd
FROM fnd_concurrent_requests fcr,
fnd_lookups fl,
fnd_concurrent_programs_vl fcpv
WHERE phase_code = 'P'
AND (fcr.requested_start_date >= sysdate OR
status_code = 'P')
AND LOOKUP_TYPE = 'CP_STATUS_CODE'
AND lookup_code = status_code
AND fcr.concurrent_program_id = fcpv.concurrent_program_id
AND fcr.program_application_id = fcpv.application_id
ORDER BY pname, request_date;
--Pending requests Kept (on hold)
set lines 130
select 'Requests on hold:' schedt, count(*) schedcnt
from fnd_concurrent_requests
WHERE hold_flag = 'Y'
AND phase_code = 'P';
SELECT request_id id,
nvl(meaning, 'UNKNOWN') status,
user_concurrent_program_name pname,
to_char(request_date, 'DD-MON-RR HH24:MI:SS') submitd
FROM fnd_concurrent_requests fcr,
fnd_lookups fl,
fnd_concurrent_programs_vl fcpv
WHERE phase_code = 'P'
AND hold_flag = 'Y'
AND fcr.requested_start_date <= sysdate
AND status_code != 'P'
AND LOOKUP_TYPE = 'CP_STATUS_CODE'
AND lookup_code = status_code
AND fcr.concurrent_program_id = fcpv.concurrent_program_id
AND fcr.program_application_id = fcpv.application_id
ORDER BY request_date, request_id;
--List of pending Requests (used for Pending jobs threshold Monitor)
set lines 130
set pages 200
column REQUEST heading 'Request' format 9999999999
column PHASE heading 'Phase' format A8
column STATUS heading 'Status' format A8
column PROGRAM heading 'Program Name' format A40
column SHORT heading 'Short Name' format A15
column REQUESTOR heading 'Requestor' format A15
column START_TIME heading 'Start Time' format A15
column RUN_TIME justify left heading 'Time(m)' format 999999.9
column description format a75
select b.REQUEST_ID "Request", a.description,
to_char(b.request_date, 'mm/dd/yyyy hh:mi:ss') "request_date",
to_char(b.requested_start_date, 'mm/dd/yyyy hh:mi:ss') "request_start"
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.PHASE_CODE = 'P'
and b.HOLD_FLAG !='Y'
and b.requested_start_date <= sysdate
--Steps to terminate a Concurrent request from backend
set lines 130
set pages 200
A)
select oracle_process_id ,
decode(status_code,'R','Running','D','Canceled','E','Error','X','Terminated','G', 'Warning','T','Terminating')"Status_code",
phase_code,to_char(actual_start_date,'DD-MON-YYYY=>hh24:mi:ss') "Login Time"
from apps.fnd_concurrent_requests where request_id='&Enter_conn_req_id'
B)
select s.sid,s.serial#,module,s.status from v$session s,v$process p
where s.paddr=p.addr
and p.spid=&oracle_process_id
C)
update fnd_concurrent_requests
set phase_code='C',
status_code='X'
where request_id=&Enter_REQUESTID;
commit;
**Make sure that database session for the request id is killed using the output from step A and B.**
--Steps to Cancel pending jobs
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P' AND status_code in ('Q','I');
Commit;
--Steps to Terminate pending jobs
update fnd_concurrent_requests
set phase_code='C',status_code='X'
WHERE phase_code = 'P' AND status_code in ('Q','I');
Commit;
--Query to find out list of NLS installed in Applications
set lines 130
select LANGUAGE_CODE from fnd_languages where INSTALLED_FLAG='I';
select NLS_LANGUAGE,LANGUAGE_CODE ,INSTALLED_FLAG from fnd_languages
where INSTALLED_FLAG in ('I','B');
--To get the list of installed products and patch level
set lines 130
set pages 400
col application_id format 99990 heading "ID"
col application_name format a40 heading "Name"
col application_prefix format a6 heading "Prefix"
col application_short_name format a10 heading "Short name"
col apps format a8 heading "Product"
col install_group_num format 90 heading "Inst Grp"
col installed_flag format a9 heading "Type"
col language_code format a4 heading "Code"
col module_short_name format a8 heading "Module"
col module_version format a8 heading "Version"
col product_group_id format 990 heading "ID"
col product_group_name format a28 heading "Product Group Name"
col product_group_type format a10 heading "Type"
col product_version format a8 heading "Version"
col argument1 format a20 heading "Arguments"
col release_name format a12 heading "Release"
col updated format a11 heading "Updated"
col patchset format a20 heading "Patchset Level"
col status format a14 heading "Appl Status"
prompt --> Product Installation Status, Version Info and Patch Level
select decode(nvl(a.APPLICATION_short_name,'Not Found'), 'SQLAP','AP','SQLGL','GL','OFA','FA',
'Not Found','id '||to_char(fpi.application_id), a.APPLICATION_short_name) apps,
decode(fpi.status,'I','Installed','S','Shared','N','Inactive',fpi.status) status,
fpi.product_version,nvl(fpi.patch_level,'-- Not Available --') Patchset,
to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date"
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
and fpi.oracle_id = o.oracle_id(+)
order by 1,2;
--Workflow Mailer configuration without login into OAM
set lines 130
set pages 200
col value format a30
select p.parameter_id,p.parameter_name,v.parameter_value value
from apps.fnd_svc_comp_param_vals_v v,
apps.fnd_svc_comp_params_b p,
apps.fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in
('OUTBOUND_SERVER', 'INBOUND_SERVER',
'ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')
order by p.parameter_name;
--Script to backup database links
connect to database as sysdba
set lines 130
set pages 200
select 'DB link for '||b.username||chr(10)||' create database link '||c.name||' connect to '||c.userid|| ' identified by '||c.password||' using '''||c.host||''';'
from dba_users b ,link$ c
where c.owner#=b.user_id
union
select 'DB link for Public' ||chr(10)|| ' create public database link '||c.name||' connect to
'||c.userid|| ' identified by '||c.password||' using '''||c.host||''';'
from link$ c
where owner#=1;
---------------------------------
**************************************************************
Removing Fatal NI connect error 12170 from Database Alert Log
In our one of the 11gR2 Production Database, there were so many entries w.r.t Fatal NI connect error 12170 + TNS-12535: TNS:operation timed out + TNS-00505: Operation timed out (with different ports), found in both RAC Instances Alert Log file.
Problem:
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 20-AUG-2014 14:17:36
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505
TNS-00505: Operation timed out
nt secondary err code: 110
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=44326))
Reason:
The same error message was repeating during whole day almost for every application server.
I’ve finally found what was causing the problem. Our database is behind a firewall. Firewall has an “idle session timeout” value. If a connection remains idle for more than the “idle session timeout” value it drops the connections.
Application developers usually configure their connection pools to remain alive for a long time, which is more than the “idle session timeout” value of the firewall. Not all the connections in the pool are used. Some stays idle. After sometime the firewall drops them and I get those operation timed out problems.
Solution / Workaround:
1) Add the following line to the sqlnet.ora file on the server.
SQLNET.EXPIRE_TIME=10
In this configuration database will probe the application servers every 10 minutes to see if they are up. In fact this is a mechanism to determine stale connections by database. Because it sends packets to application servers every ten minutes, the connections are detected as active by firewalls and they are not broken.
**In an installation that includes GRID, this parameter should be set in the RDBMS_HOME/network/admin/sqlnet.ora file. This would be the default location for sqlnet.ora file parameters referenced by the instance.
2) One way to minimize the impact is by using the parameter SQLNET.INBOUND_CONNECT_TIMEOUT (default to 60 seconds on 10gR2 and 11g) but, sometimes, this value is not adequate.
Oracle also mention the occurrence of this error if you use DB Console or Enterprise Manager to monitor your databases and the em agent will try to connect to the target database repeatedly and, statistically, some will fail (frequency will depend on how busy your system is).
Most of the time (certainly for DB Console and Enterprise Manager Agent) the application will try to connect again and it will succeed.
To fix the problem you could increase the value of SQLNET.INBOUND_CONNECT_TIMEOUT (in Seconds) in the sqlnet.ora / CONNECT_TIMEOUT_<DB_Name> (in Minutes) in the listener.ora file located on the server side.
3) If you already have a value you have considered adequate, you might want to add the following line on your listener.ora file:
DIAG_ADR_ENABLED_<listener_name>=OFF (in listener.ora)
& DIAG_ADR_ENABLED=OFF in sqlnet.ora
This line will hide the error on the alert.log file and the error will be posted on the $ORACLE_HOME/network/log/sqlnet.log file
-----------------------------
-----------
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual
Important Queries for Apps DBA
***************************
--Blocking Sessions--
Connect as sysdba and execute below Query to find out blocking sessions
set lines 130
set pages 200
column module format a20
column program format a20
column username format a15
select s.sid,s.serial#,s.status,p.spid,s.module,s.action,s.program
from v$session s,v$process p
where s.sid in (select session_id from dba_locks where blocking_others='Blocking')
and s.paddr=p.addr
--Note: Kill only INACTIVE blocking sessions. For Inactive blocking sessions,
--Event will be as shown below:
SQL> select EVENT from gv$session_wait where sid=&sid;
Enter value for sid: 19
old 1: select EVENT from gv$session_wait where sid=&sid
new 1: select EVENT from gv$session_wait where sid=19
EVENT
----------------------------------------------------------------
SQL*Net message from client
Locks in Specific Modules
Locks in AP Module
column object_name format a40
SELECT a.object_id, a.session_id, b.object_name
FROM v$locked_object a, dba_objects b
WHERE a.object_id = b.object_id
AND b.owner in ('AP')
--Another Query with more details
select s.sid sid_no,s.serial#,p.spid,s.action action,a.user_name user_name,
to_char(b.start_time,'DD-MON-YYYY HH24:MI') stime
from fnd_user a, fnd_logins b, v$session s, v$process p
where a.user_id = b.user_id
and p.addr = s.paddr
and s.process = b.spid
and b.end_time is null
and b.start_time > trunc(sysdate) - 1
and a.user_name = '&login_id'
and s.module like 'AP%'
--Runaway Forms Sessions
set lines 130
set pages 200
column userinfo heading "ORACLE/OS User" format a25
column terminal heading "Term" format a6
column process heading "Parent|Process ID" format a10
column "sid (audsid)" format a20
column spid heading "Shadow|Process ID" format a10
column event heading "Waiting" format a30
select s.username||' '||s.osuser||' ('||decode(s.terminal,'unknown','?','UNKNOWN','?',s.terminal) ||')' userinfo,
s.sid||','|| s.serial# ||' ('|| s.audsid ||')' "sid (audsid)", p.spid, w.event , w.p1
from v$session s, v$process p ,v$session_wait w
where p.addr = s.paddr and s.sid = w.sid and s.process = '&form_process_id' ;
--Sample Output:
Enter value for form_process_id: 19721
old 5: and s.process = '&form_process_id'
new 5: and s.process = '19721'
Shadow
ORACLE/OS User sid (audsid) Process ID Waiting P1
------------------------- -------------------- ---------- ------------------------------ ----------
APPS applmgr () 22,21485 (615657) 27872 SQL*Net message from client 1952673792
--Concurrent jobs Details
--List of Running Requests
set lines 130
set pages 200
col os form A7 head AppProc
col spid form a6 head SPID
col program form A50 trunc
set pages 38
col time form 99999.99 head Elapsed
col "Req Id" form 99999999
col "Parent" form a8
col "Prg Id" form 9999999
col serial# form 999999 head Serial#
col qname head "Manager" format a10 trunc
col sid format 9999 head SID
col user_name form A12 head User trunc
set recsep off
select
q.concurrent_queue_name qname
,f.user_name
,a.request_id "Req Id"
,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
,a.concurrent_program_id "Prg Id"
,a.phase_code,a.status_code
-- ,b.os_process_id "OS"
-- ,vs.sid
-- ,vs.serial# "Serial#"
-- ,vp.spid
,a.oracle_process_id "spid"
,(nvl(a.actual_completion_date,sysdate)-a.actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||
c2.user_concurrent_program_name||' '||a.description "Program"
from APPLSYS.fnd_Concurrent_requests a
,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_user f
-- ,v$session vs
-- ,v$process vp
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 c2.concurrent_program_id = c.concurrent_program_id
and c2.application_id = c.application_id
and a.phase_code in ('I','P','R','T')
and a.requested_by = f.user_id
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and c2.language = 'US'
-- and vs.process (+) = b.os_process_id
-- and vs.paddr = vp.addr (+)
order by 9 desc;
--List of pending Jobs waiting for managers:
set lines 130
col USER_CONCURRENT_QUEUE_NAME format a39
SELECT b.USER_CONCURRENT_QUEUE_NAME , count(*)
FROM apps.FND_CONCURRENT_WORKER_REQUESTS a,apps.FND_CONCURRENT_QUEUES_VL b
WHERE (Phase_Code = 'P' ) and a.hold_flag != 'Y'
and a.Requested_Start_Date <= SYSDATE
AND ('' IS NULL OR ('' = 'B' AND PHASE_CODE = 'R' AND
STATUS_CODE IN ('I'))) and a.CONCURRENT_QUEUE_ID!=1003
and a.CONCURRENT_QUEUE_ID=b.CONCURRENT_QUEUE_ID
group by b.USER_CONCURRENT_QUEUE_NAME ,status_code;
col program_description format a60
col user_concurrent_queue_name format a40
SELECT count(b.request_id) count, b.program_description, a.user_concurrent_queue_name
FROM apps.FND_CONCURRENT_QUEUES_VL a, apps.FND_CONCURRENT_WORKER_REQUESTS b
WHERE a.enabled_flag='Y'
AND a.concurrent_queue_id = b.concurrent_queue_id
AND (b.Phase_Code = 'P' OR b.Phase_Code = 'R') AND b.hold_flag != 'Y'
AND b.Requested_Start_Date <= SYSDATE
AND 1=1
-- and a.user_concurrent_queue_name like 'Inventory%'
group by a.user_concurrent_queue_name, b.program_description order by 3,1 asc
--Long Running Requests (over a specified amount of time hard coded as 4 hours)
set lines 130
column start_time format a15
column USER_CONCURRENT_PROGRAM_NAME format a40
select b.REQUEST_ID, a.USER_CONCURRENT_PROGRAM_NAME, b.phase_code,
(sysdate - b.actual_start_date) * 24 "running",
to_char(b.request_date, 'mm/dd/yyyy hh24:mi') "request_date",
to_char(b.actual_start_date, 'mm/dd/yyyy hh24:mi') "start_time"
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.STATUS_CODE = 'R'
and b.PHASE_CODE = 'R'
and ((sysdate - b.actual_start_date) * 24) > 4
and a.CONCURRENT_PROGRAM_ID NOT IN(36887,43393,38121,42789,31556);
--Total scheduled requests
set lines 130
select 'Scheduled requests:' schedt, count(*) schedcnt
from fnd_concurrent_requests
WHERE (requested_start_date > sysdate OR
status_code = 'P')
AND phase_code = 'P';
SELECT request_id id,
nvl(meaning, 'UNKNOWN') status,
user_concurrent_program_name pname,
to_char(request_date, 'DD-MON-RR HH24:MI:SS') submitd,
to_char(requested_start_date, 'DD-MON-RR HH24:MI:SS') requestd
FROM fnd_concurrent_requests fcr,
fnd_lookups fl,
fnd_concurrent_programs_vl fcpv
WHERE phase_code = 'P'
AND (fcr.requested_start_date >= sysdate OR
status_code = 'P')
AND LOOKUP_TYPE = 'CP_STATUS_CODE'
AND lookup_code = status_code
AND fcr.concurrent_program_id = fcpv.concurrent_program_id
AND fcr.program_application_id = fcpv.application_id
ORDER BY pname, request_date;
--Pending requests Kept (on hold)
set lines 130
select 'Requests on hold:' schedt, count(*) schedcnt
from fnd_concurrent_requests
WHERE hold_flag = 'Y'
AND phase_code = 'P';
SELECT request_id id,
nvl(meaning, 'UNKNOWN') status,
user_concurrent_program_name pname,
to_char(request_date, 'DD-MON-RR HH24:MI:SS') submitd
FROM fnd_concurrent_requests fcr,
fnd_lookups fl,
fnd_concurrent_programs_vl fcpv
WHERE phase_code = 'P'
AND hold_flag = 'Y'
AND fcr.requested_start_date <= sysdate
AND status_code != 'P'
AND LOOKUP_TYPE = 'CP_STATUS_CODE'
AND lookup_code = status_code
AND fcr.concurrent_program_id = fcpv.concurrent_program_id
AND fcr.program_application_id = fcpv.application_id
ORDER BY request_date, request_id;
--List of pending Requests (used for Pending jobs threshold Monitor)
set lines 130
set pages 200
column REQUEST heading 'Request' format 9999999999
column PHASE heading 'Phase' format A8
column STATUS heading 'Status' format A8
column PROGRAM heading 'Program Name' format A40
column SHORT heading 'Short Name' format A15
column REQUESTOR heading 'Requestor' format A15
column START_TIME heading 'Start Time' format A15
column RUN_TIME justify left heading 'Time(m)' format 999999.9
column description format a75
select b.REQUEST_ID "Request", a.description,
to_char(b.request_date, 'mm/dd/yyyy hh:mi:ss') "request_date",
to_char(b.requested_start_date, 'mm/dd/yyyy hh:mi:ss') "request_start"
from APPS.FND_CONCURRENT_PROGRAMS_VL a,
APPS.FND_CONCURRENT_REQUESTS b
where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
and b.PHASE_CODE = 'P'
and b.HOLD_FLAG !='Y'
and b.requested_start_date <= sysdate
--Steps to terminate a Concurrent request from backend
set lines 130
set pages 200
A)
select oracle_process_id ,
decode(status_code,'R','Running','D','Canceled','E','Error','X','Terminated','G', 'Warning','T','Terminating')"Status_code",
phase_code,to_char(actual_start_date,'DD-MON-YYYY=>hh24:mi:ss') "Login Time"
from apps.fnd_concurrent_requests where request_id='&Enter_conn_req_id'
B)
select s.sid,s.serial#,module,s.status from v$session s,v$process p
where s.paddr=p.addr
and p.spid=&oracle_process_id
C)
update fnd_concurrent_requests
set phase_code='C',
status_code='X'
where request_id=&Enter_REQUESTID;
commit;
**Make sure that database session for the request id is killed using the output from step A and B.**
--Steps to Cancel pending jobs
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P' AND status_code in ('Q','I');
Commit;
--Steps to Terminate pending jobs
update fnd_concurrent_requests
set phase_code='C',status_code='X'
WHERE phase_code = 'P' AND status_code in ('Q','I');
Commit;
--Query to find out list of NLS installed in Applications
set lines 130
select LANGUAGE_CODE from fnd_languages where INSTALLED_FLAG='I';
select NLS_LANGUAGE,LANGUAGE_CODE ,INSTALLED_FLAG from fnd_languages
where INSTALLED_FLAG in ('I','B');
--To get the list of installed products and patch level
set lines 130
set pages 400
col application_id format 99990 heading "ID"
col application_name format a40 heading "Name"
col application_prefix format a6 heading "Prefix"
col application_short_name format a10 heading "Short name"
col apps format a8 heading "Product"
col install_group_num format 90 heading "Inst Grp"
col installed_flag format a9 heading "Type"
col language_code format a4 heading "Code"
col module_short_name format a8 heading "Module"
col module_version format a8 heading "Version"
col product_group_id format 990 heading "ID"
col product_group_name format a28 heading "Product Group Name"
col product_group_type format a10 heading "Type"
col product_version format a8 heading "Version"
col argument1 format a20 heading "Arguments"
col release_name format a12 heading "Release"
col updated format a11 heading "Updated"
col patchset format a20 heading "Patchset Level"
col status format a14 heading "Appl Status"
prompt --> Product Installation Status, Version Info and Patch Level
select decode(nvl(a.APPLICATION_short_name,'Not Found'), 'SQLAP','AP','SQLGL','GL','OFA','FA',
'Not Found','id '||to_char(fpi.application_id), a.APPLICATION_short_name) apps,
decode(fpi.status,'I','Installed','S','Shared','N','Inactive',fpi.status) status,
fpi.product_version,nvl(fpi.patch_level,'-- Not Available --') Patchset,
to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date"
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
and fpi.oracle_id = o.oracle_id(+)
order by 1,2;
--Workflow Mailer configuration without login into OAM
set lines 130
set pages 200
col value format a30
select p.parameter_id,p.parameter_name,v.parameter_value value
from apps.fnd_svc_comp_param_vals_v v,
apps.fnd_svc_comp_params_b p,
apps.fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in
('OUTBOUND_SERVER', 'INBOUND_SERVER',
'ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')
order by p.parameter_name;
--Script to backup database links
connect to database as sysdba
set lines 130
set pages 200
select 'DB link for '||b.username||chr(10)||' create database link '||c.name||' connect to '||c.userid|| ' identified by '||c.password||' using '''||c.host||''';'
from dba_users b ,link$ c
where c.owner#=b.user_id
union
select 'DB link for Public' ||chr(10)|| ' create public database link '||c.name||' connect to
'||c.userid|| ' identified by '||c.password||' using '''||c.host||''';'
from link$ c
where owner#=1;
---------------------------------
**************************************************************
Removing Fatal NI connect error 12170 from Database Alert Log
In our one of the 11gR2 Production Database, there were so many entries w.r.t Fatal NI connect error 12170 + TNS-12535: TNS:operation timed out + TNS-00505: Operation timed out (with different ports), found in both RAC Instances Alert Log file.
Problem:
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 20-AUG-2014 14:17:36
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505
TNS-00505: Operation timed out
nt secondary err code: 110
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=44326))
Reason:
The same error message was repeating during whole day almost for every application server.
I’ve finally found what was causing the problem. Our database is behind a firewall. Firewall has an “idle session timeout” value. If a connection remains idle for more than the “idle session timeout” value it drops the connections.
Application developers usually configure their connection pools to remain alive for a long time, which is more than the “idle session timeout” value of the firewall. Not all the connections in the pool are used. Some stays idle. After sometime the firewall drops them and I get those operation timed out problems.
Solution / Workaround:
1) Add the following line to the sqlnet.ora file on the server.
SQLNET.EXPIRE_TIME=10
In this configuration database will probe the application servers every 10 minutes to see if they are up. In fact this is a mechanism to determine stale connections by database. Because it sends packets to application servers every ten minutes, the connections are detected as active by firewalls and they are not broken.
**In an installation that includes GRID, this parameter should be set in the RDBMS_HOME/network/admin/sqlnet.ora file. This would be the default location for sqlnet.ora file parameters referenced by the instance.
2) One way to minimize the impact is by using the parameter SQLNET.INBOUND_CONNECT_TIMEOUT (default to 60 seconds on 10gR2 and 11g) but, sometimes, this value is not adequate.
Oracle also mention the occurrence of this error if you use DB Console or Enterprise Manager to monitor your databases and the em agent will try to connect to the target database repeatedly and, statistically, some will fail (frequency will depend on how busy your system is).
Most of the time (certainly for DB Console and Enterprise Manager Agent) the application will try to connect again and it will succeed.
To fix the problem you could increase the value of SQLNET.INBOUND_CONNECT_TIMEOUT (in Seconds) in the sqlnet.ora / CONNECT_TIMEOUT_<DB_Name> (in Minutes) in the listener.ora file located on the server side.
3) If you already have a value you have considered adequate, you might want to add the following line on your listener.ora file:
DIAG_ADR_ENABLED_<listener_name>=OFF (in listener.ora)
& DIAG_ADR_ENABLED=OFF in sqlnet.ora
This line will hide the error on the alert.log file and the error will be posted on the $ORACLE_HOME/network/log/sqlnet.log file
-----------------------------
No comments:
Post a Comment