Logminer Utility in Oracle

                  It’s a pl/sql based utility Used to mine data Queried by Users. This is most Important Ultitliy for DBA's

                  Follow the Below steps to Mine the data :

LogMiner is an Oracle utility. Using LogMiner one can query the contents of online redo log files and archived log files. 

·         It can be used as a powerful data audit tool, as well as a tool for sophisticated data analysis.

·        

          The 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.

·        

          Log Miner extracts all DDL and DML activity from the redo log files for viewing by a DBA via the dynamic performance view V$LOGMNR_CONTENTS

  Steps:

          

SQL> Alter database add supplemental log data;

1.      create a directory to store dictionary file.

$mkdir         /u01/user/demo/

2.      Specify the location of dictionary file at os level.

Sql>alter system set utl_file_dir=’/u01/user/demo’ scope=spfile;

3.      Bounce back the database.

Sql>startup force;

4.      create a dictionary file

       sql>exec dbms_logmnr_d.build(‘dfile’,’/u01/user/demo’ );

If You will get Below Error

ERROR at line 1:
ORA-01371: Complete LogMiner dictionary not found
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 1

Solution 

Cross 'UTL_FILE_DIR' parameter , and Directory is present or not Physically on your System.

Also check,   ’/u01/user/demo’ Exist or Not

SQL>select member from v$logfile ; 

5.      connect to a sys user and specify the all logfiles to Logminer session.

Sql>exec dbms_logmnr.add_logfile(‘/u01/user/prod/redo/redo01.log’);

Sql>exec dbms_logmnr.add_logfile(‘/u01/user/prod/redo/redo02.log’);

Sql>exec dbms_logmnr.add_logfile(‘/u01/user/prod/redo/redo03.log’);        

6.      Start the minning process

Sql>exec           dbms_logmnr.start_logmnr(dictfilename=>’/u01/user/demo/dfile’);

                 

 Sql>spool abc.sql

Sql>select sql_undo,sql_redo from v$logmnr_contents  where seg_owner=’USER1’ and seg_name=’EMP’;

Sql>spool off

      

 

Log miner related data dictionary views

V$LOGMNR_CONTENTS - Shows changes made to user and table information.

V$LOGMNR_DICTIONARY - Shows information about the Log Miner

dictionary file, provided the dictionary was created using the STORE_IN_FLAT_FILE option.

V$LOGMNR_LOGS - Shows information about specified redo logs. There is one row for each redo log.

V$LOGMNR_PARAMETERS - Shows information about optional Log Miner parameters, including starting and ending system change numbers (SCNs) and starting and ending times.

 

Viewing disk group clients with V$ASM_CLIENT

 How do we see Viewing disk group clients with V$ASM_CLIENT ?

SQL> SELECT dg.name AS diskgroup, SUBSTR(c.instance_name,1,12) AS instance,

2 SUBSTR(c.db_name,1,12) AS dbname, SUBSTR(c.SOFTWARE_VERSION,1,12) AS software,

3 SUBSTR(c.COMPATIBLE_VERSION,1,12) AS compatible

4 FROM V$ASM_DISKGROUP dg, V$ASM_CLIENT c

5 WHERE dg.group_number = c.group_number;

Output

 

DISKGROUP INSTANCE DBNAME SOFTWARE COMPATIBLE

------------------------------ ------------------------------------ ------------------------ --------------

DATA +ASM ERPPROD 11.2.0.2.0 11.2.0.0.0

FRA +ASM ERPPROD 11.2.0.2.0 11.2.0.0.0

REDO1 +ASM ERPPROD 11.2.0.2.0 11.2.0.0.0

REDO2 +ASM ERPPROD 11.2.0.2.0 11.2.0.0.0

Script to Analyze Undo Usage

Hello , Below is the scipt to Script to Analyze Undo Usage

SET SERVEROUTPUT ON
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';

DECLARE
    v_analyse_start_time    DATE := SYSDATE - 7;
    v_analyse_end_time      DATE := SYSDATE;
    v_cur_dt                DATE;
    v_undo_info_ret         BOOLEAN;
    v_cur_undo_mb           NUMBER;
    v_undo_tbs_name         VARCHAR2(100);
    v_undo_tbs_size         NUMBER;
    v_undo_autoext          BOOLEAN;
    v_undo_retention        NUMBER(5);
    v_undo_guarantee        BOOLEAN;
    v_instance_number       NUMBER;
    v_undo_advisor_advice   VARCHAR2(100);
    v_undo_health_ret       NUMBER;
    v_problem               VARCHAR2(1000);
    v_recommendation        VARCHAR2(1000);
    v_rationale             VARCHAR2(1000);
    v_retention             NUMBER;
    v_utbsize               NUMBER;
    v_best_retention        NUMBER;
    v_longest_query         NUMBER;
    v_required_retention    NUMBER;
BEGIN
    select sysdate into v_cur_dt from dual;
    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');

    v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);
    select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;

    DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');
    DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);
    DBMS_OUTPUT.PUT_LINE('End Time   : ' || v_analyse_end_time);
  
    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');
    DBMS_OUTPUT.PUT_LINE('--------------------------');
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');
    IF V_UNDO_AUTOEXT THEN
        DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');
    ELSE
        DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');
    END IF;
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);

    IF v_undo_guarantee THEN
        DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
    ELSE
        dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
    END IF;
    DBMS_OUTPUT.PUT_LINE(CHR(9));

    SELECT instance_number INTO v_instance_number FROM V$INSTANCE;

    DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');
    DBMS_OUTPUT.PUT_LINE('---------------------------');

    v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);
    DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);

    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
    DBMS_OUTPUT.PUT_LINE('-------------------------');

    v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);
    IF v_undo_health_ret > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Minimum Recommendation           : ' || v_recommendation);
        DBMS_OUTPUT.PUT_LINE('Rationale                        : ' || v_rationale);
        DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
    END IF;
  
    SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
    SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
    SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;

    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
    DBMS_OUTPUT.PUT_LINE('------------------------');
    DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
    DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
    DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');

END;
/