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 dictionary_location options => dbms_logmnrd.store_in_flat_file options => dbms_logmnrd.store_in_redo_logs options => filname

ou ou

Ÿ on identifie l'option "flat dico" comme une parmi d'autres Ÿ on ajoute la possibilité d'enregistrer le dico en redo log Ÿ on ne préçise rien, en prévoyant d'utiliser le dico actuel, au start

Ÿ A l'analyse, on préçise l'origine des données dico DBMS_LOGMNR.START_LOGMNR( Ÿ dico en redo logs options Ÿ dico actuel options Ÿ on ne préçise rien implique "flat dico" options plus quelques options supplémentaires Ÿ retour des seuls DMLs commités options Ÿ saut des enregs corrompus en redo (ça n'est pas le défaut) options Ÿ raffraichissement automatique du "flat dico" options Ÿ divers présentation : ne pas mettre ";" à la fin options Ÿ divers présentation : format ddl/dml élaboré options

=> dbms_logmnr.dict_from_redo_logs => dbms_logmnr.dict_from_online_catalog => => => => => =>

dbms_logmnr.commited_data_only dbms_logmnr.skip_corruption dbms_logmnr.ddl_dict_tracking dbms_logmnr.no_sql_delimiter 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 RAPPEL

Chap 3 LOGMINER Enhcts Page 3/14

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 alter

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.

databa se ADD

SUPPLEMENTAL

( )

DATA

LOG

primary key

In Oracle9i, LogMiner allows you to store additional information by creating supplemental redo log groups.

COLUMNS unique index

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 alter

You can create a supplemental redo log for a single table using the ALTER TABLE statement or for the entire database using the ALTER DATABASE statement.

table ADD

SUPPLEMENTAL

GROUP

groupname

LOG

( )

ALWAYS

, colname

SUPPRESSION DU SUPPLEMENTAL LOGGING alter

databa se DRO P

alter

table

SUPPLEMENTAL

LOG

DATA

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.

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 LES DONNEES ADITIONNELLES EN REDO LOG, ISSUES DE LA TABLE

Chap 3 LOGMINER Enhcts Page 6/14

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.

Oracle upg adm 9i

Claude DA COSTA

Chap 3 LOGMINER Enhcts Page 7/14

LES 4 PHASES Ÿ PLACER LES INFORMATIONS DE "TRANSLATION" DU DICTIONNAIRE DE DONNEES

DBMS_LOGMNR_D

.

DBMS_LOGMNR

.

add_logfile

Ÿ LANCER LA SESSION D'ANALYSE

DBMS_LOGMNR

.

start_logmnr

Ÿ CONSULTER LE RESULTAT

V$LOGMNR_CONTENTS

Ÿ

DECLARER LES LOGS A CONSOMMER

BUILD

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 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 DBMS_LOGMNR_ D

.

BUILD locna m e

( , )

dictionary_filename

=>

'ficname

dictionary_location

=>

'locname'

options

=>

DBMS_LOGMNR_ D

options

options

=>

DBMS_LOGMNR_ D

Dic o Logmin e r

.

.

store_in_flat_file

redo log

redo log

redo log

redo log

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.

Oracle upg adm 9i

Claude DA COSTA Chap 3 LOGMINER Enhcts Page 9/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 STORE_IN_REDO_LOGS option.

mode. Execute the DBMS_LOGMNR_D.BUILD procedure with the

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.

Chap 3 LOGMINER Enhcts Page 10/14

DBMS_LOGM NR

.

NEW

DBMS_LOGM NR

.

ADDFI L E

DBMS_LOGM NR

.

REMOVEFI L E

archive

'arcname'

archive

=>

archive

options

SCN k deb SCN k fin

( , )

=>

+1

logfilename

SCN k deb SCN k fin

+1

add_logfile

+1

.

SCN SCN kk deb deb SCN k fin

DBMS_LOGM NR

archive

Claude DA COSTA PHASE 2 : DECLARER LES LOGS A CONSOMMER

SCN k deb SCN k fin

Oracle upg adm 9i

Oracle upg adm 9i

Claude DA COSTA PHASE 3 : LANCER LA SESSION D'ANALYSE

dictfilename

DBMS_LOGM NR

.

start_logm n r

=>

=>

starttime

startscn

=>

startscn

endtime

=>

endtime

endscn

=>

endsc n

( , ) options

=>

Si utilisation du "flat file"

'arcname'

starttime

Chap 3 LOGMINER Enhcts Page 11/14

Retour des seuls DMLs de transactions com mitées

DBMS_LOGM NR

. commited_data_only

DBMS_LOGM NR

. skip_corruption

DBMS_LOGM NR

. ddl_dict_tracking

DBMS_LOGM NR

. no_sql_delimiter

DBMS_LOGM NR

. print_pretty_sql

DBMS_LOGM NR

. dict_from_online_catalog

DBMS_LOGM NR

. dict_from_r edo_log

Sauter toutes corruptions rencontrées en redo log et les noter dans V$logmnr_contents(info)

V$LOGMNR_CONTENTS

Ÿ info

Garantir le refresh auto du "flat dico "ou "redo log" si DDL durant construction

ne pas insérer le ";" à la fin de chaque ordre SQL reconstruit

Reconstruire les ordres pour les montrer lisibles humainement

Utiliser Dico directement Utiliser

redologs archive

Si aucun dict_xxx, utiliser Flat dico DBMS_LOGM NR

.

end_logm nr

Dico Logminer

Oracle upg adm 9i

Claude DA COSTA PHASE 4 : CONSULTER LE RESULTAT

Chap 3 LOGMINER Enhcts Page 12/14

timestamp V$LOGMNR_CONTENTS Ÿ username Ÿ scn Ÿ commit_timestamp

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

Ÿ sql_redo Ÿ sql_undo

ordre ayant suscité l'enreg ordre inverse

Ÿ OPERATION Ÿ update Ÿ insert Ÿ delete

ŸDDL Ÿ info

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

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.

Oracle upg adm 9i

Claude DA COSTA CAS D'USAGE

Chap 3 LOGMINER Enhcts Page 13/14

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 ou 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 14/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é.