Introduction and instance demonstration of blocked in Oracle RAC environment, racblocked

Introduction and instance demonstration of blocked in Oracle RAC environment, racblocked

The blocking in the RAC environment is different from that in a single instance, because we need to consider the session in different instances. That is to say, the corresponding strain of the previously queried v $ session and v $ lock is global. This article provides two query scripts, and provides an example to demonstrate which sessions are blocked and which are blocked. For the concept of blocking and blocking in a single instance environment, see Oracle blocking (blocking blocked)

1. Demo Environment

Scott @ DEVDB> select * from v $ version where rownum <2; BANNER implements Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production -- publish SQL statements in scott session, scott @ DEVDB> begin 2 update emp set sal = sal + 100 where empno = 7788; 3 update dept set dname = 'dba 'where deptno = 10; 4 end; 5/PL/SQL procedure successfully completed. -- update the emp object leshami @ DEVDB> update scott in the leshami session. emp set sal = sal-200 where empno = 7788; -- update emp object usr1 @ DEVDB> update scott in usr1 session. dept set dname = 'dev' where deptno = 10;

2. Search for blocking

Scott @ DEVDB> @ inclusid_serial CONN_INSTANCE sid program osuser machine LOCK_TYPE LOCK_MODE CTIME OBJECT_NAME too far ------------------ ---- upper ------- upper -------------- lower Blocking-> '100 5' devdb1 20 sqlplus @ Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusi Ve 666 DEPTBlocking-> '100 5' devdb1 20 sqlplus @ Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusive 20,154 EMPWaiting '100 7' devdb1 49 sqlplus @ Linux-01 (TNS V1-V3) oracle Linux-01 Transaction None 618 EMPWaiting '933,116 91 'devdb2 933 sqlplus @ Linux-02 (TNS V1-V3) oracle Linux-02 Transaction None 558 DEPT -- through the above script we can see that session '123456' locks the object DEPT and EMP, and at this time the session '12347' and '1234569' are in the waiting state. -- The following is another way to get the blocking situation scott @ DEVDB> @ block_session_rac2BLOCKING_STATUS into SCOTT @ Linux-01 (INST = 1 SID = 20 Serail # = 1545) is blocking USR1 @ Linux-02 (INST = 2 SID = 933 Serial # = 11691) SCOTT @ Linux-01 (INST = 1 SID = 20 Serail # = 1545) is blocking leshami @ Linux-01 (INST = 1 SID = 49 Serial # = 1007) -- Author: Leshami -- Blog: http://blog.csdn.net/leshami

3. scripts used in the demo

[oracle@Linux-01 ~]$ more block_session_rac.sql set linesize 180col user_status format a15col sid_serial format a15col program format a30 wrappedcol machine format a15 wrappedcol osuser format a15 wrappedcol conn_instance format a15col object_name format a25 wrapped SELECT DECODE (l.block, 0, 'Waiting', 'Blocking ->') user_status,     CHR (39) || s.sid || ',' || s.serial# || CHR (39) sid_serial,     (SELECT instance_name      FROM gv$instance      WHERE inst_id = l.inst_id)      conn_instance,     s.sid,     s.program,     s.osuser,     s.machine,     DECODE (l.TYPE,         'RT', 'Redo Log Buffer',         'TD', 'Dictionary',         'TM', 'DML',         'TS', 'Temp Segments',         'TX', 'Transaction',         'UL', 'User',         'RW', 'Row Wait',         l.TYPE)      lock_type--,id1           --,id2     ,     DECODE (l.lmode,         0, 'None',         1, 'Null',         2, 'Row Share',         3, 'Row Excl.',         4, 'Share',         5, 'S/Row Excl.',         6, 'Exclusive',         LTRIM (TO_CHAR (lmode, '990')))      lock_mode,     ctime--,DECODE(l.BLOCK, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') lock_status     ,     object_name  FROM gv$lock l     JOIN gv$session s ON (l.inst_id = s.inst_id AND l.sid = s.sid)     JOIN gv$locked_object o      ON (o.inst_id = s.inst_id AND s.sid = o.session_id)     JOIN dba_objects d ON (d.object_id = o.object_id)  WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE                   FROM gv$lock                   WHERE request > 0)ORDER BY id1, id2, ctime DESC;[oracle@Linux-01 ~]$ more block_session_rac2.sql SELECT DISTINCT     s1.username    || '@'    || s1.machine    || ' ( INST='    || s1.inst_id    || ' SID='    || s1.sid    || ' Serail#='    || s1.serial#    || ' ) IS BLOCKING '    || s2.username    || '@'    || s2.machine    || ' ( INST='    || s2.inst_id    || ' SID='    || s2.sid    || ' Serial#='    || s2.serial#    || ' ) '     AS blocking_status FROM gv$lock l1,    gv$session s1,    gv$lock l2,    gv$session s2 WHERE   s1.sid = l1.sid    AND s2.sid = l2.sid    AND s1.inst_id = l1.inst_id    AND s2.inst_id = l2.inst_id    AND l1.block > 0    AND l2.request > 0    AND l1.id1 = l2.id1    AND l1.id2 = l2.id2;

Differences between oracle rac and ha

Ha only monitors and manages the database at the operating system level. It is generally used only for single-instance databases. The advantage is convenient management, convenient application development (convenient for developers), and low investment in projects. The disadvantage is that it has all the disadvantages of single-instance databases, such as poor fault tolerance, poor endurance, and low user capacity.
Rac, a single database multi-instance application method provided by the database itself, advantages: 1. Flexible use, can be used as a single machine or multiple machines. 2. provides a solution for massive user access, that is, the multi-host parallel operation jointly undertakes the memory, cpu, and other system resources consumed during database operation. 3. The fault tolerance capability is higher than that of a single machine, which is particularly significant in the fault tolerance of host issues. (This is not the case for a magnetic array problem ). Disadvantages: 1. High development requirements. The balance mode must be configured separately to reflect its advantages. 2. high investment, and separate investment in hardware and software.
(However, in terms of software investment, if the requirements are not high, the two solutions are similar, both of which are the basic configurations of two machines plus a magnetic array o (cost _ cost) o)

When backing up an Oracle RAC environment database, is there a difference between backing up only one database and backing up two databases?

Environment 1:
1. If the parameter file uses spfile, It is consistent. If pfile is used, it may be inconsistent.
The control files must be the same.
2. Archivelog is different.
3. The recovery method is the same as that of a single instance. You only need to ensure that the node to be restored can access the archived logs of the two nodes.

Environment 2:
1. If the parameter file uses spfile, It is consistent. If pfile is used, it may be inconsistent.
The control files must be the same.
2. Archivelog is different.
3. Back up the Database and Controlfile of any node, and back up the Archivelog of the two nodes.

Question added:
Generally, script A is used.