Oracle Supplemental 补全日志介绍

本文涉及的产品
日志服务 SLS,月写入数据量 50GB 1个月
简介:

Oracle补全日志(Supplemental logging)特性因其作用的不同可分为以下几种:最小(Minimal),支持所有字段(all),支持主键(primary key),支持唯一键(unique),支持外键(foreign key)。包括LONG,LOB,LONG RAW及集合等字段类型均无法利用补全日志。

最小(Minimal)补全日志开启后可以使得logmnr工具支持链式行,簇表和索引组织表。可以通过以下SQL检查最小补全日志是否已经开启:

SELECT supplemental_log_data_min FROM v$database;

若结果返回YES或IMPLICIT则说明已开启最小补全日志,当使用ALL,PRIMARY,UNIQUE或FOREIGN补全日志时最小补全日志默认开启(即检查结果为IMPLICIT)。

一般情况下我们在使用逻辑备库时启用主键和惟一键的补全日志,而有时表上可能没有主键,惟一键或唯一索引;我们通过以下实验总结这种情况下Oracle的表现。

首先建立相关的测试表:

alter database add supplemental log data (primary key,unique index) columns ;

create table test (t1 int , t2 int ,t3 int ,t4 int );

alter table test add constraint pk_t1 primary key (t1); --添加主键

随后使用循环插入一定量的数据

update test set t2=10;       commit;   -- 更新数据

使用LOGMNR工具分析之前的操作,可以看到REDO中记录的SQL形式如下:

update "SYS"."TEST" set "T2" = '10' where "T1" = '64' and "T2" = '65' and ROWID = 'AAAMiSAABAAAOhiAA/';

其中where字句后分别记录了主键值,被修改字段的值和原行的ROWID。

现在我们将原表上的主键去掉来观察。

alter table test drop constraint pk_t1 ;

update test set t2=11;       commit;   -- 更新数据

使用LOGMNR分析可以发现,REDO中的SQL记录如下:

update "SYS"."TEST" set "T2" = '11' where "T1" = '1' and "T2" = '10' and "T3" = '3' and "T4" = '4' and ROWID = 'AAAMiSAABAAAOhiAAA';

当没有主键的情况下,where子句后记录了所有列值和ROWID。

以下实验在存在唯一索引情况下的表现

create unique index pk_t1 on test(t1);

update test set t2=15; commit;

使用LOGMNR分析可以发现,REDO中的SQL记录如下:

update "SYS"."TEST" set "T2" = '15' where "T1" = '9' and "T2" = '11' and "T3" = '11' and "T4" = '12' and ROWID = 'AAAMiSAABAAAOhiAAI';

以上是t1列有唯一索引但不限定not null的情况,下面我们加上not null限制

alter table test modify t1 not null;

update test set t2=21; commit;

使用LOGMNR分析可以发现,REDO中的SQL记录如下:

update "SYS"."TEST" set "T2" = '21' where "T1" = '2' and "T2" = '15' and ROWID = 'AAAMiSAABAAAOhiAAB';

如以上SQL所示,在存在唯一索引的情况下where子句后仍记录了所有列和ROWID;在存在唯一索引和非空约束的情况下表现与存在主键的情况一致。

当某个表上的列数量较多时且没有主键或唯一索引和非空约束的情况下,开启补全日志可能导致重做日志总量大幅提高。

首先建立一个存在250列的表:

Drop table test;

create table test (

t1 varchar2(5),

t2 varchar2(5),

t3 varchar2(5),

t4 varchar2(5),  …t250 varchar2(5))

insert into test values ('TEST','TEST' ……);   commit; --将255个列填入数据

alter database drop supplemental log data (primary key,unique index) columns;  --关闭补全日志

set autotrace on;

update test set t2='BZZZZ' where t1='TEST'; commit;

可以从自动跟踪信息中看到,本条更新产生了516的重做量。

alter database add supplemental log data (primary key,unique index) columns;  --重新开启补全日志

update test set t2='FSDSD' where t1='TEST';

跟踪信息显示产生了3044的重做量。

补全日志因作用域的不同又可分为数据库级的和表级的。表级补全日志又可以分为有条件的和无条件的。有条件限制的表级补全日志仅在特定列被更新时才会起作用,有条件限制的表级补全日志较少使用,这里我们不做讨论。

下面我们来观察无条件限制表级补全日志的具体表现:

alter database drop supplemental log data (primary key,unique index) columns;

alter table test add supplemental log data (primary key,unique index) columns;

update test set t2='ZZZZZ'; commit;

使用LOGMNR工具查看redo中的SQL:
update "SYS"."TEST" set "T2" = 'ZZZZZ' where "T1" = 'TEST' and "T2" = 'AAAAA' and "T3" = 'TEST'………

