正式服务器,昨天晚上,到今天下午,分别 1.昨晚10点左右;2.今早10点左右;3.今下午15:47左右,断掉3次,重启服务又好了。alert..log中没有什么错误,process数和session数都是够的,业务量也没有井喷,应用稳定运行了半年,没有修改过,应该也和应用连接是否关闭无关(应用中连接都是用完了会关的)磁盘空间也够,监听文件9.6M也不算大,自动归档也开了,也没有中毒,求高手指点。
SQL> show parameter processesNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 3
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 900SQL> select count(*) from v$process; COUNT(*)
----------
222SQL> 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 995
shared_server_sessions integer SQL> select count(*) from v$session; COUNT(*)
----------
168SQL> show parameter cursorNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 20
SQL> select count(*) from v$open_cursor; COUNT(*)
----------
2411 //2411 是累计所有用户的,那个一般问题不大
SQL> select name,log_mode from v$database;NAME LOG_MODE
--------- ------------
AAAAAA ARCHIVELOGSQL> alert_...log(这里面三段SYS auditing is disabled都是一样的我截图下来了在下面):
Dump file d:\oracle\rdbms\trace\alert_orclyxkj.log
Tue Feb 19 15:44:49 2013
ORACLE V10.2.0.1.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 24 - type 8664, 2 Physical Cores
Process Affinity : 0x0000000000000000
Memory (Avail/Total): Ph:22949M/24530M, Ph+PgF:25018M/25803M
Tue Feb 19 15:44:49 2013
Adjusting the default value of parameter parallel_max_servers
from 480 to 135 due to the value of parameter processes (150)
Tue Feb 19 15:44:49 2013
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Dump file d:\oracle\rdbms\trace\alert_orclyxkj.log
Wed Feb 20 11:01:22 2013
ORACLE V10.2.0.1.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 24 - type 8664, 2 Physical Cores
Process Affinity : 0x0000000000000000
Memory (Avail/Total): Ph:23417M/24530M, Ph+PgF:24659M/25803M
Wed Feb 20 11:01:22 2013
Adjusting the default value of parameter parallel_max_servers
from 480 to 135 due to the value of parameter processes (150)
Wed Feb 20 11:01:22 2013
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Dump file d:\oracle\rdbms\trace\alert_orclyxkj.log
Thu Feb 21 20:22:16 2013
ORACLE V10.2.0.1.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 24 - type 8664, 2 Physical Cores
Process Affinity : 0x0000000000000000
Memory (Avail/Total): Ph:23435M/24530M, Ph+PgF:24667M/25803M
Thu Feb 21 20:22:16 2013
Adjusting the default value of parameter parallel_max_servers
from 480 to 135 due to the value of parameter processes (150)
Thu Feb 21 20:22:16 2013
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
oracleconnctionsession
SQL> show parameter processesNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 3
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 900SQL> select count(*) from v$process; COUNT(*)
----------
222SQL> 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 995
shared_server_sessions integer SQL> select count(*) from v$session; COUNT(*)
----------
168SQL> show parameter cursorNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 20
SQL> select count(*) from v$open_cursor; COUNT(*)
----------
2411 //2411 是累计所有用户的,那个一般问题不大
SQL> select name,log_mode from v$database;NAME LOG_MODE
--------- ------------
AAAAAA ARCHIVELOGSQL> alert_...log(这里面三段SYS auditing is disabled都是一样的我截图下来了在下面):
Dump file d:\oracle\rdbms\trace\alert_orclyxkj.log
Tue Feb 19 15:44:49 2013
ORACLE V10.2.0.1.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 24 - type 8664, 2 Physical Cores
Process Affinity : 0x0000000000000000
Memory (Avail/Total): Ph:22949M/24530M, Ph+PgF:25018M/25803M
Tue Feb 19 15:44:49 2013
Adjusting the default value of parameter parallel_max_servers
from 480 to 135 due to the value of parameter processes (150)
Tue Feb 19 15:44:49 2013
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Dump file d:\oracle\rdbms\trace\alert_orclyxkj.log
Wed Feb 20 11:01:22 2013
ORACLE V10.2.0.1.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 24 - type 8664, 2 Physical Cores
Process Affinity : 0x0000000000000000
Memory (Avail/Total): Ph:23417M/24530M, Ph+PgF:24659M/25803M
Wed Feb 20 11:01:22 2013
Adjusting the default value of parameter parallel_max_servers
from 480 to 135 due to the value of parameter processes (150)
Wed Feb 20 11:01:22 2013
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Dump file d:\oracle\rdbms\trace\alert_orclyxkj.log
Thu Feb 21 20:22:16 2013
ORACLE V10.2.0.1.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 24 - type 8664, 2 Physical Cores
Process Affinity : 0x0000000000000000
Memory (Avail/Total): Ph:23435M/24530M, Ph+PgF:24667M/25803M
Thu Feb 21 20:22:16 2013
Adjusting the default value of parameter parallel_max_servers
from 480 to 135 due to the value of parameter processes (150)
Thu Feb 21 20:22:16 2013
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
oracleconnctionsession
Tue Feb 19 15:44:49 2013
Starting ORACLE instance (normal)
Wed Feb 20 11:01:22 2013
Starting ORACLE instance (normal)
Thu Feb 21 20:22:16 2013
Starting ORACLE instance (normal)
==================================
这是手工重启服务的时间吗?远程断掉时,服务器本地能否登陆?
但是查询任何东西,比如process数和session数和某个表的数据,都会报 no logged in ;
TNS-12519: TNS: 没有找到适用的服务处理程序
20-3月 -2013 23:35:45 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orclyxkj)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.81)(PORT=2515)) * establish * orclyxkj * 12516
TNS-12516: TNS: 监听程序找不到符合协议堆栈要求的可用处理程序
20-3月 -2013 23:35:45 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=XX)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.81)(PORT=2516)) * establish * orclyxkj * 12516
TNS-12516: TNS: 监听程序找不到符合协议堆栈要求的可用处理程序
20-3月 -2013 23:35:45 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=XX)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.81)(PORT=2517)) * establish * orclyxkj * 12516
TNS-12516: TNS: 监听程序找不到符合协议堆栈要求的可用处理程序
20-3月 -2013 23:35:45 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orclyxkj)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.81)(PORT=2519)) * establish * orclyxkj * 12516
TNS-12516: TNS: 监听程序找不到符合协议堆栈要求的可用处理程序
20-3月 -2013 23:35:45 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=XX)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.81)(PORT=2520)) * establish * orclyxkj * 12516
TNS-12516: TNS: 监听程序找不到符合协议堆栈要求的可用处理程序
20-3月 -2013 23:35:45 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orclyxkj)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.81)(PORT=2521)) * establish * orclyxkj * 12516
TNS-12516: TNS: 监听程序找不到符合协议堆栈要求的可用处理程序
20-3月 -2013 23:35:45 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orclyxkj)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.81)(PORT=2523)) * establish * orclyxkj * 12516
TNS-12516: TNS: 监听程序找不到符合协议堆栈要求的可用处理程序
20-3月 -2013 23:35:45 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=XX)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.81)(PORT=2524)) * establish * orclyxkj * 12516
TNS-12516: TNS: 监听程序找不到符合协议堆栈要求的可用处理程序
20-3月 -2013 23:35:45 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=XX)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.81)(PORT=2525)) * establish * orclyxkj * 12516
TNS-12516: TNS: 监听程序找不到符合协议堆栈要求的可用处理程序
20-3月 -2013 23:35:48 * (CONNECT_DATA=(SERVICE_NAME=orclyxkj)(CID=(PROGRAM=D:\XX\XX\XX.exe)(HOST=WWW-36E1070A7C4)(USER=XX))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.89)(PORT=XX)) * establish * XX * 12516
TNS-12516: TNS: 监听程序找不到符合协议堆栈要求的可用处理程序