View DRM defaults

SQL>
SELECT x.ksppinm  as name,
       y.ksppstvl as value,
       y.ksppstdf as isdefault,
       x.ksppdesc describ
  FROM SYS.x$ksppi x, SYS.x$ksppcv y
 WHERE x.inst_id = USERENV('Instance')
   AND y.inst_id = USERENV('Instance')
   AND x.indx = y.indx
   AND x.ksppinm in ('_gc_policy_time', '_gc_undo_affinity');

     NAME  VALUE  ISDEFAULT  DESCRIB
1  _gc_undo_affinity  TRUE  TRUE  if TRUE, enable dynamic undo affinity
2  _gc_policy_time  10          TRUE  how often to make object policy decisions in minutes

Closing method: all instances need to be stopped before the instance can be started

SQL> alter system set "_gc_policy_time"=0 scope=spfile sid='*';
SQL> alter system set "_gc_undo_affinity"=FALSE scope=spfile sid='*';

[oracle@rac01 ~]$ srvctl stop database -d cjcdb
[oracle@rac01 ~]$ srvctl start database -d cjcdb
[oracle@rac01 ~]$ srvctl status database -d cjcdb -v

Instance cjcdb1 is running on node rac01. Instance status: Open.
Instance cjcdb2 is running on node rac02. Instance status: Open.

View modified values

SELECT x.ksppinm  as name,
       y.ksppstvl as value,
       y.ksppstdf as isdefault,
       x.ksppdesc describ
  FROM SYS.x$ksppi x, SYS.x$ksppcv y
 WHERE x.inst_id = USERENV('Instance')
   AND y.inst_id = USERENV('Instance')
   AND x.indx = y.indx
   AND x.ksppinm in ('_gc_policy_time', '_gc_undo_affinity');

     NAME  VALUE  ISDEFAULT  DESCRIB
1  _gc_undo_affinity  FALSE  FALSE  if TRUE, enable dynamic undo affinity
2  _gc_policy_time  0  FALSE  how often to make object policy decisions in minutes

If only one instance is restarted after modifying the parameters, errors ORA-01105 and ORA-01606 will be reported

---cjcdb1

SQL> alter system set "_gc_policy_time"=0 scope=spfile sid='*';
SQL> alter system set "_gc_undo_affinity"=FALSE scope=spfile sid='*';
SQL> shutdown immediate
SQL> startup
ORACLE instance started.
Total System Global Area 1023004672 bytes
Fixed Size        2259640 bytes
Variable Size      704644424 bytes
Database Buffers    310378496 bytes
Redo Buffers        5722112 bytes
ORA-01105: mount is incompatible with mounts by other instances
ORA-01606: parameter not identical to that of another mounted instance

At this time, after restarting node 2, node 1 can also be started

---cjcdb2

SQL> shutdown immediate
SQL> startup

---cjcdb1

SQL> shutdown immediate
SQL> alter database mount;
SQL> alter database open;
[oracle@rac01 ~]$ srvctl status database -d cjcdb -v
Instance cjcdb1 is running on node rac01. Instance status: Open.
Instance cjcdb2 is running on node rac02. Instance status: Open.

If you want to close the DRM of only one node, it obviously conflicts with the principle of DRM, and this method is not feasible.

SQL> alter system set "_gc_policy_time"=10 scope=spfile sid='cjcdb1';
SQL> alter system set "_gc_undo_affinity"=TRUE scope=spfile sid='cjcdb1';

[oracle@rac01 ~]$ srvctl stop instance -d cjcdb -i cjcdb1 -o immediate
[oracle@rac01 ~]$ srvctl start instance -d cjcdb -i cjcdb1 -o open

PRCR-1013 : Failed to start resource ora.cjcdb.db
PRCR-1064 : Failed to start resource ora.cjcdb.db on node rac01
CRS-5017: The resource action "ora.cjcdb.db start" encountered the following error: 
ORA-01105: mount is incompatible with mounts by other instances
ORA-01606: parameter not identical to that of another mounted instance
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0/grid/log/rac01/agent/crsd/oraagent_oracle/oraagent_oracle.log".
CRS-2674: Start of 'ora.cjcdb.db' on 'rac01' failed

