{"id":1306,"date":"2007-08-07T12:00:00","date_gmt":"2007-08-07T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1306"},"modified":"2007-08-07T12:00:00","modified_gmt":"2007-08-07T12:00:00","slug":"full-volume-database-refresh-part-3","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2007\/08\/07\/full-volume-database-refresh-part-3\/","title":{"rendered":"Full Volume Database Refresh &#8211; Part 3"},"content":{"rendered":"<p>After <a href=\"http:\/\/18.133.199.212\/?p=1305\">solving the jigsaw puzzle<\/a> and waiting for tape drives to do their thing, we&#8217;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&#8217;ve always found <a href=\"http:\/\/dizwell.com\">Howard&#8217;s stuff<\/a> top-notch.)<\/p>\n<p>But what happens next is perhaps the trickiest bit (particularly if you&#8217;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&#8217;ve been here before.<\/p>\n<ul>\n<li>&#8220;<em>Challenges we encountered were related to encryption (can&#8217;t have<br \/>\nproduction keys outside of production) and localization of data values<br \/>\n(stuff like production URLs being stored in table columns which needed<br \/>\nto be updated to point at test URLs).<\/em>&#8221; (Dominic D.)<\/li>\n<li>&#8220;<em>and there is the need to obscure production data yet maintain integrity<br \/>\n&#8211; some of my customers are quite secretive. Mangling names and<br \/>\naddresses can be a headache<\/em>&#8221; (Pete S.)<\/li>\n<\/ul>\n<p>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. <\/p>\n<p>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! <\/p>\n<p>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 :-<\/p>\n<p>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&#8217;m not going to elaborate, but let the thought rattle around your mind for a bit. (It&#8217;s more fun and scary that way!) &#128577;<\/p>\n<p>2) User Accounts and Passwords. Every Oracle account in the Test environment now has the Production password, privileges and profile. It&#8217;s amazing how confusing most developers and testers find this, even when you point out this is an *exact* copy of Production &#128521; 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&#8217;ll ask you to loosen them. But then, that opens up a whole new can of worms &#8230;.<\/p>\n<p>3) Sensitive Customer Data. Many Production databases contain sensitive customer data. That&#8217;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 &#128521;). So, if the database contains all of the Production data then we should apply the same controls, or <em>modify the data<\/em>.<\/p>\n<p>4) Instance Parameters. In some cases, you want a perfect replica of Production, perhaps for performance tests. But sometimes you&#8217;ll want to reduce the memory used for the Test copy.<\/p>\n<p>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&#8217;s a recipe for disaster!<\/p>\n<p>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&#8217;t thought it through completely and we spend the next few weeks solving these &#8216;little problems&#8217; one by one. <\/p>\n<p>A common term for the steps required to make a copy of Production look <em>almost<\/em> exactly the same, but not quite, is <em>localisation<\/em>. (Well, it&#8217;s the most common term I&#8217;ve heard on my travels.)<\/p>\n<p>It&#8217;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&#8217;d always want to check that the scripts worked as we expected.<\/p>\n<p>Whatever the refresh method selected and the technical process that results (sometimes complex and sometimes not), there are functional considerations. To address those, there&#8217;s no substitute for human beings getting together, <em>talking<\/em>, <em>thinking<\/em> and reducing the final decisions to a series of simple, repeatable steps.<\/p>\n<p>We&#8217;re just about there &#128521;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>After solving the jigsaw puzzle and waiting for tape drives to do their thing, we&#8217;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&hellip; <a class=\"more-link\" href=\"http:\/\/orcldoug.com\/blog\/2007\/08\/07\/full-volume-database-refresh-part-3\/\">Continue reading <span class=\"screen-reader-text\">Full Volume Database Refresh &#8211; Part 3<\/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-1306","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1304,"url":"http:\/\/orcldoug.com\/blog\/2007\/08\/04\/full-volume-database-refresh-part-1\/","url_meta":{"origin":1306,"position":0},"title":"Full Volume Database Refresh &#8211; Part 1","date":"August 4, 2007","format":false,"excerpt":"(... 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\u2026","rel":"","context":"With 17 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":1306,"position":1},"title":"Full Volume Database Refresh &#8211; 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":1312,"url":"http:\/\/orcldoug.com\/blog\/2007\/09\/02\/full-volume-database-refresh-part-4\/","url_meta":{"origin":1306,"position":2},"title":"Full Volume Database Refresh &#8211; 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":1306,"position":3},"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":[]},{"id":1191,"url":"http:\/\/orcldoug.com\/blog\/2007\/01\/27\/dba-documentation-catalogue\/","url_meta":{"origin":1306,"position":4},"title":"DBA Documentation &#8211; 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":1417,"url":"http:\/\/orcldoug.com\/blog\/2008\/06\/08\/real-application-testing-and-more-relinking\/","url_meta":{"origin":1306,"position":5},"title":"Real Application Testing and more Relinking","date":"June 8, 2008","format":false,"excerpt":"I'd been thinking of blogging about the appearance of Real Application Testing in 10.2.0.4 since I noticed it post-upgrade, just before the course in Prague. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsIt looked like a\u2026","rel":"","context":"With 9 comments","img":{"alt_text":"","src":"\/serendipity\/uploads\/xRAT_7.png.pagespeed.ic.3MsqSFp_gp.png","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1306","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=1306"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1306\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1306"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1306"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1306"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}