LOGMINER ENHANCEMENTS Objectifs : Limiter les cas de - PowerPoint PPT Presentation

Oracle upg adm 9i Claude DA COSTA Chap 3 LOGMINER Enhcts Page 1/14 LOGMINER ENHANCEMENTS Objectifs : � Limiter les cas de désynchronisation entre le dictionnaire de données (fournisseur d'infos de translation des données binaires en données lisibles), et les informations lues dans les redo log online et les archives. � Présenter les ordres DDL, de façon explicite. Mise en oeuvre � A la construction classique de "flat dico" DBMS_LOGMNR_D.BUILD( locname dictionary_filename filname dictionary_location � on identifie l'option "flat dico" comme une parmi d'autres options => dbms_logmnrd.store_in_flat_file ou � on ajoute la possibilité d'enregistrer le dico en redo log options => dbms_logmnrd.store_in_redo_logs ou � on ne préçise rien, en prévoyant d'utiliser le dico actuel, au start options => � A l'analyse, on préçise l'origine des données dico DBMS_LOGMNR.START_LOGMNR( � dico en redo logs options => dbms_logmnr.dict_from_redo_logs � dico actuel options => dbms_logmnr.dict_from_online_catalog � on ne préçise rien implique "flat dico" options => plus quelques options supplémentaires � retour des seuls DMLs commités options => dbms_logmnr.commited_data_only � saut des enregs corrompus en redo (ça n'est pas le défaut) options => dbms_logmnr.skip_corruption � raffraichissement automatique du "flat dico" options => dbms_logmnr.ddl_dict_tracking � divers présentation : ne pas mettre ";" à la fin options => dbms_logmnr.no_sql_delimiter � divers présentation : format ddl/dml élaboré options => dbms_logmnr.print_pretty_sql

Oracle upg adm 9i Claude DA COSTA Chap 3 LOGMINER Enhcts Page 2/14 LES SUPPLEMENTAL DATAS

Oracle upg adm 9i Claude DA COSTA Chap 3 LOGMINER Enhcts Page 3/14 RAPPEL RAPPEL : Logminer � permet d'obtenir les ordres DML passés, à partir des enregistrements d'archives log et redo online. � élabore les ordres DML INVERSES. SAVOIR : � Logminer a besoin de connaître les noms d'objets et de colonnes (meta data) inscrits dans le dictionnaire de données. La raison en est que les informations inscrites en enregistrements redo contiennent des identifieurs numériques. � L'opération consistant à traduire les valeurs binaires en noms usuels est appelée "translation. En l'absence de données correspondantes dans le dictionnaire, logminer retournera les seules valeurs binaires. � Par exemple, au lieu de l'orde SQL suivant INSERT INTO emp(name, salary) VALUES ('John Doe', 50000); � Logminer publiera (dans V$LMNR_CONTENTS(sql_redo) ) insert into Object#2581(col#1, col#2) values (hextoraw('4a6f686e20446f65'), hextoraw('c306'));"

Oracle upg adm 9i Claude DA COSTA Chap 3 LOGMINER Enhcts Page 4/14 LES DONNEES ADITIONNELLES EN REDO LOG SAVOIR : Les enregistrements redolog ne sont pas prévus à l'origine pour un tel service utilisateurs. Ils ne contiennent pas en standard la totalité des informations nécessaires à la construction des ordres SQL redo et undo. OBTENIR DES INFOS NON INCLUSES PAR DEFAUT DANS LES ENREG REDO LOG By default, the information stored in the SQL_UNDO and alter database SQL_REDO columns of the V$LOGMNR_CONTENTS view include the ROWID, but not other columns. ADD SUPPLEMENTAL LOG It is often desirable to identify rows based on their primary key value or some other combination of values. primary key ( In Oracle9i, LogMiner allows you to store additional DATA COLUMNS ) unique index information by creating supplemental redo log groups. When a supplemental redo log group is created, then the additional information is viewable through LogMiner. OBTENIR LE CONTENU DE COLONNES PRECISES UTILISATEUR MEME SI NON MISES A JOUR LORS D'UNE MODIFICATION SUR LA TABLE QUI LES CONTIENT You can create a supplemental redo log for a single table alter table using the ALTER TABLE statement ADD SUPPLEMENTAL LOG ALWAYS or for the entire database using the , ALTER DATABASE statement. ( GROUP groupname colname ) If you later decide that this additional logging is not necessary, you can use the DROP SUPPLEMENTAL LOG DATA clause SUPPRESSION DU SUPPLEMENTAL LOGGING with either the alter database ALTER TABLE or ALTER DATABASE statement to discontinue the logging of this additional information. DRO P SUPPLEMENTAL LOG DATA alter table

