iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Oracle 11g New Features

Online Oracle Training

 

You can use the Oracle LogMiner utility to enable you to query redo log files through a SQL interface.
 


Dictionary
You can use dictionary so when you query information from log files, you don't see reference object id instead of object name. You can use the following method to extract object name instead of object id. Extracting the Dictionary to a Flat File or to Redo Log Files
 


Using the Online Catalog


LogMiner Restrictions
The following restrictions apply (The following are not supported)
o   Data types LONG and LOB 
o   Simple and nested abstract data types ( ADTs) 
o   Collections (nested tables and VARRAYs) 
o   Object Refs 
o   Index Organized Tables (IOTs) 


Steps to use logminer:
Create a Dictionary Flat File for an Oracle Database
Add the UTL_FILE_DIR parameter to the init<SID>.ora file and then stop and start the database:

For example:

# cd $ORACLE_HOME/dbs

# vi initmydb.ora  -- Then add the following line and save the file.
UTL_FILE_DIR = /oracle/logminor
 
Execute the PL/SQL procedure DBMS_LOGMNR_D.BUILD to create dictionary with the following options:
DBMS_LOGMNR_D.STORE_IN_FLAT _FILE
DBMS_LOGMNR_D.STORE_IN_REDO _LOGS
 
--with flat file option
SQL> EXECUTE DBMS_LOGMNR_D.BUILD(
     DICTIONARY_FILENAME => 'mydictionaryname.ora',
     DICTIONARY_LOCATION => '/oracle/logminor');
 
--OR
SQL> EXECUTE DBMS_LOGMNR_D.BUILD(
     'flatdictionary.ora', 
     '/oracle/logminor/',
     options => DBMS_LOGMNR_D.STORE_IN_FLAT _FILE);
 
--with redo logs option
SQL> EXECUTE DBMS_LOGMNR_D.BUILD(
     options => DBMS_LOGMNR_D.STORE_IN_REDO _LOGS);
 
If you get the following error:
SQL> EXECUTE BEGIN DBMS_LOGMNR_D.BUILD(OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS); END;
*
ERROR at line 1:
ORA-01347: Supplemental log data no longer found
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 2562
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 2617
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 1

First check if you have SUPPLEMENTAL Logging enabled,
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;


If not,
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
 
Adding or Removing the Redo Log Files for Analysis
You can specify the redo log files as following examples with the mount or nomount option of startup.


-- for new log file or the first one.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(
     LOGFILENAME => '/oracle/archivelogs/log_01 _132_6576654328.ora',
     OPTIONS => DBMS_LOGMNR.NEW);
 
-- for adding an additional log file.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(
     LOGFILENAME => '/oracle/archivelogs/log_01 _133_6576654328.ora',
     OPTIONS => DBMS_LOGMNR.ADDFILE);
 
-- for removing a log file.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(
     LOGFILENAME => '/oracle/archivelogs/log_01 _133_6576654328.ora',
     OPTIONS => DBMS_LOGMNR.REMOVEFILE);
 
Starting LogMiner
After you have create a dictionary file and specify which redo log files to analyze, you can start LogMiner and begin your analysis. Take the following steps:


--To start Log Miner with flat dictionary:
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(
     DICTFILENAME =>'/oracle/database/dictionary .ora');
 
--To start Log Miner with using dictionary from redo logs:
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(
OPTIONS =>DBMS_LOGMNR.DICT_FROM_REDO _LOGS);
 
--To start Log Miner with using Online Catalog Dictionary:
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(
OPTIONS =>DBMS_LOGMNR.DICT_FROM_ONLINE _CATALOG);


--To start Log Miner using starting and ending time:
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(
      DICTFILENAME => '/oracle/flatdictionary.ora',
      STARTTIME => TO_DATE('01-Jan-1998 08:30:00', 'DD-MON-YYYY HH:MI:SS')
      ENDTIME => TO_DATE('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'));
 
--To start Log Miner using the SCN number:
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(
      DICTFILENAME => '/oracle/dictionary.ora',
      STARTSCN => 100,
      ENDSCN => 150);
 
--To start Log Miner using the following OPTIONs:
COMMITTED_DATA_ONLY
SKIP_CORRUPTION
DDL_DICT_TRACKING
NO_DICT_RESET_ONSELECT
DICT_FROM_ONLINE_CATALOG
DICT_FROM_REDO_LOGS
 
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(
      OPTIONS =>
      DBMS_LOGMNR.DDL_DICT_TRACKING +
      DBMS_LOGMNR.NO_DICT_RESET _ONSELECT +
      DBMS_LOGMNR.DICT_FROM_REDO _LOGS);
 
Now you should be able to do this:
SQL> SELECT count(*) FROM v$logmnr_contents;
SQL> DESC v$logmnr_contents
 
Querying LogMiner
(EXAMPLES of how to read from v$logmnr_contents)
To read the log file, you need to do the following query.
SQL> COL table_name FORMAT a20
SQL> SELECT sql_redo FROM SYS.V$LOGMNR_CONTENTS;
 
To query the V$LOGMNR_CONTENTS view to see changes done by a specific user:
SQL> SELECT sql_redo, sql_undo FROM V$LOGMNR_CONTENTS
     WHERE USERNAME = 'SAASUB' AND TABLE_NAME = 'EVENT';
 
SQL> SELECT rownum, sql_redo
     FROM V$LOGMNR_CONTENTS
     WHERE sql_redo like '%SAABUD%' and
           sql_redo NOT like '%SYS%' and
           rownum < 10;
 
--with time stamp
SQL> SELECT 'Row Number: ' || rownum,
            'Date-Time: ' || to_char(timestamp,'DD-MM HH24:MI:SS'),
            'Transaction on table: ' ||
            table_name || '--->' ||
            SUBSTR(sql_redo,1,20)  
     FROM V$LOGMNR_CONTENTS
     WHERE sql_redo like '%SAABUD%' AND
           sql_redo NOT like '%SYS%' AND
           rownum < 10;
 
To determine which tables were modified in the range of time.
SQL> SELECT seg_owner, seg_name, count(*) AS Hits
     FROM V$LOGMNR_CONTENTS WHERE seg_name NOT LIKE '%$'
     GROUP BY seg_owner, seg_name;
 
SQL> SELECT rownum, to_char(timestamp,'DD-MM HH24:MI:SS')
            as "Date/Time",
            table_name,
            SUBSTR(sql_redo,1,40)  
     FROM V$LOGMNR_CONTENTS
     WHERE sql_redo like '%SAABUD%' AND
           sql_redo NOT like '%SYS%';
 
To determine who drop any objects.
SQL> SELECT rownum, to_char(timestamp,'DD-MM HH24:MI:SS')
            as "Date/Time",
            table_name,
            SUBSTR(sql_redo,1,40)  
     FROM V$LOGMNR_CONTENTS
     WHERE sql_redo like '%SAABUD%' AND
           sql_redo NOT like '%SYS%' AND
           UPPER(sql_redo) like '%DROP%';
 
Ending LogMiner
To end the log miner.
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR; 

 

 

Good Luck!

 

Google
 
Web web site