This discussion is archived
4 Replies Latest reply: Jan 23, 2013 2:06 AM by 986210 RSS

Using SCN, getting extra rows

986210 Newbie
Currently Being Moderated
Hello,

I have a curious problem I have been battling with for a while, still haven't been able to figure it out. I hope someone will have an advice for me. :)

So, I am using Oracle Data Integrator 11g to load an Oracle DB. Source is Oracle DB as well, connection is made through a DBLINK.

I have an requirement to fetch data using the AS OF SCN flashback query.

My initial thought was to implement it in the Oracle LKM, when creating the view. But it seems you can't create a view using the AS OF SCN over the DBLINK.

So I implemented it in the Oracle IKM, when loading the I$ table. I've modified it to include the AS OF SCN query.

Now, it works fine, on each rerun I get new data in my target database.

But when I query the source database using the SCN which ODI used to load the target, I get less rows returned.

I found the records which shoudn't exist in the target, and ora_rowscn is greater than the SCN ODI used.

I checked other records from the target, which match with the records from query on the source with the SCN, and I can see that the ora_rowscn for those is smaller than the SCN ODI used.

I haven't been able to figure out why does ODI include those records, when it clearly shouldn't.

My thoughts:

1. Could it be somehow related to "dirty blocks"?

Is it possible that somehow ODI creates view containing "dirty blocks", after which those dirty blocks are cleansed on the source, and get the new SCN?

But when using the flashback query on the view, for the insert into I$, shouldn't it exclude dirty blocks?

2. When creating the view over dblink, does ODI copy the SCN as well, or is the view created without SCN? If that's the case, then the AS OF SCN query on insert into I$ step would make no sense. The view is created for every schema in the source, and then data inserted into

one table in target.

