Database Initialization Parameters for Oracle 19c

 

Database Initialization Parameters for Oracle 19c

1 Required Parameters

The following list describes database initialization parameters required for this specific release of the Oracle Database 19c and is in addition to the common database initialization parameters which can be referred to in note 396009.1.

####################################################################
#
# Oracle E-Business Suite Release 12
# Release-Specific Database Initialization Parameters for 19c
#
####################################################################

compatible = 19.0.0 #MP
optimizer_adaptive_plans = TRUE #MP
optimizer_adaptive_statistics = FALSE #MP
pga_aggregate_limit = 0 #MP
temp_undo_enabled = FALSE
_pdb_name_case_sensitive = TRUE #MP
event='10946 trace name context forever, level 8454144' #MP
_optimizer_gather_stats_on_conventional_dml = FALSE #MP
_optimizer_use_stats_on_conventional_dml = FALSE #MP
optimizer_real_time_statistics = FALSE #MP


###############################################################################
#
# End of Release-Specific Database Initialization Parameters Section for 19c
#
###############################################################################

2 Parameter Removal List for Oracle Database 19c

If they exist, you should remove the following parameters from your database initialization parameters file for Oracle Database 19c.

Note: Parameters may appear on a "removal list" because they are obsolete; because the default value is required and no other value may be set; or to cater for certain special cases where a non-default value has to be set to meet specific needs (currently, there is only one such case, which is described in Section 6).

_kks_use_mutex_pin
_shared_pool_reserved_min_alloc
_sqlexec_progression_cost
exafusion_enabled
exclude_seed_cdb_view
global_context_pool_size
max_enabled_roles
o7_dictionary_accessibility
olap_page_pool_size
optimizer_adaptive_features
optimizer_features_enable
parallel_automatic_tuning
parallel_degree_level
parallel_io_cap_enabled
parallel_server
parallel_server_instances
plsql_compiler_flags
plsql_native_library_dir
plsql_native_library_subdir_count
plsql_optimize_level
standby_archive_dest
timed_statistics
use_indirect_data_buffers
utl_file_dir

 

DB LINKS

 The following script queries the DBA_DB_LINKS view to access link information:

COL OWNER FORMAT a10

COL USERNAME FORMAT A8 HEADING "USER"

COL DB_LINK FORMAT A30

COL HOST FORMAT A7 HEADING "SERVICE"

SELECT * FROM DBA_DB_LINKS

/

COL DB_LINK FORMAT A25

COL OWNER_ID FORMAT 99999 HEADING "OWNID"

COL LOGGED_ON FORMAT A5 HEADING "LOGON"

COL HETEROGENEOUS FORMAT A5 HEADING "HETER"

COL PROTOCOL FORMAT A8

COL OPEN_CURSORS FORMAT 999 HEADING "OPN_CUR"

COL IN_TRANSACTION FORMAT A3 HEADING "TXN"

COL UPDATE_SENT FORMAT A6 HEADING "UPDATE"

COL COMMIT_POINT_STRENGTH FORMAT 99999 HEADING "C_P_S"

SELECT * FROM V$DBLINK

/

Example 32-1 Determining the Host of Outgoing Database Links

This query results show the connect data, including the host name, for each outgoing

database link.

SELECT DB_LINK, DBMS_TNS.RESOLVE_TNSNAME(HOST) FROM DBA_DB_LINKS;

SELECT DB_NAME, HOST_NAME, FIRST_LOGON_TIME, LAST_LOGON_TIME

 FROM DBA_DB_LINK_SOURCES;

Performance Tunning

 SQL History:

All in one:

set lines 1000 pages 9999

COL instance_number FOR 9999 HEA 'Inst';

COL end_time HEA 'End Time';

COL plan_hash_value HEA 'Plan|Hash Value';

COL executions_total FOR 999,999 HEA 'Execs|Total';

COL rows_per_exec HEA 'Rows Per Exec';

COL et_secs_per_exec HEA 'Elap Secs|Per Exec';

COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';

COL io_secs_per_exec HEA 'IO Secs|Per Exec';

COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';

COL ap_secs_per_exec HEA 'App Secs|Per Exec';

COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';

COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';

COL ja_secs_per_exec HEA 'Java Secs|Per Exec';

SELECT 'gv$dba_hist_sqlstat' source,h.instance_number,

TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,

TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,

h.sql_id,

h.plan_hash_value,

h.executions_total,

TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,

TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,

TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,

TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,

TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,

TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,

TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,

TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,

TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec

