Log Miner

LogMiner enables the analysis of the contents of archived redo logs.
It can be used to provide a historical view of the database without the need for point-in-time recovery.
It can also be used to undo operations, allowing repair of logical corruption
SQL statements and keeps the contents of the redolog file in the fixed view called "V$logmnr_contents ".

LogMiner tool can help the DBA to the find changed records in redo log files by using a set of PL/SQL procedures and functions .

Listing archive log

	SQL> archive log list

	Database log mode	       		Archive Mode
	Automatic archival	      	 	Enabled
	Archive destination	       		USE_DB_RECOVERY_FILE_DEST
	Oldest online log sequence     		1
	Next log sequence to archive   		2
	Current log sequence	       		2

Enable Supplemental logging:

	SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

	Database altered.

	SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

	SUPPLEME
	--------
	YES

	SQL> select member from v$logfile;

	MEMBER
	-----------------------------------------
	/u01/app/oracle/oradata/dev/redo03.log
	/u01/app/oracle/oradata/dev/redo02.log
	/u01/app/oracle/oradata/dev/redo01.log

	SQL> ed
	Wrote file afiedt.buf

	  1  begin
	  2	DBMS_LOGMNR.add_logfile (
	  3    	 options => DBMS_LOGMNR.new,
	  4    	 logfilename => '/u01/app/oracle/oradata/tesprim1/redo01.log');
	  5*   	end;
	  6  /

	PL/SQL procedure successfully completed.

	SQL> begin
		DBMS_LOGMNR.add_logfile (
			 options => DBMS_LOGMNR.new,
			 logfilename => '/u01/app/oracle/oradata/tesprim1/redo02.log');
			end;
	  2    3    4    5    6
	  7  /

	PL/SQL procedure successfully completed.

	SQL> begin
		DBMS_LOGMNR.add_logfile (
			 options => DBMS_LOGMNR.new,
			 logfilename => '/u01/app/oracle/oradata/tesprim1/redo03.log');
			end;  2    3    4    5
	  6  /


	SQL> execute DBMS_LOGMNR.START_LOGMNR (options => dbms_logmnr.dict_from_online_catalog);
	PL/SQL procedure successfully completed.

CREATE USER

	SQL> create user u1 identified by u1;
	User created.

	SQL> grant connect,resource,unlimited tablespace to u1;
	Grant succeeded.

	SQL> conn u1/u1
	Connected.

	SQL>

CREATE TABLE

	SQL> create table t1(id int,name varchar2(20));
	Table created.

	SQL> insert into t1 values(1,'aa');
	1 row created.

	SQL> SELECT OPERATION, STATUS, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER ='U1';

	OPERATION	STATUS		SQL_REDO
	---------	-------		------------
	DDL		0		create user u1 identified by  VALUES
					'S:C0C70F4169B9AC0448FE1FBCE8DC151AA4
					C0723F211BD25AE6BFC14B952F;T:C1327AEF
					3D980F52674203D4606A00BC054CFDCF221AC
					D391BC6D952BDBFFCD68D299D0959E5CC94FC
					AFBA2FAAD7A10E9886271E9F671572B677C49
					C67841C02797D40ADCD20BB0C6F8863C055D3
					4EF5' ;

	DDL		0		create table t1(id int,name varchar2(20));


	OPERATION	STATUS		SQL_REDO
	----------	---------	-------------
	INSERT		0		insert into "U1"."T1"("ID","NAME")
					values ('1','aa');

Stopping logminer process:
Once the analysis is complete, logminer should be stopped using the END_LOGMNR procedure.

	SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();
	Accessing LogMiner Information :

LogMiner information is contained in the following views. We can use SQL to query them as we 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 shown 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.