学习笔记十（managing undo data）

经过了疯狂的加班后，长长的休息，我想应该可以每天继续下听下去了managing undo data听完了，的确不错。

[@more@]

--10 managing undo data
9i版本用undo 代替了roll back，
sql>show parameter undo;
可见undo 管理的形式
---undo segment
如果我们往table1里面update一笔资料，则被改变的新值放在该table里面而update 前的旧值放在undo segment,也就是undo segment 里面放的是被改变的这笔数据的旧值得镜像
/
---undo segments：purpose
1.transaction rollback 对数据delete 只要还没有commit这个时候被改变的这个旧值放在undo segment里面，此时如果对delete动作想恢复过来就用到undo segment里面得值了
ex
sql>delete scott.emp
sql>select * from scott.emp
没有资料select 到
要把被改变的旧值找回来则下面的指令
sql>rollback;
然后sql>select * from scott.emp;有资料了
2.read consistency 在任何database 里面都不允许大家脏读，也就是人家改变了的数据只要他还没有确认那另外的session 在读这个数据的时候应该读不到这个脏数据，也就是你对database做的修改没有commit的时候就不应该出现在其他session 的结果集里面
ex
sql>delete scott.emp;
然后开启另一个sql plus connect scott/tiger
然后sql>select * from scott.emp;
可以看见有资料的也就是验证了上面所说的只要你没有确认你所作的动作就不能出现在其他用户的结果集里面
3.transaction recovery 例如在insert 资料突然掉电了，这些资料没有commit当下次重新启动的时候就要进行recovery 动作，把没有commit的数据改变取消掉
ex
sql>delete scott.emp
sql>shutdown abort
sql>startup pfile=d:oracleinityh.ora(重新启动的时候如果有问题可以多试几次)
sql>select * from scott.emp
/
---types of undo segment
1.system tablespace
- SYSTEM Undo Segment
      The SYSTEM undo segment is created in the SYSTEM tablespace when a database is created. This undo segment can be used only for changes made to objects in the SYSTEM tablespace. The SYSTEM undo segment exists and works the same in both manual and auto mode.
- Non-SYSTEM Undo Segments
      used for objects in other tablespaces
sql>alter system set undo_managment=manu
Auto mode: Requires an UNDO tablespace
Manual mode:
 Private: Acquired by a single instance
 Public: Acquired by any instance
Deferred Undo Segment: Used when tablespaces are taken offline immediate, temporary, or for recovery(当我们把表空间离线的时候有四种形式normal ,immediate, temporary ,or recovery,其中offline normal是对表空间下的所有datafile都作了checkpoint,也就是这些数据文件在离线是时都是一致的状态所以不存在要被回滚得信息，offline immediate ,temporary ,or recovery则存在被离线的tablespace 下的datafile可能不同步，所以在online的时候需要recovery而online 的时候所需要recovery的信息则存在于deferred tablespace里面，这类回滚段不需要我们人为管理，而是由oracle自动配生出来的用完了会自动消失)
sql>alter tablespace users offline normal;
sql>alter tablespace users online ;
看见不需要任何恢复
sql>alter tablespace users offline immediate（offline immediate会checkpoint表空间里在线的数据文件但是它不会）
sql>alter tablespace users online;
则出现
error ora-01113 需要介质恢复 ora-01110 数据文件 'd:oracleusers02.dbf'
sql>recovery datafile 'd:oracleusers02.dbf';
完成介质恢复
sql>alter tablespace users online;
表空间online 完成
sql>select * from dba_rollback_segs;
可见回滚段的位置等信息
/
---automatic undo management :concepts
1.Undo data is managed using an UNDO tablespace.
2.You allocate one UNDO tablespace per instance with enough space for the    workload of the instance.
3.The Oracle server automatically maintains undo data within the UNDO    tablespace.
/
---automatic undo management configuration
1.configure two parameters in the initialization file;
A.UNDO_MANAGEMENT
B.UNDO_TABLESPACE
2.create at least one undo tablespace(如果要自动管理的话必须最少有一个undo_tablespace,需要知道在一个系统里面任何一个时候你可以在系统里面建立多个undo_tablespace,但是在任何时候只可能有一个undo_tablespace被激活，也就是说在任何时候undo段的空间来源他只可能来自于一个undo_tablespace,不可能undo 段1的一个空间来自undo_tablespace1 另一个undo段2的空间来自undo_tablespace2)
sql>show parameter undo可见相关信息。
修改管理模式
1。修改参数文件里的undo_management=manual
                     
