11gR2RACDynamicremastering - 数据库编程

In this post, I will demonstrate dynamic remastering of the resources in RAC . 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 .When one instance departs the cluster, the GRD portion of that instance needs to be redistributed to the surviving nodes. Similarly, when a new instance enters the cluster, the GRD portions of the existing instances must be redistributed to create the GRD portion of the new instance. This is called dynamic resource reconfiguration. In addition to dynamic resource reconfiguration, This is called dynamic remastering. The basic idea is to master a buffer cache resource on the instance where it is mostly accessed. In order to determine whether dynamic remastering is necessary, the GCS essentially keeps track of the number of GCS requests on a per-instance and per-object basis. This means that if an instance, compared to another, is heavily accessing blocks from the same object, the GCS can take the decision to dynamically migrate all of that object’s resources to the instance that is accessing the object most. LMON, LMD and LMS processes are responsible for Dynamic remastering. ? Remastering can be triggered as result of ? Manual remastering ? Resource affinity ? Instance crash ? CURRENT SCENARIO - - 3 node setup - name of the database ? racdb ― SETUP ? ? Get data_object_id for scott.emp

SYS>  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 = 'EMP';

OWNER DATA_OBJECT_ID OBJECT_NAME ―――- ――――? ――――― SCOTT 73181 EMP ? Get File_id and block_id of emp table

SQL>select empno, dbms_rowid.rowid_relative_fno(rowid), 
                  dbms_rowid.rowid_block_number(rowid) 
          from scott.emp 
           where empno in (7788, 7369);

EMPNO DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ―――- ―――――――――――― ―――――――――――― 7369 4 151 7788 4 151 ? MANUAL REMASTERING ? You can manually remaster an object with oradebug command : oradebug lkdebug -m pkey

? NODE1 ? shutdown the database and restart

[oracle@host01 ~]$ srvctl stop database -d racdb 
                  srvctl start database -d racdb
                  srvctl status database -d racdb

? Issue a select on the object from NODE2

SCOTT@NODE2> select * from  emp;

? Find the GCS resource name to be used in the query 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 in V$gc_element and v$dlm_rss views get_resource_name

SYS@NODE2>col hexname for a25 
             col resource_name for a15 
             select b.kjblname hexname, b.kjblname2 resource_name, 
                     b.kjblgrant, b.kjblrole, b.kjblrequest  
           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 = 151 
                                and obj    = 73181 
                               and class  = 1 
                                and state   <> 3);

HEXNAME RESOURCE_NAME KJBLGRANT KJBLROLE KJBLREQUE ――――――――- ――――― ――― ―――- ――― [0x97][0x4],[BL] 151,4,BL KJUSERPR 0 KJUSERNL ? Check the current master of the block ? ? Note that current master of scott.emp is node1 (numbering starts from 0) ? Previous master = 32767 is a place holder indicating that prior master was not known, meaning first remastering of that object.hat index happened. Now the master is 0 which is instance 1. ? REMASTER_CNT = 1 indicating the object has been remastered only once

SYS>select o.object_name, m.CURRENT_MASTER, 
                   m.PREVIOUS_MASTER, m.RE

11gR2RACDynamicremastering(一)