[20170520]利用undo表空間保護資料.txt

[20170520]利用undo表空間保護資料.txt

--//undo表空間是用來記錄前映像資訊,也用來保證查詢時一緻性的.上個星期去聽一些課,提到不打開歸檔情況下一些維護技巧,

--//就是建立多個redo日志檔案,用來儲存日志,至少維持3-4天甚至1個星期的日志,這樣可以一定程度減少錯誤以及會查問題.

--//另外提到一種利用undo表空間避免異常操作的恢複方法,就是再建立1個undo表空間,出現異常dml語句時切換使用新的undo表空間.

--//這樣可以非常從容的恢複資訊.當然最好不要再有dml操作這個表.通過例子說明:

1.環境:

SCOTT@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER

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

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE

  '/mnt/ramdisk/book/undotbs02.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

ONLINE

RETENTION NOGUARANTEE

BLOCKSIZE 8K

FLASHBACK ON;

SCOTT@book> show parameter undo

NAME             TYPE    VALUE

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

undo_management  string  AUTO

undo_retention   integer 900

undo_tablespace  string  UNDOTBS1

SCOTT@book> create table tx as select * from dba_objects ;

Table created.

SCOTT@book> set numw 12

SCOTT@book> select sysdate , current_scn from v$database ;

SYSDATE              CURRENT_SCN

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

2017-05-19 18:00:23  13277861094

SCOTT@book> update tx set object_name=lower(object_name);

87006 rows updated.

SCOTT@book> commit ;

Commit complete.

SCOTT@book> alter system set undo_tablespace=UNDOTBS2;

System altered.

SCOTT@book> select * from tx as of scn 13277861094 where rownum=1;

OWNER  OBJECT_NAME          SUBOBJECT_    OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S    NAMESPACE EDITION_NAME

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

SYS    ICOL$                                     20              2 TABLE               2013-08-24 11:37:35 2013-08-24 11:47:37 2013-08-24:11:37:35 VALID   N N N            1

SCOTT@book> update deptx set dname=upper(dname) where rownum=1;

1 row updated.

SCOTT@book> @ &r/xid

XIDUSN_XIDSLOT_XIDSQN

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

20.2.4

C70                                                                        XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC STATUS            USED_UBLK  USED_UREC XID              ADDR             START_DATE

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

ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU20_1294681377$' XID 20 2 4;               20          2          4          7        274          1          4 ACTIVE                    1          2 1400020004000000 00000000818ED500 2017-05-19 18:03:36

ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU20_1294681377$';

ALTER SYSTEM DUMP DATAFILE 7 BLOCK 274;

--//現在新的事務使用新undo表空間.下個星期看看是否還能能查詢.

2017-05-25 08:38:09  13278488313

--//時間已經過去将近6天,依舊能查詢到修改前星期,當然如果這個表有dml發生,也許就不行.不過這樣确實提供一種思路.