Skip to content

Using LOG Miner – Complete reference for the new oracle DBA




Suppose your users accidentally deleted some rows from the application or using some other tool. You want to restore those rows. The user knows the time around which he or she deleted those rows. You can use different ways to restore those rows

1)you can use rman to restore the database to a point in time in the past. But this option is not good here because when you restore a database to a point in time in the past then all the changes made by all other users also will be rolled back. whereas here we just want to undo the changes made by this user.

2)if the flashback functionality has been enabled for your database then you can use the flash back commands to restore this data.

3)you can use log miner tool to get these deleted rows from the redo logs and then once you get these deleted rows which will be in the form of sql statements you can then talk with the user and once the user is ok you can manually execute those sql statements to insert back those deleted rows. so in this way the user is getting back his/her rows and the database is open as well.

Read the below article which gives some concepts of the log miner tool and also gives a detailed example so you know how to use the log miner incase you have currently a production database issue and you need this info.

LogMiner Dictionary Options

LogMiner requires a dictionary to translate object IDs into object names when it returns redo data to you. LogMiner gives you three options for supplying the dictionary:

Oracle recommends that you use this option when you will have access to the source database from which the redo log files were created and when no changes to the column definitions in the tables of interest are anticipated. This is the most efficient and easy-to-use option.

Oracle recommends that you use this option when you do not expect to have access to the source database from which the redo log files were created, or if you anticipate that changes will be made to the column definitions in the tables of interest.

This option is maintained for backward compatibility with previous releases. This option does not guarantee transactional consistency. Oracle recommends that you use either the online catalog or extract the dictionary from redo log files instead.

 

Using the Online Catalog

To direct LogMiner to use the dictionary currently in use for the database, specify the online catalog as your dictionary source when you start LogMiner, as follows:

EXECUTE DBMS_LOGMNR.START_LOGMNR(-
   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
 

The online catalog option requires that the database be open.

The online catalog option is not valid with the DDL_DICT_TRACKING option of DBMS_LOGMNR.START_LOGMNR.

Showing Only Committed Transactions

When you use the COMMITTED_DATA_ONLY option to DBMS_LOGMNR.START_LOGMNR, only rows belonging to committed transactions are shown in the V$LOGMNR_CONTENTS view. This enables you to filter out rolled back transactions, transactions that are in progress, and internal operations.

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => -
  DBMS_LOGMNR.COMMITTED_DATA_ONLY);

Filtering Data by Time

To filter data by time, set the STARTTIME and ENDTIME parameters in the DBMS_LOGMNR.START_LOGMNR procedure.

To avoid the need to specify the date format in the call to the PL/SQL DBMS_LOGMNR.START_LOGMNR procedure, you can use the SQL ALTER SESSION SET NLS_DATE_FORMAT statement first, as shown in the following example.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
   DICTFILENAME => '/oracle/database/dictionary.ora', -
   STARTTIME => '01-Jan-1998 08:30:00', -
   ENDTIME => '01-Jan-1998 08:45:00'-
   OPTIONS => DBMS_LOGMNR.CONTINUOUS_MINE);

 

1) UTL_FILE_DIR must be set in the pfile ( I agree it is strange as it is deprecated in 10g)

so I deported the manipulation on acceptance oratest01  ( I copied there the archives logs I needed )

same engine, same os, it is possible , see below

Analyzing Archived Redo Log Files from Other Databases:

========================================================

You can run LogMiner on an instance of a database while analyzing redo log files from a different database. To analyze archived redo log files from other databases,

LogMiner must:

* Access a dictionary file that is both created from the same database as the  redo log files and created with the same database character set.

*

* Run on the same hardware platform that generated the log files, although it does not need to be on the same system.

* Use redo log files that can be applied for recovery from Oracle version 8.0 and later.

2) creation of a dictionary

execute dbms_logmnr_d.build(dictionary_filename=>’dictionary.ora’,dictionary_location=>’/tmp’);

(dictionary is not mandatory but avoid hieroglyph )

3) addind target to read

execute dbms_logmnr.add_logfile(logfilename=>’/tmp/arch_1_5.arc’); etc …

execute dbms_logmnr.add_logfile(logfilename=>’/tmp/arch_1_6.arc’);

you can even add the online redo log if you want

5) starting a logminer session

EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>’/tmp/dictionary.ora’);

6) useful views

SVRMGR> select * from v$logmnr_logs;                                                                                                                                                                

will show you the archives or redo you put online

SVRMGR> select * from v$logmnr_dictionary                                                                                                                                                              

show you info about your dictionary

7) getting info about sql_redo and undo

sqlplus> select substr(operation,1,10),substr(sql_redo,1, 30) ,substr(sql_undo,1,30) ,session#,username from v$logmnr_contents

8) if you need more info

SVRMGR> desc  v$logmnr_contents;                      

Nom de la colonne              Null?    Type          

—————————— ——– —-          

SCN                                     NUMBER        

TIMESTAMP                               DATE          

THREAD#                                 NUMBER        

LOG_ID                                  NUMBER        

XIDUSN                                  NUMBER        

XIDSLT                                  NUMBER        

XIDSQN                                  NUMBER        

