Init parameter in 12c database

Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1) has recommendations on parameter values.  Below lists init parameters that are not set as the default to support a R12.1.3 instance with about 300 users at peak time.

NAME	VALUE
_b_tree_bitmap_plans	FALSE
_fast_full_scan_enabled	FALSE
_like_with_bind_as_equality	TRUE
_optimizer_autostats_job	FALSE
_sort_elimination_cost_ratio	5
_sqlexec_progression_cost	2147483647
_system_trig_enabled	TRUE
_use_single_log_writer	TRUE
aq_tm_processes	4
audit_sys_operations	TRUE
audit_trail	DB (default is NONE)
compatible	12.1.0
control_file_record_keep_time	30
control_files	/path/to1/ctrl01.dbf, /path/to2/ctrl02.dbf, /path/to3/ctrl03.dbf
cursor_sharing	EXACT
db_block_checking	FALSE
db_block_checksum	TRUE
db_block_size	8192
db_cache_size	5368709120 (or, 5G)
db_files	1000
db_name	EBSPROD
diagnostic_dest	/path/to/db_ebsprod/admin/EBSPROD_ebsdb1p
dml_locks	10000
filesystemio_options	setall
java_jit_enabled	FALSE
job_queue_processes	20
local_listener	(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EBSPROD_ipc)))
log_archive_dest	/path/to/EBSPROD/arch/arch.log
log_buffer	10485760 (or, 10M)
log_checkpoint_interval	100000
log_checkpoint_timeout	1200
log_checkpoints_to_alert	TRUE
max_dump_file_size	10000
memory_max_target	25769803776 (or, 24G)
memory_target	25769803776
nls_comp	binary
nls_date_format	DD-MON-RR
nls_language	AMERICAN
nls_length_semantics	BYTE
nls_numeric_characters	.,
nls_sort	binary
nls_territory	AMERICA
olap_page_pool_size	4194304 (or, 4M)
open_cursors	4096
optimizer_adaptive_features	FALSE
optimizer_secure_view_merging	FALSE
os_authent_prefix	ext$
parallel_max_servers	64
parallel_min_servers	0
parallel_servers_target	64
parallel_threads_per_cpu	2
pga_aggregate_limit	0
pga_aggregate_target	0 (this is the default. Could be set to 1G)
plsql_code_type	INTERPRETED
plsql_optimize_level	0
processes	3000
query_rewrite_enabled	true
recovery_parallelism	1
recyclebin	ON
remote_login_passwordfile	NONE
remote_os_authent	FALSE
sec_case_sensitive_logon	FALSE
sec_protocol_error_further_action	drop,3
session_cached_cursors	500
session_max_open_files	100
sessions	4536
sga_max_size	25769803776 (or, 24G)
shared_pool_reserved_size	214748364
shared_pool_size	4294967296 (or, 4G)
sql92_security	TRUE
temp_undo_enabled	TRUE
timed_statistics	TRUE
undo_management	AUTO
undo_retention	43200
undo_tablespace	APPS_UNDO_TBS
utl_file_dir	/path/to/EBSPROD/utl_dir, /usr/tmp
workarea_size_policy	AUTO

The database server is an IBM LPAR configuration. 4 Oracle databases on it share its resources of 6 cores CPUs and 92 GB memory, and it is all virtual.

parallel_max_servers =  Maximum value should be 2 X #of CPUs.
This parameter depends on the number of cores. It uses logical CPUs and 5 cores (4-way SMP).  The SMP threads don’t really replicate a full core.

optimizer_adaptive_features = FALSE
If it is set to true, queries may run OK on a first run, but poorly on subsequent runs. Oracle will try to adapt the plans using cardinality feedback and other features that can be good, but can also cause issues.

parallel_threads_per_cpu = 2
Because the core CPU are 4-way SMP, it shows 4x more CPUs than it really has. If it set to a higher value (say, 16), Oracle will keep overallocating the CPU resources for parallel queries and spinning up too many parallel threads for the memory and CPU that it has.

APP-FND-02702: ABM is not a valid oracle user

