Friday Nov 07, 2014

Sleeping Beauties - Upgrade to 11.2.0.4 can be slow

A customer from the US did contact me past week via LinkedIn and raised a question:

"Is it expected that my patch set upgrade from Oracle 11.2.0.3 to Oracle 11.2.0.4 takes over 3 hours?"

Of course, no - this is not expected

This is the upgrade stats gathered post upgrade with utlu112s.sql:

SQL> @?/rdbms/admin/utlu112s.sql ; .
Oracle Database 11.2 Post-Upgrade Status Tool 10-31-2014 10:05:29
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server  
. VALID 11.2.0.4.0 02:46:19
JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:08:34
[..]
Final Actions
. 00:00:00
Total Upgrade Time: 03:06:47

No, this is not really expected. So we tried to nail down the root cause finding out these statements in the upgrade script c1102000.sql are causing the trouble:

194 -- wri$_optstat_histhead_history2.
195 execute immediate
196 q'#create unique index i_wri$_optstat_hh_obj_icol_st on
197 wri$_optstat_histhead_history (obj#, intcol#, savtime, colname)
198 tablespace sysaux #';
199
200 execute immediate
201 q'#create index i_wri$_optstat_hh_st on
202 wri$_optstat_histhead_history (savtime)
203 tablespace sysaux #';
204 end;
205 /

It's index rebuilds on histogram tables. And the customer has a large amount of stats data in his database as the default stats retention is 31 days.

Obviously the index rebuild is not done very efficiently (not done in parallel, no nologging clause). Those things can happen and sometimes this may not cause any issues. But in this case it lead to over 2 hours for just those index rebuilds.  

Luckily my colleague Cindy is an excellent resource for such things - after asking our team I've got the reply that this is tagged with a bug number and code fix already got checked in (under review right now):

bug19855835:
Upgrade from 11.2.0.2 to 11.2.0.4 is slow 

-Mike

PS: Credits go to Tan for bringing this to my attention - and sorry for the inconvenience! 

Thursday Nov 06, 2014

Beijing, Seoul - and OTN Tour Tokyo - just in one week

Uhhh ... next week will be a tough traveling experience. 19870 km (12347 mi), all in cattle class on 4 different airlines.

Picture rights remain at The New York Times

I tried to avoid Lufthansa as they canceled recently flights I heavily relied on - with no reason! And there's still a huge risk of announcing just their 9th strike within a year and a bit.

But I'm really looking forward to the workshops, to see all the colleagues, customers and of course some great friends. Roy and I will do two internal two-day workshops in Beijing and Seoul with each more than 50 participants - and we'll include a nice hands-on-experience as well. And mid-week I'll head out to Tokyo to present at the DB TECH SHOWCASE 2014 - which is part of the OTN ACE Tour.

CU soon - thanks!!!

-Mike 


Wednesday Nov 05, 2014

Premier Support for Oracle 11.2 will end soon ...
Upgrade to Oracle Database 12c now!

Most customer and partners know it already - but still these days I get emails about upgrading from Oracle 10.2 to Oracle Database 11.2.0.4. Well, I have stopped arguing with people as there may be a reason to invest money and resources into a release which will go out of Premier Support in less than 3 months.

Some clarifications:

  • End of Premier Support means that Oracle will not offer fixes for new issues unless an extra-cost offer called Extended Support has been purchased. But you'll still get Sustaining Support until you'll stop working on the release. For a description what terms such as Premier Support, Extended Support and Sustaining Support mean please refer to the Lifetime Support Policy.
    .
  • For Oracle Database 11.2.0.4 - the Terminal (final) Patch Set for Oracle Database 11.2 - Oracle will waive (no extra cost, no action required) the first year of Extended Support to every customer with a valid support contract. Premier Support for Oracle Database 11.2 will still end on Jan-31, 2015, but you'll get Extended Support for Oracle Database 11.2.0.4 for one additional year at no extra cost. You can extend the Extended Support for Oracle Database 11.2.0.4 by a maximum of two additional years until 31-JAN-2018 - but then at the usual extra cost. 
    This information can be found in MOS Note:742060.1.
    .
  • For Oracle Database 11.2.0.3 Oracle will waive also Extended Support, but as Extended Support for Oracle Database 11.2.0.3 will end on 27-AUG-2015 no bug fixes will be available (including PSUs, SPUs and BPs) after that date. There's no offer to extend Extended Support for Oracle Database 11.2.0.3 upon that date.
    This information can be found in MOS Note:742060.1.
    .
  • For Oracle Database 11.2.0.2 there's no such Extended Support offering available.
    This information can be found in MOS Note:742060.1.
    ..
  • For Oracle Database 11.2.0.1 there's no such Extended Support offering available.
    This information can be found in MOS Note:742060.1.
    .

If you are interested in more details about Upgrade, Migrate and Consolidate to Oracle Database 12c please see the refreshed version (31-OCT-2014) of our slide deck available via the Slides Download Center to your right. 

-Mike 

Friday Oct 31, 2014

Utrecht holds the new EMEA record now!
Plus: Updated Slide Deck

OMG ... 154 people in an Database Upgrade/Migrate/Consolidate to Oracle Database 12c workshop! That's the EMEA record so far for workshop attendance rates for our workshops in the past 7 years. I'm really impressed. Really!!! And you were such a great audience. I've had plenty of fun, lots of good discussions during the breaks and after the workshop.

As the auditorium room at the Oracle location in Utrecht fits just 120 people the local marketing folks had the idea of asking Jost if he can stream the workshop to another room. The other option would have been to reject a lot of registered persons. And the streaming went quite well. I've never done this before and I hope it was still ok. But as the other room cross the hallway had daylight windows, fresh air and more space it seemed to be ok for most attendees :-)

I promissed also a new version of the slide deck - and here it is ready for download. Only 522 slides this time ;-) But I just added two new ones. You may check the log at the end of the deck for recent changes.