DRM principle

https://blogs.oracle.com/database4cn/drm

Firstly, we introduce some concepts related to DRM.

Buffer: for RAC database, when a data block is read into the buffer cache, we call it buffer. Cache fusion will manage the buffer as a resource.

Master: in the world of RAC database, every resource has a master instance, The master instance will allocate some space in the shared pool (for example: gcs resource and ges resource) to store information related to the resource, such as which instance has the latest version of the buffer, which instance has the lock of what level of the buffer, etc. in addition, it is responsible for maintaining and the status of the resource.

Next, we briefly describe the process of accessing a buffer in the RAC environment. We take a 4-node RAC database as an example. Note that we will only list one typical case, not all possible cases, and only briefly introduce the steps.

 

Step 1: instance 3 needs to X(exclusive)Mode access buffer1, towards master Instance (1) made a request.
Step 2: master Instance (1) found instance 2 to X Mode holding buffer1，Then notify instance 2 to release X lock，And put buffer1 Send to instance 3.
Step 3: Instance 2 release X lock，And put the latest version buffer1 Send to instance 3.
Step 4: Example 3 obtains buffer1, And notify master Instance (1) update resources buffer1 The latest status of.

From the above steps, it is not difficult to see that in the RAC database, when we access a buffer, there are at most three instances, master instance, holder instance and requestor instance. There are two kinds of data transmission, message: for lock related information transmission, and data: for buffer transmission. At the same time, according to the above steps, we naturally think that if the master and requestor are on the same instance, In this case, the message transmission between instances can be reduced and the code path accessed will be shorter, so as to improve the performance. However, when each buffer is read to the buffer cache, the selection of the master node is random. Based on this consideration, oracle launched a new feature DRM (Dynamic Resource management) from 10g.

The main function of DRM is to determine the instance to which the buffer corresponding to the database object should be mastered according to the number and mode of access to a database object (10gR1 in data file) for each instance within a period of time (10 minutes by default). Within the specified time, If an instance accesses a database object a certain number of times higher than other instances (50 times by default), oracle will transfer the master information of all buffers of the object to the corresponding instance (Note: it is not a buffer transfer). Of course, the transfer process is gradual. When oracle decides to determine the master instance of a buffer to the local instance, it will add an affinity lock to the buffer to achieve fast access. This is also the origin of object affinity we often mention.

Next, we introduce the basic steps of DRM.

 

1． Oracle Stop all when needed remastering of buffer Operation on. be careful: DRM It is gradual, that is to say windows Unit, one part at a time buffer conduct remastering Operation.
2． Lmon Notify all instances to prepare remastering
3． In the old master Instance clear correspondence buffer of master information
4． take master Transfer information to new master example
5． In the new master The latest status of the instance build resource
6． End and release all resources occupied by all previous steps.

Then, we briefly introduce some parameters related to DRM.

 

_gc_policy_time : Unit: minute, control DRM Statistical instance access buffer The time interval of times. The default is 10 minutes.
_gc_affinity_ratio: Control progress remastering The minimum proportion (threshold) to be reached is 50 by default, that is, if an instance is within 10 minutes(_gc_policy_time)When a database object is accessed 50 times more than all other instances(Note: it's 50 times, not 50 times)，For this database object buffer conduct remastering. 

 

Note: please do not modify the values of the above parameters unless you know what you are doing or according to the suggestions of oracle engineers.

Finally, if you encounter DRM related problems, it is recommended that you review the following information.

1. Trace files of lmon, lmd, lms and diag processes to confirm which step of DRM the problem occurs and the status of lms, lmon and lmd processes.

2. AWR and ASH report, confirm that those waiting events last for a long time and the status of lmon,lms and lmd.

3. Refer to note 1492990.1 for DMR diagnostic script output.

I hope the above introduction is helpful to understand DRM.

For more database related learning materials, you can check my ITPUB blog, net name chenoracle:

http://blog.itpub.net/29785807/