flashback database in 10g Rel 2

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. Received on Wed Mar 19 2008 - 05:30:38 CDT