So thanks again - it was my pleasure to spend the day with you. And I hope next time, when I'll ask "Who's live on Oracle Database 12c" that I will see way more fingers going up :-)!!!

-Mike



Thursday Oct 23, 2014

Maintenance Windows is too small? Autotask Jobs fail

Since Oracle Database 10g we have automatic maintenance jobs in the database running. Since Oracle Database 11g (and the same applies to 12c) we have:

  • Weekday Jobs being able to get executed from 10pm until 2am on MON-FRI
  • Weekend Jobs being able to get executed from 6am until 2am on SAT-SUN

A support colleague contacted me a while ago asking why at his customer the jobs don't start anymore as they have set the duration for weekdays to 15 minutes. You may adjust the window generally by yourself with:

  • exec DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW','DURATION',numtodsinterval(2,'hour'));

But in this particular case only changing the default setting of _autotask_min_window=14 helped. That ensured that - once the job window was set to as little as 15 minutes that Automatic Maintenance Jobs still get executed. Once you adjust your Maintenance Windows making them really small ensure that you'll adjust this little underscore as well.

-Mike 

ORA-20000 Unable to gather statistics concurrently: Resource Manager is not enabled
ORA-06512: at "SYS.DBMS_STATS"

We are running a great training this week in Munich, called TECH CHALLENGE for Oracle people only. And one group hit the below issue so I thought I should share this as it is very likely it will happen with the upgrade to Oracle Database 12c in your environment as well.

During the run of catctl.pl or DBUA you may get an error saying:

ORA-20000: Unable to gather statistics concurrently:
Resource Manager is not
enabled.

ORA-06512: at "SYS.DBMS_STATS"

Even worse, the upgrade will fail and exit in phase 68 of the parallel upgrade.
This issue is new with Oracle Database 12.1.0.2 and is logged as bug19664340 

Reason why this happens:
It seems to be "somebody" has switched statistics gathering with Oracle Database 12.1.0.2 to CONCURRENT.

Workaround:

  1. SQL> exec dbms_stats.set_global_prefs('CONCURRENT', 'FALSE');
  2. Reset it after upgrade with
    SQL> exec dbms_stats.set_global_prefs('CONCURRENT', 'TRUE');
  3. Enter SQL*Plus in your 12c environment [even if you had used the DBUA]
  4. Restart your database in upgrade mode
    SQL> startup upgrade
    SQL> exit
  5. cd to $ORACLE_HOME/rdbms/admin
  6. $ORACLE_HOME/perl/bin/perl catctl.pl -l /home/oracle/upgrade -p 68 catupgrd.sql
    The "-p" option will restart the parallel upgrade now at phase 68. 

I also would like to thank Jason Arneil, who's Blog Post helped me a lot! 


Since Feb 23, 2015, there's a generic one off patch available on top of Oracle 12.1.0.2:

Patch 19664340: ORA-20000: UNABLE TO GATHER STATISTICS CONCURRENTLY: RESOURCE MANAGER IS NOT ENA

