适用:
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)