SGA_MIN_SIZE in Oracle Database 12.2

I did summarize all new init.ora/spfile parameters in Oracle Database 12.2.0.1 a while ago:

New SPFILE parameters in Oracle Database 12.2.0.1

And one caught Roy’s and my attention: SGA_MIN_SIZE.

When we present this new parameter as part of the Multitenant slides in our workshops, some people ask themselves:
“What happens if I oversubscribe such a parameter?” For example, if SGA_TARGET=4G and I have 10 PDBs and define in each of them SGA_MIN_SIZE=500M. Will the 9th and 10th PDB not startup anymore? Or will the 7th PDB already in trouble as the CDB$ROOT will need some SGA portion as well?

None of this is true.

If the sum of SGA_MIN_SIZE across all PDBs is larger than 50% of the CDB’s setting for SGA_TARGET, then SGA_MIN_SIZE will automatically scaled down to meet the criteria of not adding up across all PDBs to more than 50% of SGA_TARGET of the root.

If you scale down SGA_MIN_SIZE too small for a given PDB, you may see an error warning.

The documentation says:

  • You must set the SGA_MIN_SIZE value to a value that meets these requirements:
    • In a PDB, to a value that is less than or equal to 50% of the value of SGA_TARGET in the PDB
    • In a PDB, to a value that is less than or equal to 50% of the value of SGA_TARGET at the CDB level
    • Across all the PDBs in a CDB, the sum of SGA_MIN_SIZE values must be less than or equal to 50% of the SGA_TARGET value at the CDB level.

    When you set SGA_MIN_SIZE in a PDB to a value that does not meet these requirements, you receive an error. If these requirements are violated after the PDB’s parameter is set (for example, if the SGA_MIN_SIZE value is changed at the CDB level, Oracle will adjust the PDB’s value to meet these requirements.

As you see, you won’t receive always an error. But in such a case where my SGA_TARGET in the CDB$ROOT is only 1.5GB, I will get the following error when I try to massively oversubscribe SGA_MIN_SIZE in a PDB:

SQL> alter session set container=pdb1;
Session altered.

SQL> alter system set sga_target=1G;
System SET altered.

SQL> alter system set sga_min_size=800M;

Error starting at line : 1 in command -
alter system set sga_min_size=800M
Error report -
ORA-32017: failure in updating SPFILE
ORA-56746: invalid value 838860800 for parameter sga_min_size; must be smaller
than 50% of parameter sga_target
32017. 00000 -  "failure in updating SPFILE"
*Cause:    A failure occured while updating the SPFILE.
*Action:   See associated errors.

SQL> alter system set sga_min_size=500M;
System SET altered.

First learning: SGA_MIN_SIZE gets treated relatively to the SGA_TARGET setting in the PDB.

Now lets try the same exercise in my second PDB:

SQL> alter session set container=pdb2;
Session altered.

SQL> alter system set sga_target=1G;
System SET altered.

SQL> alter system set sga_min_size=500M;
System SET altered.

Hm … interesting … let’s check in the CDB$ROOT:

SQL> alter session set container=cdb$root;
Session altered.

SQL> show sga
Total System Global Area   1577058304 bytes      
Fixed Size                    8793208 bytes      
Variable Size               385876872 bytes      
Database Buffers           1174405120 bytes      
Redo Buffers                  7983104 bytes    
  
SQL> show parameter sga_
NAME                         TYPE        VALUE   
---------------------------- ----------- ------- 
sga_max_size                 big integer 1504M   
sga_min_size                 big integer 0       
sga_target                   big integer 1504M   

It becomes a bit more clear that the values get adjusted internally (secretly) when you compare the values across PDBs:

SQL> select con_id, name, value from v$system_parameter where name like 'sga%' order by con_id;

    CON_ID NAME           VALUE               
---------- -------------- --------------------
         0 sga_min_size   0                   
         0 sga_max_size   1577058304          
         0 sga_target     1577058304          
         2 sga_target     0                   
         3 sga_target     1073741824          
         3 sga_min_size   524288000           
         4 sga_target     1073741824          
         4 sga_min_size   524288000           

8 rows selected. 

So finally, let’s monitor the actual SGA spread and consumption per PDB (thanks to Tim Hall for his queries):

SET LINESIZE 150
COLUMN pdb_name FORMAT A10

SQL> SELECT r.con_id,
            c.pdb_name,
            round(r.sga_bytes/(1024*1024),1) "SGA in MB",
            round(r.buffer_cache_bytes/(1024*1024),1) "BCh in MB",
            round(r.shared_pool_bytes/(1024*1024),1) "ShP in MB"
     FROM   v$rsrcpdbmetric r,
            cdb_pdbs c
     WHERE  r.con_id = c.con_id
     ORDER BY r.con_id;

    CON_ID PDB_NAME    SGA in MB  BCh in MB  ShP in MB
---------- ---------- ---------- ---------- ----------
         3 PDB2             37.3       25.2       12.1
         4 PDB1             38.9       25.5       13.4

–Mike

PGA_AGGREGATE_LIMIT enforces default since Oracle Database 12.2.0.1

The init.ora/spfile parameter PGA_AGGREGATE_LIMIT got introduced in Oracle Database 12.1.0.1.

As per documentation in Oracle Database 12.1 it got defined as:

PGA_AGGREGATE_LIMIT specifies a limit on the aggregate PGA memory consumed by the instance.“.

Furthermore the algorithm for its setting got described as:

By default, PGA_AGGREGATE_LIMIT is set to the greater of 2 GB, 200% of PGA_AGGREGATE_TARGET, and 3 MB times the PROCESSES parameter. It will be set below 200% of PGA_AGGREGATE_TARGET if it is larger than 90% of the physical memory size minus the total SGA size, but not below 100% of PGA_AGGREGATE_TARGET.”

Default Value Change in Oracle Database 12.2.0.1

In Oracle Database 12.2.0.1 the default value gets adjusted a bit as it turned out that restricting PGA_AGGREGATE_LIMIT to a value too low will lead to a significant number of issues.

See the Oracle Database 12.2.0.1 documentation on PGA_AGGREGATE_LIMIT explaining:

“If MEMORY_TARGET is set, then PGA_AGGREGATE_LIMIT defaults to the MEMORY_MAX_TARGET value.
If MEMORY_TARGET is not set, then PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET.
If MEMORY_TARGET is not set, and PGA_AGGREGATE_TARGET is explicitly set to 0, then the value of PGA_AGGREGATE_LIMIT is set to 90% of the physical memory size minus the total SGA size.
In all cases, the default PGA_AGGREGATE_LIMIT is at least 2GB and at least 3MB times the PROCESSES parameter.”

And please remember, DO NOT use MEMORY_TARGET as it will lead to issues such as no use of huge pages etc.

What happened in Oracle Database 12.1.0.2 when you set PGA_AGGREGATE_LIMIT too low?

How much is too low? Too low means it has been set lower that 2x the value of PGA_AGGREGATE_TARGET. And then it got adjusted “silently”, i.e. the value of PGA_AGGREGATE_LIMIT got adjusted internally after startup to reflect the minimum. Unfortunately this adjusted value does not get displayed by “show parameter”.

Short example in Oracle Database 12.1.0.2:

SQL> show parameter pga 

NAME			TYPE                      VALUE
----------------------- ------------------------- -----------------------------
pga_aggregate_limit     big integer               2G
pga_aggregate_target    big integer               120M
SQL> alter system set pga_aggregate_limit=1G scope=spfile;
System altered.

SQL> alter system set pga_aggregate_target=1G scope=spfile;
System altered.

After restarting the database:

SQL> show parameter pga

NAME				     TYPE	  VALUE
------------------------------------ ------------ ------------------------------
pga_aggregate_limit		     big integer  1G
pga_aggregate_target		     big integer  1G
Different behavior in Oracle Database 12.2.0.1

Repeating the same test in Oracle Database 12.2.0.1 leads to ORA-93:

SQL> show parameter pga 

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit		     big integer 2G
pga_aggregate_target		     big integer 120M
SQL> alter system set pga_aggregate_limit=1G scope=spfile;
System altered.

SQL> alter system set pga_aggregate_target=1G scope=spfile;
System altered.

SQL> create pfile from spfile;
File created.

SQL> startup force
ORA-00093: pga_aggregate_limit must be between 2048M and 100000G
ORA-01078: failure in processing system parameters

After increasing PGA_AGGREGATE_LIMIT=2G I can startup my database again. PGA_AGGREGATE_LIMIT gets enforced as at least 2x the value of PGA_AGGREGATE_TARGET since Oracle Database 12.2.0.1.

–Mike

New SPFILE parameters in Oracle Database 12.1.0.2 with July 2016 (and newer) PSU/BP

New Parameters in Oracle Database 12.1.0.2 with July 2016 PSU/BP

By following an internal discussion and checking parameter changes between Patch Set Updates (PSU) and Proactive Bundle Patches (BP) I learned that we introduced two new SPFILE parameters in Oracle Database 12.1.0.2 with the July PSU and BP. One is documented in the patch readme, the other one can be found right now only in the Oracle Database 12.2.0.1 manual:

The Oracle 12.2 documentation about ALLOW_GROUP_ACCESS_TO_SGA, the parameter which appears not in the Oracle 12.1 documentation right now, says:

ALLOW_GROUP_ACCESS_TO_SGA controls group access to shared memory on UNIX platforms.

The default value is FALSE, which means that database shared memory is created with owner access only. In Oracle Database releases prior to Oracle Database 12c Release 2 (12.2.0.1), database shared memory was created with owner and group access.

When this parameter is set to TRUE, database shared memory is created with owner and group access. This behavior grants permissions to DBAs to manage shared memory outside the database, but also allows DBAs to read and write to shared memory, which may not be desirable for certain installations.

So there’s a tiny correction required:
It should say “prior to Oracle Database 12.1.0.2 July 2016 PSU/BP”.

The ENCRYPT_NEW_TABLESPACES parameter came in for the cloud deployments and is documented in the description
of the July Proactive BP
:

12.1.0.2.DBBP:160719 21281607E Transparently encrypt tablespace at creation in Cloud (adds “encrypt_new_tablespaces”)

 

Why does ALLOW_GROUP_ACCESS_TO_SGA appear in Oracle Database 12.1.0.2?

Simple reasons: it will make Oracle Database 12.1.0.2 more secure. Default for this parameter is FALSE – which actually changes behavior but may not affect you at first sight. And that’s why I blog about it.

You will recognize that the Oracle executable runs now with permission “600” – whereas it was “640” before. See my example of an 12.1.0.2 database with the January 2017 BP in place:

$ ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x00000000 23298063   oracle     600        2932736    76                      
0x00000000 23330832   oracle     600        1040187392 38                      
0x00000000 23363601   oracle     600        5455872    38                      
0xc8969114 23396370   oracle     600        20480      38       
...

whereas my 11.2.0.4 database runs with different permissions:

$ ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status     
0x00000000 22872084   oracle     640        12582912   21                      
0x00000000 22904853   oracle     640        721420288  21                      
0xd41b1c5c 22937622   oracle     640        2097152    21 
...

Does this effect the connection of your applications to the database?
No, of course not.

It has only an effect if you try to access the SGA from the OS level, i.e. attaching to the shared memory segment. In the old behavior an OS user being in the same group can attach and read from the SGA. With the new “600” protection only the OWNER can attach to it – and read out the SGA.

This is the standard behavior in Oracle Database 12.2.0.1 and onward. And it has been backported to the July 2016 PSU and Proactive Bundle Patches which are cumulative, i.e it is in all following PSUs and BPs included as well.

–Mike

Default Changes SPFILE Parameters – Oracle 12.2

This is the 4th posting in my series about init.ora/SPFILE parameters in Oracle Database 12.2.0.1.

Roy and I did a comparison between default parameter settings in Oracle Database 11.2.0.4 vs Oracle Database 12.1.0.2 vs Oracle Database 12.2.0.1. And some changes are quite interesting – of course the memory driven parameters are left out in this list.

Color RED marks a change between releases.
The databases were all on OL (Oracle Linux) 6.8 and located on file system, not in ASM – therefore certain values may differ when the database is located in ASM and/or on a different OS port.

Parameter Oracle 11.2.0.4 Oracle 12.1.0.2 Oracle. 12.2.0.1
audit_sys_operations FALSE TRUE TRUE
compatible 11.2.0.4 12.1.0.2.0 12.2.0
control_file_record_keep_time 7 7 30
db_securefile PERMITTED PREFERRED PREFERRED
dml_locks 616 1416 2076
filesystemio_options NONE NONE setall
job_queue_processes 1000 1000 4000
object_cache_optimal_size 102400 102400 10240000
optimizer_features_enable 11.2.0.4 12.1.0.2 12.2.0.1
parallel_max_servers 48 80 80
parallel_min_servers 0 8 8
parallel_servers_target 64 32 32
parallel_adaptive_multi_user TRUE TRUE FALSE
pre_page_sga FALSE TRUE TRUE
resource_limit FALSE TRUE TRUE
sec_max_failed_login_attempts 10 3 4
sec_protocol_error_trace_action CONTINUE TRACE LOG
spatial_vector_acceleration FALSE FALSE TRUE
sql92_security FALSE FALSE TRUE

–Mike

Enabling ADAPTIVE Features of Oracle 12.2 in 12.1

Oracle Database 12.2 introduces the new split-up adaptive parameters, OPTIMIZER_ADAPTIVE_PLANS and OPTIMITER_ADAPTIVE_STATISTICS.

For more information please see:

But Oracle Database 12.2 on-premises is not out yet – so what
should you do when upgrading to Oracle Database 12.1 – or struggling
with some of the “adaptive” features in Oracle 12.1?

It recommends to adopt the Oracle Database 12.2 defaults when
upgrading to Oracle Database 12.1. This can be achieved by installing
two patches – we call it the recommended approach.

    • The patch for bug# 22652097 introduces the two parameters OPTIMIZER_ADAPTIVE_PLANS and OPTIMIZER_ADAPTIVE_STATISTICS, and in addition removes the parameter OPTIMIZER_ADAPTIVE_FEATURES.
    • The patch for bug# 21171382 disables the automatic creation of extended statistics unless the optimizer preference AUTO_STATS_EXTENSIONS is set to ON.

Please make sure you’ll remove OPTIMIZER_ADAPTIVE_FEATURES from your spfile:

alter system reset optimizer_adaptive_features;

when applying the patches.

Both patches should help as well once you have upgraded already to Oracle Database 12.1 but encountering performance issues.

Please note that it’s not necessary to set OPTIMIZER_DYNAMIC_SAMPLING to a
non-default value because the patches will disable the use of adaptive dynamic sampling
to match the default behavior in Oracle Database 12.2 when both new parameters are used in their default settings.

Further Information:

–Mike

OPTIMIZER_ADAPTIVE_FEATURES obsolete in Oracle 12.2

The Oracle Database 12.1 parameter OPTIMIZER_ADAPTIVE_FEATURES has been made OBSOLETE (i.e. must be removed from the SPFILE when upgrading) in Oracle Database 12.2.

It gets replaced with two parameters of whom one is enabled, the other one is disabled by default:

  • OPTIMIZER_ADAPTIVE_PLANS=TRUE by default
  • OPTIMIZER_ADAPTIVE_STATISTICS=FALSE by default

Nigel Bayliss, our “Optimizer” Product Manager has blogged about it already with way more detailed insights.

But as Oracle Database 12.2 on-premises is not available how should you deal with this feature in Oracle Database 12.1?

Enabling ADAPTIVE Features of Oracle 12.2 in 12.1

 

 

–Mike
.

SPFILE Parameter: max_pdbs – a must for Single Tenant

Sometimes my job has a aspect making me smile at the end of the day 😉

I sat together with Johannes Ahrends during a talk at the OUGN Conference on the boat from Oslo towards Kiel. And we were discussing afterwards why there’s no official way to limit the number of PDBs which will be essential for customers wanting to go the Single Tenant track. I had my Hands-On environment up and we played a bit in the break recognizing that a constraint on CONTAINER$ won’t be the correct solution as unplug/plug operations leave leftovers in it unless you DROP PLUGGABLE DATABASE. And even if you drop the remains the constraint solution does not work.

At the breakfast the next morning Johannes mentioned a trigger – and he published one soon after. But it is not a good idea in terms of keeping support for your database when you fiddle around with the data dictionary.

So I did ask my contacts internally and received a message saying clearly
Somebody doesn’t want this.“. 🙂
Well, I work long enough for Oracle to know how to read it. It’s a common thing to blame it on “Somebody” when you don’t want to discuss things further. One can hide perfectly well behind “Somebody“.

The higher my surprise was when I started testing and playing with Oracle Database 12.2 – and collecting init.ora parameters between release labels to detect changes and additions. And apparently, this one here appeared:

as a new parameter in Oracle Database 12.2 saying “max number of pdbs allowed in CDB or Application ROOT” in its parameter description.
I was (a) surprised and (b) happy and (c) had to try it out immediately in my environment where I had already 3 PDBs (the PDB$SEED does not count):

SQL> alter system set max_pdbs=3;
System altered.

SQL> show pdbs

CON_ID CON_NAME               OPEN MODE  RESTRICTED
------ ---------------------- ---------- ----------
2      PDB$SEED               READ ONLY  NO
3      PDB1                   READ WRITE NO
4      PDB2                   READ WRITE NO
5      CLONE_PDB              MOUNTED

SQL> alter system set max_pdbs=2;
alter system set max_pdbs=2
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-65331: DDL on a data link table is outside an application action.
.

So even though the error message is a bit rough (at least in my release drop) the parameter does what we want. In a single tenant environment you’ll set it to “1” and prevent the creation or plugin of a 2nd PDB in this container database..

Another test with a fresh container database:

SQL> show pdbs

CON_ID CON_NAME      OPEN MODE  RESTRICTED
------ ------------- ---------- ----------
2      PDB$SEED      READ ONLY  NO


SQL> alter system set max_pdbs=1;
System altered.

SQL> show parameter max_pdbs

NAME                     TYPE     VALUE
------------------------ -------- ----- 
max_pdbs                 integer      1


SQL> create pluggable database pdb1 admin user adm identified by adm file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb1');
Pluggable database created.

SQL> create pluggable database pdb2 admin user adm identified by adm file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb2');

create pluggable database pdb2 admin user adm identified by adm file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb2')
*
ERROR at line 1:
ORA-65010: maximum number of pluggable databases created

SQL> drop pluggable database pdb1 including datafiles;
Pluggable database dropped.

SQL> create pluggable database pdb2 admin user adm identified by adm file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb2');
Pluggable database created.

Looks like a solid solution to me.

But please see also this blog post by Oracle ACE Director Franck Pachot about an issue with the parameter:
http://blog.dbi-services.com/oracle-12cr2-max_pdbs/

–Mike

Deprecated Parameters in Oracle Database 12.2.0.1

This is the 3rd posting in my series about init.ora/SPFILE parameters in Oracle Database 12.2.0.1.

 

Finally, here’s the list of DEPRECATED parameters in Oracle Database 12.2.0.1:

O7_DICTIONARY_ACCESSIBILITY
active_instance_count
asm_preferred_read_failure_groups
background_dump_dest
buffer_pool_keep
buffer_pool_recycle
commit_write
cursor_space_for_time
db_block_buffers
fast_start_io_target
instance_groups
lock_name_space
log_archive_start
parallel_adaptive_multi_user
plsql_debug
plsql_v2_compatibility
rdbms_server_dn
remote_os_authent
resource_manager_cpu_allocation
sec_case_sensitive_logon
serial_reuse
sql_trace
standby_archive_dest
unified_audit_sga_queue_size
user_dump_dest
utl_file_dir

Only the ones in BOLD were newly marked as DEPRECATED in Oracle Database 12.2.0.1. The non-bold ones had been deprecated in earlier releases already.
Please see also the column ISDEPRECATED in V$PARAMETER.

–Mike

Obsolete SPFILE Parameters in Oracle Database 12.2.0.1

This is the 2nd posting in my series about init.ora/SPFILE parameters in Oracle Database 12.2.0.1.

Find the list of the 159 obsoleted parameters here (and of course in V$OBSOLETE_PARAMETERS):

_app_ctx_vers
_average_dirties_half_life
_aw_row_source_enabled
_compatible_no_recovery
_data_transfer_cache_size
_db_no_mount_lock
_dlm_send_timeout
_dtree_bintest_id
_dtree_compressbmp_enabled
_evolve_plan_baseline_report_level
_fast_start_instance_recovery_target
_fic_max_length
_fic_outofmem_candidates
_idl_conventional_index_maintenance
_kgl_latch_count
_kks_free_cursor_stat_pct
_kspptbl_mem_usage
_lm_direct_sends
_lm_multiple_receivers
_lm_rcv_buffer_size
_lm_statistics
_log_archive_buffer_size
_log_io_size
_max_log_write_io_parallelism
_module_action_old_length
_optimizer_adaptive_plans
_optimizer_choose_permutation
_oracle_trace_events
_oracle_trace_facility_version
_plan_verify_local_time_limit
_plsql_conditional_compilation
_px_async_getgranule
_px_slaves_share_cursors
_seq_process_cache_const
_spr_use_hash_table
_sqlexec_progression_cost
_use_hidden_partitions
_very_large_partitioned_table
allow_partial_sn_results
always_anti_join
always_semi_join
arch_io_slaves
b_tree_bitmap_plans
backup_disk_io_slaves
cache_size_threshold
cell_partition_large_extents
cleanup_rollback_entries
close_cached_open_cursors
complex_view_merging
db_block_checkpoint_batch
db_block_lru_extended_statistics
db_block_lru_latches
db_block_lru_statistics
db_block_max_dirty_target
db_file_simultaneous_writes
dblink_encrypt_login
ddl_wait_for_locks
delayed_logging_block_cleanouts
discrete_transactions_enabled
distributed_recovery_connection_hold_time
distributed_transactions
drs_start
enqueue_resources
exclude_seed_cdb_view
fast_full_scan_enabled
freeze_DB_for_fast_instance_recovery
gc_defer_time
gc_files_to_locks
gc_latches
gc_lck_procs
gc_releasable_locks
gc_rollback_locks
hash_join_enabled
hash_multiblock_io_count
instance_nodeset
job_queue_interval
job_queue_keep_connections
large_pool_min_alloc
lgwr_io_slaves
lm_locks
lm_procs
lm_procs
lm_ress
lock_sga_areas
log_block_checksum
log_files
log_parallelism
log_simultaneous_copies
log_small_entry_max_size
logmnr_max_persistent_sessions
max_commit_propagation_delay
max_rollback_segments
max_transaction_branches
mts_circuits
mts_dispatchers
mts_listener_address
mts_max_dispatchers
mts_max_servers
mts_multiple_listeners
mts_servers
mts_service
mts_sessions
ogms_home
ops_admin_group
ops_interconnects
optimizer_adaptive_features
optimizer_max_permutations
optimizer_percent_parallel
optimizer_search_limit
oracle_trace_collection_name
oracle_trace_collection_path
oracle_trace_collection_size
oracle_trace_enable
oracle_trace_facility_name
oracle_trace_facility_path
parallel_automatic_tuning
parallel_broadcast_enabled
parallel_default_max_instances
parallel_degree_level
parallel_io_cap_enabled
parallel_min_message_pool
parallel_server
parallel_server_idle_time
parallel_server_instances
parallel_transaction_resource_timeout
partition_view_enabled
plsql_compiler_flags
plsql_native_c_compiler
plsql_native_library_dir
plsql_native_library_subdir_count
plsql_native_linker
plsql_native_make_file_name
plsql_native_make_utility
push_join_predicate
remote_archive_enable
row_cache_cursors
row_locking
sequence_cache_entries
sequence_cache_hash_buckets
serializable
shared_pool_reserved_min_alloc
snapshot_refresh_interval
snapshot_refresh_keep_connections
snapshot_refresh_processes  
sort_direct_writes
sort_multiblock_read_count
sort_read_fac
sort_spacemap_size
sort_write_buffer_size
sort_write_buffers
spin_count
sql_version
standby_preserves_names
temporary_table_locks
text_enable
transaction_auditing
undo_suppress_errors
use_indirect_data_buffers
use_ism

–Mike

New SPFILE parameters in Oracle Database 12.2.0.1

This is the 1st posting in my series about init.ora/SPFILE parameters in Oracle Database 12.2.0.1.

Oracle Database 12.2.0.1 is available now in the Oracle Cloud.

And this is the list of 46 new init.ora/spfile parameters compared to Oracle Database 12.1.0.2 – including the links (where possible) to the Oracle Database 12.2 Reference documentation.

 

Parameter

Description

allow_global_dblinks LDAP lookup for DBLINKS
allow_group_access_to_sga Allow read access for SGA to users of Oracle owner group
approx_for_aggregation Replace exact aggregation with approximate aggregation
approx_for_count_distinct Replace count distinct with approx_count_distinct
approx_for_percentile Replace percentile_* with approx_percentile
asm_io_processes number of I/O processes per domain in the ASM IOSERVER
instance
autotask_max_active_pdbs Setting for Autotask Maximum Maintenance PDBs
awr_pdb_autoflush_enabled Enable/Disable AWR automatic PDB flushing
cdb_cluster [undocumented if TRUE startup in CDB Cluster mode
cdb_cluster_name [undocumented] CDB Cluster name
clonedb_dir CloneDB Directory
containers_parallel_degree Parallel degree for a CONTAINERS() query
cursor_invalidation default for DDL cursor invalidation semantics
data_guard_sync_latency Data Guard SYNC latency
data_transfer_cache_size Size of data transfer cache
default_sharing Default sharing clause
disable_pdb_feature [undocumented] Disable features
enable_automatic_maintenance_pdb Enable/Disable Automated Maintenance for Non-Root PDB
enable_dnfs_dispatcher Enable DNFS Dispatcher
enabled_PDBs_on_standby List of Enabled PDB patterns
encrypt_new_tablespaces whether to encrypt newly created tablespaces
exafusion_enabled Enable Exafusion
external_keystore_credential_location external keystore credential location
inmemory_adg_enabled Enable IMC support on ADG
inmemory_expressions_usage Controls which In-Memory Expressions are populated
in-memory
inmemory_virtual_columns Controls which user-defined virtual columns are stored
in-memory
instance_abort_delay_time time to delay an internal initiated abort (in seconds)
instance_mode indicates whether the instance read-only or read-write
or read-mostly
long_module_action Use longer module and action
max_datapump_jobs_per_pdb maximum number of concurrent Data Pump Jobs per PDB
max_idle_time maximum session idle time in minutes
max_iops MAX IO per second
max_mbps MAX MB per second
max_pdbs max number of pdbs allowed in CDB or Application ROOT
ofs_threads Number of OFS threads
one_step_plugin_for_pdb_with_tde [undocumented] Facilitate one-step plugin for PDB with TDE encrypted
data
optimizer_adaptive_plans controls all types of adaptive plans
optimizer_adaptive_statistics controls all types of adaptive statistics
outbound_dblink_protocols Outbound DBLINK Protocols allowed
remote_recovery_file_dest default remote database recovery file location for
refresh/relocate
resource_manage_goldengate goldengate resource manager enabled
sga_min_size Minimum, guaranteed size of PDB’s SGA
shrd_dupl_table_refresh_rate duplicated table refresh rate (in seconds)
standby_db_preserve_states Preserve state cross standby role transition
target_pdbs [undocumented] Parameter is a hint to adjust certain attributes of the
CDB
uniform_log_timestamp_format use uniform timestamp formats vs pre-12.2 formats

 

–Mike