And please see also the COMMENT section with the comments by Arun Gupta - thanks a lot, Arun!

-Mike 

Monday Oct 20, 2014

Thanks for coming to the Upgrade Workshops in Dublin & Belfast - special thanks to my friends at LUFTHANSA

Thanks to everybody who came to our Upgrade/Migrate/Consolidate to Oracle Database 12c on Oct 14/15 in Dublin and Belfast. You were great audiences, I had plenty of fun, excellent deep dive discussion during the breaks and afterwards during the customer visits day on Thursday.

And very special thanks to my colleagues from Oracle Ireland, especially Mina and Kelly-Marie, but also all the others who were involved in the events and visits and made it happen so smoothly. Everything worked perfectly well except for things not in our hands (such as the massive train delay on the way back from Belfast).

Just in case you'd like to get the slides I'd recommend to download the deck via this link here:
Upgrade, Migrate & Consolidate to Oracle Database 12c

And VERY SVERY PECIAL THANKS to my dear friends at Lufthansa. Mina dropped my off at the airport on Thursday, I wanted to check in and hand over my luggage to the assistant - "Oh sorry, your direct flight to Munich has been canceled". Wonderful news. Dublin must have bad Karma for my travel plans as I've got locked in during the ash cloud some 4.5 years ago. 

Now my friends at Lufthansa have rebooked me to a very early morning flight for Friday, but with a stopover in Frankfurt (which is not my favorite airport at all). Anyhow, a call with Oracle Travel showed my a flight option that same night with KLM via Amsterdam to Munich. So I called Lufthansa again just finding out that Lufthansa refused to rebook me to that flight. Why? Because the first leg to Amsterdam is operated by Air Lingus - and "Lufthansa has no agreement with Air Lingus".  I told them that I don't care if they have an agreement or not, it's myself getting grounded it Dublin for no reason. But no chance. Even though the lady on the Senator hotline asked her supervisor and his manager (and I really thanked her a lot as she really tried it).

Simple calculation Lufthansa is doing:

  • Flight costs for the KLM flight to Munich: 730 Euro
  • Total costs for Mike staying another night in Dublin: 100 Euro for the hotel, a few bucks for the dinner and maybe the max fine of 250 Euro (which you can claim back only when you involve a lawyer or a service such as FlightRight). Summing up to a max of 400 Euro. Way cheaper to let him stay another night in Dublin at an airport hotel and make him get up at 4 in the morning to catch the flight to Frankfurt.
And, to make my experience even more valuable Lufthansa added 45 mins of delay to my leg from FRA to MUC, a flight distance of 35 minutes including take off and landing. I felt so good afterwards, so valued as a customer who flies roughly 5 or 6 times around the globe per year with this airline. 
Thanks you very much, Lufthansa!!! I made it home on Friday at 12:30pm - instead of 21:45pm on Thursday. Lovely, isn't it?

 -Mike

Wednesday Oct 15, 2014

PSU October 2014

October 14, 2014 Oracle released a new Patch Set Update. And as usual we strongly recommend that you'll apply it as soon as possible to your databases.  

There's one fix for JAVA in it which requires to either take the database down or do some other actions. Please find a detailed description here: 

  • Eric Maurice’s security blog:
    https://blogs.oracle.com/security/entry/october_2014_critical_patch_update
    "Due to the nature of the fixes required, Oracle development was not able to produce a normal RAC-rolling fix for these issues. To help protect customers until they can apply the Oracle JavaVM component Database PSU, which requires downtime, Oracle produced a script that introduces new controls to prevent new Java classes from being deployed or new calls from being made to existing Java classes, while preserving the ability of the database to execute the existing Java stored procedures that customers may rely on."
  • MOS:1929745.1 - "Oracle JavaVM Component Database PSU"
-Mike 

Tuesday Oct 07, 2014

Upcoming UPGRADE Workshops in:
Netherlands, Ireland, Northern Ireland, Spain and Belgium

Wow ... another OOW is over ... I have returned well but a bit jetlaged on Saturday from San Fransciso. It was a pleasure to meet with so many great customers, partners, Oracle ACEs, geeks and of course colleagues from all over the world.

And I'm still surprised how little DBAs and managers are aware of the upcoming end of Premier Support for Oracle Database 11.2 and still believe in "The 2nd Release Only". But I'll write something about this later this or next week.

And as soon as my time allows (most likely in the 2nd half of October) I will publish also the details about how to setup the HOL by yourself as we are not allowed to distribute it because of the patch sets included into it. 

