Wednesday, April 07, 2010

Dynamic Re-Mastering in 11g

This is a follow-up on a post from last year

http://el-caro.blogspot.com/search/label/10g%20RAC%20DRM

Node 1
========
SQL> create table obj as select * from sys.obj$;

Table created.

SQL> select object_id,owner,object_type from dba_objects where object_name='OBJ' and object_type='TABLE';

OBJECT_ID OWNER OBJECT_TYPE
---------- ------------------------------ -------------------
136388 REGOFA TABLE


SQL> select * from V$GCSPFMASTER_INFO where DATA_OBJECT_ID=136388;

no rows selected


Now we go to node 2 and manually master the object there...

Node 2
=========
SQL> oradebug setmypid
Statement processed.
SQL> oradebug lkdebug -m pkey 136388
Statement processed.
SQL> select * from V$GCSPFMASTER_INFO where data_object_id=136388;

FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER
---------- -------------- ----------- -------------- ---------------
REMASTER_CNT
------------
0 136388 Affinity 1 32767
1


Node 1
======

SQL> oradebug setmypid
Statement processed.
SQL> oradebug lkdebug -m pkey 136388
Statement processed.

Also Bug 5649377: REMASTER_CNT IN V$GCSPFMASTER_INFO IS NOT GETTING UPDATED
which I logged in 2006 is fixed in 11g

SQL> /

FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER
---------- -------------- ----------- -------------- ---------------
REMASTER_CNT
------------
0 136388 Affinity 0 1
2

As you can see the remaster_cnt is correctly set to 2 above
SQL> select * from X$KJDRMAFNSTATS;


ADDR INDX INST_ID DRMS AVG_DRM_TIME OBJECTS_PER_DRM
---------------- ---------- ---------- ---------- ------------ ---------------
QUIESCE_T FRZ_T CLEANUP_T REPLAY_T FIXWRITE_T SYNC_T RES_CLEANED
---------- ---------- ---------- ---------- ---------- ---------- -----------
REPLAY_S REPLAY_R MY_OBJECTS
---------- ---------- ----------
0000000060016FC0 0 1 104 11291 186
3219 28 1622 1302 4589 510 0
26 191959 2


SQL> select count(*) from V$GCSPFMASTER_INFO where current_master=0;

COUNT(*)
----------
2


DRM info is now also available in AWR tables via DBA_HIST_DYN_REMASTER_STATS

Also the following event can be used to trace DRM information

Event can be turned ON dynamically

SQL> alter system set events '10430 trace name context forever, level 10';

System altered.


*** 2010-04-07 21:02:23.831
Begin DRM(245) (swin 0) - AFFINITY transfer pkey 136388.0 to 2 oscan 1.1
kjiobjscn 1
ftd (30) received from node 1 (8/0.0.0)
all ftds received

* kjxftdn: break from kjxftdn, post lmon later

Event can be turned off
SQL> alter system set events '10430 trace name context off';

System altered.


Also the LMON and LMS trace files contains DRM related info

Although I have seen a lot of issues in 10G and turned off DRM in many cases I haven't seen any such issues
in 11.2 so far