Re: flashback database in 10g Rel 2

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 Received on Wed Mar 19 2008 - 07:00:34 CDT