Tuesday Mar 03, 2015

There is still time to register for next week's Upgrade Workshop in Omaha!

Upgrade Workshop Banner

While the event in San Francisco is a sell-out (metaphorically speaking -- Upgrade Workshops are of course free of charge), we do still have room at next week's seminar in Omaha on Wednesday, March 11:

Oracle Database 12c Upgrade Seminar

This full-day event at the Embassy Suites in La Vista will include discussions of many ways to upgrade and migrate to Oracle Database 12c. I will also talk about Oracle Multitenant and other new features. We also include a guide for how to ensure you will have good performance after the upgrade.

I have visited Omaha as a tourist before, but this is my first upgrade workshop in the area. I hope to see you there! 

ORAchk 12.1.0.2.3 with new checks + Restart support

ORAchk

Never used or heard about ORAchk? Then it's time to give it a try! 

ORAchk 12.1.0.2.3 has now be released. ORAchk 12.1.0.2.3 has so many great features, especially more than 50 new health checks, OL7 Support - and is now aware of Oracle Restart environments.

New features include:

  • Linux on System Z (RHEL 6, RHEL 7, SuSE 12)
  • Oracle Enterprise Linux 7
  • Single Instance ASM Checks
  • Upgrade Validation checks for 12.1.0.2
  • Enhanced Golden Gate Checks 
  • Enterprise Manager Agent Checks
  • Enhanced Reporting to highlight checks that ORAchk cannot gain full visibility for
    (checks that require manual validation)
  • Improved health Score Calculation (you can now hit 100%)
  • Over 50 new health checks
  • Bug Fixes

Download the most recent version via MOS Note:1268927.1.

Learn more about the Upgrade Readiness Assessment with ORAckh in MOS Note: 1457357.1

--Mike 

PS: Updated the post on Mar 7, 2015, as ORAchk 12.1.0.2.3 became production!

Monday Mar 02, 2015

New Behaviour in Oracle Database 12c and 11.2.0.4: SELECT ANY DICTIONARY with reduced privilege set

You've just upgraded to Oracle Database 12c - but your favorite admin tool receives an ORA-1031: Insufficient Privileges after connection?

Then the reason may be the reduced set of privileges for the SELECT ANY DICTIONARY privilege. This privilege does not allow access to tables USER$, ENC$ and DEFAULT_PWD$, LINK$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, and XS$VERIFIERS. Actually such changes are not new. For instance in Oracle 10.1 we removed the access to  LINK$ in SELECT ANY DICTIONARY (well, this may have happened because the dblink's password was stored in clear text in LINK$ - a misbehavior which is fixed since Oracle 10.2).

Please be very careful with granting this privilege. Furthermore, you need to be aware that it can't be granted either through a role, nor is it included in the GRANT ALL PRIVILEGES.

Oracle 11.2:

Oracle 12.1:

Documentation can be found here: 

  1. SELECT ANY DICTIONARY Privilege No Longer Accesses Some SYS Data Dictionary Tables
    For better security, the SELECT ANY DICTIONARY system privilege no longer permits you to query the SYS schema system tables DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, and XS$VERIFIERS. Only user SYShas access to these tables, but user SYS can grant object privileges (such as GRANT SELECT ON USER$ TO sec_admin) to other users.
    .
  2. 2.9.2.5 Increased Security When Using SELECT ANY DICTIONARY

Please be aware that you can't query anywhere inside the database which privileges are included in the SELECT ANY DICTIONARY privilege as this is embedded in our code.

 --Mike

PS: Credits go to Andy Kielhorn for highlighting this to me and thanks to Gopal for providing me with the doc links

Friday Feb 27, 2015

Why you seriously can't wait for the second release!

Premier Support for Oracle 11.2 has ended 4 weeks ago at 31-January-2015. 
Read here if you didn't know or don't believe it.
https://blogs.oracle.com/UPGRADE/entry/premier_support_for_oracle_11

I think most Oracle DBAs are aware of it. And I have stressed the topic about the need to upgrade to Oracle Database 12.1.0.2 a lot in the past months via the blog, in workshops and in discussions and customer meetings.

But there are still plenty of people out there you would like to wait for Oracle Database 12.2, the so called "second" release. From looking backwards I can understand this thinking. Neither Oracle 9.0 nor Oracle 10.1 were the best and most stable releases ever. If you waited a while then you could expect at least the first or sometimes even the 2nd patch set for the SECOND release being available. And then most people started going live. And some did wait for the terminal patch set.

But this has changed. You can't seriously wait anymore for the second release. Why? There are several reasons and it's fairly easy to explain.

  • Reason 1 - Every release is a full release
  • Reason 2 - Every release has a significant number of changes
  • Reason 3 - Every release has a significant number of new features
  • Reason 4 - Oracle 12.1.0.2 is the TERMINAL patch set 
  • Reason 5 - The time span between releases has grown to large
  • Reason 6 - Important application providers will certify Oracle 12.1.0.2

 

Especially the Reason 5 is very important. You can't seriously wait for Oracle Database 12.2 as you will potentially see a period with no bug fixing support for Oracle 11.2. 

So let's be honest:
You don't wait for the second release. You'll wait at least for the first patch set.
Patch Sets in the past got released roughly 12 months after the initial drop has been put out (please don't get this wrong: I'm not saying that 12.2.0.2 will be released 12 months after 12.2.0.1 - I just try to project from the past!).

We announced already the planned availability of Oracle 12.2 for H1CY16 (first half of calender year 2016).
See the Release Schedule MOS Note:742060.1for further details.

And keep in mind that this is a plan and no fixed schedule. So let's project the usual patch set cycle from the past. Then we may be in the 2nd half of 2017. If you start your tests (I hope you'll test :-) ) by then you may be ready to go live in 2018 - and Extended Support for Oracle 11.2.0.4 will end 31-January-2018.

