（原创）ORA-01092: ORACLE 例程终止。强行断开连接
2024-08-29 13:28:55
供稿：网友
国内最大的酷站演示中心!
今天测试部门的人叫我过去，说是数据库当了，起不来了。
我过去看了看情况，做了如下操作
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/[email protected] 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的情况：）
