实践--闪回表

最新推荐文章于 2024-09-09 09:47:53 发布

columsms27884

最新推荐文章于 2024-09-09 09:47:53 发布

阅读量117

点赞数

文章标签：数据库

闪回表实践

1.原理

Logical Flashback Features Useful in Backup and Recovery

The remaining flashback features operate at the logical level. The logical features documented in this chapter are as follows:

· Flashback Table

You can recover a table or set of tables to a specified point in time in the past without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations. Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers and constraints, and not requiring you to find and restore application-specific properties.

"Rewinding a Table with Flashback Table" explains how to use this feature.

你能够还原一个表或者多个表到以前的某一时刻，而且数据库在线可用。Flashback Table避免执行更加复杂的point-in-time recovery。 Flashback Table还原表的记录，而且相关的索引、触发器和约束也会被自动进行维护。

· Flashback Drop

You can reverse the effects of a DROP TABLE statement.

Flashback Drop 回退一个DROP TABLE操作

"Rewinding a DROP TABLE Operation with Flashback Drop" explains how to use this feature.

Note:

Because the logical flashback features have uses not specific to backup and recovery, some documentation for them is located elsewhere in the documentation set.

All logical flashback features except Flashback Drop rely on undo data. Used primarily for providing read consistency for SQL queries and rolling back transactions, undo records contain the information required to reconstruct data as it existed at a past time and examine the record of changes since that past time.

所有的logic flashback特性，除了Flashback Drop之外，都依赖于undo数据库。undo记录用来读一致和回滚事务，也用来重构数据库之前的状态和检查块变化的情况。

Flashback Drop relies on a mechanism called the recycle bin, which the database uses to manage dropped database objects until the space they occupied is needed for new data. There is no fixed amount of space allocated to the recycle bin, and no guarantee about how long dropped objects remain in the recycle bin. Depending on system activity, a dropped object may remain in the recycle bin for seconds or for months.

Flashback Drop依赖于recycle bin机制，recycle bin空间由系统自动管理，如果空间不够时，recycle会自动清空对象，释放空间，因此某个删除的对象，可能在recycle内的时间是不定的。

Rewinding a Table with Flashback Table

Flashback Table uses information in the undo tablespace rather than restored backups to retrieve the table. When a Flashback Table operation occurs, new rows are deleted and old rows are reinserted. The rest of your database remains available while the flashback of the table is being performed.

Prerequisites of Flashback Table

To use the Flashback Table feature on one or more tables, use the FLASHBACK TABLE SQL statement with a target time or SCN.

You must have the following privileges to use the Flashback Table feature:

· You must have been granted the FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object privilege on the table.

· You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.

· To flash back a table to a restore point, you must have the SELECT ANY DICTIONARY or FLASHBACK ANY TABLE system privilege or the SELECT_CATALOG_ROLE role.

For an object to be eligible（资格的） to be flashed back, the following prerequisites must be met:

· The object must not be included the following categories: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions.

· The structure of the table must not have been changed between the current time and the target flash back time.

The following DDL operations change the structure of a table: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (except adding a range partition).

· Row movement must be enabled on the table, which indicates that rowids will change after the flashback occurs.

This restriction exists because if rowids before the flashback were stored by the application, then there is no guarantee that the rowids correspond to the same rows after the flashback. If your application depends on rowids, then you cannot use Flashback Table.

· The undo data in the undo tablespace must extend far enough back in time to satisfy the flashback target time or SCN.

The point to which you can perform Flashback Table is determined by the undo retention period, which is the minimal time for which undo data is kept before being recycled, and tablespace characteristics. The undo data contains information about data blocks before they were changed. The flashback operation uses undo to re-create the original data.

To ensure that the undo information is retained for Flashback Table operations, Oracle suggests setting the UNDO_RETENTION parameter to 86400 seconds (24 hours) or greater for the undo tablespace.

Note:

FLASHBACK TABLE...TOBEFOREDROP is a use of the Flashback Drop feature, not Flashback Table, and therefore is not subject to these prerequisites. See "Rewinding a DROP TABLE Operation with Flashback Drop" for more information.

执行Flashback Table的限制，分为系统权限和对于哪些表和操作不适用于Flashback Table功能，主要如下：

必须对目标表有DML权限和SELECT ANY DICTIONARY or FLASHBACK ANY TABLE系统权限；

tables that are part of a cluste、materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions无法使用Flashback Table功能；

如果upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (except adding a range partition)操作被执行，无法闪回到这些操作以前的时间点；

Row movement must be enabled；

undo表空间足够大；

实践步骤

In this scenario, assume that you want to perform a flashback of the andy.test table after a user made some incorrect updates.

The perform a flashback of andy.test:

Connect SQL*Plus to the target database and identify the current SCN.

You cannot roll back a FLASHBACK TABLE statement, but you can issue another FLASHBACK TABLE statement and specify a time just before the current time. Therefore, it is advisable to record the current SCN. You can obtain it by querying V$DATABASE as follows:

无法对FLASHBACK TABLE语句进行回滚，但是你能够重新发布另外一个FLASHBACK TABLE语句来进行恢复。

SELECT CURRENT_SCN

FROM V$DATABASE;

--记录SCN为288003781

---删除表数据库，模拟故障

提交；

Identify the time, SCN, or restore point to which you want to return the table.

If you have created restore points, then you can list available restore points by executing the following query:

SELECT NAME, SCN, TIME

FROM V$RESTORE_POINT;

Ensure that enough undo data exists to rewind the table to the specified target.

If the UNDO_RETENTION intialization parameter is set, and the undo retention guarantee is on, then you can use the following query to determine how long undo data is being retained:

SELECT NAME, VALUE/60 MINUTES_RETAINED

FROM V$PARAMETER

WHERE NAME = 'undo_retention';

Ensure that row movement is enabled for all objects that you are rewinding with Flashback Table.

You can enable row movement for a table with the following SQL statement, where table is the name of the table that you are rewinding:

ALTER TABLE test ENABLE ROW MOVEMENT;

Determine whether the table that you intend to flash back has dependencies on other tables. If dependencies exist, then decide whether to flash back these tables as well.

You can issue the following SQL query to determine the dependencies, where schema_name is the schema for the table to be flashed back and table_name is the name of the table:

确定是否存在依赖关系。如果依赖存在，那么确定相关的表是否需要闪回。执行如下语句确定是否存在依赖关系。

SELECT other.owner, other.table_name

FROM sys.all_constraints this, sys.all_constraints other

WHERE this.owner = 'ANDY'

AND this.table_name = 'TEST'

AND this.r_owner = other.owner

AND this.r_constraint_name = other.constraint_name

AND this.constraint_type='R';

Execute a FLASHBACK TABLE statement for the objects that you want to flash back.

The following SQL statement returns the andy.test table to the restore point named temp_employees_update:

FLASHBACK TABLE andy.test

TO RESTORE POINT temp_employees_update;

The following SQL statement rewinds the andy.test table to its state when the database was at the time specified by the SCN:

FLASHBACK TABLE andy.test

TO SCN 288003781;

As shown in the following example, you can also specify the target point in time with TO_TIMESTAMP:

FLASHBACK TABLE andy.test

TO TIMESTAMP TO_TIMESTAMP('2007-10-17 09:30:00', 'YYYY-MM-DD HH:MI:SS');

Note:

The mapping of timestamps to SCNs is not always exact. When using timestamps with the FLASHBACKTABLE statement, the time to which the table is flashed back can vary by up to approximately three seconds of the time specified for TO_TIMESTAMP. If an exact point in time is required, then use an SCN rather than a time.