In case you miss the downloads of Oracle Database 12.1.0.2 on OTN

Somebody asked me the other day why the download options for Oracle Database 12.1.0.2 for Linux and Solaris  disappeared from OTN? The same will happen with the other platforms as soon as Oracle Database 12.2.0.1 is available on-premises for them as well. You can always download Oracle Database 12.1.0.2 and previous releases from: Oracle Software Cloud (eDelivery) If you can’t locate the older release you are looking for please see this blog post I did in addition: Download Oracle Database 12.1.0.2 or older versions from the Oracle Software Cloud –Mike

Upgrade to Oracle Database 12.2 and Converting it into an 12.2 PDB (plugin)

2 customers requested a bit more explanation on how to: Convert and Plugin an Oracle 12.1 non-CDB database into an Oracle 12.2 container database Cool, I like that 🙂 I will do this right away and use the approach to Upgrade first, then Plugin afterwards. Upgrading an Oracle 12.1.0.2 database to Oracle 12.2.0.1 First the preupgrade.jar must be executed – please download always the most recent version from MOS Note:884522.1 as the version we publish is usually newer including important improvements (and fixes) than the version you’ll get with the release drop. We are working on including always the newest…

DBMS_QOPATCH does not work in PDBs (right now)

Thanks to Murthy who commented on this blog post and Jeannette Holland (SimCorp) who opened an SR resulting in an ER. DBMS_QOPATCH in Multitenant DBMS_QOPATCH will deliver useful information about installed patches only when executed within the CDB$ROOT. It has been designed this way for security reasons in Oracle Database 12.1 but I can easily see a need to check for installed patches within a PDB as well. Testcase I “borrowed” this test case from Jeannette’s SR: SQL> COLUMN NAME FORMAT A8 SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID; NAME     CON_ID DBID       CON_UID   …

February 2017 version of PREUPGRD.SQL is available

The new version of the preupgrd.sql (including the utluppkg.sql package) in its February 2017 version is available for download from MOS Note:884522.1. If you are going to upgrade to Oracle Database 12.1.0.2 please exchange your current preupgrd.sql/utluppkg.sql either in ?/rdbms/admin or in the location of the source system you’ve copied it to with the new version. –Mike

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…

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…

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

Global Temporary Tables – Change in Oracle 12c

A few weeks back I was copied on an email conversation about a important change with Global Temporary Tables (GTT) in Oracle Database 12c. Something you need to be aware of when using GTTs in Oracle Database 12.1.0.2: Prior to this release GTTs shared statistics between sessions. Statistics were SHARED between different sessions. Now since Oracle Database 12c this is different by default – statistics on GTTs are visible to the SESSION only. This can be changed and altered of course. And there are pros and cons. But as I’m not an optimizer expert I’m glad that my colleague Nigel…

Upgrade to Oracle Database 12c: We don’t insist :-)

It’s so funny – for years I discuss with customers minimal downtime upgrade strategies back and forth, up and down. I saw DBAs really hunting to save a few seconds potential downtime – and I always take this serious as there is usually a real reason behind that. Just to learn a few days later by repeating experience that my work Windows7 laptop goes down to apply upgrades … and this takes looooooooong …. sometimes it takes a lengthy +30 minutes of quiet time. Whereas my OL6 VBox image applies upgrades in the background and needs a simple restart to…

DBUA and Read-Only Tablespaces – Things to Know II

Related Blog Posts: DBUA and Read-Only Tablespaces – Things to Know – I (Feb 3, 2016) DBUA and Read Only Tablespaces – Things to Know – II (Mar 30, 2016) DBUA displays wrong RMAN Backup for Restore (Sep 21, 2015) DBUA 12c and “datapatch.pl” – things to know (Jul 20, 2015) Thanks to Rodolfo Baselli commenting on a previous blog post about the DBUA and Read-Only Tablespaces I dug a bit deeper and found out that “assuming silently” does not mean “works as intended“. But one piece after another. Rodolfo commented that if he triggers the DBUA to switch all data…

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…

Differences between Automatic Statistics Gathering job and GATHER_SCHEMA_STATS

Recently a customer raised a question whether there are differences between the Automatic Statistics Gathering job and a manual creation of stats via the GATHER_SCHEMA_STATS procedure. The results in performance were quite interesting. Performance after an upgrade from Oracle Database 11.2.0.3 to Oracle Database 11.2.0.4 was not good when the automatic stats job got used. But performance changed significantly to the better when schema stats were created with the downside of taking more resources during the gathering. Is the Automatic Stats Gathering job enabled? That question can be answered quite easily. There’s a very good MOS Note:1233203.1 – FAQ: Automatic…

DBUA and Read-Only Tablespaces – Things to Know – I