Ouch ...

Look at the release cycles:

Oracle Release Cycle

It has grown from 18 months in the past to 45 months for Oracle Database 12.1.0.1. For Oracle Database 12.2 we may be at 30-36 months based on the currently announced plan.

Any further questions?

Be smart and transform from "We'll go live on the 2nd release only" into "We'll go live with the current release's first or terminal patch set!". This will be Oracle Database 12.1.0.2. There's a reason why large application providers such as SAP will announce support for Oracle Database 12.1.0.2 soon.

--Mike 

Thursday Feb 26, 2015

Oracle In-Memory Advisor with Oracle Multitenant? Issues?

Do you want to use the Oracle In-Memory Advisor in an Oracle Multitenant environment?

This is possible but with the current version it will require a workaround. The next available version of the IM Advisor is designated to support it without any workarounds.

At the moment the IM Advisor does not install into the CDB$ROOT container. But it can be installed into any PDB.

  • Create the user IMADVISOR in the PDB as local user first
  • No objects must be placed into the IMADVISOR schema but the user must have a default and a temporary tablespace
  • Install the IM Advisor locally in this PDB by using the install script imstimadv.sql
    • The script will detect the presences of the IMADVISOR schema. If the IMADVISOR schema is empty, it will proceed. It will then ask you to press ENTER instead of prompting you to set the password and default tablespace. Unless you setup the IMADVISOR user with an automated authentication method, it will still prompt you for the IMADVISOR's password when it connects to the IMADVISOR user

An issue which I learned about in the past days:

ORA-24817 get raised when using the IM Advisor.'s imadvisor_fetch_recommendations script unless you do not have a massive shared pool configured. The script does a SET LONGCHUNKSIZE 2000000000; and this needs to be reduced.
There has been a bug filed for it and I think it's supposed to be fixed in the next release of the IM Advisor.

-Mike 

Tuesday Feb 24, 2015

Oracle In-Memory Advisor now available

Oracle In-Memory is such a great feature - but often the challenge you'll face is the tricky question:
Which of your tables and/or partitions should you mark for In-Memory column store availability? 

And the answer is now easier to find as the new In-.Memory Advisor is available via download from MyOracle Support:

The Advisor produces a report identifying the objects that should be placed In-Memory for maximum benefit, along with a SQL*PLUS script which implements those recommendations. It can be run on Oracle Database 11.2.0.3 and above. And of course the recommendations can be implemented on Oracle Database 12.1.0.2. (and newer).

Important to know: The In-Memory Advisor is licensed as part of the Database Tuning Pack.

Further information can be found at: OTN

In case you have obtained a pre-release version of the Advisor, please discard it and replace it with the production version 

--Mike 

Monday Feb 23, 2015

Upgrade the Operating System in a RAC Environment

Last week at the upgrade workshop in Budapest a customer had a interesting and - I believe - not uncommon question.

How can I upgrade my Linux Operating System in my RAC environment without taking the entire cluster down?
In this specific case the customer wanted to upgrade from RHEL 5.10 to RHEL6 or RHEL7.

Let's assume it's the typical 2-node-RAC where one wants to upgrade in a rolling fashion. And the data is stored within ASM.

  1. Drain Node 1 (i.e. take the workload off) - this will be the node getting upgraded first
  2. Remove Node 1 from the  cluster (deleteNode procedure)
  3. Upgrade the OS (which is most likely a reimaging of the node). If the OS upgrade does not wipe out the entire server you can follow MOS Note:1559762.1 as it shows an OS upgrade from OL 6.2 to 6.4 which leave the Oracle installations intact) 
  4. Add Node 1 back to the cluster (addNode procedure)
  5. Extend the Database Home to Node 1 using either cloning or addNode
  6. Make the database available on the newly added Node 1 and drain Node 2
  7. Repeat steps 2-6 for Node 2 
  8. Ideally now you'll perform a rolling upgrade of Oracle GI to Oracle Database 12c. Please apply the most recent PSU as well.
  9. Furthermore you may now look into upgrading your databases to Oracle Database 12.1.0.2 as well.
See the following documentation: 
 --Mike  

Friday Feb 20, 2015

Upgrade Workshops coming in March in North America

The days are getting longer, the snow is still getting deeper, and the upgrade workshops keep coming in North America! Remember that you can always go to http://events.oracle.com and plug in a keyword of "upgrade" to find upcoming live events. Roy will be the speaker at some of these events, while others will be delivered by some of our top presales technical folks like Dan Wittry:

March 5: Oracle 12c Database Upgrade Seminar-Detroit (Troy, MI)

March 11: Oracle Database 12c Upgrade Seminar - Omaha (La Vista, NE)

March 12: Oracle Database 12c Upgrade Seminar - Redwood Shores, CA Sold out!

March 17: Oracle 12c Database Upgrade Seminar-Calgary

March 19: Oracle Database 12c Upgrade Seminar - Seattle (Bellevue, WA)

March 19: Oracle Database 12c Upgrade Seminar Tampa

As you can see, we do sometimes reach capacity and have to shut down registrations. If you are interested in any of these events, sign up now and don't be left out in the cold (literally in the north, or metaphorically in other regions).

Downgrade Oracle Restart 12c - Grid Infrastructure only?

