数据库经常无缘无故失去相应 操作系统为windows2003,数据库访问量非常小,一个月有一个用户访问大概7、8次,但是不知道为什么最近经常出现无法登录数据库,不管是使用plsql还是Enterprise Manager Console登录都是长时间失去相应,没有任何提示错误信息。请问各位这种情况应该从什么地方入手解决问题呢?是查询数据库日志吗?本人菜菜鸟,希望各位能解答得详细些,谢谢 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 经常出现无法登陆数据库的情况???那偶尔还是可以登陆的咯你的数据库是什么版本的,把信息说清楚点可以吗?看一下警告日志和链接日志吧警告日志路径类似:E:\oracle\product\10.2.0\admin\orcl\bdump\alert_orcl.log链接日志:E:\oracle\product\10.2.0\db_1\NETWORK\log\listener.log把相应的错误信息贴出来吧 1、检查网络是否有丢包问题2、如果网络没有问题,检查警告日志文件,看有没有相应的报错信息3、如果警告日志没有报错,那么在有客户机出现无响应故障时,登录数据库服务器,看使用专用服务器连接数据库sqlplus system/syspass是否能成功?4、如果在数据库服务器上能使用专用服务器连接也不能连接数据库,那么可能数据库可能工作不正常,检查数据库进程,以及cpu、io、网络等方面资源使用情况。5、如果使用专用服务器可能成功连接数据库,那么你的客户机是否使用了共享服务器,检查共享服务器是否存在排队情况:select * from v$queue;本人曾遇到类似问题,客户端登录长时间无响应,即不报错也登不进去。结果就是因为有少数客户在运行大型查询导致共享服务器太忙,没有多余的共享服务器进程供新发起的客户端连接。 感谢各位的热心,由于不是我公司的服务器,所以我还无法经常上去查看. 首先应该不是网络问题,我曾在服务器本身进行登录,依然会无响应. 其次应该不是在做大型查询而导致无响应,因为这个服务器提供的服务只有一个人使用,我在登录的时候使用者是不会再用的,后台也没有什么大型的查询. 最后日志方面,我曾经看过listener.log,没有看到任何不正常的信息,只是就在某一个时刻突然就没有记录了,一直持续到我们重启服务器才开始再次记录.再有机会的话我会登录服务器取一下alert_orcl.log记录.谢谢大家. 日志拿来了:Dump file s:\ora\admin\jkflora\bdump\alert_jkflora.logMon May 24 09:39:17 2010ORACLE V9.2.0.1.0 - Production vsnsta=0vsnsql=12 vsnxtr=3Windows 2000 Version 5.2 , CPU type 586Mon May 24 09:39:17 2010Starting ORACLE instance (normal)LICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0SCN scheme 2Using log_archive_dest parameter default valueLICENSE_MAX_USERS = 0SYS 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 = 201326592 large_pool_size = 8388608 java_pool_size = 33554432 control_files = s:\ora\oradata\jkflora\CONTROL01.CTL, s:\ora\oradata\jkflora\CONTROL02.CTL, s:\ora\oradata\jkflora\CONTROL03.CTL db_block_size = 8192 db_cache_size = 603979776 compatible = 9.2.0.0.0 db_file_multiblock_read_count= 32 fast_start_mttr_target = 300 undo_management = AUTO undo_tablespace = UNDOTBS1 undo_retention = 10800 remote_login_passwordfile= EXCLUSIVE db_domain = instance_name = jkflora dispatchers = (PROTOCOL=TCP) (SERVICE=jkfloraXDB), (PROTOCOL=TCP) local_listener = (ADDRESS=(PROTOCOL=TCP)(HOST=135.192.9.27)(PORT=1521)) job_queue_processes = 10 hash_join_enabled = TRUE hash_area_size = 1048576 background_dump_dest = s:\ora\admin\jkflora\bdump user_dump_dest = s:\ora\admin\jkflora\udump core_dump_dest = s:\ora\admin\jkflora\cdump sort_area_size = 1048576 db_name = jkflora open_cursors = 300 star_transformation_enabled= TRUE query_rewrite_enabled = TRUE pga_aggregate_target = 500000000 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 May 24 09:39:20 2010starting up 1 shared server(s) ...starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...Mon May 24 09:39:21 2010alter database "JKFLORA" mount exclusiveMon May 24 09:39:28 2010Successful mount of redo thread 1, with mount id 2140946955.Mon May 24 09:39:28 2010Database mounted in Exclusive Mode.Completed: alter database "JKFLORA" mount exclusiveMon May 24 09:39:28 2010alter database openMon May 24 09:39:28 2010Beginning crash recovery of 1 threadsMon May 24 09:39:28 2010Started first pass scanMon May 24 09:39:28 2010Completed first pass scan 0 redo blocks read, 0 data blocks need recoveryMon May 24 09:39:28 2010Started recovery at Thread 1: logseq 1897, block 204799, scn 0.453236047Recovery of Online Redo Log: Thread 1 Group 3 Seq 1897 Reading mem 0 Mem# 0 errs 0: S:\ORA\ORADATA\JKFLORA\REDO03.LOGMon May 24 09:39:28 2010Ended recovery at Thread 1: logseq 1897, block 204799, scn 0.453256048 0 data blocks read, 0 data blocks written, 0 redo blocks readCrash recovery completed successfullyMon May 24 09:39:28 2010LGWR: Primary database is in CLUSTER CONSISTENT modeMon May 24 09:39:28 2010ARCH: Evaluating archive log 1 thread 1 sequence 1895ARCH: Beginning to archive log 1 thread 1 sequence 1895Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\ORA92\RDBMS\ARC01895.001'ARCH: Completed archiving log 1 thread 1 sequence 1895Mon May 24 09:39:32 2010LGWR: Primary database is in CLUSTER CONSISTENT modeThread 1 advanced to log sequence 1898Thread 1 opened at log sequence 1898 Current log# 1 seq# 1898 mem# 0: S:\ORA\ORADATA\JKFLORA\REDO01.LOGSuccessful open of redo thread 1.Mon May 24 09:39:32 2010SMON: enabling cache recoveryMon May 24 09:39:32 2010Undo Segment 1 OnlinedUndo Segment 2 OnlinedUndo Segment 3 OnlinedUndo Segment 4 OnlinedUndo Segment 5 OnlinedUndo Segment 6 OnlinedUndo Segment 7 OnlinedUndo Segment 8 OnlinedUndo Segment 9 OnlinedUndo Segment 10 OnlinedSuccessfully onlined Undo Tablespace 1.Mon May 24 09:39:32 2010SMON: enabling tx recoveryMon May 24 09:39:35 2010Database Characterset is ZHS16GBKreplication_dependency_tracking turned off (no async multimaster replication found)Completed: alter database openMon May 24 09:39:57 2010Errors in file s:\ora\admin\jkflora\udump\jkflora_ora_2660.trc:ORA-00604: 递归 SQL 层 1 出现错误ORA-01401: 插入的值对于列过大ORA-06512: 在line 2Mon May 24 09:39:58 2010Errors in file s:\ora\admin\jkflora\udump\jkflora_ora_5552.trc:ORA-00604: 递归 SQL 层 1 出现错误ORA-01401: 插入的值对于列过大ORA-06512: 在line 2Mon May 24 09:40:35 2010Errors in file s:\ora\admin\jkflora\udump\jkflora_ora_5144.trc:ORA-00604: 递归 SQL 层 1 出现错误ORA-01401: 插入的值对于列过大ORA-06512: 在line 2Mon May 24 09:57:37 2010Errors in file s:\ora\admin\jkflora\udump\jkflora_ora_4884.trc:ORA-00604: 递归 SQL 层 1 出现错误ORA-01401: 插入的值对于列过大ORA-06512: 在line 2Mon May 24 09:57:37 2010Errors in file s:\ora\admin\jkflora\udump\jkflora_ora_5996.trc:ORA-00604: 递归 SQL 层 1 出现错误ORA-01401: 插入的值对于列过大ORA-06512: 在line 2Mon May 24 09:57:55 2010Errors in file s:\ora\admin\jkflora\udump\jkflora_ora_3420.trc:ORA-00604: 递归 SQL 层 1 出现错误ORA-01401: 插入的值对于列过大ORA-06512: 在line 2Tue May 25 11:14:12 2010Thread 1 cannot allocate new log, sequence 1899All online logs needed archiving Current log# 1 seq# 1898 mem# 0: S:\ORA\ORADATA\JKFLORA\REDO01.LOGWed May 26 08:22:56 2010Errors in file s:\ora\admin\jkflora\udump\jkflora_ora_2736.trc:ORA-00604: 递归 SQL 层 1 出现错误ORA-03113: 通信通道的文件结束ORA-06512: 在line 2似乎是在写入表的时候出现问题了,请各位帮忙分析一下。 你的sql报错了,现在已经不仅仅是性能问题了先找到你的insert语句吧,解决了这个问题之后再观察 insert语句怎么查呢?是在jkflora_ora_2736.trc中吗? 先到\ora\admin\jkflora\udump\看一下跟踪日志信息吧错误信息很明显了而且你似乎做了归档日志 Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\ORA92\RDBMS\ARC01895.001'也可以用Logminer来查看归档日志信息~ 如何用imp还原数据? isqlplussvc.exe-应用程序错误 求教SQL语句的写法 数据库统计问题 江湖救急呀,有几个问题,请大家帮忙解答一下 如何使这个delete语句用到索引 日期运算 紧急求助一个棘手的SQL语句问题 怎样把某字段重复的记录删除只留一条 我用PL/SQL DEVELOPER 调试存储过程怎么报错啊? sql INSERT 问题 ORacle9i 的数据如何导入到Sql2005, 没有分了 求大家帮忙了
你的数据库是什么版本的,把信息说清楚点可以吗?
看一下警告日志和链接日志吧
警告日志路径类似:E:\oracle\product\10.2.0\admin\orcl\bdump\alert_orcl.log
链接日志:E:\oracle\product\10.2.0\db_1\NETWORK\log\listener.log把相应的错误信息贴出来吧
2、如果网络没有问题,检查警告日志文件,看有没有相应的报错信息
3、如果警告日志没有报错,那么在有客户机出现无响应故障时,登录数据库服务器,看使用专用服务器连接数据库sqlplus system/syspass是否能成功?
4、如果在数据库服务器上能使用专用服务器连接也不能连接数据库,那么可能数据库可能工作不正常,检查数据库进程,以及cpu、io、网络等方面资源使用情况。
5、如果使用专用服务器可能成功连接数据库,那么你的客户机是否使用了共享服务器,检查共享服务器是否存在排队情况:select * from v$queue;本人曾遇到类似问题,客户端登录长时间无响应,即不报错也登不进去。结果就是因为有少数客户在运行大型查询导致共享服务器太忙,没有多余的共享服务器进程供新发起的客户端连接。
首先应该不是网络问题,我曾在服务器本身进行登录,依然会无响应.
其次应该不是在做大型查询而导致无响应,因为这个服务器提供的服务只有一个人使用,我在登录的时候使用者是不会再用的,后台也没有什么大型的查询.
最后日志方面,我曾经看过listener.log,没有看到任何不正常的信息,只是就在某一个时刻突然就没有记录了,一直持续到我们重启服务器才开始再次记录.再有机会的话我会登录服务器取一下alert_orcl.log记录.谢谢大家.
Mon May 24 09:39:17 2010
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 , CPU type 586
Mon May 24 09:39:17 2010
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
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 = 201326592
large_pool_size = 8388608
java_pool_size = 33554432
control_files = s:\ora\oradata\jkflora\CONTROL01.CTL, s:\ora\oradata\jkflora\CONTROL02.CTL, s:\ora\oradata\jkflora\CONTROL03.CTL
db_block_size = 8192
db_cache_size = 603979776
compatible = 9.2.0.0.0
db_file_multiblock_read_count= 32
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = jkflora
dispatchers = (PROTOCOL=TCP) (SERVICE=jkfloraXDB), (PROTOCOL=TCP)
local_listener = (ADDRESS=(PROTOCOL=TCP)(HOST=135.192.9.27)(PORT=1521))
job_queue_processes = 10
hash_join_enabled = TRUE
hash_area_size = 1048576
background_dump_dest = s:\ora\admin\jkflora\bdump
user_dump_dest = s:\ora\admin\jkflora\udump
core_dump_dest = s:\ora\admin\jkflora\cdump
sort_area_size = 1048576
db_name = jkflora
open_cursors = 300
star_transformation_enabled= TRUE
query_rewrite_enabled = TRUE
pga_aggregate_target = 500000000
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 May 24 09:39:20 2010
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Mon May 24 09:39:21 2010
alter database "JKFLORA" mount exclusive
Mon May 24 09:39:28 2010
Successful mount of redo thread 1, with mount id 2140946955.
Mon May 24 09:39:28 2010
Database mounted in Exclusive Mode.
Completed: alter database "JKFLORA" mount exclusive
Mon May 24 09:39:28 2010
alter database open
Mon May 24 09:39:28 2010
Beginning crash recovery of 1 threads
Mon May 24 09:39:28 2010
Started first pass scan
Mon May 24 09:39:28 2010
Completed first pass scan
0 redo blocks read, 0 data blocks need recovery
Mon May 24 09:39:28 2010
Started recovery at
Thread 1: logseq 1897, block 204799, scn 0.453236047
Recovery of Online Redo Log: Thread 1 Group 3 Seq 1897 Reading mem 0
Mem# 0 errs 0: S:\ORA\ORADATA\JKFLORA\REDO03.LOG
Mon May 24 09:39:28 2010
Ended recovery at
Thread 1: logseq 1897, block 204799, scn 0.453256048
0 data blocks read, 0 data blocks written, 0 redo blocks read
Crash recovery completed successfully
Mon May 24 09:39:28 2010
LGWR: Primary database is in CLUSTER CONSISTENT mode
Mon May 24 09:39:28 2010
ARCH: Evaluating archive log 1 thread 1 sequence 1895
ARCH: Beginning to archive log 1 thread 1 sequence 1895
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\ORA92\RDBMS\ARC01895.001'
ARCH: Completed archiving log 1 thread 1 sequence 1895
Mon May 24 09:39:32 2010
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 1898
Thread 1 opened at log sequence 1898
Current log# 1 seq# 1898 mem# 0: S:\ORA\ORADATA\JKFLORA\REDO01.LOG
Successful open of redo thread 1.
Mon May 24 09:39:32 2010
SMON: enabling cache recovery
Mon May 24 09:39:32 2010
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Mon May 24 09:39:32 2010
SMON: enabling tx recovery
Mon May 24 09:39:35 2010
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open
Mon May 24 09:39:57 2010
Errors in file s:\ora\admin\jkflora\udump\jkflora_ora_2660.trc:
ORA-00604: 递归 SQL 层 1 出现错误
ORA-01401: 插入的值对于列过大
ORA-06512: 在line 2Mon May 24 09:39:58 2010
Errors in file s:\ora\admin\jkflora\udump\jkflora_ora_5552.trc:
ORA-00604: 递归 SQL 层 1 出现错误
ORA-01401: 插入的值对于列过大
ORA-06512: 在line 2Mon May 24 09:40:35 2010
Errors in file s:\ora\admin\jkflora\udump\jkflora_ora_5144.trc:
ORA-00604: 递归 SQL 层 1 出现错误
ORA-01401: 插入的值对于列过大
ORA-06512: 在line 2Mon May 24 09:57:37 2010
Errors in file s:\ora\admin\jkflora\udump\jkflora_ora_4884.trc:
ORA-00604: 递归 SQL 层 1 出现错误
ORA-01401: 插入的值对于列过大
ORA-06512: 在line 2Mon May 24 09:57:37 2010
Errors in file s:\ora\admin\jkflora\udump\jkflora_ora_5996.trc:
ORA-00604: 递归 SQL 层 1 出现错误
ORA-01401: 插入的值对于列过大
ORA-06512: 在line 2Mon May 24 09:57:55 2010
Errors in file s:\ora\admin\jkflora\udump\jkflora_ora_3420.trc:
ORA-00604: 递归 SQL 层 1 出现错误
ORA-01401: 插入的值对于列过大
ORA-06512: 在line 2Tue May 25 11:14:12 2010
Thread 1 cannot allocate new log, sequence 1899
All online logs needed archiving
Current log# 1 seq# 1898 mem# 0: S:\ORA\ORADATA\JKFLORA\REDO01.LOG
Wed May 26 08:22:56 2010
Errors in file s:\ora\admin\jkflora\udump\jkflora_ora_2736.trc:
ORA-00604: 递归 SQL 层 1 出现错误
ORA-03113: 通信通道的文件结束
ORA-06512: 在line 2
似乎是在写入表的时候出现问题了,请各位帮忙分析一下。
错误信息很明显了而且你似乎做了归档日志
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\ORA92\RDBMS\ARC01895.001'
也可以用Logminer来查看归档日志信息~