可以发现where子句之后包含了所有列值。

delete test; commit;

使用LOGMNR工具查看redo中的SQL:

delete from "SYS"."TEST" where "T1" = 'TEST' and "T2" = 'ZZZZZ' and "T3" = 'TEST' and "T4" = 'TEST' and "T5" ……

delete操作同样在where子句之后包含了所有列值。

又我们可以针对表上字段建立特定的补全日志组,以减少where子句后列值的出现。

alter table test drop supplemental log data (primary key,unique index) columns;  --关闭表上原先的补全日志

alter table test add supplemental log group test_lgp (t1 ,t2,t3,t4,t5,t6,t12,t250) always; --创建补全日志组

update test set t2='XXXXX' ; commit;

使用LOGMNR工具查看redo中的SQL:

update "SYS"."TEST" set "T2" = 'XXXXX' where "T1" = 'TEST' and "T2" = 'TEST' and "T3" = 'TEST' and "T4" = 'TEST' and "T5" = 'TEST' and "T6" = 'TEST' and "T12" = 'TEST' and "T250" = 'TEST' and ROWID = 'AAAMieAABAAAOhnAAA';

如上所示重做日志中正确地显示了UPDATE操作中用户指定的字段值。

delete test;

使用LOGMNR工具查看redo中的SQL:

delete from "SYS"."TEST" where "T1" = 'TEST' and "T2" = 'XXXXX' and "T3" = 'TEST' ……

delete操作在重做日志中仍然保留了所有列值。

针对字段较多的表,我们在能够以多个列保证数据唯一性且非空的情况下(即应用概念上的主键)来指定表上的补全日志组,以减少update操作时所产生的重做日志,而对于delete操作则无法有效改善。



本文转自maclean_007 51CTO博客,原文链接:http://blog.51cto.com/maclean/1276808

相关实践学习
【涂鸦即艺术】基于云应用开发平台CAP部署AI实时生图绘板
【涂鸦即艺术】基于云应用开发平台CAP部署AI实时生图绘板
目录
打赏
0
0
0
0
347
分享
相关文章
问题出在Debezium Oracle Connector的日志解析器上
问题出在Debezium Oracle Connector的日志解析器上
240 2
Mysql、Oracle审计日志的开启
通过上述步骤,可以在 MySQL 和 Oracle 数据库中启用和配置审计日志。这些日志对于监控数据库操作、提高安全性和满足合规性要求非常重要。确保正确配置审计参数和策略,定期查看和分析审计日志,有助于及时发现并处理潜在的安全问题。
365 11
【赵渝强老师】Oracle的控制文件与归档日志文件
本文介绍了Oracle数据库中的控制文件和归档日志文件。控制文件记录了数据库的物理结构信息,如数据库名、数据文件和联机日志文件的位置等。为了保护数据库,通常会进行控制文件的多路复用。归档日志文件是联机重做日志文件的副本,用于记录数据库的变更历史。文章还提供了相关SQL语句,帮助查看和设置数据库的日志模式。
220 1
【赵渝强老师】Oracle的控制文件与归档日志文件
【赵渝强老师】Oracle的参数文件与告警日志文件
本文介绍了Oracle数据库的参数文件和告警日志文件。参数文件分为初始化参数文件(PFile)和服务器端参数文件(SPFile),在数据库启动时读取并分配资源。告警日志文件记录了数据库的重要活动、错误和警告信息,帮助诊断问题。文中还提供了相关视频讲解和示例代码。
213 1
【赵渝强老师】Oracle的联机重做日志文件与数据写入过程
在Oracle数据库中,联机重做日志文件记录了数据库的变化,用于实例恢复。每个数据库有多组联机重做日志,每组建议至少有两个成员。通过SQL语句可查看日志文件信息。视频讲解和示意图进一步解释了这一过程。
143 0
实时计算 Flink版产品使用合集之从Oracle数据库同步数据时,checkpoint恢复后无法捕获到任务暂停期间的变更日志,如何处理
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
"揭秘!一键解锁Oracle日志清理魔法,让海量归档日志无处遁形,守护数据库健康,告别磁盘空间告急噩梦!"
【8月更文挑战第9天】随着Oracle数据库在企业应用中的普及,归档日志管理对保持数据库健康至关重要。归档日志记录所有更改,对数据恢复极为重要,但也可能迅速占用大量磁盘空间影响性能。利用Oracle提供的RMAN工具,可通过编写Shell脚本来自动清理归档日志。脚本包括设置环境变量、连接数据库、检查和删除指定时间前的日志,并记录执行情况。通过Cron作业定时运行脚本,可有效管理日志文件,确保数据库稳定运行。
372 7
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等