9 Replies Latest reply: Jan 30, 2013 6:25 AM by 901586 RSS

    Scrubbing Database

    901586
      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
          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
            This is a near operational enviroment, and the database is 11g r3. Thanks
            • 3. Re: Scrubbing Database
              jgarry
              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
                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
                  >
                  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
                    Tnist wrote:
                    the database is 11g r3
                    Your database is 11g Release 3 ?
                    • 7. Re: Scrubbing Database
                      Osama_Mustafa
                      I guess he meant 11.2.0.3 :)
                      • 8. Re: Scrubbing Database
                        Osama_Mustafa
                        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
                          .