PL/SQL Packages and Types Reference 47

• DBMS_LOGMNR

  The DBMS_LOGMNR package contains procedures used to initialize the LogMiner tool and to begin and end a LogMiner session.

  See Also:

  Oracle Database Utilities for information about using LogMiner and DBMS_LOGMNR_D for information on the package subprograms that extract a LogMiner dictionary and re-create LogMiner tables in alternate tablespaces

  This chapter contains the following topics:

  • Using DBMS_LOGMNR
    • Security Model
    • Constants
    • Operational Notes
  • Summary of DBMS_LOGMNR Subprograms

  package contains procedures used to initialize the LogMiner tool and to begin and end a LogMiner session. See Also:

  Oracle Database Utilities for information about using LogMiner and DBMS_LOGMNR_D for information on the package subprograms that extract a LogMiner dictionary and re-create LogMiner tables in alternate tablespaces

  This chapter contains the following topics:

  • Using DBMS_LOGMNR
    • Security Model
    • Constants
    • Operational Notes
  • Summary of DBMS_LOGMNR Subprograms

  ---

  Using DBMS_LOGMNR

  • Security Model
  • Constants
  • Operational Notes

  ---

  Security Model

  You must have the role, EXECUTE_CATALOG_ROLE to use the DBMS_LOGMNR package.

  ---

  Constants

  Table 47-1 describes the constants for the ADD_LOGFILE options flag in the DBMS_LOGMNR package.

  Table 47-1  Constants for ADD_LOGFILE Options Flag

  Constant Description

  NEW

  Implicitly calls the DBMS_LOGMNR.END_LOGMNR procedure to end the current LogMiner session and then creates a new session. The new session starts a new list of redo log files to be analyzed, beginning with the redo log file you specify.

  ADDFILE

  Adds the specified redo log file to the list of redo log files to be analyzed. Any attempt to add a duplicate file raises an exception (ORA-01289). This is the default if no options flag is specified.

  Table 47-2 describes the constants for the START_LOGMNR options flag in the DBMS_LOGMNR package.

  Table 47-2  Constants for START_LOGMNR Options Flag

  Constant Description

  COMMITTED_DATA_ONLY

  If set, DML statements corresponding to committed transactions are returned. DML statements corresponding to a committed transaction are grouped together. Transactions are returned in their commit order. Transactions that are rolled back or in-progress are filtered out, as are internal redo records (those related to index operations, management, and so on).

  If this option is not set, all rows for all transactions (committed, rolled back, and in-progress) are returned in the order in which they are found in the redo logs (in order of SCN values).

  SKIP_CORRUPTION

  Directs a select operation on the V$LOGMNR_CONTENTS view to skip any corruptions in the redo log file being analyzed and continue processing. This option works only when a block in the redo log file (and not the header of the redo log file) is corrupt. You should check the INFO column in the V$LOGMNR_CONTENTS view to determine the corrupt blocks skipped by LogMiner. When a corruption in the redo log file is skipped, the OPERATION column contains the value CORRUPTED_BLOCKS, and the STATUS column contains the value 1343.

  DDL_DICT_TRACKING

  If the LogMiner dictionary in use is a flat file or in the redo log files, LogMiner updates its internal dictionary if a DDL event occurs. This ensures that correct SQL_REDO and SQL_UNDO information is maintained for objects that are modified after the LogMiner internal dictionary is built. The database to which LogMiner is connected must be open.

  This option cannot be used in conjunction with the DICT_FROM_ONLINE_CATALOG option and cannot be used when the LogMiner dictionary being used is one that was extracted to a flat file prior to Oracle9i.

  DICT_FROM_ONLINE_CATALOG

  Directs LogMiner to use the current online database dictionary rather than a LogMiner dictionary contained in a flat file or in the redo log files being analyzed.

  This option cannot be used in conjunction with the DDL_DICT_TRACKING option. The database to which LogMiner is connected must be the same one that generated the redo log files.

  EXPect to see a value of 2 in the STATUS column of the V$LOGMNR_CONTENTS view if the table definition in the database does not match the table definition in the redo log file.

  DICT_FROM_REDO_LOGS

  If set, LogMiner expects to find a LogMiner dictionary in the redo log files that were specified. The redo log files are specified with the DBMS_LOGMNR.ADD_LOGFILE procedure or with the DBMS_LOGMNR.START_LOGMNR procedure with the CONTINUOUS_MINE option.

  NO_SQL_DELIMITER

  If set, the SQL delimiter (a semicolon) is not placed at the end of reconstrUCted SQL statements. This is helpful for applications that open a cursor and then execute the reconstructed statements.

  NO_ROWID_IN_STMT

  If set, the ROWID clause is not included in the reconstructed SQL statements. The redo log file may already contain logically unique identifiers for modified rows if supplemental logging is enabled.

  When using this option, you must be sure that supplemental logging was enabled in the source database at the appropriate level and that no duplicate rows exist in the tables of interest. LogMiner does not make any guarantee regarding the uniqueness of logical row identifiers.

  PRINT_PRETTY_SQL

  If set, LogMiner formats the reconstructed SQL statements for ease of reading. These reconstructed SQL statements are not executable.

  CONTINUOUS_MINE

  Directs LogMiner to automatically add redo log files, as needed, to find the data of interest. You only need to specify the first log to start mining, or just the starting SCN or date to indicate to LogMiner where to begin mining logs. You are not required to specify any redo log files explicitly. LogMiner automatically adds and mines the (archived and online) redo log files for the data of interest. This option requires that LogMiner is connected to the same database instance that is generating the redo log files.

  ---

  Operational Notes

  A LogMiner session begins with a call to DBMS_LOGMNR.ADD_LOGFILE or DBMS_LOGMNR.START_LOGMNR (the former if you plan to specify log files explicitly; the latter if you plan to use continuous mining). The session ends with a call to DBMS_LOGMNR.END_LOGMNR. Within a LogMiner session, you can specify the redo log files to be analyzed and the SCN or time range of interest; then you can issue SQL SELECT statements against the V$LOGMNR_CONTENTS view to retrieve the data of interest.

  ---

  Summary of DBMS_LOGMNR Subprograms

  Table 47-3  DBMS_LOGMNR Package Subprograms

  Subprogram Description

  ADD_LOGFILE Procedure

  Adds a redo log file to the existing or newly created list of redo log files for LogMiner to process, so that if a new list is created, this marks the beginning of a LogMiner session

  COLUMN_PRESENT Function

  You can call this function for any row returned from the V$LOGMNR_CONTENTS view to determine if undo or redo column values exist for the column specified by the column_name input parameter to this function

  END_LOGMNR Procedure

  Finishes a LogMiner session

  MINE_VALUE Function

  You can call this function for any row returned from the V$LOGMNR_CONTENTS view to retrieve the undo or redo column value of the column specified by the column_name input parameter to this function

  REMOVE_LOGFILE Procedure

  Removes a redo log file from the list of redo log files for LogMiner to process

  START_LOGMNR Procedure

  Initializes the LogMiner utility and starts LogMiner (unless the session was already started with a call to DBMS_LOGMNR.ADD_LOGFILE)

  ---

  ADD_LOGFILE Procedure

  This procedure adds a file to an existing or newly created list of log files for LogMiner to process.

  Syntax

  DBMS_LOGMNR.ADD_LOGFILE( 
     LogFileName     IN VARCHAR2,
     options         IN BINARY_INTEGER default ADDFILE );
  

  Parameters

  Table 47-4  ADD_LOGFILE Procedure Parameters

  Parameter Description

  LogFileName

  Specifies the name of the redo log file to add to the list of redo log files to be analyzed during this session.

  options

  Does one of the following:

  • Starts a new LogMiner session and a new list of redo log files for analysis (DBMS_LOGMNR.NEW)
  • Adds a file to an existing list of redo log files for analysis (DBMS_LOGMNR.ADDFILE)

  See Table 47-1, " Constants for ADD_LOGFILE Options Flag".

  Usage Notes

  • Before querying the V$LOGMNR_CONTENTS view, you must make a successful call to the DBMS_LOGMNR.START_LOGMNR procedure (within the current LogMiner session).
  • Unless you specify the CONTINUOUS_MINE option, the LogMiner session must be set up with a list of redo log files to be analyzed. Use the ADD_LOGFILE procedure to specify the list of redo log files to analyze.
  • If you are not using the CONTINUOUS_MINE option and you want to analyze more than one redo log file, you must call the ADD_LOGFILE procedure separately for each redo log file. The redo log files do not need to be registered in any particular order.
  • Both archived and online redo log files can be mined.
  • After you have added the first redo log file to the list, each additional redo log file that you add to the list must be associated with the same database and database RESETLOGS SCN as the first redo log file. (The database RESETLOGS SCN uniquely identifies each execution of an ALTER DATABASE OPEN RESETLOGS statement. When the online redo logs are reset, Oracle creates a new and unique incarnation of the database.)
  • To analyze the redo log files from a different database (or a database incarnation with a different database RESETLOGS SCN) than that with which the current list of redo log files is associated, use the END_LOGMNR procedure to end the current LogMiner session, and then build a new list using the ADD_LOGFILE procedure.
  • LogMiner matches redo log files by the log sequence number. Thus, two redo log files with different names but with the same log sequence number will return the ORA-01289 exception. For instance, the online counterpart of an archived redo log file has a different name from the archived redo log file, but attempting to register it with LogMiner after registering the archived counterpart will result in the ORA-01289 exception being returned.

  Exceptions

  • ORA-01284: file <filename> cannot be opened.
  • ORA-01287: file <filename> is from a different database incarnation.
  • ORA-01289: cannot add duplicate log file <filename>.
  • ORA-01290: cannot remove unlisted log file <filename>.
  • ORA-01324: cannot add file <filename> due to DB_ID mismatch.

  ---

  COLUMN_PRESENT Function

  This function is designed to be used in conjunction with the MINE_VALUE function.

  If the MINE_VALUE function returns a NULL value, it can mean either:

  • The specified column is not present in the redo or undo portion of the data.
  • The specified column is present and has a NULL value.

  To distinguish between these two cases, use the COLUMN_PRESENT function, which returns a 1 if the column is present in the redo or undo portion of the data. Otherwise, it returns a 0.

  Syntax

  DBMS_LOGMNR.COLUMN_PRESENT(
       sql_redo_undo      IN  RAW,
       column_name        IN  VARCHAR2 default '') RETURN NUMBER;
  

  Parameters

  Table 47-5  COLUMN_PRESENT Function Parameters

  Parameter Description

  sql_redo_undo

  Specifies either the REDO_VALUE or the UNDO_VALUE column in the V$LOGMNR_CONTENTS view from which to extract data values. See the Usage Notes for more information.

  column_name

  Specifies the fully qualified name (schema.table.column) of the column for which this function will return information.

  Return Values

  Table 47-6 describes the return values for the COLUMN_PRESENT function. The COLUMN_PRESENT function returns 1 if the self-describing record (the first parameter) contains the column specified in the second parameter. This can be used to determine the meaning of NULL values returned by the DBMS_LOGMNR.MINE_VALUE function.

  Table 47-6  Return Values for COLUMN_PRESENT Function

  Return Description

  0

  Specified column is not present in this row of V$LOGMNR_CONTENTS.

  1

  Column is present in this row of V$LOGMNR_CONTENTS.

  Usage Notes

  • To use the COLUMN_PRESENT function, you must have successfully started LogMiner.
  • The COLUMN_PRESENT function must be invoked in the context of a select operation on the V$LOGMNR_CONTENTS view.
  • The COLUMN_PRESENT function does not support LONG, LOB, ADT, or COLLECTION datatypes.
  • The value for the sql_redo_undo parameter depends on the operation performed and the data of interest:
    • If an update operation was performed and you want to know what the value was prior to the update operation, specify UNDO_VALUE.
    • If an update operation was performed and you want to know what the value is after the update operation, specify REDO_VALUE.
    • If an insert operation was performed, typically you would specify REDO_VALUE (because the value of a column prior to an insert operation will always be null).
    • If a delete operation was performed, typically you would specify UNDO_VALUE (because the value of a column after a delete operation will always be null).

  Exceptions

  • ORA-01323: invalid state.

    
    

  • [1] [2] [3] [4] [5] 下一页 
  • 复制本页网址和标题，发送给你QQ/Msn的好友一起分享
    
    
  • PL/SQL Packages and Types Reference 47 相关文章：
  • ·PL/SQL Packages and Types Reference 47
  • PL/SQL Packages and Types Reference 47 相关软件

  上一篇:数据库运行在非归档模式下，数据文件被误删的解决方法

  下一篇:更改数据库SID

  • 特别声明：本站除部分特别声明禁止转载的专稿外的其他文章可以自由转载，但请务必注明出处和原始作
  • 者.文章版权归文章原始作者所有.对于被本站转载文章的个人和网站，我们表示深深的谢意。如果本站转
  • 载的文章有版权问题请联系编辑人员,我们尽快予以更正. 转载请注明来源：http://www.hackhome.com

精品推荐

热点TOP10

特别推荐