Oracle RestartCan you downgrade your Oracle Restart installation from Oracle 12c back to Oracle 11g?

Actually there's no real direct downgrade supported for Oracle Restart. But of course there's a way to do it.

Basically it is: 

  • Deconfigure Oracle Restart in 12c
  • Configure Oracle Restart in 11g 

But wait a minute. It is very important to know if you have upgraded your database already. If that is the case then first you MUST downgrade your database(s) as you can't manage a higher version Oracle Database with a lower version Clusterware.

So first of all, please downgrade your Oracle database(s) first: 

At the next stage you'll need to "downgrade" the Oracle Clusterware resp Grid Infrastructure for Oracle Restart: 

Before you attempt this you'll need to deconfigure the Restart resources - and please be aware that here's a small difference in commands between Oracle 12.1.0.2 and Oracle 12.1.0.1.

This is from the documentation for Oracle 12.1.0.2

  • Deconfigure Oracle Restart:
    • Log in as root
    • cd /u01/app/12.1.0.2/grid/crs/install
    • roothas.sh -deconfig -force
  • Once this is complete you can now deinstall the Grid Infrastructure with the deinstall tool
  • Then you will need either to reinstall the previous - for instance Oracle 11.2.0.4 - Grid Infrastructure or - if it's still present on the machine - reconfigure it by running root.sh from the previous Clusterware's home
  • And finally reconfigure the database(s) again with Clusterware
    • $ srvctl downgrade database -d db-unique-name -o oraclehome -t to_version

.
In case you'd plan to do this exercise back from Oracle 12.1.0.1 instead then you'll have different steps to follow to deconfigure Oracle Restart 12.1.0.1:

  • Deconfigure Oracle Restart 12.1.0.1
    • # cd /u01/app/12.1.0.1/grid/crs/install
    • # /u01/app/12.1.0.1/grid/perl/bin/perl /u01/app/12.1.0.1/grid/crs/install/roothas.pl -deconfig
      .

Regarding ASM backwards compatibility please be aware that changing the COMPATIBLE attributes within ASM will get you in trouble - see the Oracle Database Upgrade Guide for further information:
http://docs.oracle.com/database/121/UPGRD/afterup.htm#CEGJDCDD

--Mike

PS: See bug18160024 or the GI install guide, section A11.4 for the Standalone GI downgrade instructions from 12.1.0.2.0. 

Wednesday Feb 18, 2015

Grid Infrastructure PSU Jan 2015 - Am I too intolerant?

Germans are not only known for being precise and timely - but sometimes also for being too direct. Well, Roy could tell you stories ... and I always honor his politeness :-)

Enough about stereotypes. I work with a customer at the moment on their 12c upgrades. And I did recommend the most recent PSU (Patch Set Updates) for their Grid Infrastructure environments running Oracle Restart. Same of course for the database homes but this blog post will just name some findings I've had the other night when trying to apply the January 2015 GI PSU to my Oracle Restart environment.

First of all, start here:

  • Drill down from MOS Note:161818.1 (Click on the Release Link for 12.1.0!) into
  • Oracle 12.1 Support Status and Alerts MOS Note:1565065.1 and further into 
  • 12.1.0.2 Patch Set - Availability and Known Issues MOS Note:1683799.1 to get
    • Recommendations for patches on top of Oracle Database 12.1.0.2 - and of course for Grid Infrastructure (GI), Engineered Systems and Database In-Memory as well.

For GI you'll get the following recommendations:

Grid Infrastructure

Document Description Rolling RAC Patch Download
Note:20132450.8 Combo of 12.1.0.2.2 OJVM PSU and 12.1.0.2.2 GI PSU (Jan 2015) Part Patch:20132450
Note:19954978.8 12.1.0.2.2 (Jan 2015) Grid Infrastructure Patch Set Update (GI PSU) Yes Patch:19954978

Well, first question:
Do you need the Combo Patch or the non-Combo? 

My personal recommendation: Take the non-Combo Patch as the combo patch includes not only the GI PSU, but also the Database PSU, the OJVM Patch and some other things you will not need for patching your Grid Infrastructure only. Of course I can see the benefit of downloading just one big piece and applying everything all together to my environment. But first of all parts of the patch (speaking of OJVM) are not rolling applicable - and second I'd like to control and script things separately. But please feel free to see and do this in a different way.

So I did download Patch:19954978 to my environment. Unzipped it. 
.

You all know it already - you will need a new OPatch!

Of course my OPatch version is too old. You will need at least OPatch 12.1.0.1.5 - the most recent one is OPatch 12.1.0.1.6 - and you simply download it via MOS patch 6880880 and install it after removing the old directory into your $ORACLE_HOME. 
.

First learning experience? OPatch doesn't do anything without a response file.

The reason for this (at least for me as I don't patch daily) new requirement seems to be the new opatchauto call which scripts the entire apply process in a silent way. Well, at least the patch readme tells me what to do. Please read MOS Note:966023.1 to learn about how to create this response file.

If you do not have the OCM response file (ocm.rsp), see the following My Oracle Support Document 966023.1 How To Create An OCM Response File For Opatch Silent Installation.

I did create it with OCM OFF as I don't see a benefit in my environment for using OCM. And just on the side: I was a bit worried that this note does not contain the new opatchauto syntax but instead lists an example (which is always good and nice and helpful) from the old days:

$ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /u02/unconfig.rsp

-

Second learning experience? Analyze analyzes ... not everything!

In the readme under 2.2 you'll find also the requirement to run an anylze for conflict detection and resolution first. Maybe I'm too naive as I'm so happy with all the stuff ORAchk (credits for this gem of a tool go to Sandesh Rao's team - I will write something about it later) can deliver - and expected too much. In my case the anylyze signaled: All good, sky is bright and nice and clear - ready to fly!

