viernes, 19 de julio de 2013

Using LogMiner to Analyze Redo Log Files

Using LogMiner to Analyze Redo Log Files

A continuación una forma fácil de identificar quien realizó algún cambio en la DB, utilizando la herramienta LogMiner de Oracle usando los archived log.
1.       Activamos la opción Minimal Supplemental Logging
SQL> CONNECT / AS SYSDBA
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Debemos revisar el valor: SUPPLEMENTAL_LOG_DATA_MIN
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
2.      Buscamos los log dependiendo la configuración.
Seteamos el formato de la fecha y hora (se puede saltar este paso usando el script de abajo logminer.sql).
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
En caso de que tengamos la BD en redolog, sacamos el último redolog realizado.
select a.group#, b.member, a.archived, a.status
from v$log a, v$logfile b
where a.group#=b.group#
and a.FIRST_TIME=(SELECT MAX(c.FIRST_TIME) FROM v$log c);
GROUP#  MEMBER                          ARCHIVED STATUS
--------------------------------------------------------------------------------
5 /u01/oracle/data/log10a.dbf NO CURRENT
5 /u01/oracle/data/log10b.dbf' NO CURRENT

En caso de que tengamos configurado la base de datos en modo archivelog, sacamos el último archivelog.
SELECT NAME FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
NAME
--------------------------------------------------------------------------------
/u01/oracle/recovery_area/PROD/archivelog/2012_01_15/o1_mf_1_46051_78p6574l_.arc 

3.      Cargamos los archivos log en logmnr con el resultado de las consultas anteriores.
         Dependiendo el caso se va utilizar solo uno:

     Carga manual redolog
  begin  dbms_logmnr.add_logfile( '/u01/oracle/data/log10a.dbf', dbms_logmnr.new );   dbms_logmnr.add_logfile( '/u01/oracle/data/log10b.dbf', dbms_logmnr. addfile);   end;
  /
