vc例程:ORA-01092: ORACLE 例程终止 强行断开连接来源: 发布时间:星期一, 2009年3月2日 浏览:0次 评论:0
="t18">
今天测试部门
 人叫我过去 说是数据库当了起不来了
我过去看了看情况做了如下操作
SQL> shutdown immediate数据库已经关闭 已经卸载数据库 Oracle 例程已经关闭 SQL> startup ORACLE 例程已经启动
Total  Global Area 135338868  sFixed Size 453492 sVariable Size 109051904 sDatabase Buffers 25165824 sRedo Buffers 667648 s数据库装载完毕 ORA-01092: ORACLE 例程终止 强行断开连接
表面上看不出问题我查看alert_oracas.log文件
SYS auditing is disabledStarting up ORACLE RDBMS Version: 9.2.0.1.0. 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_do  = 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 Global Area 135338868 sFixed Size 453492 sVariable Size 109051904 sDatabase Buffers 25165824 sRedo Buffers 667648 s数据库装载完毕 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  AUTOundo_retention  eger 10800undo_suppress_errors boolean FALSE undo_tablespace UNDOSTB1SQL> 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 immediateORA-01109: 数据库未打开 已经卸载数据库 ORACLE 例程已经关闭 SQL> startup mount ORACLE 例程已经启动
Total Global Area 135338868 sFixed Size 453492 sVariable Size 109051904 sDatabase Buffers 25165824 sRedo Buffers 667648 s数据库装载完毕 SQL> alter database open; 数据库已更改
查看表空间信息
SQL> show parameter undo
NAME TYPE VALUE------------------------------------ ----------- ------------------------------ undo_management MANUALundo_retention eger 10800undo_suppress_errors boolean FALSE undo_tablespace systemSQL> 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 Global Area 135338868 sFixed Size 453492 sVariable Size 109051904 sDatabase Buffers 25165824 sRedo Buffers 667648 s数据库装载完毕 数据库已经打开 SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management MANUALundo_retention eger 10800undo_suppress_errors boolean FALSE undo_tablespace systemSQL> alter database  undo_management=auto;alter database undo_management=auto* ERROR 位于第 1 行: ORA-02231: 缺少或无效 ALTER DATABASE 选项
SQL> alter database undo_management=auto scope=spfile;alter database undo_management=auto scope=spfile* ERROR 位于第 1 行: ORA-02231: 缺少或无效 ALTER DATABASE 选项
SQL> alter system undo_management=auto scope=spfile;
系统已更改
SQL> alter system undo_tablespace='undotbs1' scope=spfile;
重启验证:
SQL> shutdown immediate数据库已经关闭 已经卸载数据库 ORACLE 例程已经关闭 SQL> startup ORACLE 例程已经启动
Total Global Area 135338868 sFixed Size 453492 sVariable Size 109051904 sDatabase Buffers 25165824 sRedo Buffers 667648 s数据库装载完毕 数据库已经打开 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 AUTOundo_retention eger 10800undo_suppress_errors boolean FALSE undo_tablespace undotbs1
发现已经好使了
教训整理总结:其实只要开始分析下v$tablespace就能发现是undo表空间名字错了很快就能解决
不过我上面这种思路方法可以用于undo表空间文件corrupt情况:) 0
相关文章读者评论发表评论 |
|
 |
专注于互联网--专注于架构 |
最新标签 网站地图 文章索引 Rss订阅 |
