Backup From Physical Standby

The subject of taking backups from an Oracle Physical Standby database is something that has cropped up in conversation at work for me a few times over the past 6 months. It was something I had read about rather than done myself, and 2 of my colleagues reported trying, but without successfully restoring. I wanted to get my hands dirty and have a try…

The only problem was getting into a position where I could test. There was no opportunity at work and I had my sights set on something else at home. Well, that something else left me with an Oracle VM Server and Oracle VM Manager setup that would allow me to set up a Data Guard test environment with ease.

Anyway, what I ended up with was a bit much for a blog post, so I thought a PDF was in order… Backup From Physical Standby (MN: Updated PDF on 2011-07-16 due to a typo).

Deleting Archivelogs with RMAN

I’m a big fan of RMAN. I think it is very powerful, reliable and, if you know the syntax, easy to use. Not everyone agrees and I work with people who delete archivelogs using OS commands and then run CROSSCHECK ARCHIVELOG ALL followed by DELETE EXPIRED in order to remove archivelogs that are perceived as no longer required. I don’t like that approach at all. Nothing knows the facts about my archivelogs better than the database and RMAN, so I’d much rather use RMAN to perform any archivelog maintenance rather that rely on my own judgement.

For various reasons storage can be pretty tight on some of the databases I currently support and it is relatively common to be doing a bit of firefighting if redo generation rises. When removing archivelogs, the last thing that should happen is an archivelog be removed from disk before it has been backed up (obviously requirements and configuration vary). Taking the OS level deletion approach relies on the DBA to correctly understand which archivelogs have been backed up to an archivelog backupset, which might not seem like the most difficult task, but my view is that if any opportunity for operator error can be avoided then do so…

In steps RMAN with some useful commands.

If you want to simply remove all archivelogs that are already in a backupset then use:

RMAN> delete archivelog all backed up 1 times to disk;

However, I like to keep as much redo on disk as possible, within the confines of the storage available and alerting systems. For this reason I’ve found the following command to be very useful.

RMAN> delete archivelog until time '{date/time}' backed up 1 times to disk;

What would be really nice is a way of keeping as much redo on disk as possible without manual intervention… Sounds like a job for the Flash Recovery Area (FRA), but more on that at some point in the future. In the meantime watch this excellent presentation from Christo Kutrovsky on the subject.