Parameters

Oracle Database 19.10.0 and Blockchain Tables and COMPATIBLE

Many of you recognized that we delivered a new feature with Oracle Database 19.10.0, the January 2021 Release Upgrade. So I’d like to put a few interesting things together from the upgrade and migration perspective regarding Oracle Database 19.10.0 and Blockchain Tables and COMPATIBLE.

Oracle Database 19.10.0 and Blockchain Tables and COMPATIBLE

Photo by Daniel von Appen on Unsplash

What are Blockchain Tables?

At first, I don’t want to repeat what other people have blogged and written about already. You may just go through the blog posts of Connor McDonald and Tim Hall and others to learn more about this cool feature.

Connor McDonald about “Blockchain

New Parameters in Oracle 19.10.0 – and a default change

Posted on February 8, 2021 by Mike.Dietrich Behavior Change Parameters 9

Oracle Database 19.10.0, the January 2021 Release Update (RU), does seem to change some well known rules. Especially when you have a closer look to a backported feature, Blockchain Tables. But I will write a bit more about this change next week. For now, let’s have a closer look at New Parameters in Oracle 19.10.0 – and a default change.

Photo by Stephen Leonardi on Unsplash

New Parameters in Oracle 19.10.0

This is the list of new parameters in the January 2021 Release Update 19.10.0:

Parameter	Description	Default
blockchain_table_max_no_drop	maximum idle retention minutes for blockchain table	NONE
container_data

Oracle 12.2 and higher: Set _cursor_obsolete_threshold to old default

Posted on September 11, 2018 by Mike.Dietrich Parameters 18

Sometimes development groups change default parameter settings because it fits well for a specific feature. This has happened with _cursor_obsolete_threshold in Oracle Database 12.2. But there’s now an official recommendation for Oracle 12.2 and higher: Set _cursor_obsolete_threshold to old default.

What is _cursor_obsolete_threshold?

First of all, this is obviously an underscore parameter. And usually the policy is: Don’t change underscore parameters unless either you clearly understand what you are changing. Or unless Oracle Support (or another Oracle authority) advises you to do.

Oracle 12.2 and higher: Set _cursor_obsolete_threshold to old default

Photo by Jakub Gorajek on Unsplash

We introduced this parameter in Oracle 11.2.0.3 for an issue of growing …

_optimizer_ignore_hints is now optimizer_ignore_hints

Posted on June 25, 2018 by Mike.Dietrich Optimizer Parameters 3

I’m refreshing our Hands-On Lab for Oracle Database 18c. And as part of a performance evaluation I wanted to set an underscore parameter to ignore all hints my load test tool sets. But then I learned: This does not work anymore as _optimizer_ignore_hints is now optimizer_ignore_hints. We made the underscore obsolete in Oracle Database 18c.

_optimizer_ignore_hints is now optimizer_ignore_hints

Actually in Oracle Database 12.2.0.1 you can find a long list of obsolete parameters here:

Obsolete Parameters in Oracle Database 12.2.0.1 (Nov 10, 2016)

To be precise: We obsoleted159 parameters. In addition, in Oracle Database 18c we added an additional …

Drop a tablespace in a PDB with a Guaranteed Restore Point being active

Posted on June 5, 2018 by Mike.Dietrich Parameters Single-/Multitenant 1

Drop a tablespace in a PDB with a Guaranteed Restore Point being active This was an interesting question I received from Alain Fuhrer a couple of weeks ago: “Why can’t I drop a tablespace in a PDB whit a Guaranteed Restore Point being active?”. It’s important to note that the GRP is not active in the same but in another PDB.

An SR resolved the issue. But in case you face the same situation, here’s the solution.

Drop a tablespace in a PDB with a Guaranteed Restore Point being active

Simple test scenario. These are my PDBs:

show pdbs

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

Remove Underscore Parameters not only before you upgrade

Posted on May 22, 2018 by Mike.Dietrich Parameters 7

Underscore parameters are a wonderful thing in Oracle. They are treated like a secret. But you can view them of course. Everybody can. Make a guess how many underscore parameters exist in Oracle 12.2.0.1.0. You’ll find the answer at the end of this blog post. But why should you remove underscore parameters not only before you upgrade?

I’d like to explain below why underscores, especially too many of them, may cause a lot of trouble. Therefore, housekeeping is very important.

Remove Underscore Parameters not only before you upgrade

This is from a recent upgrade from Oracle 12.1.0.2 to Oracle 12.2.0.1 …

Why EXCLUDE_SEED_CDB_VIEW is now an underscore in Oracle 12.2

