症状:
---------
近期常遇到oracle数据库无法连接的问题,
今日又出现这种情况,
原有的数据库连接正常,即原有已建立好数据库连接的客户端应用程序能够正常运行,
但是建立新的连接时,无法建立,客户端应用程序呈假死状态,包括:sqlplus,toad,系统情况:
---------
目前使用该数据库的应用系统有两套:
1.BS程序,使用JBoss作服务,建立了最小50,最大100的连接池。
2.二层CS程序,程序启动建立数据库连接。正常情况下,查V$Session,有70多条记录
---------
近期常遇到oracle数据库无法连接的问题,
今日又出现这种情况,
原有的数据库连接正常,即原有已建立好数据库连接的客户端应用程序能够正常运行,
但是建立新的连接时,无法建立,客户端应用程序呈假死状态,包括:sqlplus,toad,系统情况:
---------
目前使用该数据库的应用系统有两套:
1.BS程序,使用JBoss作服务,建立了最小50,最大100的连接池。
2.二层CS程序,程序启动建立数据库连接。正常情况下,查V$Session,有70多条记录
11-9月 -2009 11:03:41 * (CONNECT_DATA=(SID=DOC)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.20.xxx.xxx)(PORT=1887)) * establish * DOC * 0
11-9月 -2009 11:03:41 * (CONNECT_DATA=(SID=DOC)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.20.xxx.xxx)(PORT=1888)) * establish * DOC * 0
11-9月 -2009 11:03:41 * (CONNECT_DATA=(SID=DOC)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.20.xxx.xxx)(PORT=1889)) * establish * DOC * 0
11-9月 -2009 11:03:41 * (CONNECT_DATA=(SID=DOC)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.20.xxx.xxx)(PORT=1890)) * establish * DOC * 0
11-9月 -2009 11:03:41 * service_update * doc * 0
11-9月 -2009 11:03:41 * (CONNECT_DATA=(SID=DOC)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.20.xxx.xxx)(PORT=1891)) * establish * DOC * 0
11-9月 -2009 11:03:41 * (CONNECT_DATA=(SID=DOC)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.20.xxx.xxx)(PORT=1892)) * establish * DOC * 0
processes=多少?
sql> show parameter
别外,把alert.log的信息也打出来看看
10g呀,用上面的方式查看一下你的parameter吧。把你的pga设置的大一些。如果sga设置的比较大的话,把sga设置的小一些,给pga腾一些地方出来。
==================================
show parameter sessionNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
session_cached_cursors integer 20
session_max_open_files integer 10
sessions integer 555
shared_server_sessions integer
SQL> show parameter processNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 500
SQL> select count(*) from v$process; COUNT(*)
----------
61SQL> select count(*) from v$session; COUNT(*)
----------
59
我自9月8日开始截取到9月11日数据库出现问题,重启了服务器的部分内容如下(9月7日重启了服务器):Tue Sep 08 05:04:16 2009
Thread 1 advanced to log sequence 901
Current log# 3 seq# 901 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\REDO03.LOG
Current log# 3 seq# 901 mem# 1: E:\ORADATA\DOC\REDO03B.LOG
Tue Sep 08 16:05:40 2009
Memory Notification: Library Cache Object loaded into SGA
Heap size 2187K exceeds notification threshold (2048K)
KGL object name :XDB.XDbD/PLZ01TcHgNAgAIIegtw== Tue Sep 08 16:05:41 2009
Memory Notification: Library Cache Object loaded into SGA
Heap size 2372K exceeds notification threshold (2048K)
Details in trace file d:\oracle\product\10.2.0\admin\doc\udump\doc_ora_4508.trc
KGL object name :XDB.XDZynXpO5DTv2jNMI2jLTZZA== Tue Sep 08 16:38:25 2009
Thread 1 advanced to log sequence 902
Current log# 1 seq# 902 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\REDO01.LOG
Current log# 1 seq# 902 mem# 1: E:\ORADATA\DOC\REDO01B.LOG
Tue Sep 08 22:15:38 2009
Thread 1 advanced to log sequence 903
Current log# 2 seq# 903 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\REDO02.LOG
Current log# 2 seq# 903 mem# 1: E:\ORADATA\DOC\REDO02B.LOG
Wed Sep 09 03:31:22 2009
Thread 1 advanced to log sequence 904
Current log# 3 seq# 904 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\REDO03.LOG
Current log# 3 seq# 904 mem# 1: E:\ORADATA\DOC\REDO03B.LOG
Thread 1 advanced to log sequence 905
Current log# 1 seq# 905 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\REDO01.LOG
Current log# 1 seq# 905 mem# 1: E:\ORADATA\DOC\REDO01B.LOG
Wed Sep 09 04:44:20 2009
Thread 1 advanced to log sequence 906
Current log# 2 seq# 906 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\REDO02.LOG
Current log# 2 seq# 906 mem# 1: E:\ORADATA\DOC\REDO02B.LOG
Thread 1 advanced to log sequence 907
Current log# 3 seq# 907 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\REDO03.LOG
Current log# 3 seq# 907 mem# 1: E:\ORADATA\DOC\REDO03B.LOG
Wed Sep 09 16:41:37 2009
Thread 1 advanced to log sequence 908
Current log# 1 seq# 908 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\REDO01.LOG
Current log# 1 seq# 908 mem# 1: E:\ORADATA\DOC\REDO01B.LOG
Wed Sep 09 22:00:57 2009
Thread 1 advanced to log sequence 909
Current log# 2 seq# 909 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\REDO02.LOG
Current log# 2 seq# 909 mem# 1: E:\ORADATA\DOC\REDO02B.LOG
Thu Sep 10 03:31:10 2009
Thread 1 advanced to log sequence 910
Current log# 3 seq# 910 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\REDO03.LOG
Current log# 3 seq# 910 mem# 1: E:\ORADATA\DOC\REDO03B.LOG
Thread 1 advanced to log sequence 911
Current log# 1 seq# 911 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\REDO01.LOG
Current log# 1 seq# 911 mem# 1: E:\ORADATA\DOC\REDO01B.LOG
Thu Sep 10 04:44:20 2009
Thread 1 advanced to log sequence 912
Current log# 2 seq# 912 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\REDO02.LOG
Current log# 2 seq# 912 mem# 1: E:\ORADATA\DOC\REDO02B.LOG
Thread 1 advanced to log sequence 913
Current log# 3 seq# 913 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\REDO03.LOG
Current log# 3 seq# 913 mem# 1: E:\ORADATA\DOC\REDO03B.LOG
Thu Sep 10 13:00:11 2009
Thread 1 advanced to log sequence 914
Current log# 1 seq# 914 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\REDO01.LOG
Current log# 1 seq# 914 mem# 1: E:\ORADATA\DOC\REDO01B.LOG
Thu Sep 10 22:00:25 2009
Thread 1 advanced to log sequence 915
Current log# 2 seq# 915 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\REDO02.LOG
Current log# 2 seq# 915 mem# 1: E:\ORADATA\DOC\REDO02B.LOG
Fri Sep 11 06:45:08 2009
Thread 1 advanced to log sequence 916
Current log# 3 seq# 916 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\REDO03.LOG
Current log# 3 seq# 916 mem# 1: E:\ORADATA\DOC\REDO03B.LOG
Dump file d:\oracle\product\10.2.0/admin/doc/bdump\alert_doc.log
Fri Sep 11 09:43:28 2009
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 2 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:3363M/3839M, Ph+PgF:5412M/5724M, VA:1927M/2047M
Fri Sep 11 09:43:28 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Autotune of undo retention is turned on.
IMODE=BR
ILAT =61
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
processes = 500
sessions = 555
sga_max_size = 1073741824
__shared_pool_size = 109051904
__large_pool_size = 8388608
__java_pool_size = 4194304
__streams_pool_size = 0
sga_target = 612368384
control_files = D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\CONTROL01.CTL, D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\CONTROL02.CTL, D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\CONTROL03.CTL, E:\ORADATA\DOC\CONTROL01.CTL, E:\ORADATA\DOC\CONTROL02.CTL, E:\ORADATA\DOC\CONTROL03.CTL
db_block_size = 8192
__db_cache_size = 482344960
compatible = 10.2.0.1.0
log_archive_dest = E:\arch
standby_archive_dest =
db_file_multiblock_read_count= 16
db_recovery_file_dest =
db_recovery_file_dest_size= 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=docXDB)
job_queue_processes = 10
audit_file_dest = D:\ORACLE\PRODUCT\10.2.0\ADMIN\DOC\ADUMP
background_dump_dest = D:\ORACLE\PRODUCT\10.2.0\ADMIN\DOC\BDUMP
user_dump_dest = D:\ORACLE\PRODUCT\10.2.0\ADMIN\DOC\UDUMP
core_dump_dest = D:\ORACLE\PRODUCT\10.2.0\ADMIN\DOC\CDUMP
db_name = doc
open_cursors = 300
pga_aggregate_target = 419430400
PMON started with pid=2, OS id=3728
MMAN started with pid=4, OS id=3736
DBW0 started with pid=5, OS id=3740
LGWR started with pid=6, OS id=3772
CKPT started with pid=7, OS id=3776
SMON started with pid=8, OS id=3780
RECO started with pid=9, OS id=3784
CJQ0 started with pid=10, OS id=3788
MMON started with pid=11, OS id=3796
MMNL started with pid=12, OS id=3800
Fri Sep 11 09:43:32 2009
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
PSP0 started with pid=3, OS id=3732
Fri Sep 11 09:43:33 2009
alter database mount exclusive
Fri Sep 11 09:43:38 2009
Setting recovery target incarnation to 2
Fri Sep 11 09:43:38 2009
Successful mount of redo thread 1, with mount id 3725883014
Fri Sep 11 09:43:38 2009
Database mounted in Exclusive Mode
Completed: alter database mount exclusive
Fri Sep 11 09:43:38 2009
alter database open
Fri Sep 11 09:43:38 2009
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Fri Sep 11 09:43:38 2009
Started redo scan
Fri Sep 11 09:43:38 2009
Completed redo scan
92 redo blocks read, 41 data blocks need recovery
Fri Sep 11 09:43:38 2009
Started redo application at
Thread 1: logseq 916, block 71589
Fri Sep 11 09:43:38 2009
Recovery of Online Redo Log: Thread 1 Group 3 Seq 916 Reading mem 0
Mem# 0 errs 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\REDO03.LOG
Mem# 1 errs 0: E:\ORADATA\DOC\REDO03B.LOG
Fri Sep 11 09:43:38 2009
Completed redo application
Fri Sep 11 09:43:39 2009
Completed crash recovery at
Thread 1: logseq 916, block 71681, scn 1387686081
41 data blocks read, 41 data blocks written, 92 redo blocks read
Fri Sep 11 09:43:39 2009
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=18, OS id=3972
Fri Sep 11 09:43:39 2009
ARC0: Archival started
ARC1: Archival started
ARC1 started with pid=19, OS id=3976
Fri Sep 11 09:43:40 2009
LGWR: STARTING ARCH PROCESSES COMPLETE
Thread 1 advanced to log sequence 917
Thread 1 opened at log sequence 917
Current log# 1 seq# 917 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOC\REDO01.LOG
Current log# 1 seq# 917 mem# 1: E:\ORADATA\DOC\REDO01B.LOG
Successful open of redo thread 1
Fri Sep 11 09:43:40 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Sep 11 09:43:40 2009
SMON: enabling cache recovery
Fri Sep 11 09:43:40 2009
ARC1: STARTING ARCH PROCESSES
Fri Sep 11 09:43:40 2009
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Fri Sep 11 09:43:40 2009
ARC2: Archival started
ARC1: STARTING ARCH PROCESSES COMPLETE
ARC2 started with pid=20, OS id=4092
Fri Sep 11 09:43:41 2009
ARC1: Becoming the heartbeat ARCH
Fri Sep 11 09:43:41 2009
Successfully onlined Undo Tablespace 1.
Fri Sep 11 09:43:41 2009
SMON: enabling tx recovery
Fri Sep 11 09:43:41 2009
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=21, OS id=196
Fri Sep 11 09:43:46 2009
Completed: alter database open
Fri Sep 11 09:52:00 2009
Shutting down archive processes
Fri Sep 11 09:52:05 2009
ARCH shutting down
ARC2: Archival stopped