在一个系统里面任何一个时候你可以在系统里面建立多个undo_tablespace,但是在任何时候只可能有一个undo_tablespace被激活，也就是说在任何时候undo段的空间来源他只可能来自于一个undo_tablespace,
sql>create undo tablespace undotbs1 datafile 'd:oracleundotbs1.dbf' size 10m;
sql>alter system set undo_tablespace=undotbs1;
相当于原来激活的是undotbs 现在激活的是undotbs1
sql>select * from dba_rollback_sets;
可见tablespace 改变了
/
---automatic undo management :undo tablespace
略
如果是当前活动的undo_tablespace 则普通tablespace可以完成的动undo_tablespace 不可以完成（A.不能offline,B.不能设定为read only模式 ）但是datafile增加，存储参数设定等可以改变
ex
sql>alter tablespace undotbsq
   2 offline;
ora-00959 表空间undotbsq不存在
sql>l1
sql>c /q/1/
sql>run;
error ora-30040 撤销表空间处于脱机状态
sql>del 2
sql>input read only
sql>run
error ora-30021不允许对撤销表空间执行操作
/
---automatic undo management:switching undo tablespaces

1.You can switch from using one UNDO tablespace to another.
2.Only one UNDO tablespace can be in assigned to a database at a time.(任何  时候一个instance只能指定一个undo tablespace)
3.More than one UNDO tablespace may exist within an instance, but only one     can be active.(z在任何时候只有一个undo tablespace 可以被激活)
4.Use the ALTER SYSTEM command for dynamic switching between UNDO     tablespaces.
sql>alter system set undo_tablespace=undotbs;
sql>select * from dba_rollback_segs;
/
---automatic undo management:dropping an undo tablespace
1.The DROP TABLESPACE command drops an UNDO tablespace.
  ：DROP TABLESPACE UNDOTBS2;

2.An UNDO tablespace can only be dropped if it is currently not in use by  any instance.
EX
sql>drop tablespace undotbs1;
完成
sql>drop tablespace undotbs;
error ora-30013 撤销表空间undotbs当前正在使用中

3.To drop an active UNDO tablespace:
A.Switch to a new UNDO tablespace.
B.Drop the tablespace after all current transactions are complete.
ex
sql>create undo tablespace undotbs1 datafile 'd:oracleundotbs1.dbf' reuse;
sql>delete socott.emp;
重新起一个session
sql>alter system set undo_tablespace=undotbs1;
sql>drop tablespace undotbs;
error ora-30013 撤销表空间undotbs当前正在使用中
登到delete session里面
sql>rollback后再
sql>drop tablespace undotbs;
则OK了，这样会发现Drop the tablespace after all current transactions are complete.
/
---Automatic undo management :other parameters
1.UNDO_SUPPRESS_ERRORS parameter:
Set to TRUE, this parameter suppresses errors while attempting to execute manual operations in AUTO mode.(要忽猎手工管理时报的错，如果false则不会报错)
ex
sql>show parameter undo
可见undo_suppress_errors =false
然后
sql>create rollback segment rbs1 tablespace undotbs;
error ora-30019自动撤销模式中的回滚段操作非法
sql>alter system set undo_suppress_errors=true;
sql>create rollback segment rbs1 tablespace undotbs;
则出现:回滚段建立
sql>select * from dba_rollback_segs;
可见刚刚建的rbs1并没有在结果中
可见忽猎了错误提示
2. UNDO_RETENTION parameter:
This parameter controls the amount of undo data to retain for consistent read（为了提供数据读一致性在系统里面undo 里面数据保存的时间）
sql>show parameter undo
可见UNDO_RETENTION 值
oracle在9i里面引入了回闪的功能，oracle以前的版本如果一个transaction对一笔资料作了修改如果commit了后他所作的修改结果就反映在database里面了其旧值就没法找到了9i引入了undo_retention参数就是把旧数据保留在回滚段里面，即使提交了在database 里面也能看见
ex
sql>delete scott.emp;
sql>commit;
sql>select * from scott.emp;
没有值选中
sql>rollback;
回退完成
sql>select * from scott.emp;
仍然没有值
此时如果还想用到scott.emp里面的值，则利用回闪来实现:
connect scott/tiger
sql>select * from emp;
用dbms flash back
sql>desc dbms_flashback;
sql>execute dbms_flashback.enable_at_time('2005-05-13 23:30')(我把数据回闪到了11:30分，那我在11：32所作的动作的值则可找回来了)
sql>select * from emp;
但是需要注意的时这个数据没有恢复到表里面去，只是从回滚段的镜像里面读了个旧的copy出来，而且这些数据是只读的。
sql>execute dbms_flashback.disable;
sql>select * from emp;
可见没有值产生，因此回闪只是从undo 段里面读了个旧的copy出来，只是帮助我们验证结果，要知道oracle对commit了的数据是没法找回来的，除非recovery
可以读到多长时间以前的数据呢?
就取决于UNDO_RETENTION parameter(default 为900秒)

