1. Using the Online Catalog: (Set LogMiner in Source Database)
Supplemental logging places additional column data into the redo log file whenever an UPDATE operation is performed.
Step-1:
Ensure that you have on at a minimal level supplemental logging.
To work with logMiner you must have database supplemental logging on of the source database at a minimum level.
By default, Oracle DB does not provide any supplemental logging, that means, by default LogMiner is not usable.
You can check your supplemental logging on of off by following commands,
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
In order to on it at a minimal level,
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
Step-2:
Install the DBMS_LOGMNR package.
The next step is to install DBMS_LOGMNR package. If you have already installed then ignore this steps. You can install this package by running $ORACLE_HOME/rdbms/admin/dbmslm.sql script. If you create your database with dbca then this script run automatically. So you can ignore this step. However if you create database manually with CREATE DATABASE ... command then you must run the script before using logminer. That is ,
SQL>@$ORACLE_HOME/rdbms/admin/dbmslm.sql
Step-3:
Grant the EXECUTE_CATALOG_ROLE role.
The user who will do the mining task give him EXECUTE_CATALOG_ROLE. Here user is nahar.
SQL>GRANT EXECUTE_CATALOG_ROLE TO nahar;
Step-4:
Create the synonym. nahar creates a public synonym:
CREATE PUBLIC SYNONYM dbms_logminer_nahar FOR SYS.DBMS_LOGMNR;
--All above four steps are needed just for once.
************************************************************** kisukkhon por por abar ekhan theke shuru
Step-5:
Specify the scope of the mining.
Now you decide on which file you will do the analysis. You may have interest over archived redo log files or online redo log files based on your scenario.
i. Analysis Redo logs:
Suppose you have recent problem in your database and so you might show interest of your online redo log files. You can see
current online redo logs by,
SQL> SELECT distinct member LOGFILENAME FROM V$LOGFILE;
LOGFILENAME
--------------------------------------------------------------------------------
/oracle/app/oradata/FolderName/redo02.log
/oracle/app/oradata/FolderName/redo03.log
/oracle/app/oradata/FolderName/redo01.log
SQL>BEGIN DBMS_LOGMNR.ADD_LOGFILE
('/oracle/app/oradata/FolderName/redo01.log');
END;
/
PL/SQL procedure successfully completed.
BEGIN DBMS_LOGMNR.ADD_LOGFILE
('/oracle/app/oradata/FolderName/redo02.log');
END;
/
PL/SQL procedure successfully completed.
BEGIN DBMS_LOGMNR.ADD_LOGFILE
('/oracle/app/oradata/FolderName/redo03.log');
END;
/
PL/SQL procedure successfully completed.
Practice:
To add multiple log file:
SQL>BEGIN DBMS_LOGMNR.ADD_LOGFILE
('/oradata2/data1/dbase/redo01.log');
DBMS_LOGMNR.ADD_LOGFILE
('/oradata2/data1/dbase/redo03.log');
END;
/
ii. Analysis Archive logs:
Suppose you have past problem in your database and so you might show interest of your archive log files.
Sometimes, you want to mine the redo log file that was most recently archived. You can see --
To check recent archive log:
SQL> SELECT NAME FROM V$ARCHIVED_LOG
WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
NAME
--------------------------------------------------------------------------------
/oracle/database_name/archivelog/2008_08_27/o1_mf_1_8516_4cb52pvt_.arc
SQL> BEGIN DBMS_LOGMNR.ADD_LOGFILE
('/oracle/database_name/archivelog/2008_08_27/o1_mf_1_8516_4cb52pvt_.arc');
END;
/
Practice:
SQL>column NAME format a70
SELECT NAME,to_char(FIRST_TIME,'mm/dd/yy hh24:mi:ss') Time FROM V$ARCHIVED_LOG
WHERE FIRST_TIME like ('27-AUG-08%'); --to get the 26 august's archive log
Step-6:
To direct LogMiner to use the dictionary currently in use for the database, specify the online catalog as your dictionary source -
Start the LogMiner session and specify a dictionary.
To start the LogMiner session:
BEGIN
DBMS_LOGMNR.START_LOGMNR
(options =>
dbms_logmnr.dict_from_online_catalog);
END;
/
PL/SQL procedure successfully completed.
Using the OPTIONS parameter, it is specified that Oracle Database read the dictionary information to convert the object names from the online catalog while starting LogMiner.
Step-7: ***
Request the redo data
Check contents from V$LOGMNR_CONTENTS view.
To get information and when DML or DDL happened in the V$LOGMNR_CONTENTS about table STUDENT we can issue
SQL> set lines 200
SQL> set pages 0 -------to delete the column header
sys@DatabaseName>column USERNAME format a20
sys@DatabaseName>column SEG_NAME format a15
sys@DatabaseName>column SEG_TYPE_NAME format a15
sys@DatabaseName>column TABLE_SPACE format a15
SQL> select username, to_char(timestamp,'mm/dd/yy hh24:mi:ss') timestamp,
seg_type_name, seg_name, table_space, session# SID, serial# , operation
from v$logmnr_contents
where table_name = 'STUDENT';
We can get SQL_UNDO and SQL_REDO information by,
SQL>col SQL_REDO format a30
SQL> col SQL_UNDO format a30
SQL> select sql_undo, sql_redo
from v$logmnr_contents
where table_name = 'STUDENT' and OPERATION='UPDATE';
Request the archive data:
select username, to_char(timestamp,'mm/dd/yy hh24:mi:ss') timestamp,
seg_type_name, seg_name, table_space, session# SID, serial# , operation
from v$logmnr_contents
where table_name = 'LOGIN';
select username, to_char(timestamp,'mm/dd/yy hh24:mi:ss') timestamp,
seg_type_name, seg_name, table_space, session# SID, serial# , operation
from v$logmnr_contents
where table_name = 'LOGIN'
and timestamp like ('27-AUG-08%'); --to get the info of a specific date
step-8:
End the LogMiner session.
Use the DBMS_LOGMNR.END_LOGMNR procedure.
SQL>
BEGIN
DBMS_LOGMNR.END_LOGMNR;
END;
/
PL/SQL procedure successfully completed.
