使用 sqlplus 或 rman 备份/恢复用于 oracle 10g 测试的数据库

Backup/Restore database for oracle 10g testing using sqlplus or rman

提问by crackity_jones

Using Oracle 10g with our testing server what is the most efficient/easy way to backup and restore a database to a static point, assuming that you always want to go back to the given point once a backup has been created.

将 Oracle 10g 与我们的测试服务器一起使用，这是将数据库备份和还原到静态点的最有效/最简单的方法，假设您总是希望在创建备份后返回给定点。

A sample use case would be the following

示例用例如下

1. install and configure all software
2. Modify data to the base testing point
3. take a backup somehow (this is part of the question, how to do this)
4. do testing
5. return to step 3 state (restore back to backup point, this is the other half of the question)

1. 安装和配置所有软件
2. 修改数据到基础测试点
3. 以某种方式进行备份（这是问题的一部分，如何做到这一点）
4. 做测试
5. 返回第3步状态（恢复回备份点，这是另一半问题）

Optimally this would be completed through sqlplus or rman or some other scriptable method.

最好通过 sqlplus 或 rman 或其他一些可编写脚本的方法来完成。

采纳答案by crackity_jones

You do not need to take a backup at your base time. Just enable flashback database, create a guaranteed restore point, run your tests and flashback to the previously created restore point.

您无需在基准时间进行备份。只需启用闪回数据库，创建一个有保证的还原点，运行您的测试并闪回到之前创建的还原点。

The steps for this would be:

这样做的步骤是：

1. Startup the instance in mount mode.

   startup force mount;

2. Create the restore point.

   create restore point before_test guarantee flashback database;

3. Open the database.

   alter database open;

4. Run your tests.

5. Shutdown and mount the instance.

   shutdown immediate; startup mount;

6. Flashback to the restore point.

   flashback database to restore point before_test;

7. Open the database.

   alter database open;

1. 以挂载模式启动实例。

   启动强制安装；

2. 创建还原点。

   创建还原点 before_test 保证闪回数据库；

3. 打开数据库。

   更改数据库打开；

4. 运行您的测试。

5. 关闭并挂载实例。

   立即关机；启动挂载；

6. 闪回还原点。

   闪回数据库还原点before_test；

7. 打开数据库。

   更改数据库打开；

回答by Rich Adams

You could use a feature in Oracle called Flashbackwhich allows you to create a restore point, which you can easily jump back to after you've done testing.

您可以使用 Oracle 中称为Flashback的功能，它允许您创建一个还原点，您可以在完成测试后轻松跳转回。

Quoted from the site,

引自网站，

Flashback Database is like a 'rewind button' for your database. It provides database point in time recovery without requiring a backup of the database to first be restored. When you eliminate the time it takes to restore a database backup from tape, database point in time recovery is fast.

闪回数据库就像数据库的“倒带按钮”。它提供数据库时间点恢复，而无需首先恢复数据库的备份。当您消除从磁带恢复数据库备份所需的时间时，数据库时间点恢复会很快。

回答by robertpostill

From my experience import/export is probably the way to go. Export creates a logical snapshot of your DB so you won't find it useful for big DBs or exacting performance requirements. However it works great for making snapshots and whatnot to use on a number of machines.

根据我的经验，导入/导出可能是要走的路。导出会创建数据库的逻辑快照，因此您不会发现它对大型数据库或严格的性能要求很有用。然而，它非常适合制作快照以及在许多机器上不使用的东西。

I used it on a rails project to get a prod snapshot that we could swap between developers for integration testing and we did the job within rake scripts. We wrote a small sqlplus script that destroyed the DB then imported the dump file over the top.

我在 rails 项目中使用它来获取生产快照，我们可以在开发人员之间交换以进行集成测试，我们在 rake 脚本中完成了这项工作。我们编写了一个小的 sqlplus 脚本来破坏数据库，然后在顶部导入转储文件。

Some articles you may want to check: OraFAQ CheatsheetOracle Wiki

您可能想查看的一些文章： OraFAQ Cheatsheet Oracle Wiki

Oracle apparently don't like imp/exp any more in favour of data pump, when we used data pump we needed things we couldn't have (i.e. SYSDBA privileges we couldn't get in a shared environment). So take a look but don't be disheartened if data pump is not your bag, the old imp/exp are still there :)

Oracle 显然不再喜欢 imp/exp 支持数据泵，当我们使用数据泵时，我们需要我们无法拥有的东西（即我们在共享环境中无法获得的 SYSDBA 特权）。所以看看，如果数据泵不是你的包，不要灰心，旧的 imp/exp 仍然存在:)

I can't recommend RMAN for this kind of thing becuase RMAN takes a lot of setup and will need config in the DB (it also has its own catalog DB for backups which is a pain in the proverbial for a bare metal restore).

我不能推荐 RMAN 用于这种事情，因为 RMAN 需要进行大量设置并且需要在数据库中进行配置（它也有自己的目录数据库用于备份，这对于裸机恢复来说是众所周知的痛苦）。

回答by Michael Ridley

If you are using a filesystem that supports copy-on-write snapshots, you could set up the database to the state that you want. Then shut down everything and take a filesystem snapshot. Then go about your testing and when you're ready to start over you could roll back the snapshot. This might be simpler than other options, assuming you have a filesystem which supports snapshots.

如果您使用的文件系统支持写时复制快照，您可以将数据库设置为您想要的状态。然后关闭所有内容并拍摄文件系统快照。然后开始您的测试，当您准备好重新开始时，您可以回滚快照。假设您有一个支持快照的文件系统，这可能比其他选项更简单。

回答by Matthew Watson

@Michael Ridley solution is perfectly scriptable, and will work with any version of oracle.

@Michael Ridley 解决方案完全可以编写脚本，并且适用于任何版本的 oracle。

This is exactly what I do, I have a script which runs weekly to

这正是我所做的，我有一个每周运行的脚本

1. Rollback the file system
2. Apply production archive logs
3. Take new "Pre-Data-Masking" FS snapshot
4. Reset logs
5. Apply "preproduction" data masking.
6. Take new "Post-Data-Masking" snapshot (allows rollback to post masked data)
7. Open database

1. 回滚文件系统
2. 应用生产归档日志
3. 拍摄新的“Pre-Data-Masking”FS 快照
4. 重置日志
5. 应用“预生产”数据屏蔽。
6. 拍摄新的“数据屏蔽后”快照（允许回滚以发布屏蔽数据）
7. 打开数据库

This allows us to keep our development databases close to our production database.

这使我们能够将我们的开发数据库保持在我们的生产数据库附近。

To do this I use ZFS.

为此，我使用 ZFS。

This method can also be used for your applications, or even you entire "environment" (eg, you could "rollback" your entire environment with a single (scripted) command.

此方法也可用于您的应用程序，甚至整个“环境”（例如，您可以使用单个（脚本化）命令“回滚”整个环境。

If you are running 10g though, the first thing you'd probably want to look into is Flashback, as its built into the database.

但是，如果您运行的是 10g，您可能首先要考虑的是 Flashback，因为它内置于数据库中。