mysql数据库hang住_数据库开启最小补充日志hang住

根据MOS 406498.1创建输出Trace文件

conn/ assysdba

SQL> select * from v$diag_info where name='Default Trace File';

INST_ID NAME VALUE--------------------------------------------------------------------------------

1Default Trace File

C:\WIN_ORACLE_11_DATABASE\APP\diag\rdbms\win11\win11\trace\win11_ora_6524.trc

SQL> alter session set tracefile_identifier='SUPP';

SQL> select * from v$diag_info where name='Default Trace File';

INST_ID NAME VALUE--------------------------------------------------------------------------------

1Default Trace File

C:\WIN_ORACLE_11_DATABASE\APP\diag\rdbms\win11\win11\trace\win11_ora_6524_SUPP.trc

alter sessionset max_dump_file_size=unlimited;

alter sessionset events '32593 errorstack(3) systemstate(266)';

alter database add supplemental log data;

SQL>alter database drop supplemental log data;

3)本次实际测试，并未同MOS说明，观察到Trace文件，本次是通过与常规行锁TX contention处理方式相同。

通过v$session找到被阻塞的session

INST_ID SID SERIAL# USERNAME STATUS MACHINE SQL_ID EVENT s LAST_CALL_ET------- ------ ------- -------------------- ---------- -------------------- -------------------- ------------------------------ ------- ------------

1 240 33 SYS ACTIVE WORKGROUP\ZHUOYANG 1u5y5b3gmh4rn enq: TX - contention 108 84

1 419 21 SYS ACTIVE WORKGROUP\ZHUOYANG 9gw8kwb4ajm6t SQL*Net message from client 626 0本次可以发现，执行删除drop supplemental操作会话被hang住

SQL> select sql_text from v$sql where sql_id='1u5y5b3gmh4rn';

SQL_TEXT------------------------------------------------------alter database drop supplemental log data

SQL> select BLOCKING_SESSION from v$session where sid=240 and serial#=33;

BLOCKING_SESSION----------------

182

查询阻塞源头session信息

selectINST_ID

,sid

,serial#

,USERNAME

,STATUS

,MACHINE

,SQL_ID

,EVENT

,(sysdate-LOGON_TIME)*86400 as "s",LAST_CALL_ETfrom gv$session where sid=182;

INST_ID SID SERIAL# USERNAME STATUS MACHINE SQL_ID EVENT s LAST_CALL_ET------- ------ ------- -------------------- ---------- -------------------- -------------------- ------------------------------ ------- ------------

1 182 47 SCOTT INACTIVE WORKGROUP\ZHUOYANG SQL*Net message from client 2419 734SQL> select STATUS,START_TIME from v$transaction where ses_addr=(select saddr from v$session where sid=182 and serial#=47);

STATUS START_TIME---------- --------------------ACTIVE09/20/19 09:41:37SQL> alter system kill session '182,47'immediate;

系统已更改。

SQL> select database_role,SUPPLEMENTAL_LOG_DATA_MIN fromv$database;

DATABASE_ROLE SUPPLEME---------------- --------PRIMARY YES

SQL>alter database drop supplemental log data;

数据库已更改。