Hola lectores DBAs,
Muchas veces queremos conocer los comandos ejecutados y el detalle, de cierta información que han modificaron en el pasado; pues para esto, existe una herramienta de Oracle llamada LogMiner, que sin necesidad de que hayamos activado audioría por objeto, usuario, acción, etc. podemos conocer todos estos detalles solamente con tener la BD configurada en ArchiveLog Mode.
Nota: Si lo que estás buscando es configurar una auditoría para futuros cambios o simplemente comenzar a auditar los eventos que ocurran en tu BD, puedes consultar nuestro siguiente artículo:
Cómo auditar una Base de Datos Oracle?
Para utilizar el LogMiner primero tenemos que saber básicamente cómo trabaja.
Esta herramienta lo que nos permite es, configurar o añadir metadatos relacionados con nuestros archives generados, a la BD. Lo cual nos permitirá leerlos de una forma muy fácil.
Nota: Recordemos que todos los cambios (operaciones "logged") hechos en nuestra BD pasan por los Redo Log files, y cuando está configurada en ArchiveLog Mode, se están sacando copias físicas de éstos archivos o ficheros Redo a nuestra ruta de Archives (ej. log_archive_dest).
Ahora que ya tenemos un panorama general, solo debemos decirle a Oracle que lea los ficheros o "Archives" generados en un cierto periodo de tiempo.
Todo esto será más fácil visualizarlo con un ejemplo:
Supongamos que llega el grupo de Desarrolladores y nos comentan que "alguien" - como siempre los desarrolladores no tienen la culpa ;) - ha modificado información aprox. de las 2pm a las 3:45pm de ayer...
Nota: Es posible también que queramos conocer la información modificada, porque durante un periodo de tiempo corto se generaron demasiados archives; por lo que necesitamos pruebas (comandos ejecutados) para dárselas al grupo de desarrollo o de Aplicaciones y preguntarles por qué se modificaron tantos datos, en ese rango de tiempo.
Pues es hora de trabajar...
Recuerden que la BD, a revisar, debe o debió haber estado en archive log para que haya registrado esas operaciones "logueadas":
1) Revisemos los archives hechos ayer de las 2:00pm a las 3:45pm
select name, SEQUENCE#, to_char(FIRST_TIME, 'DD-Mon-YY HH24:Mi:SS') as TIME_Changes
from v$archived_log
where to_char(FIRST_TIME, 'DD-Mon-YY HH24:Mi:SS')
between '03-Jun-08 14:00:00' and '03-Jun-08 16:00:00';
NAME SEQUENCE# TIME_CHANGES
-------------------------------------------------- ---------- ------------------------
/oracle/SID/oraarch/SIDarch1_5332_628715840.dbf 5332 03-Jun-08 14:47:58
/oracle/SID/oraarch/SIDarch1_5333_628715840.dbf 5333 03-Jun-08 14:48:03
/oracle/SID/oraarch/SIDarch1_5334_628715840.dbf 5334 03-Jun-08 14:48:10
/oracle/SID/oraarch/SIDarch1_5335_628715840.dbf 5335 03-Jun-08 14:48:18
2) Ya hemos detectado que entre esos rangos de tiempo se generaron 4 archives. Pues vamos a revisar qué contienen.
Primero debemos de añadir, en nuestra sesión, los metadatos de esos archives que vamos a revisar:
SQL> exec DBMS_LOGMNR.ADD_LOGFILE('/oracle/SID/oraarch/SIDarch1_5332_628715840.dbf');
PL/SQL procedure successfully completed.
SQL> exec DBMS_LOGMNR.ADD_LOGFILE('/oracle/SID/oraarch/SIDarch1_5333_628715840.dbf');
PL/SQL procedure successfully completed.
SQL> exec DBMS_LOGMNR.ADD_LOGFILE('/oracle/SID/oraarch/SIDarch1_5334_628715840.dbf');
PL/SQL procedure successfully completed.
SQL> exec DBMS_LOGMNR.ADD_LOGFILE('/oracle/SID/oraarch/SIDarch1_5335_628715840.dbf');
PL/SQL procedure successfully completed.
3) Ahora arrancamos la sesión de LogMiner.
SQL> exec dbms_logmnr.start_logmnr();
PL/SQL procedure successfully completed.
4) Ahora ya puedes consultar la vista V$LOGMNR_CONTENTS, la cual tendrá comandos para rehacer cambios, comandos ejecutados, horario exacto, etc.
Por ejemplo, para ver las fechas a revisar:
select min(to_char(COMMIT_TIMESTAMP,'DD/Mon/YY HH24:Mi:SS')) from V$LOGMNR_CONTENTS;
select min(to_char(COMMIT_TIMESTAMP,'DD/Mon/YY HH24:Mi:SS')) MIN,
max(to_char(COMMIT_TIMESTAMP,'DD/Mon/YY HH24:Mi:SS')) MAX
from V$LOGMNR_CONTENTS;
MIN MAX
------------------------ ------------------------
03/Jun/08 14:48:03 03/Jun/08 16:14:46
5) Ahora revisemos los comandos que cambiaron la información:
SQL> select USERNAME, SQL_REDO from V$LOGMNR_CONTENTS where rownum < 10;
USERNAME
------------------------------
USER_CARLOS_CONTRERAS
SQL_REDO
---------------------------------
delete from "UNKNOWN"."OBJ# 89776" where "COL 1" = HEXTORAW('53') and "COL 2" = HEXTORAW('3331393330')
6) Como podrás ver, no es tan fácil la lectura... Aparecen Object_IDs en lugar de Nombres de Tablas, números de columnas en lugar de sus nombres, etc... PERO para hacerlo fácil...
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
7) Ahora ejecuta de nuevo tu select y ya verás completamente comprensibles, a simple vista, los resultados:
SQL> select USERNAME, SQL_REDO from V$LOGMNR_CONTENTS where rownum < 10;
USERNAME
------------------------------
USER_CARLOS_CONTRERAS
SQL_REDO
---------------------------------
delete from "JAMES_BOND"."DELINCUENTES_ATRAPADOS" where "NOMBRE" = 'CARLOS' and "APELLIDO" = 'CONTRERAS'...
Nota: Con el campo REDO_VALUE y UNDO_VALUE te dará el comando para revertir o rehacer los cambios hechos.
Y listo!!! Ahora podrás saber quien hizo que con lujo de detalle (username, tiempo, desde qué PC, usuario a nivel Sistema Operativo, etc etc!!)
Utiliza para detener el servicio de LogMiner:
exec DBMS_LOGMNR.END_LOGMNR()
Para borrar algún Archive que no quieras analizar:
exec DBMS_LOGMNR.REMOVE_LOGFILE('...')
Espero te ayude y postea algún comentario si tienes dudas!
Saludos,
Carlos Contreras.
Errores que posiblemente te trajeron a este post:
-------------------------------------------------
Error 1:
ERROR at line 1:
ORA-01292: no log file has been specified for the current LogMiner session
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 1
Solución:
Primero añade tus archives a revisar y luego arranca el LogMiner en tu sesión. Revisa los pasos 2 y 3 de este artículo.
-------------------------------------------------
Error 2:
ARC1: Failed to archive thread 1 sequence 6817 (19504)
ARC1: Closing local archive destination LOG_ARCHIVE_DEST_1: '/oracle/SID/oraarch/SIDarch1_1289_644692398.dbf' (error 19502)
ORA-19502: write error on file "/oracle/SID/oraarch/SIDarch1_1289_644692398.dbf", blockno 38913 (blocksize=512)
ORA-27061: waiting for async I/Os failed
OS Error: 28: No space left on device
Solución:
1) Mueve archives a otra ruta, ya que tu BD estará suspendida hasta que liberes espacio físico ahí. Recuerda tratar de no borrar archives, por si después los llegases a necesitar en una recuperación.
2) Si tus logs generados fueron demasiados y no es común en tu ambiente, examina con LogMiner, los archives para que puedas ver los comandos ejecutados en ese horario (Este artículo te dirá cómo usar LogMiner)
-------------------------------------------------