I blogged multiple times about DBMS_OPTIM_BUNDLE, the package to work with the Fix Control Persistence (FCP) framework in the database. And we also gave recommendations over the years that you need to test carefully when you are going to enable the fixes while patching. But it is a good practice do enable them when you do upgrade testing, and also when you start creating new databases in a new environment. Just make sure you keep the settings equal across your environments. The other day I received a question from Dennis asking about DBMS_OPTIM_BUNDLE – what it shows, and what oradiff …
Continue reading...Parameters
DB_LOST_WRITE_PROTECT default change in RU 19.26.0
ORAdiff is an amazing tool I use at least once a day. And if you did the same before applying 19.26.0 or moving from 19.25.0 to 19.27.0, you may have spotted this change as well. There is a DB_LOST_WRITE_PROTECT default change in RU 19.26.0.
Photo by Andrew Neel on Unsplash
What is DB_LOST_WRITE_PROTECT?
This is quite a useful parameter, especially for Data Guard environments. The parameter enables or disabled the data block write-protection. It is meant to prevent such situations: A data block lost write occurs when an I/O subsystem acknowledges the completion of the block write, while in …
Continue reading...New interesting underscores for more than 255 _FIX_CONTROLs
I keep saying this – we have the best and most skilled customers. Christian Klose from my Franconian home region contacted me via LinkedIn and asked for some assistance with an SR. I confess, I replied way too late (information overflow). But the finding Christian had is great to know. There are new interesting underscores for more than 255 _FIX_CONTROLs.
Photo by Casey Horner on Unsplash
Why don’t you see them in ORAdiff?
As usual, I went to ORAdiff and tried to find out anything about these new _fix_control parameters. But I couldn’t find any indication for what Christian …
Continue reading...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.
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.
… Continue reading...New Parameters in Oracle 19.10.0 – and a default change
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:
|
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
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.
Photo by Jakub Gorajek on Unsplash
We introduced this parameter in Oracle 11.2.0.3 for an issue of growing …
Continue reading..._optimizer_ignore_hints is now optimizer_ignore_hints
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:
To be precise: We obsoleted159 parameters. In addition, in Oracle Database 18c we added an additional …
Continue reading...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 … Continue reading...
Remove Underscore Parameters not only before you upgrade
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 …
Continue reading...Why 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 …
Continue reading...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:
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
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
… Continue reading...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:
… Continue reading...
ALLOW_GROUP_ACCESS_TO_SGAcontrolsgroupaccess to shared memory
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.
- New SPFILE Parameters in Oracle Database 12.2.0.1
- Obsolete SPFILE Parameters in Oracle Database 12.2.0.1
- Deprecated SPFILE Parameters in Oracle Database 12.2.0.1
- Default SPFILE Parameter changes between Oracle Database 11.2.0.4, 12.1.0.2 and 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
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:
- https://mikedietrichde.com/2016/11/22/optimizer_adaptive_features-obsolete-in-oracle-12-2/
- https://blogs.oracle.com/optimizer/entry/optimizer_adaptive_features_in_the
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
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?
…
Continue reading...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 …
Continue reading...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.
- New SPFILE Parameters in Oracle Database 12.2.0.1
- Obsolete SPFILE Parameters in Oracle Database 12.2.0.1
- Deprecated SPFILE Parameters in Oracle Database 12.2.0.1
- Default SPFILE Parameter changes between Oracle Database 11.2.0.4, 12.1.0.2 and 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 …
Continue reading...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.
- New SPFILE Parameters in Oracle Database 12.2.0.1
- Obsolete SPFILE Parameters in Oracle Database 12.2.0.1
- Deprecated SPFILE Parameters in Oracle Database 12.2.0.1
- Default SPFILE Parameter changes between Oracle Database 11.2.0.4, 12.1.0.2 and 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… Continue reading...
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.
- New SPFILE Parameters in Oracle Database 12.2.0.1
- Obsolete SPFILE Parameters in Oracle Database 12.2.0.1
- Deprecated SPFILE Parameters in Oracle Database 12.2.0.1
- Default SPFILE Parameter changes between Oracle Database 11.2.0.4, 12.1.0.2 and 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.
|
_rowsets_enabled – Apply patch and use the default
I while back I blogged about issues with “rowsets“, a new Oracle 12c feature which unfortunately had two known wrong result (WQR) bugs:
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:
… Continue reading...“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
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):
… Continue reading...Because Transparent HugePages are
GC Freelist Session Waits causing slowness and hangs
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 …
Continue reading...Parameter Recommendations for Oracle Database 12c – Part II
See also:
Time for a new round on Parameter Recommendations for Oracle Database 12.1.0.2. The focus of this blog post settles on very well known parameters with interesting behavior. This can be a behavior change or simply something we’d like to point out. And even if you still work on Oracle Database 11g some of the below recommendations may apply to your environment as well.
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
See also:
A few weeks ago we’ve published some parameter recommendations including several underscores but based on an internal discussion (still ongoing) we decided to remove this entry and split up the tasks. The optimizer team will take over parts of it and I’ll post an update as soon as something is published.
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 …
Continue reading...
