11 Replies Latest reply: Nov 21, 2012 9:14 AM by TexasApexDeveloper RSS

    Rollback in my workspace

    Bernardo
      I have a big big problem...

      One field (closure date) in one of my tables modified itself and I can't find the reason. 174 records modified the field with this value: 19-NOV-2012 15:54:39.

      I have this trigger to set the closure date value when the status is closed:
      create or replace TRIGGER "SD_INCIDENTS_TIME2"
      BEFORE
      update on "PROBLEMS"
      for each row
      begin
          IF (:new.STATUS='CLOSED') THEN
          :new.CDATE :=SYSTIMESTAMP AT TIME ZONE '-3:00';
          END IF;
      end;
      I have been doing tests to determine if this trigger did the problem but not....

      The other thing that I want to know is where can I find the logs to see who did those changes or what triggered that..

      Thanks, Bernardo

      Regards
        • 1. Re: Rollback in my workspace
          603257
          Assuming you mean flashback? If so then you can only flashback for as long as the undo information in available (depends on a whole multitude of things).
          As for checking who/what did it? You could try "select * from v$sql" however things only stay in there for a very short amount of time.
          I'd recommend taking the EBS approach and adding the following mandatory columns to your table(s):

          creation_date
          created_by
          last_update_date
          last_updated_by
          last_update_login (represents the users session id and is nullable)

          Then any time you do an update, you always set those. Sure, if there are two successive changes then (disregarding flashback) you might struggle. But if you're required to be able to trace to that level then you should be triggering all inserts/updates/deletes off into an audit table with columns similar to the above.
          • 2. Re: Rollback in my workspace
            Bernardo
            Thank you for your answer but that does not respond my question.. I mean, totally...

            We tried the flashback feature, but it does not work... The only thing that I care about is who did those changes...

            Is there any log on log of every table?

            Thanks in advance
            • 3. Re: Rollback in my workspace
              603257
              Other than v$sql so see the statement which updated it, not that I'm aware of. And you'll be way too far gone to get info from there now.
              My suggestion of adding audit columns was to provide this information in the future, not to resolve your current problem. I don't think you'll be able to do that now.
              • 4. Re: Rollback in my workspace
                Vite DBA
                Hi Bernardo,

                there are various auditing options and add ons in the Oracle database, all of which you can read about in the Oracle documentation and simply just by googling. The problem you face is that most of these are not turned on by default due to possible performance overheads, and in your case, there is no use shutting the gate after the horse has bolted. On top of the standard features, you can also specifically target auditing with the use of extra columns and tables and triggers to maintain them. Once again, this has to be thought about in advance.

                Regards
                Andre
                • 5. Re: Rollback in my workspace
                  joelkallman-Oracle
                  Hi Bernardo,

                  1) There are no logs on every table, unless you wrote these logs yourself.

                  2) You say "We tried the flashback feature, but it does not work.", but what does "does not work" mean? You can only go so far back via a flashback, and it's a function of the size of the undo tablespace. But you should have been able to go back at least a day or so. That's why the "does not work" statement is ambiguous.

                  3) With all respect, I find it highly unlikely that someone outside your workspace (on this shared instance) decided to go in and manually update these 174 rows for no good reason. I think it is more plausible that someone within your workspace or via some logic in your application issued this update.

                  I hope this helps.

                  Joel
                  • 6. Re: Rollback in my workspace
                    Bernardo
                    What I meant with "we tried flashback" is we could not go back what we want... we wanted go back 4 days.

                    About point 3... I'm thinking the same thing... that's why i would like to find a way to see who did it....

                    Thanks
                    • 7. Re: Rollback in my workspace
                      joelkallman-Oracle
                      Bernardo,

                      It seems odd that you wanted to go back 4 days, no? You reported that these rows were updated at 19-NOV-2012 15:54:39, and your first post on this thread was also on 19-NOV. If you wanted to get back to the state of these rows prior to the update, why not go back to 18-NOV (which probably would have been possible at the time, but probably not now, on 21-NOV).

                      Joel
                      • 8. Re: Rollback in my workspace
                        Bernardo
                        That was because those updates were with this value: 15-NOV-2012 15:32 that's why we tried that first... go back 4 days...
                        • 9. Re: Rollback in my workspace
                          joelkallman-Oracle
                          Hi Bernardo,

                          +>> "those updates were with this value: 15-NOV-2012 15:32"+

                          This seems to contradict your original statement of "174 records modified the field with this value: 19-NOV-2012 15:54:39."

                          But it seems like you have this problem understood. It wasn't a problem with flashback query, per se. It's just that you couldn't go back that far in time.

                          Joel
                          • 10. Re: Rollback in my workspace
                            Bernardo
                            Yeah exactly... and the only thing that I want is find a way to know who did it...... or howww
                            • 11. Re: Rollback in my workspace
                              TexasApexDeveloper
                              Might be a little too late to figure out the who or how.. Might I suggest you follow the suggestion and add the auditing columns to your table and a trigger to populate the audit columns or enable auditing in your instance to track FUTURE issues..

                              Thank you,

                              Tony Miller
                              Ruckersville, VA