FAQ
Hi All,

I'd like to have the option to flashback a few large-ish tables (NOT the whole database) to the start of a complex data manipulation operation. However I have no idea how long this operation will take so I have to set UNDO_RETENTION to a big value just to be sure. I'd prefer to be able to define a point at the start of the operation, and say "keep enough undo to go back here". Is there any such mechanism? (Only thing I could think of was to keep dynamically cranking up UNDO_RETENTION as the operation proceeds but that seems really messy).

I'll probably just stick with the tried and trusted CTAS "backup" of the tables before I start but just wanted to check that I'm not missing out on something that could save me time and space.

Any suggestions welcome - thanks!
Charlotte

Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

Search Discussions

  • Robert Freeman at Apr 30, 2008 at 8:22 pm
    11g makes this much easier .... :-) You can define a flashback archive
    that is set to time x, assign that archive to a tablespace, assign the
    table to that archive and you will have guaranteed retention of x for
    that table. Very cool feature.

    All in my 11g New Features book. I thought I did a blog entry on this but can't find it. Guess I will be adding that in the next couple of days!!

    Cheers!!

    RF
    Robert G. Freeman
    Author:
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press)

    Original Message ----
    From: Charlotte Hammond
    To: oracle-l_at_freelists.org
    Sent: Wednesday, April 30, 2008 2:01:52 PM
    Subject: Guaranteed Flashback Table

    Hi All,

    I'd like to have the option to flashback a few large-ish tables (NOT the whole database) to the start of a complex data manipulation operation. However I have no idea how long this operation will take so I have to set UNDO_RETENTION to a big value just to be sure. I'd prefer to be able to define a point at the start of the operation, and say "keep enough undo to go back here". Is there any such mechanism? (Only thing I could think of was to keep dynamically cranking up UNDO_RETENTION as the operation proceeds but that seems really messy).

    I'll probably just stick with the tried and trusted CTAS "backup" of the tables before I start but just wanted to check that I'm not missing out on something that could save me time and space.

    Any suggestions welcome - thanks!
    Charlotte

    Be a better friend, newshound, and
    know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
  • Stefan Knecht at Apr 30, 2008 at 9:16 pm
    Hi Charlotte

    No need to go to 11g -- you can do this in 10gR2 as well -- take a look at
    the retention guarantee clause, which you can specifiy at tablespace level:

    From:
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7003.htm#i2231734

    RETENTION GUARANTEE specifies that Oracle Database should preserve unexpired
    undo data in all undo segments of tablespace even if doing so forces the
    failure of ongoing operations that need undo space in those segments. This
    setting is useful if you need to issue an Oracle Flashback Query or an
    Oracle Flashback Transaction Query to diagnose and correct a problem with
    the data.

    Cheers

    Stefan

    On Wed, Apr 30, 2008 at 10:01 PM, Charlotte Hammond <
    charlottejanehammond_at_yahoo.com> wrote:
    Hi All,

    I'd like to have the option to flashback a few large-ish tables (NOT the
    whole database) to the start of a complex data manipulation operation.
    However I have no idea how long this operation will take so I have to set
    UNDO_RETENTION to a big value just to be sure. I'd prefer to be able to
    define a point at the start of the operation, and say "keep enough undo to
    go back here". Is there any such mechanism? (Only thing I could think of
    was to keep dynamically cranking up UNDO_RETENTION as the operation proceeds
    but that seems really messy).

    I'll probably just stick with the tried and trusted CTAS "backup" of the
    tables before I start but just wanted to check that I'm not missing out on
    something that could save me time and space.


    Any suggestions welcome - thanks!
    Charlotte






    ____________________________________________________________________________________
    Be a better friend, newshound, and
    know-it-all with Yahoo! Mobile. Try it now.
    http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
    --
    http://www.freelists.org/webpage/oracle-l

    --
    =========================

    Stefan P Knecht
    Senior Consultant
    Infrastructure Managed Services

    Trivadis AG
    Europa-Strasse 5
    CH-8152 Glattbrugg

    Phone +41-44-808 70 20
    Fax +41-808 70 12
    Mobile +41-79-571 36 27
    stefan.knecht_at_trivadis.com
    http://www.trivadis.com

    OCP 9i/10g SCSA SCNA
    =========================

    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Apr 30, 2008 at 10:20 pm
    RETENTION GUARANTEE may be the answer to her problem, yes.

    RETENTION GUARANTEE and flashback archive
    tablespaces are very different animals though. If you use retention guarantee
    and the undo tablespace fills up, then you start having wide scale
    database problems with DML across all database objects. Probably not
    the best of all worlds.

    With flashback archive tablespaces, you don't have this wide scale
    problem. Flashback data is retained only for the objects assigned to
    the tablespace. If you run out of space, then it's only the objects
    assigned to that flashback archive (not the tablespace) that will have
    issues with DML.

    Certainly RETENTION GUARANTEE can be used in her case, but with wide scale risks to all database DML. Flashback archives eliminates this risk considerably.

    Cheers!

    RF

    Robert G. Freeman
    Author:
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press)

    Original Message ----
    From: Stefan Knecht
    To: charlottejanehammond_at_yahoo.com
    Cc: oracle-l
    Sent: Wednesday, April 30, 2008 3:16:56 PM
    Subject: Re: Guaranteed Flashback Table

    Hi Charlotte

    No need to go to 11g -- you can do this in 10gR2 as well -- take a look at the retention guarantee clause, which you can specifiy at tablespace level:

    From: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7003.htm#i2231734

    RETENTION GUARANTEE specifies that Oracle Database should preserve unexpired undo data in all undo segments of tablespace even if doing so forces the failure of ongoing operations that need
    undo space in those segments. This setting is useful if you need to
    issue an Oracle Flashback Query or an Oracle Flashback Transaction
    Query to diagnose and correct a problem with the data.

    Cheers

    Stefan

    On Wed, Apr 30, 2008 at 10:01 PM, Charlotte Hammond wrote:

    Hi All,

    I'd like to have the option to flashback a few large-ish tables (NOT the whole database) to the start of a complex data manipulation operation. However I have no idea how long this operation will take so I have to set UNDO_RETENTION to a big value just to be sure. I'd prefer to be able to define a point at the start of the operation, and say "keep enough undo to go back here". Is there any such mechanism? (Only thing I could think of was to keep dynamically cranking up UNDO_RETENTION as the operation proceeds but that seems really messy).

    I'll probably just stick with the tried and trusted CTAS "backup" of the tables before I start but just wanted to check that I'm not missing out on something that could save me time and space.

    Any suggestions welcome - thanks!
    Charlotte

    Be a better friend, newshound, and
    know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
    --
    http://www.freelists.org/webpage/oracle-l

    --

    Stefan P Knecht
    Senior Consultant
    Infrastructure Managed Services

    Trivadis AG
    Europa-Strasse 5
    CH-8152 Glattbrugg

    Phone +41-44-808 70 20
    Fax +41-808 70 12
    Mobile +41-79-571 36 27
    stefan.knecht_at_trivadis.com
    http://www.trivadis.com

    OCP 9i/10g SCSA SCNA

    --
    http://www.freelists.org/webpage/oracle-l
  • Maureen English at Apr 30, 2008 at 11:41 pm
    Has anyone successfully applied the CPUApr2008 patch to
    a 10.2.0.3.0 database on HP Tru64 unix?

    I get no errors applying the patch to the RDBMS, but when
    I start up the database and run the catcpu.sql script, it
    fails with an ORA-07445 error. I did open a service request
    with Oracle, but am curious if there's anyone else experiencing
    the same problem.

    Maureen
    University of Alaska
  • Robert Freeman at Apr 30, 2008 at 10:20 pm
    OF course, she could be on 9iR1 and then none of this makes a difference! :-)

    Robert G. Freeman
    Author:
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press)

    Original Message ----
    From: Stefan Knecht
    To: charlottejanehammond_at_yahoo.com
    Cc: oracle-l
    Sent: Wednesday, April 30, 2008 3:16:56 PM
    Subject: Re: Guaranteed Flashback Table

    Hi Charlotte

    No need to go to 11g -- you can do this in 10gR2 as well -- take a look at the retention guarantee clause, which you can specifiy at tablespace level:

    From: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7003.htm#i2231734

    RETENTION GUARANTEE specifies that Oracle Database should preserve unexpired undo data in all undo segments of tablespace even if doing so forces the failure of ongoing operations that need
    undo space in those segments. This setting is useful if you need to
    issue an Oracle Flashback Query or an Oracle Flashback Transaction
    Query to diagnose and correct a problem with the data.

    Cheers

    Stefan

    On Wed, Apr 30, 2008 at 10:01 PM, Charlotte Hammond wrote:

    Hi All,

    I'd like to have the option to flashback a few large-ish tables (NOT the whole database) to the start of a complex data manipulation operation. However I have no idea how long this operation will take so I have to set UNDO_RETENTION to a big value just to be sure. I'd prefer to be able to define a point at the start of the operation, and say "keep enough undo to go back here". Is there any such mechanism? (Only thing I could think of was to keep dynamically cranking up UNDO_RETENTION as the operation proceeds but that seems really messy).

    I'll probably just stick with the tried and trusted CTAS "backup" of the tables before I start but just wanted to check that I'm not missing out on something that could save me time and space.

    Any suggestions welcome - thanks!
    Charlotte

    Be a better friend, newshound, and
    know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
    --
    http://www.freelists.org/webpage/oracle-l

    --

    Stefan P Knecht
    Senior Consultant
    Infrastructure Managed Services

    Trivadis AG
    Europa-Strasse 5
    CH-8152 Glattbrugg

    Phone +41-44-808 70 20
    Fax +41-808 70 12
    Mobile +41-79-571 36 27
    stefan.knecht_at_trivadis.com
    http://www.trivadis.com

    OCP 9i/10g SCSA SCNA

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 30, '08 at 8:01p
activeApr 30, '08 at 11:41p
posts6
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase