1、日志缓冲区记录的是块的更改过程
Undo记录的是块的撤销过程
2、几个概念:
每个事务只能分配一个还原段
一个还原段可以同时服务多个事务处理
一个实例只能有一个Undo表空间
12C在CDB中才能管理UNDO表空间
3、查看Undo相关信息:
i.查看Undo表空间
SQL> showparameter undo_t
NAME TYPE VALUE
----------------------------------------------- ------------------------------
undo_tablespace string UNDOTBS1
查看Undo管理模式
SQL> showparameter undo_m
NAME TYPE VALUE
----------------------------------------------- ------------------------------
undo_management string AUTO
需要设置为自动管理
4、误操作时恢复Undo表空间
i.删除Undo数据文件:
SQL> select namefrom v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/wyzc10g/system01.dbf
/u01/oracle/oradata/wyzc10g/undotbs01.dbf
/u01/oracle/oradata/wyzc10g/sysaux01.dbf
/u01/oracle/oradata/wyzc10g/users01.dbf
/u01/oracle/oradata/wyzc10g/example01.dbf
如上，/u01/oracle/oradata/wyzc10g/undotbs01.dbf即为undo表空间数据文件
SQL> ho rm/u01/oracle/oradata/wyzc10g/undotbs01.dbf
Ii.关闭数据库后重新打开
SQL> shutdown immediate(若关不掉，用abort)
Database closed.
Database dismounted.
ORACLE instance shutdown.
重新启动后报错:
SQL> startup
ORACLE instancestarted.
Total System GlobalArea 524288000 bytes
Fixed Size 2097592 bytes
Variable Size 293604936 bytes
DatabaseBuffers 222298112 bytes
Redo Buffers 6287360 bytes
Database mounted.
ORA-01157: cannotidentify/lock data file 2 - see DBWR trace file
ORA-01110: data file2: '/u01/oracle/oradata/wyzc10g/undotbs01.dbf'
2号文件即为Undo
Iii.修改undo_management参数为手工，并重新启动
SQL> alter systemset undo_management=manual scope=spfile;
System altered.
修改参数之后重启，执行shutdown之后重新startup,依然报错，但是此时可以不用undo
将数据库脱机并删除
SQL> alterdatabase datafile 2 offline drop;
Database altered.
再将数据库状态改为打开
SQL> alterdatabase open;
Database altered.
重新建立新的undo表空间
SQL> droptablespace UNDOTBS1;
Tablespace dropped.
SQL> create undotablespace undotbs1 datafile '/u01/oracle/oradata/wyzc10g/undotbs01.dbf' size10m autoextend on;
Tablespace created.
Iv.将参数改回去，并重新启动数据库以生效
