拍摄不断更新的 Oracle 数据库的“快照”的最佳方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4058356/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What's the best way to take a "snapshot" of an Oracle database that is constantly being updated?
提问by Bhasker Pandya
I want to take a consistent snapshot of an Oracle database that is constantly being updated by a TIBCO DB Adaptor.
我想对由 TIBCO DB Adaptor 不断更新的 Oracle 数据库拍摄一致的快照。
Typcially TIBCO updates a bunch of tables at once, and then COMMITs. If I traverse through all the tables, taking a snapshot once a day, then I could grab data from table A before the commit, and from table B after the commit - but if A and B have a relationship, then they will no longer match properly.
通常，TIBCO 一次更新一堆表，然后提交。如果我遍历所有表，每天拍摄一次快照，那么我可以在提交前从表 A 中获取数据，在提交后从表 B 中获取数据 - 但如果 A 和 B 有关系，那么它们将不再匹配适当地。
Is "SET TRANSACTION READ ONLY" the way to go?
e.g.
“SET TRANSACTION READ ONLY”是要走的路吗？
例如
COMMIT
SET TRANSACTION READ ONLY
SELECT * FROM A WHERE A.ADB_UPDATEDDATE > TODAY()-1
SELECT * FROM B WHERE B.ADB_UPDATEDDATE > TODAY()-1
etc.
COMMIT
(TODAY syntax might not be correct, not important!)
（TODAY 语法可能不正确，不重要！）
Or is there something better that I can do?
或者我可以做些什么更好的事情？
采纳答案by Gaius
回答by dpbradley
If by "snapshot" you mean a full copy of the database in a consistent mode, then I would restore the database from a backup and recover it until the desired point in time. The Oracle recovery processes will take care of of the consistency (tracked by System Change Number or SCN).
如果“快照”是指在一致模式下数据库的完整副本，那么我将从备份中恢复数据库并将其恢复到所需的时间点。Oracle 恢复过程将负责一致性（由系统更改号或 SCN 跟踪）。
If you are using RMAN for backups and recovery, there is a "DUPLICATE DATABASE" command with a time clause that will make this relatively painless.
如果您使用 RMAN 进行备份和恢复，则有一个带有时间子句的“DUPLICATE DATABASE”命令可以使这相对轻松。
On the other hand, if you're just looking to extract a few tables in a consistent mode I can think of two options:
另一方面，如果您只是想以一致的模式提取一些表，我可以想到两个选项：
- Export the group of tables with the consistent=y option of the (older) exp utility
- Use the newer expdp utility with the flashback_time option
- 使用（旧）exp 实用程序的consistent=y 选项导出表组
- 使用带有 flashback_time 选项的较新的 expdp 实用程序
回答by Leigh Riffel
In addition to dpbradley's suggestions, if it is only a few not too big tables and you have flashback query available you could create a copy of the tables using a flashback query as of the same timestamp.
除了 dpbradley 的建议之外，如果只有几个不太大的表并且您有可用的闪回查询，您可以使用相同时间戳的闪回查询创建表的副本。
回答by elwood
Maybe doing an export in consistent mode helps. Have a look at http://www.dba-oracle.com/tips_oracle_export_utility.htm
也许在一致模式下进行导出会有所帮助。看看http://www.dba-oracle.com/tips_oracle_export_utility.htm
回答by rogerdpack
You could use "storage level" based snapshots as well, though oracle seems to think that using RMAN is a better way to go: http://www.oracle.com/technetwork/database/features/availability/rman-fra-snapshot-322251.html
您也可以使用基于“存储级别”的快照，尽管 oracle 似乎认为使用 RMAN 是更好的方法：http: //www.oracle.com/technetwork/database/features/availability/rman-fra-snapshot -322251.html
回答by Ilia Maskov
First of all as others guys have said there are special tools for "snapshotting"in Oracle and it's better to use it for the task for me. But if we look on the problem in particular we will see that it's a non-repeatable reads & phantom readsproblem [1], so it's all about transaction isolation levels. We have the SERIALISABLElevel in Oracle to avoid (but it doesn't mean that it's good for your task in general case)these problems [2], so if you don't want to get some surprisesand want to get your database in consistent state on certain moment of time (your transaction start point time) you should do this:
首先，正如其他人所说，Oracle 中有用于“快照”的特殊工具，最好将它用于我的任务。但是，如果我们特别关注这个问题，我们会发现这是一个不可重复读取和幻读问题 [1]，所以这完全与事务隔离级别有关。我们SERIALISABLE在 Oracle 中有级别可以避免（但这并不意味着它在一般情况下对您的任务有益）这些问题 [2]，因此如果您不想得到一些惊喜并希望使您的数据库保持一致在特定时刻（您的交易开始点时间）的状态，您应该这样做：
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Links:
链接：
