Full Volume Database Refresh - Part 2

First the good news. "The Refresh from Hell" (because it was) has become "The Slightly Tricky Refresh".

It only took 6.5 hours on Sunday (in time to watch Celtic's first match of the season) and apart from a couple of minor panics along the way, it went well. Anyway, back to the overall process, with additional insight based on the first painful test and yesterday's almost painless one ...

Based on where I left Part 1, let's assume that the Production database has been copied, recovered and started as a new test instance and database.

Actually, let's not assume too much here, because the copy process can be an absolute nightmare, depending on a few factors, including one pointed out in Dominic Delmolino's comment to the last post.

Try to make the filesystems the same -- makes things a lot easier. And really, why would they need to be different?

That comment made me chuckle to myself, because I can hear the voice of painful experience echoing through the internet As Dominic says, there's no reason not to have the source and target databases on identical filesystems if they're on different servers. At the very least, give me the same number of filesystems that are sized identically. That way, any edits you need to file locations in a control file trace output or RMAN "SET NEWNAME" commands are easier. But look at the start of his comment. Why say "try" if the filesystems are always the same? It's such a no-brainer!

Because they are often completely different! Don't get me wrong, you can shuffle files around to new locations so that they all fit, but it's a monumental pain in the backside. It's almost a rite of passage for a DBA to have tried to solve the database file equivalent of one of these.

There are two main strategies if you have a mish-mash of incompatible filesystems.

1. Plan meticulously until you are sure that you know where every file from the source database fits on the target filesystems.
2. Have a stab at it and set the first restore (or copy) running. When it fails, make a few edits and restart the process. Repeat.

Generally I find that option 2 works best for me, as long as the restore operation can skip over the already-restored files very quickly.

One of the main reasons why the most recent practice went so smoothly was that I'd put together all of the SET NEWNAME commands during the last run and there hadn't been any files added to Production since then. So I already have my source-to-target file mapping in place.

Going forward, I need to bear the following in mind.

1) The Production database files will grow.
2) Production files might appear or disappear.

In fact one recent client insisted that all space requests for Production and the Volume Test environment were submitted through the change management process so that we could ensure that the environments were identical, but I haven't often worked in such a well-managed environment.

Why might the file systems be different? All sorts of reasons but usually because small test environments are rapidly converted into full volume. When I say rapid, I mean - 'we need it yesterday' - so compromises are made. Not good, but part of the world I operate in.

Let's just say that, if you're going to perform a regular refresh, it's worth spending time setting up a new set of filesystems on the target server that match the source server. As Dominic said, it 'makes things a lot easier'.

Oh, wow, another part of this blog gone and I haven't even got to the tricky stuff yet! Next time