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 type value------------------------------------ ----------- -----------------------undo_management string autoundo_retention integer 10800undo_suppress_errors boolean falseundo_tablespace string undostb1sql> select name from v$tablespace;
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;
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;