Re: flashback database in 10g Rel 2
From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 19 Mar 2008 13:40:52 -0700
Message-ID: <1205959249.465544@bubbleator.drizzle.com>
>> Hi all,
>>
>> I am trying to use flashback database in 10g to do testing comparisons
>> between several iterations of a tuning exercise. So basically:
>>
>> 1) Start from a known state, and enable flashback database functionality
>> 2) Update tables that are required to be performed for the tuning exercise
>> 3) Run application, also gathering statspack, etc. ( First run of the app
>> will be the "un-tuned" application )
>> 4) Collect all the statistics, statspack report, etc.
>> 5) Tune application from step 4
>> 6) Flashback database prior to step 3 ( but after step 2 )
>> 7) Go back to step 3, etc...
>>
>> ... until most of the app is tuned.
>>
>> I'm using flashback database:
>>
>> * To ensure the same set of data is used for each iteration
>> * Flashback database is a lot faster then re-importing the entire schema,
>> specially big tables.
>>
>> Questions:
>>
>> 1) Can you run "flashback database to ..." in sqlplus (logged in as sysdba),
>> or should you only run this in rman ?
>>
>> 2) When I tried tried running "flashback database" from rman, after shutting
>> down the instance and mounting the database, "flashback database to
>> timestamp" is not recognised. Instead "flash database to time" is OK. Can't
>> recall the exact ORA error, since it was at work. Haven't tried "flashback
>> database to timestamp" from sqlplus though.
>>
>> 3) From within rman, I also could not specify the correct date / time format
>> to do a PITR given an absolute time. Tried something like:
>>
>> flashback database to time "19-Mar-2008 16:35:00"
>>
>> but I get an "ORA-01830 date format picture ends before converting entire
>> input string." Since I am in rman, I could not use built-in functions like
>> to_timestamp or to_date:
>>
>> flashback database to time to_timestamp('2008-03-19 16:35:00', 'YYYY-MM-DD
>> HH24:MI:SS' );
>>
>> ... because it complains of "to_timestamp" as not an expected keyword. Only
>> way I could specify the time from rman in by doing a subtraction from
>> sysdate:
>>
>> flashback database to time sysdate-1/24;
>>
>> 4) If I could run it from sqlplus, even when the database is mounted only,
>> then I presume I could use to_date or to_timestamp or to_date. Will try
>> again tomorrow.
Date: Wed, 19 Mar 2008 13:40:52 -0700
Message-ID: <1205959249.465544@bubbleator.drizzle.com>
Steve Howard wrote:
> On Mar 19, 6:30 am, jms <no..._at_noone.org> wrote:
>> Hi all,
>>
>> I am trying to use flashback database in 10g to do testing comparisons
>> between several iterations of a tuning exercise. So basically:
>>
>> 1) Start from a known state, and enable flashback database functionality
>> 2) Update tables that are required to be performed for the tuning exercise
>> 3) Run application, also gathering statspack, etc. ( First run of the app
>> will be the "un-tuned" application )
>> 4) Collect all the statistics, statspack report, etc.
>> 5) Tune application from step 4
>> 6) Flashback database prior to step 3 ( but after step 2 )
>> 7) Go back to step 3, etc...
>>
>> ... until most of the app is tuned.
>>
>> I'm using flashback database:
>>
>> * To ensure the same set of data is used for each iteration
>> * Flashback database is a lot faster then re-importing the entire schema,
>> specially big tables.
>>
>> Questions:
>>
>> 1) Can you run "flashback database to ..." in sqlplus (logged in as sysdba),
>> or should you only run this in rman ?
>>
>> 2) When I tried tried running "flashback database" from rman, after shutting
>> down the instance and mounting the database, "flashback database to
>> timestamp" is not recognised. Instead "flash database to time" is OK. Can't
>> recall the exact ORA error, since it was at work. Haven't tried "flashback
>> database to timestamp" from sqlplus though.
>>
>> 3) From within rman, I also could not specify the correct date / time format
>> to do a PITR given an absolute time. Tried something like:
>>
>> flashback database to time "19-Mar-2008 16:35:00"
>>
>> but I get an "ORA-01830 date format picture ends before converting entire
>> input string." Since I am in rman, I could not use built-in functions like
>> to_timestamp or to_date:
>>
>> flashback database to time to_timestamp('2008-03-19 16:35:00', 'YYYY-MM-DD
>> HH24:MI:SS' );
>>
>> ... because it complains of "to_timestamp" as not an expected keyword. Only
>> way I could specify the time from rman in by doing a subtraction from
>> sysdate:
>>
>> flashback database to time sysdate-1/24;
>>
>> 4) If I could run it from sqlplus, even when the database is mounted only,
>> then I presume I could use to_date or to_timestamp or to_date. Will try
>> again tomorrow.
> > Hi, > > I would look at "create restore point", as it would meet exactly what > you need. > > http://www.oracle.com/technology/oramag/oracle/06-nov/o66recovery.html > > HTH, > > Steve
Also look at CREATE GUARANTEED RESTORE POINT. It is a bit safer http://www.psoug.org/reference/restore_points.html
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Wed Mar 19 2008 - 15:40:52 CDT