Menu de navegação

[mpunganParticipante]

Seguem as informações abaixo, alguém já passou por uma situação assim, o processo de checkpoint esta causando lock no banco.

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
PL/SQL Release 11.1.0.7.0 – Production
“CORE 11.1.0.7.0 Production”
TNS for Linux: Version 11.1.0.7.0 – Production
NLSRTL Version 11.1.0.7.0 – Production

Locked at : 21/03/2012 10:12:19

Session Info :

Serial # : 1
User # : 0
User Name :
SchemaName : SYS
Process Type : BACKGROUND

OS User : aleph
Program : oracle@banshee.prs.br (CKPT)
Machine : banshee.prs.br
Terminal : UNKNOWN

Status : ACTIVE
Logon Time : 09:10:10
Logon Date : 21/03/2012

BackGround Process Info :

Process Name : CKPT
Process Desc : checkpoint

Sessions Being Blocked :

SID : 308
UserName : ALEPH
OS User : aleph
Process : 23303
Program : www_server_main@banshee.prs.br (TNS V1-V3)
Machine : banshee.prs.br
Terminal :
———
SID : 432
UserName : ALEPH
OS User : aleph
Process : 23279
Program : www_server_main@banshee.prs.br (TNS V1-V3)
Machine : banshee.prs.br
Terminal :
———
SID : 444
UserName : ALEPH
OS User : aleph
Process : 21984
Program : www_server_main@banshee.prs.br (TNS V1-V3)
Machine : banshee.prs.br
Terminal :
———

[mpunganParticipante]

Também aparece sessões que estão bloqueadas com Rollback segment.

Locked at : 21/03/2012 10:18:32

Session Info :

Serial # : 1020
User # : 72
User Name : ALEPH
SchemaName : ALEPH
Process Type : USER

OS User : aleph
Program : www_server_main@banshee.prs.br (TNS V1-V3)
Machine : banshee.prs.br
Terminal :

Status : ACTIVE
Logon Time : 10:11:47
Logon Date : 21/03/2012

Rollback Lock :

Rollback Segment _SYSSMU19_1332332345$

Sessions Being Blocked :

[vieriParticipante]

roda estás query’s no momento do bloqueio:

---

GV$LOCK

SELECT substr(DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid,1,12) sess,
id1, id2, lmode, request, type, inst_id
FROM GV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
ORDER BY id1, request;

================================

Show all ddl locks in the system

select decode(lob.kglobtyp,
0, ‘NEXT OBJECT’, 1, ‘INDEX’, 2, ‘TABLE’, 3, ‘CLUSTER’,
4, ‘VIEW’, 5, ‘SYNONYM’, 6, ‘SEQUENCE’,
7, ‘PROCEDURE’, 8, ‘FUNCTION’, 9, ‘PACKAGE’,
11, ‘PACKAGE BODY’, 12, ‘TRIGGER’,
13, ‘TYPE’, 14, ‘TYPE BODY’,
19, ‘TABLE PARTITION’, 20, ‘INDEX PARTITION’, 21, ‘LOB’,
22, ‘LIBRARY’, 23, ‘DIRECTORY’, 24, ‘QUEUE’,
28, ‘JAVA SOURCE’, 29, ‘JAVA CLASS’, 30, ‘JAVA RESOURCE’,
32, ‘INDEXTYPE’, 33, ‘OPERATOR’,
34, ‘TABLE SUBPARTITION’, 35, ‘INDEX SUBPARTITION’,
40, ‘LOB PARTITION’, 41, ‘LOB SUBPARTITION’,
42, ‘MATERIALIZED VIEW’,
43, ‘DIMENSION’,
44, ‘CONTEXT’, 46, ‘RULE SET’, 47, ‘RESOURCE PLAN’,
48, ‘CONSUMER GROUP’,
51, ‘SUBSCRIPTION’, 52, ‘LOCATION’,
55, ‘XML SCHEMA’, 56, ‘JAVA DATA’,
57, ‘SECURITY PROFILE’, 59, ‘RULE’,
62, ‘EVALUATION CONTEXT’,’UNDEFINED’
) object_type
, lob.kglnaobj object_name
, pn.kglpnmod lock_mode_held
, pn.kglpnreq lock_mode_requested
, ses.sid
, ses.serial#
, ses.username
from v$session_wait vsw
, x$kglob lob
, x$kglpn pn
, v$session ses
where vsw.event = ‘library cache lock’
and vsw.p1raw = lob.kglhdadr
and lob.kglhdadr = pn.kglpnhdl
and pn.kglpnmod != 0
and pn.kglpnuse = ses.saddr
/

[mpunganParticipante]

Valeu pela força, mas já resolvemos o problema. O problema era o paralelismo no processe de rollback. Alteramos o parametro abaixo

alter system fast_start_parallel_rollback=false scope=spfile;

Alguns selects que nos ajudaram:

select usn, state, undoblockstotal “Total”, undoblocksdone “Done”, undoblockstotal-undoblocksdone “ToDo”,
decode(cputime,0,’unknown’,sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) “Estimated time to complete”
from v$fast_start_transactions;

select * from gv$active_session_history;

select ktuxesiz from sys.x$ktuxe where ktuxesta=’ACTIVE’ and ktuxecfl=’DEAD’;

Obrigado.

[Autor]

Posts