Posted on July 21, 2017 by Mike.Dietrich Parameters Single-/Multitenant 3

Hiding information is not good - and exclude_seed_cdb_view is now an underscore in Oracle 12.2 I have received several questions from customers in the past weeks using Oracle Multitenant about the parameter exclude_seed_cdb_view.It magically disappeared and morphed into _exclude_seed_cdb_view since Oracle Database 12.2.0.1. To be fair, the exclude_seed_cdb_view is listed in the list of obsolete parameters in Oracle Database 12.2.0.1.

What is EXCLUDE_SEED_CDB_VIEW?

This parameter hides everything belonging to the PDB$SEED from the usual queries. For instance you ran a query against CDB_DATA_FILES but you won’t see the data files belonging to PDB$SEED in the result set. Whether this is good or bad, at least RMAN (Recovery Manager) worked always correctly.

But I’m …

SGA_MIN_SIZE in Oracle Database 12.2

Posted on May 8, 2017 by Mike.Dietrich Parameters Single-/Multitenant 0

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 …

PGA_AGGREGATE_LIMIT enforces default since Oracle Database 12.2.0.1

Posted on May 4, 2017 by Mike.Dietrich Behavior Change Oracle Database 12.2.0.1 Parameters 11

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

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

Posted on February 1, 2017 by Mike.Dietrich Behavior Change Oracle Database 12c Parameters 7

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:

ALLOW_GROUP_ACCESS_TO_SGA

ENCRYPT_NEW_TABLESPACES

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

Default Changes SPFILE Parameters – Oracle 12.2

Posted on January 18, 2017 by Mike.Dietrich Oracle Database 12.2.0.1 Parameters 9

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 …

Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

Posted on November 23, 2016 by Mike.Dietrich Optimizer Oracle Database 12c Parameters 10

Oracle Database 12.2 introduces the new split-up adaptive parameters, OPTIMIZER_ADAPTIVE_PLANS and OPTIMITER_ADAPTIVE_STATISTICS. And the key trigger to cure potential and known performance issues in Oracle Database 12.1.0.2 is enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

For more information please see:

Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

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?

MOS Note: 2187449.1
Oracle Highly Recommended Adaptive Feature Configuration Parameter Settings for 12.1.0.2.0

OPTIMIZER_ADAPTIVE_FEATURES obsolete in Oracle 12.2

Posted on November 22, 2016 by Mike.Dietrich Optimizer Oracle Database 12.2.0.1 Parameters 10

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.

https://blogs.oracle.com/optimizer/entry/optimizer_adaptive_features_in_the

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

Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

…

SPFILE Parameter: max_pdbs – a must for Single Tenant

Posted on November 14, 2016 by Mike.Dietrich Parameters Single-/Multitenant 4

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 …

Deprecated Parameters in Oracle Database 12.2.0.1

Posted on November 11, 2016 by Mike.Dietrich Deprecated / Desupported Oracle Database 12.2.0.1 Parameters 11

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 …

Obsolete SPFILE Parameters in Oracle Database 12.2.0.1

Posted on November 10, 2016 by Mike.Dietrich Oracle Database 12.2.0.1 Parameters 5

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

New SPFILE parameters in Oracle Database 12.2.0.1

Posted on November 9, 2016 by Mike.Dietrich Oracle Database 12.2.0.1 Parameters 8

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

_rowsets_enabled – Apply patch and use the default

Posted on October 11, 2016 by Mike.Dietrich Flaws and Pitfalls Optimizer Oracle Database 12c Parameters Patch Recommendation 1

I while back I blogged about issues with “rowsets“, a new Oracle 12c feature which unfortunately had two known wrong result (WQR) bugs:

Switch off “_rowsets_enabled” in Oracle Database 12c

UPDATE: _rowsets_enabled in Oracle Database 12c

What does “rowsets” actually mean?

I’d like to thank Sankar, our Development manager for providing this explanation which sheds some light on how important this feature actually is:

“Rowsets is a SQL execution performance enhancement introduced in Oracle RDBMS release 12.1 and further extended in a future release of the Oracle Database.

Prior to 12.1, data processing in the SQL layer were

Disable Transparent Hugepages on SLES11, RHEL6, RHEL7, OL6, OL7 and UEK2 Kernels

Posted on March 29, 2016 by Mike.Dietrich Flaws and Pitfalls Operating System Parameters 3

This blog post is not related to database upgrades and migrations. But still I think it is very useful for many customers operating on modern Linux systems.

Recommendation

Support just published an ALERT strongly recommending to disable Transparent Hugepages on Linux systems. And the below information does not apply to RAC systems only but also to single instance environments.

Which Linux Distrubutions/Kernels are affected?

