tag:blogger.com,1999:blog-27482298.post8816352684965440019..comments2024-02-25T22:29:18.572+00:00Comments on Andrew's Oracle Blog: LF_ROWS and DEL_LF_ROWSUnknownnoreply@blogger.comBlogger4125tag:blogger.com,1999:blog-27482298.post-41076520827838122772013-10-25T22:14:35.085+01:002013-10-25T22:14:35.085+01:00I have seen a disaster recovery set up where the p...I have seen a disaster recovery set up where the production and disaster recovery environments were identical but on 2 different networks. However I have not seen this idea used for test environments. I can imagine it would work well if done with proper controls.<br />I don't like the thought of using exp and imp to set up test environments.<br />Here is one method I have seen for copying production databases into test databases:<br />(1) Close the production database and make a snapshot of it at the hardware level. Open the production database again. This can be done in 15 minutes or so.<br />(2) Mount the production snapshot to the server hosting the test database.<br />(3) Copy the database's datafiles from the snapshot into the file system holding the test database. This can take a few hours.<br />(4) Connect to the production database and do an ALTER DATABASE BACKUP CONTROLFILE TO TRACE to get SQL to recreate the controlfile.<br />(5) Using a global replace, change the directory names in the trace file to get SQL to recreate the test database's controlfile. Use this SQL to create a test database with the desired name.<br />This method can be scripted with a bit of work so that it can be run regularly without intervention.<br /><br />However, I have to say that your idea of copying production directly into an identical test area on a separate network is superior!Andrew Reidhttps://www.blogger.com/profile/10622696064206411002noreply@blogger.comtag:blogger.com,1999:blog-27482298.post-71728691671535186722013-10-25T15:21:31.348+01:002013-10-25T15:21:31.348+01:00I concur that rebuilds should be the exception not...I concur that rebuilds should be the exception not the norm. In this case, I've just started with this company. The 'SYSTEM' DBAs are located in another city, 6 hours away. One of the tasks of my predecessor was to monitor the indexes. At a previous job, I had the luxury of rebuilding every user index every weekend. At another job, there was no scheduled index rebuilds at all. No task to even look to see if any required it. I am in the last day of my first 3 weeks with this company. At this point I have no idea how they refresh DEV and TEST from PRODUCTION. I'll pick that information up in little more than a week. <br />That being said, I have used two different methods of doing the refresh. <br />One used two different networks, where everything was identical. The refresh was just a physical restore of all the production files to the test servers. That should give the test environment where the indexes would be the same. That’s the job where I had the luxury to rebuild the world in 7 hours. Oracle 9 in a WINDOWS environment.<br />The other method used was to refresh only specific schemas. All the objects in the DEV instance would be dropped, then an IMP would be perform to import only that specific schema. All the indexes in that schema would be rebuilt. That was the REBUILD-LESS environment. Oracle 11 in a UNIX environment, still using DICTIONARY MANAGED tablespaces, something I am looking to address.<br />What method do you recommend for refreshing the DEV environment so that the desired testing can be performed? The choice is not limited to the two I’ve used. I’m always looking for better ways to perform tasks.Anonymoushttps://www.blogger.com/profile/17287758916192298690noreply@blogger.comtag:blogger.com,1999:blog-27482298.post-54391820350419155782013-10-24T21:23:37.000+01:002013-10-24T21:23:37.000+01:00Dear Mark,
Excellent question and thank you for t...Dear Mark,<br /><br />Excellent question and thank you for taking the time to comment on my blog.<br /><br />The answer to the first part is that we are able to take copies of our production databases to test index rebuilds and see whether they improve performance. In most cases they don't seem to make much difference at all.<br /><br />I agree that there is not a great deal of risk with index rebuilds. The main problem for me is that they just take so long to do if the tables concerned are large and I find it hard to justify them if I cannot demonstrate a tangible improvement as a result.<br /><br />Kind Regards,<br />AndrewAndrew Reidhttps://www.blogger.com/profile/10622696064206411002noreply@blogger.comtag:blogger.com,1999:blog-27482298.post-47437414007839747052013-10-24T19:51:39.759+01:002013-10-24T19:51:39.759+01:00How can "measurable performance improvement&q...How can "measurable performance improvement" be demonstrated without rebuilding the index? And what are the possible risks with an index rebuild?Anonymoushttps://www.blogger.com/profile/17287758916192298690noreply@blogger.com