12 Replies Latest reply: Jul 22, 2009 6:51 AM by EdStevens RSS

    log minor

    694352
      Hello

      Assume I issue:
      delete from table where date< sysdate
      commit;


      If I use log minor, Can I rolback this transaction;

      Does it stored in redolog like row by row or only statement like "delete from table where date< sysdate" ?

      Edited by: Ricardo Quaresma on 20/07/2009 21:08
        • 1. Re: log minor
          NavneetU
          hi,

          No you cant rollback the transaction once it is committed....

          Logminer is a utility which is just used to mine the logs and nothing else...

          Redo logs does not record the data nor statements....its just Change vectors for the statements that are stored... Change vector signifies the change occured in the single block...To know whats there in redo go through the link

          http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/onlineredo.htm#sthref857

          Regards,
          Navneet
          • 2. Re: log minor
            Santosh Kumar
            Perhaps you may need to recover the database(incomplete recovery). Logminer may help you in identifying the exact archive log, which contains your mistake,the query (I'm not saying the redo contains query, but oracle will generate that for you).
            Hot backup on NOARCHIVELOG mode?

            Once you have identified the archive, you may perform incomplete recovery leaving the problematic archived log.

            Regards,
            S.K.
            • 3. Re: log minor
              694352
              You dont get me....

              Assume accidentally I issue:

              delete from history where date <sysdate-10
              commit;


              If I investigate the redolog with logminor,and look the opposite of query..
              Can I see it ike:

              insert into history where date='..'
              insert into history where date='..'
              insert into history where date='..'
              insert into history where date='..'
              ...
              ...



              In this case I can reinsert the data.
              • 4. Re: log minor
                Santosh Kumar
                You dont get me....
                
                Assume accidentally I issue:
                
                delete from history where date <sysdate-10
                commit;
                
                If I investigate the redolog with logminor,and look the opposite of query..
                Can I see it ike:
                
                insert into history where date='..'
                insert into history where date='..'
                insert into history where date='..'
                insert into history where date='..'
                ...
                ...
                
                
                
                In this case I can reinsert the data.
                Interesting. But unfortunately the facility is unavailable, you have to pay for your mistake in terms of time and effort.

                Regards,
                S.K.
                • 5. Re: log minor
                  513949
                  A good solution is based on Oracle Flashback to identify what rows you incorrectly deleted and reinsert then.
                  • 6. Re: log minor
                    Uwehesse-Oracle
                    Hi,
                    in fact that is most likely possible. Try
                    execute dbms_logmnr.add_logfile('<name of current logfile>',dbms_logmnr.new);
                    execute dbms_logmnr.start_logmnr(options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
                    select sql_undo from v$logmnr_contents where seg_name='HISTORY';
                    For a more detailed explanation how to use Logminer see

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

                    Kind regards
                    Uwe

                    http://uhesse.wordpress.com
                    • 7. Re: log minor
                      Daljit
                      And if you don't have flashback enabled then point of time recovery is the only solution.


                      Thanks
                      Daljit Singh
                      • 8. Re: log minor
                        Uwehesse-Oracle
                        >
                        And if you don't have flashback enabled then point of time recovery is the only solution.
                        >

                        Regarding FLASHBACK TABLE TO TIMESTAMP - which is possible since 10g, then I am afraid to correct you, but that is not needing flashback logs.

                        You need flashback logs only in order o be able to do FLASHBACK DATABASE.

                        I have a couple of postings on my blog explaining the various flashback forms and architectures briefly:

                        http://uhesse.wordpress.com/tag/flashback/

                        Logminer - which was asked here - is by contrast available even since 8i. To come to the point:
                        Although the question was about Logminer, the problem is most likely solveable with a flashback table to timestamp command - if we are on 10g or higher. Should the therefore needed before images in the undo tablespace be overwritten, then still Logminer is relevant and can probably be used to solve the problem.

                        Kind regards
                        Uwe

                        http://uhesse.wordpress.com
                        • 9. Re: log minor
                          EdStevens
                          Santosh Kumar wrote:
                          You dont get me....
                          
                          Assume accidentally I issue:
                          
                          delete from history where date <sysdate-10
                          commit;
                          
                          If I investigate the redolog with logminor,and look the opposite of query..
                          Can I see it ike:
                          If you want to know what you can see with logminer, why not just fire it up and see ... ?
                          insert into history where date='..'
                          insert into history where date='..'
                          insert into history where date='..'
                          insert into history where date='..'
                          ...
                          ...



                          In this case I can reinsert the data.
                          Interesting. But unfortunately the facility is unavailable, you have to pay for your mistake in terms of time and effort.

                          Regards,
                          S.K.
                          • 10. Re: logminer
                            Santosh Kumar
                            Hi Edstevens,

                            I didn't get you.

                            Regards,
                            S.K.
                            • 11. Re: log minor
                              Uwehesse-Oracle
                              Hi Ricardo,

                              I have just put a demonstration on my Blog about how to achieve exactly what you have asked:

                              http://uhesse.wordpress.com/2009/07/22/logminer-not-as-modern-as-flashback-but-still-useful/

                              Kind regards
                              Uwe

                              http://uhesse.wordpress.com
                              • 12. Re: logminer
                                EdStevens
                                Santosh Kumar wrote:
                                Hi Edstevens,

                                I didn't get you.

                                Regards,
                                S.K.
                                I didn't either. I must have pulled the trigger too fast on the wrong message. ;-)