Dear Readers,
In this article, we will see the following Log Miner Configuration for Oracle Database.
We can easily track history of activity on a database from archived redolog files using log miner.
1. ENABLE SUPPLEMENTAL LOGGING FOR THE DATABASE
|
1 |
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; |
|
1 2 3 4 |
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE; SUPPLEME -------- YES |
2. CHECK THE CURRENT SCN OF DATABASE
|
1 2 3 4 |
SELECT CURRENT_SCN FROM V$DATABASE; CURRENT_SCN --------------------- 479877052 |
3. CHECK REDOLOGS EXISTING IN DATABASE
|
1 2 3 4 5 |
SELECT MEMBER FROM V$LOGFILE WHERE GROUP# = (SELECT GROUP# FROM V$LOG WHERE STATUS = 'CURRENT'); MEMBER ---------------------------------------------------------- +DATA/ORCL/ONLINELOG/group_4.552.998405713 +DATA/ORCL/ONLINELOG/group_4.552.998405714 |
4. DML OERATIONS FOR TEST CASE
|
1 2 3 4 5 6 7 8 |
CREATE TABLE TEST1(ID NUMBER); INSERT INTO TEST1 VALUES(1); INSERT INTO TEST1 VALUES(2); INSERT INTO TEST1 VALUES(3); UPDATE TEST1 SET ID=11 WHERE ID=1; UPDATE TEST1 SET ID-12 WHERE ID=2; DELETE TEST1 WHERE ID=11; COMMIT; |
5. MANUALLY CREATE A LIST OF REDO LOG FILES BEFORE YOU START LOGMINER USING DBMS_LOGMNR.ADD_LOGFILE
|
1 2 3 |
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - LOGFILENAME => '+DATA/ORCL/ONLINELOG/group_4.552.998405713', - OPTIONS => DBMS_LOGMNR.NEW); |
|
1 2 3 |
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - LOGFILENAME => '+DATA/ORCL/ONLINELOG/group_4.552.998405714', - OPTIONS => DBMS_LOGMNR.ADDFILE); |
6. CHECK CURRENT SCN OF DATABASE
|
1 2 3 4 |
SELECT CURRENT_SCN FROM V$DATABASE; CURRENT_SCN ----------- 479877794 |
7. STARTING LOGMINER USING DBMS_LOGMNR.START_LOGMNR
|
1 |
BEGIN SYS.DBMS_LOGMNR.START_LOGMNR(STARTSCN =>479877052, ENDSCN =>479877794,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.NO_ROWID_IN_STMT); END; |
8. READING CONTENTS BY USING V$LOGMNR_CONTENTS VIEW
|
1 2 3 4 5 6 7 8 9 |
SELECT SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER IS NULL OR SEG_OWNER NOT IN ('SYS', 'SYSTEM') AND SQL_UNDO LIKE '%"USER"."TEST1"%'; SQL_REDO ------------------------------------------------------------------------------------------------------------------------------------- INSERT INTO "USER"."TEST1"("ID") VALUES ('1'); INSET INTO "USER"."TEST1"("ID") VALUES ('2'); INSERT INTO "USER"."TEST1"("ID") VALUES ('3'); UPDATE "USER"."TEST1" SET "ID" = '11' WHERE "ID" = '1'; UPDATE "USER"."TEST1" SET "ID" = '12' WHERE "ID" = '2'; DELETE FROM "USER"."TEST1" WHERE "ID" = '11'; |
Thank you for giving your valuable time to read the above information.
Follow us on
Website : www.ktexperts.com
Facebook Page : KTexperts
Linkedin Page : KT EXPERTS
Loading...
