Logminer

Home Up

 

horizontal rule

Extracting info with Logminer and Smenu
1. Introduction

    Using Smenu you can extract information of logminer. Smenu will perform all administrative declarations and retrive
names and archivelogs figures for you.
    In Oracle 9iR2, there are 3 ways to use logminer :
bulletUse logminer with data dictionary in a flat file
bulletUse logminer  with data dictionary exported to redo logs
bulletUse logminer with the online data dictionary
Oracle 9iR2 function best if it can export the current dictionary to a flat file. This is done so that to keep history of DDL
as the online dictionary does not keep history of DDL.  DBMS_LOGMNR_D.BUILD('dictionary.ora',
'/database/9i/logminer') does not accept the usage of a directory.

The second option to export the dictionary to redo logs works fine,  but in production it may hurt some sensibilities, usually in
the technical incompetent part of  the hierarchy, but these people decide and pay you. Beside that it is against Smenu
philosophy to directly  impact a DB so this option is discarted.

So we are left as the least ugly thing for Smenu to implement logminer with the online dictionary :
    DBMS_LOGMNR.START_LOGMNR(options => dbms_logmnr.dict_from_online_catalog)
The drawback of using the online dictionary is that you cannot track the history of DDL nor any DDL at all.

2. List of Logminer operations with Smenu:

bulletGive SCN and get the SQL in return
bulletList contents and count for an archive log

1. Give SCN and get the SQL in return

Smenu retrireves from v$archived_logs the proper archive and give it as argument to logminer. In return you get the SQL of the SCN
[poldev]:> lgm -s 188215452797
Statement generated:
 execute dbms_logmnr.Add_logfile(options => dbms_logmnr.new, logfilename=>'/archive/POLDEV/_1_737.ARC');
execute DBMS_LOGMNR.START_LOGMNR(options => dbms_logmnr.dict_from_online_catalog);
set pagesize 100
SELECT sql_redo FROM V$LOGMNR_CONTENTS where scn = 188215452797 ;


MACHINE befsam18          - ORACLE_SID : POLDEV                                             Page:   1
Date              -  Monday    16th January   2006  16:30:05
Username          -  SYS
Show SQL command for a given SCN

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL
-------------------------------------------------------------------------------------------------------------
insert into "CUST"."ORDER_INFO"("CUST_STAT_ID","SORTING_DT","MAIL_FORMAT","POF_POST_OFFICE_ID","RACT_ID",
"DELIVERY_SEGMENT_ID","PDP_ID","PDP_SUFFIX","DAY_PARTITION","COUNT","CREATION_DATE") values
('993',TO_DATE('31-DEC-99', 'DD-MON-RR'),'1','1','1','1','1','1','11','1',TO_DATE('11-JAN-06', 'DD-MON-RR'));

2. List contents and count for an archive log

List the first 30 rows of archive sequence number 1106. Exclude the commit rows (-nc).  Rows belonging to SYS and all rows of operation type
'START' are by default not shown to decrease the spam. You can still include them with option -sys (include sys) and -ist (Include start)

[POLDEV]:> lgm -l -a 1106 -desc -r 30 -nc 

Statement generated and executed:


execute dbms_logmnr.Add_logfile(options => dbms_logmnr.new, logfilename=>'/archive/POLDEV/arch_1_1106.arc');
execute DBMS_LOGMNR.START_LOGMNR(options => dbms_logmnr.dict_from_online_catalog);
select scn, cpt, son, operation, fdate from (
               select scn, count(scn) cpt, seg_owner||'.'||seg_name son, operation,
       from V$LOGMNR_CONTENTS  where  username != 'SYS'   and OPERATION != 'START'  and  OPERATION != 'COMMIT'
            group by scn, seg_owner||'.'||seg_name , operation ,
       where rownum<=30 ;

[MISPROD]:/scripts/oracle/bin> lgm -a 663 -l -r 30

MACHINE s30301          - ORACLE_SID : MISPROD                                             Page:   1

Date              -  Tuesday   17th January   2006  14:07:16
Username          -  SYS
(help: lgm -h) List contents and counts of an archvielog


PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

               Nbr rows
     Scn         in SCN Object Owner and Name                             Operation          Operation time
------------- --------- --------------------------------------------- ------------------ ----------------------
 188185400000         2 CLIENT.PROCESSED_VOLUME_INFORMATIONS           INSERT             11-01-2006 15:25:30
 188185400000         1 CLIENT.PROCESSED_VOLUME_INFORMATIONS,IDX_PROCE INTERNAL           11-01-2006 15:25:30
                        SSED_DEST

 188185400000         1 CLIENT.PROCESSED_VOLUME_INFORMATIONS,PK_PROCES INTERNAL           11-01-2006 15:25:30
                        SED_VOLUME_INFORMATIO

 188185394500        11 CLIENT.PROCESSED_VOLUME_INFORMATIONS           INSERT             11-01-2006 15:25:16
 188185394500        12 CLIENT.PROCESSED_VOLUME_INFORMATIONS,IDX_PROCE INTERNAL           11-01-2006 15:25:16
                        SSED_DEST

 188185394500        12 CLIENT.PROCESSED_VOLUME_INFORMATIONS,PK_PROCES INTERNAL           11-01-2006 15:25:16
                        SED_VOLUME_INFORMATIO

 188185394600         1 SYS.SEQ$                                       UPDATE             11-01-2006 15:25:16
 188185394700         1 SYS.SEQ$                                       UPDATE             11-01-2006 15:25:16
 188185394700         2 CLIENT.PROCESSED_VOLUME_INFORMATIONS           INSERT             11-01-2006 15:25:16
 188185394700         2 CLIENT.PROCESSED_VOLUME_INFORMATIONS,IDX_PROCE INTERNAL           11-01-2006 15:25:16
                        SSED_DEST

 188185394700         2 CLIENT.PROCESSED_VOLUME_INFORMATIONS,PK_PROCES INTERNAL           11-01-2006 15:25:16
                        SED_VOLUME_INFORMATIO

 188185394800         3 CLIENT.PROCESSED_VOLUME_INFORMATIONS           INSERT             11-01-2006 15:25:16
 188185394800         4 CLIENT.PROCESSED_VOLUME_INFORMATIONS,IDX_PROCE INTERNAL           11-01-2006 15:25:16
                        SSED_DEST

 188185394800         4 CLIENT.PROCESSED_VOLUME_INFORMATIONS,PK_PROCES INTERNAL           11-01-2006 15:25:16
                        SED_VOLUME_INFORMATIO

188185395000        10 CLIENT.PROCESSED_VOLUME_INFORMATIONS            INSERT             11-01-2006 15:25:17
 188185395000        10 CLIENT.PROCESSED_VOLUME_INFORMATIONS,IDX_PROCE INTERNAL           11-01-2006 15:25:17
                        SSED_DEST

 188185395000        10 CLIENT.PROCESSED_VOLUME_INFORMATIONS,PK_PROCES INTERNAL           11-01-2006 15:25:17
                        SED_VOLUME_INFORMATIO

 188185395100         3 CLIENT.PROCESSED_VOLUME_INFORMATIONS           INSERT             11-01-2006 15:25:17
 188185395100         3 CLIENT.PROCESSED_VOLUME_INFORMATIONS,IDX_PROCE INTERNAL           11-01-2006 15:25:17
                        SSED_DEST

 188185395100         3 CLIENT.PROCESSED_VOLUME_INFORMATIONS,PK_PROCES INTERNAL           11-01-2006 15:25:17
                        SED_VOLUME_INFORMATIO



                                  Email : bpolarsk@yahoo.com                                        Last update : 16 January 2006