Related Blog Posts: DBUA and Read-Only Tablespaces – Things to Know – I (Feb 3, 2016) DBUA and Read Only Tablespaces – Things to Know – II (Mar 30, 2016) DBUA displays wrong RMAN Backup for Restore (Sep 21, 2015) DBUA 12c and “datapatch.pl” – things to know (Jul 20, 2015) Some people prefer the manual upgrade on the command line, others prefer the graphical tool Database Upgrade Assistant (DBUA). DBUA and Read-Only Tablespaces  The DBUA offers you an option of setting your non-Oracle tablespaces read-only during the upgrade. What the option doesn’t tell you is the purpose – and…

New PREUPGRD.SQL is available for Upgrades to 12c

As of today a new version of our upgrade tool preupgrd.sql (including the package utluppkg.sql) for upgrades to Oracle Database 12.1.0.2 is available as download from MOS: MOS Note:884522.1 How to Download and Run Oracle’s Database Pre-Upgrade Utility preupgrd.sql – Build 12 Download it and exchange the existing preupgrd.sql and utluppkg.sql in your current Oracle 12.1.0.2 ?/rdbms/admin directory. –Mike

RMAN NOOPEN Clause DUPLICATE FOR UPGRADE

In Oracle Database 12c, the RMAN “DUPLICATE” command has new “NOOPEN” option which clones the database but does not open the auxiliary instance. This feature can be used to duplicate a database to higher version. MOS Note: 2022820.1 Upgrade to 12c database using RMAN Duplicate with NOOPEN clause The note contains also a full example on how to duplicate the production database as a standby, and upgrade it. –Mike

Oracle Database certification on Microsoft Windows 10

The MOS Note: 1307195.1  about Certification Information for Oracle Database on Microsoft Windows x64 (64-bit) got updated right before the 2015 Christmas holidays with the official certification information for Oracle Database 12c on Microsoft Windows 10. And it says: Windows 10 O/S Information: RAC is not certified. The earliest release certified on Windows 10 is 12.1.0.2. –Mike Previous related blog posts from Aug 14, 2015: Oracle Databases and Microsoft Windows 10

Query on ALL_SYNONYMS is slow in Oracle Database 12c

A customer (Thanks Stefano!) alerted me on this issue during a workshop and I did some further investigation. Basic headline is: Query on ALL_SYNONYMS is very slow in Oracle Database 12.1.0.2 compared to 11g. The workaround for this  21324443: SLOW QUERY IN 12C ON ALL_SYNONYMS. is: dbms_stats.gather_table_stats(‘SYS’,’OBJ$’,estimate_percent =>100, method_opt => ‘for columns flags size 1, spare3 size 254, type# size 254’); and I see that at least 8 other customers opened SRs hitting the same issue. Does the workaround suit you in any way? The bug had no progress since it was opened. If you are seeking progress I can…

Network ACLs and Database Upgrade to Oracle 12c

What has been changed in Oracle Database 12c with Network ACLs? Starting from 12c, network access control in the Oracle database is implemented using Real Application Security access control lists (ACLs). Existing 11g network ACLs in XDB will be migrated. Existing procedures and functions of the DBMS_NETWORK_ACL_ADMIN PL/SQL package and catalog views have been deprecated and replaced with new equivalents In 12c, a network privilege can be granted by appending an access control entry (ACE) to a host ACL using DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE.  If you append an ACE to a host that has no existing host ACL, a new host ACL will…

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…

Ouch, this hurts: bug 21923026 – patch Oracle 12c Home before upgrading if you have OLTP Compression in 11g

Actually I’d consider this as a real serious issue which may affect many customers with larger deployments using the Advanced Compression Option’s OLTP Compression. I came across it as Don Seiler full of anger twittered some bad words about Oracle Database 12.1.0.2.. We’ve exchanged a good number of emails – and I could see the issues Don and his colleagues got while assisting a customer to go live on Oracle Database 12c. The most interesting one happened on the physical standby after the primary got upgraded. The MRP failed with an ORA-600. Issue After upgrading from Oracle 11.2.0.3 to Oracle 12.1.0.2…

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…

Ouch, this hurts: bug 20880215 – patch Oracle 12c

Just realized that my preivous blog post heading was misleading: it said “patch AFTER upgrade” which is misleading. You should apply this patch BEFORE you upgrade but of course to your new/future Oracle 12c home. If you hit the issue then apply the patch after upgrading to remedy it. Sorry for the confusion. No updates for over a week? Sorry for that but too many workshops, swamped with OOW prep work – and a wonderful issue with the browser cache and the blog software putting all my new blog post work directly into /dev/null. Thanks to Marcel Paul for highlighting…