This post gives more detail about it and which patches to apply. It would be nice to have those patches merged into each bundle patch, but it is not.
\n<\/p>\n
dbi services 12c<\/em>R2 free event<\/h3>\nThis Month, the 12c<\/em>R2 was released and we immediately informed our customers about the new features that we think are important to know in order to plan when to go to 12.2 and for which projects. We started with the most important one, the Adaptive Statistics which helps to avoid all those performance issues encountered after migrating to 12c<\/em>R1. We also mentioned that this new behavior has been backported to 12.2 with two patches, as explained here by Clemens Bleile:
\n![\"IMG_3833\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/IMG_3833.jpg\")
<\/a><\/p>\n12c<\/em>R1<\/h3>\nThis event was to inform about 12.2 but lot of customers waited for this to plan their 12.1 migration. Knowing the roadmap and the new features helps to plan what can wait for a stable 12c<\/em>R2 (after few PSUs) and what must be migrated now to 12c<\/em>R1. This is why we did everything to rollout this event as soon as possible once the main platform (Linux and Windows) were publicly available.<\/p>\nWhat to install for 12.1.0.2<\/h3>\n
Our recommendation for new installations of 12c<\/em> for current migration with minimal risks of regression is<\/p>\n\n- The latest patchset of Oracle Database 12cR1: 12.1.0.2<\/li>\n
- The latest Proactive Bundle Patch: JAN2017 BP<\/li>\n
- The two patches to get full control over Adaptive statistics: patches 22652097 and 21171382<\/li>\n<\/ul>\n
Nothing is easy with patching, so I’ll detail how to find exactly what to install.<\/p>\n
Latest patchset of Oracle Database 12cR1: 12.1.0.2<\/h3>\n
Now that the latest version is 12.2.0.1 you cannot find anymore the 12.1.0.2 on the oracle.com download page<\/a>.
\nYou can download 12.1.0.2 from the Patchset 21419221: https:\/\/updates.oracle.com\/download\/21419221.html<\/a><\/p>\nLatest Proactive Bundle Patch: JAN2017 BP<\/h3>\n
Finding the latest patch set update is easy if you follow the MOS Note Quick Reference to Patch Numbers for Database\/GI PSU, SPU(CPU), Bundle Patches and Patchsets (Doc ID 1454618.1)<\/a><\/p>\nYou will download https:\/\/updates.oracle.com\/download\/24968615.html<\/a> for Linux or https:\/\/updates.oracle.com\/download\/25115951.html<\/a> for Windows. <\/p>\nAdaptive statistics: patches 22652097 and 21171382<\/h3>\n
Then you have to find the two patches to apply them on top of the JAN17 ProactiveBP.<\/p>\n
The first one is for separate parameters to enable adaptive plans and adaptive statistics separately: https:\/\/updates.oracle.com\/download\/22652097.html<\/a> and you can find it for Oracle 12.1.0.2.170117 Proactive BP<\/p>\nThe second one is for dbms_stats preference to control statistics extensions creation and you will have two suprises when following the link:
\nThe name is AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY because the change has been merged with some other changes on concurrent statistics
\nIt is available only for (listed in the non-chronological order of the platform list on MOS):<\/p>\n
\n- Oracle 12.1.0.2.13 Proactive BP<\/li>\n
- Oracle 12.1.0.2.0<\/li>\n
- Oracle 12.1.0.2.5<\/li>\n
- Oracle 12.1.0.2.160119 Proactive BP<\/li>\n
- Oracle 12.1.0.2.7 Proactive BP<\/li>\n<\/ul>\n
If you want to go further, you have to open a SR, provide an opatch lsinventory (because it seems that Oracle Support Engineers are not able to get the lsinventory for the latest Proactive BP – the recommended one). And this is where the nightmare starts. The lastest we have here is for JAN16 Proactive Bundle Patch – 12.1.0.2.160119 Proactive BP.<\/p>\n
I can’t wait for a relevant answer from MOS support engineers, so I got to look at the patch. It is very simple change actually.In DBMS_STATS it has to check whether AUTO_STAT_EXTENSIONS is ON before creating the column group. This is all in prvtstat.plb and if we are lucky there were no changes on it since the JAN16.<\/p>\n
Before trying it, we can check conflicts in MOS. Here are the 3 patches I would like to apply, in their most recent release for Linux:<\/p>\n
![\"CaptureOpatchConflict\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureOpatchConflict.png\")
<\/a><\/p>\nYes, this is a nice feature of My Oracle Support: you can analyze the conflicts online.<\/p>\n
The result of conflict analysis shows that we are lucky:
\n![\"CaptureOpatchConflict003\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureOpatchConflict0031.png\")
<\/a><\/p>\nReady now to download the files:
\n![\"CaptureOpatchConflict004\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureOpatchConflict004.png\")
<\/a><\/p>\nSo the last patch we need, https:\/\/updates.oracle.com\/download\/21171382.html<\/a>, can be downloaded in its latest Proactive BP version, even if it is 1 year old. And don’t worry about its name: p21171382_12102160119DBEngSysandDBIM_Generic.zip<\/p>\ndatapatch and verification<\/h3>\n
Don’t forget to run datapatch on your databases to be sure that the dictionary is patched.<\/p>\n
$ORACLE_HOME\/OPatch\/datapatch\n \nSQL Patching tool version 12.1.0.2.0 Production on Mon Mar 27 09:18:47 2017\nCopyright (c) 2012, 2017, Oracle. All rights reserved.\n \nConnecting to database...OK\nBootstrapping registry and package to current versions...done\nDetermining current state...done\nAdding patches to installation queue and performing prereq checks...done\nInstallation queue:\n Nothing to roll back\n The following patches will be applied:\n 24732088 (DATABASE BUNDLE PATCH 12.1.0.2.170117)\n 21171382 (AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY)\n 22652097 (PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATISTICS FEATURES)\n \nInstalling patches...\nPatch installation complete. Total patches installed: 3\n \nValidating logfiles...done\nSQL Patching tool complete on Mon Mar 27 09:21:33 2017<\/code><\/pre>\nAnd then connect to check the new Adaptive Statistics behavior:<\/p>\n
SQL> show parameter optimizer_adaptive\n \nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\noptimizer_adaptive_plans boolean TRUE\noptimizer_adaptive_reporting_only boolean FALSE\noptimizer_adaptive_statistics boolean FALSE\n \nSQL> select dbms_stats.get_prefs('AUTO_STAT_EXTENSIONS') from dual;\n \nDBMS_STATS.GET_PREFS('AUTO_STAT_EXTENSIONS')\n--------------------------------------------------------------------------------\nOFF<\/code><\/pre>\nSummary for 12.1.0.2 new install<\/h3>\n
Don’t install a 12c<\/em> database with only the software and documentation that was released 3 years ago. Lot of migration feedback has improved the stability of such migration, and this is implemented in patchset updates and MOS notes.
\nA good source of information is Mike Dietrich blog (the best source you can find to benefit from lot of migration projects experience):<\/p>\n\n- https:\/\/blogs.oracle.com\/UPGRADE\/entry\/parameter_recommendations_for_oracle_database<\/a><\/li>\n
- https:\/\/blogs.oracle.com\/UPGRADE\/entry\/parameter_recommendations_for_oracle_database1<\/a><\/li>\n<\/ul>\n
Then, get the latest recommended software.<\/p>\n
\n- The patchset: https:\/\/updates.oracle.com\/download\/21419221.html<\/a> for your platform<\/li>\n
- The patchset update https:\/\/updates.oracle.com\/download\/24968615.html<\/a> for Unix\/Linux
or https:\/\/updates.oracle.com\/download\/25115951.html<\/a> for Windows<\/li>\n- The Adaptive Statistics instance parameters: https:\/\/updates.oracle.com\/download\/22652097.html<\/a> for Oracle 12.1.0.2.170117 Proactive BP\n<\/li>\n
- The Adaptive Statistics stat preference https:\/\/updates.oracle.com\/download\/21171382.html<\/a> for Oracle 12.1.0.2.160119 Proactive BP<\/li>\n<\/ol>\n
Don’t forget to run datapatch on all databases, even the newly created ones.<\/p>\n
Update for APR17<\/h3>\n
\n
<\/a><\/p>\n12c<\/em>R1<\/h3>\nThis event was to inform about 12.2 but lot of customers waited for this to plan their 12.1 migration. Knowing the roadmap and the new features helps to plan what can wait for a stable 12c<\/em>R2 (after few PSUs) and what must be migrated now to 12c<\/em>R1. This is why we did everything to rollout this event as soon as possible once the main platform (Linux and Windows) were publicly available.<\/p>\nWhat to install for 12.1.0.2<\/h3>\n
Our recommendation for new installations of 12c<\/em> for current migration with minimal risks of regression is<\/p>\n\n- The latest patchset of Oracle Database 12cR1: 12.1.0.2<\/li>\n
- The latest Proactive Bundle Patch: JAN2017 BP<\/li>\n
- The two patches to get full control over Adaptive statistics: patches 22652097 and 21171382<\/li>\n<\/ul>\n
Nothing is easy with patching, so I’ll detail how to find exactly what to install.<\/p>\n
Latest patchset of Oracle Database 12cR1: 12.1.0.2<\/h3>\n
Now that the latest version is 12.2.0.1 you cannot find anymore the 12.1.0.2 on the oracle.com download page<\/a>.
\nYou can download 12.1.0.2 from the Patchset 21419221: https:\/\/updates.oracle.com\/download\/21419221.html<\/a><\/p>\nLatest Proactive Bundle Patch: JAN2017 BP<\/h3>\n
Finding the latest patch set update is easy if you follow the MOS Note Quick Reference to Patch Numbers for Database\/GI PSU, SPU(CPU), Bundle Patches and Patchsets (Doc ID 1454618.1)<\/a><\/p>\nYou will download https:\/\/updates.oracle.com\/download\/24968615.html<\/a> for Linux or https:\/\/updates.oracle.com\/download\/25115951.html<\/a> for Windows. <\/p>\nAdaptive statistics: patches 22652097 and 21171382<\/h3>\n
Then you have to find the two patches to apply them on top of the JAN17 ProactiveBP.<\/p>\n
The first one is for separate parameters to enable adaptive plans and adaptive statistics separately: https:\/\/updates.oracle.com\/download\/22652097.html<\/a> and you can find it for Oracle 12.1.0.2.170117 Proactive BP<\/p>\nThe second one is for dbms_stats preference to control statistics extensions creation and you will have two suprises when following the link:
\nThe name is AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY because the change has been merged with some other changes on concurrent statistics
\nIt is available only for (listed in the non-chronological order of the platform list on MOS):<\/p>\n
\n- Oracle 12.1.0.2.13 Proactive BP<\/li>\n
- Oracle 12.1.0.2.0<\/li>\n
- Oracle 12.1.0.2.5<\/li>\n
- Oracle 12.1.0.2.160119 Proactive BP<\/li>\n
- Oracle 12.1.0.2.7 Proactive BP<\/li>\n<\/ul>\n
If you want to go further, you have to open a SR, provide an opatch lsinventory (because it seems that Oracle Support Engineers are not able to get the lsinventory for the latest Proactive BP – the recommended one). And this is where the nightmare starts. The lastest we have here is for JAN16 Proactive Bundle Patch – 12.1.0.2.160119 Proactive BP.<\/p>\n
I can’t wait for a relevant answer from MOS support engineers, so I got to look at the patch. It is very simple change actually.In DBMS_STATS it has to check whether AUTO_STAT_EXTENSIONS is ON before creating the column group. This is all in prvtstat.plb and if we are lucky there were no changes on it since the JAN16.<\/p>\n
Before trying it, we can check conflicts in MOS. Here are the 3 patches I would like to apply, in their most recent release for Linux:<\/p>\n
<\/a><\/p>\nYes, this is a nice feature of My Oracle Support: you can analyze the conflicts online.<\/p>\n
The result of conflict analysis shows that we are lucky:
\n<\/a><\/p>\nReady now to download the files:
\n<\/a><\/p>\nSo the last patch we need, https:\/\/updates.oracle.com\/download\/21171382.html<\/a>, can be downloaded in its latest Proactive BP version, even if it is 1 year old. And don’t worry about its name: p21171382_12102160119DBEngSysandDBIM_Generic.zip<\/p>\ndatapatch and verification<\/h3>\n
Don’t forget to run datapatch on your databases to be sure that the dictionary is patched.<\/p>\n
$ORACLE_HOME\/OPatch\/datapatch\n \nSQL Patching tool version 12.1.0.2.0 Production on Mon Mar 27 09:18:47 2017\nCopyright (c) 2012, 2017, Oracle. All rights reserved.\n \nConnecting to database...OK\nBootstrapping registry and package to current versions...done\nDetermining current state...done\nAdding patches to installation queue and performing prereq checks...done\nInstallation queue:\n Nothing to roll back\n The following patches will be applied:\n 24732088 (DATABASE BUNDLE PATCH 12.1.0.2.170117)\n 21171382 (AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY)\n 22652097 (PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATISTICS FEATURES)\n \nInstalling patches...\nPatch installation complete. Total patches installed: 3\n \nValidating logfiles...done\nSQL Patching tool complete on Mon Mar 27 09:21:33 2017<\/code><\/pre>\nAnd then connect to check the new Adaptive Statistics behavior:<\/p>\n
SQL> show parameter optimizer_adaptive\n \nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\noptimizer_adaptive_plans boolean TRUE\noptimizer_adaptive_reporting_only boolean FALSE\noptimizer_adaptive_statistics boolean FALSE\n \nSQL> select dbms_stats.get_prefs('AUTO_STAT_EXTENSIONS') from dual;\n \nDBMS_STATS.GET_PREFS('AUTO_STAT_EXTENSIONS')\n--------------------------------------------------------------------------------\nOFF<\/code><\/pre>\nSummary for 12.1.0.2 new install<\/h3>\n
Don’t install a 12c<\/em> database with only the software and documentation that was released 3 years ago. Lot of migration feedback has improved the stability of such migration, and this is implemented in patchset updates and MOS notes.
\nA good source of information is Mike Dietrich blog (the best source you can find to benefit from lot of migration projects experience):<\/p>\n\n- https:\/\/blogs.oracle.com\/UPGRADE\/entry\/parameter_recommendations_for_oracle_database<\/a><\/li>\n
- https:\/\/blogs.oracle.com\/UPGRADE\/entry\/parameter_recommendations_for_oracle_database1<\/a><\/li>\n<\/ul>\n
Then, get the latest recommended software.<\/p>\n
\n- The patchset: https:\/\/updates.oracle.com\/download\/21419221.html<\/a> for your platform<\/li>\n
- The patchset update https:\/\/updates.oracle.com\/download\/24968615.html<\/a> for Unix\/Linux
or https:\/\/updates.oracle.com\/download\/25115951.html<\/a> for Windows<\/li>\n- The Adaptive Statistics instance parameters: https:\/\/updates.oracle.com\/download\/22652097.html<\/a> for Oracle 12.1.0.2.170117 Proactive BP\n<\/li>\n
- The Adaptive Statistics stat preference https:\/\/updates.oracle.com\/download\/21171382.html<\/a> for Oracle 12.1.0.2.160119 Proactive BP<\/li>\n<\/ol>\n
Don’t forget to run datapatch on all databases, even the newly created ones.<\/p>\n
Update for APR17<\/h3>\n
What to install for 12.1.0.2<\/h3>\n
Our recommendation for new installations of 12c<\/em> for current migration with minimal risks of regression is<\/p>\n Nothing is easy with patching, so I’ll detail how to find exactly what to install.<\/p>\n Now that the latest version is 12.2.0.1 you cannot find anymore the 12.1.0.2 on the oracle.com download page<\/a>. Finding the latest patch set update is easy if you follow the MOS Note Quick Reference to Patch Numbers for Database\/GI PSU, SPU(CPU), Bundle Patches and Patchsets (Doc ID 1454618.1)<\/a><\/p>\n You will download https:\/\/updates.oracle.com\/download\/24968615.html<\/a> for Linux or https:\/\/updates.oracle.com\/download\/25115951.html<\/a> for Windows. <\/p>\n Then you have to find the two patches to apply them on top of the JAN17 ProactiveBP.<\/p>\n The first one is for separate parameters to enable adaptive plans and adaptive statistics separately: https:\/\/updates.oracle.com\/download\/22652097.html<\/a> and you can find it for Oracle 12.1.0.2.170117 Proactive BP<\/p>\n The second one is for dbms_stats preference to control statistics extensions creation and you will have two suprises when following the link: If you want to go further, you have to open a SR, provide an opatch lsinventory (because it seems that Oracle Support Engineers are not able to get the lsinventory for the latest Proactive BP – the recommended one). And this is where the nightmare starts. The lastest we have here is for JAN16 Proactive Bundle Patch – 12.1.0.2.160119 Proactive BP.<\/p>\n I can’t wait for a relevant answer from MOS support engineers, so I got to look at the patch. It is very simple change actually.In DBMS_STATS it has to check whether AUTO_STAT_EXTENSIONS is ON before creating the column group. This is all in prvtstat.plb and if we are lucky there were no changes on it since the JAN16.<\/p>\n Before trying it, we can check conflicts in MOS. Here are the 3 patches I would like to apply, in their most recent release for Linux:<\/p>\n Yes, this is a nice feature of My Oracle Support: you can analyze the conflicts online.<\/p>\n The result of conflict analysis shows that we are lucky: Ready now to download the files: So the last patch we need, https:\/\/updates.oracle.com\/download\/21171382.html<\/a>, can be downloaded in its latest Proactive BP version, even if it is 1 year old. And don’t worry about its name: p21171382_12102160119DBEngSysandDBIM_Generic.zip<\/p>\n Don’t forget to run datapatch on your databases to be sure that the dictionary is patched.<\/p>\n And then connect to check the new Adaptive Statistics behavior:<\/p>\n Don’t install a 12c<\/em> database with only the software and documentation that was released 3 years ago. Lot of migration feedback has improved the stability of such migration, and this is implemented in patchset updates and MOS notes. Then, get the latest recommended software.<\/p>\n Don’t forget to run datapatch on all databases, even the newly created ones.<\/p>\n\n
Latest patchset of Oracle Database 12cR1: 12.1.0.2<\/h3>\n
\nYou can download 12.1.0.2 from the Patchset 21419221: https:\/\/updates.oracle.com\/download\/21419221.html<\/a><\/p>\nLatest Proactive Bundle Patch: JAN2017 BP<\/h3>\n
Adaptive statistics: patches 22652097 and 21171382<\/h3>\n
\nThe name is AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY because the change has been merged with some other changes on concurrent statistics
\nIt is available only for (listed in the non-chronological order of the platform list on MOS):<\/p>\n\n
<\/a><\/p>\n
\n<\/a><\/p>\n
\n<\/a><\/p>\ndatapatch and verification<\/h3>\n
$ORACLE_HOME\/OPatch\/datapatch\n \nSQL Patching tool version 12.1.0.2.0 Production on Mon Mar 27 09:18:47 2017\nCopyright (c) 2012, 2017, Oracle. All rights reserved.\n \nConnecting to database...OK\nBootstrapping registry and package to current versions...done\nDetermining current state...done\nAdding patches to installation queue and performing prereq checks...done\nInstallation queue:\n Nothing to roll back\n The following patches will be applied:\n 24732088 (DATABASE BUNDLE PATCH 12.1.0.2.170117)\n 21171382 (AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY)\n 22652097 (PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATISTICS FEATURES)\n \nInstalling patches...\nPatch installation complete. Total patches installed: 3\n \nValidating logfiles...done\nSQL Patching tool complete on Mon Mar 27 09:21:33 2017<\/code><\/pre>\nSQL> show parameter optimizer_adaptive\n \nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\noptimizer_adaptive_plans boolean TRUE\noptimizer_adaptive_reporting_only boolean FALSE\noptimizer_adaptive_statistics boolean FALSE\n \nSQL> select dbms_stats.get_prefs('AUTO_STAT_EXTENSIONS') from dual;\n \nDBMS_STATS.GET_PREFS('AUTO_STAT_EXTENSIONS')\n--------------------------------------------------------------------------------\nOFF<\/code><\/pre>\nSummary for 12.1.0.2 new install<\/h3>\n
\nA good source of information is Mike Dietrich blog (the best source you can find to benefit from lot of migration projects experience):<\/p>\n\n
\n
or https:\/\/updates.oracle.com\/download\/25115951.html<\/a> for Windows<\/li>\nUpdate for APR17<\/h3>\n