Oracle 12c Undo数据管理分析

1、Oracle 12c Undo数据管理分析重现ORA-01555 录1234Undo 概述ORA-01555ORA-3003612c Undo 管理Undo 概述1Undo “old” datain undo tablespaceUPDATEDML operationsData in buffer cacheRedo log bufferRedo log filesNew change details in redo log filesUndo segmentRedo与UndoUndoRedoRecord ofHow to undo a changeHow to reproduce a cha

2、ngeUsed forRollback, read consistency, flashbackRolling forward database changesStored inUndo segmentsRedo log filesUndo 管理方式与新特性Oracle 9iOracle 10gOracle 12cR1Oracle 12cR2Before Oracle 9iManaging Rollback Segments 手工管理回滚段Autotune Undo Retention Guarantee Undo RetentionTemporary UndoCDB使 Shared Undo

3、 ModeCDB 持Local Undo ModeAutomatic Undo Management（AUM） Flashback QueryAutomatic Undo Management（AUM）Undo 数据应A record of the action of a transactionCaptured for every transaction that changes dataRetained at least until the transaction is endedUsed to support:Rollback operationsRead-consistent queri

4、esOracle Flashback Query, Oracle Flashback Transaction, and Oracle Flashback Table Recovery from failed transactionsRead-consistent queries 读致性查询Session 29:30 update 更新 UPDATE empSET salary= 20000 WHERE empno=7788;commit;commit complete.Session 39:40 查 询 SELECT salary FROM empWHERE empno=7788;结果：200

5、00Session 19:00 开始查询 SELECT salary FROM empWHERE empno=7788;10:00返回结果 100?00CategoryDescriptionActiveUncommitted undo informationSupports an active transaction and is never overwrittenUnexpiredCommitted undo informationRequired to meet the undo retention intervalExpiredExpired undo informationOverwr

6、itten when space is required for an active transactionUndo 状态Automatic Undo Management（AUM）：Undo 状态activeunexpiredexpireduncommmitted持 rollback隔离脏读committed未超出retention保留期持 致性读、闪回查询undo_management=auto undo_retention=900 undo_tablespace=undotbs1SYSpdb1 update test1 set name=OCM where id=2;1 row upda

7、ted.SYSpdb1 select * from test1;ID NAME- -OracleOCMStep 2.Session 2SYSpdb1 select * from test1;ID NAME- -OracleDBASYSpdb1 select * from test1;ID NAME- -OracleDBAStep 3.Session 3Undo 应 实 验 ： Long-running queries & Flasback querySYSpdb1 select current_scn from v$database;CURRENT_SCN-1814514SYSpdb1 sel

8、ect * from test1;ID NAME- -OracleDBASYSpdb1 variable rfc refcursor;SYSpdb1 execute open :rfc for select * from test1;PL/SQL procedure successfully completedStep 1.Session 1SYSpdb1 commit;Commit complete.SYSpdb1 select * from test1;ID NAME- -OracleOCMStep 4.Session 2SYSpdb1 select * from test1;ID NAM

9、E- -OracleOCMStep 5.Session 3Undo 应 实验：致性读，闪回查询ID NAME- -OracleDBASYSpdb1 select * from test1;ID NAME- -OracleOCMSYSpdb1 select * from test1 as of scn 1814514;ID NAME- -OracleDBAStep 6.Session 1SYSpdb1 print :rfcORA-015552Snapshot Too Old实验二：ORA-1555实验准备: fixed size Undo TablespaceSYScdb1 create und

10、o tablespace smallundodatafile /u01/app/oracle/oradata/cdb1/smallundo01.dbf size 2m autoextenSYScdb1 alter system set undo_tablespace=SMALLUNDO;SYSpdb1 beginfor i in 1.20000 loopupdate test1 set name=Oracle;commit;end loop;end;7 /PL/SQL procedure successfully completed.SYSpdb1 select * from test1;ID

11、 NAME- -OracleOracleSYSpdb1 select * from test1;ID NAME- -OracleOracleStep 3.Session 3实验二：ORA-01555snapshot too oldSYSpdb1 select current_scn from v$database;CURRENT_SCN- 1817879SYSpdb1 select * from test1;ID NAME- -OracleOCMSYSpdb1 variable rfc refcursor;SYSpdb1 execute open :rfc for select * from

12、test1;PL/SQL procedure successfully completedStep 1.Session 1Step 2.Session 2实验二：ORA-01555snapshot too oldStep 4.Session 1ORA-01555 snapshot too oldfixed size Undo TablespaceAutomatic Undo RetentionORA-01555activeunexpireduncommmitted持 rollback隔离脏读committed未超出retention保留期持 致性读、闪回查询undo_management=au

