Re: Rollback via Fast Recovery Area - Oracle 11g
Date: Mon, 3 Aug 2015 16:07:18 -0500
Message-ID: <CALrB5pooA1dkZSe7V3f3vGi=TFKhmS=Og-1K=gChE=_MYcOn7g_at_mail.gmail.com>
It sounds like what's really needed is some sort of separate product staging environment which would be considered a pre-production environment (we had something similar at a previous employer). Have the business user, client, etc look at the product there. Look good? Then deploy to Production. If it truly has to be done in Production then maybe a custom versioning application for your product line with ties back to your data would make more sense.
I'm not sure how often you would be expecting to rollback the changes/transaction but could you afford to shutdown the database and open resetlogs every time you needed to do this? Perhaps you can but I would imagine if it's Production you wouldn't have that luxury.
Not knowing the intricate details of what you are trying to do, the above suggestion may not even make any sense. :(
Alfredo
On Thu, Jul 30, 2015 at 1:52 PM, Seth Miller <sethmiller.sm_at_gmail.com> wrote:
> Eriovaldo,
>
> 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-lReceived on Mon Aug 03 2015 - 23:07:18 CEST