Friday, 28 June 2013

Locking issues


Testing.

User session 1

MANZ SES_1>delete from emp where empno = 1;
1 row deleted.
--- Not commited.

User Session 2

MANZ_SES_2>delete from emp where empno = 1;

--- Session hanged...

DBA Session.
SQL> select a.sid, a.serial#, a.sql_id, a.event, a.p1text, a.p2text, a.p3text, a.p1, a.p2, a.p3, b.sql_text from V$session a, V$sql b where
     a.sql_hash_value = b.hash_value and a.sql_address = b.address;
       SID    SERIAL# SQL_ID        EVENT                                                            P1TEXT
---------- ---------- ------------- ---------------------------------------------------------------- ----------------------------------------------------------------
P2TEXT                                                           P3TEXT                                                                   P1         P2         P3
---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------- ----------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        47         11 1u6pm5aay1kcm enq: TX - row lock contention                                    name|mode                                                        usn<<16 | slot                                                   sequence                                                         1415053318     524313       2611
delete from emp where empno = 1

-- Show the second session is waiting cause of row lock contention.

As per the document.


id1
The first identifier (id1) of the enqueue or global lock takes its value from P2 or
P2RAW. The meaning of the identifier depends on the name (P1).
id2
The second identifier (id2) of the enqueue or global lock takes its value from P3 or
P3RAW. The meaning of the identifier depends on the name (P1).

select * from dba_lock where session_id = 47;

SESSION_ID LOCK_TYPE                  MODE_HELD            MODE_REQUESTED       LOCK_ID1             LOCK_ID2             LAST_CONVERT BLOCKING_OTHERS
---------- -------------------------- -------------------- -------------------- -------------------- -------------------- ------------ --------------------
        47 Transaction                None                 Exclusive            524313               2611                         3382 Not Blocking
        47 AE                         Share                None                 100                  0                           13242 Not Blocking
        47 DML                        Row-X (SX)           None                 76334                0                            3382 Not Blocking
        47 DML                        Row-X (SX)           None                 76335                0                            3382 Not Blocking

Blocking session:-
SQL> select * from dba_lock where blocking_others = 'Blocking';
SESSION_ID LOCK_TYPE                  MODE_HELD            MODE_REQUESTED       LOCK_ID1             LOCK_ID2             LAST_CONVERT BLOCKING_OTHERS
---------- -------------------------- -------------------- -------------------- -------------------- -------------------- ------------ --------------------
        49 Transaction                Exclusive            None                 524313               2611                         3474 Blocking

SQL> select p1, p2, p3 , p1text, p2text, p3text from v$session where sid = 47;

        P1         P2         P3 P1TEXT               P2TEXT               P3TEXT
---------- ---------- ---------- -------------------- -------------------- --------------------
1415053318     524313       2611 name|mode            usn<<16 | slot       sequence

-- From the above for the haning session we could see that
SQL> select p1, p1raw from V$session where sid = 47;
        P1 P1RAW
---------- ----------------
1415053318 0000000054580006

p1 denotes name|mode ( Type of the lock and lock mode -- 5458 denotes the type and 0006 the mode which is exclusive).

The name or "type" of the enqueue or global lock can be determined by looking at the
two high order bytes of P1 or P1RAW. The name is always two characters. Use the
following SQL statement to retrieve the lock name.
select chr(bitand(p1,-16777216)/16777215)||
chr(bitand(p1,16711680)/65535) "Lock"
from v$session where sid = 47;
Lock
--------
TX

p2 denotes usn<<16 | slot ( USN is the UNDO SEGMENT NUMBER AND | SLOT IS THE UNDO SLOT) this can be identified using the value of p2
p2 value is 524313
SQL> select trunc(524313/power(2,16)) USN, mod(524313,power(2,16)) SLOT from dual;
       USN       SLOT
---------- ----------
         8         25
Using the above we can find the blocking session.
SQL> select a.sid, a.serial#, a.event, b.xidusn, b.xidslot from V$session a, V$transaction b where a.taddr = b.addr and b.xidusn = 8 and b.xidslot = 25;
       SID    SERIAL# EVENT                                                                XIDUSN    XIDSLOT
