Resource Master in Oracle 11g RAC

11g R2 RAC: HOW TO FIND THE RESOURCE MASTER?

In RAC, every data block is mastered by an instance. Mastering a block simply means that master instance keeps track of the state of the block until the next reconfiguration event.

– Remastering can be triggered as result of

- Manually

- Resource affinity

- Instance crash

 There are three ways to find the master node for a resource.

-  Method –  I gets info about master node from v$gcspfmaster_info   using data_object_id

- Method – II gets info about master node from v$dlm_ress and v$ges_enqueue   using resource name in hexadecimal format

- Method – III gets info about master node from x$kjbl with x$le using resource name in hexadecimal format

– CURRENT SCENARIO -

- 3 node setup

- name of the database – orcl

— SETUP –

SYS@NODE1>create table scott.emp1 as

select * from scott.emp;

– Get data_object_id for scott.emp1

SQL> col owner for a10

col data_object_id for 9999999
col object_name for a15
select owner, data_object_id, object_name
from dba_objects
where owner = ‘SCOTT’
and object_name = ‘EMP1′;

OWNER      DATA_OBJECT_ID OBJECT_NAME

———- ————– —————

SCOTT               74652 EMP1

For Method-II & III, we need to find out file_id and block_id and hence GCS resource name in hexadecimal format

 – Get File_id and range of block_ids of emp1 table

– It can be seen that emp1 lies in block 523 of file 4.

SQL>select dbms_rowid.rowid_relative_fno(rowid) FILE_NO,

min(dbms_rowid.rowid_block_number(rowid)) MIN_BLOCK_ID,
max(dbms_rowid.rowid_block_number(rowid))  MAX_BLOCK_ID
from scott.emp1
group by dbms_rowid.rowid_relative_fno(rowid);

FILE_NO MIN_BLOCK_ID MAX_BLOCK_ID

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

  4          523          523

– Find the GCS resource name to be used in  the query using blodk_id and data_object_id retrieved above.

x$kjbl.kjblname = resource name in hexadecimal format([id1],[id2],[type]

x$kjbl.kjblname2 = resource name in decimal format

Hexname will be used to query resource master using v$dlm_ress , v$ges_enqueue, $kjbl

and x$le

SQL> col hexname for a25

col resource_name for a15
select b.kjblname hexname, b.kjblname2 resource_name
from x$le a, x$kjbl b
where a.le_kjbl=b.kjbllockp
and a.le_addr = ( select le_addr
from x$bh
where dbablk = 523
and obj    = 74652
and class  = 1
and state   <> 3);

HEXNAME                   RESOURCE_NAME

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

[0x20b][0x4],[BL]         523,4,BL

– Manually master the EMP table to node1 –

SYS@NODE1>oradebug lkdebug -m pkey <objectid>

SYS@NODE1>oradebug lkdebug -m pkey 74652

—- GET RESOURCE MASTER NAME ———-

Method – I gets info about master node from v$gcspfmaster_info using data_object_id

– ——-

– Note that current master is node1 (Node numbering starts from 0)

SYS@node1>col object_name for A10

select o.object_name, m.CURRENT_MASTER
from   dba_objects o, v$gcspfmaster_info m
where o.data_object_id=74652
and m.data_object_id = 74652 ;

OBJECT_NAM CURRENT_MASTER

———- ————–

EMP1                    0

—- Method II gets info about master node from v$dlm_ress and v$ges_enqueue

using resource name in hexadecimal format

– check that master node is node1 (node numbering starts with 0)

SYS@NODE1>col resource_name for a22

select a.resource_name,  a.master_node
from   v$dlm_ress a, v$ges_enqueue b
where upper(a.resource_name) = upper(b.resource_name1)
and a.resource_name like ‘%[0x20b][0x4],[BL]%’;

RESOURCE_NAME          MASTER_NODE

———————- ———–

[0x20b][0x4],[BL]                0

Method – III gets info about master node from x$kjbl with x$le

using resource name in hexadecimal format

–  This SQL joins   x$kjbl with x$le to retrieve resource master for a block

– Note that current master is node1(MASTER=0)

SYS@NODE1> select kj.kjblname, kj.kjblname2, kj.kjblmaster master
from (select kjblname, kjblname2, kjblowner, kjblmaster, kjbllockp
from x$kjbl
where kjblname = ‘[0x20b][0x4],[BL]‘
) kj, x$le le
where le.le_kjbl = kj.kjbllockp
order by le.le_addr;

KJBLNAME                       KJBLNAME2                          MASTER

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

[0x20b][0x4],[BL]              523,4,BL                                     0