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

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(

dictionary_filename dictionary_location

• n identifie l'option "flat dico" comme une parmi d'autres
• ptions => dbms_logmnrd.store_in_flat_file
• n ajoute la possibilité d'enregistrer le dico en redo log
• ptions => dbms_logmnrd.store_in_redo_logs
• n ne préçise rien, en prévoyant d'utiliser le dico actuel, au start
• ptions =>
• A l'analyse, on préçise l'origine des données dico

DBMS_LOGMNR.START_LOGMNR(

• dico en redo logs
• ptions => dbms_logmnr.dict_from_redo_logs
• dico actuel
• ptions => dbms_logmnr.dict_from_online_catalog
• n ne préçise rien implique "flat dico"
• ptions =>

plus quelques options supplémentaires

• retour des seuls DMLs commités
• ptions => dbms_logmnr.commited_data_only
• saut des enregs corrompus en redo (ça n'est pas le défaut)
• ptions => dbms_logmnr.skip_corruption
• raffraichissement automatique du "flat dico"
• ptions => dbms_logmnr.ddl_dict_tracking
• divers présentation : ne pas mettre ";" à la fin
• ptions => dbms_logmnr.no_sql_delimiter
• divers présentation : format ddl/dml élaboré
• ptions => dbms_logmnr.print_pretty_sql
• u
• u

locname filname

Oracle upg adm 9i Claude DA COSTA Chap 3 LOGMINER Enhcts Page 1/14

LES SUPPLEMENTAL DATAS

Oracle upg adm 9i Claude DA COSTA Chap 3 LOGMINER Enhcts Page 2/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 3/14

( )

alter

database

ADD SUPPLEMENTAL LOG primary key unique index COLUMNS DATA

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.

LES DONNEES ADITIONNELLES EN REDO LOG

OBTENIR DES INFOS NON INCLUSES PAR DEFAUT DANS LES ENREG REDO LOG OBTENIR LE CONTENU DE COLONNES PRECISES UTILISATEUR MEME SI NON MISES A JOUR LORS D'UNE MODIFICATION SUR LA TABLE QUI LES CONTIENT

By default, the information stored in the SQL_UNDO and SQL_REDO columns of the V$LOGMNR_CONTENTS view include the ROWID, but not other columns. It is often desirable to identify rows based on their primary key value or some other combination of values. In Oracle9i, LogMiner allows you to store additional information by creating supplemental redo log groups. When a supplemental redo log group is created, then the additional information is viewable through LogMiner. You can create a supplemental redo log for a single table using the ALTER TABLE statement

• r for the entire database using the

ALTER DATABASE statement. If you later decide that this additional logging is not necessary, you can use the DROP SUPPLEMENTAL LOG DATA clause with either the ALTER TABLE or ALTER DATABASE statement to discontinue the logging of this additional information. ( )

alter

table

colname

,

GROUP groupname ADD SUPPLEMENTAL LOG ALWAYS SUPPRESSION DU SUPPLEMENTAL LOGGING alter DRO P SUPPLEMENTAL DATA alter

database table

LOG

Oracle upg adm 9i Claude DA COSTA Chap 3 LOGMINER Enhcts Page 4/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 5/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
• nly 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
• f 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.

Oracle upg adm 9i Claude DA COSTA Chap 3 LOGMINER Enhcts Page 6/14

LES 4 PHASES

• PLACER LES INFORMATIONS DE "TRANSLATION" DU DICTIONNAIRE DE DONNEES
• DECLARER LES LOGS A CONSOMMER
• LANCER LA SESSION D'ANALYSE
• CONSULTER LE RESULTAT

V$LOGMNR_CONTENTS

DBMS_LOGMNR_D . BUILD DBMS_LOGMNR .

add_logfile

DBMS_LOGMNR .

start_logmnr

Oracle upg adm 9i Claude DA COSTA Chap 3 LOGMINER Enhcts Page 7/14

( , )

PHASE 1 : PLACER LES INFORMATIONS DE "TRANSLATION" DU DICTIONNAIRE DE DONNEES

DBMS_LOGMNR_ D . BUILD Dic o Logminer

redo log redo log redo log redo log

BIEN VOIR : avec 9i, 3 modes d'exploitation du dictionnaire de données sont disponibles :

• Le traditionnel "dico file" qui prend une image du dictionnaire en précédence de l'analyse de logs
• Le nouveau mode "dico in redo log" qui permet d'enregistrer des images du dictionnaire au fil du temps, conservées dans les

enregistrements redo : LA BASE DOIT ETRE EN MODE ARCHIVE LOG

• Le nouveau mode "dico on line" qui se passe de toute prise d'image et laisse l'analyse se dérouler avec seulement la version

courante du dictionnaire de données. end

dictionary_filename

=>

'ficname dictionary_location

=>

'locname'

• ptions

=> DBMS_LOGMNR_ D .

store_in_flat_file

• ptions

=> DBMS_LOGMNR_ D .

store_in_redo_logs

SAVOIR : L'exécution sous SQLPLUS permet de spécifier, dans la méthode "flat dico", les location et filename du fichier dico à plat. L'exécution sous PL/SQL, ne permet pas de donner la location du fichier. Oracle s'appuie sur le contenu de la variable d'initialisation :

UTL_FILE_DIR

UTL_FILE_DIR lets you specify one or more directories that Oracle should use for PL/SQL file I/O. If you are specifying multiple directories, you must repeat the UTL_FILE_DIR parameter for each directory on separate lines of the initialization parameter file.

• ptions

=>

locname

Oracle upg adm 9i Claude DA COSTA Chap 3 LOGMINER Enhcts Page 8/14

PHASE 1 : PLACER LES INFORMATIONS DE "TRANSLATION" DU DICTIONNAIRE DE DONNEES

TRANSLATION OF LOG DATA

Oracle9i's LogMiner utility allows you to query redo log files using SQL. However, to fully translate the log data, LogMiner requires a source dictionary. Prior to Oracle9i, you could only use a dictionary that had been extracted to a flat file. With Oracle9i, LogMiner can use a source dictionary that has been extracted to an operating system file, a dictionary that has been extracted to the redo logs, or the current online data dictionary. If no dictionary is specified, then LogMiner shows redo log information in hexadecimal format. To extract database dictionary information to a flat file, use the DBMS_LOGMNR_D.BUILD procedure. The steps you should take are:

• 1. Set the UTL_FILE_DIR initialization parameter to include the desired location of the dictionary file.
• 2. Mount and open the database.
• 3. Execute the DBMS_LOGMNR_D.BUILD

procedure specifying a dictionary filename and a directory path name for the dictionary. You can also include the STORE_IN_FLAT_FILE option, but this is not required because it is the default. To extract a dictionary to the redo log files, the database must be in ARCHIVELOG mode. Execute the DBMS_LOGMNR_D.BUILD procedure with the STORE_IN_REDO_LOGS option. To use the current online catalog, execute the DBMS_LOGMNR.START procedure with the DICT_FROM_ONLINE_CATALOG option. Accessing the data dictionary from a flat file uses fewer system resources, and users can issue DDL statements while the data dictionary is being extracted. Accessing the data dictionary from the redo log files guarantees that the dictionary is consistent. However, while extracting the data dictionary, no DDL statement can be issued. Extraction to the redo logs is faster than extraction to a flat file. Accessing the data dictionary from the online catalog contains the current information about the database. If you are analyzing older redo log files, the structure of the database may have changed and the online catalog might not be applicable to the records being examined by LogMiner. Oracle upg adm 9i Claude DA COSTA Chap 3 LOGMINER Enhcts Page 9/14

( , )

PHASE 2 : DECLARER LES LOGS A CONSOMMER

• ptions

=>

logfilename

=>

'arcname'

SCN k deb

archive archive

+ 1

archive archive

+1 + 1 SCN k deb SCN k fin SCN k deb SCN k fin SCN k deb SCN k fin SCN k deb SCN k fin

DBMS_LOGMNR . REMOVEFILE DBMS_LOGMNR . ADDFILE DBMS_LOGMNR . NEW DBMS_LOGMNR .

add_logfile

Oracle upg adm 9i Claude DA COSTA Chap 3 LOGMINER Enhcts Page 10/14

( , )

PHASE 3 : LANCER LA SESSION D'ANALYSE

starttime

=>

starttime endscn

=>

endscn endtime

=>

endtime startscn

=>

startscn

• ptions

=>

dictfilename

=>

'arcname'

Retour des seuls DMLs de transactions commitées Sauter toutes corruptions rencontrées en redo log et les noter dans V$logmnr_contents(info) Garantir le refresh auto du "flat dico "ou "redo log" si DDL durant construction Utiliser Dico directement Utiliser redologs ne pas insérer le "

;" à la fin de chaque

• rdre SQL reconstruit

Reconstruire les ordres pour les montrer lisibles humainement DBMS_LOGMNR . DBMS_LOGMNR . DBMS_LOGMNR . skip_corruption ddl_dict_tracking commited_data_only Dico Logminer archive Si aucun dict_xxx, utiliser Flat dico

V$LOGMNR_CONTENTS

info

DBMS_LOGMNR .

start_logmnr

DBMS_LOGMNR .

end_logmnr

DBMS_LOGMNR . DBMS_LOGMNR . DBMS_LOGMNR . DBMS_LOGMNR . dict_from_online_catalog no_sql_delimiter print_pretty_sql dict_from_redo_log Si utilisation du "flat file"

Oracle upg adm 9i Claude DA COSTA Chap 3 LOGMINER Enhcts Page 11/14

PHASE 4 : CONSULTER LE RESULTAT A propos du DDL : In Oracle9i, LogMiner has the ability to display DDL that has occurred, but no way to undo DDL. For DDL operations, the underlying DML is still included in V$LOGMNR_CONTENTS, but an additional row, with an OPERATION value of "DDL" is included.

• The SQL_REDO column displays the actual DDL, but
• the SQL_UNDO column will be NULL.

timestamp V$LOGMNR_CONTENTS

username scn

SCN au moment de la génération de l'enregistrement redo

commit_timestamp sql_redo

• rdre ayant suscité l'enreg

sql_undo

• rdre inverse

OPERATION update insert delete

DDL info

message d'information (notamment pour détection de blocs corrompus, sautés avec l'option "skip_corruption ")

Oracle upg adm 9i Claude DA COSTA Chap 3 LOGMINER Enhcts Page 12/14

CAS D'USAGE

CORRUPTION : Suite à un constat de corruption logique de bloc (par exemple, update intempestif et indésirable), logminer permet de remonter à l'instant préçis précédant la corruption, afin d'opérer un recovery time

• u change based (cf flashback).

ERREUR UTILISATEUR : En cas d'erreur utilisateur, logminer permet de créer un petit ensemble d'ordres SQL pour reconstituer les données, en alternatice au processus de restauration incomplète. Depuis la 9i, logminer permet de VOIR les ordres DDL, ce qui permet de trouver le moment EXACT (ou SCN PRECIS) jusqu'auquel on doit engager le cas échéant, une restauration incomplète. PERFORMANCE TUNING & CAPACITY : Déterminer quelles tables subissent le plus d'updates et d'inserts : Cette information fournit une explication HISTORIQUE sur les statistiques d'accés disque, qui peut être utilisée dans des propositions de tuning. POST-AUDITING : Pister tous les ordres DML et DDL passés.

Oracle upg adm 9i Claude DA COSTA Chap 3 LOGMINER Enhcts Page 13/14

DISPOSITIF DE DETECTION DE SNAPSHOT (flat dico ou redo log dico) approprié

APPROPRIATE SNAPSHOT : La détection qu'un snapshot est approprié ou non est facilitée par le fait qu'en Oracle 9i, tout enregistrement redo contient un numéro de version d'objet OBJV# Ce numéro de version est incrémenté à chaque opération sur l'objet, du type ALTER TABLE ADD DROP MODIFY La table OBJ# contient une colonne pour cette information Puisque OBJV# est aussi contenu dans le snapshot utilisé par Logminer, une simple comparaison de la valeur trouvée en enreg redo, avec celle du dictionnaire, indique si le dictionnaire est "out of date" ou non vis à vis de l'objet concerné.

Oracle upg adm 9i Claude DA COSTA Chap 3 LOGMINER Enhcts Page 14/14