A brief discussion on the use of LogMiner
- 2020-06-15 10:24:40
- OfStack
Logminer is a tool that every Dba should be familiar with. When you need to do incomplete recovery due to the user's wrong operation in one day, you cannot be sure when the operation was done, so it will be very difficult for you to recover the lost data completely. LogMiner will help you determine the exact time of the error.
My test environment is es5EN4.3.3 operating system and Oracle9.2.0.3 test library.
1. Some notes on LogMiner
LogMiner can help you determine the specific time and number of various DML, DDL operations issued at a certain time, based on archived log files and online log files.
2. It can only be used in Oracle8i and later, but it can analyze Oracle8 logs.
3. Oracle8i can only be used to analyze DML operation, and Oracle9i can analyze DDL operation.
4. LogMiner does not support index organization tables, Long, LOB and collection types.
5. MTS cannot be used in the LogMiner environment.
2. Operation steps
1. Set the location of data files used for LogMiner analysis
This is the process of setting the utl_file_dir parameter, my example:
Add the following line to the initctc.ora file
utl_file_dir=/u01/arch
b, database
oracle > sqlplus /nolog
sql > conn / as sysdba
sql > shutdown immediate
sql > statup
2. The data dictionary file is generated by dbms_ES68en_d.build ().
SQL > BEGIN
2 dbms_logmnr_d.build(
3 dictionary_filename = > ’logminer_dict.dat’,
4 dictionary_location = > ’/u01/arch’
5 );
6 END;
7 /
dictionary_location refers to the location of the Logminer data dictionary file, which must match the setting of utl_file_dir.
dictionary_filename refers to the name of the dictionary file placed in its location, which can be arbitrarily chosen.
3. Establish a log analysis table
To establish the log analysis table database, the database must be in mount or nomount state, start the database to mount state.
sqlplus /nolog
sql > conn / as sysdba
sql > shutdown immediate
sql > starup mount
Set up log analysis table using dbms_ES127en.add_ES129en ()
SQL > BEGIN
2 dbms_logmnr.add_logfile(
3 options = > dbms_logmnr.new,
4 logfilename = > ’/u01/arch/arc_ctc_0503.arc’
5 );
6 END;
7 /
options has three values. dbms_logmnr. new is used to build a log analysis table. dbms_logmnr.addfile is used to add log files for analysis; dbms_logmnr.removefile is used to remove log files for analysis.
4. Add log files for analysis.
SQL > BEGIN
2 dbms_logmnr.add_logfile(
3 options = > dbms_logmnr.addfile,
4 logfilename = > ’/u01/arch/arc_ctc_0504.arc’
5 );
6 END;
7 /
Use to remove the file from the log analysis table without parsing.
SQL > BEGIN
2 dbms_logmnr.add_logfile(
3 options = > dbms_logmnr.removefile,
4 logfilename = > ’/u01/arch/arc_ctc_0503.arc’
5 );
6 END;
7 /
5. Start LogMiner for analysis.
SQL > BEGIN
2 dbms_logmnr.start_logmnr(
3 dictfilename = > ’/u01/arch/logminer_dict.dat’,
4 starttime = > to_date(’20030501 12:15:00’,’yyyymmdd hh24:mi:ss’),
5 endtime = > to_date(’20030501 15:40:30’,’yyyymmdd hh24:mi:ss’)
6 );
7 END;
8 /
Analyze the time period from 12:15 to 15:40 on May 1, 2003, and put the results into a data dictionary for querying. There are also two parameters, StartScn(starting scn) and EndScn(ending Scn).
6. To check the results of log analysis, you can query v$logmnr_contents
a, view DML operation, example:
SELECT operation,
sql_redo,
sql_undo,
FROM V$logmnr_contents
WHERE seg_name = ’QIUYB’;
OPERATION SQL_REDO SQL_UNDO
---------- -------------------------- --------------------------
INSERT inser into qiuyb.qiuyb ... delete from qiuyb.qiuyb...
Where operation refers to the operation, sql_redo refers to the actual operation, and sql_undo refers to the opposite operation for cancellation.
b, check DDL operation, example:
SELECT timstamp,
sql_redo
FROM v$logmnr_contents
WHERE upper(sql_redo) like ’%TRUNCATE%’;
7. End the analysis of LogMiner.
SQL > BEGIN
2 dbms_logmnr.end_logmnr;
3 end;
4 /
3. Data dictionary related to LogMiner.
v$loglist It is used to display some information about the history log file
v$logmnr_dictionary Because logmnr can have multiple dictionary files, this view is used to display this information.
It is used to display the parameters of logmnr
It is used to display log list information for analysis.
My test environment is es5EN4.3.3 operating system and Oracle9.2.0.3 test library.
1. Some notes on LogMiner
LogMiner can help you determine the specific time and number of various DML, DDL operations issued at a certain time, based on archived log files and online log files.
2. It can only be used in Oracle8i and later, but it can analyze Oracle8 logs.
3. Oracle8i can only be used to analyze DML operation, and Oracle9i can analyze DDL operation.
4. LogMiner does not support index organization tables, Long, LOB and collection types.
5. MTS cannot be used in the LogMiner environment.
2. Operation steps
1. Set the location of data files used for LogMiner analysis
This is the process of setting the utl_file_dir parameter, my example:
Add the following line to the initctc.ora file
utl_file_dir=/u01/arch
b, database
oracle > sqlplus /nolog
sql > conn / as sysdba
sql > shutdown immediate
sql > statup
2. The data dictionary file is generated by dbms_ES68en_d.build ().
SQL > BEGIN
2 dbms_logmnr_d.build(
3 dictionary_filename = > ’logminer_dict.dat’,
4 dictionary_location = > ’/u01/arch’
5 );
6 END;
7 /
dictionary_location refers to the location of the Logminer data dictionary file, which must match the setting of utl_file_dir.
dictionary_filename refers to the name of the dictionary file placed in its location, which can be arbitrarily chosen.
3. Establish a log analysis table
To establish the log analysis table database, the database must be in mount or nomount state, start the database to mount state.
sqlplus /nolog
sql > conn / as sysdba
sql > shutdown immediate
sql > starup mount
Set up log analysis table using dbms_ES127en.add_ES129en ()
SQL > BEGIN
2 dbms_logmnr.add_logfile(
3 options = > dbms_logmnr.new,
4 logfilename = > ’/u01/arch/arc_ctc_0503.arc’
5 );
6 END;
7 /
options has three values. dbms_logmnr. new is used to build a log analysis table. dbms_logmnr.addfile is used to add log files for analysis; dbms_logmnr.removefile is used to remove log files for analysis.
4. Add log files for analysis.
SQL > BEGIN
2 dbms_logmnr.add_logfile(
3 options = > dbms_logmnr.addfile,
4 logfilename = > ’/u01/arch/arc_ctc_0504.arc’
5 );
6 END;
7 /
Use to remove the file from the log analysis table without parsing.
SQL > BEGIN
2 dbms_logmnr.add_logfile(
3 options = > dbms_logmnr.removefile,
4 logfilename = > ’/u01/arch/arc_ctc_0503.arc’
5 );
6 END;
7 /
5. Start LogMiner for analysis.
SQL > BEGIN
2 dbms_logmnr.start_logmnr(
3 dictfilename = > ’/u01/arch/logminer_dict.dat’,
4 starttime = > to_date(’20030501 12:15:00’,’yyyymmdd hh24:mi:ss’),
5 endtime = > to_date(’20030501 15:40:30’,’yyyymmdd hh24:mi:ss’)
6 );
7 END;
8 /
Analyze the time period from 12:15 to 15:40 on May 1, 2003, and put the results into a data dictionary for querying. There are also two parameters, StartScn(starting scn) and EndScn(ending Scn).
6. To check the results of log analysis, you can query v$logmnr_contents
a, view DML operation, example:
SELECT operation,
sql_redo,
sql_undo,
FROM V$logmnr_contents
WHERE seg_name = ’QIUYB’;
OPERATION SQL_REDO SQL_UNDO
---------- -------------------------- --------------------------
INSERT inser into qiuyb.qiuyb ... delete from qiuyb.qiuyb...
Where operation refers to the operation, sql_redo refers to the actual operation, and sql_undo refers to the opposite operation for cancellation.
b, check DDL operation, example:
SELECT timstamp,
sql_redo
FROM v$logmnr_contents
WHERE upper(sql_redo) like ’%TRUNCATE%’;
7. End the analysis of LogMiner.
SQL > BEGIN
2 dbms_logmnr.end_logmnr;
3 end;
4 /
3. Data dictionary related to LogMiner.
v$loglist It is used to display some information about the history log file
v$logmnr_dictionary Because logmnr can have multiple dictionary files, this view is used to display this information.
It is used to display the parameters of logmnr
It is used to display log list information for analysis.