数据库是oracle 的,运行一段时间(大概一两周)后就不能访问。用plsql developer 工具连接时,工具就死在那里也没任何提示。数据库重启后,一切又正常了。请问这有可能是什么问题,怎么解决?各位兄弟有什么好的方法?
解决方案 »
- 如何根据时间区间的范围来查询date类型字段呢?
- Oracle 11g下扩展索引与普通索引联合查询的问题
- truncate后恢复问题
- proc中的SQL使用to_date与NLS_DATE_FORMAT关系
- 这样设计这四张表合理吗``请高人给指点下``
- 取表前5条记录,oracle的sql语句怎么写?
- 关于透明网关的问题,怎样设置SQL SERVER的连接?
- 请问BDE和ORACLE连接的问题,各位大虾,帮帮忙忙,急啊。。。。
- oracle的触发器,,,,
- 我在LINUX怎样可以打开DBA STUDIO
- XML数据文件怎么导入到Oracle数据库中?
- oracle 10G连ms server 2005数据库
------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群:62697716
下面两个目录下都有 alert.log,应该看那个呢?
D:\oracle\product\10.2.0\admin\mid\bdump
D:\oracle\product\10.2.0\db_1\RDBMS\trace
------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群:62697716
数据库是 28 号上午不能访问的 下面是27到28 的日志。能看出什么问题吗
Wed Jan 27 07:38:42 2010
Thread 1 advanced to log sequence 1026
Current log# 2 seq# 1026 mem# 0: D:\ORACLE\ORADATA\MID\REDO02.LOG
Wed Jan 27 07:43:53 2010
Thread 1 advanced to log sequence 1027
Current log# 3 seq# 1027 mem# 0: D:\ORACLE\ORADATA\MID\REDO03.LOG
Wed Jan 27 09:09:43 2010
Memory Notification: Library Cache Object loaded into SGA
Heap size 2237K exceeds notification threshold (2048K)
KGL object name :XDB.XDbD/PLZ01TcHgNAgAIIegtw== Wed Jan 27 09:09:43 2010
Memory Notification: Library Cache Object loaded into SGA
Heap size 2439K exceeds notification threshold (2048K)
Details in trace file d:\oracle\product\10.2.0\admin\mid\udump\mid_ora_1712.trc
KGL object name :XDB.XDZynXpO5DTv2jNMI2jLTZZA== Thu Jan 28 07:45:23 2010
Thread 1 advanced to log sequence 1028
Current log# 1 seq# 1028 mem# 0: D:\ORACLE\ORADATA\MID\REDO01.LOG
Thu Jan 28 07:48:00 2010
Thread 1 advanced to log sequence 1029
Current log# 2 seq# 1029 mem# 0: D:\ORACLE\ORADATA\MID\REDO02.LOG
Thu Jan 28 09:10:14 2010
Starting background process EMN0
EMN0 started with pid=149, OS id=3724
Thu Jan 28 09:10:14 2010
Shutting down instance: further logons disabled
Thu Jan 28 09:10:14 2010
Stopping background process QMNC
Thu Jan 28 09:10:14 2010
Stopping background process CJQ0
Thu Jan 28 09:10:15 2010
Stopping background process MMNL
Thu Jan 28 09:10:16 2010
Stopping background process MMON
Thu Jan 28 09:10:17 2010
Shutting down instance (immediate)
License high water = 132
Thu Jan 28 09:10:17 2010
Stopping Job queue slave processes
Thu Jan 28 09:10:17 2010
Job queue slave processes stopped
All dispatchers and shared servers shutdown
Thu Jan 28 09:10:29 2010
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
Thu Jan 28 09:10:42 2010
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
Thu Jan 28 09:10:54 2010
alter database close normal
Thu Jan 28 09:10:54 2010
SMON: disabling tx recovery
SMON: disabling cache recovery
Thu Jan 28 09:10:54 2010
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 1029
Successful close of redo thread 1
Thu Jan 28 09:10:54 2010
Completed: alter database close normal
Thu Jan 28 09:10:54 2010
alter database dismount
Completed: alter database dismount
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
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 = 150
sga_max_size = 1073741824
__shared_pool_size = 532676608
__large_pool_size = 4194304
__java_pool_size = 4194304
__streams_pool_size = 0
spfile = D:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILEMID.ORA
nls_language = SIMPLIFIED CHINESE
nls_territory = CHINA
sga_target = 935329792
control_files = D:\ORACLE\ORADATA\MID\CONTROL01.CTL, D:\ORACLE\ORADATA\MID\CONTROL02.CTL, D:\ORACLE\ORADATA\MID\CONTROL03.CTL
db_block_size = 8192
__db_cache_size = 385875968
compatible = 10.2.0.1.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = D:\oracle\product\10.2.0/flash_recovery_area
db_recovery_file_dest_size= 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=midXDB)
job_queue_processes = 10
audit_file_dest = D:\ORACLE\PRODUCT\10.2.0\ADMIN\MID\ADUMP
background_dump_dest = D:\ORACLE\PRODUCT\10.2.0\ADMIN\MID\BDUMP
user_dump_dest = D:\ORACLE\PRODUCT\10.2.0\ADMIN\MID\UDUMP
core_dump_dest = D:\ORACLE\PRODUCT\10.2.0\ADMIN\MID\CDUMP
db_name = mid
open_cursors = 300
pga_aggregate_target = 268435456
PMON started with pid=2, OS id=1860
PSP0 started with pid=3, OS id=5944
MMAN started with pid=4, OS id=3768
DBW0 started with pid=5, OS id=5108
LGWR started with pid=6, OS id=5364
CKPT started with pid=7, OS id=3348
SMON started with pid=8, OS id=2444
RECO started with pid=9, OS id=5488
CJQ0 started with pid=10, OS id=5924
MMON started with pid=11, OS id=5544
MMNL started with pid=12, OS id=5716
Thu Jan 28 09:10:59 2010
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
Thu Jan 28 09:10:59 2010
alter database mount exclusive
Thu Jan 28 09:11:03 2010
Setting recovery target incarnation to 2
Thu Jan 28 09:11:03 2010
Successful mount of redo thread 1, with mount id 2738119715
Thu Jan 28 09:11:03 2010
Database mounted in Exclusive Mode
Completed: alter database mount exclusive
Thu Jan 28 09:11:03 2010
alter database open
Thu Jan 28 09:11:03 2010
Thread 1 opened at log sequence 1029
Current log# 2 seq# 1029 mem# 0: D:\ORACLE\ORADATA\MID\REDO02.LOG
Successful open of redo thread 1
Thu Jan 28 09:11:03 2010
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Jan 28 09:11:03 2010
SMON: enabling cache recovery
Thu Jan 28 09:11:04 2010
Successfully onlined Undo Tablespace 1.
Thu Jan 28 09:11:04 2010
SMON: enabling tx recovery
Thu Jan 28 09:11:04 2010
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=16, OS id=5180
Thu Jan 28 09:11:06 2010
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Thu Jan 28 09:11:06 2010
Completed: alter database open
Thu Jan 28 09:16:55 2010
Memory Notification: Library Cache Object loaded into SGA
Heap size 2230K exceeds notification threshold (2048K)
KGL object name :XDB.XDbD/PLZ01TcHgNAgAIIegtw==
alert log 没有看出什么问题1. 数据库有没有设置连接的限制,比如最大只能连多少个session. 检查下数据库的参数看看。2. 连不上时数据库进程有没有挂死?3. 数据库现在性能怎么样?做个AWR分析下看看。 ------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群:62697716
1、
select limit_value from v$resource_limit where resource_name='sessions';
170 个
2、没有看,也不会看
3、awr分析有什么作用,是不是可以查看sql语句的执行效率以及对数据库性能的影响什么的?
没有弄过数据库管理这块,谢谢您的耐心讲解
03-2月 -2010 09:37:17 * (CONNECT_DATA=(SERVICE_NAME=mid)(CID=(PROGRAM=oracle)(HOST=mmyxdbsvr1)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.161)(PORT=48724)) * establish * mid * 12518
TNS-12518: TNS: 监听程序无法分发客户机连接
TNS-12560: TNS: 协议适配器错误
03-2月 -2010 09:38:39 * ping * 0
03-2月 -2010 09:38:39 * (CONNECT_DATA=(SID=mis)(CID=(PROGRAM=D:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe)(HOST=YXJKSVR)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.169)(PORT=3484)) * establish * mis * 0
03-2月 -2010 09:38:39 * ping * 0
03-2月 -2010 09:38:39 * (CONNECT_DATA=(SID=ydmis)(CID=(PROGRAM=D:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe)(HOST=YXJKSVR)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.169)(PORT=3491)) * establish * ydmis * 0
03-2月 -2010 09:38:39 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=SYSTEM))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=yxjksvr)(PORT=1521)))(VERSION=169869568)) * status * 0
03-2月 -2010 09:38:39 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=SYSTEM))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=yxjksvr)(PORT=1521)))(VERSION=169869568)) * status * 0
03-2月 -2010 09:38:43 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=mis)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.169)(PORT=3500)) * establish * mis * 0
03-2月 -2010 09:38:44 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=ydmis)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.169)(PORT=3502)) * establish * ydmis * 0
03-2月 -2010 09:38:45 * service_update * mis * 0
03-2月 -2010 09:38:45 * service_update * ydmis * 0
03-2月 -2010 09:40:39 * (CONNECT_DATA=(SERVICE_NAME=MID)(CID=(PROGRAM=C:\cloudnzzx\ToMisN.exe)(HOST=JB-2)(USER=jkkj))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.127.90.216)(PORT=1448)) * establish * MID * 12518
TNS-12518: TNS: 监听程序无法分发客户机连接
TNS-12560: TNS: 协议适配器错误
03-2月 -2010 09:42:01 * (CONNECT_DATA=(SERVICE_NAME=MID)(CID=(PROGRAM=C:\cloudnzzx\ToMisN.exe)(HOST=JB-2)(USER=jkkj))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.127.90.216)(PORT=1449)) * establish * MID * 12518
TNS-12518: TNS: 监听程序无法分发客户机连接
TNS-12560: TNS: 协议适配器错误
03-2月 -2010 09:43:24 * (CONNECT_DATA=(SERVICE_NAME=MID)(CID=(PROGRAM=C:\cloudnzzx\ToMisN.exe)(HOST=JB-2)(USER=jkkj))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.127.90.216)(PORT=1451)) * establish * MID * 12518
TNS-12518: TNS: 监听程序无法分发客户机连接
TNS-12560: TNS: 协议适配器错误
03-2月 -2010 09:44:46 * (CONNECT_DATA=(SID=mis)(CID=(PROGRAM=D:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe)(HOST=YXJKSVR)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.169)(PORT=3536)) * establish * mis * 0
03-2月 -2010 09:44:46 * service_update * ydmis * 0
03-2月 -2010 09:44:46 * service_update * mis * 0
03-2月 -2010 09:44:46 * ping * 0
03-2月 -2010 09:44:46 * (CONNECT_DATA=(SID=mis)(CID=(PROGRAM=D:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe)(HOST=YXJKSVR)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.169)(PORT=3548)) * establish * mis * 0
03-2月 -2010 09:44:46 * ping * 0
03-2月 -2010 09:44:46 * (CONNECT_DATA=(SID=ydmis)(CID=(PROGRAM=D:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe)(HOST=YXJKSVR)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.169)(PORT=3555)) * establish * ydmis * 0
03-2月 -2010 09:44:46 * (CONNECT_DATA=(SID=mis)(CID=(PROGRAM=D:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe)(HOST=YXJKSVR)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.169)(PORT=3557)) * establish * mis * 0
03-2月 -2010 09:44:46 * (CONNECT_DATA=(SERVICE_NAME=mid)(CID=(PROGRAM=oracle)(HOST=mmyxdbsvr1)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.161)(PORT=48754)) * establish * mid * 12518
TNS-12518: TNS: 监听程序无法分发客户机连接
TNS-12560: TNS: 协议适配器错误
今天数据库有挂了。。
sqlnet.log 没日志listener.log 日志如下,能看出什么问题吗03-2月 -2010 09:37:17 * (CONNECT_DATA=(SERVICE_NAME=mid)(CID=(PROGRAM=oracle)(HOST=mmyxdbsvr1)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.161)(PORT=48724)) * establish * mid * 12518
TNS-12518: TNS: 监听程序无法分发客户机连接
TNS-12560: TNS: 协议适配器错误
03-2月 -2010 09:38:39 * ping * 0
03-2月 -2010 09:38:39 * (CONNECT_DATA=(SID=mis)(CID=(PROGRAM=D:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe)(HOST=YXJKSVR)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.169)(PORT=3484)) * establish * mis * 0
03-2月 -2010 09:38:39 * ping * 0
03-2月 -2010 09:38:39 * (CONNECT_DATA=(SID=ydmis)(CID=(PROGRAM=D:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe)(HOST=YXJKSVR)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.169)(PORT=3491)) * establish * ydmis * 0
03-2月 -2010 09:38:39 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=SYSTEM))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=yxjksvr)(PORT=1521)))(VERSION=169869568)) * status * 0
03-2月 -2010 09:38:39 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=SYSTEM))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=yxjksvr)(PORT=1521)))(VERSION=169869568)) * status * 0
03-2月 -2010 09:38:43 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=mis)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.169)(PORT=3500)) * establish * mis * 0
03-2月 -2010 09:38:44 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=ydmis)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.169)(PORT=3502)) * establish * ydmis * 0
03-2月 -2010 09:38:45 * service_update * mis * 0
03-2月 -2010 09:38:45 * service_update * ydmis * 0
03-2月 -2010 09:40:39 * (CONNECT_DATA=(SERVICE_NAME=MID)(CID=(PROGRAM=C:\cloudnzzx\ToMisN.exe)(HOST=JB-2)(USER=jkkj))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.127.90.216)(PORT=1448)) * establish * MID * 12518
TNS-12518: TNS: 监听程序无法分发客户机连接
TNS-12560: TNS: 协议适配器错误
03-2月 -2010 09:42:01 * (CONNECT_DATA=(SERVICE_NAME=MID)(CID=(PROGRAM=C:\cloudnzzx\ToMisN.exe)(HOST=JB-2)(USER=jkkj))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.127.90.216)(PORT=1449)) * establish * MID * 12518
TNS-12518: TNS: 监听程序无法分发客户机连接
TNS-12560: TNS: 协议适配器错误
03-2月 -2010 09:43:24 * (CONNECT_DATA=(SERVICE_NAME=MID)(CID=(PROGRAM=C:\cloudnzzx\ToMisN.exe)(HOST=JB-2)(USER=jkkj))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.127.90.216)(PORT=1451)) * establish * MID * 12518
TNS-12518: TNS: 监听程序无法分发客户机连接
TNS-12560: TNS: 协议适配器错误
03-2月 -2010 09:44:46 * (CONNECT_DATA=(SID=mis)(CID=(PROGRAM=D:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe)(HOST=YXJKSVR)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.169)(PORT=3536)) * establish * mis * 0
03-2月 -2010 09:44:46 * service_update * ydmis * 0
03-2月 -2010 09:44:46 * service_update * mis * 0
03-2月 -2010 09:44:46 * ping * 0
03-2月 -2010 09:44:46 * (CONNECT_DATA=(SID=mis)(CID=(PROGRAM=D:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe)(HOST=YXJKSVR)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.169)(PORT=3548)) * establish * mis * 0
03-2月 -2010 09:44:46 * ping * 0
03-2月 -2010 09:44:46 * (CONNECT_DATA=(SID=ydmis)(CID=(PROGRAM=D:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe)(HOST=YXJKSVR)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.169)(PORT=3555)) * establish * ydmis * 0
03-2月 -2010 09:44:46 * (CONNECT_DATA=(SID=mis)(CID=(PROGRAM=D:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe)(HOST=YXJKSVR)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.169)(PORT=3557)) * establish * mis * 0
03-2月 -2010 09:44:46 * (CONNECT_DATA=(SERVICE_NAME=mid)(CID=(PROGRAM=oracle)(HOST=mmyxdbsvr1)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.150.240.161)(PORT=48754)) * establish * mid * 12518
TNS-12518: TNS: 监听程序无法分发客户机连接
TNS-12560: TNS: 协议适配器错误
首先,从网络方面找原因,但是看你的描述不是这个原因。
其次,连不上报什么错?
再次,报“监听程序无法分发客户机连接”一般是你建立的数据库的服务没有启动;
进行以下几步查证:
1.查看listener.ora中配置是不是你要链接的服务器;
2.查看tnsnames.ora中配置是否正确;
3.如果正确,在cmd中以下命令启动:
net start oracleservice+(实例名);
最后有个办法,就是做个windows任务计划,让他每天早上重启一下。
我目前就是重启解决这个问题的。至于你说的定时任务,在没解决前确实是个不错的选择。第1、2点,应该不是问题,因为服务重启后就正常了。。
其次,连不上报什么错?
plsql developer 和我们的应用服务都死在那里没反应。alert.log 也没什么异常信息。只有listener.log 有上面的信息