Oracle 10g 六种Flashback

最新推荐文章于 2024-07-08 21:32:26 发布

cuigaijiao2361

最新推荐文章于 2024-07-08 21:32:26 发布

阅读量97

点赞数

文章标签：数据库

Oracle 10g 支持六种闪回功能：Flashback Database、Flashback Table、Flashback Drop、Flashback Query、Flashback version Query、Flashback Transaction Query

一、Flashback Database：（基于闪回日志）

Oracle Flashback Database lets you quickly recover an Oracle database to a previous time to correct problems caused by logical data corruptions or user errors.

注：①Flashback Database不能撤销物理损坏，而只能恢复用户错误造成的逻辑错误。

②Flashback Database在功能上等价于不完整数据库恢复。但是Flashback Database通常情况下比不完整恢复要快，原因在于：不完整恢复部分过程是完全还原，其时间在很大程度上与数据库的大小成正比；而Flashback Database所需时间主要取决于需要撤销的更改的数量。任何常规环境下，与数据库总数据量相比，更改的数据量微不足道。

1、配置Flashback Database

（1）、database 必须处于 Archivelog

SYS@ tsid > select log_mode from v$database;

LOG_MODE

------------

ARCHIVELOG

（2）、必须创建flash_recovery_area

两个实例参数：db_recovery_file_dest、db_recovery_file_dest_size

SYS@tsid>alter system set db_recovery_file_dest='E:\oracle\product\10.2.0\flash_recovery_area';

System altered.

SYS@ tsid > alter system set db_recovery_file_dest_size=2G;

System altered.

（3）、设置flashback retention target

实例参数：DB_FLASHBACK_RETENTION_TARGET，单位分钟，默认一天

SYS@ tsid > alter system set db_flashback_retention_target=240;

System altered.

（4）、shutdown immediate;

（5）、startup mount;

SYS@ tsid > alter database flashback on;

Database altered.

此处注意：若数据库允许多个instance同时访问，可能会出现错误提示：

ORA-38759: Database must be mounted by only one instance and not open.

此时，必须在startup mount exclusive状态。关于startup mount exclusive，参考：http://space.itpub.net/25744374/viewspace-732248

2、使用Flashback Database

可以使用SQL*PLUB、RMAN、Database Control

方法：shutdown -> mount -> flashback database -> resetlogs

（1）、SQL*PLUS（timestamp、SCN）

SYS@ tsid > shutdown abort; /*使用任何类型的关闭无意义，都会丢失正在进行的工作*/

ORACLE instance shut down.

SYS@ tsid > startup mount;

ORACLE instance started.

Total System Global Area 209715200 bytes

Fixed Size 1289724 bytes

Variable Size 180355588 bytes

Database Buffers 20971520 bytes

Redo Buffers 7098368 bytes

Database mounted.

