数据库开启最小补充日志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()

SQL> insert into a values();

已创建  行。

Sess2   Hang住

C:\Users\Thinkpad>sqlplus / as sysdba

Oracle Database 11g Enterprise Edition Release 11.2.0.4. - 64bit Production

SQL> alter database add supplemental log data;

Fri Sep  ::

alter database add supplemental log data

SUPLOG: Previous supplemental logging attributes at scn =

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 =

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

--------------------------------------------------------------------------------

          Default 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

--------------------------------------------------------------------------------

          Default Trace File

C:\WIN_ORACLE_11_DATABASE\APP\diag\rdbms\win11\win11\trace\win11_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

------- ------ ------- -------------------- ---------- -------------------- -------------------- ------------------------------ ------- ------------

                 SYS                  ACTIVE     WORKGROUP\ZHUOYANG   1u5y5b3gmh4rn        enq: TX - contention

                 SYS                  ACTIVE     WORKGROUP\ZHUOYANG   9gw8kwb4ajm6t        SQL*Net message from client

本次可以发现，执行删除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= and serial#=;

BLOCKING_SESSION

----------------

             182
查询阻塞源头session信息

 select

INST_ID

,sid

,serial#

,USERNAME

,STATUS

,MACHINE

,SQL_ID

,EVENT

,(sysdate-LOGON_TIME)* as "s"

,LAST_CALL_ET

 from gv$session where sid=;

INST_ID    SID SERIAL# USERNAME             STATUS     MACHINE              SQL_ID               EVENT                                s LAST_CALL_ET

------- ------ ------- -------------------- ---------- -------------------- -------------------- ------------------------------ ------- ------------

                 SCOTT                INACTIVE   WORKGROUP\ZHUOYANG                        SQL*Net message from client

SQL>  select STATUS,START_TIME from v$transaction where ses_addr=(select saddr from v$session where sid= and serial#=);

STATUS     START_TIME

---------- --------------------

ACTIVE     // ::

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= and serial#=);
事物与会话进行对应

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