Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Location
    Toronto, Canada
    Posts
    76

    Unanswered: Creating a duplicate database by using data from a earlier state of the original DB

    Two days ago, a developer updated data from a table and we need to restore the original.

    Our backup policy:
    Every night, we do a whole database backup. Also, our retention window is 5 days, so we have archivelogs for the past couple of days.

    How could I create a new database, whose data is that of the original data 2 days ago?

    BTW, we are using Oracle Standard Edition so we don't have Flashback table/database feature.

    My plan is to create a duplicate database restored to 2 days ago, so that we can extract the neccessary data from the table of interest. After we have extracted the data, we'll drop the duplicate database.
    System: Oracle 10g on Fedora Core 1

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    If you have the archive logs, you can use logminer to pull the changes
    and UNDO them ..

    More work than Duck's suggestion with RMAN, but just another option.

  4. #4
    Join Date
    Sep 2002
    Location
    Toronto, Canada
    Posts
    76
    Quote Originally Posted by gbrabham
    If you have the archive logs, you can use logminer to pull the changes
    and UNDO them ..

    More work than Duck's suggestion with RMAN, but just another option.
    I've never used logminer before, but would the undo tablespace have to have data from 2 days ago. The developer ran an UPDATE statement and forgot to add a WHERE id = X clause.

    From what I understand, logminer would only show me the query that was executed.
    System: Oracle 10g on Fedora Core 1

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    My guess would be that the UNDO probably wont have the data, but it might... Logminer, you can pull the sql_undo from it and reverse the update
    the programmer did, then ... shoot the programmer

    Here's some info on Logminer ... you might want to check your doc set ... I've not worked on it with 10g yet ...

    ----- Build the LogMiner dictionary -----
    EXECUTE sys.DBMS_LOGMNR_D.BUILD( -
    DICTIONARY_FILENAME =>'1LogMiner_Dictionary.ora', -
    DICTIONARY_LOCATION => 'D:\oracle\admin\....\udump');

    ----- Specify logs you want to analyze ----

    EXECUTE sys.DBMS_LOGMNR.ADD_LOGFILE( -
    LOGFILENAME => 'D:\Oradata\....\Archives\CRE1T001S01098.ARC', -
    OPTIONS => sys.dbms_logmnr.NEW);

    -- Add other logs --

    EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
    LOGFILENAME => 'D:\Oradata\....\Archives\CRE1T001S01099.ARC', -
    OPTIONS => dbms_logmnr.ADDFILE);

    EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
    LOGFILENAME => 'C:\Oracle\oradata\....\redo03.log', -
    OPTIONS => dbms_logmnr.ADDFILE);

    -- Remove logs ---

    EXECUTE SYS.DBMS_LOGMNR.ADD_LOGFILE( -
    LOGFILENAME => 'D:\Oradata\....\Archives\CRE1T001S01099.ARC', -
    OPTIONS => SYS.dbms_logmnr.REMOVEFILE);

    EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
    LOGFILENAME => 'C:\Oracle\oradata\....\redo02.log', -
    OPTIONS => dbms_logmnr.REMOVEFILE);

    EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
    LOGFILENAME => 'C:\Oracle\oradata\....\redo03.log', -
    OPTIONS => dbms_logmnr.REMOVEFILE);

    --------- Start LogMiner ------------

    EXECUTE SYS.DBMS_LOGMNR.START_LOGMNR( -
    DICTFILENAME => 'D:\oracle\admin\CRE1\udump\1LogMiner_Dictionary.o ra', -
    STARTTIME => to_date('25-MAY-2004 10:00:00', 'DD-MON-YYYY HH24:MIS'), -
    ENDTIME => to_date('25-MAY-2004 11:15:00', 'DD-MON-YYYY HH24:MIS'));

    -- All info --
    SELECT operation, sql_redo FROM v$logmnr_contents;

    -- For a user --
    SELECT sql_redo, sql_undo FROM v$logmnr_contents
    WHERE USERNAME = 'OWNER' AND SEG_NAME = 'YOURTABLENAME';

    --- Occurances for a table ---
    SELECT to_char(timestamp,'HH24:MIS'),USERNAME,OPERATION,STATUS, sql_redo
    FROM v$logmnr_contents
    WHERE SEG_NAME = 'YOURTABLENAME';



    -- Table hits --
    SELECT seg_owner, seg_name, count(*) AS Hits FROM
    v$logmnr_contents WHERE seg_name NOT LIKE '%$' GROUP BY
    seg_owner, seg_name;


    hth
    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •