[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

---------

CDB$ROOT

SYS@test> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;

--可以很好了解都是修改控制檔案.如果在pdb取消附加日志:

SCOTT@test01p> alter database drop supplemental log data;

--//test01p

--//CDB$ROOT

--可以發現在pdb資料庫下無法取消附加日志.

SYS@test> alter database drop supplemental log data;

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

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

243 27 SCOTT XXX\Admini WORKGROUP\ SQL*Plus TX Transaction None Share 327704 24659 No 000007FF6161B848

--//沒有在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.繼續測試:

--session 1:再次插入資料,不送出:

SCOTT@test01p(10,1671)> insert into t1(a) values (2);

SYS@test> alter database add supplemental log data;

--居然ok了.pdb下有事務，不影響cdb執行開啟附加日志的功能!!

SCOTT@test01p(243,27)> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;

5.繼續測試:

SYS@test> create table t2 (id number);

Table created.

SYS@test> insert into t2 values (1);

--session 2:

--再次挂起.

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

10 1673 SYS XXX\Admini WORKGROUP\ sqlplus.exe TX Transaction None Share 917519 2263 No 000007FF6161B2D8

131 337 SYS XXX\Admini WORKGROUP\ sqlplus.exe TX Transaction Exclusive None 917519 2263 Yes

131 337 SYS XXX\Admini WORKGROUP\ sqlplus.exe TM DML(TM) Row-X (SX) None 100875 0 SYS TABLE T2 No

SYS@test(131,337)> @xid

14.15.2263

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;

--打開session 1,建立表,插入資料,不送出:

SCOTT@book> create table t2 (id number);

SCOTT@book> insert into t2 values (1);

SCOTT@book> @ &r/s

SCOTT@book(101,7171)>

SCOTT@book(101,7171)> @ &r/xid

10.23.57204

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;

SYS@book> alter database add supplemental log data;

--挂起！

SCOTT@book(101,7171)> @ &r/viewlock

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

alter database add supplemental log data

ERROR at line 1:

ORA-01013: user requested cancel of current operation

--中斷，你可以查詢發現SUPPLEMENTAL_LOG_DATA_MIN=yes.再次執行1次，估計已經開啟，不會有任何操作。

SYS@book> alter database drop supplemental log data;

--挂起！在有事務的情況下也會挂起。

--檢查發現居然也取消了。

--我12c 使用的windows版本，無法中斷。按ctrl+c就退出。

--檢查alert*.log如下：

Mon Aug 15 08:43:39 2016

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

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

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: New supplemental logging attributes at scn = 13245951518

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

繼續閱讀

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述