Mining Oracle Logs for "Big Killers"
Editor's Note：
LogMiner is a powerful tool for Oracle log mining, using this tool you can easily get the specific content in Oracle redo log files (archived log files), LogMiner analysis tool is actually composed of a set of PL/SQL packages and some dynamic views, it is distributed as part of Oracle Database, a completely free tool provided by oracle company. This article focuses on demonstrating the use of LogMiner and visually showing what LogMiner can do.
environments：Oracle 11.2.0.4 RAC
- 1. Query the current log group
- 2. Business user insertion operation
- 3. Archive Log Switching
- 4. Business user insertion operation
- 5. Archive Log Switching
- 6. Business user update operations
- 7. Archive Log Switching
- 8. Identification of logs to be analyzed
- 9. Backing up archived logs
- 10. useLogMiner analysis
Query the current log group
usesys User queriesOracle Current log group of the database:
Current log here（current） be： Coco Lee (1975-), pop singer, songwriter and actress
thread 1 sequence 30
thread 2 sequence 25
Business user insertion operation
Simulate business usersjingyu insertT2 Table data：
Archive Log Switching
To distinguish the different operations of each log， Manual archive switch for the database here， Simulates an actual archive switch in reality。
Business user insertion operation
Simulate business usersjingyu removingT2 Table Partial data：
Archive Log Switching
To distinguish the different operations of each log， Manual archive switch for the database here， Simulates an actual archive switch in reality。
Business user update operations
Simulate business usersjingyu updateT2 Table Partial data：
Archive Log Switching
To distinguish the different operations of each log， Manual archive switch for the database here， Simulates an actual archive switch in reality。
Identification of logs to be analyzed
It needs to be used after confirmationLogMiner The log of the analysis：
Backing up archived logs
The relevant archives will becopy Back it up：
The archived log file that was backed up is as follows：
useLogMiner analysis
useLogMiner Analyzing archived logs：
inquiryv$logmnr_contents
Experimental findings：
withusername Related records cannot be queried for conditions， Final confirmationusername It's allunknown instead of the business user who actually executes the statementjingyu。 And dig out the logssql_redo This field is completeSQL， Can be adoptedlike query。
For example, I analyze the logs of the update operation， You can get the following results：
So far，LogMiner The basic operation experiment is complete。
attach
andLogMiner Some operational command references：
Finally, confirm that if additional logs are turned on，username You can capture the correct value：
you can see it， Additional logs are turned on， it will display normallyusername information。
Recommended>>
1、Detailing the top 10 tech trends for 2018 artificial intelligence will be ubiquitous
2、Emergency diffusion Android exposed to deadly vulnerability People can take their own phones and steal your money
3、Pudenji TGreen Micromodule Data Center Solution II
4、A few ways to test your internet speed online
5、The next ETH 2018 Cryptocurrency Leaders Blockchain 30 is coming