Doug's Oracle Blog (Entries tagged as Database Refresh)

Full Volume Database Refresh - Part 4

dougburns@yahoo.com (Doug Burns) — Sun, 02 Sep 2007 18:52:12 +0100

As a post-script to the earlier blogs, Alex Gorbachev suggested a link to his UKOUG 2006 presentation in this area might prove useful and I agree. So here it is.

Full Volume Database Refresh - Part 3

dougburns@yahoo.com (Doug Burns) — Tue, 07 Aug 2007 19:18:48 +0100

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

Full Volume Database Refresh - Part 2

dougburns@yahoo.com (Doug Burns) — Mon, 06 Aug 2007 06:40:00 +0100

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.

Plan meticulously until you are sure that you know where every file from the source database fits on the target filesystems.
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

Full Volume Database Refresh - Part 1

dougburns@yahoo.com (Doug Burns) — Sat, 04 Aug 2007 08:33:43 +0100

(... 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.