情况是这样的，有两台OARCLE服务器，假定 A服务器和B服务器吧两太机器硬件配置差不多，xeon双核双cpu,内存都是4G,A的cpu要新点，B是个早点时候的机器两个机器上的oracle 数据库也一致，从9.2.0.1升级到9.2.0.7上的数据库里面的数据也基本相同，相差不到几百条,三个表都不大，最大表3万条,在A上看查看执行计划对这个表是全表扫描了在做三个表的连接查询，在A上3秒返回结果，在B上完全不返回，无论等多久奇怪的是，机器 B上很多类似查询都无法返回B的机器表分析做过，索引分析做过，单表查询没问题，稍微关联就死掉问题出在什么地方，从何处入手查找呢
where event not like 'SQL*Net%';看等待的是什么
select * from .....;SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
------------------------------ ---------------- ------------------------------
_SYSSMU20$ NEEDS RECOVERY SIM_UNDO
_SYSSMU21$ OFFLINE SIM_UNDO
_SYSSMU22$ OFFLINE SIM_UNDO
_SYSSMU23$ OFFLINE SIM_UNDO
_SYSSMU24$ OFFLINE SIM_UNDO这期间用了一个重要的查询SELECT b.File_Id,
b.File_Name,
a.COUNT
FROM X$kcbfwait a,
Dba_Data_Files b
WHERE a.Indx = b.File_Id-1
AND a.COUNT > 0
ORDER BY a.COUNT;
看了一下文件等待次数，发现有个undo文件等待次数超过了1000多,于是定位undo问题
而后修改pfile文件里*.undo_management='manual'又执行alter database datafile 'E:\SIM_SYSTEM_UNDO_000.DBF' offline drop;
alter database datafile 'E:\SIM_SYSTEM_UNDO_001.DBF' offline drop;
...把这些给删除了，而后
startup pfile = '...ora'数据库可以起来，查询不在等待了
但又遇到新问题了SQL> select segment_name,status,tablespace_name from dba_rollback_segs;SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU1$ OFFLINE SIM_UNDO_NEW
_SYSSMU2$ OFFLINE SIM_UNDO_NEW
_SYSSMU3$ OFFLINE SIM_UNDO_NEW
_SYSSMU4$ OFFLINE SIM_UNDO_NEW
_SYSSMU5$ OFFLINE SIM_UNDO_NEW
_SYSSMU6$ OFFLINE SIM_UNDO_NEW
_SYSSMU7$ OFFLINE SIM_UNDO_NEW
_SYSSMU8$ OFFLINE SIM_UNDO_NEW
_SYSSMU9$ OFFLINE SIM_UNDO_NEW
_SYSSMU10$ OFFLINE SIM_UNDO_NEWSEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU20$ NEEDS RECOVERY SIM_UNDO
_SYSSMU21$ OFFLINE SIM_UNDO
_SYSSMU22$ OFFLINE SIM_UNDO
_SYSSMU23$ OFFLINE SIM_UNDO
_SYSSMU24$ OFFLINE SIM_UNDO现在系统的 dba_rollback_segs 使用system表空间，我怎么重新建立一个呢
后来发现undo表空间 有文件 NEEDS RECOVERY干掉这个表空间，重新建立系统undo表空间，问题解决
(C) 版权所有 1985-2003 Microsoft Corp.C:\Documents and Settings\Administrator>sqlplus "/as sysdba'SQL*Plus: Release 9.2.0.7.0 - Production on 星期三 2月 18 15:22:32 2009Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SP2-0306: 无效选项。
用法: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
其中 <logon> : : = <username>[/<password>][@<connect_string>] | /
请输入用户名:
C:\Documents and Settings\Administrator>sqlplus "/as sysdba"SQL*Plus: Release 9.2.0.7.0 - Production on 星期三 2月 18 15:22:39 2009Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.已连接到空闲例程。SQL> startup nomount;
ORA-01078: failure in processing system parameters
LRM-00109: ???????????????? 'E:\ORACLE\ORA92\DATABASE\INITTA090213.ORA'
SQL> startup nomount;
ORA-00600: internal error code, arguments: [733], [1952543792], [pga heap], [], [], [], [], []
SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SQL> startup pfile='e:\a.ora'
ORACLE 例程已经启动。Total System Global Area 1267804344 bytes
Fixed Size 456888 bytes
Variable Size 1015021568 bytes
Database Buffers 251658240 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> select segment_name,status,tablespace_name from dba_rollback_segs;SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU1$ OFFLINE SIM_UNDO_NEW
_SYSSMU2$ OFFLINE SIM_UNDO_NEW
_SYSSMU3$ OFFLINE SIM_UNDO_NEW
_SYSSMU4$ OFFLINE SIM_UNDO_NEW
_SYSSMU5$ OFFLINE SIM_UNDO_NEW
_SYSSMU6$ OFFLINE SIM_UNDO_NEW
_SYSSMU7$ OFFLINE SIM_UNDO_NEW
_SYSSMU8$ OFFLINE SIM_UNDO_NEW
_SYSSMU9$ OFFLINE SIM_UNDO_NEW
_SYSSMU10$ OFFLINE SIM_UNDO_NEWSEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU20$ NEEDS RECOVERY SIM_UNDO
_SYSSMU21$ OFFLINE SIM_UNDO
_SYSSMU22$ OFFLINE SIM_UNDO
_SYSSMU23$ OFFLINE SIM_UNDO
_SYSSMU24$ OFFLINE SIM_UNDO已选择16行。SQL> crate spfile from pfile;
SP2-0734: 未知的命令开头 "crate spfi..." - 忽略了剩余的行。
SQL> create spfile from pfile;文件已创建。SQL> alter database datafile 'E:\TA090213data\SIM_SYSTEM_UNDO_NEW1.DBF' offline drop;数据库已更改。SQL> alter database datafile 'E:\TA090213data\SIM_SYSTEM_UNDO_NEW2.DBF' offline drop;
alter database datafile 'E:\TA090213data\SIM_SYSTEM_UNDO_NEW2.DBF' offline drop
*
ERROR 位于第 1 行:
ORA-01516: 不存在的日志文件, 数据文件或临时文件
'E:\TA090213data\SIM_SYSTEM_UNDO_NEW2.DBF'
SQL> alter database datafile 'E:\TA090213data\SIM_SYSTEM_UNDO_NEW0.DBF' offline drop;数据库已更改。SQL> alter database datafile 'E:\TA090213data\SIM_SYSTEM_UNDO_000.DBF' offline drop;数据库已更改。SQL> alter database datafile 'E:\TA090213data\SIM_SYSTEM_UNDO_001.DBF' offline drop;数据库已更改。SQL> alter database datafile 'E:\TA090213data\SIM_SYSTEM_UNDO_002.DBF' offline drop;数据库已更改。SQL> select segment_name,status,tablespace_name from dba_rollback_segs;SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU1$ OFFLINE SIM_UNDO_NEW
_SYSSMU2$ OFFLINE SIM_UNDO_NEW
_SYSSMU3$ OFFLINE SIM_UNDO_NEW
_SYSSMU4$ OFFLINE SIM_UNDO_NEW
_SYSSMU5$ OFFLINE SIM_UNDO_NEW
_SYSSMU6$ OFFLINE SIM_UNDO_NEW
_SYSSMU7$ OFFLINE SIM_UNDO_NEW
_SYSSMU8$ OFFLINE SIM_UNDO_NEW
_SYSSMU9$ OFFLINE SIM_UNDO_NEW
_SYSSMU10$ OFFLINE SIM_UNDO_NEWSEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU20$ NEEDS RECOVERY SIM_UNDO
_SYSSMU21$ OFFLINE SIM_UNDO
_SYSSMU22$ OFFLINE SIM_UNDO
_SYSSMU23$ OFFLINE SIM_UNDO
_SYSSMU24$ OFFLINE SIM_UNDO已选择16行。SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。Total System Global Area 1267804344 bytes
Fixed Size 456888 bytes
Variable Size 1015021568 bytes
Database Buffers 251658240 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
ORA-01092: ORACLE 例程终止。强行断开连接
从Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production中断开C:\Documents and Settings\Administrator>sqlplus "/as sysdba"SQL*Plus: Release 9.2.0.7.0 - Production on 星期三 2月 18 15:37:00 2009Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.已连接到空闲例程。SQL> startup pfile='e:\1.ora'
LRM-00109: 无法打开参数文件 'e:\1.ora'
ORA-01078: 处理系统参数失败
SQL> startup pfile='e:\a.ora'
ORACLE 例程已经启动。Total System Global Area 1267804344 bytes
Fixed Size 456888 bytes
Variable Size 1015021568 bytes
Database Buffers 251658240 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> select segment_name,status,tablespace_name from dba_rollback_segs;SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU1$ OFFLINE SIM_UNDO_NEW
_SYSSMU2$ OFFLINE SIM_UNDO_NEW
_SYSSMU3$ OFFLINE SIM_UNDO_NEW
_SYSSMU4$ OFFLINE SIM_UNDO_NEW
_SYSSMU5$ OFFLINE SIM_UNDO_NEW
_SYSSMU6$ OFFLINE SIM_UNDO_NEW
_SYSSMU7$ OFFLINE SIM_UNDO_NEW
_SYSSMU8$ OFFLINE SIM_UNDO_NEW
_SYSSMU9$ OFFLINE SIM_UNDO_NEW
_SYSSMU10$ OFFLINE SIM_UNDO_NEWSEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU20$ NEEDS RECOVERY SIM_UNDO
_SYSSMU21$ OFFLINE SIM_UNDO
_SYSSMU22$ OFFLINE SIM_UNDO
_SYSSMU23$ OFFLINE SIM_UNDO
_SYSSMU24$ OFFLINE SIM_UNDO已选择16行。SQL> show sgaTotal System Global Area 1267804344 bytes
Fixed Size 456888 bytes
Variable Size 1015021568 bytes
Database Buffers 251658240 bytes
Redo Buffers 667648 bytes
SQL> alter system set undo_management=manual scope=spfile;
alter system set undo_management=manual scope=spfile
*
ERROR 位于第 1 行:
ORA-32001: 已请求写入 SPFILE, 但是在启动时未指定 SPFILE
SQL> create undo tablespace UNDOTBS1
2 ;*
ERROR 位于第 2 行:
ORA-02199: 丢失 DATAFILE/TEMPFILE 子句
SQL> CREATE UNDO TABLESPACE UNDOTBS1
2 DATAFILE 'E:\TA090213data\UNDOTBS1.DBF' SIZE 1024M REUSE
3 AUTOEXTEND ON next 512M MAXSIZE 32767M,
4 'E:\TA090213data\UNDOTBS2.DBF' SIZE 1024M REUSE
5 AUTOEXTEND ON next 512M MAXSIZE 32767M
6 ;表空间已创建。SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1;
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1
*
ERROR 位于第 1 行:
ORA-02097: 无法修改参数，因为指定的值无效
ORA-30014: 此操作仅在自动撤消管理模式中才受支持