ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【Flashback】11g的闪回数据归档初探

【Flashback】11g的闪回数据归档初探

原创 Linux操作系统 作者:mikeliuy 时间:2013-07-21 23:30:57 0 删除 编辑

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

说明闪回查询获取的‘旧’数据,是从数据归档中获取,使用版本查询应该更直接得到数据的历史信息。



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

请登录后发表评论 登录
全部评论

注册时间:2012-06-18

  • 博文量
    72
  • 访问量
    381565