This discussion is archived
7 Replies Latest reply: Oct 4, 2012 6:52 AM by AJ RSS

Guaranteed restore point and storage size

AJ Explorer
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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.

Legend

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