oracle 817 SGA 性能调整求教！！！！

显示全部楼层 · 2010-10-8 09:28:52

问题一：
单位在用工作站170台左右，部分工作站工作时同时打开多个程序连接服务器数据库。每隔半月左右，客户工作站登录不了数据库，
已经登录的用户可以正常使用，登录不了的用户多次登录偶尔也还能登录成功，查看会话用户280个左右。
listener.log中报错信息如下：
04-JUN-2009 10:32:59 * (CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=obilling.exe)(HOST=SFC-4)(USER=Owner))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.31.4)(PORT=1405)) * establish * ORCL * 12500
TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceeded
TNS-12560: TNS

rotocol adapter error
TNS-00510: Internal limit restriction exceeded
32-bit Windows Error: 8: Exec format error
04-JUN-2009 10:32:59 * (CONNECT_DATA=(SERVICE_NAME=orcl)(CID=(PROGRAM=C:\nursws\nursws.exe)(HOST=N3K-1)(USER=nsk))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.43.1)(PORT=3116)) * establish * orcl * 12500
TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceeded
TNS-12560: TNS

rotocol adapter error
TNS-00510: Internal limit restriction exceeded
32-bit Windows Error: 8: Exec format error
问题二：
orclALRT.LOG日志中一直存在如下错误警告信息：
Fri Jun 26 11:43:45 2009
Thread 1 advanced to log sequence 145868
Current log# 2 seq# 145868 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO02.LOG
Current log# 2 seq# 145868 mem# 1: F:\ARCHIVE\REDO02.LOG
Fri Jun 26 11:43:45 2009
ARC0: Beginning to archive log# 1 seq# 145867
kcrrga: Warning.Log sequence in archive filename wrapped
to fix length as indicated by %S in LOG_ARCHIVE_FORMAT.
Old log archive with same name might be overwritten.
kcrrga: Warning.Log sequence in archive filename wrapped
to fix length as indicated by %S in LOG_ARCHIVE_FORMAT.
Old log archive with same name might be overwritten.
kcrrga: Warning.Log sequence in archive filename wrapped
to fix length as indicated by %S in LOG_ARCHIVE_FORMAT.
Old log archive with same name might be overwritten.
ARC0: Completed archiving log# 1 seq# 145867
服务器配置:win2003 server sp1 32位内存2G
数据库为：ORACLE8.1.7.0.0
各项参数如下：
SQL> select * from v$version;
BANNER
---------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0
PL/SQL Release 8.1.7.0.0 - Production
CORE8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - P
NLSRTL Version 3.4.1.0.0 - Production
SQL> show sga
//NAME
VALUE
//-------------------- ----------
//Fixed Size
75804---------> 74M
//Variable Size
89460736---------> 85M
//Database Buffers380583936---------> 363M
//Redo Buffers
77824---------> 78M
initorcl.ora配置
*************************************************
db_name = "orcl"
instance_name = orcl
service_names = orcl
db_files = 1024
control_files = ("D:\oracle\oradata\orcl\control01.ctl", "D:\archive\control01.ctl", "E:\archive\control01.ctl&quot

open_cursors = 300
max_enabled_roles = 120
db_file_multiblock_read_count = 32
db_block_buffers = 46458
shared_pool_size = 72236851
large_pool_size = 614400
java_pool_size = 0
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 830
parallel_max_servers = 5
log_buffer = 32768
max_dump_file_size = 10240
log_archive_start = true
log_archive_dest_1 = "location=E:\archive"
log_archive_dest_2 = "location=D:\archive"
log_archive_dest_3 = "location=F:\archive"
log_archive_format = A%S.ARC
global_names = true
background_dump_dest = D:\oracle\admin\orcl\bdump
user_dump_dest = D:\oracle\admin\orcl\udump
db_block_size = 8192
remote_login_passwordfile = exclusive
os_authent_prefix = ""
distributed_transactions = 500
compatible = 8.1.0
sort_area_size = 65536
sort_area_retained_size = 65536
**************************************************
问题一解决方案：
服务器内存增加至4G，
1.Data buffer 调整至1080M
将db_block_buffers=46458 调整为 db_block_buffers=138240
2.shared_pool_size 调整至400M
将shared_pool_size = 72236851 调整为shared_pool_size=400*1024*1024 bytes =419430400
3.Redo Log Buffer 调整至 240K
将log_buffer = 32768 调整为 log_buffer=240 * 1024 bytes= 245760
4.Large pool 调整至 1M
将large_pool_size = 614400 调整为 large_pool_size = 1*1024*1024 bytes= 1048576
5.Java pool 调整至 1M
将java_pool_size = 0 调整为java_pool_size= 1*1024*1024 bytes= 1048576
6.sort_area_size 调整至 240K
将sort_area_size = 65536 调整为 sort_area_size=240 * 1024 bytes= 245760
7.调整原则:
(1).SGA < 0.45*(OS RAM)
SGA=((db_block_buffers * block size)+(shared_pool_size+large_pool_size+java_pool_size+log_buffers)+1MB
SGA=1080M + (400M + 1M + 1M + 240K) + 1M = 1483.24M
(2).(SGA+ sort_area_size*sessions) < 物理内存(4G) * 70%
当前sessions=918
1483.24M + 0.234375*918 = 1699M < 4G * 70%
问题二解决方案：
将initorcl.ora配置文件中log_archive_format = A%S.ARC参数中的大S改为小写，
即log_archive_format = A%s.ARC
通过上述方法能否解决存在问题，请高手指点。

千问 · 2010-10-8 09:28:52

没有来解，自己来一下！

千问 · 2010-10-8 09:28:52

怎么没有人加应呀？？？

千问 · 2010-10-8 09:28:52

你的问题一，
OS 的 con-current 用户数到达了极限
OS 的用户同时在线用户参数要加大，或者看看有没有死联接 dead connection 占用了 threads.

千问 · 2010-10-8 09:28:52

第二个问题应该不算什么问题
817 sequence number 被重新使用。
不过你的 817 是什么 patch 都没打的，如果这样运行没什么问题也就罢了。但是实在应该打到最后一个 mini pack，好像是 4 还是 5

千问 · 2010-10-8 09:28:52

完全同意