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@xxxxxxxxx>
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@xxxxxxxx> 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@xxxxxxxxx]
*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@xxxxxxxx>:
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@xxxxxxxxx]
*Sent:* Tuesday, July 28, 2015 10:42 AM
*To:* Mark W. Farnham
*Cc:* ram.cheruvattath@xxxxxxxxx; 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@xxxxxxxx>:
AND, for the purpose you describe Oracle has provided Edition Based
Redefinition.
I **think** that is the future.
mwf
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Ram Cheruvattath
*Sent:* Tuesday, July 28, 2015 8:58 AM
*To:* ecandrietta@xxxxxxxxx; 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@xxxxxxxxx>
*Sent:* Tuesday, July 28, 2015 8:03 AM
*To:* ORACLE-L <oracle-l@xxxxxxxxxxxxx>
*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
