Full Volume Database Refresh - Part 3

After solving the jigsaw puzzle and waiting for tape drives to do their thing, we've finally got a running test instance accessing a full copy of production. (Well, there was some recovery, faffing around with database names, control files, tempfiles, putting the database in noarchivelog mode and the rest, but there are plenty of Oracle database cloning references out there. I've always found Howard's stuff top-notch.)

But what happens next is perhaps the trickiest bit (particularly if you're familiar with database cloning), because although the generic types of problem and appropriate solutions are the same, each database is going to be subtly different. Interestingly, the mere mention of a Production-to-Test refresh brought insightful comments on previous postings from those who've been here before.

• "Challenges we encountered were related to encryption (can't have production keys outside of production) and localization of data values (stuff like production URLs being stored in table columns which needed to be updated to point at test URLs)." (Dominic D.)
• "and there is the need to obscure production data yet maintain integrity - some of my customers are quite secretive. Mangling names and addresses can be a headache" (Pete S.)

One of the biggest benefits of the resulting Test environment is that we *know* this is exactly as Production is, or was at the time of the backup.

One of the biggest dangers of the resulting Test environment is that this is exactly as Production is, or was at the time of the backup!

Why the contradiction? Well, we now have a Test database that carries the operational footprint of Production. What do I mean by operational footprint? Well, consider the following :-

1) Database links or URLs stored in database tables. Remember how I talked about a complex environment, consisting of multiple databases that communicate with each other (through database links) or perhaps with other applications (through URLs or message queue locations)? Well, your new Test environment is pointing to Production databases! I'm not going to elaborate, but let the thought rattle around your mind for a bit. (It's more fun and scary that way!)

2) User Accounts and Passwords. Every Oracle account in the Test environment now has the Production password, privileges and profile. It's amazing how confusing most developers and testers find this, even when you point out this is an *exact* copy of Production More to the point, people will probably resist the security constraints that exist in the Production environment being applied to the Test environment, so they'll ask you to loosen them. But then, that opens up a whole new can of worms ....

3) Sensitive Customer Data. Many Production databases contain sensitive customer data. That's why we have strict controls in Production (not just because DBAs get their kicks out of being obstructive, although that might be true, too ). So, if the database contains all of the Production data then we should apply the same controls, or modify the data.

4) Instance Parameters. In some cases, you want a perfect replica of Production, perhaps for performance tests. But sometimes you'll want to reduce the memory used for the Test copy.

5) DBMS_JOBs. Do you really want all of your regular Production jobs to start running in the Test environment? If you combine this with point 1) above, that's a recipe for disaster!

I probably have at least two more blogs worth of these considerations, but I just wanted to give you a flavour of the problems you might run into. All can be solved with a little thought but there are times when I hear people request a copy of Production and I just chuckle. Invariably they haven't thought it through completely and we spend the next few weeks solving these 'little problems' one by one.

A common term for the steps required to make a copy of Production look almost exactly the same, but not quite, is localisation. (Well, it's the most common term I've heard on my travels.)

It's worth mentioning that, of the 6.5 hour process (not including a couple of hours preparation time), about 2.5 hours is spent on these surrounding issues and there have been many days spent working our way through them, discussing them and documenting the required steps. Ultimately, these should be scripted and will become quicker, but you should be aware of the dangers. Even then, I'd always want to check that the scripts worked as we expected.

Whatever the refresh method selected and the technical process that results (sometimes complex and sometimes not), there are functional considerations. To address those, there's no substitute for human beings getting together, talking, thinking and reducing the final decisions to a series of simple, repeatable steps.

We're just about there