RBASQN                                  NUMBER        

RBABLK                                  NUMBER        

RBABYTE                                 NUMBER        

UBAFIL                                  NUMBER        

UBABLK                                  NUMBER        

UBAREC                                  NUMBER         

UBASQN                                  NUMBER        

ABS_FILE#                               NUMBER        

REL_FILE#                               NUMBER        

DATA_BLK#                               NUMBER        

DATA_OBJ#                               NUMBER        

DATA_OBJD#                              NUMBER        

SEG_OWNER                               VARCHAR2(32)  

SEG_NAME                                VARCHAR2(32)  

SEG_TYPE                                NUMBER        

SEG_TYPE_NAME                           VARCHAR2(32)  

TABLE_SPACE                             VARCHAR2(32)  

ROW_ID                                  VARCHAR2(19)  

SESSION#                                NUMBER        

SERIAL#                                 NUMBER        

USERNAME                                VARCHAR2(32)  

SESSION_INFO                            VARCHAR2(4000)

ROLLBACK                                NUMBER        

OPERATION                               VARCHAR2(32)  

SQL_REDO                                VARCHAR2(4000)

SQL_UNDO                                VARCHAR2(4000)

RS_ID                                   VARCHAR2(32)  

SSN                                     NUMBER        

CSF                                     NUMBER        

INFO                                    VARCHAR2(32)  

STATUS                                  NUMBER        

PH1_NAME                                VARCHAR2(32)  

PH1_REDO                                VARCHAR2(4000)

PH1_UNDO                                VARCHAR2(4000)

PH2_NAME                                VARCHAR2(32)  

PH2_REDO                                VARCHAR2(4000)

PH2_UNDO                                VARCHAR2(4000)

PH3_NAME                                VARCHAR2(32)  

PH3_REDO                                VARCHAR2(4000)

PH3_UNDO                                VARCHAR2(4000)

PH4_NAME                                VARCHAR2(32)  

PH4_REDO                                VARCHAR2(4000)

PH4_UNDO                                VARCHAR2(4000)

PH5_NAME                                VARCHAR2(32)  

PH5_REDO                                VARCHAR2(4000)

PH5_UNDO                                VARCHAR2(4000)

9) exemple of undo records for deleted records  for Timesheet obtained  via this v$logmnr_contents view

insert into "TIMESHEETOWNER"."PROJECTRESOURCELINK"("WORKLOADID","RESOURCE_","PROJECT","A01","A02","A03","A04","A05","A06","A07","A08","A09","A10","A11","A12",

"A01ACTUAL","A02ACTUAL","A03ACTUAL","A04ACTUAL","A05ACTUAL","A06ACTUAL","A07ACTUAL","A08ACTUAL","A09ACTUAL","A10ACTUAL","A11ACTUAL","A12ACTUAL","WORKLOADY

EAR") values (‘99267′,’43’,’274′,’5′,’5′,’5′,’5′,’5′,’5′,’5′,’5′,’5′,’5′,’5′,’5′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’2008′);

insert into "TIMESHEETOWNER"."PROJECTRESOURCELINK"("WORKLOADID","RESOURCE_","PROJECT","A01","A02","A03","A04","A05","A06","A07","A08","A09","A10","A11","A12",

"A01ACTUAL","A02ACTUAL","A03ACTUAL","A04ACTUAL","A05ACTUAL","A06ACTUAL","A07ACTUAL","A08ACTUAL","A09ACTUAL","A10ACTUAL","A11ACTUAL","A12ACTUAL","WORKLOADY

EAR") values (‘99268′,’44’,’274′,’20’,’20’,’20’,’20’,’20’,’20’,’20’,’20’,’20’,’20’,’20’,’20’,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’2008′);

insert into "TIMESHEETOWNER"."PROJECTRESOURCELINK"("WORKLOADID","RESOURCE_","PROJECT","A01","A02","A03","A04","A05","A06","A07","A08","A09","A10","A11","A12",

"A01ACTUAL","A02ACTUAL","A03ACTUAL","A04ACTUAL","A05ACTUAL","A06ACTUAL","A07ACTUAL","A08ACTUAL","A09ACTUAL","A10ACTUAL","A11ACTUAL","A12ACTUAL","WORKLOADY

EAR") values (‘99275′,’51’,’41’,’5′,’5′,’5′,’5′,’5′,’5′,’5′,’5′,’5′,’5′,’5′,’5′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’2008′);

insert into "TIMESHEETOWNER"."PROJECTRESOURCELINK"("WORKLOADID","RESOURCE_","PROJECT","A01","A02","A03","A04","A05","A06","A07","A08","A09","A10","A11","A12",

"A01ACTUAL","A02ACTUAL","A03ACTUAL","A04ACTUAL","A05ACTUAL","A06ACTUAL","A07ACTUAL","A08ACTUAL","A09ACTUAL","A10ACTUAL","A11ACTUAL","A12ACTUAL","WORKLOADY

EAR") values (‘99276′,’52’,’681′,’10’,’10’,’10’,’10’,’10’,’10’,’10’,’10’,’10’,’10’,’10’,’10’,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’0′,’2008′);

10) just apply them back to the db.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.