In this post, I will demonstrate three methods to find the master of a resource(block).
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
- 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′;
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 and Method-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);
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);
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 ;
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]%';
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;
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
I hope you found this information useful.
Keep visiting the blog !!!
——————————————————————————————–
oradebug setmypid
oradebug setmypid need to be placed before oradebug lkdebug -m pkey for command to rub