oracle flashback 知识点

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             318769536 bytes
Database Buffers           96468992 bytes
Redo Buffers                6094848 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8

SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL
-------------------- ---------

              891640 27-APR-15

SQL> flashback database to scn 891640;  闪回到scn891640

Flashback complete.

alter database open resetlogs;

练习

SQL> conn scott/weiwei;
已连接。
SQL> create table dept2 as select * from dept;

表已创建。
SQL> select * from dept2;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH         DALLAS
        30 SALES                CHICAGO
        40 OPERATIONS     BOSTON

21:51:57 SQL> delete from dept2 where deptno=40;

已删除 1 行。

21:52:30 SQL> commit;

提交完成。

21:52:38 SQL> alter table dept2 enable row movement;

表已更改。

21:55:19 SQL> flashback table dept2 to timestamp to_timestamp('2015-04-28 21:51:56','yyyy-mm-ddhh24:mi:ss');

闪回完成。

21:57:31 SQL> select * from dept2;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON  删除记录回来了