用例子说明Supplemental Logging对LogMiner的作用

最新推荐文章于 2023-05-31 12:16:06 发布

DataVoyager

最新推荐文章于 2023-05-31 12:16:06 发布

阅读量136

点赞数

文章标签： sql dba 数据库 ogg

适用：

Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
*** Checked for relevance on 29-Jan-2015 ****

目标

Supplemental Logging :

redo log file通常被用于instance recovery 和media recovery，这些操作所需要的data已经自动被记录到redo log file中。但是，一个基于redo的应用程序可能需要额外的column被记录(logged)到redo log中。这些额外列的logging的处理被称为supplemental logging。其他操作可能会依赖于supplemental logging，比如impdp使用哪种模式(Direct_Path模式或者External_Table模式)也与supplemental logging有关（请参见552424.1）

请参考下面提到的与Supplemental Logging有关的两点

你必须在生成logfile之前（该logfile被LogMiner分析）启用Supplemental Logging

当你启用Supplemental Logging之后，额外的信息被记录到redo stream中，以便使redo log file中的信息对您有用。因此，您至少必须启用minimal supplemental logging，如下面的SQL语句所示

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

为了确定supplemental logging是否被启用，请查询v$database,如下所示：

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

上述查询结果若是返回YES或者IMPLICIT，就表示minimal supplemental logging被启用

您可以使用如下语句来关闭Supplemental Logging：

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

默认情况下，Oracle数据库不会提供任何的Supplemental Logging，这意味着在默认情况下LogMiner不可用。

让我们举一个例子，logminer dictionary使用Online Catalog option

Part A : Shows result from V$LOGMNR_CONTENTS when supplemental logging is turned OFF (default option )

Part B : Shows result from V$LOGMNR_CONTENTS when supplemental logging is turned ON.

解决方案：

Part A :

Shows result from V$LOGMNR_CONTENTS when supplemental logging is turned OFF (default option )

-----------------------------------------------------------------------------------------------------------------
Example assumes that database is in ARCHIVE LOG MODE

SQL> connect / as sysdba
Connected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
SQL> alter system switch logfile;

System altered.

SQL> SELECT NAME FROM V$ARCHIVED_LOG
2 WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);

NAME
--------------------------------------------------------------------------------
<ORACLE_HOME>\FLASH_RECOVERY_AREA\<SID>\ARCHIVELOG\<DIR>\<FILENAME1>.ARC


SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
NO

SQL> connect scott/tiger
Connected.


SQL> create table test ( n number );
Table created.

SQL> insert into test values ( 1);
1 row created.

SQL> insert into test values (2);
1 row created.

SQL> commit;
Commit complete.

SQL> connect / as sysdba
Connected.

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);

NAME
-----------------------------------------------------------------------------------------------------------------------------------------
<ORACLE_HOME>\FLASH_RECOVERY_AREA\<SID>\ARCHIVELOG\<DIR>\<FILENAME2>.ARC

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
> LOGFILENAME =>'<ORACLE_HOME>\FLASH_RECOVERY_AREA\<SID>\ARCHIVELOG\<DIR>\<FILENAME2>.ARC' , -
> OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

SQL> SELECT SQL_REDO , SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username='SCOTT';

no rows selected

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.

PART B

Shows result from V$LOGMNR_CONTENTS when supplemental logging is turned ON.

The same example as above but with Supplemental Logging turned ON.


SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
YES

SQL> alter system switch logfile;
System altered.

SQL> connect scott/tiger
Connected.

SQL> create table test2 ( n2 number );
Table created.

SQL> insert into test2 values ( 2211);
1 row created.

SQL> insert into test2 values ( 2222);
1 row created.

SQL> commit;
Commit complete.

SQL> connect / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
<ORACLE_HOME>\FLASH_RECOVERY_AREA\<SID>\ARCHIVELOG\<DIR>\<FILENAME3>.ARC

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
> LOGFILENAME => '<ORACLE_HOME>\FLASH_RECOVERY_AREA\<SID>\ARCHIVELOG\<DIR>\<FILENAME3>.ARC' , -
> OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.


SQL> SELECT SQL_REDO , SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username='SCOTT';

SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------

commit;

set transaction read write;

insert into "SCOTT"."TEST2"("N2") values ('2211');
delete from "SCOTT"."TEST2" where "N2" = '2211' and ROWID = 'AAAM7oAAEAAAAGtAAA'

SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
;

insert into "SCOTT"."TEST2"("N2") values ('2222');
delete from "SCOTT"."TEST2" where "N2" = '2222' and ROWID = 'AAAM7oAAEAAAAGtAAB'
;

commit;

23 rows selected.

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.

Please make a note that V$LOGMNR_CONTENTS also shows Data dictionary logs like inserting data into Data Dictionary of Database but have been removed in our example and only relevant data is captured.

注意：

为了LogMiner能正常保存username和session信息，redo需要在至少minimal supplemental database logging被启用的情况下被生成。即使 supplemental logging 被启用，也可能通过实例参数TRANSACTION_AUDITING=FALSE(10.1及其更高版本中的_TRANSACTION_AUDITING=FALSE)将存储在redo中的session信息和username data关闭。该实例参数设置为TRUE时（启用supplemental database logging会隐含将该参数设置为TRUE），Oracle会生成一个特殊的redo record，这个特殊的redo record中包括user logon name、username、the session id、一些操作系统信息、客户端信息。对于每个后续的事务，Oracle生成一个仅仅包括session id的record。这些后续记录链接回first record，这个first record也包括session id.

当该参数设置为FALSE时，Oracle会阻止这个特殊record写入到redo中，这会导致V$LOGMNR_CONTENTS中需要从这个特殊record中获取数据的所有列会报NULL 值

翻译来源：

Effect of Supplemental Logging on LogMiner with Example (Doc ID 750198.1)

DataVoyager

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
用例子说明Supplemental Logging对LogMiner的作用

适用：Oracle Database Cloud Service - Version N/A and laterOracle Database - Enterprise Edition - Version 10.2.0.1 and laterOracle Database Exadata Cloud Machine - Version N/A and laterOracle Cloud Infrastructure - Database Service - Version N/A and lat
复制链接

扫一扫