oracle undo相关知识整理

个人感觉，若是不谈一些底层上的东西，undo这一块的知识，相对是比较简单的，于是也成了我拿来整理的第一站。


一、oracle undo的相关理论知识


1、undo是什么


联机文档对undo的解释如下：
Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. 
Such information consists of records of the actions of transactions, primarily before they are committed. 
These records are collectively referred to as undo.


简而言之，undo是由一些record组成，这些record记录了数据库中变化数据的原始镜像。
比如我要对数据库中一张表的几条数据做delete操作，这时候oracle会将这几条记录delete前的信息写到undo表空间中，
若是delete操作意外中止或者rollback等，通过undo来恢复这几条数据的原始面貌。



2、undo的作用是什么


a、Roll back transactions when a ROLLBACK statement is issued


b、Recover the database


c、Provide read consistency


d、Analyze data as of an earlier point in time by using Oracle Flashback Query


e、Recover from logical corruptions using Oracle Flashback features




对于a点，就是当用户发出rollback的命令后，对修改的数据进行回滚
对于b点，当数据库意外崩溃等情况下，重新启动的过程中，需要对崩溃前未提交的事务进行回滚操作
对于c点，当一个会话在修改某个数据并且未提交期间，其他会话查询该数据依然是原来的信息（通过查询undo表空间中的数据），以此实现读一致性
对于d点和e点，都是和oracle flashback特性相关，需要用到undo信息，详细信息我将整理oracle flashback中阐述




3、undo相关参数


a、undo_tablespace   ---指定数据库的默认undo表空间
b、undo_retention    ---指定undo 数据的保留时间，超过此时间后即可被覆盖
c、undo_management   ---指定undo的管理方式，auto和manual




关于undo_retention：
对于undo_management是auto的情况，undo_retention的值是oracle尝试去保留undo信息的最小时间单位，oracle会根据系统负载和undo表空间大小情况，在undo_retention值之上保留undo信息。
当undo表空间是固定大小，没有空闲空间，且其他的过期undo都已经被覆盖后，这时候oracle也会去覆盖一些未过期的数据（即这些undo数据还在undo_retention之内）。
当一个长查询所需的undo信息被覆盖掉后，alert日志便会抛出ora-01555的错。




换句话说，当undo表空间足够的情况下，对于一些长查询的存在，会将undo_retention的值扩的相当大。


这里要提及两个个隐藏参数：_undo_autotune 和_highthreshold_undoretention


_undo_autotune默认值是true，当undo_management为auto的时候，会对undo_retention的值进行自动扩展
_highthreshold_undoretention 则是可以限制undo_retention扩展到一个最大值便不可扩展。




4、Retention Guarantee（默认是disable）


当你想让undo_retention的值变得可靠，对于未过期的undo信息不允许oracle进行覆盖，这时候可以考虑使用Retention Guarantee


You enable retention guarantee by specifying the RETENTION GUARANTEE clause for the undo tablespace when you create it with either the CREATE DATABASE or CREATE UNDO TABLESPACE statement. 
Or, you can later specify this clause in an ALTER TABLESPACE statement. 
You disable retention guarantee with the RETENTION NOGUARANTEE clause.


例如：
SQL> alter tablespace undotbs1 retention guarantee;


Tablespace altered.


SQL> alter tablespace undotbs1 retention noguarantee;


Tablespace altered.






5、 关于undo_management的auto和manual
auto：oracle服务器自动管理重做段的创建，分配，调整
manual：手工管理重做段的创建，分配，调整。这时9i之前的唯一方法


因为10g后的undo_management主流都是auto，在此不对manual做过多叙述（其实就是我懒）


oracle通过参数auto_management参数来控制使用哪种方式来管理undo。
如果设为auto，就使用undo表空间，这时候必须要指定一个undo表空间，如果设为manual，oracle启动后使用rollback segment方式存储undo信息。


当实例启动的时候，oracle自动选择第一个有效的表空间或者是rollback segment，如果没有有效的可用的undo表空间或者回滚段，
oracle将使用system rollback segment。




