Re: Rollback via Fast Recovery Area - Oracle 11g

You may also want to consider one of the many other flashback options available like flashback transaction to roll back entire transactions. There are limitations to this like not being able to roll back a transaction across a DDL change (prior to 12c) but this can be combined with features like flashback drop and create table as select (CTAS). There are also time and data volume limitations on many of these flashback features because they rely on undo data but that can also be addressed using Total Recall (renamed Flashback Data Archive in 12c) to create an archive of changes to an object beyond what is present in the undo tablespace.

When it comes down to it, unless you're writing procedures into your releases that utilize features like EBR or flashback transaction, flashback database is probably the way to go.

Seth Miller

On Wed, Jul 29, 2015 at 8:46 AM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> That is the part that is handled by cross edition triggers. Which, as Ram
> noted, could become complex for some changes and which might be unneeded
> overhead when being able to do approval changes with interruptions for
> service and it is okay to throw away transaction made during the approval
> window.
>
>
>
> Rolling back to a restore point seems like an easier route for iterative
> proposals in a development environment while EBR shines most brightly for
> non-stop production upgrades and improvements.
>
>
>
> When a database change is required, as perhaps with virtual columns,
> between the “old” and the “new” doing it via EBR and cross edition triggers
> has the salutary reward for the extra effort not only of facilitating
> non-stop production actuation, but also of deeply understanding the changes
> being made.
>
>
>
> My friend Connor expressed this very well here, together with a useful
> follow-up comment from Bryn:
>
>
>
>
> http://www.oaktable.net/content/edition-based-redefinition-%E2%80%93-apology
>
>
>
> mwf
>
>
>
>
>
> *From:* Eriovaldo Andrietta [mailto:ecandrietta_at_gmail.com]
> *Sent:* Wednesday, July 29, 2015 7:21 AM
> *To:* Mark W. Farnham
> *Cc:* Ramsankar Cheruvattath; ORACLE-L
>
> *Subject:* Re: Rollback via Fast Recovery Area - Oracle 11g
>
>
>
> Mark,
>
>
>
> The Edition-Based Redefinition is about DDL and not DML.
>
> There is a step that change data via DML commands in the database.
>
> I also need to rollback data.
>
>
>
> Regards
>
> Eriovaldo
>
>
>
>
>
> 2015-07-28 11:57 GMT-03:00 Mark W. Farnham <mwf_at_rsiz.com>:
>
> I’m not sure what you mean by “attend completely.”
>
>
>
> Perhaps you can describe what cannot be accomplished with EBR that you are
> trying to accomplish.
>
>
>
> mwf
>
>
>
> *From:* Eriovaldo Andrietta [mailto:ecandrietta_at_gmail.com]
> *Sent:* Tuesday, July 28, 2015 10:42 AM
> *To:* Mark W. Farnham
> *Cc:* ram.cheruvattath_at_gmail.com; ORACLE-L
>
>
> *Subject:* Re: Rollback via Fast Recovery Area - Oracle 11g
>
>
>
> Mark,
>
>
>
> I did some tests with this resource as described in the link below, but it
> does not attend completely.
>
>
>
> http://www.oracle.com/technetwork/testcontent/o10asktom-172777.html
>
>
>
> Regards
>
> Eriovaldo
>
>
>
>
>
> 2015-07-28 10:38 GMT-03:00 Mark W. Farnham <mwf_at_rsiz.com>:
>
> AND, for the purpose you describe Oracle has provided Edition Based
> Redefinition.
>
>
>
> I **think** that is the future.
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Ram Cheruvattath
> *Sent:* Tuesday, July 28, 2015 8:58 AM
> *To:* ecandrietta_at_gmail.com; ORACLE-L
> *Subject:* Re: Rollback via Fast Recovery Area - Oracle 11g
>
>
>
> If all you want to do is flashback to a specific restore point, there is
> no need to set flashback logging on at the database level. A guaranteed
> restore point is all you need. There should be no need to shutdown the
> database to enable flashback logging.
>
>
>
> Ram
>
>
>
> *From:* Eriovaldo Andrietta <ecandrietta_at_gmail.com>
>
> *Sent:* Tuesday, July 28, 2015 8:03 AM
>
> *To:* ORACLE-L <oracle-l_at_freelists.org>
>
> *Subject:* Rollback via Fast Recovery Area - Oracle 11g
>
>
>
> Hi,
>
>
>
> I am planning a rollback (Fast Recovery Area) procedure in the Oracle
> 11g.
>
> The idea is: Change the product version. For example, the product is in
> the version 10.0 and I need to change some objects and data increasing the
> version to 10.1.
>
>
>
> If everything is OK I don´t need rollback, it is considered as GO
>
> but for some reason, I can imagine many, the customer says: NOGO, so I
> need to rollback to version 10.0
>
>
>
> I did some research about Fast Recovery Area and commands are bellow.
>
>
>
> My doubts are:
>
>
>
> 1.) Does anyone have experience with this recovery resource ?
>
> 2.) If I don´t need rollback , can I only drop restore point that all
> changes made will stay in the database ?
>
>
>
>
>
> /* ---------- BEGIN ------------------------- */
>
>
>
> shutdown immediate
>
> startup mount
>
> archive log list
>
> show parameter db_recovery_file
>
>
>
> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10g;
>
> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '<some folder in the database
> server disk>';
>
>
>
> select flashback_on from v$database;
>
> alter database flashback on ;
>
> select flashback_on from v$database;
>
> create restore point teste guarantee flashback database;
>
> select flashback_on from v$database;
>
> select scn, garantee_flashback_database, time, name from v$restore_point;
>
> alter database open;
>
> -----------> ****************************************************
> <------------
>
> -----------> Change objects via DDL commands in the database
> <------------
>
> -----------> Change data via DML commands in the database
> <------------
>
> -----------> ****************************************************
> <------------
>
> shutdown immediate
>
> startup mount
>
> flashback database to restore point stable;
>
> alter database open resetlogs;
>
>
>
> drop restore point stable;
>
> select flashback_on from v$database;
>
> alter database flashback on ;
>
> select flashback_on from v$database;
>
> flashback database to restore point teste;
>
> alter database open resetlogs;
>
> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 0;
>
> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '';
>
>
>
> /* ---------- END ------------------------- */
>
>
>
>
>
> Regards
>
> Eriovaldo
>
>
>
>
>
>
>

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