Oracle DBA Study Notes-log files (use logminer to analyze logs)

previous: oracle DBA Study Notes-control files

RedoLog Files

Redo log files record the changes made to the database (or changes to transactions) in the form of redo records, including the execution of DMLOrDDLStatement modification to the database,
It also includesDBAModify the database structure,Redo log files also need to be reused.

 

Log File writing principles:
1.Write order must strictly follow the execution order of concurrent transactions
2.You must write the redo log file before writing the data file.

Redo log write cycle:
When the first group of logs is fully written, switch the redo log (Alter system switch logfileTo write the record of the redo log to the second redo log file, and so on.

Archive log list

Select * from V $ log
StatusIf yesInactiveIndicates that no user or transaction is currently accessed, and data can be overwritten.
ArchiveIndicates whether the log information is archived,NoIndicates no archive

Switch log serial number

Alter sytem switch logfile

Select * from V $ logfile

Add log files

Alter database add logfile

('D: \ oracle \ oradata \ work \ log3a. log', 'd: oracle \ oradata \ work \ log3b. log ')

 

UseLogminerAnalyze log files

1.Check whether or not to setUtl_file_dirParameters

Show parameters utl_file_dir

IfValueIf the value is not null, it indicates that the parameter has been set.

Otherwise, you need to set it.

The settings are as follows:

After the settings are completeOracleInitialize the ParameterSpfile.

D: \ oracle \ ora90 \ database \ spfilework. ora

 

2.Extract information from the database data dictionary and createLogminerThe flat data dictionary file used.

Execution ProcessExecute dbms_logmnr_d.build ('woody. ora ', 'd: \ log '),Need to ensureD: \ logThe folder exists.

-

After the execution is complete,D: \ logCreatedWoody. oraFile

3.Add log files

First

Execute dbms_logmnr.add_logfile ('d: \ oracle \ oradata \ work \ redo01.log ', Options => dbms_logmnr.new)

Second

Execute dbms_logmnr.add_logfile ('d: \ oracle \ oradata \ work \ redo02.log ', Options => dbms_logmnr.addfile)

Third

Execute dbms_logmnr.add_logfile ('d: \ oracle \ oradata \ work \ redo03.log ', Options => dbms_logmnr.addfile)

You can use

Select * from V $ logmnr_logsView

3.StartLogminer.

Run

Execute dbms_logmnr.start_logmnr (dictfilename => 'd \ log \ Woody. ora ')

The following error may occur:

 

ModifyNts_languageYes (it may be caused by a log Date Format problem)

 

Execute the previous process.

4.View operation logs

ViewV $ logmnr_contentsThen, through analysisV $ logmnr_contents.

The result is as follows:

5.EndLogminerSession.(LogminerSystem resources and database resources are occupied.)

Execute dbms_logmnr.end_logmnr