8 Replies Latest reply on Apr 25, 2018 2:56 PM by Hesh

    rowscn values for auditing

    Hesh

      Hi,

       

      Oracle 12C Enterprise Edition..

       

      some puzzling situation here..

      I have a dependency job which runs on few source tables from one schema. There is an explicit log table which is used to know the source has done with its processing and I can start my job.

      Once the source schema updates the log table there should not be any activity on any of the source tables(means DML activity).

       

      But what happened today, due to some issues I have to rerun one of the job from our end as there was some duplicate issue from source table.

      What I found is the count is different from source from now comparing to initial run. The team manages source schema confirms there is no activity happened in between.

       

      I have tried rowscn function on few source tables checking few times with an hour gap or something it returns continuously new numbers ...

       

      SQL> select max(ora_rowscn), scn_to_timestamp(max(ora_rowscn)) FROM RXYZ.TAB_PRDTLS;

       

      MAX(ORA_ROWSCN)

      ---------------

      SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN))

      ---------------------------------------------------------------------------

      8002090235

      19-APR-18 17.44.45.000000000

       

      Regards

      Hesh

        • 1. Re: rowscn values for auditing
          Martin Preiss

          so the problem is that you have a table without (known) DML activity and the ORA_ROWSCN is still increased? If you have ROWDEPENENCIES enabled, you could check, which rows show the increased values.

          • 2. Re: rowscn values for auditing
            Hesh

            Hi Martin,

             

            yes that is the issue here, ORA_ROWSCN is keep changing and no one is very sure about any activity is going on.

            And yes when I convert the SCN into timestamp, few records are with latest and few are with old intended timestamp!

             

            Regards

            Hesh

            • 3. Re: rowscn values for auditing
              Martin Preiss

              this could be a job for logMiner. Without a change a SCN (= system change number) should not change: https://docs.oracle.com/database/121/SQLRF/pseudocolumns007.htm#SQLRF50953

              • 4. Re: rowscn values for auditing

                yes that is the issue here, ORA_ROWSCN is keep changing and no one is very sure about any activity is going on.

                And yes when I convert the SCN into timestamp, few records are with latest and few are with old intended timestamp!

                That is normal, typical behavior and is discussed in the Oracle docs

                 

                A SIMPLE search for 'oracle 12c ora_rowscn' returns the doc as the first link.

                https://docs.oracle.com/database/121/SQLRF/pseudocolumns007.htm#SQLRF50953

                 

                Whether at the block level or at the row level, the ORA_ROWSCN should not be considered to be an exact SCN. For example, if a transaction changed row R in a block and committed at SCN 10, it is not always true that the ORA_ROWSCN for the row would return 10. While a value less than 10 would never be returned, any value greater than or equal to 10 could be returned. That is, the ORA_ROWSCN of a row is not always guaranteed to be the exact commit SCN of the transaction that last modified that row. However, with fine-grained ORA_ROWSCN, if two transactions T1 and T2 modified the same row R, one after another, and committed, a query on the ORA_ROWSCN of row R after the commit of T1 will return a value lower than the value returned after the commit of T2. If a block is queried twice, then it is possible for the value of ORA_ROWSCN to change between the queries even though rows have not been updated in the time between the queries. The only guarantee is that the value of ORA_ROWSCN in both queries is greater than the commit SCN of the transaction that last modified that row.

                See that next to last sentence? Where it says 'it is possible for the value ...to change...even though rows have not been updated...'?

                 

                Delayed block cleanout is one cause of that. See the second reply in this stack overflow thread

                https://stackoverflow.com/questions/30512372/why-does-ora-rowscn-change-without-updating-a-table

                However, for those blocks with delayed clean out we only update the corresponding ORA_ROWSCN the next time we touch the block(DML or select). It can be several hours later. It is possible that we are doing bunch of staff in other tables (thus the SCNs get incremented) and we are not doing any transaction in this table T. However when we queried the ORA_ROWSCN for table T we can still get a recent figure for SCN, since the block has just been cleaned out (but the transaction has been committed several hours ago). This ORA_ROWSCN is the upper bound of the commit time for the last transaction.

                That last sentence is the 'service level agreement' for ORA_ROWSCN. That it is is an 'upper bound' - it is NOT necessarily the time of last commit.

                • 5. Re: rowscn values for auditing
                  Hesh

                  Thanks rp0428,

                   

                  Sounds interesting, in that sense ROWSCN is not that reliable to get last DML.

                  Any suggestions on how to get intended info from the DB?

                   

                  Regards

                  Hesh

                  • 6. Re: rowscn values for auditing

                    Sounds interesting, in that sense ROWSCN is not that reliable to get last DML.

                    Correct

                     

                    Any suggestions on how to get intended info from the DB?

                    Well - you haven't posted ANYTHING that even remotely says what 'intended info' you are even talking about.

                     

                    I suggest you create a NEW thread that provides ALL of the requirements for that 'intended info'.

                     

                    A simple materialized view log can capture the basic info about what DML changes have been made to a table. You can often use that to identify the ROWS (primary keys, rowids) of interest.

                     

                    See my replies and example code in this old thread

                    Using Materialized view logs to track data changes

                     

                    But without knowing what it is you are trying to do no other help is really possible.

                    1 person found this helpful
                    • 7. Re: rowscn values for auditing
                      John Thorton

                      Hesh wrote:

                       

                      Hi,

                       

                      Oracle 12C Enterprise Edition..

                       

                      some puzzling situation here..

                      I have a dependency job which runs on few source tables from one schema. There is an explicit log table which is used to know the source has done with its processing and I can start my job.

                      Once the source schema updates the log table there should not be any activity on any of the source tables(means DML activity).

                       

                      DML generates both REDO & UNDO

                       

                      various ways to determine if REDO and/or UNDO is being generated.

                       

                      DBMS_LOGMNR can report details regarding DML against specific table(s)

                      1 person found this helpful
                      • 8. Re: rowscn values for auditing
                        Hesh

                        Thanks rp048, John,

                         

                        MV logs and Log miner looks promising options, I will try these..

                        The important thing what I have learnt here is, knowledge can become obsolete quickly and we should not over trust what we know..

                        OTN fours is a wonderful place.. I learn always something interesting .. great community! great people!

                         

                        Regards

                        Hesh