[20160813]12c开启附加日志问题.txt
--测试需要要在12c下开启附加日志,遇到一些问题,做1个记录:
1.环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
--我的测试环境有pdb数据库的.
SYS@test> select * from CDB_PDBS;
PDB_ID PDB_NAME DBID CON_UID GUID STATUS CREATION_SCN CON_ID
---------- -------------------- ---------- ---------- -------------------------------- ------------- ------------ ----------
3 TEST01P 1652643119 1652643119 A1EEB4B6462C40349D6EE072862CABA8 NORMAL 2454021 1
2 PDB$SEED 4063864810 4063864810 E328565B49E148BDBA65856218380E9D NORMAL 2256383 1
2.我开始没有注意,在PDB=test01p下执行操作:
SCOTT@test01p> show con_name
CON_NAME
--------
TEST01P
SCOTT@test01p> alter database add supplemental log data;
Database altered.
--发现居然也可以.
SCOTT@test01p> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
-------- --- ---
YES NO NO
SYS@test> show con_name
CON_NAME
---------
CDB$ROOT
SYS@test> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
-------- --- ---
YES NO NO
--可以很好理解都是修改控制文件.如果在pdb取消附加日志:
SCOTT@test01p> alter database drop supplemental log data;
Database altered.
--//test01p
SCOTT@test01p> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
-------- --- ---
YES NO NO
--//CDB$ROOT
SYS@test> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
-------- --- ---
YES NO NO
--可以发现在pdb数据库下无法取消附加日志.
--//CDB$ROOT
SYS@test> alter database drop supplemental log data;
Database altered.
SYS@test> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
-------- --- ---
NO NO NO
--//test01p
SCOTT@test01p> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
-------- --- ---
NO NO NO
--可以发现在pdb下可以打开附加日志,这样应该全部pdb我觉得应该都会打开.但是要取消,仅仅在CDB$ROOT下操作才有效.
3.继续测试:
--顺便建立1张表插入数据.不提交.
--session 1:
SCOTT@test01p> insert into t1(a) values (1);
1 row created.
--切换到另外用户执行;
SCOTT@test01p(243,27)> alter database add supplemental log data;
--可以发现会话挂起.
SYS@test> @ wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
0000000042455100 0000000000000001 00 1111838976 1 0 131 337 539 SQL*Net message to client WAITED SHORT TIME 9 0
0000000054580004 0000000000050018 0000000000006053 1415053316 327704 24659 243 27 768 enq: TX - contention WAITING 33832130 34
--可以发现1个等待事件是enq: TX - contention
--session 1:
SCOTT@test01p> @ s
SCOTT@test01p(10,1671)> @ spid
SID SERIAL# SPID PID P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
10 1671 5720 56 61 alter system kill session '10,1671' immediate;
SYS@test> @ viewlock
SID SERIAL# USERNAME OSUSER MACHINE MODULE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
10 1671 SCOTT XXX\Admini WORKGROUP\ SQL*Plus TM DML(TM) Row-X (SX) None 105275 0 No
strator XXX
10 1671 SCOTT XXX\Admini WORKGROUP\ SQL*Plus TX Transaction Exclusive None 327704 24659 Yes
strator XXX
243 27 SCOTT XXX\Admini WORKGROUP\ SQL*Plus TX Transaction None Share 327704 24659 No 000007FF6161B848
strator XXX
--//没有在pdb下执行, OWNER OBJECT_TYP OBJECT_NAME没有显示.
SYS@test> @xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE C70
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ----------------------------------------------------------------------
5 24 24659 5 2486 1487 26 ACTIVE 1 1 0500180053600000 000007FF5E9E8778 2016-08-13 22:43:26 ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU5_2367258232$' XID 5 24 24659;
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU5_2367258232$';
ALTER SYSTEM DUMP DATAFILE 5 BLOCK 2486;
select 327704,trunc(327704/65536) XIDUSN,mod(327704,65536) XIDSLOT from dual
327704 XIDUSN XIDSLOT
---------- ---------- ----------
327704 5 24
--//这些XIDUSN,XIDSLOT,XIDSQN=5,24,24659都与LOCK_ID1,LOCK_ID2对上.说明打开附加日志需要等待事务结束.
SCOTT@test01p(10,1671)> commit ;
Commit complete.
--session 2执行ok.
4.继续测试:
SYS@test> alter database drop supplemental log data;
Database altered.
--session 1:再次插入数据,不提交:
SCOTT@test01p(10,1671)> insert into t1(a) values (2);
1 row created.
--//CDB$ROOT
SYS@test> alter database add supplemental log data;
Database altered.
--居然ok了.pdb下有事务，不影响cdb执行开启附加日志的功能!!
--//CDB$ROOT
SYS@test> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
-------- --- ---
YES NO NO
--//test01p
SCOTT@test01p(243,27)> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
-------- --- ---
YES NO NO
5.继续测试:
SYS@test> create table t2 (id number);
Table created.
SYS@test> alter database drop supplemental log data;
Database altered.
--//CDB$ROOT
SYS@test> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
-------- --- ---
NO NO NO
--//test01p
SCOTT@test01p(243,27)> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
-------- --- ---
NO NO NO
--session 1:
SYS@test> insert into t2 values (1);
1 row created.
--session 2:
SYS@test> alter database add supplemental log data;
--再次挂起.
--session 1:
SYS@test> @s
SYS@test(131,337)> @spid
SID SERIAL# SPID PID P_SERIAL# C50
------ ---------- ------ ------- ---------- --------------------------------------------------
131 337 7844 53 67 alter system kill session '131,337' immediate;
SYS@test(131,337)> @wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ------ ---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
0000000042455100 0000000000000001 00 1111838976 1 0 131 337 709 SQL*Net message to client WAITED SHORT TIME 12 0
0000000054580004 00000000000E000F 00000000000008D7 1415053316 917519 2263 10 1673 37 enq: TX - contention WAITING 100081106 100
SYS@test(131,337)> @viewlock
SID SERIAL# USERNAME OSUSER MACHINE MODULE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
10 1673 SYS XXX\Admini WORKGROUP\ sqlplus.exe TX Transaction None Share 917519 2263 No 000007FF6161B2D8
strator XXX
131 337 SYS XXX\Admini WORKGROUP\ sqlplus.exe TX Transaction Exclusive None 917519 2263 Yes
strator XXX
131 337 SYS XXX\Admini WORKGROUP\ sqlplus.exe TM DML(TM) Row-X (SX) None 100875 0 SYS TABLE T2 No
strator XXX
SYS@test(131,337)> @xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
14.15.2263
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE C70
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ----------------------------------------------------------------------
14 15 2263 5 1183 762 4 ACTIVE 1 1 0E000F00D7080000 000007FF5E981528 2016-08-13 23:03:02 ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU14_843651722$' XID 14 15 2263;
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU14_843651722$';
ALTER SYSTEM DUMP DATAFILE 5 BLOCK 1183;
select 917519,trunc(917519/65536) XIDUSN,mod(917519,65536) XIDSLOT from dual
917519 XIDUSN XIDSLOT
---------- ---------- ----------
917519 14 15
--说明在有事务下,执行开启附加日志必须等事务提交才ok.我有空给在11g下测试看看,存在事务没提交的情况下,是否打开附加日志会阻塞.
--补充11g下linux的测试：
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
-------- --- ---
NO NO NO
--打开session 1,建立表,插入数据,不提交:
SCOTT@book> create table t2 (id number);
Table created.
SCOTT@book> insert into t2 values (1);
1 row created.
SCOTT@book> @ &r/s
SCOTT@book(101,7171)>
SCOTT@book(101,7171)> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
10.23.57204
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE C70
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ----------------------------------------------------------------------
10 23 57204 3 1761 11682 6 ACTIVE 1 1 0A00170074DF0000 0000000081932FD0 2016-08-15 08:42:10 ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 23 57204;
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 1761;
--session 2:
SYS@book> alter database add supplemental log data;
--挂起！
--session 1:
SCOTT@book(101,7171)> @ &r/viewlock
SID SERIAL# USERNAME OSUSER MACHINE MODULE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
90 5421 SYS oracle gxqyydg4 sqlplus@gxqy TX Transaction None Share 655383 57204 No 00000000851E3C88
ydg4 (TNS V1
-V3)
101 7171 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction Exclusive None 655383 57204 Yes
101 7171 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 96016 0 SCOTT TABLE T2 No
SYS@book> alter database add supplemental log data;
alter database add supplemental log data
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SYS@book> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
-------- --- ---
YES NO NO
--中断，你可以查询发现SUPPLEMENTAL_LOG_DATA_MIN=yes.再次执行1次，估计已经开启，不会有任何操作。
SYS@book> alter database add supplemental log data;
Database altered.
SYS@book> alter database drop supplemental log data;
--挂起！在有事务的情况下也会挂起。
SYS@book> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
-------- --- ---
NO NO NO
--检查发现居然也取消了。
--我12c 使用的windows版本，无法中断。按ctrl+c就退出。
--检查alert*.log如下：
Mon Aug 15 08:43:39 2016
alter database add supplemental log data
SUPLOG: Previous supplemental logging attributes at scn = 13245951124
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 = 13245951124
SUPLOG: minimal = ON, primary key = OFF
SUPLOG: unique = OFF, foreign key = OFF, all column = OFF
SUPLOG: procedural replication = OFF
Mon Aug 15 08:43:43 2016
Incremental checkpoint up to RBA [0x1bd.5d30.0], current log tail at RBA [0x1bd.5e03.0]
Mon Aug 15 08:46:00 2016
ORA-1013 signalled during: alter database add supplemental log data...
Mon Aug 15 08:46:53 2016
alter database add supplemental log data
Completed: alter database add supplemental log data
Mon Aug 15 08:47:42 2016
alter database drop supplemental log data
SUPLOG: Previous supplemental logging attributes at scn = 13245951518
SUPLOG: minimal = ON, primary key = OFF
SUPLOG: unique = OFF, foreign key = OFF, all column = OFF
SUPLOG: procedural replication = OFF
SUPLOG: New supplemental logging attributes at scn = 13245951518
SUPLOG: minimal = OFF, primary key = OFF
SUPLOG: unique = OFF, foreign key = OFF, all column = OFF
SUPLOG: procedural replication = OFF
Mon Aug 15 08:48:13 2016
ORA-1013 signalled during: alter database drop supplemental log data...
--总结：
1.我不知道按ctrl+c，是否真正成功。
2.在11g下有事务的情况下无法开启与关闭附件日志，按ctrl+c中断，查询v$database提示操作成功，是否真有效。
3.12c 下pdb打开附加日志，全局有效。而有事务存在的情况也会挂起。
4.但是存在1个例外(12c)，就是pdb下有事务，cdb一样可以正常开启附加日志。