Oracle Logminer

Log files are by far the most common way in software applications to keep track of user’s activities. As the world class database server Oracle also has a very strong logging mechanism. The log files in the Oracle database are normally termed as redo logs and the mechanism through which Oracle manages and generates its redo logs is known as redo mechanism. The most important use of these redo logs in the Oracle world is in recovery process. However these redo logs are also used for auditing purposes as well. As being the logs of database they are the perfect place for knowing who logged in and did what. The only question remains to be answered is how? After all Oracle generates redo logs in binary format. Here is the clue. The name of the clue is Logminer. As the name indicates it is used to dig into redo logs and get information out of it. Here is a very brief introduction of it.

Logminer is a tool used to dig into the redo log files. Redo log files store the changes in the database. It is used for both the archived redo log files and for online redo log files. It provides a comprehensive SQL interface to the redo logs; so it can be used as a data audit tool and also as a data analyzer.

Configuration:

There are four basic things that U should be familiar with in order to configure the Logminer.

1. Source Database: it’s the database that has the required redo log files that are need to be analyze.
2. Mining Database: It’s the database that Logminer uses during the phase of analyses.
3. Logminer Dictionary: Logminer dictionary enables the Logminer to return the results in columns names rather then in the form of internal object IDs. Without the dictionary the Logminer will return result in binary form.
4. Redo Logs: They are the desired redo logs that U wants to analyze. They can be online redo logs or archived redo logs.

Tablespace Management

Oracle database is the physical part of Oracle Server. Oracle database mainly consists of different kind of physical files. This physical part has also its logical part. The existence of the logical part of database eases the database administration to a great deal.

A Tablespace in Oracle database is the logical grouping of data files. There can be one or more data files inside a Tablespace but a data file can only be a part of one Tablespace. It is also the top most in units of the logical structure of database. To create a Tablespace login to Oracle with Create Tablespace privilege and issue the following command:

SQL>create tablespace test datafile ‘C:\oracle\product\10.1.0\oradata\orcl\test01.dbf’ size 10M autoextend on extent management local uniform size 1M;

We have used the all too familiar create statement with Tablespace name and the path of datafile. Remember a Tablespace must have at least one datafile. Next we have specified the size of the datafile with the option that this file is allowed to grow automatically when necessary. And finally we specified the type of extent management we want to use for the Tablespace.

Once you have created the Tablespace you can add more files to it with the following command:

SQL>alter tablespace test add datafile ‘C:\oracle\product\10.1.0\oradata\orcl\test02.dbf’ size 5M autoextend on;

If you want to drop a datafile from the Tablespace then you can use the following command:

SQL>alter tablespace test drop datafile ‘C:\oracle\product\10.1.0\oradata\orcl\test02.dbf’;

You can also make the Tablespace off line so that users cannot access the data contained in the Tablespace:

SQL>alter tablespace test offline;

You can change back this to default one i.e. on line with following command:

SQL>alter tablespace test online;

Now if you want to drop a Tablespace then you can use the following command:

SQL>drop tablespace test;

Remember this command will only remove the Tablespace and will not delete any datafiles associated with it. If you want to do that then you must use the following command:

SQL>drop tablespace test including contents and datafiles;

When you create a database using Database Configuration Assistant Oracle creates a number tablespaces. These include System, Sysaux, Temp, Users etc. System Tablespace contains the data dictionary and is therefore the most important one in the database. Database will not start if this Tablespace is missing. Sysaux Tablespace is used for assisting the system Tablespace. Temp is the special Tablespace which contains the temporary data used during the processing. While users Tablespace is the default Tablespace where user’s data resides.

Closing Oracle Instance

Closing Oracle database or Instance for that matter means that the data stored in the database will not be available to the users. It is as simple to shutdown the database as it is to startup. You can use Oracle EM or good old SQL*PLUS to do this. SQL*PLUS is personally my favorite. (It was the first thing I liked about Oracle.) Well, talking about closing the database; the simple command to do so is the Shutdown. There are four different key words that you can use with this command; each has its own effect. These are Normal, Transactional, Immediate and Abort.

When the Shutdown command is used with Normal or no suffix then Oracle will wait for all users to disconnect, save all the unsaved data back to disks (Commit Transactions) and will perform a clean shutdown. The data will be in consistent state on next startup. This type of shutdown is extremely slow because you never know when a user will disconnect from the database. When the Shutdown command is used with Transactional suffix then Oracle will only wait for the current Transactions to complete, save the data to disks and will perform a clean shutdown. Data will be consistent at next startup. This type of shutdown is faster then Normal Shutdown but is still not ideal because you can’t tell for sure when a user will commit or rollback the transaction.

When the Shutdown command is issued with Immediate as suffix then Oracle will immediately close all connections to the database and will shutdown the database. However it will make sure that the data remains in consistent state. Means that it is also a clean shutdown. This is the ideal and most frequently used way to shutdown the database. Shutdown Abort is the brutal force. It will force all users to disconnect from the database loosing all the unsaved changes. You should never use this command to close the database except when it is absolutely necessary. On next startup Instance recovery will be needed to bring back the database in consistent state.