This discussion is archived
9 Replies Latest reply: Jan 30, 2013 6:25 AM by 901586 RSS

Scrubbing Database

901586 Newbie
Currently Being Moderated
Hello, I am a Jr. DBA and have just been asked to scrub the database of data that was inserted between a given time frame. I need to remove data from every table in the schema that was inserted during a particular time frame. How do I go about this?

Thanks in advance.
  • 1. Re: Scrubbing Database
    vlethakula Expert
    Currently Being Moderated
    Is it production?
    Version of database?
    You want to remove the data from specific dates for all tables ? Or do you have criteria for each table separtae?

    For first case you can use Flashback Database.
  • 2. Re: Scrubbing Database
    901586 Newbie
    Currently Being Moderated
    This is a near operational enviroment, and the database is 11g r3. Thanks
  • 3. Re: Scrubbing Database
    jgarry Guru
    Currently Being Moderated
    Since normally every table in the schema doesn't have some date you can use, one way is to do a point in time recovery on another database, then destructively import into the schema. This assumes you don't have references to or from other schemata.

    If the tables in question allow you to determine unequivocally all the data that needs to be deleted, then you can do it that way. For example, if you have an order system and can relate the creation time of orders to all related tables and delete as appropriate.

    It depends.
  • 4. Re: Scrubbing Database
    Mark D Powell Guru
    Currently Being Moderated
    How you go about finding all the data inserted between a specific period of time depends on your environment. First off if you application is designed to record the time of insertion/update on the row level then you can use this information.

    If auditing is configured you can check the audit rules and potentially use the audit information to find and remove the rows of interest.

    If you have a lot of undo configured and the insertions were very recent you mgiht be able to use flashback query to find the rows of interest.

    Most likely though you would have to resort to using log miner to locate the data of interest; however, use of this feature would require that the database in question be in archive log mode so that you have archived redo log information to mine.

    You mention a specific schema being effected. Any chance that the schema in question was exported rigtht before the changes were made and what you really need to do is restore the objects from the export?

    Most of the time when you post you should include the full Oracle version, edition, RAC vs non-RAC, etc ... and configuration information realted to the issue at hand.

    HTH -- Mark D Powell --
  • 5. Re: Scrubbing Database
    rp0428 Guru
    Currently Being Moderated
    >
    Hello, I am a Jr. DBA and have just been asked to scrub the database of data that was inserted between a given time frame. I need to remove data from every table in the schema that was inserted during a particular time frame. How do I go about this?
    >
    As others have asked you need to clarify the requirement.

    Does INSERTED also mean UPDATED? Does data that was DELETED during that period need to be restored? Some apps effect updates by using a combination of DELETE/INSERT. An app could delete an address record that has been there for years and insert a new one. If you delete the new one there may not be any address record left since the one that was deleted won't get restored.

    Using ONLY data that is currently in the tables it may be impossible to do proper cleansing unless you don't need to deal with the issues just mentioned and you have a date column each each table, or each parent table of a parent-child hierarchy.
  • 6. Re: Scrubbing Database
    AdamMartin Pro
    Currently Being Moderated
    Tnist wrote:
    the database is 11g r3
    Your database is 11g Release 3 ?
  • 7. Re: Scrubbing Database
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    I guess he meant 11.2.0.3 :)
  • 8. Re: Scrubbing Database
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Tnist wrote:
    Hello, I am a Jr. DBA and have just been asked to scrub the database of data that was inserted between a given time frame. I need to remove data from every table in the schema that was inserted during a particular time frame. How do I go about this?

    Thanks in advance.
    before start working On Data Scrubbing/Data Cleansing make sure you take backup of your database, Just adding to what other guru provide you you can check this link which provide you with solutions depending on your requirement:
    http://www.bardess.com/pages/solutions/data-management-data-cleansing-improvement.html
  • 9. Re: Scrubbing Database
    901586 Newbie
    Currently Being Moderated
    .

Legend

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