Any advice or a thought is appreciated.
Thanks!
  • 1. Re: Using SCN, getting extra rows
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION).

    Your problem isn't clear. Can you provide example queries? And get rid of those initials and acronyms you are using?
    >
    1. Could it be somehow related to "dirty blocks"?
    >
    No - Oracle doesn't provide any access to uncommitted data.
    >
    Is it possible that somehow ODI creates view containing "dirty blocks", after which those dirty blocks are cleansed on the source, and get the new SCN?
    But when using the flashback query on the view, for the insert into I$, shouldn't it exclude dirty blocks?
    >
    No - Oracle doesn't provide any access to uncommitted data. And views don't contain ANY data even if it has been committed. A view is just a stored query. When yo uselect from the view Oracle evaluates the query and only then accesses data.
    >
    2. When creating the view over dblink, does ODI copy the SCN as well, or is the view created without SCN? If that's the case, then the AS OF SCN query on insert into I$ step would make no sense. The view is created for every schema in the source, and then data inserted into
    >
    We don't know what you mean because you didn't show any queries. So where is the data coming from?

    You mention ORA_ROWSCN. If that means the default SCN for at the block level in a table that could be the source of your problem.

    If a block contains 100 records that haven't been touched since three years ago and you update ONE of those records. Then a query based on the current ORA_ROWSCN would return ALL of them since that is stored at the block level.

    Is that what you are talking about?

    See the SQL Language doc
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/pseudocolumns007.htm
    >
    ORA_ROWSCN Pseudocolumn For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking. Refer to CREATE TABLE ... NOROWDEPENDENCIES | ROWDEPENDENCIES for more information on row-level dependency tracking.

    You cannot use this pseudocolumn in a query to a view. However, you can use it to refer to the underlying table when creating a view. You can also use this pseudocolumn in the WHERE clause of an UPDATE or DELETE statement.

    ORA_ROWSCN is not supported for Flashback Query. Instead, use the version query pseudocolumns, which are provided explicitly for Flashback Query. Refer to the SELECT ... flashback_query_clause for information on Flashback Query and "Version Query Pseudocolumns" for additional information on those pseudocolumns.
  • 2. Re: Using SCN, getting extra rows
    986210 Newbie
    Currently Being Moderated
    Hello again.

    Thanks for the welcome, and sorry for the lack of info.

    Oracle Version: Target 11.2.0.1.0, Source 10.2.0.3.0


    I will try to explain a bit better.

    To simplify the matter, let's say I have an remote Oracle database source, with one schema that has all the tables I need (table1, table2 etc.)

    I am using Oracle Data Integrator 11g to pull, transform and load the data to my target Oracle database, which has the same tables as the schema from the source (table1, table2 etc.)

    Connection is made through a database link to the Oracle source.

    I want to pull data with the flashback query, to have a read consistent view.

    I' ve modified the Integration knowledge module, IKM SQL Control Append.

    I' ve added an option SCN with value AS OF SCN #SCN. #SCN is a variable ODI gets from select curent_scn from v$database from the source.

    I've then modified the command Insert flow into I$ table of the IKM as follows:
    iinsert into <%=snpRef.getTable("L","INT_NAME","A")%>
    (
         <%=snpRef.getColList("", "[COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%>
    )
    select <%=snpRef.getPop("DISTINCT_ROWS")%> 
         <%=snpRef.getColList("", "[EXPRESSION]", ",\n\t", "", "((INS and !TRG) and REW)")%>
    from      <%=snpRef.getFrom()%>
    *<%=odiRef.getOption("SCN")%>*
    where     <% if (snpRef.getPop("HAS_JRN").equals("0")) { %>
         (1=1)
         <%} else {%>
         JRN_FLAG <> 'D'
         <% } %>
    <%=snpRef.getJoin()%>
    <%=snpRef.getFilter()%>
    <%=snpRef.getJrnFilter()%>
    <%=snpRef.getGrpBy()%>
    <%=snpRef.getHaving()%>
    My thinking was that this should limit the dataset to the SCN provided.

    Everything executes without errors, but when I count the source with the SCN ODI used, I get less rows than ODI actually inserted into the target.

    Upon further investigation, I found the rows that are in the target, and are not in:
    select count(*) from [schema].[table]@[remotedb] as of scn #SCN;
    As I understand scn, ora_rowscn for the records ODI inserted should be smaller than the #SCN ODI used.

    I checked it for the records in question:
    SELECT scn_to_timestamp( ora_rowscn ) last_modified_date, ora_rowscn last_modified_scn
    from [schema].[table]@[remotedb];
    And ora_rowscn for all the rows that are in the target, and not in the source when I select with the SCN, is greater than the SCN. And the date is the date I ran the package, which is a bit fishy to me, and that's why I thought about dirty blocks.

    I hope I've managed to explain my problem a bit better.

    Further advice and ideas are appreciated.

    Thanks!
  • 3. Re: Using SCN, getting extra rows
    rp0428 Guru
    Currently Being Moderated
    >
    And ora_rowscn for all the rows that are in the target, and not in the source when I select with the SCN, is greater than the SCN. And the date is the date I ran the package, which is a bit fishy to me, and that's why I thought about dirty blocks.
    >
    You are mixing and confusing two different things.

    SCN values are specific to a database. An SCN in your source db has NOTHING to do with any SCN in your target db. They represent two different things.

    And ORA_ROWSCN has NOTHING to do with SCN values that are part of flashback functionality. ORA_ROWSCN is a pseudo-column that, by default, is available at the BLOCK level. As I said in my first reply if a block contains 100 rows that have existed unchanged for three years and you update ONE of those rows then the ORA_ROWSCN value for ALL rows in the same block as the one you changed will be the same.

    Any query using ORA_ROWSCN will get the same valuel for all 100 rows even though only one row actually changed.
  • 4. Re: Using SCN, getting extra rows
    986210 Newbie
    Currently Being Moderated
    >

    You are mixing and confusing two different things.

    SCN values are specific to a database. An SCN in your source db has NOTHING to do with any SCN in your target db. They represent two different things.

    >

    I wrote in my previous post:

    >

    I' ve added an option SCN with value AS OF SCN #SCN. #SCN is a variable ODI gets from select curent_scn from v$database from the source.

    >

    So, I fetch the data from source using SCN that I get also from that source, from v$database table, so I am not comparing SCN's from two databases, but trying to get a consistent set of data in my target db.

    And afer ODI finishes loading target, I am using that same SCN to query against the source, and it returns less rows than ODI inserted using the same SCN.

    So, the problem is not related to ora_rowscn, only to SCN. I used ora_rowscn only to test after loading the table.

    >

    And ORA_ROWSCN has NOTHING to do with SCN values that are part of flashback functionality. ORA_ROWSCN is a pseudo-column that, by default, is available at the BLOCK level. As I said in my first reply if a block contains 100 rows that have existed unchanged for three years and you update ONE of those rows then the ORA_ROWSCN value for ALL rows in the same block as the one you changed will be the same.

    >

    I know that ora_rowscn is different from SCN, and that it is maintained at the block level.


    But, I am wrong in thinking, that when the SCN is changed, no ORA_ROWSCN should be greater than that SCN at that time in that database?

    Isn't max(ora_rowscn) always <= SCN?

    Anyway, it only confirms that that specific record didn't exist on that SCN. So why does ODI inserts it?

    Thanks!

    Edited by: 983207 on Jan 23, 2013 2:05 AM

    Edited by: 983207 on Jan 23, 2013 2:06 AM

Legend

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