[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表空间.下个星期看看是否还能能查询.
SCOTT@book> set numw 12
SCOTT@book> select sysdate , current_scn from v$database ;
SYSDATE CURRENT_SCN
------------------- ------------
2017-05-25 08:38:09 13278488313
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
--//时间已经过去将近6天,依旧能查询到修改前星期,当然如果这个表有dml发生,也许就不行.不过这样确实提供一种思路.