ORA-00445 实例挂起

最新推荐文章于 2024-07-22 08:30:00 发布

1ihj2015

最新推荐文章于 2024-07-22 08:30:00 发布

阅读量3.9k

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle Troubleshooting

本文链接：https://blog.youkuaiyun.com/itdba/article/details/38707293

Oracle Troubleshooting 专栏收录该内容

37 篇文章

订阅专栏

本文详细分析了数据库性能问题，包括节点等待情况、进程阻塞原因、内存使用情况等，并通过重启验证了内存对数据库性能的影响。关键点在于通过SQL执行情况、等待事件、进程状态和内存使用分析，找出并解决了导致性能瓶颈的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

现象：
节点2 运行：select sum(bytes) from dba_segments 长期等待： waiting for 'gc cr request'
节点1 僵死：select sid,serial# from gv$session where username='****'

分析：
数据库内部等待关系：
blocking blocker event
388 773,397 enq: PV - syncstart
773 397 enq: PR - contention
397 enq: PR - contention os thread startup：ora-445

--1
节点1 运行语句select sid,serial# from gv$session where username='WANQIONG':
失败的原因：PROCESS 53/57 两个会话运行该语句,PROCESS 20: CKPT block这两个会话
PROCESS 20 由于waiting for 'enq: PR - contention' name|mode=0x50520006
0x50520006 所有者：PROCESS 45: CJQ0
根据：awk -f ass109.awk calldb1_ora_15639.trc 分析：
Resource Holder State
Enqueue PR-00000000-00000000 45: 0: waiting for 'os thread startup'
Enqueue PV-00000000-00000000 38: 38: is waiting for 45:
Latch 380030bc0 ??? Blocker
PROCESS 45: CJQ0 进程是罪魁祸首！该进程无法启动。

--2 节点2
节点2运行语句：select sum(bytes) from dba_segments，等待时间0: waiting for 'gc cr request'
cssd,crsd没有发现明显节点通讯报错，怀疑由于节点1死机引起

--3 节点1 警告日志分析
Mon Aug 11 09:45:49 2014
Archived Log entry 58557 added for thread 1 sequence 27907 ID 0x4bcd9425 dest 1:
Mon Aug 11 16:08:44 2014
Errors in file /oracle/diag/rdbms/calldb/calldb1/trace/calldb1_cjq0_8914.trc (incident=56362):
ORA-00445: background process "J000" did not start after 120 seconds
Incident details in: /oracle/diag/rdbms/calldb/calldb1/incident/incdir_56362/calldb1_cjq0_8914_i56362.trc
kkjcre1p: unable to spawn jobq slave process
Errors in file /oracle/diag/rdbms/calldb/calldb1/trace/calldb1_cjq0_8914.trc:
Mon Aug 11 16:12:25 2014
Errors in file /oracle/diag/rdbms/calldb/calldb1/trace/calldb1_mmon_8497.trc (incident=56202):
ORA-00445: background process "m000" did not start after 120 seconds
Incident details in: /oracle/diag/rdbms/calldb/calldb1/incident/incdir_56202/calldb1_mmon_8497_i56202.trc
Mon Aug 11 16:14:28 2014

awr分析内存16G
Begin End
Host Mem (MB): 15,744.0 15,744.0
SGA use (MB): 4,608.0 4,608.0
PGA use (MB): 462.5 460.1
% Host Mem used for SGA+PGA: 32.21 32.19

重启后内存12g
[oracle@calldb1 /home/oracle]$ prtconf | grep 'Memory'
Memory size: 12288 Megabytes

怀疑问题出在内存上：
1379200.1 根据文档可以确定：