undo--事务回退 事务提交
提交事务--DML--DDL
事务结束
1、人为commit DML
2、DDL
insert update delete执行后未提交但之后执行了DDL DCL语句后之前的语句也会提交。
eg:
update emp set sal=sal+1;
@/u01/oracle/udoinfo.sql
create tablet (id number)
@/u01/oracle/udoinfo.sql
闪回数据
select * from emp as of timestamp sysdate-1/1440;
--一分钟前数据状态
drop table emp;
commit;
insert into emp select * from emp as of timestamp
sysdate-1/1440;
undo归档下备份恢复
rman target /
backup tablespace undotbs;
删除物理文件undotbs01.dbf
restore tablespace undotbs1;
recover tablespace undotbs1;
alter database datafile 2 online;
alter database open;
无备份恢复
1、系统中是否存在其他undo
如果是则修改其他undo,把损坏的脱机后启动数据库,删除损坏的表空降并建立新的undo表空间;
2、系统中无其他表空间,使用隐藏参数undo脱机,打开数据库,删除
create undo tablespace undotbs1 datafile
'/u01/oracle/oradata/undotbs.dbf' size 205M autoextend on;
rm undotbs01.dbf
alter system set undo_tablespace=undotbs1
scope=spfile;--切换其他表空间
shutdown immediate;
startup;
alter database datafile 2 offline;
alter database open;
若依旧打不开查看oracle日志
实在不成开启隐藏参数
startup nomount;
select ksppinm from x$ksppl where ksppinm like'%roll%';
alter system set
"_offline_rollback_segments"=true scope=spfile;--隐藏参数
startup force mount;
show parameter undo_t
show parameter undi_ma
alter system set undo_management=manual
scope=spfile;--手动管理
startup force mount;
select name,status,enabled from
v$datafile;--查看undotbs01是否脱机
alter database datafile 2 offline;--参照上一步
alter database open;
select name,status,enabled from v$datafile;
select segment_name,status from dba_rollback_segs; --all
offline
drop tablespace undotbs;
create undo tablespace undotbs datafile
'/u01/oracle/oradata/undotbs01.dbf' size 205M autoextend on;
alter system set undo_tablespace=undotbs scope=spfile;
alter system set undo_management=auto
shutdown immediate;
startup
alter system reset
"_offline_rollback_segments" scope=spfile sid='*';--隐藏参数
alter system set
"_offline_rollback_segments"=false scope=spfile;
undo没有备份
1、设置参数undo_management=manual;undo_tablespace=undotbs1其他可用的;"_offline_rollback_segments"=true
2、启动mount
3、将损坏的undo表空间脱机
4、打开数据库
5、看dba_rollback_Segs中的status---损坏的表空间的的段是否有recover字样 如果没有
则可以删除损坏的undo,如果有则使用——corrupted_rollback_segments
6、删除损坏的undo,建立删除的undo
7、还原1步中修改的所有参数
8、启动数据库
***要有备用的undo表空间