Carga manual archivelog
  begin  dbms_logmnr.add_logfile( '
/u01/oracle/recovery_area/PROD/archivelog/2012_01_15/o1_mf_1_46051_78p6574l_.arc', dbms_logmnr.new ); 
  end;
  /
Carga automática por fecha
Se creó el script logminer.sql para cargar de forma automática los archived log de una fecha determinada.
[oracle@ticservice scripts]$ sqlplus / as sysdba 
SQL> @logminer.sql
4.      Using the Online Catalog (Solo para la carga manual de archive log)
 Debemos iniciar el  start_logmnr para realizar las consultas, se puede iniciar de dos formas:
 execute dbms_logmnr.start_logmnr;

//Para usar el diccionario ONLINE
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

5.       Una vez activado podemos realizar consultas para determinar el usuario que realizo cierta transacción como DDL o DML.
COLUMN sql_redo   FORMAT A25 WORD_WRAPPEDCOLUMN session_info FORMAT A25 WORD_WRAPPEDCOLUMN timestamp  FORMAT A20SET linesize 175
Ejemplo 1 DML: Se busca por los campos owner “HR” la table “HR_Personal” y la operación DELETE, esto nos ayuda a ver quienes realizaron una eliminación de registros.
SELECT timestamp, SESSION_INFO, substr(SQL_REDO ,1,80) SQL_REDO
   from v$logmnr_contents  where seg_owner='HR'   and seg_name='HR_Personal'   and operation = 'DELETE';
Ejemplo2 Datos Insertados.
SELECT timestamp, SESSION_INFO, substr(SQL_REDO ,1,80) SQL_REDO
   FROM V$LOGMNR_CONTENTS
where   OPERATION = 'INSERT'   AND TABLE_NAME ='TEST_TABLA2';
Ejemplo3 DDL: Crear o eliminar un objeto, buscamos el objeto en el campo SQL_REDO.
SELECT timestamp, SESSION_INFO, substr(SQL_REDO ,1,80) SQL_REDO
   FROM V$LOGMNR_CONTENTS   WHERE SEG_OWNER = 'APPS' And SQL_REDO like '%Personal%'
 And   OPERATION = 'DDL';

6.      End a LogMiner Session
Debemos deshabilitar el log miner cuando terminemos la revisión.
EXECUTE DBMS_LOGMNR.END_LOGMNR();
Importante: Los redo logs son sobre escritos conforme se realizan transacciones, en el caso de que la BD fuera con modo archived log dependiendo cuanto tiempo lo mantengas esos archivos se podrá consultar la información .

Script logminer.sql: El siguiente código solo se puede ejecutar con sys dba.



--Formateamos la fecha y horaalter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
--Formateamos las columnasCOLUMN sql_redo   FORMAT A25 WORD_WRAPPED
COLUMN session_info FORMAT A25 WORD_WRAPPED
COLUMN timestamp  FORMAT A20
SET linesize 175DeclareTYPE TCUR IS REF CURSOR;
CUR TCUR;lv_Name VARCHAR2(1000);
lv_FirstRec VARCHAR2(10) := 'TRUE';
lv_StDate DATE;
lv_EndDate DATE;
BEGIN    /* TODO: Change these dates as required */    lv_StDate := TO_DATE('&fecha', 'DD-MM-YYYY');
    /* Get all archive logs for the specified date time range */    OPEN CUR FOR    'SELECT NAME
     FROM V$ARCHIVED_LOG     WHERE ( trunc(FIRST_TIME) = :StDate)'    USING lv_StDate;
    LOOP         FETCH CUR INTO lv_Name;
         EXIT WHEN CUR%NOTFOUND;
        IF (lv_Name IS NOT NULL ) THEN         IF lv_FirstRec = 'TRUE' THEN              -- add archived file to the LOG Miner;               DBMS_LOGMNR.ADD_LOGFILE(lv_Name,  SYS.DBMS_LOGMNR.NEW);              lv_FirstRec := 'FALSE';
         ELSE              -- add additional files if necessary              DBMS_LOGMNR.ADD_LOGFILE(lv_Name,                DBMS_LOGMNR.ADDFILE);         END IF;
       END IF;
    END LOOP;
    CLOSE CUR;
    IF lv_FirstRec = 'TRUE' THEN         RAISE_APPLICATION_ERROR(-20000, 'NO ARCHIVED LOGS FOUND');
    END IF;
    /* start log miner (using the current catalog; catalog is needed to
resolve object names)       optionally specify the start and end time and ask for committeddata only */     DBMS_LOGMNR.START_LOGMNR(         OPTIONS =>  SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG ); END


Para que puede personalizar sus consultas en LogMiner a continuación detallo las columnas de la vista 
V$LOGMNR_CONTENTS
Column
Datatype
Description
SCN
NUMBER
System change number (SCN) when the database change was made
CSCN
NUMBER
System change number (SCN) when the transaction committed; only meaningful if theCOMMITTED_DATA_ONLY option was chosen in a DBMS_LOGMNR.START_LOGMNR() invocation
TIMESTAMP
DATE
Timestamp when the database change was made
COMMIT_TIMESTAMP
DATE
Timestamp when the transaction committed; only meaningful if the COMMITTED_DATA_ONLYoption was chosen in a DBMS_LOGMNR.START_LOGMNR() invocation
THREAD#
NUMBER
Number of the thread that made the change to the database
LOG_ID
NUMBER
This column is deprecated.
XIDUSN
NUMBER
Transaction ID undo segment number of the transaction that generated the change
XIDSLT
NUMBER
Transaction ID slot number of the transaction that generated the change
XIDSQN
NUMBER
Transaction ID sequence number of the transaction that generated the change
PXIDUSN
NUMBER
Parent transaction ID undo segment number of a parallel transaction
PXIDSLT
NUMBER
Parent transaction ID slot number of a parallel transaction
PXIDSQN
NUMBER
Parent transaction ID sequence number of a parallel transaction
RBASQN
NUMBER
Sequence# associated with the Redo Block Address (RBA) of the redo record associated with the change
RBABLK
NUMBER
RBA block number within the log file
RBABYTE
NUMBER
RBA byte offset within the block
UBAFIL
NUMBER
Undo Block Address (UBA) file number identifying the file containing the undo block
UBABLK
NUMBER
UBA block number for the undo block
UBAREC
NUMBER
UBA record index within the undo block
UBASQN
NUMBER
UBA undo block sequence number
ABS_FILE#
NUMBER
Data block absolute file number of the block changed by the transaction
REL_FILE#
NUMBER
Data block relative file number. The file number is relative to the tablespace of the object
DATA_BLK#
NUMBER
Data block number within the file
DATA_OBJ#
NUMBER
Data block object number identifying the object
DATA_OBJD#
NUMBER
Data block data object number identifying the object within the tablespace
SEG_OWNER
VARCHAR2(32)
Owner of the modified segment
SEG_NAME
VARCHAR2(256)
Name of the modified data segment
TABLE_NAME
VARCHAR2(32)
Name of the modified table (in case the redo pertains to a table modification)
SEG_TYPE
NUMBER
Type of the modified data segment. Possible values are:
  • 0 = UNKNOWN
  • 1 = INDEX
  • 2 = TABLE
  • 19 = TABLE PARTITION
  • 20 = INDEX PARTITION
  • 34 = TABLE SUBPARTITION
  • All other values = UNSUPPORTED
SEG_TYPE_NAME
VARCHAR2(32)
Segment type name. Possible values are:
  • UNKNOWN
  • INDEX
  • TABLE
  • TABLE PARTITION
  • UNSUPPORTED
  • TABLE_SPACE
TABLE_SPACE
VARCHAR2(32)
Name of the tablespace containing the modified data segment. This column is not populated for rows where the value of the OPERATION column is DDL. This is because DDL may operate on more than one tablespace.
ROW_ID
VARCHAR2(18)
Row ID of the row modified by the change (only meaningful if the change pertains to a DML) This will be NULL if the redo record is not associated with a DML.
SESSION#
NUMBER
Session number of the session that made the change
SERIAL#
NUMBER
Serial number of the session that made the change
USERNAME
VARCHAR2(30)
Name of the user who executed the transaction
SESSION_INFO
VARCHAR2(4000)
Information about the database session that executed the transaction. Contains process information, machine name from which the user logged in etc. A possible SESSION_INFOcolumn may contain the following:
  • login_username = HR
  • client_info =
  • OS_username = jkundu
  • Machine_name = nirvan
  • OS_terminal = pts/31
  • OS_program_name = sqlplus@nirvan (TNS V1-V3)
TX_NAME
VARCHAR2(256)
Name of the transaction that made the change. This is only meaningful if the transaction is a named transaction.
ROLLBACK
NUMBER
1 = if the redo record was generated because of a partial or a full rollback of the associated transaction
0 = otherwise
OPERATION
VARCHAR2(32)
User level SQL operation that made the change. Possible values are:
  • INSERT = change was caused by an insert statement
  • UPDATE = change was caused by an update statement
  • DELETE = change was caused by a delete statement
  • DDL = change was caused by a DDL statement
  • START = change was caused by the start of a transaction
  • COMMIT = change was caused by the commit of a transaction
  • ROLLBACK = change was caused by a full rollback of a transaction
  • LOB_WRITE = change was caused by an invocation of DBMS_LOB.WRITE
  • LOB_TRIM = change was caused by an invocation of DBMS_LOB.TRIM
  • LOB_ERASE = change was caused by an invocation of DBMS_LOB.ERASE
  • SELECT_FOR_UPDATE = operation was a SELECT FOR UPDATE statement
  • SEL_LOB_LOCATOR = operation was a SELECT statement that returns a LOB locator
  • MISSING_SCN = LogMiner encountered a gap in the redo records. This is most likely because not all redo logs were registered with LogMiner.
  • INTERNAL = change was caused by internal operations initiated by the database
  • UNSUPPORTED = change was caused by operations not currently supported by LogMiner (for example, changes made to tables with ADT columns)
OPERATION_CODE
NUMBER
Number of the operation code. Possible values are:
  • 0 = INTERNAL
  • 1 = INSERT
  • 2 = DELETE
  • 3 = UPDATE
  • 5 = DDL
  • 6 = START
  • 7 = COMMIT
  • 9 = SELECT_LOB_LOCATOR
  • 10 = LOB_WRITE
  • 11 = LOB_TRIM
  • 25 = SELECT_FOR_UPDATE
  • 28 = LOB_ERASE
  • 34 = MISSING_SCN
  • 36 = ROLLBACK
  • 255 = UNSUPPORTED
SQL_REDO
VARCHAR2(4000)
Reconstructed SQL statement that is equivalent to the original SQL statement that made the change. Please refer to Oracle Database Utilities before executing SQL_REDO to your database.
LogMiner does not generate SQL redo for temporary tables. In such a case, this column will contain the string "/* No SQL_REDO for temporary tables */".
SQL_UNDO
VARCHAR2(4000)
Reconstructed SQL statement that can be used to undo the effect of the original statement that made the change. DDL statements have no corresponding SQL_UNDO. Please refer toOracle Database Utilities before executing SQL_UNDO to your database.
LogMiner does not generate SQL undo for temporary tables. In such a case, this column will contain the string "/* No SQL_UNDO for temporary tables */".
RS_ID
VARCHAR2(32)
Record set ID. The tuple (RS_ID, SSN) together uniquely identifies a row inV$LOGMNR_CONTENTS. RS_ID uniquely identifies the redo record that generated the row.
SEQUENCE#
NUMBER
Sequence number of the redo log that contained the redo record corresponding to the database change
SSN
NUMBER
SQL sequence number. Used in conjunction with RS_ID, this uniquely identifies a row in theV$LOGMNR_CONTENTS view.
CSF
NUMBER
Continuation SQL flag. Possible values are:
  • 0 = indicates SQL_REDO and SQL_UNDO is contained within the same row
  • 1 = indicates that either SQL_REDO or SQL_UNDO is greater than 4000 bytes in size and is continued in the next row returned by the view
INFO
VARCHAR2(32)
Informational message about the row. For instance, the string "USER DDL" in INFO column indicates that the DDL statement returned in SQL_REDO column was the top-level DDL executed by the user and the string "INTERNAL DDL" in INFO column indicates that DDL statement returned in SQL_REDO column was executed internally by the RDBMS.
STATUS
NUMBER
0 indicates that the reconstructed SQL statements as shown in the SQL_REDO and SQL_UNDO columns are valid executable SQL statements. Otherwise, the reconstructed SQL statements are not executable. This may be due to the fact that no data dictionary was provided to LogMiner for the analysis, or that the data dictionary provided did not have the definition of the object being mined.
REDO_VALUE
NUMBER
Used as input to the DBMS_LOGMNR.MINE_VALUE() and DBMS_LOGMNR.COLUMN_PRESENT()functions
UNDO_VALUE
NUMBER
Used as input to the DBMS_LOGMNR.MINE_VALUE() and DBMS_LOGMNR.COLUMN_PRESENT()functions
SQL_COLUMN_TYPE
VARCHAR2(30)
This column is deprecated.
SQL_COLUMN_NAME
VARCHAR2(30)
This column is deprecated.
REDO_LENGTH
NUMBER
This column is deprecated.
REDO_OFFSET
NUMBER
This column is deprecated.
UNDO_LENGTH
NUMBER
This column is deprecated.
UNDO_OFFSET
NUMBER
This column is deprecated.
DATA_OBJV#
NUMBER
Version number of the table being modified
SAFE_RESUME_SCN
NUMBER
Reserved for future use
XID
RAW(8)
Raw representation of the transaction identifier
PXID
RAW(8)
Raw representation of the parent transaction identifier
AUDIT_SESSIONID
NUMBER
Audit session ID associated with the user session making the change

Referencias:
http://www.angelfire.com/linux/setia/top/main_logminer.html
http://www.idevelopment.info/data/Oracle/DBA_tips/LogMiner/LOGMINER_15.shtml
http://docs.oracle.com/cd/B19306_01/server.102/b14215/logminer.htm#i1010243
http://www.peoug.org/%C2%BFpor-que-mucha-generacion-de-redo-y-archivelogs/
http://blog.e-dba.biz/2008/10/desapareci-mi-tabla-confiesen-quin-fue.html
http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_1154.htm
Column USERNAME And SESSION_INFO Are UNKNOWN Or NULL In V$LOGMNR_CONTENTS [ID 110301.1]        
Publicado por en 7 comentarios:
Etiquetas:
Suscribirse a: Entradas (Atom)