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.
(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!!!
Thanks that was a great read!
How to best fallback method. If the main engines and engines using oracle 11 second oracle 9i (production server status). We offered a solution by:
The first vendor: as sql insert
The second vendor: dbvisit with 3 months license
The third vendor: export – import
four vendors: golden gate trial version
Thanks for your comment.
It depends on your customer’s downtime requirements.
exp/imp of course works. But it depends on the size of the database, data types (LOBs for instance).
DBvisit and Oracle Golden Gate cost money 😉
Cheers, Mike
Hi Mike,
The above method looks great, but in reality after changing the compatibility parameter, we hand over the DB for application testing, and here we cannot keep data tablespaces in readonly mode and we put them back to read/write .
Now let’s say application team comes back and say that the application is not working as expected post upgrade, then i think only option left is to restore from last rman fullbackup with incremental restores if any.
I think such fallback strategy of read only tablespace is good only for the duration of upgrade, before handing over to application team.
Well I did liked guaranteed restore points, but that too has limitations which cannot be used beyond compatible parameter changes.
Any thoughts here?
Thanks /Malesh