#GRID_HOME/OPatch/opatchauto apply <UNZIPPED_PATCH_LOCATION>/19954978 -analyze -ocmrf <ocm response file>

You'll read below why my assumption was incorrect.
.

Third learning experience? An error is an error is an error!

Can you expect a patch to get applied correctly in the first run?

opatchauto apply /oradata/grid/12.1.0/19954978 -oh /oradata/grid/12.1.0 -ocmrf /oradata/grid/12.1.0/OPatch/ocm/bin/emocmrsp 

I did. Well ... mine failed. Must be Murphy's Law See the log below - I have marked the failing part in red.

2015-02-16_19-45-09 :
Failed to run this command :
/oradata/grid/12.1.0/OPatch/opatch napply -phBaseFile /tmp/OraGI12Home1_oracle_patchList -local  -invPtrLoc /oradata/grid/12.1.0/oraInst.loc -oh /oradata/grid/12.1.0 -silent -ocmrf /oradata/grid/12.1.0/OPatch/ocm/bin/emocmrsp
oracle.opatchauto.gi.RunExecutionSteps.runGenericShellCommands(RunExecutionSteps.java:913)
oracle.opatchauto.gi.RunExecutionSteps.processAllSteps(RunExecutionSteps.java:215)
oracle.opatchauto.gi.GIPatching.processPatchingSteps(GIPatching.java:544)
oracle.opatchauto.gi.OPatchautoExecution.main(OPatchautoExecution.java:141)
Command "/oradata/grid/12.1.0/OPatch/opatch napply -phBaseFile /tmp/OraGI12Home1_oracle_patchList -local  -invPtrLoc /oradata/grid/12.1.0/oraInst.loc -oh /oradata/grid/12.1.0 -silent -ocmrf /oradata/grid/12.1.0/OPatch/ocm/bin/emocmrsp" execution failed:
UtilSession failed:
Prerequisite check "CheckSystemSpace" failed.

Log file Location for the failed command: /oradata/grid/12.1.0/cfgtoollogs/opatch/opatch2015-02-16_19-45-03PM_1.log

2015-02-16_19-45-09 :

--------------After fixing the cause of failure you have two options shown below:
Run 'opatchauto resume'
  or
Manually run the commands listed below
---------------------------------------------------------------------------------

/oradata/grid/12.1.0/OPatch/opatch napply -phBaseFile /tmp/OraGI12Home1_oracle_patchList -local  -invPtrLoc /oradata/grid/12.1.0/oraInst.loc -oh /oradata/grid/12.1.0 -silent -ocmrf /oradata/grid/12.1.0/OPatch/ocm/bin/emocmrsp (Run as oracle) - (TRIED BUT FAILED)

.
Now the question is:
How much space does it really require to apply this PSU? 

At this point I was wondering as the analyze passed successfully - and I couldn't find anything in the readme about specific space requirements. My disk had roughly 8GB of free space - and as my GI Restart installation's footprint was around 6GB I don't had any bad thoughts. 
.

Forth learning experience? Always see the logfile first ...

