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

Thursday May 19, 2011

Upgrade Fallback: Offline Backup??

When me an Roy talk about Fallback Scenarios in our Upgrade Workshop we cover first issues happening during the upgrade and second issues happening a few days (hours, weeks) after the upgrade.

One of the fallback options for issues encountered during the upgrade is to take an offline backup. Ok, not a complete offline backup of your 24TB database. Take all data tablespaces into read-only mode, then shutdown the database. Take a backup of all datafiles for SYSTEM, SYSAUX and UNDO plus the controlfiles plus the redologs. TEMP files are not a requirement as you could also ALTER TABLESPACE TEMP ADD TEMPFILE afterwards. Or you copy in TEMP datafiles as well. Now you do the upgrade and in case something fails you simply would shutdown, copy in the backuped files and startup again in the pre-upgrade environment.

Today Bartosz Mróz who was at the Upgrade Workshop last week in Warsaw sent me an email complaining that he has gotten an error:
ORA-01647: tablespace 'TOOLS' is read-only, cannot allocate space in it
when he did follow my recommendation :-(

TOOLSShame on me - and again I appologize and change the slides. The TOOLS tablespace got introduced a while back (was it Oracle8?) to move data out of SYSTEM and place repositories such as for the Developer Suite etc. into that tablespace, and have it not in SYSTEM anymore. A concept we later again introduced with SYSAUX in 10g onwards. So TOOLS has to be included in the list of files you have to offline backup as well.

Still the idea of the idea is to not recover your complete 24TB database in case something fails during the upgrade. And you could test read-only queries as well before changing the data tablespaces back to read-write.

Update 3-JUN-2011:

Again I have to appologize as the list of tablespace is not complete - neither here nor on the slides. I will most likely work well on a 10g/11g database but not for a 9i database with many components such as OLAP, UltraSearch etc. in it. Simple reason: In Oracle 8i or 9i these components had their own designated repository tablespaces. And once you set them READ ONLY during upgrade the component upgrade will fail.

Sorry again: this here should be the correct and complete list of tablespaces having to stay in READ WRITE mode during upgrade: SYSTEM, SYSAUX, TEMP, UNDO, TOOLS, DRSYS, XDB, ODM

Thanks again Bartosz!




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:

- -


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