Anyhow, back in reality (still on vacation today) let me offer you the final seats for the upcoming UPGRADE, MIGRATE & CONSOLIDATE TO ORACLE DATABASE 12c workshops in the following weeks: 

In case you'll miss links I'll update the blog post as soon as I get them.

-Mike 

Wednesday Oct 01, 2014

OOW2014 - Upgrade in a Multitenant Environment

On Sept 30, 2014 at Oracle OpenWorld, Joseph Errede gave his excellent talk about all the tech details in catctl.pl focusing on what happens when you upgrade a multitenant environment. The presentation is very technical explaining many of the switches of catctl.pl. If you'd like to get the slides please find them either in the Slides Download Center to the right or directly via this link:

-Mike 

Tuesday Sep 30, 2014

OOW2014 - Presentation and Hands.On.Lab

Thanks to everybody for coming by yesterday evening to our talk about Upgrade, Migrate and Consolidate to Oracle Database 12c. It was really a pleasure to speak in front of so many interested people :-)

If you'd like to download the slides please access them via the Slides Downloads Center to your right - or via this direct link: OOW 2014: Upgrade, Migrate, Consolidate to Oracle Database 12c

Or you may get the huge +500 slide deck covering all the different options and much more about Oracle Database 12c here: Upgrade, Migrate & Consolidate to Oracle Database 12c

hands on lab - oow 2014 - upgrade

And thanks to everybody who did show up for the Hands-On-Lab in the Nikko Hotel's Bay View room. Please be aware that you have to be at the room at least 10 minutes before lab starts. Otherwise the open seats will be given away by the door keepers. Not our idea, and we've just learned about it yesterday So please keep that in mind for the 3 remaining labs.

If you'd like to download the lab instructions get the via this link: 

And I'll promise I'll post instructions on how to setup the lab by yourself as we are not allow 

-Mike 

Friday Sep 26, 2014

Oracle Database 12.1.0.2 for MS Windows is available

Finally Oracle Database 12.1.0.2 for MS Windows is available for download from OTN, eDelivery and MOS:

-Mike

Best Practices for Patching -- learn more at OOW!

Although upgrade and patching are truly two very different processes, they both fall into the same general category of planned maintenance and are often associated with each other. So, it seems relevant to point out yet another important talk at OpenWorld in San Francisco:

Database Patching Best Practices II [CON7748]

  • Tuesday, Sep 30, 5:00 PM - 5:45 PM - Moscone South - 104

Building on a highly popular session from Oracle OpenWorld 2013, this session further explores ways to help you maintain and patch your database systems most efficiently. Learn about patch testing best practices, techniques for minimizing downtimes, how to best roll out patches in cloud environments, and more. The presentation also shares the latest Oracle Database 12c features and tooling to help ease patching processes. 

This is a great time and place to hear from the patching gurus who can tell you how to maintain your systems with minimal downtime and how to take advantage of some tools, techniques, and information about which you might not be aware. Hope to see you there! 

Wednesday Sep 24, 2014

OOW 2014: Focus on Upgrades/Migrations

For our presentations and others around the topic of database upgrades and migrations please see our "Focus On ..." listening - but there are also many others for different topics collected by the responsible Product Managers:

The whole set of "Focus On" documents that will help you find sessions about any given topic?  For example, you could take a look at

Focus on Database Upgrade

or

Focus on Database Utilities: Data Pump, SQL*Loader, Transportable Tablespaces

I hope you will find these useful as you plan what is sure to be a very, VERY busy week!  

-Roy 

Monday Sep 22, 2014

New AQ Background Architecture in Oracle 12c

Advanced Queing

I know a some really high profile customers making heavy use of Oracle Advanced Queuing. Now in Oracle Database 12c there's a change in the background architecture of AQ. The doc describes it more clearly than I ever could:

As far as I can see there shouldn't be any backwards compatibility issues. But (as always) I'd be happy to hear, read and learn about your experiences :-) 

And see a list of changes, additions, enhancements and new features of AQ here:


-Mike 

Friday Sep 19, 2014

Highly Recommended OOW Sessions

Here's a list of highly recommended OOW sessions apart from our own talks related to upgrades and migrations: 

-Mike 

Tuesday Sep 16, 2014

Oracle Database Documentation - From Past to Present

Yesterday one of my colleagues, well known Data Guard Guru Larry "Murphy" Carpenter, sent out an internal email with links to older and recent Oracle Database Documentation. Reason why you should use the below links: Some sites in the www have stored copies of the doc - but some may represent not the most recent state or may not be complete. So stay with the official Oracle docs only:

