Full Volume Database Refresh - Part 1

(... or the Refresh from Hell)

This weekend will be the final practice run (there's been one already) for a database refresh procedure I'll be working on for a further four weekends. It's a full copy of one of our production databases that forms part of a user acceptance test environment for a significant application upgrade due in the next couple of months.

Companies should run more development and test databases than production ones, to support multiple development streams and different phases of testing, such as system or user acceptance testing. Many of those databases will contain the same schema objects as production, but a tiny subset of the data. They pose an interesting challenge that I might write about one day.

But nothing is as reassuring as testing on the same database that's running in production already. It's an expensive business, of course, because you need sufficient disk space to support it and most businesses don't have 300Gb USB drives as an option. What I mean to say is that disk space isn't quite as cheap as I keep hearing because of the surrounding infrastructure and support costs. You might also want to use an identical server to run it on if you're interested in testing absolute performance. It depends on what your testing is supposed to achieve.

In this case, the main concerns relate to the data. It's a complex system and the testers need to be able to see the same data as production, which will then go through a week long flow of changes and checks and the same overnight batch runs as production. Essentially, the testers will simulate 'a week in the life' of this system.

I said earlier that this test database will form 'part of' a UAT environment, and that's adds a little to the complexity. Is it just me or does everyone want to design applications these days to use as many different databases, all talking to each other as possible? I strongly suspect the hands of architects here Might it be for performance reasons? Probably not when all of the databases sit on the same server! I don't know why, but I'm seeing this approach used more often. In this case, the test database is going to be co-operating with at least three other databases. However, we don't want most of those other databases to be refreshed, just the main one and another SQL Server database that the new application uses!

OK, down to business and the point of this blog. I'm not going to give you all of the details of a client's process, but let me higlight some of the main stages, challenges and possible solutions. I think this is fairly typical of other refresh procedures I've seen at other sites.

Copying the Data
We need the production data to appear in our full-volume test database. This can be the most time-consuming process (4 hours in our case), but it depends on what approach you take.

- Export the production data and import it into test. This is the most prone to failure and probably the slowest solution, even with Data Pump (which we can't use - this is a 9i database). It does have certain benefits, that will appear later in this discussion, but isn't a sensible option for large databases in my view. The database we're working on is around 140GB. Not too large by today's standards, but a little tricky to handle with exports. However, exports have been used for this refresh in the past, and I pity the poor DBA who had to do them!

- Copy the database files from one set of filesystems to another, possibly on a different server. This is essentially the approach we're taking because we're going to restore one of the hot backups of production to the test server and then perform recovery on it.

- Have an extra set of disk mirrors that can be temporarily broken away from the main production disks and mounted elsewhere. This can be a super-fast option but it's not available to us in this particular situation.

We're going to restore files to a new location, in other words 'Cloning a Database'.

Actually, I'll leave it there for now and post more later.