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?

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;

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!!!