parameter

When and how should you change COMPATIBLE?

Posted on April 17, 2019 by Mike.Dietrich Best Practice 23

When and how should you change COMPATIBLE?

COMPATIBLE is an almost mystic parameter. It has a default setting for each release. But if you try to find more information what it really does, you won’t be very happy. And in reply to my previous blog post about whether you need to change COMPATIBLE when you apply an RU, I received the following question: When and how should you change COMPATIBLE?

What does COMPATIBLE do?

To find an answer to this question, I consulted the documentation at first. And I found this:

COMPATIBLE
Setting COMPATIBLE ensures that new features do not write data formats or structures

Why you should set _EXCLUDE_SEED_CDB_VIEW=FALSE

Posted on April 15, 2019 by Mike.Dietrich Single-/Multitenant 0

Why you should set _EXCLUDE_SEED_CDB_VIEW=FALSE

There usually a philosophic battle happening when it comes to underscore parameters. The official statement is more or less: It’s an underscore, hence it is undocumented and you shouldn’t touch it unless Oracle Support advises you to do so. I agree in many cases. But in this specific one, I don’t. And in this post I will explain why you should set _EXCLUDE_SEED_CDB_VIEW=FALSE in all your singe- and Multitenant environments. Always!

A bit of history

At first, a little bit of a history excursion. This parameter wasn’t an underscore in Oracle 12.1. You can find it as EXCLUDE_SEED_CDB_VIEW in 12.1.0.1 …

_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

SPFILE Parameter: max_pdbs – a must for Single Tenant

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

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 9

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 6

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

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 …

New in Oracle 12c: _optimizer_gather_stats_on_load

Posted on October 6, 2015 by Mike.Dietrich Behavior Change Optimizer Oracle Database 12c Parameters 1

Received an email from Roy last night with some performance issues a customer in the US encountered recently during their upgrade testing.

One issue the customer encountered has to do with tons of parallel slaves creating a massive noise on the system when they are doing a CTAS (Create Table As Select) – and the same thing happens with an IAS (Insert Append Select).

What caused this change?

In this case the behavior change is well documented, even though not linked to the responsible underscore parameter.

Oracle White Paper: Best Practices for Gathering Statistics –

New 11.2.0.4 Parameter: ENABLE_GOLDENGATE_REPLICATION

Posted on February 11, 2014 by Mike.Dietrich Oracle Database 11g Parameters 0

Just learned something new I couldn’t find actually in the doc at the first glance:

There’s a new init.ora parameter introduced in Oracle Database 11.2.0.4 named:
ENABLE_GOLDENGATE_REPLICATION

By default it is set to FALSE and the parameter got introduced because not only the external use of Oracle GoldenGate requires a valid license but also the use of the internal APIs. For example, XStream provides high performance APIs that enable client
applications to receive and send real-time data changes from an Oracle
database. Other APIs were added for encryption support, trigger
suppression, etc. None of these APIs are licensed with …

Parameter _rollback_segment_count can cause trouble

Posted on September 19, 2012 by Mike.Dietrich Flaws and Pitfalls Parameters 2

Just some weeks ago we’ve learned that setting the hidden underscore parameter:

_rollback_segment_count

may cause trouble during upgrade. This parameter is used in very rare cases to have under all circumstances and situations this specified number of UNDO’s online. Now during upgrade this may result in massive latch contention – and there’s a patch available as well. Recommendation is to unset it during upgrade.

I don’t think that many people will hit this as I personally haven’t seen databases with this underscore in their init.ora or spfiles. So take this post more or less as a reminder …

DBMS_SCHEDULER jobs stuck after upgrade to 11.2? Check this parameter!

Posted on August 8, 2011 by Roy.Swonger Parameters 0

I think I learn something new practically every time that I talk to or work with customers. Here’s a nugget of information that may be worth its weight in gold if you are upgrading in a RAC environment. You might find after the upgrade that your DBMS_SCHEDULER jobs seem to be scheduled but unable to run, as if the scheduler is stuck for some reason.

The reason for this is an interesting parameter job_queue_processes whose behavior changed in 11.2. The parameter itself has been around for a long time, but here is the important note from the 11.2 Upgrade …

New hidden parameters in Oracle 11.2

Posted on March 9, 2011 by Mike.Dietrich Parameters 0

We really welcome every external review of our slides. And also recommendations from customers visiting our workshops.

So it happened to me more than a week ago that Marco Patzwahl, the owner of MuniqSoft GmbH, had a very lengthy train ride in Germany (as the engine drivers go on strike this week it could have become even worse) and nothing better to do than reviewing our slide set. And he had plenty of recommendations.

Besides that he pointed us to something at least I was not aware of and added it to the slides:

In patch set 11.2.0.2 …

parameter

When and how should you change COMPATIBLE?

What does COMPATIBLE do?

Why you should set _EXCLUDE_SEED_CDB_VIEW=FALSE

A bit of history

_optimizer_ignore_hints is now optimizer_ignore_hints

_optimizer_ignore_hints is now optimizer_ignore_hints

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

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

SPFILE Parameter: max_pdbs – a must for Single Tenant

Deprecated Parameters in Oracle Database 12.2.0.1

New SPFILE parameters in Oracle Database 12.2.0.1

GC Freelist Session Waits causing slowness and hangs

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

New in Oracle 12c: _optimizer_gather_stats_on_load

What caused this change?

New 11.2.0.4 Parameter: ENABLE_GOLDENGATE_REPLICATION

Parameter _rollback_segment_count can cause trouble

DBMS_SCHEDULER jobs stuck after upgrade to 11.2? Check this parameter!

New hidden parameters in Oracle 11.2

Archives

Categories

Upcoming Events

APAC OUG: AutoUpgrade to 19c and 21c

APAC OUG: Welcome to the Jungle – Database Patching Insights

Upgrade @YouTube

Blog Stats

RSS Feed for Upgrade your Database – NOW!

Subscribe via Email

Follow me on Twitter

Recent Posts