1 2 Previous Next 15 Replies Latest reply on Jun 19, 2013 7:32 PM by jgarry

    Data loss

    user586345

      Hi All,

       

      Thanks in advance

       

      Most of the time we are getting expected results from the Database tables, but In some scenarios we feel  the insertion seems to be delayed in oracle database tables.

       

      For instance, we are querying from the database table in the interval of 10 minutes with a backlog of 1 minute, My last fetch is 10:10:15 am and next fetch is 10:20:15 am.

       

      In this interval sometimes one or two records getting missed. When i query the same interval few minutes later, I could able get the  set of records without any loss.

       

      Thanks

      Rajan.S

        • 1. Re: Data loss
          Fran

          you should audit your table to know what is happening:

           

           

          AUDIT INSERT,DELETE,UPDATE ON your_table;

           

          more details :

          http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_4007.htm

          • 2. Re: Data loss
            Gamblesk

            You did not give a lot of details but one possibility is that those 'missing' records have not been committed when you issue your first query. By the time you issue the second one the records have been committed.

            • 3. Re: Data loss
              sb92075

              Problem Exists Between Keyboard And Chair

              • 4. Re: Data loss
                Lubiez Jean-Valentin

                Hello,

                 

                 

                In this interval sometimes one or two records getting missed. When i query the same interval few minutes later, I could able get the  set of records without any loss.


                You may check how were the datas in your Table in the past by using Flashback Query, provided that the Undo Retention is large enough:

                 

                SELECT * FROM <your_table>

                AS OF TIMESTAMP

                TO_TIMESTAMP('<date_in_the_format_yyyy-mm-dd hh24:mi:ss>', 'YYYY-MM-DD HH24:MI:SS');

                 

                Please, find enclosed a link about the use of Flashback Query:

                 

                http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_flashback.htm#i1008579

                 

                 

                Hope this help,

                Best Regards,

                Jean-Valentin Lubiez

                • 5. Re: Data loss
                  Uwehesse-Oracle

                  We don't know how you insert the data nor how you select afterwards.

                  There may be some kind of application layer involved that is completely unclear to us.

                   

                  Technically, when you commit after an insert and you get commit complete (respectively the control is returned back to your application) that means your transaction is persistent.

                  It will not suffer data loss without media error - and even this can be implemented bulletproof.

                   

                  Kind regards

                  Uwe Hesse

                  • 6. Re: Data loss
                    user586345

                    Hi,

                     

                    Thanks.

                     

                    I am using the procedure to insert the data into the table.  Also am firing the commit statement after every insertion.

                     

                    Since, we are facing data loss in the above said scenario.

                     

                    Note : when we query the same interval after sometime, I can able to fetch the data without any loss.

                     

                     

                    Thanks

                    Rajan.S

                    • 7. Re: Data loss
                      Hemant K Chitale

                      What version are you running ? On what platform ?  Are you running OPS or RAC ?

                       

                      Hemant K Chitale

                      • 8. Re: Data loss
                        user586345

                        Hi,

                         

                        The version we are running is 11 g R2 under windows platform in RAC environment.

                         

                         

                        Thanks

                         

                        Rajan.S

                        • 9. Re: Data loss

                          If you are asked for a version, please specify all 4 digits in the form <xxx>.<xxx>.<xxx>.<xxx>

                          11 gR2 has at least three different patch releases.

                          Also 'windows platform' is inaccurate. You need to specify the exact version of Windows, including servicepack.

                           

                          --------

                          Sybrand Bakker

                          Senior Oracle DBA

                          1 person found this helpful
                          • 10. Re: Data loss
                            Hemant K Chitale

                            There used to be a parameter MAX_COMMIT_PROPAGATION_DELAY  upto 9i that might mean delayed viewing of committed data across instances in a cluster.  But that doesn't apply in 11.2

                             

                            Are you sure that the application isn't "delaying" the commits ?

                             

                            Hemant K Chitale

                            • 11. Re: Data loss
                              user586345

                              Hi,

                               

                              Please find below the details,

                               

                              Oracle version   : 11.2.0.3

                               

                              Platform   :  Windows 2008 R2 with SP1

                               

                              Environment : RAC

                               

                              Thanks

                               

                              Rajan.S

                              • 12. Re: Data loss
                                user586345

                                Hi Hemant,

                                 

                                We are not sure that it was due to delay in commits, because some time we could able to fetch the records without any loss and  sometimes we are facing this issue(data loss).

                                so we included the backlog but still we are facing the same issue.

                                 

                                 

                                Thanks

                                 

                                Rajan.S

                                • 13. Re: Data loss
                                  rp0428

                                   

                                  We are not sure that it was due to delay in commits, because some time we could able to fetch the records without any loss and  sometimes we are facing this issue(data loss).

                                  so we included the backlog but still we are facing the same issue.

                                   

                                   

                                  We can't help you if you don't post the query you are using to 'fetch the records' from the database.

                                   

                                  You can't just say 'I have a problem' you have to SHOW US what you are doing.

                                   

                                  If you are trying to use a query that uses a DATE column with the 'fetch time' to select the data and that column gets populated by SYSDATE or similar by an INSERT/UPDATE statement then your method is likely flawed and will often produce results like you describe.

                                   

                                  You can't use such a column for reliable extraction because the DATE value gets assigned BEFORE the data actually gets committed but your query can only access data that IS committed.

                                   

                                  1. T0 - no activity going on

                                  2. T1 - Insert query creates ROW using SYSDATE

                                  3. T2 - you query data for the period T0 to T2 - this query does NOT see the T1 data

                                  4. T3 - the query from step #2 issues a COMMIT

                                  5. T4 - you query data for the period T2 to T4 - this query also does NOT see the T1 data

                                   

                                  The query in step #5 won't see that data either because even though it is committed the DATE value used reflects SYSDATE as of T1 and that is before T2.

                                   

                                  If you are using a similar process your method is flawed.

                                   

                                  Since you still haven't posted what you are really doing we can't really help you.

                                  • 14. Re: Data loss
                                    EdStevens

                                    939675 wrote:

                                     

                                    Hi Hemant,

                                     

                                    We are not sure that it was due to delay in commits,

                                    Then you also not sure that it is NOT due to delay in commits ..

                                     

                                    And the very nature of the reported problem is pretty much proof positive that it is due to a delay.  You yourself report that even when you see this "loss" of data, after some time the data is there after all.  So there really is no loss of data, just an inability of a given transaction/query/session to see the data at a given point in time.

                                    because some time we could able to fetch the records without any loss and  sometimes we are facing this issue(data loss).

                                    so we included the backlog but still we are facing the same issue.

                                     

                                     

                                    Thanks

                                     

                                    Rajan.S

                                    1 2 Previous Next