Increase Concurrent Manager in Oracle EBS

https://dbissues.blogspot.com/2018/06/increase-concurrent-manager.html

For my Case

1:  Go to system =administrator responsibility and Click =(Define Profile Options)

2: Click on =Concurrent ->  Manager -> Define

3: Now Press on = F11

4: Now Type Standard Manager like below.

5: Now press ctrl with  F11 

6: Press on work shift .

7: Increase Process from 3 to 6  and press save like below.

8: Now Exit all Screen and go to -->Concurrent --> Manager --> Administrator.

9: Now You can see here Concurrent Manager value is 6.

The End

http://aporaclepayables.blogspot.com/2016/01/online-create-accounting-program-stucks.html

Gather Schema Statistics in Oracle EBS

https://dbissues.blogspot.com/2018/06/gather-schema-statistics.html

Introduction: When the data is updated continuously by updating, inserting or deleting of by users (Functional/Technical/End users), it becomes necessary to gather statistics as the performance of database goes down. Cost-Based Optimizer (CBO) - The CBO uses database statistics to generate several execution plans, picking the one with the lowest cost, where cost relates to system resources required to complete the operation. Oracle E-Business Suite provides a set of procedures in the FND_STATS package to facilitate collection of these statistics. FND_STATS uses the DBMS_STATS package to gather statistics.

Run Gather Scheme From Back End:

Use the following command to gather schema statistics:
exec fnd_stats.gather_schema_statistics(‘ONT’) < For a specific schema >
exec fnd_stats.gather_schema_statistics(‘ALL’) < For all schemas >

How to run Gather Schema Statistics in ALL Schema's:

1: Run Single Request .




2: Type Gather Schema with percentage as below and press TAB.




3: The following image appears.




4: Fill the form like below.




5: Press OK and then press Submit.




6: Gather Schema is in Process.

Parameters Introduction:

Schema Name: In this parameter you have specify schema name Or All For all schema name.

Estimate Percent: Not Allowed in 11g oracle works here automatically.

Degree: Cpu x cores

Backup Flag: Backup previous last run gather stats.

Restart Request ID: Submit request id in case of error restart through request after when issue was resolved.

History Mode: Save history of last run gather stats.

Gather Options: Auto Gather which run only on incremental changes.

Modification Threshold: The default is 10% (i.e. meaning any table which has changed via DML more than 10%, stats will be collected, otherwise it will be skipped).

Invalidate Dependent Cursor:

===========Error:===========

**Starts**03-JAN-2018 22:03:26

ORACLE error 20005 in FDPSTP

Cause: FDPSTP failed due to ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "APPS.FND_STATS", line 780

ORA-06512: at line 1

The SQL statement being executed at the time of the error was: and was exe

+---------------------------------------------------------------------------+

Start of log messages from FND_FILE

+---------------------------------------------------------------------------+

In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 2 internal_flag= NOBACKUP

stats on table FND_CP_GSM_IPC_AQTBL is locked

stats on table FND_SOA_JMS_IN is locked

stats on table FND_SOA_JMS_OUT is locked

ORA-20005: object statistics are locked (stattype = ALL)

+---------------------------------------------------------------------------+

Query to Check Locked Schema Objects:

select table_name, stattype_locked from dba_tab_statistics where owner = '<schema>' and stattype_locked is not null;

Query to Check All Locked Schema Objects:

select table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

Solution:

Unlock:

EXEC DBMS_STATS.UNLOCK_TABLE_STATS(USER,' FND_CP_GSM_IPC_AQTBL');

EXEC DBMS_STATS.UNLOCK_TABLE_STATS(USER,'FND_SOA_JMS_IN');

EXEC DBMS_STATS.UNLOCK_TABLE_STATS(USER,'FND_SOA_JMS_OUT');

Run Gather Schema:

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'FND_CP_GSM_IPC_AQTBL');

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'FND_SOA_JMS_IN');

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'FND_SOA_JMS_OUT'); 

To generate unlock statement for all tables in the schema you can use following,
select ‘exec DBMS_STATS.UNLOCK_TABLE_STATS (”’|| owner ||”’,”’|| table_name ||”’);’ from dba_tab_statistics where owner = ‘<schema>” and stattype_locked is not null;