13、to undo_retention=900 undo_tablespace=undotbs1autoextend off noguaranteeORA-01555 快照太旧tuned undo retention 200sfixed size Undo Tablespaceundo_retention参数指定了提交后的undo数据（unexpired）保留多少秒。当前环境中设置了 undo_retention=900，默认undo表空间未设置guarantee。使固定大小的undo表空间时，undo_retention值被忽略，数据库根据undo表空间大小和系统 负载情况动态调整优化reten

14、tion值。v$undostat视图可以查看动态调整后的retention值。v$undostat视 图每10分钟成记录，当前时间对应的tuned undo retention值会随事务繁忙程度变化而更新， 事务越繁忙，优化后的retention值越小。3ORA-30036Out Of SpaceGuaranteeing Undo Retention（10g）SYSpdb1 beginfor i in 1.20000 loopupdate test1 set name=12C OCM;commit;end loop;end; 7 / begin*ERROR at line 1:ORA-3003

15、6: unable to extend segment by 8 in undo tablespace SMALLUNDOORA-06512: at line 3SYSpdb1 select * from test1; ID NAME- - 1 12C OCM2 12C OCMStep 2.Session 2实验三：ORA-30036 out of spaceSYSpdb1 select current_scn from v$database;CURRENT_SCN-1857058SYSpdb1 select * from test1;ID NAME- -OracleOracleSYSpdb1

16、 variable rfc refcursor;SYSpdb1 execute open :rfc for select * from test1;PL/SQL procedure successfully completedStep 1.Session 1实验三：ORA-30036 out of spaceSYSpdb1 print :rfcID NAME- -OracleOracleSYSpdb1 select * from test1 as of scn 1857058;ID NAME- -OracleOracleSYSpdb1 select * from test1;ID NAME-

17、- 1 12C OCM2 12C OCMStep 3.Session 1实验三：Tunned_undoretention : 900实验三：Automatic Undo ManagementORA-30036activeunexpiredtunedundo retention 900suncommmitted持 rollback隔离脏读committed未超出retention保留期持 致性读、闪回查询undo_management=auto undo_retention=900 undo_tablespace=undotbs1autoextend off guaranteeORA-30036

18、out of space实验四：取消undo表空间 Guarantee设置实验四：插入百万数据，模拟大表delete操作SYSpdb1 create table test2 as select * from dba_objects; SYSpdb1 insert into test2 select * from test2; SYSpdb1 insert into test2 select * from test2; SYSpdb1 insert into test2 select * from test2; SYSpdb1 insert into test2 select * from te

19、st2; SYSpdb1 commit;Commit complete.实验四：fixed size Undo Tablespace当前已经取消undo表空间guarantee属性，但由于使了固定大小的undo表 空间，大事务操作也可能会因undo空间不足而报错ORA-30036。同时由于事务越繁忙，优化后的undo保留期值越小，仍然有可能报ORA-01555错 误。实验四：Automatic Undo RetentionORA-30036activeunexpiredtunedundo retention 52suncommmitted持 rollback隔离脏读committed未超出re

20、tention保留期持 致性读、闪回查询undo_management=auto undo_retention=900 undo_tablespace=undotbs1autoextend off noguaranteeORA-30036out of space实验五： Undo Tablsepaceautoextend onSYSpdb1 beginfor i in 1.20000 loopupdate test1 set name=Oracle DBA;commit;end loop;end;7 /PL/SQL procedure successfully completed.SYSpdb

21、1 select * from test1;ID NAME- -Oracle DBAOracle DBAStep 2.Session 2实验五：Undo Tablespaceautoextend onSYSpdb1 select current_scn from v$database;CURRENT_SCN-1858725SYSpdb1 select * from test1;ID NAME- - 1 12C OCM2 12C OCMSYSpdb1 variable rfc refcursor;SYSpdb1 execute open :rfc for select * from test1;

22、PL/SQL procedure successfully completedStep 1.Session 1实验五：Undo Tablespaceautoextend onSYSpdb1 print :rfcID NAME- - 1 12C OCM2 12C OCMSYSpdb1 select * from test1 as of scn 1858725;ID NAME- - 1 12C OCM2 12C OCMSYSpdb1 select * from test1;ID NAME- -Oracle DBAOracle DBAStep 3.Session 1undo表空间数据件设置为 aut

23、oextend on时，数据库以当前设 置的undo_retention参数值作为下限 值，根据maxquerylen动态调整优化retention值,优先满足Long-runningqueries的致性读。当undo空间不足时，数据库会扩 展undo表空间对应数据件。Undo Tablespace autoexntend onAutomatic Undo Retentionautoextend onactiveunexpiredexpireduncommmitted持 rollback隔离脏读committed未超出retention保留期持 致性读、闪回查询undo_management=auto undo_retention=900 undo_tablespace=undotbs1autoextend on noguaranteetuned undo retentionmaxquerylenUndo Retention 自动优化412c 新特性New FeaturesUndo “ol