解释下三个名词：系统回滚段，非系统回滚段，延迟回滚段


系统回滚段：创建在system表空间中，主要是用于系统级的事务和分配普通事务于其他回滚段上。当手工创建数据库后需要创建普通回滚段之前必须首先创建系统回滚段。
            系统回滚段主要用于两个方面。一是系统事务，比如针对数据字典的操作的truncate table和drop table。
            如果truncate table or drop table的过程中没有成功，则系统会根据系统回滚段中的数据字典操作信息对该DDL操作进行回退。
            另一方面就是延迟回滚段。延迟回滚段表示的是，当我们使一个表空间offline之后，由于表空间不可用，这个时候若是有事务数据位于该表空间并且执行了回滚命令，
            回滚完成将显示给客户端，对于client看来该事务已经回滚，但是对于数据库来说该回滚并没有真正完成，这个时候数据库将该回滚信息写入系统回滚段（这就是延迟回滚段），
            等表空间重新online的时候，数据库将从系统回滚段中将回滚信息写入表空间


非系统回滚段：数据库拥有多个表空间时需要至少一个非系统的手工模式或者自动模式的重做表空间。






6、管理undo 表空间


a、创建undo 表空间（一种是在手动建库的时候创建，一种是使用create undo tablespace命令）


CREATE DATABASE rbdb1
     CONTROLFILE REUSE
     .
     .
     .
     UNDO TABLESPACE undotbs_01 DATAFILE '/u01/oracle/rbdb1/undo0101.dbf';






create undo tablespace undo_test datafile 'c:\undotest.dbf' size 5m autoextend on maxsize 10m;


你可以创建多个undo 表空间，但同一时间只能有一个是活动的。也就是undo_tablespace指定的undo 表空间






b、更改undo表空间：alter tablespace


Adding a datafile：
增加数据文件：
alter tablespace undo_test add datafile 'c:\undotest1.dbf' size 5m autoextend on maxsize 10m;






Renaming a datafile：
关于重命名一个数据文件，有两种方式：alter database和alter tablespace


alter tablespace只能用于下面情况下的数据文件：不是system表空间，不包含激活的回滚段还有临时段。
但是用alter tablespace 可以在实例open的情况下执行
而alter database 则适应于任何的数据文件，但是数据库要在mount的情况下


<1>首先要将表空间offline，然后在系统层面将数据文件重命名，再在数据库中执行如下语句
alter tablespace undo_test rename datafile 'c:\undotest1.dbf' to 'c:\undotest2.dbf';


<2>首先要关闭数据库，在系统层面重命名数据文件，启动在mount状态，再在库上执行如下语句：
alter database rename file 'c:\undotest1.dbf' to 'c:\undotest2.dbf';                   ------------注意，这个地方是file，不是datafile






Bringing a datafile online or taking it offline：
表空间离线：
alter tablespace undo_test online/offline;


Beginning or ending an open backup on a datafile：
开始/结束备份表空间：


SQL> alter tablespace undo_test begin backup;


表空间已更改。


SQL> alter tablespace undo_test end backup;


表空间已更改。


Enabling and disabling undo retention guarantee：
undo retention保证


alter tablespace undo_test retention guarantee/noguarantee;





7、undo表空间的切换


切换undo表空间多是为了缩小undo表空间


步骤：


a、建立一个新的较小的undo表空间 
b、将UNDO_TABLESPACE参数指向新建立的表空间 
c、删除老的undo表空间。 




注意：


a、如果之前的undo表空间中还存在活动的事务，则之前的表空间一直处于Pending Switch-Out state，同时在alter日志中会有类似"undo Tablespace 1 moved to Pending Switch-Out state信息。
这是删除之前的undo表空间会报ORA-30013 : undo tablespace '%s' is currently in use。必须等所有的事务都提交或者roll back。
可通过下列语句查看事务及连接信息：


SELECT a.usn,
       a.name,
       b.status,
       c.tablespace_name,
       d.addr,
       e.sid,
       e.serial#,
       e.username,
       e.program,
       e.machine,
       e.osuser
  FROM v$rollname        a,
       v$rollstat        b,
       dba_rollback_segs c,
       v$transaction     d,
       v$session         e
 WHERE a.usn = b.usn
   AND a.name = c.segment_name
   AND a.usn = d.xidusn
   AND d.addr = e.taddr
   AND b.status = 'PENDING OFFLINE';


通过下列语句kill掉相应的用户进程，让事务回滚：
ALTER SYSTEM KILL SESSION '<sid#>, <serial#>'; 




b、当有分布式事务时，可能存在不正常的undo信息一直存在，也就是说事务永远结束不了，之前的undo表空间一直无法删除，这时可采用如下步骤解决：
通过如下两条语句查询存在问题的分布式事务：


SELECT KTUXEUSN,
       KTUXESLT,
       KTUXESQN, --这几个字段表示事务ID，其中第一个表示回滚段，如7 5 311977，则事务ID为7.5.311977
       KTUXESTA Status,
       KTUXECFL Flags
  FROM x$ktuxe
 WHERE ktuxesta != 'INACTIVE';




查看结果中存在状态为prepare的记录。
SELECT LOCAL_TRAN_ID,STATE FROM DBA_2PC_PENDING;
查看结果中存在状态为prepare的记录。
存在三种情况：1.事务ID在x$ktuxe中存在，但DBA_2PC_PENDING中没有；
              2.事务ID在x$ktuxe中不存在，但在DBA_2PC_PENDING中存在；
              3.在两者中都存在。对于这三种情况分别采用下面三种方法进行清除：


1)事务ID在x$ktuxe中存在，但DBA_2PC_PENDING中没有:
alter system disable distributed recovery;
insert into pending_trans$
  (LOCAL_TRAN_ID,
   GLOBAL_TRAN_FMT,
   GLOBAL_ORACLE_ID,
   STATE,
   STATUS,
   SESSION_VECTOR,
   RECO_VECTOR,
   TYPE#,
   FAIL_TIME,
   RECO_TIME)
values
  ('7.5.311977', --<== replaced by your transaction ID
   306206,
   'XXXXXXX.12345.1.2.3',
   'prepared',
   'P',
   hextoraw('00000001'),
   hextoraw('00000000'),
   0,
   sysdate,
   sysdate);
insert into pending_sessions$
values
  ('7.5.311977', --<== replaced by your transaction ID */
   1,
   hextoraw('05004F003A1500000104'),
   'C',
   0,
   30258592,
   '',
   146);


commit;
commit force '7.5.311977'; --<== replaced by your transaction ID


alter system enable distributed recovery;
commit;
exec dbms_transaction.purge_lost_db_entry( '7.5.311977' ); --<== replaced by your transaction ID
commit;
以上语句建议采用sqlplus / as sysdba本地进行操作，否则可能出现错误。




2)事务ID在x$ktuxe中不存在，但在DBA_2PC_PENDING中存在:
直接清除掉dba_2pc_pending中记录即可
set transaction use rollback segment SYSTEM;
delete from sys.pending_trans$ where local_tran_id ='7.5.311977';
delete from sys.pending_sessions$ where local_tran_id ='7.5.311977';
delete from sys.pending_sub_sessions$ where local_tran_id ='7.5.311977';
commit;




3)在两者中都存在
采用手工删除掉dba_2pc_pending中记录，然后按照第一种情况处理。
即先执行2)中步骤，然后执行1）中步骤。






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


结束语：
本以为个把小时就可以整理完undo的，但依然花了不少时间，不过也是有不少收获的，一些之前不是很明朗的基础知识得以进一步巩固
当然，受限于当下的心态，对于undo_management为manual的情况，还是不甚清楚，也就一味的糊弄过去了。


在undo切换那个地方看似简单，但到了生产环境，总会出现各种情况，导致undo表空间的切换不甚胜利，文中也借鉴了些他人的解决方法。