Oracle Database Health Check

https://dbissues.blogspot.com/2018/06/oracle-database-health-check.html

1: Check the free space of all mount points from the following command.
Command: df -h

################################################################

2: Check is there any unwanted file or folder which is taking space but don't delete them only tell their
database administrator (You have to check this manually).

#################################################################

3: Check the database size where all the data files are present with the following command.

for example :

cd /u01/oracle/PROD/db/apps_st

Command: du -sh data

#####################################################################

4: Check the amount of memory through the following command and check how much memory is utilizing and how much is free.

Command : top

#######################################################################

5: Must check thier user and group name is database and application is running on same user and group

Or different from the following command.

Command: ls -alrt

##########################################################################

6: Check all users password's check is there any password which is set to default .
for example:

/as sysdba

system / manager

apps/apps

gl/gl

ap/ap

Note: If there is any password which is set to default as above tell there database administrator to change it.

#####################################################################

7: Check from which parameter file database instance is running. Is it is running with pfile Or spfile. from the following command.

Command :

sql>show parameter spfile

Note : Please if it is running with pfile please tell thier database adminitrator to run thier database with

spfile because pfile is easily editible.

#######################################################################

8: Check control file status from sqlplus from the following command. If all control file set to same mount points are same location tell their database administrator to multiplex it.

connect as sysdba
SQL> select status, name from v$controlfile;

STATUS NAME
------- ---------------------------------
/u01/oradata/L102/control01.ctl
/u02/oradata/L102/control02.ctl

###############################################################

9: Check number of Datatop's from the following commands.

conn sqlplus

sql> select filename from dba_data_files;

##############################################################################

10: Check the archiving is enabled and log file is switching successfully to its position. from the following command.

To check location

sql> archive log list.

Switch logfile to check weather its switching to its position or not.

sql> alter system switch logfile.

########################################################################

11: Check alert log file check is there is any error inside error log .Check location from the following command.
sql> show parameter background;

###########################################################################

12: Check rman backup policy from rman through the following command and check Rman backup log

check if there is any error inside or not.
Rman> show all;

#############################################################################

13: Check the tablespace size from following command if need to add tell their database administrator add it.

SELECT a.tablespace_name,

ROUND (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES) * 100,2) percentage_used,

c.BYTES / 1024 / 1024 space_allocated,

ROUND (c.BYTES / 1024 / 1024 - NVL (b.BYTES, 0) / 1024 / 1024,2) space_used,

ROUND (NVL (b.BYTES, 0) / 1024 / 1024, 2) space_free,

c.DATAFILES

FROM dba_tablespaces a,

( SELECT tablespace_name,

SUM (BYTES) BYTES

FROM dba_free_space

GROUP BY tablespace_name

) b,

( SELECT COUNT (1) DATAFILES,

SUM (BYTES) BYTES,

tablespace_name

FROM dba_data_files

GROUP BY tablespace_name

) c

WHERE b.tablespace_name(+) = a.tablespace_name

AND c.tablespace_name(+) = a.tablespace_name

ORDER BY NVL (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES), 0) DESC;

##############################################################################

14: Check how much temporary tablespace is running from the following command.

sql> select tablespace_name, sum(bytes)/1024/1024 mb

from dba_temp_files

group by tablespace_name;

#############################################################################

15: Check which temporary tablespace is set to default from the following command.
select * from database_properties where property_name like 'DEFAULT%TABLESPACE';

#################################################################################

16: Check current usage of temporary tablespace.

select ss.tablespace_name,
sum((ss.used_blocks*ts.blocksize))/1024/1024 mb
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name;



Note : If need to add than tell thier database administrator to add.

############################################################################

17: Check the size of undo tablespace from the following command.

sql> select tablespace_name,sum(bytes)/1024/1024 "MB" from dba_data_files where TABLESPACE_NAME like '%UNDO%' group by tablespace_name;

#################################################################################

18: check name and retention policy of undo tablespace from the following command.

sql> show parameter undo;

#################################################################################

19: Check the datafile size of undo tablespace from the following command.

