Oracle11g LogMiner analysis and redo log practices

1. Install LogMiner and run the following two scripts as a DBA User: dbmslm. SQL is used to create the DBMS_LOGMNR package, which is used to analyze log files. SQL @ $ ORACLE_HOMErdbmsadmindbmslm. SQL; Packagecreated. Grantsucceeded. Synonymcreated. dbmslmd. SQL is used to create the DBMS_LOGMNR_D package. This package is used to create

1. Install LogMiner and run the following two scripts as a DBA User: dbmslm. SQL is used to create the DBMS_LOGMNR package, which is used to analyze log files. SQL @ $ ORACLE_HOME/rdbms/admin/dbmslm. SQL; Package created. Grant succeeded. Synonym created. dbmslmd. SQL is used to create the DBMS_LOGMNR_D Package. This Package is used to create

1. Install LogMiner

Run the following two scripts as a DBA User:

• Dbmslm. SQL is used to create a DBMS_LOGMNR package, which is used to analyze log files.
  

SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql;Package created.Grant succeeded.Synonym created.

• Dbmslmd. SQL is used to create the DBMS_LOGMNR_D package, which is used to create a data dictionary file.
  

SQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql;Package created.Synonym created.

2. Set the UTL_FILE_DIR parameter.

Add the UTL_FILE_DIR parameter. The value of this parameter is the directory where the data dictionary file is placed on the server,

SQL> CREATE DIRECTORY utlfile AS '/home/oracle/logminer';Directory created.SQL> alter system set utl_file_dir='/home/oracle/logminer' scope=spfile;System altered.

Restart the database to make the above settings take effect.

SQL> shutdown immediate;SQL> startup;SQL> show parameters utl_file_dir;

3. Start supplemental logs

Supplemental logs are not necessary for LogMiner log analysis. However, if you do not enable supplemental logs, many result set information of the analytical logs will be displayed as "UNKNOWN". The following describes how to enable minimum supplemental logs.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;Database altered.SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;SUPPLEME -------- YES

4. Create a dedicated LogMiner user

It is not a must. It is actually a secure and convenient management.

5. Create a data dictionary

SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'/home/oracle/logminer');

6. Add online logs or archive logs to be analyzed

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/home/oracle/flash_recovery_area/PRIMARY/archivelog/2016_04_25/o1_mf_1_199_ckv04o6w_.arc',OPTIONS => DBMS_LOGMNR.NEW);SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/home/oracle/flash_recovery_area/PRIMARY/archivelog/2016_04_25/o1_mf_1_201_ckv08jyp_.arc',OPTIONS => DBMS_LOGMNR.ADDFILE);PL/SQL procedure successfully completed.SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/home/oracle/flash_recovery_area/PRIMARY/archivelog/2016_04_25/o1_mf_1_200_ckv05pmp_.arc',OPTIONS => DBMS_LOGMNR.ADDFILE);PL/SQL procedure successfully completed.

7. Use a dictionary to analyze archived log files

SQL> EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logminer/dictionary.ora');

8. Online log analysis instance

• Insert data

SQL> insert into nn.t1 values(1000,'succ');1 row created.SQL> commit;Commit complete.

• Query the serial number of the Current Log

SQL>  SELECT group#, sequence#, status, first_change#, first_time FROM V$log ORD                                                                             ER BY first_change#;  GROUP#  SEQUENCE# STATUS           FIRST_CHANGE# FIRST_TIM---------- ---------- ---------------- ------------- ---------         1        208 INACTIVE               2825768 25-APR-16         2        209 INACTIVE               2825872 25-APR-16         3        210 CURRENT                2845771 25-APR-16

• Add to current log Group

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/home/oracle/oradata/orcl/redo03.log',OPTIONS => DBMS_LOGMNR.ADDFILE);PL/SQL procedure successfully completed.

• Use LogMiner for analysis

SQL>  EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logminer/dictionary.ora');PL/SQL procedure successfully completed.

• Query analysis results

SQL> SELECT sql_redo, sql_undo, seg_owner FROM v$logmnr_contents WHERE seg_name='T1';SQL_REDO      SQL_UNDO          SEG_OWNERinsert into "NN"."T1"("TID","TNAME") values ('1000','succ');delete from "NN"."T1" where "TID" = '1000' and "TNAME" = 'succ' and ROWID = 'AAAR7YAAEAAAACrAAD';NN

9. Disable LogMiner sessions

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;