oracle的flashback功能测试

一、flashback query

依赖于对UNDO信息的查询,只有在UNDO信息有效的时候才能查询出结果,否则会出现错误。

SYS@orcl>show parameter undo

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
undo_management              string     AUTO
undo_retention               integer     1800
undo_tablespace              string     UNDOTBS1

a. 查询某个时间点的数据

ADAM@orcl>create table t (n number);

Table created.

ADAM@orcl>insert into t values (10);

1 row created.

ADAM@orcl>commit;

Commit complete.

ADAM@orcl>select to_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss’) now from dual;
NOW
——————-
2011-04-26 21:38:50

ADAM@orcl>delete from t;

1 row deleted.

ADAM@orcl>commit;

Commit complete.

ADAM@orcl>select * from t;

no rows selected

ADAM@orcl>select * from t as of timestamp
to_timestamp(’2011-04-26 21:38:50′, ‘yyyy-mm-dd hh24:mi:ss’);

N
———-

10

b. 查询表所有的修改历史

ADAM@orcl>insert into t values(20);

1 row created.

ADAM@orcl>commit;

Commit complete.

ADAM@orcl>select to_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss’) now from dual;
NOW
——————-
2011-04-26 21:42:36

ADAM@orcl>column versions_starttime format a25;
ADAM@orcl>column versions_endtime format a25;
ADAM@orcl> select versions_starttime, versions_endtime, versions_operation, t.*
2 from t
3 versions between timestamp minvalue and maxvalue

VERSIONS_STARTTIME      VERSIONS_ENDTIME        V           N
------------------------- ------------------------- - ----------
26-APR-11 09.42.28 PM                    I          20
26-APR-11 09.39.19 PM                    D          10
26-APR-11 09.37.37 PM      26-APR-11 09.39.19 PM     I          10

3. 查询出恢复操作所需的sql语句

ADAM@orcl>select versions_xid from t versions between timestamp minvalue and maxvalue
2 where versions_operation = ‘D’;
VERSIONS_XID
—————-
0A001000F9000000

–切换到SYS用户
SYS@orcl>select undo_sql from flashback_transaction_query where xid = ’0A001000F9000000′;

UNDO_SQL
———————————————————————————————–
insert into “ADAM”.”T”(“N”) values (’10′);

二、表的flashback操作

–恢复到只有一条n=10记录的状态

SYS@orcl>select * from adam.t;

N
———-
20

SYS@orcl>flashback table adam.t to timestamp to_timestamp(’2011-04-26 21:38:50′, ‘yyyy-mm-dd hh24:mi:ss’);
flashback table adam.t to timestamp to_timestamp(’2011-04-26 21:38:50′, ‘yyyy-mm-dd hh24:mi:ss’)

*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

SYS@orcl>select row_movement from dba_tables where table_name = ‘T’;
ROW_MOVE
——–
DISABLED

SYS@orcl>alter table adam.t enable row movement;

Table altered.

SYS@orcl>select row_movement from dba_tables where table_name = ‘T’;
ROW_MOVE
——–
ENABLED

SYS@orcl>flashback table adam.t to timestamp to_timestamp(’2011-04-26 21:38:50′, ‘yyyy-mm-dd hh24:mi:ss’);

Flashback complete.

SYS@orcl>select * from adam.t;

N
———-
10

三、整个数据库的flashback,数据库必须运行于Archivelog状态

数据库ALTER DATABASE FLASHBACK ON后,性能有2%的降低,一般情况下还是可以容忍的。

SYS@orcl>archive log list;

Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /opt/oralog
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

–查询数据库的目前状态

SYS@orcl>select flashback_on from v$database;

SYS@orcl>select name, status from v$archived_log;
NAME
————————————————————————————————————————
S
-
/opt/ora10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORCL/archivelog/2011_03_15/o1_mf_1_5_6qyw7jkt_.arc
A
/opt/oralog/1_6_744231562.dbf
A
/opt/oralog/1_7_744231562.dbf
A
SYS@orcl>show parameter flashback
NAME
TYPE
VALUE
———————————— ———– ——————————
db_flashback_retention_target
integer
1440
SYS@orcl>select to_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss’) now from dual;
NOW
——————-
2011-04-26 22:25:02

SYS@orcl>insert into adam.t values (100);

1 row created.

SYS@orcl>commit;

Commit complete.

SYS@orcl>flashback database to timestamp to_timestamp(’2011-04-26 21:35:00′, ‘yyyy-mm-dd hh24:mi:ss’);
flashback database to timestamp to_timestamp(’2011-04-26 21:35:00′, ‘yyyy-mm-dd hh24:mi:ss’)
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.
SYS@orcl>shutdown;

Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl>startup mount exclusive;

ORACLE instance started.
Total System Global Area
524288000 bytes
Fixed Size

1220336 bytes
Variable Size

125829392 bytes
Database Buffers

390070272 bytes
Redo Buffers

7168000 bytes
Database mounted.
SYS@orcl>flashback database to timestamp to_timestamp(’2011-04-26 22:25:00′, ‘yyyy-mm-dd hh24:mi:ss’);

Flashback complete.

SYS@orcl>alter database open resetlogs;

Database altered.

SYS@orcl>select * from adam.t;

N
———-
10

四、关于drop table后的恢复,请参考oracle中的Recyclebin测试

参考文章:

Undo the past with Oracle’s Flashback Technology

Recovery Made Simple: Oracle Flashback Query

 

Comments are closed.