ora-600 内部错误代码, 参数: [kddummy_blkchk], [2], [41], [38504], [], [], [], []
2015/08/31
desertxu
今天
看了 xifenfei 在 http://www.xifenfei.com/2013/03/bbed%E6%A8%A1%E6%8B%9F%E6%8F%90%E4%BA%A4%E4%BA%8B%E5%8A%A1%E4%BA%8C%E4%B9%8B%E5%B1%8F%E8%94%BDsmon%E5%9B%9E%E6%BB%9A%E4%BA%8B%E5%8A%A1.html 博客写关于
模拟提交事务二之屏蔽smon回滚事务,所以我也想学习学习
用bbed 模拟oracle 提交事物,可是事物是提交成功了,重启之后(shutdown abort) startup 之后就报上面的错误,ora-600 kddummy_blkchk,2,41,38505,这里2 代表2号文件 undo tablespace 41号块,正是3号回滚段 _SYSSMU3$ 的段头。查看了xifenfei blog写了处理方案 如下
附:执行的语句
alter system set "_offline_rollback_segments"= "_SYSSMU3$" scope=spfile;
alter system set undo_management=MANUAL scope=spfile;
alter system set undo_tablespace=SYSTEM scope=spfile;
3.启动数据库,快速删除包含_SYSSMU3$ undo表空间
create undo tablespace undotbs02 datafile '/export/home/oracle/oradata/ora10g/udotbs02.dbf' size 100M autoextend on maxsize unlimited;
alter system set undo_tablespace=undotbs02 scope=spfile;
alter system se undo_management=AUTO scope=spfile;
drop tablespace undotbs1 including conte nts and datafile;
alter system set '_offline_rollback_segments'=''scope=spfile;
用bbed 模拟oracle 提交事物,可是事物是提交成功了,重启之后(shutdown abort) startup 之后就报上面的错误,ora-600 kddummy_blkchk,2,41,38505,这里2 代表2号文件 undo tablespace 41号块,正是3号回滚段 _SYSSMU3$ 的段头。查看了xifenfei blog写了处理方案 如下
处理方案
1.使用隐含参数屏蔽异常回滚段_offline_rollback_segments= _SYSSMU3$
2.修改undo_tablespace=SYSTEM/undo_management=MANUAL
3.启动数据库,快速删除包含_SYSSMU3$ undo表空间
4.新建undo表空间
5.修改undo_tablespace=new_undo/undo_management=AUTO,除掉隐含参数
6.使用新参数文件重启数据库
7.建议:使用逻辑导出导入重建数据库
以下是我处理的过程
idle@0>
idle@0>
idle@0> shutdown abort
ORACLE 例程已经关闭。
idle@0> startup mount
ORACLE 例程已经启动。
Total System Global Area 935329792 bytes
Fixed Size 1283524 bytes
Variable Size 234883644 bytes
Database Buffers 696254464 bytes
Redo Buffers 2908160 bytes
数据库装载完毕。
idle@0> alter system set '_offline_rollback_segments'= _SYSSMU3$;
alter system set '_offline_rollback_segments'= _SYSSMU3$
*
第 1 行出现错误:
ORA-00922: 选项缺失或无效
idle@0> alter system set "_offline_rollback_segments"= _SYSSMU3$;
alter system set "_offline_rollback_segments"= _SYSSMU3$
*
第 1 行出现错误:
ORA-00911: 无效字符
idle@0> alter system set "_offline_rollback_segments"= "_SYSSMU3$";
alter system set "_offline_rollback_segments"= "_SYSSMU3$"
*
第 1 行出现错误:
ORA-02095: 无法修改指定的初始化参数
idle@0> alter system set "_offline_rollback_segments"= "_SYSSMU3$" scope=spfile;
系统已更改。
idle@0> alter system set "_offline_rollback_segments"= "_SYSSMU3$" scope=spfile;
系统已更改。
idle@0> atler system set undo_management=MANUAL;
SP2-0734: 未知的命令开头 "atler syst..." - 忽略了剩余的行。
idle@0> aatler system set undo_management=MANUAL;
SP2-0734: 未知的命令开头 "aatler sys..." - 忽略了剩余的行。
idle@0> atler system set undo_management=MANUAL;
SP2-0734: 未知的命令开头 "atler syst..." - 忽略了剩余的行。
idle@0> alter
2
idle@0>
idle@0> alter system set undo_management=MANUAL;
alter system set undo_management=MANUAL
*
第 1 行出现错误:
ORA-02095: 无法修改指定的初始化参数
idle@0> alter system set undo_management=MANUAL scope=spfile;
alter system set undo_tablespace=SYSTEM scope=spfile;
系统已更改。
idle@0>
]
系统已更改。
idle@0>
SP2-0042: 未知命令 "]" - 其余行忽略。
idle@0>
idle@0>
idle@0> create undo tablespace undotbs02 datafile '/export/home/oracle/oradata/udotbs02.dbf' size 100M autoextend on maxsize unlimited;
create undo tablespace undotbs02 datafile '/export/home/oracle/oradata/udotbs02.dbf' size 100M autoextend on maxsize unlimited
*
第 1 行出现错误:
ORA-01109: 数据库未打开
idle@0> shutdown immedaite
SP2-0717: 非法的 SHUTDOWN 选项
idle@0> shutdown immediate
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
idle@0> startup
ORACLE 例程已经启动。
Total System Global Area 935329792 bytes
Fixed Size 1283524 bytes
Variable Size 234883644 bytes
Database Buffers 696254464 bytes
Redo Buffers 2908160 bytes
数据库装载完毕。
数据库已经打开。
idle@0> create undo tablespace undotbs02 datafile '/export/home/oracle/oradata/udotbs02.dbf' size 100M autoextend on maxsize unlimited;
表空间已创建。
idle@0> alter system set undo_tablespace=undotbs02;
alter system set undo_tablespace=undotbs02
*
第 1 行出现错误:
ORA-02097: 无法修改参数, 因为指定的值无效
ORA-30014: 此操作仅在自动还原管理模式中才受支持
idle@0> alter system se undo_management=AUTO;
alter system se undo_management=AUTO
*
第 1 行出现错误:
ORA-02065: 非法的 ALTER SYSTEM 选项
idle@0> alter system se undo_management=AUTO;
alter system se undo_management=AUTO
*
第 1 行出现错误:
ORA-02065: 非法的 ALTER SYSTEM 选项
idle@0> alter system set undo_management=auto scope=spfile;
系统已更改。
idle@0> alter system set undo_tablespace=undotbs02 scope=spfile;
系统已更改。
idle@0> drop tablespace undotbs01 including datafiles and contents;
drop tablespace undotbs01 including datafiles and contents
*
第 1 行出现错误:
ORA-01911: 需要 CONTENTS 关键字
idle@0> drop tablespace undotbs01 including contents and datafile;
drop tablespace undotbs01 including contents and datafile
*
第 1 行出现错误:
ORA-00905: 缺失关键字
idle@0> drop tablespace undotbs01 including contents;
drop tablespace undotbs01 including contents
*
第 1 行出现错误:
ORA-00959: 表空间 'UNDOTBS01' 不存在
idle@0> drop tablespace undotbs01 including contents and datafiles;
drop tablespace undotbs01 including contents and datafiles
*
第 1 行出现错误:
ORA-00959: 表空间 'UNDOTBS01' 不存在
idle@0> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
EXAMPLE
UNDOTBS02
已选择7行。
idle@0> drop tablespace UNDOTBS1 including contents and datafiles;
表空间已删除。
idle@0> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
idle@0> startup
ORACLE 例程已经启动。
Total System Global Area 935329792 bytes
Fixed Size 1283524 bytes
Variable Size 234883644 bytes
Database Buffers 696254464 bytes
Redo Buffers 2908160 bytes
数据库装载完毕。
数据库已经打开。
idle@0> alter system set '_offline_rollback_segments'=''scope=spfile;
alter system set '_offline_rollback_segments'=''scope=spfile
*
第 1 行出现错误:
ORA-00922: 选项缺失或无效
idle@0> alter system set "_offline_rollback_segments"='' scope=spfile;
idle@0> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS02
idle@0>
以下是我处理的过程
idle@0>
idle@0>
idle@0> shutdown abort
ORACLE 例程已经关闭。
idle@0> startup mount
ORACLE 例程已经启动。
Total System Global Area 935329792 bytes
Fixed Size 1283524 bytes
Variable Size 234883644 bytes
Database Buffers 696254464 bytes
Redo Buffers 2908160 bytes
数据库装载完毕。
idle@0> alter system set '_offline_rollback_segments'= _SYSSMU3$;
alter system set '_offline_rollback_segments'= _SYSSMU3$
*
第 1 行出现错误:
ORA-00922: 选项缺失或无效
idle@0> alter system set "_offline_rollback_segments"= _SYSSMU3$;
alter system set "_offline_rollback_segments"= _SYSSMU3$
*
第 1 行出现错误:
ORA-00911: 无效字符
idle@0> alter system set "_offline_rollback_segments"= "_SYSSMU3$";
alter system set "_offline_rollback_segments"= "_SYSSMU3$"
*
第 1 行出现错误:
ORA-02095: 无法修改指定的初始化参数
idle@0> alter system set "_offline_rollback_segments"= "_SYSSMU3$" scope=spfile;
系统已更改。
idle@0> alter system set "_offline_rollback_segments"= "_SYSSMU3$" scope=spfile;
系统已更改。
idle@0> atler system set undo_management=MANUAL;
SP2-0734: 未知的命令开头 "atler syst..." - 忽略了剩余的行。
idle@0> aatler system set undo_management=MANUAL;
SP2-0734: 未知的命令开头 "aatler sys..." - 忽略了剩余的行。
idle@0> atler system set undo_management=MANUAL;
SP2-0734: 未知的命令开头 "atler syst..." - 忽略了剩余的行。
idle@0> alter
2
idle@0>
idle@0> alter system set undo_management=MANUAL;
alter system set undo_management=MANUAL
*
第 1 行出现错误:
ORA-02095: 无法修改指定的初始化参数
idle@0> alter system set undo_management=MANUAL scope=spfile;
alter system set undo_tablespace=SYSTEM scope=spfile;
系统已更改。
idle@0>
]
系统已更改。
idle@0>
SP2-0042: 未知命令 "]" - 其余行忽略。
idle@0>
idle@0>
idle@0> create undo tablespace undotbs02 datafile '/export/home/oracle/oradata/udotbs02.dbf' size 100M autoextend on maxsize unlimited;
create undo tablespace undotbs02 datafile '/export/home/oracle/oradata/udotbs02.dbf' size 100M autoextend on maxsize unlimited
*
第 1 行出现错误:
ORA-01109: 数据库未打开
idle@0> shutdown immedaite
SP2-0717: 非法的 SHUTDOWN 选项
idle@0> shutdown immediate
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
idle@0> startup
ORACLE 例程已经启动。
Total System Global Area 935329792 bytes
Fixed Size 1283524 bytes
Variable Size 234883644 bytes
Database Buffers 696254464 bytes
Redo Buffers 2908160 bytes
数据库装载完毕。
数据库已经打开。
idle@0> create undo tablespace undotbs02 datafile '/export/home/oracle/oradata/udotbs02.dbf' size 100M autoextend on maxsize unlimited;
表空间已创建。
idle@0> alter system set undo_tablespace=undotbs02;
alter system set undo_tablespace=undotbs02
*
第 1 行出现错误:
ORA-02097: 无法修改参数, 因为指定的值无效
ORA-30014: 此操作仅在自动还原管理模式中才受支持
idle@0> alter system se undo_management=AUTO;
alter system se undo_management=AUTO
*
第 1 行出现错误:
ORA-02065: 非法的 ALTER SYSTEM 选项
idle@0> alter system se undo_management=AUTO;
alter system se undo_management=AUTO
*
第 1 行出现错误:
ORA-02065: 非法的 ALTER SYSTEM 选项
idle@0> alter system set undo_management=auto scope=spfile;
系统已更改。
idle@0> alter system set undo_tablespace=undotbs02 scope=spfile;
系统已更改。
idle@0> drop tablespace undotbs01 including datafiles and contents;
drop tablespace undotbs01 including datafiles and contents
*
第 1 行出现错误:
ORA-01911: 需要 CONTENTS 关键字
idle@0> drop tablespace undotbs01 including contents and datafile;
drop tablespace undotbs01 including contents and datafile
*
第 1 行出现错误:
ORA-00905: 缺失关键字
idle@0> drop tablespace undotbs01 including contents;
drop tablespace undotbs01 including contents
*
第 1 行出现错误:
ORA-00959: 表空间 'UNDOTBS01' 不存在
idle@0> drop tablespace undotbs01 including contents and datafiles;
drop tablespace undotbs01 including contents and datafiles
*
第 1 行出现错误:
ORA-00959: 表空间 'UNDOTBS01' 不存在
idle@0> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
EXAMPLE
UNDOTBS02
已选择7行。
idle@0> drop tablespace UNDOTBS1 including contents and datafiles;
表空间已删除。
idle@0> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
idle@0> startup
ORACLE 例程已经启动。
Total System Global Area 935329792 bytes
Fixed Size 1283524 bytes
Variable Size 234883644 bytes
Database Buffers 696254464 bytes
Redo Buffers 2908160 bytes
数据库装载完毕。
数据库已经打开。
idle@0> alter system set '_offline_rollback_segments'=''scope=spfile;
alter system set '_offline_rollback_segments'=''scope=spfile
*
第 1 行出现错误:
ORA-00922: 选项缺失或无效
idle@0> alter system set "_offline_rollback_segments"='' scope=spfile;
idle@0> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS02
idle@0>
附:执行的语句
alter system set "_offline_rollback_segments"= "_SYSSMU3$" scope=spfile;
alter system set undo_management=MANUAL scope=spfile;
alter system set undo_tablespace=SYSTEM scope=spfile;
3.启动数据库,快速删除包含_SYSSMU3$ undo表空间
create undo tablespace undotbs02 datafile '/export/home/oracle/oradata/ora10g/udotbs02.dbf' size 100M autoextend on maxsize unlimited;
alter system set undo_tablespace=undotbs02 scope=spfile;
alter system se undo_management=AUTO scope=spfile;
drop tablespace undotbs1 including conte nts and datafile;
alter system set '_offline_rollback_segments'=''scope=spfile;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21980353/viewspace-1787596/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21980353/viewspace-1787596/