-Mike

PS: For those who haven't upgraded for the past 17 years or for those with nostalgic feelings this is the link to the Oracle 7.3.4 documentation :-)

Tuesday Sep 09, 2014

OOW 2014 - Upgrade Hands-On-Lab at NIKKO HOTEL

This year's Oracle Open World will be a real challenge logistically for us. Non-privileged employees were assigned to airport hotels only. So Roy, Joe, Dean and I stay >16 miles (>25km) away from Moscone Center. And to make things even more complicated due to some reconstruction at the Moscone Center (I hope they'll get rid of their 1990s audio equipment and such) our Hands On Labs for instance are at the NIKKO Hotel, not at the Marriott Marquee anymore.

These are the dates for our HOL about How to upgrade to Oracle Database 12c and plug into Oracle Multitenant:

  • Monday , Sep 29 - 11:45 AM in Hotel Nikko - Bay View (0/32)
  • Tuesday , Sep 30 - 5:15 PM in Hotel Nikko - Bay View (0/32)
  • Wednesday, Oct 01 - 4:15 PM in Hotel Nikko - Bay View (0/32)
  • Thursday , Oct 02 - 8:30 AM in Hotel Nikko - Bay View (0/32)
Follow this link to the OOW Session Agenda - ID of the Labs is [HOL9127] - and usually you'll have to hurry as the 50 seats per lab sell out quickly.

Especially Thursday will be a tough call to be there at 8:30am in the morning. At least traffic on 101 won't be that terrible at such a night time ;-)

-Mike

Monday Sep 08, 2014

OOW Demo Booth SD-138 in Moscone South Exhib Hall

Just in case you'd like to meet with the Upgrade and/or Data Pump Team at OOW in San Francisco please come by during the Exhibition hours in Moscone South on the LEFT side of thehall - SLD-138 is the number of our booth (see the BLUE arrow in the chart below):

-Mike 

Thursday Sep 04, 2014

OOW 2014 - Upgrade and Data Pump Talks

Oracle Open World (OOW) 2014 in San Francisco is coming ... just a few weeks to go ... everybody is in the prep phase for demos, presentations, labs etc. 

If you'd like to get in touch with us to discuss your upgrade and migration strategies please feel free to contact either Roy Swonger or myself directly. We'll be happy to assist you. And of course you are welcome to stop by at our combined Upgrade/DataPump booth at the demo grounds and visit one of our talks.

Our group is happy to deliver the following talks and labs:

How to Upgrade, Migrate, and Consolidate to Oracle Database 12c [CON7647]
Monday, Sep 29, 5:15 PM - 6:00 PM - Moscone South - 102

The most widely anticipated feature of Oracle Database 12c is now available, and you may be wondering just how you can move your current databases to pluggable databases in a multitenant architecture. Whether you are just starting to explore the world of pluggable databases or are planning a production upgrade in the near future to Oracle Database 12c, this presentation by Oracle Database upgrade and migration experts gives you all the details: what methods are available; how they work; and which is the best for your particular upgrade, migration, or consolidation scenario.

How an Oracle Database 12c Upgrade Works in a Multitenant Environment [CON7648]
Tuesday, Sep 30, 12:00 PM - 12:45 PM - Moscone South - 306

With the first patch set of Oracle Database 12c, you will be able to choose between various methods of upgrading a multitenant container database and its pluggable databases. In this session, you will hear from Oracle upgrade experts about all the details of how a database upgrade works in a multitenant environment. You will learn what your options are, how parallelism works for database upgrades, and what is new for database upgrades in the first patch set of Oracle Database 12c.

How and Why to Migrate from Schema Consolidation to Pluggable Databases [CON7649]
Wednesday, Oct 1, 11:30 AM - 12:15 PM - Moscone South - 306

One important use case for pluggable databases is to enable you to move from schema consolidation with multiple applications in the same database to a more secure environment with Oracle Multitenant and pluggable databases. In this technical session, you will hear from Oracle development experts about the methods available for migrating from schema consolidation to a multitenant database environment with Oracle Data Pump, transportable tablespaces, or new features in Oracle Multitenant.

Oracle Database 12c Upgrade: Tools and Best Practices from Oracle Support [CON8236]
This talk is not done by us but by our Global Tech Lead for Upgrades in Support, Agrim Pandit
Tuesday, Sep 30, 5:00 PM - 5:45 PM - Moscone South - 310