Oracle upg adm 9i Claude DA COSTA Chap 3 LOGMINER Enhcts Page 5/14 LES DONNEES ADITIONNELLES EN REDO LOG, ISSUES DE LA DATABASE Database Supplemental Logging There are two types of database supplemental logging: minimal and identification key logging. Minimal supplemental logging logs the minimal amount of information needed for LogMiner to identify, group, and merge the REDO operations associated with DML changes. It ensures that LogMiner (and any products building on LogMiner technology) have sufficient information to support chained rows and various storage arrangements such as cluster tables. In most situations, you should at least enable minimal supplemental logging. To do so, execute the following statement: SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA Identification key logging enables database-wide before-image logging of primary keys or unique indexes (in the absence of primary keys) for all updates. With this type of logging, an application can identify updated rows logically rather than resorting to ROWIDs. Note: In LogMiner release 9.0.1, minimal supplemental logging was the default behavior. In release 9.2, the default is no supplemental logging. It must be specifically enabled. Identification key logging is necessary when supplemental log data will be the source of change in another database, such as a logical standby. To enable identification key logging, execute the following statement: SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS; This statement results in all primary key values, database-wide, being logged regardless of whether or not any of them are modified. If a table does not have a primary key, but has one or more non-null unique key constraints, one of the constraints is chosen arbitrarily for logging as a means of identifying the row getting updated. If the table has neither a primary key nor a unique index, then all columns except LONG and LOB are supplementally logged. Therefore, Oracle Corporation recommends that when you use supplemental logging, all or most tables be defined to have primary or unique keys. To disable either minimal or identification key logging, execute the following statement. SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA; Usage Notes for Identification Key Logging Keep the following in mind when you use identification key logging: � Identification key logging is not required for delete operations because DELETE statements contain all the column values required to identify a row. � If the database is open when you enable identification key logging, all DML cursors in the cursor cache are invalidated. This can have a performance impact until the cache is repopulated.

Oracle upg adm 9i Claude DA COSTA Chap 3 LOGMINER Enhcts Page 6/14 LES DONNEES ADITIONNELLES EN REDO LOG, ISSUES DE LA TABLE Table Supplemental Logging Table supplemental logging uses log groups to log supplemental information. There are two types of log groups: � Unconditional log groups - The before images of specified columns are logged any time the table is updated, regardless of whether the update affected any of the specified columns. This is sometimes referred to as an ALWAYS log group. � Conditional log groups - The before images of all specified columns are logged only if at least one of the columns in the log group is updated. Unconditional Log Groups To enable supplemental logging that uses unconditional log groups, use the ALWAYS clause as shown in the following example: SQL> ALTER TABLE scott.emp ADD SUPPLEMENTAL LOG GROUP emp_parttime ( empno , ename , deptno ) ALWAYS; This creates a log group named emp_parttime on scott.emp that consists of the columns empno, ename, and deptno. These columns will be logged every time an UPDATE statement is executed on scott.emp , regardless of whether or not the update affected them. If you wanted to have the entire row image logged any time an update was made, you could create a log group that contained all the columns in the table. Conditional Log Groups To enable supplemental logging that uses conditional log groups, omit the ALWAYS clause from your ALTER TABLE statement, as shown in the following example: SQL> ALTER TABLE scott.emp ADD SUPPLEMENTAL LOG GROUP emp _fulltime ( empno , ename , deptno ); This creates a log group named emp_fulltime on scott.emp. Just like the previous example, it consists of the columns empno, ename, and deptno. But because the ALWAYS clause was omitted , before images of the columns will be logged only if at least one of the columns is updated . Usage Notes for Log Groups Keep the following in mind when you use log groups: � A column can belong to more than one log group. However, the before image of the columns gets logged only once. � Redo logs do not contain any information about which log group a column is part of or whether a column’s before image is being logged because of log group logging or identification key logging. � If you specify the same columns to be logged both conditionally and unconditionally, the columns are logged unconditionally.