Point-in-time Data Recovery

We can recover data back to a specific Point-in-time in two manners:

• flashback version + flashback query
• log miner

flashback version + flashback query
this is obliged to use AUTO UNDO MANAGEMENT and the ability to recover data back to point-in-time is limited by the undo retention policy and DML operations of the database.

log miner

It’s obliged for the database to run in archivelog mode and it can be recovered back to time where it’s protected by the archived logs.

Step 1) identified the archived logs + online redo logs containing the change records

select SEQUENCE#, FIRST_TIME, ARCHIVED, DELETED from v$archived_log
where FIRST_TIME between to_date('04/02/2011 17:30', 'dd/mm/yyyy hh24:mi')
and  to_date('04/02/2011 18:30', 'dd/mm/yyyy hh24:mi');

SEQUENCE# FIRST_TIM ARC DEL
---------- --------- --- ---
5698 04-FEB-11 YES YES
5699 04-FEB-11 YES YES
5700 04-FEB-11 YES YES
5701 04-FEB-11 YES YES

Step 2) Restore archived logs from backup

alias RMAN
RMAN='export NLS_DATE_FORMAT='\''yyyy-mm-dd hh24:mi:ss'\''; rman target / catalog glprman/glprman@GOLDRMAN'

RMAN>  list backup of archivelog from logseq 5698 until logseq 5701;

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
35542   658.55M    DISK        00:02:02     2011-02-04 18:32:10
BP Key: 35545   Status: AVAILABLE  Compressed: YES  Tag: ARCH_COMP
Piece Name: /export/backup/GOLD/GLP/GOLDPROD/rman/archivelog/arch_3098344053_GOLDPROD_1023_1_20110204

List of Archived Logs in backup set 35542
Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
---- ------- ---------- ------------------- ---------- ---------
1    5698    2898841190 2011-02-04 17:45:04 2898954050 2011-02-04 17:56:35
1    5699    2898954050 2011-02-04 17:56:35 2899066784 2011-02-04 18:03:01
1    5700    2899066784 2011-02-04 18:03:01 2899188633 2011-02-04 18:18:17
1    5701    2899188633 2011-02-04 18:18:17 2899251120 2011-02-04 18:30:03

RMAN> restore archivelog from logseq 5698 until logseq 5701;

Starting restore at 2011-02-07 10:40:00
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=369 devtype=DISK

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5698
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5699
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5700
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5701
channel ORA_DISK_1: reading from backup piece /export/backup/GOLD/GLP/GOLDPROD/rman/archivelog/arch_3098344053_GOLDPROD_1023_1_20110204
channel ORA_DISK_1: restored backup piece 1
piece handle=/export/backup/GOLD/GLP/GOLDPROD/rman/archivelog/arch_3098344053_GOLDPROD_1023_1_20110204 tag=ARCH_COMP
channel ORA_DISK_1: restore complete, elapsed time: 00:02:17
Finished restore at 2011-02-07 10:42:18

select SEQUENCE#, name , FIRST_TIME, ARCHIVED, DELETED from v$archived_log
where FIRST_TIME between to_date('04/02/2011 17:30', 'dd/mm/yyyy hh24:mi')
and  to_date('04/02/2011 18:30', 'dd/mm/yyyy hh24:mi')
<strong>and deleted = 'NO'</strong>
order by sequence#;

SEQUENCE# NAME                                                                                                 FIRST_TIM ARC DEL
---------- ---------------------------------------------------------------------------------------------------- --------- --- ---
5698 /gold/GLP/archive/flash_recovery_area/GOLDPROD/archivelog/2011_02_07/o1_mf_1_5698_6nzhhob5_.arc      04-FEB-11 YES NO
5699 /gold/GLP/archive/flash_recovery_area/GOLDPROD/archivelog/2011_02_07/o1_mf_1_5699_6nzhhnn1_.arc      04-FEB-11 YES NO
5700 /gold/GLP/archive/flash_recovery_area/GOLDPROD/archivelog/2011_02_07/o1_mf_1_5700_6nzhho5o_.arc      04-FEB-11 YES NO
5701 /gold/GLP/archive/flash_recovery_area/GOLDPROD/archivelog/2011_02_07/o1_mf_1_5701_6nzhm1j4_.arc      04-FEB-11 YES NO

Step 3) Load the archived logs into log miner

exec dbms_logmnr.add_logfile('/gold/GLP/archive/flash_recovery_area/GOLDPROD/archivelog/2011_02_07/o1_mf_1_5698_6nzhhob5_.arc', dbms_logmnr.NEW);

exec dbms_logmnr.add_logfile('/gold/GLP/archive/flash_recovery_area/GOLDPROD/archivelog/2011_02_07/o1_mf_1_5699_6nzhhnn1_.arc', dbms_logmnr.ADDFILE);

exec dbms_logmnr.add_logfile('/gold/GLP/archive/flash_recovery_area/GOLDPROD/archivelog/2011_02_07/o1_mf_1_5700_6nzhho5o_.arc', dbms_logmnr.ADDFILE);

exec dbms_logmnr.add_logfile('/gold/GLP/archive/flash_recovery_area/GOLDPROD/archivelog/2011_02_07/o1_mf_1_5701_6nzhm1j4_.arc', dbms_logmnr.ADDFILE);

Step 4) start the log miner

exec dbms_logmnr.start_logmnr(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Step 5) Examine log miner contents

Since the logminer contents are cached and accessible only from current session. Create a temp table holding relevant information to speed up our analysis.

create table my_logmnr_contents as

select * from v$logmnr_contents where sql_redo like '%CDEENTCDE%'

and timestamp between to_date('04/02/2011 17:58', 'dd/mm/yyyy hh24:mi')
and to_date('04/02/2011 17:59', 'dd/mm/yyyy hh24:mi');

select timestamp, sql_redo, sql_undo from my_logmnr_contents;

you can compare sql_redo and sql_undo statements to find out which value was updated.