（原創）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的情況：）