Just rerunning the mentioned command gave me the correct information (just wondering why OPatch couldn't tell me this during the failed run?). So I did execute as oracle user:

/oradata/grid/12.1.0/OPatch/opatch napply -phBaseFile /tmp/OraGI12Home1_oracle_patchList -local  -invPtrLoc /oradata/grid/12.1.0/oraInst.loc -oh /oradata/grid/12.1.0 -silent -ocmrf /oradata/grid/12.1.0/OPatch/ocm/bin/emocmrsp

Receiving finally this result:

Verifying environment and performing prerequisite checks...
Prerequisite check "CheckSystemSpace" failed.
The details are:
Required amount of space(12773.858MB) is not available.
UtilSession failed:
Prerequisite check "CheckSystemSpace" failed.
Log file location: /oradata/grid/12.1.0/cfgtoollogs/opatch/opatch2015-02-16_19-49-04PM_1.log

Ouch - 12.7GB of free space??? Really? Seriously?? Now I was scared. 

The patch for Linux x86-64 has a tip size of 873 MB - but unzipped it takes 3.3 GB. So why does it require 12.7 GB of free space? Actually I don't know the answer yet but I will follow up here as soon I know the details. Colleagues in Development ensure that especially on AIX you will need even more free space, such as in the area of 22GB!!!

One of the reasons for such a huge space requirement may be this:

Starting with 10.2, Opatch does not backup only the affected modules, it also takes a backup of the complete affected libraries to $ORACLE_HOME/.patch_storage/<patchid>/backup/<lib_directory_name>/<library_name>.

.

Fifth learning experience? There are workarounds ...

Blogs are sometimes VERY helpful. I found two helpful entries from external bloggers (but didn't bookmark them so I can't credit them here - sorry).

/oradata/grid/12.1.0/OPatch/opatch napply -phBaseFile /tmp/OraGI12Home1_oracle_patchList -local  -invPtrLoc /oradata/grid/12.1.0/oraInst.loc -oh /oradata/grid/12.1.0 -silent -ocmrf /oradata/grid/12.1.0/OPatch/ocm/bin/emocmrsp OPatch.SKIP_VERIFY_SPACE=true

Of course you can't give this variable at the end to opatchauto as it wouldn't understand.
.

Final learning lesson? Clean up is a great idea!

This is not as simple as I did expect it. Of course, it's not. But please see this MOS Note:550522.1 - How To Avoid Disk Full Issues Because OPatch Backups Take Big Amount Of Disk Space
.

Famous Last Words?

Patches are great. In fact YOU NEED TO PATCH. Take the PSUs. There's no way out as it will help you to avoid plenty of known issues. But I hope that my above learning experience may help you to sail around one or the other pitfall :-)

--Mike

Monday Feb 16, 2015

Oracle Fail Safe 4.1.1 released - supports Multitenant

Oracle Fail SafeOracle Fail Safe 4.1.1 is now released. It will be included in future Oracle Database 12c media packs.  Customers can download the kit and documentation from the Oracle Technology Network (OTN) now by starting at the main Fail Safe page.  

The main changes in this release are:

  • Basic multi-tenant (container database, or CDB) support
  • This release of Oracle Fail Safe adds support for the container database feature that was introduced in Oracle Database 12c. Fail Safe will recognize that a database is a root container and will start and stop individual pluggable databases owned by the container database. When a database is failed over or moved to another node in the cluster, Oracle Fail Safe will start each pluggable database using the state that was saved by the last SQL ALTER PLUGGABLE DATABASE ALL SAVE STATE command. Oracle database 12c patch set 1 (12.1.0.2) is required to have the ability to save the state of the pluggable databases. 

  • Improved net (TNS) configuration
  • Cluster Shared Volume (CSV) support

 All documentation for this release, including the release notes, can be found here.

--Mike

Friday Feb 13, 2015

Is it always the Optimizer? Should you disable Group By Elimination in 12c?

I wouldn't say it's always the optimizer - but sometimes one or two tiny little things are broken making it necessary to turn off new functionality for a while.

Please don't misinterpret this posting!
As far as I see (and I really do work with customers!) I'd state the Oracle Database 12.1.0.2 Optimizer is more stable, faster and predictable compared to 11.2.0.x. Our Optimizer Architects and Developers have done a great job. But with all the complexity involved sometimes it takes a few fixes or incarnations until a great new feature really matures. The Group-By-Elimination feature in Oracle Database 12c seems to be such a candidate. 

What does the feature do? A simple example demonstrates the feature.

First the elimination is OFF (FALSE): 

SQL> explain plan for
  2  select /*+ opt_param('_optimizer_aggr_groupby_elim', 'false')*/
  3   dummy, sum(cnt)
  4    from (select dummy,
  5                 count(*) cnt
  6            from dualcopy
  7           group by dummy)
  8   group by dummy
  9  ; 
Explained

-----------------------------------------
|  Ld | Operation            | Name     |
-----------------------------------------
|   0 | SELECT STATEMENT     |          |
|   1 |  HASH GROUP BY       |          |
|   2 |   VIEW               |          |
|   3 |    HASH GROUP BY     |          |
|   4 |     TABLE ACCESS FULL| DUALCOPY |
-----------------------------------------


And now it's ON (TRUE):

SQL> explain plan for
  2  select /*+ opt_param('_optimizer_aggr_groupby_elim', 'true')*/
  3   dummy, sum(cnt)
  4    from (select dummy,
  5                 count(*) cnt
  6            from dualcopy
  7           group by dummy)
  8   group by dummy
  9  ;
Explained
 
---------------------------------------
| Ld  | Operation          | Name     |
---------------------------------------
|   0 | SELECT STATEMENT   |          |
|   1 |  HASH GROUP BY     |          |
|   2 |   TABLE ACCESS FULL| DUALCOPY |
---------------------------------------

By comparing the two execution plans you'll see the difference immediately.

But there seem to be a few issues with that new feature such as several wrong query result bugs. The issues will be be tracked under the non-public bug20508819. Support may release a note as well.

At the moment we'd recommend to set: 

"_optimizer_aggr_groupby_elim"=false

--Mike 

Hands-On-Lab "Upgrade, Migrate & Consolidate to Oracle Database 12c" available for DOWNLOAD now!

Wow ... that was a hard piece of work. Roy put a lot of effort into getting our Hands-On-Lab on OTN for download. We promised to have it available after OOW - or at least a description how to create it by yourself. And finally it's there. Find it here:

A few important things to mention before you start the download: 

  • It's a Virtual Box image
  • You will need to install Oracle Virtual Box first - and please install also the VBox Extensions
  • Your PC must have a 64-bit host operating system
  • You need to enable Virtualization options in your computer's BIOS
  • You PC should have at least 4GB of RAM - having 8GB is very helpful
  • A fast disk (SSD) will speed up things
  • The instructions are available for download but are included in the download as well
  • The lab will guide you through the following tasks:
    1. Upgrade an 11.2.0.4 database to Oracle 12.1.0.2
    2. Plug in this database into a 12.1.0.2 CDB
    3. Migrate an 11.2.0.4 database with Full Transportable Export into another PDB
    4. Unplug an 12.1.0.1 PDB and plug/upgrade it into an 12.1.0.2 CDB

You'll find a picture as screen background inside the VBox image always giving you guidance about "what to accomplish" and "how to switch environments".

Enjoy :-)

--Mike 

 

 

Monday Feb 09, 2015

Virtual Technology Summit with Hands-On Labs!

VTS Virtual Technology Summit

If you are at all interested in upgrade to Oracle Database 12c, you really should sign up for the Virtual Technology Summit for February. This is a "pseudo-live" event with recorded webcasts and live experts online to answer questions. Even better, it includes a hands-on lab that will take you through the process of:

  1. Upgrading from 11.2.0.4 to 12.1.0.2
  2. Plugging that upgraded database in as a PDB
  3. Migrating a second 11.2.0.4 database to a second PDB using the new Full Transportable Export/Import feature

The Virtual Technology Summit will run three times: 

and it has content for developers and sysadmins as well as DBAs.

So, sign up now and get a head start by downloading the hands-on lab VM image here:

We think you will find the VM environment really useful to keep around for those times when you want to try something out, without having to install binaries or create databases. 

-Roy 

Friday Feb 06, 2015

New MOS Note:1962125.1 - Overview of Database Patch Delivery Methods

Usually I don't announce MOS Notes but this one may be very helpful to sort out between all the different patches available these days for the database only. From Patch Sets to PSUs to SPUs to Interim Patches to Bundle Patches and so on.

Plus it includes also recommendations for testing and if you should apply them on a regular basis.

A very important MOS Note for most DBAs:

MOS Note:1962125.1
Overview of Database Patch Delivery Methods

MOS Note - Patch Delivery Methods

--Mike 

Thursday Feb 05, 2015

Oracle Multitenant: New SQL Container Clause

Tiny little enhancement in Oracle Database 12.1.0.2
The new CONTAINER clause to access data from different containers within one SQL statement. This may be very helpful, especially in case of schema consolidation. Similar things could have be done in Oracle 12.1.0.1 already by using database links - but resulting in way more complicated SQLs.

This is the new clause: 

SELECT ename FROM CONTAINERS(scott.emp) WHERE CON_ID IN (45, 49); 

See the documentation for more info about it.

--Mike 

Tuesday Feb 03, 2015

How to migrate to Unified Auditing?

Lock

What is Unified Auditing and is it on by default?

Unified Auditing is the new auditing facility since Oracle Database 12c. But the "old" auditing is still working. And there are a few things to mention if you'd like to make the right choice. I have written some things about it a while ago but as I discovered yesterday my previous blog post (https://blogs.oracle.com/UPGRADE/entry/unified_auditing_is_it_on)  doesn't satisfy all my needs.

The initial motivation to move towards the new Unified Audit trail is audit performance. The audit records will be written into the read-only table AUDSYS in SYSAUX tablespace. But there are other benefits such as no dependency on init.ora parameters, one location - one format, and close interaction with Oracle Audit Vault and Database Firewall. And of course tiny things such as the immediate write, which avoids losing any audit records during an instance crash.

Audit records are coming from those sources:

  • Audit records (including SYS audit records) from unified audit policies and AUDIT settings
  • Fine-grained audit records from the DBMS_FGA PL/SQL package
  • Oracle Database Real Application Security audit records
  • Oracle Recovery Manager audit records
  • Oracle Database Vault audit records
  • Oracle Label Security audit records
  • Oracle Data Mining records
  • Oracle Data Pump
  • Oracle SQL*Loader Direct Load 

In addition to user SYS all users having the roles AUDIT_ADMIN and AUDIT_VIEWER can query the AUDSYS table.

After upgrade to Oracle Database 12c Unified Auditing is not enabled by default in order to prevent customers having "old" auditing on already from enabling both auditing facilities at the same time. This is something you need to be aware of: Unified Auditing can be on together with the "old" auditing at the same time

Check if Unified Auditing is linked into the oracle kernel;

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

PARAMETER         VALUE
----------------  ---------- 
Unified Auditing  FALSE

To link it into the kernel or enable it use the following commands/actions - and the documentation states that you'll have to shut down the listener and restart it again afterwards:

  • UNIX
    • cd $ORACLE_HOME/rdbms/lib
      make -f ins_rdbms.mk uniaud_on ioracle
  • Windows
    • Rename the file %ORACLE_HOME%/bin/orauniaud12.dll.option to %ORACLE_HOME%/bin/orauniaud12.dll

The tricky part is now that - even though Unified Auditing is not enabled by default - Unified Auditing is enabled in a Mixed Mode, i.e. there are two auditing policies enabled - but the option is not linked into the kernel.

To disable these policies you'll execute:

  • SQL> noaudit policy ORA_SECURECONFIG;
  • SQL> noaudit policy ORA_LOGON_FAILURES;

Don't get me wrong: This is not a recommendation to disable Unified Auditing. I just would like to explain what's on and the possibilities to turn things into the desired direction. The documentation says about Mixed Mode:

Mixed mode is intended to introduce unified auditing, so that you can have a feel of how it works and what its nuances and benefits are. Mixed mode enables you to migrate your existing applications and scripts to use unified auditing. Once you have decided to use pure unified auditing, you can relink the oracle binary with the unified audit option turned on and thereby enable it as the one and only audit facility the Oracle database runs. 

How do you enable a Unified Auditing Policy?

The documentation offers a straight forward tutorial (which is a bit EM driven):
https://docs.oracle.com/database/121/TDPSG/tdpsg_auditing.htm#TDPSG50528

How to change between IMMEDIATE and QUEUED WRITE mode?

For a performance evaluation please see Szymon's blog post at the CERN blogs. To switch between the different modes please see the Oracle Documentation:

  • To use immediate write mode use this procedure:
  • BEGIN
     DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
      DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
      DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE, 
      DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE);
    END;
    /
    
  • To use queued write mode run this procedure:
  • BEGIN
     DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
      DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, 
      DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE, 
      DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE);
    END;
    /

The size of the queue by default is 1MB. If you'd like to change it (maximum: 30MB) the initialization parameter UNIFIED_AUDIT_SGA_QUEUE_SIZE has to be changed.