---------- ---------- ---------------------------------------------------------------- ---------- ----------
        49        179 SQL*Net message from client                                               8         25


SQL> select
(select username from v$session where sid=a.sid) blocker, a.sid,' is blocking ',
(select username from v$session where sid=b.sid) blockee, b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
/
BLOCKER                               SID 'ISBLOCKING'  BLOCKEE                               SID
------------------------------ ---------- ------------- ------------------------------ ----------
MANZOOR                                49  is blocking  MANZOOR                                47

select username,
v$lock.sid,
trunc(id1/power(2,16)) rbs,
bitand(id1,to_number('ffff','xxxx'))+0 slot,
id2 seq,
lmode,
request
from v$lock, v$session
where v$lock.type = 'TX'
and v$lock.sid = v$session.sid
and v$session.username = 'MANZOOR'
/

USERNAME                              SID        RBS       SLOT        SEQ      LMODE    REQUEST
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
MANZOOR                                49          8         25       2611          6          0
MANZOOR                                47          8         25       2611          0          6

select
(select username from v$session where sid=a.sid) BLOCKER,
a.sid Blocker_SID,
(select serial# from V$session where sid=a.sid) SERIAL#,
' Is Blocking ',
(select username from V$session where sid=b.sid) Blocked_User,
b.sid Blocked_SID
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
/
BLOCKER                        BLOCKER_SID    SERIAL# 'ISBLOCKING'  BLOCKED_USER                   BLOCKED_SID
------------------------------ ----------- ---------- ------------- ------------------------------ -----------
MANZOOR                                 49        179  Is Blocking  MANZOOR                                 47

Blocking session using ASH

SQL> select        count(*) cnt,
              session_id sid,
              substr(event,1,30) event,
              mod(p1,16)  as lm,
                  sql_id,
                  CURRENT_OBJ# || ' ' || object_name obj
                , o.object_type type
                , CURRENT_FILE# file#
                , CURRENT_BLOCK#  block#
            , blocking_session bsid
    from v$active_session_history ash,
         all_objects o
    where
            event  like 'enq: T%'
      and o.object_id (+)= ash.current_obj#
   group by event,session_id,p1,sql_ID,CURRENT_OBJ#,OBJECT_NAME,OBJECT_TYPE,CURRENT_FILE#, CURRENT_BLOCK#, BLOCKING_SESSION
order by  count(*)
   /
       CNT        SID EVENT                                                                                                                            LM SQL_ID        OBJ                                                                     TYPE                     FILE#     BLOCK#       BSID
---------- ---------- ------------------------------------------------------------------------------------------------------------------------ ---------- ------------- ----------------------------------------------------------------------- ------------------- ---------- ---------- ----------
      1552         47 enq: TX - row lock contention                                                                                                     4 5hnbsyk0zh8p6 -1                                                                                                   0          0         49
      4386         47 enq: TX - row lock contention                                                                                                     6 1u6pm5aay1kcm 76335 EMP                                                               TABLE                        5        142         49



SQL>  select a.sample_id,a.sample_time,a.session_id,a.event,
             a.session_state,a.event,a.sql_id,
    a.blocking_session,a.blocking_session_status
    from v$active_session_history a, dba_users u
    where u.user_id = a.user_id
    and u.username = 'MANZOOR';
 SAMPLE_ID SAMPLE_TIME                                                                 SESSION_ID EVENT                                                            SESSION
---------- --------------------------------------------------------------------------- ---------- ---------------------------------------------------------------- -------
EVENT                                                            SQL_ID        BLOCKING_SESSION BLOCKING_SE
---------------------------------------------------------------- ------------- ---------------- -----------
    202272 28-JUN-13 10.27.53.538 PM                                                           47 enq: TX - row lock contention                                    WAITING
enq: TX - row lock contention                                    1u6pm5aay1kcm               49 VALID
    202271 28-JUN-13 10.27.52.538 PM                                                           47 enq: TX - row lock contention                                    WAITING
enq: TX - row lock contention                                    1u6pm5aay1kcm               49 VALID



some useful site.
https://sites.google.com/site/embtdbo/wait-event-documentation/oracle-enqueues#TOC-enq:-TX---row-lock-contention-mode-6





Posted by at No comments:

Monday, 17 June 2013

Replicat abend issues

1. Issue
========

Replicat abended with the error

2013-06-17 17:08:45  ERROR   OGG-01163  Bad column length (7) specified for column COMM_NEED_FLG in table SIEBEL.S_SRV_REQ, maximum allowable length is 4.


2. Steps handled to solve.
==========================


>> -- Problemtic Record from the report file.


2013-06-17 17:08:45  ERROR   OGG-01163  Bad column length (7) specified for column COMM_NEED_FLG in table SIEBEL.S_SRV_REQ, maximum allowable length is 4.

***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************

Reading ./dirdat/PQ000364, current RBA 821565342, 26218 records

Report at 2013-06-17 17:08:45 (activity since 2013-06-17 17:06:59)

From Table SIEBEL.S_SRV_REQ to SIEBEL.S_SRV_REQ:
       #                   inserts:         0
       #                   updates:     26218
       #                   deletes:         0
       #                  discards:         0



Last log location read:
     FILE:      ./dirdat/PQ000364
     SEQNO:     364
     RBA:       821565342
     TIMESTAMP: 2013-06-16 01:33:00.838513
     EOF:       NO
     READERR:   0


>> -- Problemtic record from the logdump file.

logdump> open ./dirdat/PQ000364
logdump> ghdr on
logdump> detail on
logdump> detail data
logdump> ggstoken on
logdump> ggstoken detail
logdump> pos 821565342
logdump> n

==============================================================================
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :  4127  (x101f)   IO Time    : 2013/06/16 01:33:00.838.513
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x01)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :      13685       AuditPos   : 201126860
Continued  :     N  (x00)     RecCount   :     1  (x01)

