Fallback Strategy: Partial Offline Backup

Oh, I think it took us years to get rid of recommendations in MOS notes saying before this or that change you must take an offline backup. These recommendations dated back to times where not almost everybody was using RMAN for online backups. But the times have changed. Still, there are situations where the “Offline Backup” word can be mentioned. And in this blog post I will describe a Fallback Strategy: Partial Offline Backup.

Usage Scenario

This method will only protect you against failure during an upgrade.

First of all, Offline Backup sounds really brutal and old-fashioned. But this method is helpful in several scenarios:

• Your database is – on purpose – in NOARCHIVELOG mode
• You have a very large database and the RTO is very tight
• You use Oracle Standard Edition and hence can’t use Guaranteed Restore Points (GRP)

You can change the COMPATIBLE parameter – but you can’t run DML (INSERT, UPDATE, DELETE).

Fallback Strategy: Partial Offline Backup

The idea behind this method is simple. During the upgrade we won’t touch user data. Hence, we can take all user data tablespaces read-only and keep only “our” tablespaces in read-write access. But before we invoke the upgrade we shutdown the database and do an offline backup of the core of the database:

• SYSTEM, SYAUX and UNDO tablespaces
• All Oracle repository tablespaces
• Controlfiles
• Redologs

In case of an issue during the upgrade we can safely and – this is the important part – quickly restore the files we took an offline backup of. This may be faster than restoring a 100 TB DWH in case it was operating in NOARCHIVELOG mode.

Step 1 – Take USER DATA tablespaces read-only

This sounds as the easy part. But actually this is the part of the process having the most pitfalls.

First of all, the term “Oracle repository tablespace” refers to tablespaces where an option such as XDB stores data in. Nowadays this is the SYSTEM or SYSAUX tablespace. But it wasn’t in the older days. And there were other repository tablespaces which we don’t create since Oracle 10g anymore when SYSAUX was designed to collect all these repositories together. So please watch out for tablespaces named:

• TOOLS
• ODM
• XDB
• DRSYS

And I can’t guarantee that there aren’t others. It may be that somebody created a TEXT tablespace to store the repository of Oracle Text in it. Check for such obvious names – and if you are unsure, don’t take it read-only but include it in your backup as well.

alter tablespace USERS read only;
alter tablespace DATA read only;

Step 2 – Copy / backup the files

Does it have to be said? Of course, as with every other backup/restore strategy, you will test this upfront to make sure you haven’t forgotten anything. And in this stage you will now shutdown the database and copy the files belonging to all Oracle maintained tablespaces plus the ones you haven’t taken into read-only plus the controlfiles and the redologs to a safe location. This is the Partial Offline Backup.

shutdown immediate

Then copy all files belonging to:

• SYSTEM, SYSAUX and UNDO tablespaces
• Repository tablespaces
• Tablespaces you haven’t switched to read-only
• Controlfiles
• Redologs

Now you can invoke the database upgrade. You could even change the COMPATIBLE parameter but I don’t see a bigger benefit from this. But it is possible.

If all goes well and the upgrade completes flawless (or you finished your testing), then you can take all user and data tablespaces back into read-write mode.

Stage 3 – In case of failure …

But in case of failure or testing you will shutdown the database, copy the saved files back into their original location and restart the database in the previous Oracle environment.

Please be aware that you’ll have to reverse the user and data tablespaces into read-write mode in order to be productive. But at this stage you can’t rely on your previous Partial Offline Backup anymore. You’ll have to create another set before your next test. It is only valid as long as the user and data tablespaces are kept in read-only mode.

And you may have to create (a) new TEMP tablespace(s).

The Database Upgrade Assistant (DBUA)

The DBUA had some issues with this technique in the past. See y previous blog posts here:

• DBUA and Read-Only Tablespaces – Things To Know 1
• DBUA and Read-Only Tablespaces – Things To Know 2

Personally – and this is my very personal opinion only – I wouldn’t do such a sensitive method in a GUI tool. Not for the reason that I wouldn’t trust a GUI tool but simply for the reason that you catch pitfalls much faster.

Further Information

• Fallback Strategy: Flashback to a Guaranteed Restore Point
• Fallback Strategy: Flashback Pluggable Database
• Fallback Strategy: Partial Offline Backups
• Fallback Strategies with Full Transportable Export/Import

–Mike