Using Oracle LogMiner

Log miner is an amazingly useful Oracle utility. I love to use it when there is a dispute about what transactions happened and when.

Alert Log Transaction Information

If you have been looking in your alert log, and perhaps you are even using CDC, and have seen a number which has the format 0040.027.002c8951, then you have stumbled across your transaction ID. This 3 part number is shown in hex: 0040 (XIDUSN) . 027 (XIDSLT) . 002c8951 (XIDSQN)

Some of the DBA and performance views store the transaction ID as these 3 pieces, but not in hex; they are stored as decimal numbers so you have to convert the hex numbers above to decimal in order to query on them– e.g, use TO_NUMBER(‘0040′,’xxxx’), as you will see below.

Mining REDO Logs Commands

The set of commands below can be used to add the archived redo log files to your log miner session to start looking at their contents.

NOTE: I have supplemental logging enabled in my database which means that all of the object IDs get converted to the segment names automatically. Check it’s enabled from the SUPPLEMENTAL_LOG_DATA_MIN column in the V$DATABASE view

Top Tip: If you want to get more meaningful information when querying the contents from the REDO log, run a build procedure against your DB and add that logfile below first. It can be a log file which was generated after the one which you want to look at, that’s not a problem.

So, let me first show you how to create a copy of the dictionary in the REDO log for use by logminer:


This will populate a REDO log with a copy of the dictionary. You then use this archived REDO log file in the next step below. For the next step you should have available the archived REDO log file you want to mine and the one with the dictionary build in it.

NOTE: The files need to be available locally to where the database server sits. In the example below, I have put the files 1_94753.ARC and 1_94761.ARC in the E:\Logs folder on the database server.


We then start the log miner session with the command below. Once started, you can only query the V$LOGMNR_CONTENTS view from the same session that you executed the “START_LOGMNR” command in.


When you have finished you will use the END_LOGMNR command to finish the session and ensure you have freed up the resources.


No Dictionary REDO Build? No Problem…

It should be noted that the first REDO log file above should be one which contains a dictionary build in it. You can identify which of your log files has this by looking at the DICTIONARY_BEGIN column from V$ARCHIVED_LOG. If you don’t run the build command the contents displayed from your queries will not have much meaning because it will just be object IDs instead of names of objects, for example.

If you don’t have that enabled, fear not…You can still query the V$LOGMNR_CONTENTS view you just won’t have as nicely readable data but you can still search by object IDs so it’s not the end of the world. You’ll notice that if you run the above command to start your log miner session it will fail so you just have to remove the part of the command which tells it to look in the REDO log file for the data dictionary definition. For example:


Querying the Log Miner Contents

If you remember back to the beginning of this post where I mentioned that you should be able to find a transaction ID in your alert log, which will look somehting like this: 0040.027.002c8951. We’ll be using this in the first method to find out about the transaction. If you don’t have this, don’t worry, just look at method 2 below.

Method 1 is good when you know that there was a large transaction and you want to find out what caused it. Method 2 is if you just want to scan through the contents of the REDO.

Method 1

Using the above, and the notes at the beginning of this post, you can find out the xidsqn value to use in the next query. We have just taken the 1st part of the transaction ID for the xidusn value and the 2nd part of it for the xidslt value. We’ll then use the TO_NUMBER function on both:

SELECT xidsqn, count(*)
WHERE xidusn = TO_NUMBER('0040','XXXX')
  AND xidslt = TO_NUMBER('027','XXX')
GROUP BY xidsqn
  ORDER BY count(*) desc;

The output from the above query will show you where there are a lot of statements within one transaction. That will be where there is a large COUNT(*) for the xidsqn value. We can then look at just that xidsqn value, using this query:

  FROM v$logmnr_contents
WHERE xidsqn = '1778006';

Now you should be able to see exactly what statements caused the large transaction.

Method 2

If you want to just query the contents of the REDO then you can run more basic queries against the V$LOGMNR_CONTENTS view. For example, let’s say that we want to see what was happening between two times. We could use the following to do that:

WHERE timestamp BETWEEN to_date('01-05-13 15:45:20','DD-MM-YY HH24:MI:SS') and to_date('01-05-13 15:45:30','DD-MM-YY HH24:MI:SS')
ORDER BY timestamp;

You can run whatever queries you like against the V$LOGMNR_CONTENTS view so have a look at the data and see what you can discover.

Happy hunting!

Like it, share it...

Category: LogMiner

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *