Log Miner Example

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips



Minimum Privs Required

DBA Role.

Archive log Mode : ENABLE

If we want to analyze ARCHIVELOG files also with ONLINE REDO LOG FILES

Analyzing RedoLog Files

1. Create New User --- Not require.


SQL> create user mtaj identified by taj;

User created.


Grant DBA role to user


SQL> grant dba to mtaj;

Grant succeeded.


Conn with new user and issue some DDL, DML statement


SQL> conn mtaj/taj
Connected.
SQL> create table taj ( no number, name varchar2(20));

Table created.

SQL> insert into taj values ( 1,'test');

1 row created.

SQL> insert into taj values ( 2,'test2\');

1 row created.

SQL> commit;

Commit complete.


Enable Supplemental Log



SQL> --Enable Supplemental log
SQL> alter database add supplemental log data;

Database altered.


Select Redo log file for analyze



SQL> column member format a50
SQL> select a.member,b.status
2 from v$logfile a , v$log b
3 where a.group# = b.group#;

MEMBER STATUS
-------------------------------------------------- ---------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG CURRENT
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG ACTIVE
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG INACTIVE

Note: here we anaylze current redo log files.

SQL> execute sys.dbms_logmnr.add_logfile(-
> logfilename => 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG',-
> options => sys.dbms_logmnr.new);

PL/SQL procedure successfully completed.

YOu can also analyze ARCHIVELOG file.

Query for Most recently archived File

SQL> SELECT NAME FROM V$ARCHIVED_LOG
2 WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);

NAME
--------------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2007_06_28\O1_MF_1_

66_386GL2YW_.ARC

Start Logmnr Session

SQL> execute sys.dbms_logmnr.start_logmnr (-
> options => sys.dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

Query in V$LOGMNR_CONTENTS view for REDO,UNDO data

SQL> select sql_redo,sql_undo from v$logmnr_contents where table_name = 'TEST';

no rows selected

End Logmnr session

SQL> execute sys.dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.