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

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

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 not a big fan…

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…

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…

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: 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 on UNIX…

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 all on…

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 It recommends to…

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. 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 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…

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…

_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: 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 done on…

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): Because Transparent HugePages are known…

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…

Parameter Recommendations for Oracle Database 12c – Part II

See also: Parameter Recommendations for Oracle Database 12c – Part I 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 Support. Always use proper testing…

Parameter Recommendations for Oracle Database 12c – Part I

See also: Parameter Recommendations for Oracle Database 12c – Part II 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 verify the effect of any of those…

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

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 to database upgrades as…

UPDATE: _rowsets_enabled in Oracle Database 12c

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 why one…

Switch off “_rowsets_enabled” in Oracle Database 12c

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 Jonathan’s…

Where do these large trace files come from in Oracle 12c?

Just had an observation about very large trace files on one of my customers I’m working with at the moment. When I write “very” I mean “VERY” as some grew over 10GB within a few hours. The files contained a ton of such messages: —– Cursor Obsoletion Dump sql_id=5p8a9d4017bq3 —– Parent cursor obsoleted 1 time(s). maxchild=1024 basephd=00007FFB8AD45CB0 phd=00007FFB8AD45CB0 After doing a bit of research I came across this document and an explanation: MOS Note:1955319.1; Huge Trace Files Created Containing “—– Cursor Obsoletion Dump sql_id=%s —–“ Well, we introduced an Enhancement – via an unpublished bug (and I’d guess it is…

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…

SQL Monitoring – Limitation at 300 lines per statement

One of the best parts of my job at Oracle: I still learn something new every day. Yesterday I’ve learned from my colleague from Oracle Switzerland, Thomas Teske, that SQL Real Time Monitoring has an embedded default limitation on the number of lines in the statement. If the limit (default: 300 lines) is exceeded the statement won’t be monitored. We both work with a leading Swiss company and we wanted to monitor a complex plan. Now you may think: Who the heck has statements longer than 300 lines? Well … sometimes that is beyond your influence as in this particular case…

Optimizer Issue in Oracle 12.0.1.2: “Reduce Group By”

DBAs biggest fears I’d guess are Optimizer Wrong Query Results bugs as usually the optimizer does not write a message into the alert.log saying “Sorry, I was in a bad mood today …”. The Oracle Database Optimizer is a complex piece – and in Oracle 12c it delivers great performance results. Plus (my personal experience when you know what to do) it is more predictable which I like a lot when changing databases from one to another release. But due to its complexity sometimes we see issues – and sometimes it is necessary to switch off tiny little pieces until…