select FILE_NAME,TABLESPACE_NAME,sum(bytes)/1024/1024 "MB" from dba_data_files where TABLESPACE_NAME like '%UNDO%' group by FILE_NAME,TABLESPACE_NAME;

##########################################################################

20: Check free space of tablespace.

SELECT SUM(BYTES)/1024/1024 "MB" FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME ='UNDO_TBS';

Note: if need to add then tell their database administrator to add datafile.

#####################################################

21: Check the last log of adpreclone.pl on database Tier check is thier any error inside the log or not from the following location.

for example:

/u01/oracle/VIS/db/tech_st/11.1.0/appsutil/log/VIS_erp/StageDBTier_12251847.log

############################################################################

22: Check the last log of adpreclone.pl on APPS Tier check is their any error inside the log or not from the following location.

for example:

/u01/oracle/VIS/inst/apps/VIS_erp/admin/log/StageAppsTier_12251850.log

#################################################################################

22: Check invalid objects through the following command.

sql> select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where status='INVALID';

################################################################################

23: Check tnsnames.ora file to check dataguard tns ping is successful or not through the following command.

Command: tnsping standby

###############################################################################

24: Check cold backup going fine check previous backup size and current backup size through the following command.

Command : du -sh

##############################################################################

25: Check hosts file from location to verify standby ip and hostname through the following command
and ping standby ip on primary server to check no network break found between standby and primary.

cat /etc/hosts

################################################################################

26: Check recycle bin is there any unwanted data is stored in recycle bin from the following command.

select * from recyclebin.

Note: Is there any data which is in recycle bin tell their dba to purge recyclebin to realease space from database.

#################################################################################

27: Sga should be at least 40% or more then 40% of total ram. and check pga must be 20% of sga or higher.

#################################################################################

28: Check DBA_USERS to verify is there any unwanted or expired custom user is there with the following command.

sql> select * from dba_users;

#################################################################################

29: check is there any user which have dba role through the following command.

sql>select * from DBA_ROLE_PRIVS;

#################################################################################

30: check dba_db_links to check is there any unwanted db_link is there through the following command.

sql> select * from dba_db_links;

#################################################################################

31: Check crontab to verify how much job are running automatically and also check are all shell scripts doing there job successfully or not through the command.

crontab -l

#################################################################################

32: Run active user report and active responsibility report from front end to check whether is there any.

expired user left or not.

#################################################################################

33: Check the concurrent status from end that how many concurrent is running and how many is free check this for load testing.

#################################################################################

34: Check is there is any corruption in database.

select * from V$DATABASE_BLOCK_CORRUPTION;

#################################################################################

34: Check audit logs from backend. through the command .

SQL> show parameter audit.

#################################################################################

35: The v$resource_limit shows the current and maximum global resource utilization for some system resources.

sql> select * from v$resource_limit where resource_name in ('processes','sessions',transactions');

#################################################################################

36: Generate AWR report for health check .

sql> @$ORACLE_HOME/rdbms/admin/awrrpt.sql <---AUTOMATUC WORK REPOSITORY

#################################################################################

37: ADDM report provides Findings and Recommendations to fix the issue.

SQL> @?/rdbms/admin/addmrpt.sql

#################################################################################

38: Check database status.

sql> select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

#################################################################################

39 : EBS Database Parameter Settings Analyzer (Doc ID 1953468.1)

conn apps/apps

>@db_param_analyzer.sql

fnd users = 300

#################################################################################

40: Undo adviser How much undo have you needed and how much you have.

col "ACTUAL UNDO SIZE [MByte]" for 999999999

col "UNDO RETENTION [Sec]" for a20

col "OPTIMAL UNDO RETENTION [Sec]" for 999999999

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MB]",

SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",

(TO_NUMBER(e.value) * TO_NUMBER(f.value) *

g.undo_block_per_sec) / (1024*1024)

"NEEDED UNDO SIZE [MB]"

FROM (SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO'

AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f, (

SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat ) g WHERE e.name = 'undo_retention'

AND f.name = 'db_block_size';

#################################################################################

Allow and Deny Hosts to connect Linux Operating System

https://dbissues.blogspot.com/2018/06/allow-and-deny-hosts-in-oracle-database.html

Allow Hosts

cd  /etc

[root@testweb etc]# vi hosts.deny

x

Deny Hosts:

cd  /etc

vi  hosts.deny

After setting the above parameter no one can connect now.

Changing ssh Port

https://dbissues.blogspot.com/2018/06/changing-ssh-port.html

cd   /etc/ssh

vi    sshd_config

Edit the port which you want to set  In my case i have set 1993.

x

Restart sshd services.

Finished

MONITORING TABLEPSACE SIZE

https://dbissues.blogspot.com/2018/06/monitoring-tablepsace-size.html

1:) To check total size of table space in "MB";
select tablespace_name,sum(bytes)/1024/1024 "total size in mb" from dba_data_files

group by tablespace_name;


2:) To check free space in table space in "MB";
select tablespace_name,sum(bytes)/1024/1024 "free space in mb" from dba_free_space

group by tablespace_name; 

3:) You can check the single datafile full space by the following command;
select tablespace_name,file_id,file_name,sum(bytes)/1024/1024 "total size in mb" from dba_data_files

where file_id=6

group by tablespace_name,file_id,file_name; 

4:) You can check the single datafile free space by the following command;

select tablespace_name,file_id,sum(bytes)/1024/1024 "size in MB" from dba_free_space

where file_id=6

group by tablespace_name,file_id; 

5:)To add datafile in tables pace USERS;
alter table space users add datafile '/u01/app/oracle/oradata/orcl/users03' size 2m; 

6:) To drop datafile from tablespace USERS;
alter tables pace users drop datafile '/u01/app/oracle/oradata/orcl/users02.dbf';

Application DBA Queries

https://dbissues.blogspot.com/2018/05/application-dba-queries.html


