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 to disk…
Tag Archives: parameter
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
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 8…
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
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…
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 12.2.0.1. The…
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 _very_large_partitioned_table allow_partial_sn_results…
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. Parameter Description allow_global_dblinks LDAP lookup for DBLINKS allow_group_access_to_sga Allow read access for SGA to…
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 waits…
New in Oracle 12c: _optimizer_gather_stats_on_load
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 – Page 13 (PDF: 15) http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf…
New 11.2.0.4 Parameter: ENABLE_GOLDENGATE_REPLICATION
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 the…
Parameter _rollback_segment_count can cause trouble
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 for myself 🙂…
DBMS_SCHEDULER jobs stuck after upgrade to 11.2? Check this parameter!
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 Guide,…
New hidden parameters in Oracle 11.2
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 a new…