Thursday Jun 28, 2012

Guaranteed Restore Points as Fallback Method

Thanks to the great audience yesterday in the Upgrade & Migration Workshop in Utrecht. That was really fun and I was amazed by our new facilities (and the  "wellness" lights surrounding the plenum room's walls).

And another reason why I like to do these workshops is that often I learn new things from you :-) So credits here to Rick van  Ek who has highlighted the following topic to me. Yesterday (and in some previous workshops) I did mention during the discussion about Fallback Strategies that you'll have to switch on Flashback Database beforehand to create a guaranteed restore point in case you'll encounter an issue during the database upgrade.

I knew that we've made it possible since Oracle Database 11.2 to switch Flashback Database on without taking the database into MOUNT status (you could switch it off anyway while the database is open before in all releases). But before Oracle Database 11.2 that did require MOUNT status.

SQL> create restore point rp1 guarantee flashback database ;
create restore point rp1 guarantee flashback database
ERROR at line 1:
ORA-38784: Cannot create restore point 'RP1'.
ORA-38787: Creating the first guaranteed restore point requires mount mode when
flashback database is off

But Rick did mention that I won't need to switch Flashback Database On to create a guaranteed restore point. And he's right - in older releases I would have had to go into MOUNT state to define the restore point which meant to restart the database. But in 11.2 that's no necessary anymore. And the same will apply when you upgrade your pre-11.2 database (e.g. an Oracle Database to Oracle Database 11.2.

As soon as you start your "old" not-yet-upgraded database in your 11.2 environment with STARTUP UPGRADE you can define a guaranteed restore point. If you tail the alert.log you'll see that the database will start the RVWR (Recovery Writer) background process - you'll just have to make sure that you'd define the values for db_recovery_file_dest_size and db_recovery_file_dest.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             134221104 bytes
Database Buffers          272629760 bytes
Redo Buffers                8466432 bytes
Database mounted.
Database opened.
SQL> create restore point grpt guarantee flashback database;
Restore point created.
SQL> drop restore point grpt;

And don't forget to drop that restore point the sooner or later as it is guaranteed - and will fill up your Fast Recovery Area pretty quickly ;-) Just on the side: in any case archivelog mode is required if you'd like to work with restore points.

- Mike

Wednesday Jun 20, 2012

Let's do the Time Warp again!

Once you start reading about Daylight Saving Time changes in MyOracleSupport you'll find still a lot of notes explaining this and that and back and forth. But sometimes there seems to be a bit too much information - and lacking clear instructions. Once a customer called that the "Time Zone Spaghetti" after reading MOS notes about DST for several hours ending up with the note where he has begun to read before still not clear what to do now ;-)

I'm using usually the scripts from MOS Note:977512.1 as you'll just have to exchange the DST version you are upgrading to and it has everything you need to check and adjust the time zone data in the database - for instance after applying the DST V18 patch to your database's homes. As a reminder to myself when traveling I have stored a copy of the script part of that note here - and please note that this is not an official Oracle version. Always read and check the original MOS Note:977512.1 as it may have gotten changed in between and may contain changes or corrections and as it has a lot of more explainationary information than I could cover here.

And credit to Gunter Vermeir from Oracle Support, who is the owner of that MOS Note and has compiled all that useful stuff together.

Tuesday Jun 19, 2012

New Time Zone Patch DST V18 is available

Sorry for not updating the blog more often at the moment - but more updates will come soon as I play around with Oracle Restart and single instance databases in ASM with Oracle 11.2.

Just on the side there's a new time zone patch to DST V18 available since May 2012. You can download it via PATCH download from MOS with the patch number: 13417321

What do you think? Will Lufthansa operate a faster jet the other night? Will the jet stream be more powerful? Or a better type of fuel? Or is it just the travel portal which hasn't applied the correct time zone patches to catch DST change that night in the US whereas it happens two weeks later in Europe? Guess ...

And please see the readme about how to apply the patch and our slides about why time zone patching may be important even in your environment ;-)

RDBMS bug: Bug 13417321: DST 18 : HALF YEARLY DST PATCHES, MAY 2012
OJVM Bug 14112098 - dst changes for dstv18 (tzdata2012c) - need ojvm fix

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

Based in Germany. Interlink between customers/partners and the Upgrade Development. Running workshops between Arctic and Antartica. Assisting customers in their reference projects onsite and remotely. Connect via:

- -


« June 2012 »
Oracle related Tech Blogs
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Viewlets and Videos
Workshop Map
This week on my Rega & Pono
Upgrade Reference Papers