Another hectic day in
life of DBA. Whenever any critical issues come up we all are up on our toes.
Well the problem description: The system was generating archive logs at an
alarming rate. Even though the archive_lag_target is set is 900s the archive logs
were getting generated very frequently and with huge size causing the
mountpoint on which the archives were stored to get full frequently thereby
halting the database.
We dug into the
database to find out if there are any long running queries in the database and to
surprise we found there isn’t any. Then we went on to search if there are any
deadlocks in the system. All in vain. No deadlocks in the system. Tried almost
all the possible first hand tactics to analyze the database but all in vain.
Then why so much archives were generated and that too at an alarming rate.
There must be something going on in the database. So then we decided to go on
and analyze the archives that were generated.
Archives logs are not
readable to naked eyes. They are in encrypted form. Hence you need to use the
Oracle provided utility of LOGMINER to analyze the online redo log files and
archived log files. I wont go into much detail in explaining about LOGMINER
utility. You can get a lot of documentation on internet regarding this utility.
Just google it.
In this blog I would be
more concentrating on how to use this utility and analyze the log files.
The first and foremost
requirement while using this utility is to check whether the database is in
archive log mode or not? For using the logminer utility the DB should be in
archive log mode.
Then Get the list of all
the archives present in your database. Here I am altering the session to
required data format. It becomes easy for analysis.
Now Set the initialization parameter,
UTL_FILE_DIR,in the initialization parameter file. For example, to set
UTL_FILE_DIR to use “C:\app\dict” as the
directory where the dictionary file is placed, enter the following in the
initialization parameter file. Please note that UTL_FILE_DIR is a static
parameter. Hence scope needs to be defined as SPFILE and DB restart is needed
for the same. Nonetheless it is advised that you do all the analysis part on
the TEST Server. Just copy the required Archive log files from Production to
Test setup and analyze them.
Now as the parameter is
set, we move on with the actual part.
Execute the PL/SQL procedure DBMS_LOGMNR_D.BUILD.
Specify a filename for the
dictionary and a directory path name for the file. This procedure creates the
dictionary file. For example,
enter the following to create the file dict..ora in “C:\app\dict”. Make sure the directory is
already created before creating the dictionary file.
Now register all the logfiles that you need to analyze.
Check if all the archives to want to analyze are
registered in the database or not
Now start the logminer session.
Finally you have done it. Here you have to note that the
contents analyzed by the logminer are stored on the temporary view V$LOGMNR_CONTENTS which is session specific.
So before you disconnect from session create a permanent table from this view
so that you can analyze the contents later on too.
Volla!!!! The table is created. Now you can query this table and find the
suitable information. In my case some nasty module of user was continuously
firing DML statements. The statements lasted for very short duration of time. Hence
were not visible any any of the tricks we used for analysis before.
