| 本月文章推荐 |
ORA-01092: ORACLE 例程终止 强行断开连接发表日期：2008-2-9
|
|
今天测试部门的人叫我过去，说是数据库当了，起不来了。 我过去看了看情况，做了如下操作 SQL> shutdown immediate 数据库已经关闭。 已经卸载数据库。 Oracle 例程已经关闭。 SQL> startup ORACLE 例程已经启动。 Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes 数据库装载完毕。 ORA-01092: ORACLE 例程终止。强行断开连接 表面上看不出问题，我查看alert_oracas.log文件 SYS auditing is disabled Starting 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 = 1 PMON started with pid=2 DBW0 started with pid=3 LGWR started with pid=4 CKPT started with pid=5 SMON started with pid=6 RECO started with pid=7 CJQ0 started with pid=8 QMN0 started with pid=9 Mon Apr 18 17:30:25 2005 starting up 1 shared server(s) ... starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... Mon Apr 18 17:30:26 2005 ALTER DATABASE MOUNT Mon Apr 18 17:30:30 2005 SUCcessful mount of redo thread 1, with mount id 2424210674. Mon Apr 18 17:30:30 2005 Database mounted in Exclusive Mode. Completed: ALTER DATABASE MOUNT Mon Apr 18 17:30:30 2005 ALTER DATABASE OPEN Mon Apr 18 17:30:32 2005 Thread 1 opened at log sequence 105 Current log# 2 seq# 105 mem# 0: F:\ORACLE\ORADATA\ORACAS\REDO02.LOG Successful open of redo thread 1. Mon Apr 18 17:30:32 2005 SMON: enabling cache recovery Mon Apr 18 17:30:34 2005 Errors in file f:\oracle\admin\oracas\udump\oracas_ora_3404.trc: ORA-30012: 撤消表空间 'UNDOSTB1' 不存在或类型不正确 Mon Apr 18 17:30:34 2005 Error 30012 happened during db open, shutting down database USER: terminating instance due to error 30012 Mon Apr 18 17:30:35 2005 Errors 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 2005 Errors 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 = 3404 ORA-1092 signalled during: ALTER DATABASE OPEN... 于是我查看了以下信息来确认undo表空间的问题 SQL> conn sys/sys@oracas as sysdba 已连接到空闲例程。 SQL> startup mount ORACLE 例程已经启动。 Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes 数据库装载完毕。 SQL> select name from v$datafile; NAME ------------------------------------------------------------------------ F:\ORACLE\ORADATA\ORACAS\SYSTEM01.DBF F:\ORACLE\ORADATA\ORACAS\UNDOTBS01.DBF F:\ORACLE\ORADATA\ORACAS\CWMLITE01.DBF F:\ORACLE\ORADATA\ORACAS\DRSYS01.DBF F:\ORACLE\ORADATA\ORACAS\EXAMPLE01.DBF F:\ORACLE\ORADATA\ORACAS\INDX01.DBF F:\ORACLE\ORADATA\ORACAS\ODM01.DBF F:\ORACLE\ORADATA\ORACAS\TOOLS01.DBF F:\ORACLE\ORADATA\ORACAS\USERS01.DBF F:\ORACLE\ORADATA\ORACAS\XDB01.DBF D:\ORADATA\ORACAS\TSCAS11.DBF E:\ORADATA\ORACAS\TFCAS12.DBF D:\ORADATA\ORACAS\TSCAS21.DBF E:\ORADATA\ORACAS\TFCAS22.DBF D:\ORADATA\ORACAS\TSCAS31.DBF E:\ORADATA\ORACAS\TFCAS32.DBF D:\ORADATA\ORACAS\TSCAS41.DBF E:\ORADATA\ORACAS\TFCAS42.DBF D:\ORADATA\ORACAS\TSCASINDX1.DBF E:\ORADATA\ORACAS\TSCASINDX2.DBF D:\ORADATA\ORACAS\TFCAS13.DBF D:\ORADATA\ORACAS\TFCAS14.DBF D:\ORADATA\ORACAS\TFCAS23.DBF D:\ORADATA\ORACAS\TFCAS24.DBF E:\ORADATA\ORACAS\TSCASINDX12.DBF E:\ORADATA\ORACAS\TSCASINDX13.DBF E:\ORADATA\ORACAS\TSCASINDX24.DBF E:\ORADATA\ORACAS\TSCASINDX25.DBF C:\SNAPSHOT01.DBF C:\TSCAS1.DBF 已选择30行。 SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ----------------------- undo_management string AUTO undo_retention integer 10800 undo_suppress_errors boolean FALSE undo_tablespace string UNDOSTB1 SQL> select name from v$tablespace; NAME ------------------------------ CWMLITE DRSYS EXAMPLE INDX ODM SYSTEM TOOLS UNDOTBS1 USERS XDB TEMP TSCAS1 TSCAS2 TSCAS3 TSCAS4 TSCASINDX1 TSCASINDX2 SNAPSHOT_TS 已选择18行。 当时没有自己看问题，就以为是undo文件损坏了，决定重建。 注重：后来才发现这里以后的操作是饶了一个解决问题的弯路。 由于不open没有办法重新创建undo 表空间，所以决定先用系统默认的undo表空间来启动，然后重建undo 表空间，具体操作如下： SQL> create pfile from spfile; 文件已创建。 修改pfile文件 #*.undo_management='AUTO' #*.undo_tablespace='UNDOSTB1' undo_management=manual undo_tablespace='system' 关闭数据库，并且从目录F:\oracle\ora92\database下去掉SPFILEORACAS.ORA文件 重新启动 SQL> shutdown immediate ORA-01109: 数据库未打开 已经卸载数据库。 ORACLE 例程已经关闭。 SQL> startup mount ORACLE 例程已经启动。 Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes 数据库装载完毕。 SQL> alter database open; 数据库已更改。 查看表空间信息 SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string MANUAL undo_retention integer 10800 undo_suppress_errors boolean FALSE undo_tablespace string system SQL> select name from v$tablespace; NAME ------------------------------ CWMLITE DRSYS EXAMPLE INDX ODM SYSTEM TOOLS UNDOTBS1 USERS XDB TEMP TSCAS1 TSCAS2 TSCAS3 TSCAS4 TSCASINDX1 TSCASINDX2 SNAPSHOT_TS 并且打开图形界面查看，这时候才发现undo表空间的名字是UNDOTBS1 其实早就能发现的，大家以后分析问题一定不要太快下结论，要自己分析。。。 于是做以下操作改变undo 表空间的名字 SQL> create spfile from pfile; 文件已创建。 SQL> shutdown immediate 数据库已经关闭。 已经卸载数据库。 ORACLE 例程已经关闭。 SQL> startup ORACLE 例程已经启动。 Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes 数据库装载完毕。 数据库已经打开。 SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string MANUAL undo_retention integer 10800 undo_suppress_errors boolean FALSE undo_tablespace string system SQL> 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> startup ORACLE 例程已经启动。 Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo 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 AUTO undo_retention integer 10800 undo_suppress_errors boolean FALSE undo_tablespace string undotbs1 发现已经好使了 教训总结：其实只要一开始分析下v$tablespace就能发现是undo表空间的名字错了，很快就能解决的。 不过我上面这种方法可以用于undo表空间文件corrupt的情况：） |
| 上一篇：ORA-03113错误分析与解决
人气:731 下一篇：用DBMS_SYS_SQL包进行批量授权 人气:703 |
| 浏览全部Oracle教程的内容
Dreamweaver插件下载 网页广告代码 祝你圣诞节快乐 2009年新年快乐 |
