[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一樣可以正常開啟附加日志。

總結

以上是生活随笔為你收集整理的[20160813]12c开启附加日志问题.txt的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。