FROM dba_hist_sqlstat h, dba_hist_snapshot s WHERE h.sql_id = '&sql_id'

AND h.executions_total > 0 AND s.snap_id = h.snap_id

AND s.dbid = h.dbid AND s.instance_number = h.instance_number

UNION ALL

SELECT 'gv$sqlarea_plan_hash' source,h.inst_id,

TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,

TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,

h.sql_id, h.plan_hash_value, h.executions,

TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec,

TO_CHAR(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') et_secs_per_exec,

TO_CHAR(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') cpu_secs_per_exec,

TO_CHAR(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') io_secs_per_exec,

TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cl_secs_per_exec,

TO_CHAR(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') ap_secs_per_exec,

TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cc_secs_per_exec,

TO_CHAR(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') pl_secs_per_exec,

TO_CHAR(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') ja_secs_per_exec

FROM gv$sqlarea_plan_hash h

WHERE h.sql_id = '&&sql_id'

AND h.executions > 0

order by source ;

AWR DATA:

set lines 1000 pages 9999

COL instance_number FOR 9999 HEA 'Inst';

COL end_time HEA 'End Time';

COL plan_hash_value HEA 'Plan|Hash Value';

COL executions_total FOR 999,999 HEA 'Execs|Total';

COL rows_per_exec HEA 'Rows Per Exec';

COL et_secs_per_exec HEA 'Elap Secs|Per Exec';

COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';

COL io_secs_per_exec HEA 'IO Secs|Per Exec';

COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';

COL ap_secs_per_exec HEA 'App Secs|Per Exec';

COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';

COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';

COL ja_secs_per_exec HEA 'Java Secs|Per Exec';

SELECT h.instance_number,

TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,

TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,

h.sql_id, h.plan_hash_value, h.executions_total,

TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,

TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,

TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,

TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,

TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,

TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,

TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,

TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,

TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec

FROM dba_hist_sqlstat h,

dba_hist_snapshot s

WHERE h.sql_id = '&sql_id'

AND h.executions_total > 0

AND s.snap_id = h.snap_id

AND s.dbid = h.dbid

AND s.instance_number = h.instance_number

ORDER BY

s.begin_interval_time,

s.end_interval_time;

AWR-LIO:

col execs for 999,999,999

col avg_etime for 999,999

col avg_lio for 999,999,999

col avg_pio for 999,999,999

col begin_interval_time for a30

col node for 99999

break on plan_hash_value on startup_time skip 1

select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,

nvl(executions_delta,0) execs,

(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,

(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,

ROUND(disk_reads_delta/DECODE(executions_delta,0,1, executions_delta),1) avg_pio,

ROUND(rows_processed_delta/DECODE(executions_delta,0, 1, executions_delta), 1) avg_rows,

round(px_servers_execs_delta/decode(executions_delta,0,1, executions_delta), 1) avg_px

from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

where sql_id = nvl('&sql_id','4dqs2k5tynk61')

and ss.snap_id = S.snap_id

and ss.instance_number = S.instance_number

and executions_delta > 0

order by 1, 2, 3

/

To find which is using Current memory:

set linesize 999

col avg_et_secs justify right format 9999999.99

col cost justify right format 9999999999

col timestamp justify center format a25

col parsing_schema_name justify center format a30

col inst_id format 999999999

alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

select 'gv$sqlarea_plan_hash' source, INST_ID, SQL_ID, PLAN_HASH_VALUE,

round(elapsed_time/decode(nvl(executions,0),0,1,executions)/1e6/

decode(px_servers_executions,0,1,px_servers_executions)/decode(nvl(executions,0),0,1,executions),2) avg_et_secs,

px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_px,

optimizer_cost cost, LAST_LOAD_TIME timestamp, parsing_schema_name --FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME, SQL_PROFILE

from gv$sqlarea_plan_hash

where sql_id = nvl(trim('&sql_id'),sql_id)

UNION

SELECT 'dba_hist_sql_plan' source, null INST_ID, t1.sql_id sql_id, t1.plan_hash_value plan_hash_value, t2.avg_et_secs avg_et_secs, t2.avg_px, t1.cost cost, t1.timestamp timestamp, NULL parsing_schema_name

FROM dba_hist_sql_plan t1,

(

SELECT sql_id, plan_hash_value, --round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6,2) avg_et_secs

round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6/

decode(SUM(px_servers_execs_total),0,1,SUM(px_servers_execs_total))/decode(SUM(executions_total),0,1,SUM(executions_total)),2) avg_et_secs,

SUM(px_servers_execs_total)/decode(SUM(executions_total),0,1,SUM(executions_total)) avg_px

FROM dba_hist_sqlstat

WHERE

executions_total > 0

GROUP BY sql_id, plan_hash_value

) t2

WHERE

t1.sql_id = nvl(TRIM('&sql_id'), t1.sql_id)

AND t1.depth = 0

AND t1.sql_id = t2.sql_id(+)

AND t1.plan_hash_value = t2.plan_hash_value(+)

order by avg_et_secs, cost

/

Identify the Culprit: SQL , SQL TEXT , BIND VARIABLE

col exact_matching_signature for 99999999999999999999999999

col sql_text for a50

select sql_id, exact_matching_signature, SQL_TEXT from v$sqlarea where UPPER(sql_text) like '%DUMMY%' order by UPPER(sql_text);

set long 20000

set lines 750 pages 9999

select sql_text from dba_hist_sqltext where sql_id = '&SQL_ID';

set long 20000

set lines 750 pages 9999

select sql_text from gv$sqlarea where sql_id = '&SQL_ID';

col VALUE_STRING for a50

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING FROM gv$sql_bind_capture WHERE sql_id='&sql_id';

statistics of objects of a specific sql id:

set lines 300 set pages 300

col table_name for a40

col owner for a30

select distinct owner, table_name, STALE_STATS, last_analyzed, stattype_locked

from dba_tab_statistics

where (owner, table_name) in

(select distinct owner, table_name

from dba_tables

where ( table_name)

in ( select object_name

from gv$sql_plan

where upper(sql_id) = upper('&sql_id') and object_name is not null))

--and STALE_STATS='YES'

/

col index_name for a50

SELECT owner, index_name, table_name,last_analyzed, sample_size, num_rows, partitioned, global_stats

FROM dba_indexes

WHERE index_name IN (

select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')

from (

SELECT plan_table_output

FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', null, 'BASIC'))

UNION ALL

SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null, 'ALL'))

)

where plan_table_output like '%INDEX%'

)

ORDER BY owner, table_name, index_name

/

Sql id current rows, I/O , read/wrire details:

select SQL_ID,PLAN_HASH_VALUE,ELAPSED_TIME,PHYSICAL_READ_BYTES,PHYSICAL_WRITE_BYTES,DISK_READS,DIRECT_WRITES,BUFFER_GETS,ROWS_PROCESSED from v$sqlstats where sql_id='&sql_id';

select sql_text,rows_processed from v$sql

where USERS_EXECUTING>0;

select s.sid, s.serial#, p.spid, s.username, s.program,

t.xidusn, t.used_ublk, t.used_urec, sa.sql_text from

v$process p,v$session s, v$sqlarea sa, v$transaction t

where s.paddr=p.addr

and s.taddr=t.addr

and s.sql_address=sa.address(+)

and s.sql_hash_value=sa.hash_value(+)

order by s.sid

/

exec dbms_stats.flush_database_monitoring_info;

select inserts,updates,deletes from user_tab_modifications where table_name = '&table_name';

Check the progress of DML statements:

col sql_text for a60

SELECT rows_processed "Total Rows Processed",

ROUND((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60, 1) "Total Time (Min)",

TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) "Rows/Min",

TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60)) "Rows/Sec" , SUBSTR(sql_text, 1, 60) sql_text

FROM gv$sqlarea

WHERE SQL_ID='&SQL_ID'

AND open_versions > 0

AND rows_processed > 0;

Detailed history of SQL_ID:

select instance_number inst_id,SESSION_ID,USER_ID,PROGRAM,sql_id,SQL_CHILD_NUMBER,sql_plan_hash_value,to_char (sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start from

dba_hist_active_sess_history where

sql_id='&sql_id';

select sql_id,

starting_time,

end_time,

(EXTRACT(HOUR FROM run_time) * 3600

+ EXTRACT(MINUTE FROM run_time) * 60

+ EXTRACT(SECOND FROM run_time))/60 run_time_MIN,

READ_IO_BYTES,

PGA_ALLOCATED PGA_ALLOCATED_BYTES,

TEMP_ALLOCATED TEMP_ALLOCATED_BYTES

from (

select

sql_id,

max(sample_time - sql_exec_start) run_time,

max(sample_time) end_time,

sql_exec_start starting_time,

sum(DELTA_READ_IO_BYTES) READ_IO_BYTES,

sum(DELTA_PGA) PGA_ALLOCATED,

sum(DELTA_TEMP) TEMP_ALLOCATED

from

(

select sql_id,

sample_time,

sql_exec_start,

DELTA_READ_IO_BYTES,

sql_exec_id,

greatest(PGA_ALLOCATED - first_value(PGA_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_PGA,

greatest(TEMP_SPACE_ALLOCATED - first_value(TEMP_SPACE_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_TEMP

from

dba_hist_active_sess_history

where

sample_time >= trunc(sysdate)

--and sample_time < to_date ('2013/04/16 03:10:00','YYYY/MM/DD HH24:MI:SS')

and sql_exec_start is not null

and IS_SQLID_CURRENT='Y'

)

group by sql_id,SQL_EXEC_ID,sql_exec_start

order by sql_id

)

where sql_id = '&sql_id'

order by sql_id, run_time_MIN desc;

To find Sql Hanging or not:

select sess_io.inst_id,

sess_io.sid,

sesion.sql_id,

sess_io.block_gets,

sess_io.consistent_gets,

sess_io.physical_reads,

sess_io.block_changes,

sess_io.consistent_changes

from gv$sess_io sess_io, gv$session sesion

where sesion.sid = sess_io.sid and

sess_io.inst_id = sesion.inst_id and

sesion.sql_id='&sql_id'

and sesion.username is not null ;

select sess_io.inst_id,

sess_io.sid,

sess_io.block_gets,

sess_io.consistent_gets,

sess_io.physical_reads,

sess_io.block_changes,

sess_io.consistent_changes

from gv$sess_io sess_io, gv$session sesion

where sesion.sid = sess_io.sid and

sess_io.inst_id = sesion.inst_id

and sesion.username is not null ;

SQL to show the full SQL executing for active sessions:

select sesion.sid,sql_text from v$sqltext sqltext, v$session sesion where sesion.sql_hash_value = sqltext.hash_value and sesion.sql_address = sqltext.address and sesion.username is not null

order by sqltext.piece;

select a.sid,b.sql_fulltext from V$Session a, V$SQLAREA b where a.sql_id=b.sql_id and a.status='ACTIVE';

nn

 SELECT 

SQL_ID,pian_hash_value,SQL_PROFILE,SQL_PLAN_BASELINE,ROUND(SUM(etapsed tirne)/SUM(exec utions)/1e6, 3) 

avg_et_secs,ROUN D(SUM(cpu_time)/SUM(executions)/1e6, 

3 avg_cpu_secs,ROUNDiSUM(user_io_wa it_time)/SU M (exec uti o ns)/1 e6, 3) 

avg_io_secs, ROUND(SUM (bu ffer_g et s)/SU M (executions)) avg_buffer gets,

ROUND(SUM (disk_reads)/SUM (executions)) avg_disk reads,ROUND(SUM(rows_processed)/SUM(executions))

 avg_rows,SUM(executions) executions,Substr(sql_text1,100)

 "SQL Text"FROM gvSsqIWHERE executions > O 

 GROUP BY sql_id,plan_hash value,sql_text,SQL_PROFILE,SQL_PLAN_BASELINE ORDER 

BY 1;

Post Cutover R12.2 - Clear the old editions script

SQL> 

select

 '/* '||e.object_name||' '||s.osuser||' '||s.username||' '||s.program||' */' info

 , 'ALTER SYSTEM KILL SESSION '||''''||s.sid||','||s.serial#||''';' kill

 from

 v$session s

 , v$process p

 , database_properties run /* run edition name */

 , dba_objects_ae e

 where s.type <> 'BACKGROUND'

 and p.addr = s.paddr

 and run.property_name = 'DEFAULT_EDITION'

 and e.object_id = s.session_edition_id

 and e.object_name < run.property_value

 order by 1

 /

EBS After adop CUTOVER oaea_wls.properties file was blank in application Intranet node2

 

EBS After adop CUTOVER oaea_wls.properties file was blank in application Intranet node2

 

 

To implement this workaround, please do the following:

Notes: Test the following steps in a development instance and then migrate accordingly:

1. Ensure a valid backup exists for the instance where the solution is being testing.

2. Copy file $INST_TOP/appl/admin/oaea_wls.properties from the original filesystem (fs1) over to the additional filesystems (fs2), to migrate the proper node settings to oaea_wls.properties

3. Restart the servers to ensure the file changes are processed.

4. Retest the login and confirm the error is resolved.

Reference:

12.2 AccessGate Login Fails with HTTP 500 Error 'Request Failed for : /accessgate/ssologin?, Resp Code : [500]' On Secondary Nodes After A Complete ADOP Patching Cycle As File oaea_wls.properties Is Not Updated On Multiple Filesystems (fs2) ( Doc ID 2273885.1 )