当前页面位置: » 丰搜网 » 文档中心 » 详细内容
（原创）ora-01092: oracle 例程终止。强行断开连接
今天测试部门的人叫我过去，说是数据库当了，起不来了。
我过去看了看情况，做了如下操作
sql> shutdown immediate数据库已经关闭。已经卸载数据库。oracle 例程已经关闭。sql> startuporacle 例程已经启动。
total system global area 135338868 bytesfixed size 453492 bytesvariable size 109051904 bytesdatabase buffers 25165824 bytesredo buffers 667648 bytes数据库装载完毕。ora-01092: oracle 例程终止。强行断开连接
表面上看不出问题，我查看alert_oracas.log文件
sys auditing is disabledstarting up oracle rdbms version: 9.2.0.1.0.system parameters with non-default values: processes = 150 timed_statistics = true shared_pool_size = 50331648 large_pool_size = 8388608 java_pool_size = 33554432 control_files = f:\oracle\oradata\oracas\control01.ctl, f:\oracle\oradata\oracas\control02.ctl, f:\oracle\oradata\oracas\control03.ctl db_block_size = 8192 db_cache_size = 25165824 compatible = 9.2.0.0.0 db_file_multiblock_read_count= 16 fast_start_mttr_target = 300 undo_management = auto undo_tablespace = undostb1 undo_retention = 10800 remote_login_passwordfile= exclusive db_domain = instance_name = oracas dispatchers = (protocol=tcp) (service=oracasxdb) job_queue_processes = 10 hash_join_enabled = true background_dump_dest = f:\oracle\admin\oracas\bdump user_dump_dest = f:\oracle\admin\oracas\udump core_dump_dest = f:\oracle\admin\oracas\cdump sort_area_size = 524288 db_name = oracas open_cursors = 300 star_transformation_enabled= false query_rewrite_enabled = false pga_aggregate_target = 19922944 aq_tm_processes = 1pmon started with pid=2dbw0 started with pid=3lgwr started with pid=4ckpt started with pid=5smon started with pid=6reco started with pid=7cjq0 started with pid=8qmn0 started with pid=9mon apr 18 17:30:25 2005starting up 1 shared server(s) ...starting up 1 dispatcher(s) for.network address '(address=(partial=yes)(protocol=tcp))'...mon apr 18 17:30:26 2005alter database mountmon apr 18 17:30:30 2005successful mount of redo thread 1, with mount id 2424210674.mon apr 18 17:30:30 2005database mounted in exclusive mode.completed: alter database mountmon apr 18 17:30:30 2005alter database openmon apr 18 17:30:32 2005thread 1 opened at log sequence 105 current log# 2 seq# 105 mem# 0: f:\oracle\oradata\oracas\redo02.logsuccessful open of redo thread 1.mon apr 18 17:30:32 2005smon: enabling cache recoverymon apr 18 17:30:34 2005errors in file f:\oracle\admin\oracas\udump\oracas_ora_3404.trc:ora-30012: 撤消表空间 'undostb1' 不存在或类型不正确
mon apr 18 17:30:34 2005error 30012 happened during db open, shutting down databaseuser: terminating instance due to error 30012mon apr 18 17:30:35 2005errors in file f:\oracle\admin\oracas\bdump\oracas_smon_996.trc:ora-30012: undo tablespace '' does not exist or of wrong type
mon apr 18 17:30:35 2005errors in file f:\oracle\admin\oracas\bdump\oracas_pmon_3500.trc:ora-30012: undo tablespace '' does not exist or of wrong type
instance terminated by user, pid = 3404ora-1092 signalled during: alter database open...
于是我查看了以下信息来确认undo表空间的问题
sql> conn sys/sys@oracas as sysdba已连接到空闲例程。sql> startup mountoracle 例程已经启动。
total system global area 135338868 bytesfixed size 453492 bytesvariable size 109051904 bytesdatabase buffers 25165824 bytesredo buffers 667648 bytes数据库装载完毕。sql> select name from v$datafile;
name------------------------------------------------------------------------f:\oracle\oradata\oracas ystem01.dbff:\oracle\oradata\oracas\undotbs01.dbff:\oracle\oradata\oracas\cwmlite01.dbff:\oracle\oradata\oracas\drsys01.dbff:\oracle\oradata\oracas\example01.dbff:\oracle\oradata\oracas\indx01.dbff:\oracle\oradata\oracas\odm01.dbff:\oracle\oradata\oracas\tools01.dbff:\oracle\oradata\oracas\users01.dbff:\oracle\oradata\oracas\xdb01.dbfd:\oradata\oracas\tscas11.dbfe:\oradata\oracas\tfcas12.dbfd:\oradata\oracas\tscas21.dbfe:\oradata\oracas\tfcas22.dbfd:\oradata\oracas\tscas31.dbfe:\oradata\oracas\tfcas32.dbfd:\oradata\oracas\tscas41.dbfe:\oradata\oracas\tfcas42.dbfd:\oradata\oracas\tscasindx1.dbfe:\oradata\oracas\tscasindx2.dbfd:\oradata\oracas\tfcas13.dbfd:\oradata\oracas\tfcas14.dbfd:\oradata\oracas\tfcas23.dbfd:\oradata\oracas\tfcas24.dbfe:\oradata\oracas\tscasindx12.dbfe:\oradata\oracas\tscasindx13.dbfe:\oradata\oracas\tscasindx24.dbfe:\oradata\oracas\tscasindx25.dbfc: napshot01.dbfc:\tscas1.dbf
已选择30行。
sql> show parameter undo
name type value------------------------------------ ----------- -----------------------undo_management string autoundo_retention integer 10800undo_suppress_errors boolean falseundo_tablespace string undostb1sql> select name from v$tablespace;
name------------------------------cwmlitedrsysexampleindxodmsystemtoolsundotbs1usersxdbtemptscas1tscas2tscas3tscas4tscasindx1tscasindx2snapshot_ts
已选择18行。当时没有自己看问题，就以为是undo文件损坏了，决定重建。
注意：后来才发现这里以后的操作是饶了一个解决问题的弯路。
由于不open没有办法重新创建undo 表空间，所以决定先用系统默认的undo表空间来启动，然后重建undo
表空间，具体操作如下：
sql> create pfile from spfile;
文件已创建。
修改pfile文件
#*.undo_management='auto'#*.undo_tablespace='undostb1'undo_management=manualundo_tablespace='system'
关闭数据库，并且从目录f:\oracle\ora92\database下去掉spfileoracas.ora文件
重新启动
sql> shutdown immediateora-01109: 数据库未打开
已经卸载数据库。oracle 例程已经关闭。sql> startup mountoracle 例程已经启动。
total system global area 135338868 bytesfixed size 453492 bytesvariable size 109051904 bytesdatabase buffers 25165824 bytesredo buffers 667648 bytes数据库装载完毕。sql> alter database open;
数据库已更改。
查看表空间信息
sql> show parameter undo
name type value------------------------------------ ----------- ------------------------------undo_management string manualundo_retention integer 10800undo_suppress_errors boolean falseundo_tablespace string systemsql> select name from v$tablespace;
name------------------------------cwmlitedrsysexampleindxodmsystemtoolsundotbs1usersxdbtemptscas1tscas2tscas3tscas4tscasindx1tscasindx2snapshot_ts
并且打开图形界面查看，这时候才发现undo表空间的名字是undotbs1
其实早就能发现的，大家以后分析问题一定不要太快下结论，要自己分析。。。
于是做以下操作改变undo 表空间的名字
sql> create spfile from pfile;
文件已创建。
sql> shutdown immediate数据库已经关闭。已经卸载数据库。oracle 例程已经关闭。sql> startuporacle 例程已经启动。
total system global area 135338868 bytesfixed size 453492 bytesvariable size 109051904 bytesdatabase buffers 25165824 bytesredo buffers 667648 bytes数据库装载完毕。数据库已经打开。sql> show parameter undo
name type value------------------------------------ ----------- ------------------------------undo_management string manualundo_retention integer 10800undo_suppress_errors boolean falseundo_tablespace string systemsql> alter database set undo_management=auto;alter database set undo_management=auto *error 位于第 1 行:ora-02231: 缺少或无效的 alter database 选项
sql> alter database set undo_management=auto scope=spfile;alter database set undo_management=auto scope=spfile *error 位于第 1 行:ora-02231: 缺少或无效的 alter database 选项
sql> alter system set undo_management=auto scope=spfile;
系统已更改。
sql> alter system set undo_tablespace='undotbs1' scope=spfile;
重启验证：
sql> shutdown immediate数据库已经关闭。已经卸载数据库。oracle 例程已经关闭。sql> startuporacle 例程已经启动。
total system global area 135338868 bytesfixed size 453492 bytesvariable size 109051904 bytesdatabase buffers 25165824 bytesredo buffers 667648 bytes数据库装载完毕。数据库已经打开。sql> create fpile from spfile;create fpile from spfile *error 位于第 1 行:ora-00901: 无效 create 命令
sql> create pfile from spfile;
文件已创建。
sql> show parameter undo
name type value------------------------------------ ----------- ------------------------------undo_management string autoundo_retention integer 10800undo_suppress_errors boolean falseundo_tablespace string undotbs1
发现已经好使了
教训总结：其实只要一开始分析下v$tablespace就能发现是undo表空间的名字错了，很快就能解决的。
不过我上面这种方法可以用于undo表空间文件corrupt的情况：）
