【Flashback】11g的閃回資料歸檔初探

1、準備環境

1）、建立閃回資料歸檔使用的表空間

SQL> create tablespace fla_tbs1

datafile '/u01/app/oracle/oradata/ORCL/fla_tbs01.dbf'

size 10M reuse autoextend on next 640K maxsize 500M

extent management local segment space management auto; 

Tablespace created.

2）、建立一個資料歸檔的管理使用者，並授予相關許可權

SQL> create user archive_admin identified by oracle

  2  default tablespace fla_tbs1

  3  temporary tablespace temp

  4  account unlock; 

User created. 

SQL> GRANT ALTER SESSION TO ARCHIVE_ADMIN;

Grant succeeded. 

SQL> GRANT CREATE DATABASE LINK TO ARCHIVE_ADMIN;

Grant succeeded. 

SQL> GRANT CREATE SEQUENCE TO ARCHIVE_ADMIN;

Grant succeeded. 

SQL> GRANT CREATE SESSION TO ARCHIVE_ADMIN;

Grant succeeded. 

SQL> GRANT CREATE SYNONYM TO ARCHIVE_ADMIN;

Grant succeeded. 

SQL> GRANT CREATE VIEW TO ARCHIVE_ADMIN;

Grant succeeded. 

SQL> GRANT UNLIMITED TABLESPACE TO ARCHIVE_ADMIN;

Grant succeeded. 

SQL> GRANT EXECUTE ON SYS.DBMS_STATS TO ARCHIVE_ADMIN;

Grant succeeded. 

SQL> GRANT CONNECT, RESOURCE TO ARCHIVE_ADMIN;

Grant succeeded. 

SQL> grant flashback archive administer to archive_admin;

Grant succeeded.

2、建立資料歸檔

SQL> conn archive_admin/oracle

Connected. 

SQL> show user

USER is "ARCHIVE_ADMIN" 

SQL> create flashback archive fla1

  2  tablespace fla_tbs1

  3  quota 10M       

  4  retention 1 year; 

Flashback archive created.

3、授予scott使用者使用資料歸檔fla1的許可權

SQL> grant flashback archive on fla1 to scott;

Grant succeeded.

 

4、連線scott使用者，配置emp表使用資料歸檔。

SQL> conn scott/tiger

Connected. 

SQL> alter table emp flashback archive fla1;

Table altered.

注意：如果emp使用另外的資料歸檔，則報錯。

ORA-55600: The table "SCOTT"."EMP" is already enabled for Flashback Archive

5、做些DML操作

SQL> select * from emp where empno=7902;

EMPNO  ENAME  JOB    MGR HIREDATE      SAL    COMM   DEPTNO

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

7902   FORD   ANALYST    7566   03-DEC-81  4000          20

 

SQL> update emp set sal=sal+1000 where empno=7902;

1 row updated. 

SQL> commit;

Commit complete. 

SQL> update emp set sal=sal+1000 where empno=7902;

1 row updated. 

SQL> commit;

Commit complete. 

SQL> update emp set sal=sal+1000 where empno=7902;

1 row updated. 

SQL> commit;

Commit complete. 

SQL> select * from emp where empno=7902; 

EMPNO  ENAME  JOB    MGR HIREDATE      SAL    COMM   DEPTNO

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

7902   FORD   ANALYST    7566   03-DEC-81  7000          20

查詢資料歸檔的內部表名。

SQL> select * from user_flashback_archive_tables;

 

TABLE_NAME                     OWNER_NAME

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

FLASHBACK_ARCHIVE_NAME

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

ARCHIVE_TABLE_NAME                                    STATUS

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

EMP                            SCOTT

FLA1

SYS_FBA_HIST_73181                                    ENABLED

 

6、透過更換undo表空間，已達到清除原undo表空間中的‘舊’資料。

SQL> conn / as sysdba

Connected.

 

SQL> show parameter undo_tablespace

NAME                            TYPE          VALUE

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

undo_tablespace                    string     UNDOTBS1

 

SQL> create undo tablespace undotbs2

  2  datafile '/u01/app/oracle/oradata/ORCL/undotbs02.dbf'

  3  size 100M reuse autoextend on;

Tablespace created.

 

SQL> alter system set undo_tablespace=undotbs2;

System altered.

 

SQL> drop tablespace UNDOTBS1;

Tablespace dropped.

 

SQL> show parameter undo_tablespace
NAME                            TYPE          VALUE

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

undo_tablespace                    string     UNDOTBS2

 

7、使用閃回查詢，查詢過去的資料。

SQL> select * from emp

  2  as of timestamp (systimestamp - interval '18' minute)

  3  where empno=7902;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

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

    DEPTNO

----------

      7902 FORD       ANALYST         7566 03-DEC-81       7000

        20

 

SQL> select * from emp

  2  as of timestamp (systimestamp - interval '19' minute)

  3  where empno=7902;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

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

    DEPTNO

----------

      7902 FORD       ANALYST         7566 03-DEC-81       6000

        20

 

SQL> select * from emp

  2  as of timestamp (systimestamp - interval '20' minute)

  3  where empno=7902;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

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

    DEPTNO

----------

      7902 FORD       ANALYST         7566 03-DEC-81       4000

        20

說明閃回查詢獲取的‘舊’資料，是從資料歸檔中獲取，使用版本查詢應該更直接得到資料的歷史資訊。