|
You can use the
Oracle LogMiner
utility to enable you to query redo log files through a SQL interface.
Dictionary
You can use dictionary so when you query information from log files, you
don't see
reference object id instead of object name. You can use the following
method to extract object name instead of object id. Extracting the
Dictionary to a Flat File or to Redo Log Files
Using the Online Catalog
LogMiner Restrictions
The following restrictions apply (The following are not supported)
o Data types LONG and LOB
o Simple and nested abstract data types ( ADTs)
o Collections (nested tables and VARRAYs)
o Object Refs
o Index Organized Tables (IOTs)
Steps to use logminer:
Create a Dictionary Flat File for an Oracle Database
Add the
UTL_FILE_DIR
parameter to the init<SID>.ora file and then stop and start the
database:
For example:
# cd $ORACLE_HOME/dbs
# vi initmydb.ora -- Then add the
following line and save the file.
UTL_FILE_DIR = /oracle/logminor
Execute the PL/SQL procedure DBMS_LOGMNR_D.BUILD to create dictionary
with the following options:
DBMS_LOGMNR_D.STORE_IN_FLAT _FILE
DBMS_LOGMNR_D.STORE_IN_REDO _LOGS
--with
flat file option
SQL> EXECUTE DBMS_LOGMNR_D.BUILD(
DICTIONARY_FILENAME => 'mydictionaryname.ora',
DICTIONARY_LOCATION => '/oracle/logminor');
--OR
SQL> EXECUTE DBMS_LOGMNR_D.BUILD(
'flatdictionary.ora',
'/oracle/logminor/',
options =>
DBMS_LOGMNR_D.STORE_IN_FLAT _FILE);
--with
redo logs option
SQL> EXECUTE DBMS_LOGMNR_D.BUILD(
options => DBMS_LOGMNR_D.STORE_IN_REDO _LOGS);
If you get the following error:
SQL> EXECUTE BEGIN DBMS_LOGMNR_D.BUILD(OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
END;
*
ERROR at line 1:
ORA-01347: Supplemental log data no longer found
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 2562
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 2617
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 1
First check if you have SUPPLEMENTAL Logging enabled,
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
If not,
SQL>
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Adding or Removing the Redo Log Files for Analysis
You can specify the redo log files as following examples with the mount
or nomount option of startup.
-- for new log file or the first one.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(
LOGFILENAME => '/oracle/archivelogs/log_01 _132_6576654328.ora',
OPTIONS => DBMS_LOGMNR.NEW);
-- for adding an additional log file.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(
LOGFILENAME => '/oracle/archivelogs/log_01 _133_6576654328.ora',
OPTIONS => DBMS_LOGMNR.ADDFILE);
-- for removing a log file.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(
LOGFILENAME => '/oracle/archivelogs/log_01 _133_6576654328.ora',
OPTIONS => DBMS_LOGMNR.REMOVEFILE);
Starting LogMiner
After you have create a dictionary file and specify which redo log files
to analyze, you can start LogMiner and begin your analysis. Take the
following steps:
--To
start Log Miner with flat dictionary:
SQL>
EXECUTE DBMS_LOGMNR.START_LOGMNR(
DICTFILENAME =>'/oracle/database/dictionary .ora');
--To
start Log Miner with using dictionary from redo logs:
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(
OPTIONS =>DBMS_LOGMNR.DICT_FROM_REDO _LOGS);
--To
start Log Miner with using Online Catalog Dictionary:
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(
OPTIONS =>DBMS_LOGMNR.DICT_FROM_ONLINE _CATALOG);
--To
start Log Miner using starting and ending time:
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(
DICTFILENAME => '/oracle/flatdictionary.ora',
STARTTIME => TO_DATE('01-Jan-1998 08:30:00', 'DD-MON-YYYY HH:MI:SS')
ENDTIME => TO_DATE('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'));
--To start Log Miner using the SCN number:
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(
DICTFILENAME => '/oracle/dictionary.ora',
STARTSCN => 100,
ENDSCN => 150);
--To start Log Miner using the following OPTIONs:
COMMITTED_DATA_ONLY
SKIP_CORRUPTION
DDL_DICT_TRACKING
NO_DICT_RESET_ONSELECT
DICT_FROM_ONLINE_CATALOG
DICT_FROM_REDO_LOGS
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(
OPTIONS =>
DBMS_LOGMNR.DDL_DICT_TRACKING +
DBMS_LOGMNR.NO_DICT_RESET _ONSELECT +
DBMS_LOGMNR.DICT_FROM_REDO _LOGS);
Now you should be able to do this:
SQL> SELECT count(*) FROM v$logmnr_contents;
SQL> DESC v$logmnr_contents
Querying LogMiner
(EXAMPLES of how to read from v$logmnr_contents)
To read the log file, you need to do the following query.
SQL> COL table_name FORMAT a20
SQL> SELECT sql_redo FROM SYS.V$LOGMNR_CONTENTS;
To query the V$LOGMNR_CONTENTS view to see changes done by a specific
user:
SQL> SELECT sql_redo, sql_undo FROM V$LOGMNR_CONTENTS
WHERE USERNAME = 'SAASUB' AND TABLE_NAME = 'EVENT';
SQL> SELECT rownum, sql_redo
FROM V$LOGMNR_CONTENTS
WHERE sql_redo like '%SAABUD%' and
sql_redo NOT like '%SYS%' and
rownum < 10;
--with time stamp
SQL> SELECT 'Row Number: ' || rownum,
'Date-Time: ' || to_char(timestamp,'DD-MM HH24:MI:SS'),
'Transaction on table: ' ||
table_name || '--->' ||
SUBSTR(sql_redo,1,20)
FROM V$LOGMNR_CONTENTS
WHERE sql_redo like '%SAABUD%' AND
sql_redo NOT like '%SYS%' AND
rownum < 10;
To determine which tables were modified in the range of time.
SQL> SELECT seg_owner, seg_name, count(*) AS Hits
FROM V$LOGMNR_CONTENTS WHERE seg_name NOT LIKE '%$'
GROUP BY seg_owner, seg_name;
SQL> SELECT rownum, to_char(timestamp,'DD-MM HH24:MI:SS')
as "Date/Time",
table_name,
SUBSTR(sql_redo,1,40)
FROM V$LOGMNR_CONTENTS
WHERE sql_redo like '%SAABUD%' AND
sql_redo NOT like '%SYS%';
To determine who drop any objects.
SQL> SELECT rownum, to_char(timestamp,'DD-MM HH24:MI:SS')
as "Date/Time",
table_name,
SUBSTR(sql_redo,1,40)
FROM V$LOGMNR_CONTENTS
WHERE sql_redo like '%SAABUD%' AND
sql_redo NOT like '%SYS%' AND
UPPER(sql_redo) like '%DROP%';
Ending LogMiner
To end the log miner.
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;
Good Luck!
|