SLES11
RHEL6 and RHEL7
OL6 and OL7
UEK2 Kernels

What are the Issues?

I’m quoting MOS Note: 1557478.1 (ALERT: Disable Transparent HugePages on SLES11, RHEL6, RHEL7, OL6, OL7 and UEK2 Kernels):

Because Transparent HugePages are

GC Freelist Session Waits causing slowness and hangs

Posted on March 22, 2016 by Mike.Dietrich Behavior Change Flaws and Pitfalls Oracle Database 12c Parameters Patch Recommendation 0

One of the best things in my job:
I learn from you folks out there. Everyday.

Credits here go to Maciej Tokar who did explain the below topic to me via LinkedIn – thanks a lot, Maciej!

Locks are not being closed fast enough, resulting in gc freelist waits

You can find a reference for Global Cache Freelist in the Oracle Documentation. This issue here can or will lead to database being slow, up to complete hangs. Based on my research it looks as the issue is not related to RAC only but a general thing. In your session …

Parameter Recommendations for Oracle Database 12c – Part II

Posted on March 8, 2016 by Mike.Dietrich Behavior Change Oracle Database 12c Parameters Patch Recommendation 6

Preface

Again, please be advised – the following parameter list is mostly based on personal experience only. Some of them are officially recommended by Oracle

Parameter Recommendations for Oracle Database 12c – Part I

Posted on March 4, 2016 by Mike.Dietrich Oracle Database 12c Parameters Patch Recommendation 5

Preface

Please be advised – the following parameter list is mostly based on personal experience only. Some of them are officially recommended by Oracle Support. Always use proper testing mechanisms.

We strongly recommend SQL Performance Analyzer to …

What happened to the blog post about “12c parameters”?

Posted on February 25, 2016 by Mike.Dietrich Optimizer Parameters Performance Tuning 3

Two weeks ago I published a blog post about Parameter Recommendations for Oracle Database 12.1.0.2. And I took it down a day later. Why that?

I’ve got a lot of input from external sources for the “Parameter” blog post. And I’d like to thank everybody who contributed to it, especially Oracle ACE Ludovico Caldara.

Generally there was a bit of a misunderstanding internally about whether we should “advertise” underscore parameters to cure some misbehavior of the database. In 99% of all cases I’d agree that underscores are not a good solution – especially when it comes …

UPDATE: _rowsets_enabled in Oracle Database 12c

Posted on November 16, 2015 by Mike.Dietrich Flaws and Pitfalls Optimizer Oracle Database 12c Parameters 9

Please find a recent update here:

_rowsets_enabled – Apply patch and use the default

Last week I did post this entry with a strong recommendation to disable _rowsets_enabled in Oracle Database 12.1.0.2:

Nov 10, 2015:
Switch off _rowsets_enabled in Oracle Database 12c

Today I can give you an update, more insight information and better workarounds.

Credits go to our DWH and Optimizer people (thanks to Hermann, Angela, Nigel and Mohammed).

When is the problem happening?

When a hash join operation receives rowsets from its right input but then produces one row at a time as output. This explains …

Switch off “_rowsets_enabled” in Oracle Database 12c

Posted on November 10, 2015 by Mike.Dietrich Flaws and Pitfalls Optimizer Oracle Database 12c Parameters 1

Please find a recent update here:

UPDATE: _rowsets_enabled in Oracle Database 12c

and more important:

_rowsets_enabled – Apply patch and use the default

Twitter is a good thing. I get alerted on things I haven’t seen before. And sometimes some things are more than interesting.

This one is actually proven by Jonathan Lewis – and you can read all the details in Jonathan’s blog post here:

https://jonathanlewis.wordpress.com/2015/11/09/wrong-results/

There seems to be a realistic chance to get wrong query results displayed (regardless of using SQL*Plus or a JDBC or any other client – see the comment by Stefan Koehler below …

Parameters

What are Blockchain Tables?

New Parameters in Oracle 19.10.0

What is _cursor_obsolete_threshold?

_optimizer_ignore_hints is now optimizer_ignore_hints

Drop a tablespace in a PDB with a Guaranteed Restore Point being active

Remove Underscore Parameters not only before you upgrade

What is EXCLUDE_SEED_CDB_VIEW?

Default

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

Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

What does “rowsets” actually mean?

Recommendation

Which Linux Distrubutions/Kernels are affected?

What are the Issues?

Locks are not being closed fast enough, resulting in gc freelist waits

Preface

Preface

When is the problem happening?

Archives

Categories

Upgrade @YouTube

Subscribe via Email

Follow me on Twitter

Recent Posts