Is there a FALLBACK strategy?

Having a valid and complete backup before you start your upgrades is key.
But how do you plan your fallback strategy?

parachute.jpg

The first question you'll have to ask is:
Are you allowed to loose any data updates in case of going back - Yes or No?


If you say "Yes we can!" because you would be able to redo any changes you've done to the upgraded system already once you'll go back, then restoring your backup from before the upgrade could be your easy fallback strategy.

You don't have the possibility to do a complete online backup (maybe you don't have enough disk capacity for your 28 TB DWH) then this solution might help:


(1) Put all data tablespaces into read-only mode
(2) Shutdown database immediate
(3) Copy SYSTEM, TEMP, UNDO and SYSAUX datafiles and controlfiles/redologs
(4) Startup database again

If something fails during the upgrade:
(1) Shutdown the database
(2) Restore SYSTEM, TEMP, UNDO, SYSAUX, controlfile and redologs from the backup location
(3) Startup the database in the old environment

In this case you'll be able to change COMPATIBLE (Yes, you can!!!) - and you could run your queries as long as they are read-only operations to verify the performance etc.

Beginning with Oracle Database 10gR2 you could also create a guaranteed restore point and use flashback database for a very fast point-in-time-recovery of your database - but keep in mind that you are not allowed to change the COMPATIBLE parameter in this case.

SQL> CREATE RESTORE POINT grpt GUARANTEE FLASHBACK DATABASE;
SQL> FLASHBACK DATABASE TO RESTORE POINT grpt;




downgrade.jpg

Your initial answer is "No!! No data loss once we go back!" ?
Then your options depend on the database version you're upgrading from.

If you upgrade from Oracle 9i to Oracle Database 11g then good ol' export and import will do the job for you.

See Metalink Note:158845.1


(1) Prepare an empty database for the import just in case you'll have to step back


Then:

(2) Run the appropiate ?/rdbms/admin/catexp.sql to create the 9.2 export views in the upgraded database

(3) Use "old" 9.2 exp for the export

(4) Use "old" 9.2 imp for the import

Just as an annotation: Metalink Note:550740.1, bug 6199855 (non-public) regarding a problem with the view exu9defpswitches.

But if you upgrade from Oracle 10.1.0.5 or 10.2.0.3 or 10.2.0.4 to Oracle Database 11g then you could downgrade. Downgrade? Yes you can - again - as long as you don't change the COMPATIBLE parameter. The Upgrade Development Group puts a lot of effort into downgrades. And it's as simple as an upgrade is:

See Metalink Note:443890.1 and the Upgrade documentation.

In your upgraded environment:

SQL> SPOOL /tmp/downgrade.log
SQL> STARTUP DOWNGRADE
SQL> @catdwgrd.sql
SQL> SPOOL OFF

Then restart your database in your 10g environment:

SQL> STARTUP UPGRADE
SQL> SPOOL /tmp/reload.log
SQL> @catrelod.sql
-- The catrelod.sql script reloads the appropriate version of
-- all of the database components in the downgraded database.

SQL> SPOOL OFF

Try it - it'll really work!!!

Comments:

Thanks that was a great read!

Posted by Gretsch on January 10, 2010 at 12:01 PM CET #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Mike Dietrich - Oracle Mike Dietrich
Senior Principal Technologist - Database Upgrade Development Group - Oracle Corporation

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

Contact me either via XING or LinkedIn

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
4
5
6
9
10
12
13
15
16
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
Slides Download Center
OOW Slides Download
Visitors since 17-OCT-2011
White Paper and Docs
Oracle Blogs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers