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 🙁
Shame 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