QUERY TO GET all user assigned to GIVEN RESPONSIBILITY
SELECT UNIQUE u.user_id,
Substr(u.user_name, 1, 30) user_name,
Substr(r.responsibility_name, 1, 60) responsiblity,
Substr(a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
AND r.responsibility_name = ‘System Administrator’
–“Write Responsibility Name Here “
ORDER BY Substr(user_name, 1, 30),
Substr(a.application_name, 1, 50),
Substr(r.responsibility_name, 1, 60);


Query to get all Responsibility assigned to a user
SELECT UNIQUE u.user_id,
Substr (u.user_name, 1, 30) user_name,
Substr (r.responsibility_name, 1, 60) responsiblity,
Substr (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
AND u.user_name = ‘SYED.SALI’ –“Write Application User Name Here “
ORDER BY Substr (user_name, 1, 30),
Substr (a.application_name, 1, 50),
Substr (r.responsibility_name, 1, 60);


QUERY FOR Workflow Mailer Configurations
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;


QUERY TO FIND WHO is connected when to which responsibility 

SELECT DISTINCT fu.user_name User_Name, fr.responsibility_key Responsibility FROM fnd_user fu, fnd_responsibility fr, icx_sessions ic WHERE fu.user_id = ic.user_id AND fr.responsibility_id = ic.responsibility_id AND ic.disabled_flag = 'N' AND ic.responsibility_id IS NOT NULL AND ic.last_connect LIKE SYSDATE; -- "Enter the Date here" 

 

QUERY TO FIND Number of user connected to EBS
SELECT Count (DISTINCT d.user_name)
FROM apps.fnd_logins a,
v$session b,
v$process c,
apps.fnd_user d
WHERE b.paddr = c.addr
AND a.pid = c.pid
AND a.spid = b.process
AND d.user_id = a.user_id
AND ( d.user_name = ‘USER_NAME’
OR 1 = 1 );


QUERY TO FIND E-BUSINESS SUITE PROFILE OPTIONS FOR ALL VALUES
SELECT p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
Decode(v.level_id, 10001, ‘Site’,
10002, ‘Application’,
10003, ‘Responsibility’,
10004, ‘User’,
10005, ‘Server’,
10006, ‘Org’,
10007, Decode(To_char(v.level_value2), ‘-1’,
‘Responsibility’
,
Decode(To_char(v.level_value), ‘-1’, ‘Server’,
‘Server+Resp’)),
‘UnDef’) LEVEL_SET,
Decode(To_char(v.level_id), ‘10001’, ”,
‘10002’, app.application_short_name,
‘10003’, rsp.responsibility_key,
‘10004’, usr.user_name,
‘10005’, svr.node_name,
‘10006’, org.name,
‘10007’,
Decode(To_char(v.level_value2), ‘-1’,
rsp.responsibility_key,
Decode(
To_char(v.level_value), ‘-1’, (SELECT
node_name
FROM
fnd_nodes
WHERE
node_id = v.level_value2),
(SELECT
node_name
FROM
fnd_nodes
WHERE
node_id = v.level_value2)
|| ‘-‘
||
rsp.responsibility_key)),
‘UnDef’) “CONTEXT”,
v.profile_option_value VALUE
FROM fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE p.profile_option_id = v.profile_option_id(+)
AND p.profile_option_name = n.profile_option_name
AND Upper(p.profile_option_name) IN
(SELECT profile_option_name
FROM fnd_profile_options_tl
WHERE Upper(user_profile_option_name) LIKE
Upper(‘%&user_profile_name%’))
AND usr.user_id(+) = v.level_value
AND rsp.application_id(+) = v.level_value_application_id
AND rsp.responsibility_id(+) = v.level_value
AND app.application_id(+) = v.level_value
AND svr.node_id(+) = v.level_value
AND org.organization_id(+) = v.level_value
ORDER BY short_name,
user_profile_option_name,
level_id,
level_set;


QUERY TO FIND APPLICATION USER DETAILS
SELECT user_name,
user_id,
Decode(employee_id, NULL, NULL,
‘E’) employee_flag,
description,
start_date,
end_date,
Decode(end_date, Least(end_date, SYSDATE), NULL,
‘+’) f,
To_char(last_logon_date, ‘mm/dd/yy hh24:mi:ss’) last_logon_time,
Decode(end_date, NULL, SYSDATE – last_logon_date) last_logon_days,
Decode(end_date, NULL, Decode(30, Greatest(30, Trunc(
SYSDATE – last_logon_date))
, NULL,
‘*’)) p,
password_lifespan_days,
password_date,
Decode(end_date, NULL, SYSDATE – password_date) password_change_days
FROM applsys.fnd_user u
WHERE user_name LIKE Upper(‘%&userid%’) –“Write Application User Name Here “
ORDER BY description,
user_name;


QUERY TO FIND COMPLETE DATABASE SIZE
SELECT ‘DATA_n_INDEX: ‘
|| SUM(bytes) / 1024 / 1024 / 1024
|| ‘ GBytes’ FULL_DATABASE_SIZE
FROM dba_data_files
UNION
SELECT ‘TEMP: ‘
|| SUM(bytes) / 1024 / 1024 / 1024
|| ‘ GBytes’
FROM dba_temp_files
UNION
SELECT ‘REDO LOGS: ‘
|| SUM(bytes) / 1024 / 1024 / 1024
|| ‘ GBytes’
FROM v$log
UNION
SELECT ‘CONTROLFILE: ‘
|| SUM(file_size_blks * block_size) / 1024 / 1024
|| ‘ MBytes’
FROM v$controlfile;


QUERY TO VERIFY THE WORKFLOW IMAP SETTING
SELECT c.component_id,
c.component_name,
p.parameter_id,
p.parameter_name,
v.parameter_value value
FROM fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p,
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 ( ‘INBOUND_SERVER’, ‘ACCOUNT’, ‘REPLYTO’ )
ORDER BY c.component_id,
c.component_name,
p.parameter_name;


QUERY TO CHECK WEB SESSIONS
SELECT Count(*)
FROM v$process p,
gv$session s
WHERE p.addr = s.paddr
AND s.program = ‘JDBC Thin Client’;


QUERY TO FIND Global Locks
SELECT Count(*)
FROM gv$lock a
WHERE a.id1 IN (SELECT id1
FROM gv$lock
WHERE request <> 0);


QUERY to find out version of a PACKAGE
SELECT text
FROM dba_source
WHERE line = 2
AND name = ‘AP_IMPORT_INVOICES_PKG’; — “ENTER PACKAGE NAME HERE”