今天测试部门的人叫我过去，说是数据库当了，起不来了。
我过去看了看情况，做了如下操作
SQL shutdown immediate
数据库已经关闭。
已经卸载数据库。
Oracle 例程已经关闭。
SQL startup
ORACLE 例程已经启动。
Total System Global Area135338868 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 Area135338868 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 TYPEVALUE
------------------------------------ ----------- -----------------------
undo_managementstringAUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespacestringUNDOSTB1
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 Area135338868 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 TYPEVALUE
------------------------------------ ----------- ------------------------------
undo_managementstringMANUAL
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespacestringsystem
SQL select name fromv$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 Area135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL show parameter undo
NAME TYPEVALUE
------------------------------------ ----------- ------------------------------
undo_managementstringMANUAL
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespacestringsystem
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 Area135338868 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 TYPEVALUE
------------------------------------ ----------- ------------------------------
undo_managementstringAUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespacestringundotbs1
发现已经好使了
教训总结：其实只要一开始分析下v$tablespace就能发现是undo表空间的名字错了，很快就能解决的。
不过我上面这种方法可以用于undo表空间文件corrupt的情况：）
