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.