Oracle file https://docs.oracle.com/cd/E26401_01/doc.122/e22952/T156458T659606.htm lists schemas names of Oracle EBS. But, some schemas became obsolete in R12.

EBS schema names must be in table FND_ORACLE_USERID and EBS user name must appear in table FND_USER.

All below schema owners are not in table FND_ORACLE_USERID in my R12.1.3 instances. It is safe to change their password by alter statement, such as "alter user ABM identified by new_Pwd;", while FNDCPASS will give error APP-FND-02702: ABM is not a valid oracle user because FNDCPASS utility and ALLORACLE functionality were designed for valid applications users/schemas.

SELECT ORACLE_ID, ORACLE_USERNAME, CREATION_DATE,LAST_UPDATE_DATE,ENABLED_FLAG,DESCRIPTION
  FROM apps.FND_ORACLE_USERID
WHERE ORACLE_USERNAME IN (
'ABM',
'AHM',
'AMF',
'CSS',
'CUE',
'CUN',
'EAA',
'EVM',
'FPT',
'IBA',
'IMT',
'IPD',
'ME',
'OKB',
'OKO',
'OKR',
'OZP',
'OZS',
'RHX',
'RLA',
'VEH',
'XNC',
'XNI',
'XNM',
'XNS',
);

How to run a concurrent program by a custom Manager

Step 1: Create a custom concurrent manager.
Follow Doc ID 170524.1 (How to Create a Custom Concurrent Manager)

1. Navigate to Concurrent > Manager > Define.
2. Manager Field: Custom Manager.
3. Short Name: CUSTOM.
4. Type: Concurrent Manager.
5. Program Library - Name: FNDLIBR.
6. Enter desired cache (such as "2").
7. Work Shifts: Standard.
8. Enter number of Processes (e.g "6").   <= here you can modify it later
9. Provide Specialization Rules
   (you can include or exclude program, id, user, types or combination).
10. Save.
11. Navigate to Concurrent / Manager / Administer.
12. Activate the Custom Manager.

Step 2: Assign concurrent program to a custom manager 
Follow steps in Doc ID 2268941.1: How To Set Concurrent Program To Run Exclusively From A Custom Manager
1. From System Administrator Responsibility navigate to:
  a. Concurrent > Manager > Define
  b. Query up the Custom Manager (such as "Long Run Jobs Manager")
  c. Click on Specialization Rules button
2. In the first column, select to Include
3. In the second column select Program for Type
4. In the third column select the Application that the concurrent program was defined to run under
5. In the fourth column select the concurrent program name.
6. Save.
7. Query up the Standard Manager definition.
8. Under the Specialization Rules Exclude the same program and Save.

Additional documents on this topic:

Doc ID 343575.1: How to Determine Which Manager Ran a Specific Concurrent Request?
SQL> select b.USER_CONCURRENT_QUEUE_NAME
from fnd_concurrent_processes a,
 fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where  a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
 and    a.CONCURRENT_PROCESS_ID = c.controlling_manager
 and    c.request_id = &request_id;

Doc ID 344011.1: How Do I Find Out Which Concurrent Manager Processed A Concurrent Request/Program.

ORA-12154: TNS:could not resolve the connect identifier specified

Error " ORA-12154: TNS:could not resolve the connect identifier specified " or " ORA-12505: TNS:listener does not currently know of SID given in connect descriptor " is a very generic message. It does not tell the true problem.

For example, I had a wrong port number 1521 in tnsnames.ora file. tnsping reads the string from tnsnames.ora without a complaint:

$  tnsping ebsdev
......
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=db_server1d.domain.com)(PORT=1521)) (CONNECT_DATA= (SID=EBSDEV)))
OK (20 msec)

But SQL*Plus does not connect to the database:

$ sqlplus apps/appsPWD@ebsdev
......
ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

The real problem is that port 1521 is the wrong port entered in tnsnames.ora file.

In Oracle EBS environment, SQL*Plus uses env variable $TWO_TASK as default to make database connection, and database connection string is not necessary.

$  echo $TWO_TASK
EBSDEV

Below two lines work fine:
$ sqlplus apps/appsPWD
SQL>

