10 Replies Latest reply: Jun 21, 2011 10:09 AM by DD RSS

    basic flashback config question

    DD
      I have an Oracle database (10.2.0.4.0 on Linux) installation that I have inherited. A user deleted some data and I attempted to recover it. There were backups and archive log files in the FRA, but when I queried about flashback status I got:
      select flashback_on from v$database;
      
      
      FLASHBACK_ON
      ------------
      NO          
      
      1 row selected (0.06 seconds)
      With that config set to "no" that means I can not use flashback to recover lost data. It must be set to yes before the failure occurs in order to use flashback to recover afterwards. Do I have that right?
        • 1. Re: basic flashback config question
          sb92075
          user13716631 wrote:
          I have an Oracle database (10.2.0.4.0 on Linux) installation that I have inherited. A user deleted some data and I attempted to recover it. There were backups and archive log files in the FRA, but when I queried about flashback status I got:
          select flashback_on from v$database;
          
          
          FLASHBACK_ON
          ------------
          NO          
          
          1 row selected (0.06 seconds)
          With that config set to "no" that means I can not use flashback to recover lost data. It must be set to yes before the failure occurs in order to use flashback to recover afterwards. Do I have that right?
          You have it correct.
          • 2. Re: basic flashback config question
            Aman....
            I am afraid that the answer of Sb that you have it right is actually incorrect. The given output is for the flashback mode of the database which is set to be off by default. This is NOT for recovering a single row of the table which is deleted but for pushing the entire db back in the past. To recover a deleted row from a table, you need the Undo data which is always there but the limitation is that you need to use the flashback commands within the time period of the Undo retention of your db. So if you have it, you can very well recover the deleted data.

            HTH
            Aman....
            • 3. Re: basic flashback config question
              DD
              Thanks for the replies sb and aman.

              It seems like a good idea to leave it on if I have the disk space. The other consideration would be i/o, maybe.
              • 4. Re: basic flashback config question
                CKPT
                I have an Oracle database (10.2.0.4.0 on Linux) installation that I have inherited. A user deleted some data and I attempted to recover it. There were backups and archive log files in the FRA, but when I queried about flashback status I got:
                With that config set to "no" that means I can not use flashback to recover lost data. It must be set to yes before the failure occurs in order to use flashback to recover afterwards. Do I have that right?
                Are you sure that you have committed the deleted data, if not you can rollback.

                If your retention is not enough big, however you disabled FLASHBACK, you cant get it.

                But there is a big procedure, if it is really critical, if your database is running in archivelog mode, Restore database in any test server, recover database before deleting rows, Take an export of the table and import into this database.

                Edited by: CKPT on Jun 20, 2011 7:15 PM
                • 5. Re: basic flashback config question
                  Pavan Kumar
                  hi,

                  Oracle documentation does provide the things which are you looking for..
                  http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm

                  I hope that should clear your doubts..

                  HTH

                  - Pavan Kumar N
                  • 6. Re: basic flashback config question
                    DD
                    @ ckpt, luckily it was not critical and they were able to recreate the data.

                    @ Pavan, that documentation is for 11g (I am working with 10g) and it does not mention the flashback_on parameter. However, I do have to look at the 10g documentation for proper setup of flashback.

                    Thanks

                    Edited by: user13716631 on Jun 20, 2011 4:52 PM
                    • 7. Re: basic flashback config question
                      Aman....
                      >
                      @ Pavan, that documentation is for 11g (I am working with 10g) and it does not mention the flashback_on parameter. However, I do have to look at the 10g documentation for proper setup of flashback.
                      I guess you are thinking that FLASHBACK_ON is a parameter but its not . Its actually a column only. As I mentioned already, the flashback option is already enabled for you for the tables and dml's using the Undo data stored in the Undo tablespace. Only if you want to have the Flashback Mode on for the whole database , you would need to enable it explicitly using the command alter database flashback on at the mount stage. Hope it clears the confusion.

                      HTH
                      Aman....
                      • 8. Re: basic flashback config question
                        DD
                        Yes, Aman, now I get it. That is a good explanation. Now I have to go see what is using undo tablespace!

                        Thanks,
                        David
                        • 9. Re: basic flashback config question
                          Aman....
                          Great! Glad its clear for you now. Keep the thread updated with your findings :=) .

                          Aman....
                          • 10. Re: basic flashback config question
                            DD
                            Hi Aman,

                            So I know a undo tablespace exists and I know that management is on auto (see below). But how do I know which tables are using this undo tablespace? Are all tables on this server using undo or only selected tables and/or users?

                            as sysdba:
                            select tablespace_name from dba_undo_extents group by tablespace_name;
                            
                            TABLESPACE_NAME
                            ------------------------------
                            UNDOTBS1
                            show parameter undo
                            
                            NAME                         TYPE      VALUE
                            ------------------------------------ ----------- ------------------------------
                            undo_management                string      AUTO
                            undo_retention                    integer      900
                            undo_tablespace                string      UNDOTBS1
                            thanks,
                            David