UNDO_RETENTION parameter：
Determines how long to retain undo data to provide for consistent reads. Retaining undo data allows for longer queries and also requires larger data files for the UNDO tablespace. The UNDO_RETENTION parameter, defined in seconds, can be set in the initialization file or modified dynamically with an ALTER SYSTEM command.
SQL> ALTER SYSTEM SET UNDO_RETENTION=900;
A value of 900 retains undo data for 15 minutes.
/
---undo data statistics
select end_time,begin_time,undoblks from v$undostat;
/
---Automatic Undo Management:Sizing an UNDO Tablespace

Determining a size for the UNDO tablespace requires three pieces of information:
(UR) UNDO_RETENTION in seconds(undo 保存的时间)
(UPS) Number of undo data blocks generated per second(每一秒所需要的回滚数据块觉定)
(DBS) Overhead varies based on extent and file size (db_block_size)

Automatic Undo Management: Sizing an UNDO Tablespace

Sizing an UNDO tablespace requires three pieces of data. Two can be obtained from the initialization file: UNDO_RETENTION and DB_BLOCK_SIZE. The third piece of the formula requires a query against the database. The number of undo blocks generated per second can be acquired from V$UNDOSTAT. The following formula calculates the total number of blocks generated and divides it by the amount of time monitored, in seconds:
 SQL> SELECT (SUM(undoblks) / SUM)
  2  ((end_time - begin_time) * 86400)
  3  FROM v$undostat;
Column END_TIME and BEGIN_TIME are DATE data types. When DATE data types are subtracted, the result is in days. To convert days to seconds, you multiply by 86400, the number of seconds in a day.
The result of the query returns the number of undo blocks per second. This value must be multiplied by the size of an undo block, which is the same size as the database block defined in DB_BLOCK_SIZE. The following query calculates the number of bytes needed:
SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
  2  FROM (SELECT value AS UR
  3  FROM v$parameter
  4  WHERE name = 'undo_retention'),
  5  (SELECT (SUM(undoblks)/SUM
  6   (((end_time-begin_time)*86400))) AS UPS
  7  FROM v$undostat),
  8  (SELECT value AS DBS
  9  FROM v$parameter
  10 WHERE name = 'db_block_size');
 Bytes
 ----------
 19106213

To convert bytes to megabytes, divide by 1,048,576 bytes. The result for this database is 18.22 MB.

For best results, the calculation should be made during the time of day when the database has its heaviest workload
(觉得以上部分说明很明白，而且很有用)
/
---automatic undo management undo quota
在undo 资源有限的情况下如果一个or 某些user启用了大的transactioin 这样就消耗了整个undo segment 其他用户就没有undo segment给他们使用 而出现等待现象，所以用undo quota 限定user最多可以使用到多少undo segment 资源，用undo_pool这个参数来解决这个限定
Automatic Undo Management: Undo Quota
Using resource plans, users can be grouped and limits placed on the amount of resources that can be used by the group. The amount of undo data generated by a group can be limited by setting a value for UNDO_POOL: the default value is unlimited. When a group exceeds its limit an error is received and no new transactions can be performed for the group until current transactions complete or abort.
ORA-30027: "Undo quota violation - failed to get %s (bytes)"
Cause: The amount of undo assigned to the consumer group of this session has been exceeded.
Action: Ask DBA to increase undo quota, or wait until other transactions commit before proceeding.
Note: Resource Management is discussed further in the “Managing Password Security and Resources” lesson.
/
---obtaining undo segment information
sql>select * from dba_rollback_segs;
sql>select * from v$rollname;
sql>select * from v$rollstat;
v$undostat;
v$session;
v$transaction;

待续

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

转载于:http://blog.itpub.net/202861/viewspace-798161/