9 Replies Latest reply: Feb 28, 2013 2:27 AM by 993611 RSS

    rman recovery

    993611
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          Thanks lot