You’ve heard about Oracle Database 12c and its new capabilities. Now come hear from Oracle experts about all the great tools and resources Oracle offers to help you upgrade to Oracle Database 12c efficiently and effectively. This session’s presenters, from Oracle Support, bring years of database experience and recent lessons learned from Oracle Database 12c upgrades at companies of all sizes all around the world. You are sure to leave with valuable information that will help you plan and execute your upgrade. What's more, most, if not all, of the tools and resources they discuss are available to current customers at no additional cost through their standard product support coverage.

.

I will publish the schedules for the Hands-On-Lab (4x) and the location of the demo ground's booth as soon as I'll get it.

-Mike

Tuesday Sep 02, 2014

Unified Auditing - is it ON or OFF in Oracle 12c?

Don't trust our slides - only believe what you've verified by yourself ;-)

Actually one of our slides gives a parameter recommendation to set AUDIT_TRAIL since Oracle 11g explicitly to the value you want as otherwise it may switch to "DB" and you may not be aware of it. In conjunction with this setting we explain the new Oracle Database 12c feature Unified Auditing - which is not linked into the kernel and therefore should be off.

Should be ... well ... thanks to Marco Patzwahl who asked me why he still has over 100 audit records in V$UNIFIED_AUDIT_TRAIL? Good question - and I've had no answer. But Carol, my manager, knew the right person to ask. And Naveen replied within minutes (thanks!!!).

Here are the facts: 

  • Unified Auditing is not linked into the Oracle 12c kernel by default to offer people the choice to use it and to avoid conflicts in case somebody has auditing ON already - so neither during an upgrade nor with a fresh 12c database you'll see it included into the kernel. It will have to be linked in manually (see our slides)
    • Check if Unified Auditing is present in your environment:
      SQL> select VALUE from V$OPTION where PARAMETER='Unified Auditing';
    • In case you'll link it into the kernel
      cd $ORACLE_HOME/rdbms/lib
      make -f ins_rdbms.mk
      uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

      make sure you set AUDIT_TRAIL=NONE afterwards as otherwise both auditing mechanisms will run concurrently
  • But even though it is not linked into the kernel a bit of Unified Auditing is ON by default in MIXED MODE when you create a fresh Oracle 12c database.
    • MIXED MODE auditing?
      • See the documentation for further information
      • Just two policies are enabled by default: ORA_SECURECONFIG and ORA_LOGON_FAILURES
  • Turn Unfiied Auditing OFF?
    • If is has been linked in into the kernel, unlink it:
      cd $ORACLE_HOME/rdbms/lib
      make -f ins_rdbms.mk 
      uniaud_off ioracle ORACLE_HOME=$ORACLE_HOME
    • Disable the two default policies - this will turn off any Unified Auditing features:
      SQL> noaudit policy ORA_SECURECONFIG;
      Noaudit succeeded.
      SQL> noaudit policy ORA_LOGON_FAILURES;
      Noaudit succeeded.


-Mike

Friday Aug 22, 2014

Automatic Maintenance Jobs in every PDB?
New SPM Evolve Advisor Task in Oracle 12.1.0.2

A customer checking out our slides from the OTN Tour in August 2014 asked me a finicky question the other day:

"According to the documentation the Automatic SQL Tuning Advisor maintenance task gets executed only within the CDB$ROOT, but not within each PDB - but the slides are not clear here. So what is the truth?"

Ok, that's good question. In my understanding all tasks will get executed within each PDB - that's why we recommend (based on experience) to break up the default maintenance windows when using Oracle Multitenant. Otherwise all PDBs will have the same maintenance windows, and guess what will happen when 25 PDBs start gathering object statistics at the same time ...

The documentation indeed says:

Automatic SQL Tuning Advisor data is stored in the root. It might have results about SQL statements executed in a PDB that were analyzed by the advisor, but these results are not included if the PDB is unplugged. A common user whose current container is the root can run SQL Tuning Advisor manually for SQL statements from any PDB. When a statement is tuned, it is tuned in any container that runs the statement.

This sounds reasonable. But when we have a look into our PDBs or into the CDB_AUTOTASK_CLIENT view the result is different from what the doc says. In my environment I did create just two fresh empty PDBs (CON_ID 3 and 4):

SQL> select client_name, status, con_id from cdb_autotask_client;

