创建仅具有一组样本数据的生产的测试数据库副本 [英] Create a Test Database copy of Production with only a sample set of Data

问题描述

我们有2个Oracle数据库。一个是我们的生产客户数据库。另一个是测试数据库。测试数据库是没有任何数据的生产副本...只是空表。我试图想出一个高效和有效的方法将一个示例数据从生产数据库复制到测试。

We have 2 Oracle databases. One is our Production customer database. The other is a test database. The Test database is a copy of production without any data...just empty tables. I am trying to come up with an efficient and effective way to copy a sample set of data from the Production db to the Test.

我需要从一个示例列表开始的PKs，然后编写一个过程来填充测试数据库的客户表与该样本。然后，我需要根据样本填充子表格的相关数据。

I need to start with a sample list of PKs from our Customer table and write a process to populate the test database Customer table with that sample. Then, I need to populate child tables with relevant data based on the sample.

我们以前有一个Java进程，使用一个预备语句从生产中选择数据。 ..基于结果集创建一个文件...然后使用update语句将其导入测试数据库。这是非常低效的。

We used to have a Java process that would use a prepared statement to select data from Production...build a file based on the result set...then use an update statement to get it into the Test db. It was horribly inefficient.

我不是DBA，所以我不太了解这个领域。我试图谷歌一些关于数据库链接的信息，但没有找到很好的例子。

I am not a DBA so I don't know much of that realm. I tried to Google some info about Database Links but couldn't find very good examples.

有人知道有办法吗？或者，至少给我一个起点？

Does anyone know of a way to do this...or, at least give me a starting point?

推荐答案

如果你有足够的空间，我建议复制整个生产数据进行测试。这将更容易管理，它也可以是一个良好的机会，测试您的备份（从备份恢复到一个新的实例）。

If you have enough space, I would suggest copying the entire production data to test. It will be a lot easier to administer, it could also be a good oportunity to test your backup (restore from backup to a new instance).

从开发人员的角度来看，如果没有代表性的数据，您将无法可靠地测试应用程序的性能。此数据集应具有与生产数据相同的属性（数据量，物理分布...）。最简单的方法是在测试中生成与生产中相同的数据。

From a developer point of view, you won't be able to test the performance of your application reliably without a representative set of data. This data set should have the same properties as the production data (data volume, physical distribution...). The easiest way to achieve this is to have the same data in test as in production.

如果您能负担得起停机时间，您可以停止生产数据库，将文件复制到测试服务器并安装这两个数据库如果你不能承受停机时间，那么可能是一个好主意，拿起一些DBA技能（最终了解热备份，然后恢复到一个新的实例）。

If you can afford downtime you could stop the production db, copy the file to the test server and mount both databases. If you can't afford downtime it might be a good idea to pickup some DBA skills (and eventually learn about hot backup then restore to a new instance).

更新：如果实际复制数据库是不可行的，您应该查看批量复制数据 expdp 和 impdp （或旧的 exp / imp ）。您可以复制所有架构或过滤数据导出。在这种情况下，您将手动选择适当的WHERE子句。批量导出和导入的速度要比逐行复制数据快几个数量级。

Update: if physically copying your database is not feasible, you should look into bulk copying data with expdp and impdp (or the old exp/imp). You can either copy all schemas or filter the data on export. You would choose appropriate WHERE clause by hand in this case. Exporting and importing in bulk will be orders of magnitude faster than copying data row by row.

这篇关于创建仅具有一组样本数据的生产的测试数据库副本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！