（原创）ORA-01092: ORACLE 例程终止。强行断开连接
2024-08-29 13:44:14
供稿：网友
今天测试部门的人叫我过去，说是数据库当了，起不来了。
我过去看了看情况，做了如下操作
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的情况：）