What happens now to the traditional AUDIT_TRAIL parameter and what effect does it have?

AUDIT_TAIL will still trigger and direct the "old" auditing facilitiy (SYS.AUD$ for the database audit trail, SYS.FGA_LOG$ for fine-grained auditing, DVSYS.AUDIT_TRAIL$ for Oracle Database Vault, Oracle Label Security, and so on). So be aware to have both auditing facilities on at the same time as this won't make much sense. Our recommendation since Oracle Database 11g is generally to set AUDIT_TRAIL in every 11g/12c database explicitly to the value you want. Otherwise it could always happen (and happens many times) that your database accidentally writes audit records into AUD$. Reason why this happens so often: the default setting for AUDIT_TRAIL since Oracle Database 11g is "DB" unless you change this via the non-standard parameter listening in the DBCA (Database Configuration Assistant).

Therefore always set AUDIT_TRAIL explicitly to the value you want to prevent the database from accidental auditing.

Summary - Steps to migrate to Unified Auditing?

  1. Turn off traditional auditing with AUDIT_TRAIL=NONE
  2. Link Unified Auditing into the kernel or enable it on Windows
  3. Define your auditing policies 
  4. Monitor it with the views UNIFIED_AUDIT_TRAIL and in multitenant environments with CDB_UNIFIED_AUDIT_TRAIL
A final question remains unanswered:
What happens to your existing audit records in AUD$ and the other auditing tables?

Actually I can't answer this question but to me there seems to be no way to migrate existing audit records into the new Unified Auditing facility. But I don't think that this will cause any issues as you can keep and safely store the contents of the traditional auditing. They don't get overwritten or deleted during an upgrade.

Further information required?

-Mike 

Thursday Jan 29, 2015

Upgrade to 12c and Plugin - one fast way to move into the world of Oracle Multitenant

What is the best way to move into Oracle Multitenant (which includes Single Tenant with just one PDB) as well?

This question came up on the blog, it's part of the Multitenant chapter in our big slide deck but let me elaborate this a bit more in detail. Of course there are several ways to move a database into a Single/Multitenant environment such as Data Pump but one fact is for sure: You can't convert a stand-alone database to become a container database (CDB$ROOT). A container database has to be built up from scratch as first step. Please use the DBCA to create it as this is the easiest path.

Upgrade and Plugin

But how can your stand-alone database be moved now?

  1. Upgrade your stand-alone database to Oracle Database 12c, ideally to Oracle 12.1.0.2 with the newest PSU included already. Upgrade it to exactly the same version including the same PSU as your container database (CDB) got created from.
    --
  2. Then start your stand-alone database in read-only mode - of course at this step you'll encounter downtime:
    startup open read only;
    --
  3.  Generate the XML description file – this file will contain the information describing the database structure. To create it the database has to be in read only mode:
    exec DBMS_PDB.DESCRIBE('/tmp/pdb1.xml');
    --
  4. Shutdown the database
    shutdown immediate
    --
  5. Change into your new CDB and do a compatibility check:
    SET SERVEROUTPUT ON
    DECLARE
    compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/tmp/pdb1.xml', pdb_name => 'PDB1') WHEN TRUE THEN 'YES' ELSE 'NO'
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE(compatible);
    END;
    /
    --
  6. Plugin your stand-alone database (in my case with a new name PDB1):
    create pluggable database PDB1 using '/tmp/pdb1.xml' nocopy tempfile reuse;
    --
    Be aware that using the NOCOPY option will require that you have a valid backup of your database. If you'll use the COPY option instead you will need additional disk space. When using NOCOPY files will remain at their location. When using COPY you'll combine it with the FILE_NAME_CONVERT option, converting source to destination paths.
    It's always a recommendation to keep the TEMP tablespaces. In case you'd like to remove then you'll have to drop the tablespace and the tempfile before. Otherwise there will be a reference in the XML manifest file making it impossible to plug in your new PDB.

    --
  7. Connect to this new PDB1 and perform sanity operations:
    alter session set container=PDB1;
    @?/rdbms/admin/noncdb_to_pdb.sql

    This script will do required sanity operations to connect the PDB with the CDB. It will do things such as change object id's, mark common objects etc. Its runtime can vary between 5 minutes and several hours depending on the complexity of the former stand-alone's database data dictionary. It will do recompilations of many invalid objects as well. Please test it carefully. 
    If this step gets skipped the PDB can be open in read-only mode only.
  8. ----

  9. Now the database is plugged in – but not open yet. It will need to be started.
    startup
    show pdbs

    exit
    --
  10. To connect to the consolidated PDB1 from the command prompt the following command syntax needs to be used:
    sqlplus "sys/oracle@pdb1 as sysdba"
    As an alternative you could use the EZconnect syntax:
    sqlplus "sys/oracle@//localhost:1521/pdb1 as sysdba"

1.
-Mike

Thursday Jan 22, 2015

Non-CDB architecture of Oracle databases is DEPRECATED since Oracle Database 12.1.0.2

Beginning with Oracle Database 12.1.0.2 a non-CDB architecture is deprecated.

non-CDB deprecated in Oracle 12.1.0.2

--

What does this mean?

Deprecation first of all does not mean "desupported". It means in this case that you of course can have still stand-alone Oracle databases as you know the architecture from previous releases. But you can also have a single-tenant deployment (a CDB with one PDB - no Multitenant license required) or multitenant databases (a CDB with up to 252 PDBs - Multitenant Option license required).

But in a future Oracle release single-tenant and multitenant databases only may be allowed. Right now there's no information available when this might happen. --