$ sqlplus apps/appsPWD@ebsdev
SQL>

If that variable is set to something else, SQL*Plus will give ORA-12154 error:

$ export TWO_TASK=dbdb
$ sqlplus apps/appsPWD
......
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Query to find long-run concurrent jobs

Use below SQL statement to find who is running concurrent request and how long it has been running.

SQL> SELECT fcr.request_id req_id,

to_char(fcr.actual_start_date,'mm/dd hh24:mi') start_time,

TRUNC(((sysdate - fcr.actual_start_date)/(1/24))*60) elap_in_Mins,

substr(fcq.concurrent_queue_name, 1, 20) queue,

substr(fcp.user_concurrent_program_name, 1, 45)  name,

substr(fu.user_name, 1, 9 ) u_name,

-- round((sysdate - actual_start_date) *24, 2) elap,

-- TRUNC(((sysdate - fcr.actual_start_date)/(1/24))*60) elap,

substr(decode( fcr.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', 'TERMINATED',

'U', 'DISABLED', 'W', 'PAUSED', 'X', 'TERMINATED', 'Z', 'WAITING',

'UNKNOWN'), 1, 10) status

FROM  applsys.fnd_concurrent_queues fcq,

applsys.fnd_concurrent_processes fcp, applsys.fnd_user fu,

apps.fnd_concurrent_programs_vl fcp,

applsys.fnd_concurrent_requests fcr

WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id

and fcp.queue_application_id = fcq.application_id

and fcr.controlling_manager = fcp.concurrent_process_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 round((sysdate -fcr.actual_start_date) *24, 2) >= 1

-- TRUNC(((sysdate - fcr.actual_start_date)/(1/24))*60) > 5

and fcr.phase_code = 'R'

ORDER BY round((sysdate -actual_start_date) *24, 2) DESC, fu.user_name,

fcr.request_id, to_char(fcr.actual_start_date,'dd-mon-yy hh24:mi:ss'),

fcp.user_concurrent_program_name

; 

References: 

Doc ID 152209.1 (STATUS_CODE and PHASE_CODE Columns of FND_CONCURRENT_REQUESTS Table)

Useful queries:

-  Find the OS process IDs of a concurrent job

SQL> SELECT b.sid, b.serial#, client_identifier, oracle_process_id DB_OS_PID, os_process_id EBS_PID

FROM fnd_concurrent_requests a , v$session b

WHERE a.ORACLE_SESSION_ID = b.AUDSID and

a.request_id=5510725;

- Check the performance trend on one concurrent program/job

SQL> SELECT

fcr.request_id request_id,

round(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) time_in_Minutes,

fcr.actual_start_date start_date,

status_code,

fu.user_name user_name,

fcpt.user_concurrent_program_name user_conc_prog,

fcr.argument_text parameters

FROM

fnd_concurrent_programs fcp,

fnd_concurrent_programs_tl fcpt,

fnd_concurrent_requests fcr,

fnd_user fu

WHERE 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.program_application_id = fcpt.application_id

and fcpt.language = USERENV('Lang')

and fcr.requested_by = fu.user_id and fcr.actual_start_date > sysdate - 30 -- within 30 days

and fcp.concurrent_program_name = 'FNDGSCST'        -- Program name: on statistics

-- and fcr.argument_text like 'ALL%'

-- and status_code != 'E'

ORDER BY start_date desc;

- Check if parameter has been changed in defining a concurrent job

SELECT

fcr.request_id request_id,fcr.argument_text,

TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) time_in_mins,

fcr.actual_start_date start_date,

fu.user_name user_name,

fcpt.user_concurrent_program_name user_conc_prog

FROM

fnd_concurrent_programs fcp,

fnd_concurrent_programs_tl fcpt,

fnd_concurrent_requests fcr,

fnd_user fu

WHERE 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.program_application_id = fcpt.application_id

and fcpt.language = USERENV('Lang')

and fcr.requested_by = fu.user_id and fcr.actual_start_date > sysdate - 21  -- within 21 days

and fcp.concurrent_program_name = 'CSTRBICR5G'            -- Program name: on BOM

ORDER BY start_date desc;