CLIENT_NAME                           STATUS         CON_ID
------------------------------------- ---------- ----------
auto optimizer stats collection       ENABLED             1
sql tuning advisor                    ENABLED             1
auto space advisor                    ENABLED             1
auto optimizer stats collection       ENABLED             4
sql tuning advisor                    ENABLED             4
auto space advisor                    ENABLED             4
auto optimizer stats collection       ENABLED             3
sql tuning advisor                    ENABLED             3
auto space advisor                    ENABLED             3

9 rows selected.

I haven't verified the reason why this is different from the docs but it may have been related to one change in Oracle Database 12.1.0.2: The new SPM Evolve Advisor Task ( SYS_AUTO_SPM_EVOLVE_TASK) for automatic plan evolution for SQL Plan Management. This new task doesn't appear as a stand-alone job (client) in the maintenance window but runs as a sub-entity of the Automatic SQL Tuning Advisor task. And (I'm just guessing) this may be one of the reasons why every PDB will have to have its own Automatic SQL Tuning Advisor task 

Here you'll find more information about how to enable, disable and configure the new Oracle 12.1.0.2 SPM Evolve Advisor Task:

-Mike

Grid Infrastructure Management Repository (GIMR)
database now mandatory in Oracle GI 12.1.0.2

During the installation of Oracle Grid Infrastructure 12.1.0.1 you've had the following option to choose YES/NO to install the Grid Infrastructure Management Repository (GIMR) database MGMTDB:

With Oracle Grid Infrastructure 12.1.0.2 this choice has become obsolete and the above screen does not appear anymore. The GIMR database has become mandatory

What gets stored in the GIMR?

See the changes in Oracle Clusterware 12.1.0.2 here:

  • Automatic Installation of Grid Infrastructure Management Repository

    The Grid Infrastructure Management Repository is automatically installed with Oracle Grid Infrastructure 12crelease 1 (12.1.0.2). The Grid Infrastructure Management Repository enables such features as Cluster Health Monitor, Oracle Database QoS Management, and Rapid Home Provisioning, and provides a historical metric repository that simplifies viewing of past performance and diagnosis of issues. This capability is fully integrated into Oracle Enterprise Manager Cloud Control for seamless management.

Furthermore what the doc doesn't say explicitly:

  • The -MGMTDB has now become a single-tenant deployment having a CDB with one PDB
    • This will allow the use of a Utility Cluster that can hold the CDB for a collection of GIMR PDBs
  • When you've had already an Oracle 12.1.0.1 GIMR this database will be destroyed and recreated
    • Preserving the CHM/OS data can be acchieved with OCULMON to dump it out into node view
  • The data files associated with it will be created within the same disk group as OCR or VOTING
    •  The OUI will get the disk groups for OCR and Voting and chooses the first one - which usually is the first OCR. This may lead to serious space issues. It is tracked internally as Bug:19661882  In a future release there may be an option offered to put in into a separate disk group.
      Workaround would be to move the affected OCR to another disk group (use ocrconfig command for it) - see MOS Note:1589394.1
  • Some important MOS Notes:
    • MOS Note 1568402.1
      FAQ: 12c Grid Infrastructure Management Repository, states there's no supported procedure to enable Management Database once the GI stack is configured
    • MOS Note: 1921105.1
      Managing the Cluster Health Monitor Repository (incl how to resize)  
    • MOS Note 1589394.1
      How to Move GI Management Repository to Different Shared Storage
      (shows how to delete and recreate the MGMTDB)
    • MOS Note 1631336.1
      Cannot delete Management Database (MGMTDB) in 12.1
    • MOS Note 1945558.1
      _mgmtdb Service Registered with All Local Listeners in a Grid Infrastructure Environment
  • Average growth size per day per node is roughly 650-750 MB. E.g. a 4 node cluster would lead at the default retention of 3 days to an approximate size of  5.9-6.8 GB
  • Change the retention 12.1.0.1:
    $CRS_HOME/bin/oclumon manage -repos changeretentiontime 260000
  • Change the retention 12.1.0.2:
    $CRS_HOME/bin/oclumon manage -repos checkretentiontime 260000

-Mike

PS: Kudos to Sebastian Solbach who updated me on the things to add (retention, average growth, OUI choosing the first disk group displayed for the MGMTDB) - cheers!

Thursday Aug 14, 2014

RMAN Catalog requires Enterprise Edition (EE)
since Oracle Database 12.1.0.2

Credits go to Cameron Hodge, Malcom and Martin Mäs who all highlighted issues to me following my previous entry about RMAN - and sorry for any disappointment but I wasn't aware of all these nice little things. 

Ok, you'd upgrade your RMAN Catalog to be ready to backup/recover Oracle Database 12.1.0.2 databases and you see this error:

RMAN> upgrade catalog;

error creating create_deleted_object_seq
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-06004: ORACLE error from recovery catalog database: ORA-00439: feature not enabled: Partitioning

Now you start to wonder as your Catalog Database had been an Oracle Standard Edition (SE) database for quite a while - and of course an SE database does not have Partitioning on. And on the side the Oracle Partitioning Option is licensable option.

First of all this new behavior gets introduced with Oracle Database 12.1.0.2. And as far as I know it is not documented in relation to the "upgrade catalog". 

The valid workaround leads to a new feature called Infrastructure Repository Database - which is always an EE database without the need for extra licensening as all feature will be used only by Oracle internal mechanisms. 

Licensing doc:
http://docs.oracle.com/database/121/DBLIC/editions.htm#DBLIC119

Infrastructure Repository Databases

A separate single instance Oracle Database can be installed and used as an infrastructure repository for RMAN, Oracle Enterprise Manager Cloud Control, Automatic Workload Repository (AWR) Warehouse, Global Data Services Catalog, and Grid Infrastructure Management Repository without additional license requirements, provided that all the targets are correctly licensed. It may not be used or deployed for other uses.

The infrastructure repositories for RMAN, Oracle Enterprise Manager Cloud Control, AWR Warehouse, and Global Data Services Catalog can be shared in one database, or deployed in separate databases. Enterprise Edition must be used for the infrastructure repository database(s).

Furthermore the Recovery Manager documentation mentions this:

Creating and Managing Virtual Private Catalogs with Oracle Database 12c Release 1 (12.1.0.2)

Note:

Starting with Oracle Database 12c Release 1 (12.1.0.2), virtual private catalogs can be used only with the Enterprise Edition of the Oracle Database. This functionality is not supported for the Standard Edition.

Now this is a bit misleading as most of you won't use the Virtual Private Catalogs - but even though you may not use it still some of the functionality is in your catalog already. And therefore every RMAN catalog beginning with Oracle Database 12.1.0.2 must be hosted in an Enterprise Edition database which does not require an EE license.

The next question is:
How do I migrate my catalog into an EE database?

There are two options whereas I'd prefer the one mentioned in the RMAN documentation:

And there are more things to mention:

  • DBMS_LOB package must be present
  • UTL_HTTP must be present

.

To summarize to successfully upgrade your RMAN catalog:

  • You need to execute this script before upgrading the catalog:
    SQL> @$ORACLE_HOME/rdbms/admin/dbmsrmansys.sql
  • Your database hosting the RMAN Catalog beginning with Oracle 12.1.0.2:
    • Must be an Enterprise Edition database - no EE license required
    • Oracle Partitioning Option must be installed - no Partitioning lincese required
    • DBMS_LOB and UTL_HTTP packages must be present

-Mike                  

PS:  Credits for this addition go to Malcom!!! In addition to RMAN Catalog Database now requiring to be in an EE database this database will require the Partitioning Option linked in as well. See MOS Note:1927265.1

Monday Aug 11, 2014

RMAN Catalog Upgrade to Oracle 12.1.0.2

It sounds so simple - but in this specific case a bit of doc reading is required as upgrading the RMAN catalog to handle Oracle 12.1.0.2 databases is not as trivial as in the past. 

Thanks to a German customer and a friendly colleague from Sales Consulting in Stuttgart I have learned my RMAN lesson for Oracle Database 12.1.0.2 this week. A simple "upgrade catalog" is not the correct step once you'd like to handle backups of Oracle 12.1.0.2 databases in your current catalog schema. 

Even though you may not have ever heard before about Virtual Private Catalogs you need to follow this guideline:

http://docs.oracle.com/database/121/BRADV/rcmcatdb.htm#BRADV848

The doc tells you to run this step first:

SQL> @$ORACLE_HOME/rdbms/admin/dbmsrmansys.sql

  • Please ignore the "-all" option printed in the doc - this is a known docu bug
  • If you miss this step the upgrade of the catalog will fail with a warning that your user lacks privileges 

$ rman CATALOG my_catalog_owner@catdb
recovery catalog database Password: 
RMAN> UPGRADE CATALOG; 
RMAN> EXIT; 

It should work now :-)

-Mike 

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
« May 2015
SunMonTueWedThuFriSat
     
1
2
3
4
5
6
7
8
9
10
12
13
14
15
16
17
19
20
21
22
23
24
25
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
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers