oracle undo 表空间

Undo的作用：

1、事务处理回退

2、事务处理恢复

3、读一致性

4、闪回数据

Oracle开始一个事务，当要修改数据时，会先将修改前的数据保存到undo表空间的undo段中。保存这些修改前的数据的原因下面这些场合需要undo数据：1）事务的回滚、2）实例恢复(回滚)、3）一致性读时需要构造CR块；

 

查看使用的undo表空间

SQL> show parameter undo_

 

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO  ---使用自动管理

undo_retention                       integer     900      --保留临时数据的时间为900秒

undo_tablespace                      string      UNDOTBS1   --默认表空间为undotbs1

SQL>

 

 

那么undo表空间 中的段分为3种。

 

第一种为系统段： 由系统表空间产生的镜像数据放在系统段中

第二种为非系统段：由非系统表空间产生的镜像数据

第三种为临时段(DEFERRED)：脱机的表空间都将产生一个临时undo段，这个段就是用来存放脱机之后数据文件所有的变化的，当联机之后会写回对应数据文件中。（这个段是由system表空间生成的）

系统段与非系统段 SQL> select * from v$rollname;          USN NAME ---------- ------------------------------          0 SYSTEM        ---系统段          1 _SYSSMU1$      ---          2 _SYSSMU2$        |          3 _SYSSMU3$        |          4 _SYSSMU4$        |          5 _SYSSMU5$        |   => 这些为非系统段（至少要存在一个）          6 _SYSSMU6$        |          7 _SYSSMU7$        |          8 _SYSSMU8$        |          9 _SYSSMU9$        |         10 _SYSSMU10$     ---   11 rows selected.   SQL>   DEFERRED临时段 （只有表空间脱机才会生成临时段） 没有脱机表空间下查看是否有临时段   SQL> select status,enabled from v$datafile;   STATUS  ENABLED ------- ---------- SYSTEM  READ WRITE ONLINE  READ WRITE ONLINE  READ WRITE ONLINE  READ WRITE   SQL> SQL> select segment_name,segment_type from dba_segments where segment_type like '%DEFE%';   no rows selected   SQL>   脱机实验 SQL> alter tablespace users offline;   Tablespace altered.   SQL> select status,enabled from v$datafile;   STATUS  ENABLED ------- ---------- SYSTEM  READ WRITE ONLINE  READ WRITE ONLINE  READ WRITE OFFLINE DISABLED   SQL> select segment_name,segment_type from dba_segments where segment_type like '%DEFE%';   SEGMENT_NAME                    SEGMENT_TYPE -------------------------------------------------------------------------------- 1.60529                            DEFERRED ROLLBACK     SQL>  将表空间联机查看（online之后自动消失） SQL> alter tablespace users online;   Tablespace altered.   SQL> select segment_name,segment_type from dba_segments where segment_type like '%DEFE%';   no rows selected   SQL>

Undo表空间的建立、删除、修改

 

创建语句 SQL> create undo tablespace undo1 datafile '+data/fengzi/datafile/undo1.dbf' size 100m autoextend on;   删除undo表空间 SQL> show parameter undo_t    NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace                      string      UNDOTBS1   （因为正在使用，所以删不掉） SQL>   SQL> alter system set undo_tablespace=undo1; SQL>drop tablespace undotbs1；（有时候是删不掉的，需要重启删除）   对于修改undo表空间，只需要创建一个新的undo表空间修改参数替换就可以。   SQL> create undo tablespace undotbs1  datafile '+data/fengzi/datafile/undotbs1.dbf' size 100m autoextend on; SQL> alter system set undo_tablespace=undotbs1     重命名当前默认的undo表空间 SQL> show parameter undo_t   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace                      string      UNDOTBS1 SQL> alter tablespace undotbs1 to undo1; 查看OS 参数文件 [oracle@dongyang ~]$ cd $ORACLE_HOME [oracle@dongyang dbs]$ strings spfilefengzi.ora  |grep undo_t *.undo_tablespace='UNDO1' [oracle@dongyang dbs]$ 当重命名默认的undo表空间名时，同时也修改了参数文件中的默认undo表空间名

估算undo表空间大小

计算公式 undo space=(undo_retention*(undo blocks per second*db blocksize))+db_ block_size   相关参数值 SQL> show parameter undo_retention   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ undo_retention                       integer     900 SQL> SQL> desc v$undostat Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  BEGIN_TIME                                         DATE  END_TIME                                           DATE  UNDOTSN                                            NUMBER  UNDOBLKS                                           NUMBER  TXNCOUNT                                           NUMBER  MAXQUERYLEN                                        NUMBER  MAXQUERYID                                         VARCHAR2(13)  MAXCONCURRENCY                                     NUMBER  UNXPSTEALCNT                                       NUMBER  UNXPBLKRELCNT                                      NUMBER  UNXPBLKREUCNT                                      NUMBER  EXPSTEALCNT                                        NUMBER  EXPBLKRELCNT                                       NUMBER  EXPBLKREUCNT                                       NUMBER  SSOLDERRCNT                                        NUMBER  NOSPACEERRCNT                                      NUMBER  ACTIVEBLKS                                         NUMBER  UNEXPIREDBLKS                                      NUMBER  EXPIREDBLKS                                        NUMBER  TUNED_UNDORETENTION                                NUMBER   SQL>   SQL> select sum(undoblks)/sum((end_time-begin_time)*10800) from v$undostat;   SUM(UNDOBLKS)/SUM((END_TIME-BEGIN_TIME)*10800) ----------------------------------------------                                     .340940335   SQL> show parameter block_size   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_block_size                        integer     8192 SQL>   根据公式算出大小   SQL> select 900*0.340940335*8192 from dual;   900*0.340940335*8192 --------------------            2513684.9 或者   SQL>select (rt*(ups*bl))/1024/1024/1024 GB from (select value rt from v$parameter where name='undo_retention'),(select (sum(undoblks)/sum((end_time-begin_time)*10800)) ups from v$undostat),(select value bl from  v$parameter where name='db_block_size');         GB ---------- .002020201

 

 

事务的回退

在开始一个事务之后，执行一些DML语句没有手动或者隐式提交，那么可以使用rollback进行回退，把镜像的数据更新到原块中

 

SQL> select * from shangdong;           ID ----------        100        200        300        400   SQL> 开始一个事务，对数据修改以及创建回退保存点 SQL> update shangdong set id=id+10;   4 rows updated.   SQL> select * from shangdong;           ID ----------        110        210        310        410   SQL> savepoint q1;   Savepoint created. SQL> SQL> delete shangdong where id=110;   1 row deleted.   SQL> savepoint q2;   Savepoint created.   SQL> delete shangdong where id=210;   1 row deleted.   SQL> savepoint q3;   Savepoint created.   SQL> delete shangdong where id=310;   1 row deleted.   SQL> savepoint q4;   Savepoint created.   SQL> 回退事务 SQL> rollback to q3;   Rollback complete.   SQL> SQL> rollback to q4; rollback to q4; * ERROR at line 1: ORA-01086: savepoint 'Q4' never established   SQL> rollback to q1;   Rollback complete.                                          （可以看到，回退到前一时间点是可行的，但是回退到未知时间点是不行的。Q4保存点就是Q3的未知 时间点）               SQL> rollback to q2; ERROR at line 1: ORA-01086: savepoint 'Q2' never established   SQL> rollback;  回退到事务开始前   Rollback complete.   SQL> SQL> select * from shangdong;           ID ----------        100        200        300        400   SQL>

 

已提交的事务回退

SQL> delete shangdong;   4 rows deleted.   SQL> commit;   Commit complete.   SQL> select * from shangdong as of timestamp sysdate-2/1440;  （2分钟前的数据）           ID ----------        100        200        300        400 如果找到想要的数据，直接插入原表就好 SQL> SQL> insert into shangdong select * from shangdong as of timestamp sysdate-2/1440;   4 rows created.   SQL> select * from shangdong;           ID ----------        100        200        300        400   SQL>

Undo表空间的的备份与恢复

在归档模式下是用RMAN 进行全备backup database比较直接

 

[oracle@dongyang ~]$ rman target / RMAN> backup database;   对于undo丢失或损坏恢复 SQL> show parameter undo_t   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace                      string      UNDOTBS1 SQL>     RMAN> restore tablespace undotbs1; RMAN>recover tablespace undotbs1; SQL> alter database datafile 2 online; SQL> alter database open;

 

对于undo表空间没有进行备份的恢复

查看系统中是否存在其他的undo，如果是则修改为其他的undo，把损坏的脱机，然后在启动数据库到open，删除损坏的表空间并建立新的

 

系统中没有其他的undo表空间，使用隐藏参数，让我们的undo脱机，就可以打开数据库然后删除undo表空间并建立undo表空间。

SQL> create undo tablespace undotbs datafile '+data/fengzi/datafile/undotbs.dbf'  size 100m autoextend on;   Tablespace created.   SQL> 关闭数据库删除原undo表空间数据文件启动数据库 SQL> startup ORACLE instance started.   Total System Global Area 1124073472 bytes Fixed Size                  2020224 bytes Variable Size             704646272 bytes Database Buffers          402653184 bytes Redo Buffers               14753792 bytes Database mounted. ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: '+DATA/fengzi/datafile/undotbs1.278.842187181' 修改参数文件重启数据库 SQL> alter system set undo_tablespace=undotbs scope=spfile;   System altered.   SQL> shutdown immediate; ORA-01109: database not open     Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.   Total System Global Area 1124073472 bytes Fixed Size                  2020224 bytes Variable Size             704646272 bytes Database Buffers          402653184 bytes Redo Buffers               14753792 bytes Database mounted. ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: '+DATA/fengzi/datafile/undotbs1.278.842187181'     SQL> 将数据文件脱机启动数据库  SQL> alter database datafile 2 offline;   Database altered.   SQL> alter database open; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced     SQL> 查看报警日志 [oracle@dongyang ~]$ vi /u01/app/oracle/admin/fengzi/bdump/alert_fengzi.log GG 进入最后一行 Successful open of redo thread 1 Mon Apr 14 11:17:35 2014 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Mon Apr 14 11:17:35 2014 SMON: enabling cache recovery Mon Apr 14 11:17:35 2014 ARC0: STARTING ARCH PROCESSES Mon Apr 14 11:17:35 2014 ARC1: Becoming the 'no FAL' ARCH ARC1: Becoming the 'no SRL' ARCH ARC2 started with pid=22, OS id=7651 Mon Apr 14 11:17:36 2014 ARC2: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE ARC0: Becoming the heartbeat ARCH Mon Apr 14 11:17:38 2014 Errors in file /u01/app/oracle/admin/fengzi/udump/fengzi_ora_7641.trc: ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '+DATA/fengzi/datafile/undotbs1.278.842187181' Mon Apr 14 11:17:38 2014 Error 376 happened during db open, shutting down database USER: terminating instance due to error 376 Instance terminated by USER, pid = 7641 ORA-1092 signalled during: alter database open...   [oracle@dongyang ~]$ vi /u01/app/oracle/admin/fengzi/udump/fengzi_ora_7641.trc   /u01/app/oracle/admin/fengzi/udump/fengzi_ora_7641.trc Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 System name:    Linux Node name:      dongyang Release:        2.6.32-200.13.1.el5uek Version:        #1 SMP Wed Jul 27 21:02:33 EDT 2011 Machine:        x86_64 Instance name: fengzi Redo thread mounted by this instance: 1 Oracle process number: 15 Unix process pid: 7641, image: oracle@dongyang (TNS V1-V3)   *** 2014-04-14 11:17:35.552 *** SERVICE NAME:(SYS$USERS) 2014-04-14 11:17:35.552 *** SESSION ID:(324.3) 2014-04-14 11:17:35.552 tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)        （表示是有事务的，这种事务决定了不能根据当前日志文件处理 tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)     需要用到datafile 2 的所以脱机也是不行的） ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '+DATA/fengzi/datafile/undotbs1.278.842187181'   这时候就需要使用到隐藏参数了 SQL> startup nomount; ORACLE instance started.   Total System Global Area 1124073472 bytes Fixed Size                  2020224 bytes Variable Size             704646272 bytes Database Buffers          402653184 bytes Redo Buffers               14753792 bytes 查看需要的隐藏参数 SQL> select ksppinm from x$ksppi where ksppinm like '%roll%';   KSPPINM -------------------------------------------------------------------------------- transactions_per_rollback_segment rollback_segments _rollback_segment_initial _rollback_segment_count _offline_rollback_segments  _corrupted_rollback_segments _cleanup_rollback_entries _rollback_stopat fast_start_parallel_rollback _mv_rolling_inv   10 rows selected.   SQL> 使用隐藏参数 SQL> alter system set "_offline_rollback_segments"=true scope=spfile;   System altered.   SQL> SQL> startup force mount ORACLE instance started.   Total System Global Area 1124073472 bytes Fixed Size                  2020224 bytes Variable Size             704646272 bytes Database Buffers          402653184 bytes Redo Buffers               14753792 bytes Database mounted. 确认undo的状态 SQL> show parameter undo_t   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace                      string      UNDOTBS SQL> show parameter undo_ma   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ undo_management                      string      AUTO 修改undo管理为手动 SQL> alter system set undo_management=manual scope=spfile;   System altered.   SQL> SQL> startup force mount ORACLE instance started.   Total System Global Area 1124073472 bytes Fixed Size                  2020224 bytes Variable Size             704646272 bytes Database Buffers          402653184 bytes Redo Buffers               14753792 bytes Database mounted. SQL> 查看数据文件的状态 SQL> select name,status,enabled from v$datafile;   NAME -------------------------------------------------------------------------------- STATUS  ENABLED ------- ---------- +DATA/fengzi/datafile/system.277.842187103 SYSTEM  READ WRITE   +DATA/fengzi/datafile/undotbs1.278.842187181 OFFLINE READ WRITE   +DATA/fengzi/datafile/sysaux.279.842187235 ONLINE  READ WRITE     NAME -------------------------------------------------------------------------------- STATUS  ENABLED ------- ---------- +DATA/fengzi/datafile/users2.dbf ONLINE  READ WRITE   +DATA/fengzi/datafile/undotbs.dbf ONLINE  READ WRITE     SQL> 开启数据库 SQL> alter database open;   Database altered.   SQL>   SQL> select segment_name,status from dba_rollback_segs;     SEGMENT_NAME                   STATUS ------------------------------ ---------------- SYSTEM                         ONLINE _SYSSMU10$                     OFFLINE _SYSSMU9$                      OFFLINE _SYSSMU8$                      OFFLINE _SYSSMU7$                      OFFLINE _SYSSMU6$                      OFFLINE _SYSSMU5$                      OFFLINE _SYSSMU4$                      OFFLINE _SYSSMU3$                      OFFLINE _SYSSMU2$                      OFFLINE _SYSSMU1$                      OFFLINE   SEGMENT_NAME                   STATUS ------------------------------ ---------------- _SYSSMU20$                     OFFLINE _SYSSMU19$                     OFFLINE _SYSSMU18$                     OFFLINE _SYSSMU17$                     OFFLINE _SYSSMU16$                     OFFLINE _SYSSMU15$                     OFFLINE _SYSSMU14$                     OFFLINE _SYSSMU13$                     OFFLINE _SYSSMU12$                     OFFLINE _SYSSMU11$                     OFFLINE 21 rows selected. SQL>       （如果有recover状态的回滚段，需要使用_corrupted_rollback_segments隐藏参数标记否则删除不了 如：alter system set  “_corrupted_rollback_segments”=_SYSSMU10$  scope=spfile; ）     重建undo表空间 SQL> drop tablespace undotbs1;   Tablespace dropped.   SQL> create undo tablespace undotbs1 datafile '+data/fengzi/datafile/undotbs1.dbf' size 250m autoextend on;   Tablespace created.   SQL>  修改undo表空间默认表空间以及管理方式 SQL> alter system set undo_tablespace=undotbs1 scope=spfile;   System altered.   SQL> alter system set undo_management=auto scope=spfile;   System altered.   SQL> 取消隐藏参数 SQL> alter system reset "_offline_rollback_segments" scope=spfile sid='*';   System altered.   SQL> 重启数据库 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.   Total System Global Area 1124073472 bytes Fixed Size                  2020224 bytes Variable Size             704646272 bytes Database Buffers          402653184 bytes Redo Buffers               14753792 bytes Database mounted. Database opened. SQL> 总结：对于undo表空间最好要存在于2个undo表空间，出现状态也好进行切换处理。

 

 

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

转载于:http://blog.itpub.net/29532781/viewspace-1174667/