Attending Developer Days in Mumbai

During hands-on sessions.
Thanks - Pictures taken from Grant Ronald's Blog.

LOGMINER.

I am just testing logminer I have Oracle EE 9.2.0.1 installed on WinXP with noarchive mode.

connected with scott user.
scott@NIKUNJ> select count(*) from test;

COUNT(*)
----------
0

scott@NIKUNJ> insert into test select * from tab;
5 rows created.

scott@NIKUNJ> insert into test select * from tab;
5 rows created.

scott@NIKUNJ> insert into test select * from tab;
5 rows created.

scott@NIKUNJ> commit;
Commit complete.

scott@NIKUNJ> select count(*) from test;
COUNT(*)
----------
15

scott@NIKUNJ> delete from test;
15 rows deleted.

scott@NIKUNJ> commit;
Commit complete.

scott@NIKUNJ> select count(*) from test;
COUNT(*)
----------
0

scott@NIKUNJ>


Now I would like to get rows back.
connected with sys user and run below command.

sys@NIKUNJ> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => -
> 'C:\ORACLE\ORADATA\NIKUNJ\REDO03.LOG',OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

Here I had given my current online log file, you will get list of current log file through
sys@NIKUNJ> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
3 ONLINE
C:\ORACLE\ORADATA\NIKUNJ\REDO03.LOG
2 STALE ONLINE
C:\ORACLE\ORADATA\NIKUNJ\REDO02.LOG
1 STALE ONLINE
C:\ORACLE\ORADATA\NIKUNJ\REDO01.LOG

sys@NIKUNJ>


sys@NIKUNJ> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.

Above need for below reasons.

To fully translate the contents of redo logs, LogMiner requires access to a database
dictionary.

LogMiner uses the dictionary to translate internal object identifiers and datatypes to
object names and external data formats. Without a dictionary, LogMiner returns
internal object IDs and presents data as hex bytes.

For example, instead of the SQL statement:
INSERT INTO emp(name, salary) VALUES ('John Doe', 50000);
LogMiner will display:
insert into Object#2581(col#1, col#2) values (hextoraw('4a6f686e20446f65'),
hextoraw('c306'));"

LogMiner gives you three choices for your source dictionary:
1.Extracting the Dictionary to a Flat File
2.Extracting a Dictionary to the Redo Logs
3.Using the Online Catalog

sys@NIKUNJ> execute dbms_logmnr.start_logmnr;
PL/SQL procedure successfully completed.

sys@NIKUNJ> select sql_redo,sql_undo from v$logmnr_contents where username='SCOTT';


Then for time base you have to execute as below.

execute dbms_logmnr.start_logmnr(STARTTIME => TO_DATE('01-OCT-2005 10:00:00', 'DD-MON-YYYY HH:MI:SS'), -
ENDTIME => TO_DATE('01-OCT-2005 12:00:00', 'DD-MON-YYYY HH:MI:SS'));

might be you get error message like this which i get
ERROR at line 1:
ORA-01291: missing logfile
ORA-06512: at "SYS.DBMS_LOGMNR", line 53
ORA-06512: at line 1

then i add another log file
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => 'C:\ORACLE\ORADATA\NIKUNJ\REDO02.LOG', -
OPTIONS => DBMS_LOGMNR.ADDFILE);

and executed once again and i get sucess.
execute dbms_logmnr.start_logmnr(STARTTIME => TO_DATE('01-OCT-2005 10:00:00', 'DD-MON-YYYY HH:MI:SS'), -
ENDTIME => TO_DATE('01-OCT-2005 12:00:00', 'DD-MON-YYYY HH:MI:SS'));

EXECUTE DBMS_LOGMNR.END_LOGMNR;



LogMiner information is contained in the following views. You can use SQL to query them as you would any other view.
V$LOGMNR_CONTENTS
Shows changes made to user and table information.
V$LOGMNR_DICTIONARY
Shows information about the LogMiner dictionary file, provided the dictionary was created using the STORE_IN_FLAT_FILE option. The information show includes the database name and status information.
V$LOGMNR_LOGS
Shows information about specified redo logs. There is one row for each redo log.
V$LOGMNR_PARAMETERS
Shows information about optional LogMiner parameters, including starting and ending system change numbers (SCNs) and starting and ending times.