{"id":1304,"date":"2007-08-04T12:00:00","date_gmt":"2007-08-04T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1304"},"modified":"2007-08-04T12:00:00","modified_gmt":"2007-08-04T12:00:00","slug":"full-volume-database-refresh-part-1","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2007\/08\/04\/full-volume-database-refresh-part-1\/","title":{"rendered":"Full Volume Database Refresh – Part 1"},"content":{"rendered":"

(… or the Refresh from Hell<\/em>)<\/p>\n

This weekend will be the final practice run (there’s been one already)
\nfor a database refresh procedure I’ll be working on for a further four
\nweekends. It’s a full copy of one of our production databases that
\nforms part of a user acceptance test environment for a significant
\napplication upgrade due in the next couple of months. <\/p>\n

Companies should<\/em> run more development and test databases than
\nproduction ones, to support multiple development streams and different
\nphases of testing, such as system or user acceptance testing. Many of
\nthose databases will contain the same schema objects as production, but
\na tiny subset of the data. They pose an interesting challenge that I
\nmight write about one day. <\/p>\n

But nothing is as reassuring as testing on the same database that’s
\nrunning in production already. It’s an expensive business, of course,
\nbecause you need sufficient disk space to support it and most
\nbusinesses don’t have 300Gb USB drives as an option. What I mean to say
\nis that disk space isn’t quite as cheap as I keep hearing because of
\nthe surrounding infrastructure and support costs. You might also want
\nto use an identical server to run it on if you’re interested in testing
\nabsolute performance. It depends on what your testing is supposed to
\nachieve.<\/p>\n

In this case, the main concerns relate to the data. It’s a complex
\nsystem and the testers need to be able to see the same data as
\nproduction, which will then go through a week long flow of changes and
\nchecks and the same overnight batch runs as production. Essentially,
\nthe testers will simulate ‘a week in the life’ of this system.<\/p>\n

I said earlier that this test database will form ‘part of’ a UAT
\nenvironment, and that’s adds a little to the complexity. Is it just me
\nor does everyone want to design applications these days to use as many
\ndifferent databases, all talking to each other as possible? I strongly
\nsuspect the hands of architects here 😉 Might it be for performance
\nreasons? Probably not when all of the databases sit on the same server!
\nI don’t know why, but I’m seeing this approach used more often. In this
\ncase, the test database is going to be co-operating with at least three
\nother databases. However, we don’t want most of those other databases
\nto be refreshed, just the main one and another SQL Server database that
\nthe new application uses!<\/p>\n

OK, down to business and the point of this blog. I’m not going to give
\nyou all of the details of a client’s process, but let me higlight some
\nof the main stages, challenges and possible solutions. I think this is
\nfairly typical of other refresh procedures I’ve seen at other sites.<\/p>\n

Copying the Data
<\/strong>We need the production data to appear in our full-volume test database.
\nThis can be the most time-consuming process (4 hours in our case), but
\nit depends on what approach you take.<\/p>\n

– Export the production data and import it into test. This is the most
\nprone to failure and probably the slowest solution, even with Data Pump
\n(which we can’t use – this is a 9i database). It does have certain
\nbenefits, that will appear later in this discussion, but isn’t a
\nsensible option for large databases in my view. The database we’re
\nworking on is around 140GB. Not too large by today’s standards, but a
\nlittle 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!<\/p>\n

– Copy the database files from one set of filesystems to another, possibly on a
\ndifferent server. This is essentially the approach we’re taking because
\nwe’re going to restore one of the hot backups of production to the test
\nserver and then perform recovery on it.<\/p>\n

– Have an extra set of disk mirrors that can be temporarily broken away
\nfrom the main production disks and mounted elsewhere. This can be a
\nsuper-fast option but it’s not available to us in this particular
\nsituation.<\/p>\n

We’re going to restore files to a new location, in other words ‘Cloning a Database<\/a>‘.<\/p>\n

Actually, I’ll leave it there for now and post more later.<\/p>\n","protected":false},"excerpt":{"rendered":"

(… 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… Continue reading Full Volume Database Refresh – Part 1<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1304","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1306,"url":"http:\/\/orcldoug.com\/blog\/2007\/08\/07\/full-volume-database-refresh-part-3\/","url_meta":{"origin":1304,"position":0},"title":"Full Volume Database Refresh – Part 3","date":"August 7, 2007","format":false,"excerpt":"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\u2026","rel":"","context":"With 10 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1305,"url":"http:\/\/orcldoug.com\/blog\/2007\/08\/06\/full-volume-database-refresh-part-2\/","url_meta":{"origin":1304,"position":1},"title":"Full Volume Database Refresh – Part 2","date":"August 6, 2007","format":false,"excerpt":"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\u2026","rel":"","context":"With 10 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1191,"url":"http:\/\/orcldoug.com\/blog\/2007\/01\/27\/dba-documentation-catalogue\/","url_meta":{"origin":1304,"position":2},"title":"DBA Documentation – Catalogue","date":"January 27, 2007","format":false,"excerpt":"Prompted by Linda's comment on a previous blog, I thought it might be worth writing a couple of postings on DBA documentation.The first thing you need is a Database Catalogue of some kind.Benefits1) Even an experienced DBA arriving on site won't know what servers exist, how to login to them,\u2026","rel":"","context":"With 11 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":868,"url":"http:\/\/orcldoug.com\/blog\/2006\/02\/05\/we-never-learn-do-we\/","url_meta":{"origin":1304,"position":3},"title":"We Never Learn, Do We?","date":"February 5, 2006","format":false,"excerpt":"I had an interesting day at work yesterday. We've run into major performance problems when upgrading a number of Production databases from 8i to 9i. How could that happen, when we've performed regression tests to ensure that the applications perform as well as or better than they did on 8i?Probably\u2026","rel":"","context":"With 5 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1312,"url":"http:\/\/orcldoug.com\/blog\/2007\/09\/02\/full-volume-database-refresh-part-4\/","url_meta":{"origin":1304,"position":4},"title":"Full Volume Database Refresh – Part 4","date":"September 2, 2007","format":false,"excerpt":"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.","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1392,"url":"http:\/\/orcldoug.com\/blog\/2008\/03\/18\/paste\/","url_meta":{"origin":1304,"position":5},"title":"Paste","date":"March 18, 2008","format":false,"excerpt":"[As part of the occasional series on small but useful Unix commands ...]I've always loved Unix and vi and despair at those who prefer to faff around on Windows trying to edit files with an operating system and editors that, yes, are much more friendly but are hopelessly limited. The\u2026","rel":"","context":"With 8 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1304","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1304"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1304\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1304"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1304"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1304"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}