SYS@ tsid > flashback database to timestamp to_timestamp('2012-04-17 11:00:00','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SYS@ tsid > alter database open resetlogs;

Database altered.

=====================================================================

SYS@ tsid > shutdown abort;

ORACLE instance shut down.

SYS@ tsid > startup mount;

ORACLE instance started.

Total System Global Area 209715200 bytes

Fixed Size 1289724 bytes

Variable Size 176161284 bytes

Database Buffers 25165824 bytes

Redo Buffers 7098368 bytes

Database mounted.

SYS@ tsid > flashback database to SCN 3030912;

Flashback complete.

注意：因为时间点很难确定，可以将数据打开至read only模式，进行查询，根据情况，重复执行闪回和恢复命令，直到找到希望的时间。

alter database open read only;

recover database until time ‘2012-04-17:11:02:00’;

（2）、RMAN（time、SCN、sequence）

RMAN> shutdown abort;

Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 209715200 bytes

Fixed Size 1289724 bytes

Variable Size 176161284 bytes

Database Buffers 25165824 bytes

Redo Buffers 7098368 bytes

RMAN> flashback database to scn=3035221;

Starting flashback at 2012-04-17 11:56:20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

starting media recovery

media recovery complete, elapsed time: 00:00:15

Finished flashback at 2012-04-17 11:56:36

RMAN> alter database open resetlogs;

database opened

===============================================================

RMAN> shutdown immediate

using target database control file instead of recovery catalog

database closed

database dismounted

Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 209715200 bytes

Fixed Size 1289724 bytes

Variable Size 176161284 bytes

Database Buffers 25165824 bytes

Redo Buffers 7098368 bytes

RMAN> flashback database to sequence=25;

Starting flashback at 2012-04-17 12:12:42

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

starting media recovery

....

media recovery complete, elapsed time: 00:00:28

Finished flashback at 2012-04-17 12:13:14

（3）、Database Control

Database Control 的基于时间的闪回粒度只能是分钟，RMAN可以闪回到秒，SQL*PLUS可以闪回到时间戳——它可以是1秒的百万分之一。

3、监视Flashback Database

（1）、闪回缓冲区不受DBA限制，但可以查看当前闪回缓冲区的大小。

SYS@ tsid > select * from v$sgastat where name='flashback generation buff';

POOL NAME BYTES

------------ -------------------------- ----------

shared pool flashback generation buff 3981204

（2）、关闭不必要表空间的Flashback 功能，从而限制闪回数据量。

SYS@ tsid > alter tablespace test flashback off;

Tablespace altered.

要重新开启，在mount状态下，alter tablespace test flashback on;

通过视图v$tablespace可以查看表空间闪回状态：

SYS@ tsid > select name,flashback_on from v$tablespace;

NAME FLA

------------------------------ ---

SYSTEM YES

UNDOTBS1 YES

SYSAUX YES

USERS YES

TEST NO

TEMP YES

UNDOTBS2 YES

7 rows selected.

二、Flashback drop：（基于RECYCLEBIN）

Oracle Flashback Drop provides a way to restore accidentally dropped tables.

注：①Flashback Drop仅适用于表，但也会恢复所有相关的对象：索引、触发器、权限、唯一的主键和非空约束。但不包括外键约束。

②只能闪回drop的表，不能闪回truncate的表。

③若删除的表所占用的空间被重用，将不能闪回。

④SYSTEM表空间的表直接删除，而不是rename，不进回收站，不能Flashback drop。

⑤如果删除了用户，如：drop user scott cascade;则无法恢复任何SCOTT的表。

⑥闪回其间可以对表重命名，以防止以前的表名已被占用，但是和表一同闪回的索引、触发器、约束都保持回收站中的名称，只能手动重命名。

1、管理RECYCLEBIN

（1）回收站受参数recyclebin参数的影响，默认情况下，recyclebin参数为ON，说明启用回收站，则删除的表可以进回收站，支持Flashback drop。若recyclebin参数为OFF，则删除的表不进回收站。

（2）查询回收站：show recyclebin;

Select owner,owner,original_name,type,droptime,can_undrop,space from dba_recyclebin; (can_undrop列显示是否可Flashback Drop)

（3）对回收站空间进行回收

注意：①有关空间使用情况的正常诊断程序会忽略回收站占用的空间，这就造成在活动对象占用的空间达到警告和临界空间的使用级别之前，不会触发“表空间比例已满”的警报。因此，删除表，DBA_FREE_SPACE显示空闲空间，而实际上DBA_SEGMENTS将会显示段空间仍被占用。

②若表空间自动扩展，Oracle优先重写回收站而不是扩展数据文件。

SYS@ tsid > create tablespace small datafile 'E:\ORADATA\TEST\small.DBF' size 1M;

Tablespace created.

SYS@ tsid > create table t1(id number,name varchar(20),address varchar2(40)) tablespace small;

Table created.

插入数据，直到提示：ORA-01653: unable to extend table SYS.T1 by 8 in tablespace SMALL

查看DBA_FREE_SPACE，显示没有可用空间。删除表t1，再次查询：

SYS@ tsid > select * from dba_free_space where tablespace_name='SMALL';

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO

------------------------------ ---------- ---------- ---------- ---------- ------------

SMALL 8 9 65536 8 8

SMALL 8 17 65536 8 8

SMALL 8 25 65536 8 8

SMALL 8 33 65536 8 8

SMALL 8 41 65536 8 8

SMALL 8 49 65536 8 8

SMALL 8 57 65536 8 8

SMALL 8 65 65536 8 8

SMALL 8 73 65536 8 8

SMALL 8 81 65536 8 8

SMALL 8 89 65536 8 8

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO

------------------------------ ---------- ---------- ---------- ---------- ------------

SMALL 8 97 65536 8 8

SMALL 8 105 65536 8 8

SMALL 8 113 65536 8 8

SMALL 8 121 65536 8 8

15 rows selected.

而查看DBA_SEGMENTS：

SYS@ tsid > select segment_name,bytes from dba_segments where tablespace_name='SMALL';

SEGMENT_NAME BYTES

--------------------------------------------------------------------------------- ----------

BIN$6qV8lQb4RHSLKm7RP376ng==$0 983040

======================================================================

清除回收站对象 purge:

Purge table

Purge index

Purge tablespace

Purge tablespace user

Purge user_recyclebin;

Purge dba_recyclebin;

三、Flashback Query（基于UNDO）

注：SYS用户不支持！

（1）、单一查询方式

TEST@ tsid > select * from t;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

5 e

6 f

6 rows selected.

TEST@ tsid > delete from t where id=6;

1 row deleted.

TEST@ tsid > delete from t where id=5;

1 row deleted.

TEST@ tsid > commit;

Commit complete.

TEST@ tsid > select current_scn from v$database;

CURRENT_SCN

-----------

3085265

TEST@ tsid > select * from t;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

TEST@ tsid > select * from t as of scn 3085250;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

5 e

6 f

6 rows selected.

TEST@ tsid > select * from t as of scn 3085250 minus select * from t;

ID NAME

---------- ----------

5 e

6 f

TEST@ tsid > insert into t select * from t as of scn 3085250 minus select * from t;

2 rows created.

TEST@ tsid > commit;

Commit complete.

TEST@ tsid > select * from t;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

5 e

6 f

6 rows selected.

==========================================================================

（2）、使用DBMS_FLASHBACK程序包，将整个会话回退到过去某个时间点。

TEST@ tsid > select * from t;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

5 e

6 f

6 rows selected.

TEST@ tsid > select systimestamp from dual;

SYSTIMESTAMP

--------------------------------------------

17-APR-12 04.31.46.907000 PM +08:00

TEST@ tsid > delete from t where id=6;

1 row deleted.

TEST@ tsid > commit;

Commit complete.

TEST@ tsid > execute dbms_flashback.enable_at_time(to_timestamp('17-04-12 16:31:46','dd-mm-yy hh24:mi:ss'));

PL/SQL procedure successfully completed.

TEST@ tsid > select * from t;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

5 e

6 f

6 rows selected.

TEST@ tsid > execute dbms_flashback.disable;

PL/SQL procedure successfully completed.

TEST@ tsid > select * from t;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

5 e

四、Flashback Table

注：sys用户不支持该闪回！

==========================================================================

SQL> select * from t;

ID NAME

---------- ----------

1 a

2 b

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

2082114

SQL> update t set name='a' where id=2;

已更新 1 行。

SQL> commit;

提交完成。

SQL> select * from t;

ID NAME

---------- ----------

1 a

2 a

SQL> select * from t as of scn 2082114;

ID NAME

---------- ----------

1 a

2 b

SQL> flashback table t to scn 2082114;

flashback table t to scn 2082114

第 1 行出现错误:

ORA-08189: 因为未启用行移动功能, 不能闪回表

SQL> alter table t enable row movement;

表已更改。

SQL> flashback table t to scn 2082114;

闪回完成。

SQL> select * from t;

ID NAME

---------- ----------

1 a

2 b

当要闪回的表存在外键约束时，可能出现问题。有两种方法可以解决错误：

1、先闪回主键表，再闪回外键表。

2、如果涉及多个表和许多DML语句，逻辑上很难找到一个生效的顺序。解决方法是一起闪回这两个表。如：

Flashback table emp,dept to timestamp to_timestamp(’17-04-12 16:30:00’,’dd-mm-yy hh24:mi:ss’);

Oracle在一个事务中同时闪回两个表，并且仅在该事务的结尾检查约束，此时数据库在逻辑上是一致的。所以这样闪回会成功。

五、Flashback version Query

注：Flashback version Query不能用于外部表、临时表、V$视图，这些对象不生成undo

select versions_xid,versions_operation,versions_startscn,

versions_starttime,versions_endscn,versions_endtime,id,name

from t versions between scn minvalue and maxvalue;

六、Flashback Transaction Query

注：①查询视图FLASHBACK_TRANSACTION_QUERY必须具有SELECT ANY TRANSACTION权限。

②使用Flashback Transaction Query，需要开启supplemental logging：

SYS@ tsid > alter database add supplemental log data;

Database altered.

③视图FLASHBACK_TRANSACTION_QUERY可以返回commit和active事务。官网对其中两个字段这样描述：

COMMIT_SCN

NUMBER

Transaction commit system change number; NULL for active transactions

COMMIT_TIMESTAMP

DATE

Transaction commit timestamp; NULL for active transactions

TEST@ tsid > select * from t

2 ;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

5 e

TEST@ tsid > update t set name='f' where id<>1;

4 rows updated.

TEST@ tsid > commit;

Commit complete.

TEST@ tsid > select * from t;

ID NAME

---------- ----------

1 a

2 f

3 f

4 f

5 f

TEST@ tsid > select id,name,versions_xid from t versions between timestamp (systimestamp - 3/1440) and systimestamp where id<

>1;

ID NAME VERSIONS_XID

---------- ---------- ----------------

4 f 0900280048030000

3 f 0900280048030000

2 f 0900280048030000

2 b

3 c

4 d

5 f 0900280048030000

5 e

8 rows selected.

TEST@ tsid > col undo_sql format a90

TEST@ tsid > select operation,undo_sql from flashback_transaction_query where xid=hextoraw('0900280048030000');

OPERATION UNDO_SQL

-------------------------------- --------------------------------------------------------------------------------

UPDATE update "TEST"."T" set "NAME" = 'e' where ROWID = 'AAADB0AAEAAAAAmAAA';

UNKNOWN

UPDATE update "TEST"."T" set "NAME" = 'd' where ROWID = 'AAADB0AAEAAAAAkAAD';

UNKNOWN

UPDATE update "TEST"."T" set "NAME" = 'c' where ROWID = 'AAADB0AAEAAAAAkAAC';

UNKNOWN

UPDATE update "TEST"."T" set "NAME" = 'b' where ROWID = 'AAADB0AAEAAAAAkAAB';

UNKNOWN

BEGIN

9 rows selected.

TEST@ tsid > update "TEST"."T" set "NAME" = 'e' where ROWID = 'AAADB0AAEAAAAAmAAA';

1 row updated.

TEST@ tsid > update "TEST"."T" set "NAME" = 'd' where ROWID = 'AAADB0AAEAAAAAkAAD';

1 row updated.

TEST@ tsid > update "TEST"."T" set "NAME" = 'c' where ROWID = 'AAADB0AAEAAAAAkAAC';

1 row updated.

TEST@ tsid > update "TEST"."T" set "NAME" = 'b' where ROWID = 'AAADB0AAEAAAAAkAAB';

1 row updated.

TEST@ tsid > commit;

Commit complete.

TEST@ tsid > select * from t;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

5 e

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25744374/viewspace-721460/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25744374/viewspace-721460/

cuigaijiao2361

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle 10g 六种Flashback

Oracle 10g 支持六种闪回功能：Flashback Database、Flashback Table、Flashback Drop、Flashback Query、Flashbackversion Query...
复制链接

扫一扫