Which features are not supported at the moment?

  • Database Change Notification
  • Continuous Query Notification (CQN)
  • Client Side Cache
  • Heat Map
  • Automatic Data Optimization
  • Oracle Streams
  • Oracle Fail Safe
  • Flashback Pluggable Database (Flashback Database works but will flashback CDB$ROOT including all PDBs)
  • DBVERIFY
  • Data Recovery Advisor (DRA)
  • Flashback Transaction Backout 

-Mike 

---

From the Upgrade Guide:
By deprecate, we mean that the feature is no longer being enhanced but is still supported for the full life of the 12.1 release. By desupported, we mean that Oracle will no longer fix bugs related to that feature and may remove the code altogether. Where indicated, a deprecated feature may be desupported in a future major release

Wednesday Jan 21, 2015

Oracle Supplied Objects Listing in upobjxt.lst ... ... Oracle Supplied Users Listing in upuserxt.lst

Maybe not important to many people but as I didn't find much about it via a search I'd thought I post something about the new upobjxt.lst and upuserxt.lst files. Since Oracle Database 12.1.0.1 we deliver these files in ?/rdbms/admin .

All Oracle supplied objects are kept in an external table format.
The file's name is: upobjxt.lst

If you'd like to access it you may do the following:

  1. Create a directory object:
    CREATE OR REPLACE DIRECTORY ext_tab_data AS '/u01/app/oracle/product/12.1.0.2/rdbms/admin/';
    --
  2. Define the external table to query it:
    CREATE TABLE oracle_supplied_ext (
      owner       VARCHAR2(31),
      name        VARCHAR2(127),
      placeholder VARCHAR2(20),
      object_type number
    )
    ORGANIZATION EXTERNAL (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY ext_tab_data
      ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL
        (
          owner             CHAR(31),
          name              CHAR(127),
          placeholder       CHAR(20),
          object_type       CHAR(2)
        )
      )
      LOCATION ('upobjxt.lst')
    )
    PARALLEL 5
    REJECT LIMIT UNLIMITED;

    --
  3. Now you can query from the table oracle_supplied_ext
    For example:
    select count(*), object_type
    from oracle_supplied_ext
    group by object_type
    order by 2;
    --
  4. Would you like to match object types to object codes? Try this one  ...:
    select count(o.type#), o.type#,d.object_type
     from dba_objects d, obj$ o
     where d.object_id=o.obj#
      group by o.type#,d.object_type
      order by 2;
    Object mapping can be found also in the definition of OBJ$ in the dcore.bsq file

All Oracle supplied users are kept in an external table format in another file named: upuserxt.lst

-Mike

Tuesday Jan 20, 2015

Some Tokyo Impressions - January 2015

Just some impressions from our week in Tokyo, Japan in January 2015.

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

Tokyo 2015

-Mike 

Tuesday Jan 13, 2015

New MOS Notes on Database Upgrades for 12c with or without Oracle Multitenant

Please find some new MyOracle Support (MOS) Notes about Database Upgrades to Oracle Database 12c, with or without Oracle Multitenant:

Credits for the CDB/PDB Notes go to my colleague, Krishnakumar Kunka  :-)

-Mike 

Monday Jan 12, 2015

Upcoming Upgrade Workshops Jan/Feb 2015

The new year will start with a good bunch of Upgrade, Migrate & Consolidate to Oracle Database 12c workshops in Japan, USA, Canada and Europe: 

January 20, 2015  
Tokyo,
Japan
Oracle Database 12c Upgrade Seminar
including Hands-On for Partners
by invitation only



February 04, 2015  
Irving (Dallas), TX
USA
Oracle Database 12c Upgrade Seminar - Dallas
Register
.

February 05, 2015  
Houston, TX
USA
Oracle Database 12c Upgrade Seminar - Houston
Register
.

February 09, 2015  
Bratislava,
Slovakia
Oracle 12c Database Upgrade Seminar - Bratislava
Register
.

February 10, 2015  
Budapest,
Hungary
Oracle 12c Upgrade Seminar Budapest
Register
.

February 10, 2015  
Reston, VA
USA
Oracle 12c Upgrade Seminar Reston
Register
.

February 11, 2015  
Atlanta, GA
USA
Oracle Database 12c Upgrade Seminar - Atlanta
Register
.
February 19, 2015  
Nürnberg,
Germany
Franconian User Group:
Oracle Database 12c Upgrade
17:30-21:00 Uhr - TU Nürnberg GSO - 
Raum WE.012 Wasertorstraße 10
Anmeldung
.

Friday Jan 09, 2015

Oracle 12.1.0.2 is now certified with Oracle EBS 12.2

Just copying this information from Steven Chang's blog: 

Please note that Oracle Database 12.1.0.2 is already certified with EBS 12.1. 

-Mike 

Wednesday Dec 31, 2014

Happy New Year 2015!!!

We would like to wish you and your families a Happy and Peaceful New Year 2015

-Mike 

PS: The picture doesn't show Roy and myself - these are Lilly and Bonzo! 

About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle Corp

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite or remotely with reference customers. Acting as interlink between customers/partners and the Upgrade Development.

Follow me on TWITTER

Contact me via LinkedIn or XING

Search

Archives
« August 2015
SunMonTueWedThuFriSat
      
2
3
6
7
8
9
10
11
12
13
15
16
22
23
25
26
27
28
29
30
31
     
Today
Oracle related Tech Blogs
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
Workshop Map
This week on my Rega & Pono
Upgrade Reference Papers