Forum Stats

  • 3,741,218 Users
  • 2,248,393 Discussions
  • 7,861,681 Comments

Discussions

Accident Proof Delete Strategy

kaericn
kaericn Member Posts: 305 Blue Ribbon
edited Mar 8, 2019 5:24PM in SQL & PL/SQL

Dear community, 

Thanks for all the help.

What are the options to save the records (instrumentation) that will be deleted if the app has bug ?

We are thinking two options

  1. call a procedure that save all the parent/child record that will be affected (a config can be turn on and have no downtime )
  2. using trigger before delete to save those record( can turn off the procedure in the trigger to on the fly)

The story:

We are doing a continuous ETL between two different system.

Legacy_system <=>New_system

The app in both side can delete records.

And now we need to implement delete.

We are using a Gobal temporary table to holding the source extract

e.g

insert into tmp_tb<select * from source>merge into <target> on<key>using< select * from tmp_tb>when matched updatewhen not matchedinsert<routine_to_save_record>--find deltaselect * from targetminusselect * from sourcedelete delta
Tagged:
John ThortonMustafa_KALAYCIStefan Jagerkaericn

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Feb 28, 2019 6:56PM
    kaericn wrote:Dear community, Thanks for all the help.What are the options to save the records (instrumentation) that will be deleted if the app has bug ?We are thinking two optionscall a procedure that save all the parent/child record that will be affected (a config can be turn on and have no downtime )using trigger before delete to save those record( can turn off the procedure in the trigger to on the fly)insert into tmp_tb <select * from source>  merge into <target> on<key> using< select * from tmp_tb> when matched  update when not matched insert  <routine_to_save_record> --find delta select * from target minus select * from source  delete delta   

    If you plan on duplicating the rows before they are deleted, then why bother with the DELETE to being with?

    How often has application had DELETE bug?

    Why not just periodically export table?

  • kaericn
    kaericn Member Posts: 305 Blue Ribbon
    edited Feb 28, 2019 7:12PM

    This is a continuous ETL for the co-existence of two system.

    export/import is not practical.

    We do not when will it has a bug.....if we do, then we would have fixed it....

    Heres some info

    Our  TPS range from 0-60 rec per sec.

    and our daily load is around 60000 rec.

    Let say 5 percent max of our record will generated delete with one table.

    And that one table has 17 child tables.

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,342 Bronze Crown
    edited Feb 28, 2019 11:21PM

    first, if you want to save your rows accidental delete, you can use triggers as you said. You can use Flashback Data Archive to protect your log data which would be faster than trigger and if you are using Oracle 12c then you can save context information with data archive too (like USERENV variables).

    but I must admit I am not sure how this is related with your etl. Also in your etl structure, why don't you use materialized views? using a minus between 2 table is not an optimized solution. it will suffer when tables get much more bigger.

  • Racer I.
    Racer I. Member Posts: 113
    edited Mar 1, 2019 4:41AM

    if you can manipulate the structures on the second db you can add a YN_DELETED column and update it instead of really deleting rows. Then have your app handle that field or hide it in views.

    Or try in-database-archiving https://docs.oracle.com/database/121/VLDBG/GUID-5A76B6CE-C96D-49EE-9A89-0A2CB993A933.htm#VLDBG14154

    Maybe the latter can even be hidden behind an instead-of trigger for the DELETE (still requires a view)?

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410
    edited Mar 1, 2019 4:43AM

    Depending on how much "deleted" data is going to hang around that may have a negative impact on performance.

    I'm not saying it's a bad idea necessarily (my app has a delete flag on one of the bigger tables), just that the potential con needs to be born in mind.

  • Stefan Jager
    Stefan Jager Member Posts: 1,749 Silver Trophy
    edited Mar 1, 2019 4:53AM

    How will you determine if a delete on one side should be rolled back? If you know that, why not use the rows on the other side that haven't been deleted to restore the ones on the first side?

    That said: it's quite ambitious what you are trying to do. You'll need to keep track of all manipulations on both sides, then compare those manipulations in order to synchronize them. For example: side A inserts two rows, then updates three more. Side B also inserts two rows, one of which is identical to the insert on Side A. Side B then continues to update 5 rows, of which 2 updates are identical to Side A's update, and 3 updates would overwrite Side A's updates with different values.

    How are you going to keep track of all that, and make sure you still have valid data on both sides at the end of the day?

    (I've been in that situation, that's why I ask - we never managed to get it working, which I warned the responsible folks about, but as susual they would not listen....)

  • EdStevens
    EdStevens Member Posts: 28,239 Gold Crown
    edited Mar 1, 2019 8:59AM

    So you want to write some code to "save" rows that are wrongfully deleted due to a bug in other code?  What if your 'save the row in case of a bug' code has a bug itself?

    John Thortonkaericn
  • Mike Kutz
    Mike Kutz Member Posts: 5,588 Silver Crown
    edited Mar 1, 2019 9:52AM

    If you are willing to keep track of Inserts and Updates also.

    ... And add a time range to keep that information (eg 3 days)

    Your answer will be Flashback Data Archive.

    http://www.oracle.com/technetwork/issue-archive/2016/16-mar/o26performance-2925662.html

    https://youtu.be/2RXUUrxcKrw?list=PLlo3zrPbMP9_HDjsq9lcdi8RyiIvqfmBy

    MK

    Mustafa_KALAYCI
  • kaericn
    kaericn Member Posts: 305 Blue Ribbon
    edited Mar 1, 2019 8:14PM

    Flashback Data Archive

    and

    materialized view

    12c sounds good

    The challenge is to have the DBA team have it set up and it might take a while and the DBA team might even push back.

    The delete volume is very  small compared to the over all-volumne. And its only use when we need to do the debug

    Actually this is a three-way system. There is another system comes into the picture and that system can insert, update or  even delete.

    And honsetly, even the functional team is not 100% sure how this three way system interact so we try to make something quick and easy

    and not too involve the DBA and all that...

  • kaericn
    kaericn Member Posts: 305 Blue Ribbon
    edited Mar 1, 2019 8:15PM

    This is a very valid point.

    It is like any audit system who audit the audit-system its a chicken-egg question..

  • kaericn
    kaericn Member Posts: 305 Blue Ribbon
    edited Mar 1, 2019 9:17PM

    Honestly, if we (the db dev team) has the say we would not do a hard delete but the app and UI does it for whatever reason...

    So now we just have to deal with it..bra..bra..

  • Raj Nath
    Raj Nath Member Posts: 394
    edited Mar 4, 2019 12:46AM

    As per you, application has bug which is deleting records and you are not able to fix it. Can you please explain what all have you tried to debug this delete issue. I guess if you can fix the bug then you will have more clarity on the new changes.

  • EdStevens
    EdStevens Member Posts: 28,239 Gold Crown
    edited Mar 4, 2019 10:19AM
    kaericn wrote:Honestly, if we (the db dev team) has the say we would not do a hard delete but the app and UI does it for whatever reason...So now we just have to deal with it..bra..bra..

    So apparently you - the db dev team - have no control over how the app works?  And the app clearly has a serious bug?  So why aren't you working with the people (internal? third party?) who do have control over the code and thus have responsibility to correct the bug?  THis is sounding more and more like a case of nobody is willing to talk to or work with anyone.  That's no way to run a railroad .. or support an application.

  • kaericn
    kaericn Member Posts: 305 Blue Ribbon
    edited Mar 4, 2019 7:29PM

    Firstly, we dont know if there is a bug or not or will have a bug or not in the future..?

    This is only a preventive measure that we "think" is the best option which requires the least effort and can pay off the most...

    The impression we(DB dev team)  got is the systems are so complicated nobody have the whole picture.

    Empirical data.

    There is a QA tests we scheduled to complete against the whole system.

    We schedule to complete 100% of the test as of now but we have completed less than 30%....

  • Raj Nath
    Raj Nath Member Posts: 394
    edited Mar 5, 2019 12:52AM

    What i understood from your comments, is that, you want to backup data in case of accidental delete.

    In that case there are few questions that needs to be answered.

         1. Do you want to block delete completely?

         2. Is there any case where you allow apps to delete data and that is legitimate?

    As others have pointed out, it is better to understand system and fix the bugs, rather than trying to implement something which may or may not solve your problem.

  • EdStevens
    EdStevens Member Posts: 28,239 Gold Crown
    edited Mar 5, 2019 8:23AM
    kaericn wrote:Firstly, we dont know if there is a bug or not or will have a bug or not in the future..?....

    So you should be developing procedures and/or triggers to mitigate against all possible bugs that you think could potentially exist now or in the future.

    Therein lies madness.

    Stefan Jagerkaericn
  • kaericn
    kaericn Member Posts: 305 Blue Ribbon
    edited Mar 8, 2019 4:22PM

    1.Yes, we have a config table that can block delete on our ETL

    2.No the ETL cannot control what the app does, the apps DO delete and it can be up to 20% of our data flow.

    Again, this system is so complicated we just put any preventive measure  that can help us as long as its NOT too much effort...

    The ETL is just one  piece of the big puzzle.

    e.g.

    During our functional test of how the three system interacts in one of our use case, we find some data in-consistency.

    And from the ETL prespective, there is no bug.

    However the DB team has to help  on debugging this data inconsistency and prove it..

    And now we are experimenting on if we can  dump out all the states of the tables concerned on demand by using this tool:

    https://github.com/OraOpenSource/Logger/blob/master/docs/Best%20Practices.md

    Yet, we have to get the security team to review it if its OK to have "create any context"

    And recovering delete is not easy in our current env. so we just put something quick, easy and  the DB dev team can control to cover our ground...

  • Mike Kutz
    Mike Kutz Member Posts: 5,588 Silver Crown
    edited Mar 8, 2019 5:24PM

    A few links you should review:

    https://en.wikipedia.org/wiki/Unit_testing

    https://en.wikipedia.org/wiki/Acceptance_testing

    https://en.wikipedia.org/wiki/Quality_assurance

    https://en.wikipedia.org/wiki/Repository_(version_control)

    https://en.wikipedia.org/wiki/Microsoft_Word

    Putting a "Hello Kitty" band-aid on a severed arm isn't going to help stop the bleeding.

    You need to hunt down and fix the root cause of the problem.

    My $0.02

    MK

Sign In or Register to comment.