2013/06/16 01:33:00.838.513 FieldComp            Len  4127 RBA 821565342
Name: SIEBEL.S_SRV_REQ

After  Image:                                             Partition 4   G  m
 0000 000d 0000 0009 312d 3131 3747 5246 4f00 0100 | ........1-117GRFO...
 1500 0032 3031 322d 3031 2d30 363a 3033 3a30 313a | ...2012-01-06:03:01:
 3538 0002 000b 0000 0007 312d 524a 5936 5a00 0300 | 58........1-RJY6Z...
 1500 0032 3031 322d 3031 2d30 363a 3033 3a30 323a | ...2012-01-06:03:02:
 3030 0004 000b 0000 0007 312d 524a 5936 5a00 0500 | 00........1-RJY6Z...
 0a00 0000 0000 0000 0000 0100 0600 0500 0000 0130 | ...................0
 0007 0005 0000 0001 4e00 0800 0500 0000 014e 0009 | ........N........N..
================================================================================


Note -- The above is the problemetic record where the column comm_need_flg is having some wrong data, also this
particular record is in the middle of the trasaction (TransInd   :     .  (x01)), so all the records from the
begining to this transaction will be rolled backed by ogg.


logdump>n

___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :  4409  (x1139)   IO Time    : 2013/06/16 01:33:00.838.513
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x01)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :      13685       AuditPos   : 201130364
Continued  :     N  (x00)     RecCount   :     1  (x01)

2013/06/16 01:33:00.838.513 FieldComp            Len  4409 RBA 821569564


Note -- The above is the next record of the problemetic record. We can start the replicat from this RBA.
Before that we need to handle the previous records in this transaction.




>>-- Add the below parameters in the abended replicat group and start the replicat. This parameter will
commit the records one by one so all the records will be commited and the replicat will get abend when
exactly it reaches the problemtic record.


grouptransops 1
maxtransops 1


>>-- Now take the export of that particualr record from the source and import to the target table.


>>-- Now alter the replicat to the next good RBA which we have identified by logdump (RBA 821569564).

>>-- Remove the params  GROUPTRANSOPS 1 and MAXTRANSOPS 1 from the replciat group.


ggsci>dblogin userid <username>, password <password>
ggsci>alter replicat <replicat-name>, extrba 821569564    -- No need to specify the seq no if we are altering on the same trail file seq.
ggsci> start replicat <replicat-name>

Thanks





 











Posted by at 1 comment:
Subscribe to: Posts (Atom)