（原創）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 2005Suclearcase/" target="_blank" >ccessful 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的情況：）

原文轉自：http://www.kjueaiud.com