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

一、场景说明：

客户环境需要部署OGG，同事在数据库中执行添加最小补充日志，会话Hang住

二、环境测试

本次测试环境进行模拟，添加最小补充日志的操作，怎么会被Hang住呢？

2.1 模拟会话hang住

添加最小补充日志测试
SQL> select database_role,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
DATABASE_ROLE    SUPPLEME
---------------- --------
PRIMARY          NO

Sess1
SQL> conn scott/tiger
已连接。
SQL> desc a
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
SQL> insert into a values(1);
已创建 1 行。

Sess2   Hang住
C:UsersThinkpad>sqlplus / as sysdba
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SQL> alter database add supplemental log data;


Fri Sep 20 09:42:14 2019
alter database add supplemental log data
SUPLOG: Previous supplemental logging attributes at scn = 12138772
SUPLOG:  minimal = OFF, primary key = OFF
SUPLOG:  unique = OFF, foreign key = OFF, all column = OFF
SUPLOG:  procedural replication = OFF
SUPLOG: New supplemental logging attributes at scn = 12138772
SUPLOG:  minimal = ON, primary key = OFF
SUPLOG:  unique = OFF, foreign key = OFF, all column = OFF
SUPLOG:  procedural replication = OFF

Sess 1
SQL>rollback; 回滚事物结束
Sess 2   OK
SQL> alter database add supplemental log data;

2.2 尝试问题处理1）将2.1开启最小补充日志，调整为关闭最小补充日志；2)根据MOS文档进行操作

根据MOS 406498.1
创建输出Trace文件
conn / as sysdba 
SQL> select * from v$diag_info where name='Default Trace File';
   INST_ID NAME                   VALUE
--------------------------------------------------------------------------------
         1 Default Trace File
C:WIN_ORACLE_11_DATABASEAPPdiag
dbmswin11win11	racewin11_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
--------------------------------------------------------------------------------
         1 Default Trace File
C:WIN_ORACLE_11_DATABASEAPPdiag
dbmswin11win11	racewin11_ora_6524_SUPP.trc
alter session set max_dump_file_size=unlimited;
alter session set 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     WORKGROUPHUOYANG   1u5y5b3gmh4rn        enq: TX - contention               108           84
      1    419      21 SYS                  ACTIVE     WORKGROUPHUOYANG   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信息

 select 
INST_ID
,sid
,serial#
,USERNAME
,STATUS
,MACHINE
,SQL_ID
,EVENT
,(sysdate-LOGON_TIME)*86400 as "s"
,LAST_CALL_ET
 from gv$session where sid=182;
INST_ID    SID SERIAL# USERNAME             STATUS     MACHINE              SQL_ID               EVENT                                s LAST_CALL_ET
------- ------ ------- -------------------- ---------- -------------------- -------------------- ------------------------------ ------- ------------
      1    182      47 SCOTT                INACTIVE   WORKGROUPHUOYANG                        SQL*Net message from client       2419          734
      
SQL>  select STATUS,START_TIME from v$transaction where ses_addr=(select saddr from v$session where sid=182 and serial#=47);
STATUS     START_TIME
---------- --------------------
ACTIVE     09/20/19 09:41:37


SQL> alter system kill session '182,47' immediate;
系统已更改。
SQL> select database_role,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
DATABASE_ROLE    SUPPLEME
---------------- --------
PRIMARY          YES

SQL>  alter database drop supplemental log data;
数据库已更改。

2.3 事后总结

1)开启关闭最小补充日志，为何会被阻塞，

CAUSE
The statement ALTER DATABASE ADD SUPPLEMENTAL LOG DATA  is waiting for TX lock 

in shared mode when there is any uncommitted transaction. This is the expected behavior.

You can issue ALTER DATABASE ADD SUPPLEMENTAL LOG DATA when the database is open. 
However, OracleDatabase will invalidate all DML cursors in the cursor cache,
 which will have an effect on performance until the cache is repopulated. 
Besides,we also need to wait for the completion of all the in-flight
 transaction so that any redo generated after this DDL would have the right supplemental logging attributes.
存在未提交的事物，开启最小补充日志将处于等待TX lock

2)查询并杀死事物会话或进程

会话与事物对应
SQL>  select STATUS,START_TIME from v$transaction where ses_addr=(select saddr from v$session where sid=182 and serial#=47);
事物与会话进行对应

SQL>select sid,serial#,username,status,event,sql_id from v$session where saddr in(select ses_addr from v$transaction);