This discussion is archived
9 Replies Latest reply: Feb 28, 2013 12:27 AM by 993611 RSS

rman recovery

993611 Newbie
Currently Being Moderated
is it possible ?

1. i have a table name A having 10 rwws
2. inserted more 10 rows = 20 rows , and also update table (changes)
3. alter system switch logfile;
4. how can i revertback my table to initial position @1st step

Thanks
10g
win7

Edited by: O0O0O0O0O on Feb 28, 2013 12:17 PM
  • 1. Re: rman recovery
    Karan Kukreja Journeyer
    Currently Being Moderated
    HI ,

    Did you perform a commit ? If yes , then its not possible to get the old state of the table back ( with 10 rows).

    An approach that you should follow if the inserts are so uncertain is that take an export of the table on which DML is expected , and incase of a failed DML ( when committed but need the old data back) either perform a delete or simply drop the table and import it .

    Ofcourse you wont be doing these experiments on the Prod setup.

    Regards
    Kk
  • 2. Re: rman recovery
    993611 Newbie
    Currently Being Moderated
    Kk wrote:
    HI ,

    Did you perform a commit ? If yes , then its not possible to get the old state of the table back ( with 10 rows).
    no i didnt "commit "
    assume that period between step 1 and step 2 are 4 months . is it possible ?
    >
    An approach that you should follow if the inserts are so uncertain is that take an export of the table on which DML is expected , and incase of a failed DML ( when committed but need the old data back) either perform a delete or simply drop the table and import it .
    drop and import? what to import?
    Ofcourse you wont be doing these experiments on the Prod setup.
    no im testing


    Thanks
  • 3. Re: rman recovery
    Karan Kukreja Journeyer
    Currently Being Moderated
    Hi ,

    If you did not commit , a roolback or abnormal termination of the session would result in 10 records only.

    Incase you had commit or did a clean exit from the sqlplus session , the records are committed.

    While you test :

    assume your table name is ABC


    run this :
    exp system/<password> owner=yourschema tables=tablename file=check.dmp log=cehck.log
    this would take an export of the table. Post of your analysis you can execute the following :
    drop table ABC
    exit;
    imp  system/password owner=yourschema tables=tablename file=check.dmp log=check.log 
    Regards
    KK
  • 4. Re: rman recovery
    993611 Newbie
    Currently Being Moderated
    yea , that would be successful revert back to initial position of table.

    but i was considering scenario that what if som1 have messed up with table and dba wants to table back to its initial position , (bcos we have its archivelogs saved)

    Thanks
  • 5. Re: rman recovery
    Karan Kukreja Journeyer
    Currently Being Moderated
    Hi ,

    Its not possible.

    Archives are change vectors of the changes done at the database level necessary for recovery / BCP sync.

    Post commit its impossible to take the table back to its previous state [rollback] with the use of archivelogs. You may use export/import or oracle flashback method to get a state of table as before.

    Not knowing much of flashback myself , you can refer this for your reference :
    http://www.oracle-base.com/articles/10g/flashback-10g.php
    Regards
    KK
  • 6. Re: rman recovery
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    You can use LogMiner on the ArchiveLogs to extract the Undo statement as an SQL command and then execute it. We assume that the rows in the table haven't again been updated since that update 4 months ago.

    Other than that, you'd have to restore the 4 month ago backup to another server and then extract the table out of it.


    Hemant K Chitale
  • 7. Re: rman recovery
    993611 Newbie
    Currently Being Moderated
    Hemant K Chitale wrote:
    You can use LogMiner on the ArchiveLogs to extract the Undo statement as an SQL command and then execute it. We assume that the rows in the table haven't again been updated since that update 4 months ago.
    hey thanks , but what is Logminer and how to extract undo statement

    Other than that, you'd have to restore the 4 month ago backup to another server and then extract the table out of it.
    Thanks
  • 8. Re: rman recovery
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    See the 10g documentation on LogMiner :

    http://docs.oracle.com/cd/B19306_01/server.102/b14215/logminer.htm#sthref1875


    (if you google for it, you'd find examples of usage, I don't have one , but I suggest Oracle Support article
    "Using LogMiner, How to determine the cause of lots of redo generation." Note ID 300395.1 )


    Hemant K Chitale
  • 9. Re: rman recovery
    993611 Newbie
    Currently Being Moderated
    Thanks lot

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points