在没备份undo的情况下,undo丢失,重启数据库报ORA-01157错误
来源:互联网 发布:索尼g8145支持4g网络吗 编辑:程序博客网 时间:2024/05/16 13:02
今天做了一下undo隐藏参数的实验
在没有备份的情况下,删除正在使用的undo,然后关机
(本次使用的的oracle的隐藏参数,慎用!!!!!!!!!!!!!!)
idle> select * from V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
sys@DNDN> startup
ORACLE instance started.
Total System Global Area 335544320 bytes
Fixed Size 2020640 bytes
Variable Size 117443296 bytes
Database Buffers 213909504 bytes
Redo Buffers 2170880 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/app/oracle/oradata/dndn/undotbs1.dbf'
sys@DNDN> show parameter undo_t
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace stringUNDOTBS1
在此之前我有建立有一个未使用的undo 名字叫undotbs(区别上面的undotbs1)(*注:若没有多余的undo,则见文章最后解决办法)
sys@DNDN>shutdown immediate;
idle> startup nomount;
ORACLE instance started.
Total System Global Area 335544320 bytes
Fixed Size 2020640 bytes
Variable Size 117443296 bytes
Database Buffers 213909504 bytes
Redo Buffers 2170880 bytes
#把undo_tablespace 修改成备用的undo
idle>alter system set undo_tablespace=undotbs scope=spfile;
#把undo改成手动管理
idle> alter system set undo_management=manual scope=spfile;
System altered.
#使用隐藏参数
idle> alter system set "_offline_rollback_segments"=true scope=spfile;
System altered.
idle> shutdown immediate;
idle> startup
ORACLE instance started.
Total System Global Area 335544320 bytes
Fixed Size 2020640 bytes
Variable Size 117443296 bytes
Database Buffers 213909504 bytes
Redo Buffers 2170880 bytes
Database mounted.
Database opened.
这样数据库就可以打开了!
查看UNDOTBS1 (之前丢失的表空间) 中的段的状态是否都是offline
idle> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 OFFLINE
_SYSSMU2$ UNDOTBS1 OFFLINE
_SYSSMU3$ UNDOTBS1 OFFLINE
_SYSSMU4$ UNDOTBS1 OFFLINE
_SYSSMU5$ UNDOTBS1 OFFLINE
_SYSSMU6$ UNDOTBS1 OFFLINE
_SYSSMU7$ UNDOTBS1 OFFLINE
_SYSSMU8$ UNDOTBS1 OFFLINE
_SYSSMU9$ UNDOTBS1 OFFLINE
_SYSSMU10$ UNDOTBS1 OFFLINE
_SYSSMU11$ UNDOTBS OFFLINE
_SYSSMU12$ UNDOTBS OFFLINE
_SYSSMU13$ UNDOTBS OFFLINE
_SYSSMU14$ UNDOTBS OFFLINE
_SYSSMU15$ UNDOTBS OFFLINE
_SYSSMU16$ UNDOTBS OFFLINE
_SYSSMU17$ UNDOTBS OFFLINE
_SYSSMU18$ UNDOTBS OFFLINE
_SYSSMU19$ UNDOTBS OFFLINE
_SYSSMU20$ UNDOTBS OFFLINE
21 rows selected.
若UNDOTBS1 (之前丢失的表空间) 中的段的状态都是offline,则可以删除该undo表空间(否则,则要用到另一个隐藏参数"_corrupted_rollback_segments"把不为offline的段全用此参数设置即可删除该undo表空间 eg: alter system set "_corrupted_rollback_segments"='_SYSSMU8$' scope=spfile;或 alter system set "_corrupted_rollback_segments"='_SYSSMU8$','_SYSSMU9$' scope=spfile)
idle> drop tablespace undotbs1 including contents and datafiles;
当然打开后还要重置一下undo_management和_offline_rollback_segments
idle> alter system set undo_management=auto scope=spfile;
System altered.
idle> alter system reset "_offline_rollback_segments" scope=spfile sid='*';
System altered.
idle>shutdown immediate;
idle> startup
ORACLE instance started.
Total System Global Area 335544320 bytes
Fixed Size 2020640 bytes
Variable Size 121637600 bytes
Database Buffers 209715200 bytes
Redo Buffers 2170880 bytes
Database mounted.
Database opened.
ok!!!!!!
上面说的是有未使用的undo表空间的情况,若只有一个undo,且丢失损坏,则在数据库重启报错后,先设置隐藏参数_offline_rollback_segments和undo_management,重启,会再报错,再把这个undo给offline,就可以打开了,打开之后 删除丢失或损坏的undo,重建undo,修改参数文件指定undo,重置参数上面, 重启即可。(主要大概思路同上面方法)
注:隐藏参数
idle> 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.
- 在没备份undo的情况下,undo丢失,重启数据库报ORA-01157错误
- [Oracle] 在没有备份的情况下undo损坏怎么办?
- undo数据文件丢失的恢复
- UNDO表空间的ORA-01110错误解决(一)
- UNDO表空间的ORA-01110错误解决(二)
- 关于undo表空间配置错误的ORA-30012
- 删除undo表空间报ORA-01548
- 删除undo表空间报ORA-01548
- ORA-30052错误:修改undo-retention
- 数据库UNDO
- undo丢失的简单不完全恢复
- 【undo表空间的丢失-恢复-1】
- RMAN备份与恢复之UNDO表空间丢失
- UNDO
- Undo
- undo
- undo
- Undo
- android开发在设置权限出现Permission is only granted to system apps错误的解决办法
- Struts 2 对 Ajax 分页怎么
- 在vs2005中用正则表达式查找或替换
- poj1789之最小优先队列
- Bellman-Ford算法 和 SPFA算法
- 在没备份undo的情况下,undo丢失,重启数据库报ORA-01157错误
- Oracle DB 执行用户管理的备份和恢复
- 如何做好数据库营销
- uva10474 简单排序查找 一次AC
- Hibernate get和load区别
- C语言中《预处理》小结
- 【LeetCode】Unique Paths
- poj1789之Prim
- 购物单与怀孕预测:数据分析相关性分析案例三