查询oracle数据库adg的模式,【案例】Oracle ADG模式出现gc buffer busy release等待事件...

【案例】Oracle ADG模式出现gc buffer busy release等待事件

时间:2016-11-05 22:00   来源:Oracle研究中心   作者:HTZ   点击:

次

天萃荷净

Oracle研究中心案例分析：运维DBA反映Oracle数据库ADG模式下MRP进程不能应用日志，子进程出现gc buffer busy release等待。

1，环境介绍

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

ORACLE_HOME = /u01/oracle/product/11.2.0

System name: AIX

数据库的PSU为11.2.0.4.2

2，对ASM实例做HANGANALYZE与SYSTEMSTATE

这里对ASM实例做下面的操作不会生成很多的TRACE文件。

oradebug setmypid

oradebug unlimit

oradebug hanganalyze 3

oradebug dump systemstate 266

3，KILL MRP进程

通过recover managed standby database cancel;不能正常取消，只能通过手动KILL MRP进程，在执行RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT；后正常运用日志。

4，分析HANGANALYZE与SYSTEMSTATE日志

4.1 查看hanganalyze部分

*** 2015-03-31 20:09:59.453

===============================================================================

HANG ANALYSIS:

instances (db_name.oracle_sid): cdhtz_p780.cdhtz1

oradebug_node_dump_level: 3

os thread scheduling delay history: (sampling every 1.000000 secs)

0.000000 secs at [ 20:09:58 ]

NOTE: scheduling delay has not been sampled for 0.818740 secs 0.000000 secs from [ 20:09:54 – 20:09:59 ], 5 sec avg

0.000000 secs from [ 20:09:00 – 20:09:59 ], 1 min avg

0.000000 secs from [ 20:05:00 – 20:09:59 ], 5 min avg

vktm time drift history

===============================================================================

Chains most likely to have caused the hang:

[a] Chain 1 Signature: ‘gc buffer busy release'<=’buffer busy waits’

Chain 1 Signature Hash: 0xe473997a

[b] Chain 2 Signature: ‘parallel recovery slave next change'<=’buffer busy waits’

Chain 2 Signature Hash: 0x16abd035

[c] Chain 3 Signature: ‘gc buffer busy release'<=’buffer busy waits’

Chain 3 Signature Hash: 0xe473997a

===============================================================================

Non-intersecting chains:

——————————————————————————-

Chain 1:

——————————————————————————-

Oracle session identified by:

{

instance: 1 (cdhtz_p780.cdhtz1)

os id: 6226628

process id: 98, oracle@cdhtzrzdb1

session id: 255

session serial #: 451

}

is waiting for ‘buffer busy waits’ with wait info:

{

p1: ‘file#’=0x7

p2: ‘block#’=0x229d

p3: ‘class#’=0x62

time in wait: 0.944766 sec (last interval)

time in wait: 76 min 54 sec (total)

timeout after: never

wait id: 135153

blocking: 0 sessions

wait history:

* time between current wait and wait #1: 0.000000 sec

1. event: ‘latch: cache buffers chains’

time waited: 0.000017 sec

wait id: 152253 p1: ‘address’=0x7000103deef8f58

p2: ‘number’=0xb1

p3: ‘tries’=0x0

* time between wait #1 and #2: 0.000000 sec

2. event: ‘buffer busy waits’

time waited: 4.345775 sec (last interval)

time waited: 76 min 53 sec (total)

wait id: 135153 p1: ‘file#’=0x7

p2: ‘block#’=0x229d

p3: ‘class#’=0x62

* time between wait #2 and #3: 0.000000 sec

3. event: ‘latch: cache buffers chains’

time waited: 0.000012 sec

wait id: 152252 p1: ‘address’=0x7000103deef8f58

p2: ‘number’=0xb1

p3: ‘tries’=0x0

}

and is blocked by

=> Oracle session identified by:

{

instance: 1 (cdhtz_p780.cdhtz1)

os id: 5767982

process id: 77, oracle@cdhtzrzdb1 (PR0E)

session id: 633

session serial #: 51

}

which is waiting for ‘gc buffer busy release’ with wait info:

{

p1: ‘file#’=0x7

p2: ‘block#’=0x229d

p3: ‘class#’=0x62

time in wait: 0.923282 sec

heur. time in wait: 24 min 49 sec

timeout after: 0.076718 sec

wait id: 258197465

blocking: 2 sessions

wait