注册

执行两条insert语句,其中一条违反表唯一性约束条件后,数据库中有一条数据,通过查询v$LOGMNR_CONTENTS日志恢复时恢复0条数据。

阿彬 2024/08/14 394 1 已解决

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:DM8
【操作系统】:银河麒麟v10(基本等同于CentOS 7)
【CPU】:Hygon Dhyana Processor
【问题描述】*:执行两条insert语句,其中一条违反表唯一性约束条件后,数据库中有一条数据,通过查询v$LOGMNR_CONTENTS日志恢复时恢复0条数据。请大佬帮忙解答。
信息不足随时喊我补充
【操作步骤】如下:

(1)创建一张含有唯一性索引的表,create table SYSDBA.ALL_DATATYPES_TABLE(
col_bigint BIGINT,
col_INTEGER INTEGER,
col3 TINYINT,
col4 DECIMAL(12,4),
col5 NUMERIC(8,3),
col6 FLOAT,
col8 CHAR(1),
col9 VARCHAR2(255),
col10 CLOB,
col14 BINARY,
col15 VARBINARY,
col152 VARBINARY(4000),
col16 BLOB,
col17 DATE,
col18 DATETIME,
col19 TIMESTAMP,
col21 ROWID,
col22 XMLType,
col24 SMALLINT,
col25 DOUBLE,
col27 TEXT,
col34 BIT,
col36 TIME,
col37 NCLOB(15),
col38 DEC(13,3),
col39 NCHAR(10),
col40 VARCHAR2(170),
col41 NVARCHAR2(20),
col42 NUMBER PRIMARY KEY,
col421 NUMBER(5,2),
col43 LONG,
col44 BINARY_FLOAT,
col45 BINARY_DOUBLE,
col46 TIMESTAMP WITH TIME ZONE,
col47 INTERVAL YEAR TO MONTH,
col48 INTERVAL DAY TO SECOND,
col49 RAW(10),
col50 REAL,
col51 BFILE,
col53 TIMESTAMP WITH LOCAL TIME ZONE
);

(2)执行下面3句操作
insert into SYSDBA.ALL_DATATYPES_TABLE(col42) values('123456818');
insert into SYSDBA.ALL_DATATYPES_TABLE(col42) values('123456818');
commit;
image.png
(3)select 数据库
select col42 from SYSDBA.ALL_DATATYPES_TABLE where col42=123456818;
image.png
(4)在当前场景下,我要通过查询v$LOGMNR_CONTENTS日志,将表SYSDBA.ALL_DATATYPES_TABLE中数据恢复到其他数据库
查询结果如下:
SQL> select OPERATION_CODE,ROLL_BACK,SQL_REDO,SCN,XID,((timestamp - to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss'))*86400) as time,RS_ID,RBASQN,RBABLK,RBABYTE,SSN,ROW_ID,DATA_OBJ#,CSF from v$LOGMNR_CONTENTS where scn>=8285265 ORDER BY SCN ASC;

行号 OPERATION_CODE ROLL_BACK


SQL_REDO
---------------------------------------------------------------------------------------------------
SCN XID TIME RS_ID RBASQN RBABLK
-------------------- ------------------ ------------------------- ----- ----------- -----------
RBABYTE SSN ROW_ID DATA_OBJ# CSF
----------- ----------- ------------------ ----------- -----------
1 6 0
NULL
8285272 0x000000000021BCD1 1.723619250000000E+09 4 0 2930600
191 0 NULL NULL 0

行号 OPERATION_CODE ROLL_BACK


SQL_REDO
---------------------------------------------------------------------------------------------------
SCN XID TIME RS_ID RBASQN RBABLK
-------------------- ------------------ ------------------------- ----- ----------- -----------
RBABYTE SSN ROW_ID DATA_OBJ# CSF
----------- ----------- ------------------ ----------- -----------
2 1 0
INSERT INTO "SYSDBA"."ALL_DATATYPES_TABLE"("COL_BIGINT", "COL_INTEGER", "COL3", "COL4", "COL5", "COL6", "COL8", "COL9", "COL10", "COL14", "COL15", "COL152", "COL16", "COL17", "COL18", "COL19", "COL21", "COL22", "COL24", "COL25", "COL27", "COL34", "COL36", "COL37", "COL38", "COL39", "COL40", "COL41", "COL42", "COL421", "COL43", "COL44", "COL45", "COL46", "COL47", "COL48", "COL49", "COL50", "COL51", "COL53") VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 123456818, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
8285274 0x000000000021BCD1 1.723619250000000E+09 5 0 2930600
321 0 AAAAAAAAAAAAAAAkwI 1059 0

行号 OPERATION_CODE ROLL_BACK


SQL_REDO
---------------------------------------------------------------------------------------------------
SCN XID TIME RS_ID RBASQN RBABLK
-------------------- ------------------ ------------------------- ----- ----------- -----------
RBABYTE SSN ROW_ID DATA_OBJ# CSF
----------- ----------- ------------------ ----------- -----------
3 36 1
NULL
8285279 0x000000000021BCD1 1.723619258000000E+09 7 0 2930608
540 0 NULL NULL 0

行号 OPERATION_CODE ROLL_BACK


SQL_REDO
---------------------------------------------------------------------------------------------------
SCN XID TIME RS_ID RBASQN RBABLK
-------------------- ------------------ ------------------------- ----- ----------- -----------
RBABYTE SSN ROW_ID DATA_OBJ# CSF
----------- ----------- ------------------ ----------- -----------
4 36 0
NULL
8285280 0x000000000021BCD1 1.723619258000000E+09 6 0 2930608
594 0 NULL NULL 0

行号 OPERATION_CODE ROLL_BACK


SQL_REDO
---------------------------------------------------------------------------------------------------
SCN XID TIME RS_ID RBASQN RBABLK
-------------------- ------------------ ------------------------- ----- ----------- -----------
RBABYTE SSN ROW_ID DATA_OBJ# CSF
----------- ----------- ------------------ ----------- -----------
5 7 0
NULL
8285282 0x000000000021BCD1 1.723619282000000E+09 9 0 2930616
172 0 NULL NULL 0

已用时间: 00:00:06.179. 执行号:758479230.
(5)那么这里就出现了问题。
行号2 insert
行号3 rollback 且 ROLL_BACK ==1,恢复时抛弃这条insert
行号4 rollback 且 ROLL_BACK ==0,忽略
行号5 commit.本来该此行和行号2共同组合,恢复这条insert的,结果遇到行号3已经抛弃上面的内容了,导致最终恢复了0行,数据缺失。

回答 0
暂无回答
扫一扫
联系客服