7 Replies Latest reply: Oct 4, 2012 8:52 AM by AJ RSS

    Guaranteed restore point and storage size

    AJ
      RHEL5 (RDBMS 11.2.0.3.0 - nonrac)

      Hi,

      I've created a couple of guaranteed restore points in my database and upon quering the information about them I get the following output:
      select name, time, storage_size/1024/1024 "MB", guarantee_flashback_database from v$restore_point order by time
      
      NAME                           TIME                                        MB GUA
      ------------------------------ ----------------------------------- ---------- ---
      PRE_TAS4279_20120927           27-SEP-12 07.00.01.000000000 PM           3072 YES
      PRE_TAS4279_20120928           28-SEP-12 07.00.02.000000000 PM          10752 YES
      PRE_TAS4279_20121001           01-OCT-12 07.00.01.000000000 PM          19968 YES
      RP_PRE_BUKVILKAR               02-OCT-12 04.00.09.000000000 PM              0 YES
      PRE_TAS4279_20121002           02-OCT-12 07.00.01.000000000 PM          11264 YES
      PRE_TAS4279_20121003           03-OCT-12 07.00.01.000000000 PM           4096 YES
      As you can see one of the restore points have 0 bytes in storage size - even though the restore point is guaranteed.
      Isn't this a bit strange or am I missing something ?? Is it useless?

      Please shed some expert light.


      Thanks
        • 1. Re: Guaranteed restore point and storage size
          dataseven
          hi,

          For normal restore points, STORAGE_SIZE is zero. For guaranteed restore points, STORAGE_SIZE indicates the amount of disk space in the flash recovery area

          regards,
          • 2. Re: Guaranteed restore point and storage size
            mseberg
            Hello;

            I'm thinking something is wrong with your guaranteed restore point. This query may shake it out :
            SELECT 
              NAME, SCN, 
              TIME, 
              GUARANTEE_FLASHBACK_DATABASE, 
              STORAGE_SIZE
            FROM 
              V$RESTORE_POINT
            WHERE 
              GUARANTEE_FLASHBACK_DATABASE='YES';
            Is it possible you created a normal restore point ? Did you use "GUARANTEE FLASHBACK DATABASE" in the create statement ?

            Best Regards

            mseberg
            • 3. Re: Guaranteed restore point and storage size
              AJ
              As you can see from my original posting the column guarantee_flashback_database was YES.

              Here is the output from your query:
              SQL>select
                2  name, scn, time,
                3  guarantee_flashback_database,
                4  storage_size
                5  from v$restore_point
                6  where guarantee_flashback_database='YES';
              
              NAME                                  SCN TIME                                GUA STORAGE_SIZE
              ------------------------------ ---------- ----------------------------------- --- ------------
              RP_PRE_BUKVILKAR               2437036661 02-OCT-12 04.00.09.000000000 PM     YES            0
              PRE_TAS4279_20120927           2432564020 27-SEP-12 07.00.01.000000000 PM     YES   3221225472
              PRE_TAS4279_20121001           2434550224 01-OCT-12 07.00.01.000000000 PM     YES   2.0938E+10
              PRE_TAS4279_20121002           2437048384 02-OCT-12 07.00.01.000000000 PM     YES   1.1811E+10
              PRE_TAS4279_20121003           2438743440 03-OCT-12 07.00.01.000000000 PM     YES   4294967296
              PRE_TAS4279_20120928           2433111428 28-SEP-12 07.00.02.000000000 PM     YES   1.1274E+10
              
              6 rows selected.
              I think this is very strange. I know for normal restore points the documentation states that storage_size will only be nonzero for guaranteed restore points.
              And as you can see. Mine are guaranteed (i hope).


              Thanks
              • 4. Re: Guaranteed restore point and storage size
                mseberg
                Hello again;

                I tried to recreated :
                Connected to:
                Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                With the Partitioning, OLAP, Data Mining and Real Application Testing options
                
                SQL> CREATE RESTORE POINT RP_PRE_BUKVILKAR GUARANTEE FLASHBACK DATABASE;
                
                Restore point created.
                
                SQL> SELECT 
                  2    NAME, SCN, 
                  3    TIME, 
                  4    GUARANTEE_FLASHBACK_DATABASE, 
                  5    STORAGE_SIZE
                  6  FROM 
                  7    V$RESTORE_POINT
                  8  WHERE 
                  9    GUARANTEE_FLASHBACK_DATABASE='YES';
                
                NAME                        SCN TIME                 GUA STORAGE_SIZE
                -------------------- ---------- -------------------- --- ------------
                RP_PRE_BUKVILKAR     1.3015E+12 04-OCT-12 07.19.34.0 YES     15941632
                                                00000000 AM
                
                
                SQL> 
                But I'm unable to reproduce. Can you post your create statement? I'm thinking the issue might rest with FRA.

                Best Regards

                mseberg
                • 5. Re: Guaranteed restore point and storage size
                  AJ
                  mseberg,
                  I dont have the exact output from SQL*Plus but I'm posting the create and drop of the restore point taken from the instance's alert log:
                  Tue Oct 02 16:00:09 2012
                  Created guaranteed restore point RP_PRE_BUKVILKAR
                  Tue Oct 02 19:00:01 2012
                  
                  Thu Oct 04 13:57:05 2012
                  Drop guaranteed restore point RP_PRE_BUKVILKAR
                  Guaranteed restore point RP_PRE_BUKVILKAR dropped
                  The facts are pretty clear - the restore point was indeed created as guaranteed..
                  • 6. Re: Guaranteed restore point and storage size
                    mseberg
                    Agreed.

                    h2. Changing my answer

                    I created a second point and...

                    SQL> CREATE RESTORE POINT RP_PRE_BUKVILKAR3 GUARANTEE FLASHBACK DATABASE;
                    
                    Restore point created.
                    
                    SQL> SELECT 
                      2    NAME, SCN, 
                      3    TIME, 
                      4    GUARANTEE_FLASHBACK_DATABASE, 
                      5    STORAGE_SIZE
                      6  FROM 
                      7    V$RESTORE_POINT
                      8  WHERE 
                      9    GUARANTEE_FLASHBACK_DATABASE='YES';
                    
                    NAME                        SCN TIME                 GUA STORAGE_SIZE
                    -------------------- ---------- -------------------- --- ------------
                    RP_PRE_BUKVILKAR     1.3015E+12 04-OCT-12 07.19.34.0 YES            0
                                                    00000000 AM
                    
                    RP_PRE_BUKVILKAR3    1.3015E+12 04-OCT-12 07.58.49.0 YES     15941632
                                                    00000000 AM
                    
                    
                    SQL> 
                    Look what happened to RP_PRE_BUKVILKAR

                    So it has something to do with how close the points are together.

                    Notice the STORAGE_SIZE did not change It just moved to the other restore point.


                    Best Regards

                    mseberg

                    Edited by: mseberg on Oct 4, 2012 7:59 AM
                    • 7. Re: Guaranteed restore point and storage size
                      AJ
                      Very interesting and useful to notice.

                      I guess this explains it. And of course the first restore point should still be valid.

                      Thanks for your help :)

                      Guess this thread is solved.