Re: flashback database in 10g Rel 2

From: jms <>
Date: Sat, 12 Apr 2008 17:06:49 +1000
Message-Id: <48005f89$0$25819$>

DA Morgan wrote:

> Steve Howard wrote:
>> On Mar 19, 6:30 am, jms <> 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',
>>> 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.
>> HTH,
>> Steve
> Also look at CREATE GUARANTEED RESTORE POINT. It is a bit safer

Thanks guys. I have been using guaranteed restore points for the past 2 weeks at work since I posted this question to this ng, and all is great specially for re-testing the application over and over and over ... to prove that the changes made did improve the application's performance.

Now I have a question with regards to NON-guaranteed restore points. When I was installing Oracle10gR2 on my linux notebook, I noticed during the install that you can have flashback WITHOUT having the database in archivelog mode ???

Using dbca ( Step 6 of 9 in the manage templates ), it says:

[x] Specify Flash Recovery Area

This is used as the default for all backup and recovery operations, and is also required for automatic backup using Enterprise Manager. Oracle recommends that the database files and recovery files be located on physically different disks for data protection and performance

Flash Recovery Area: {ORACLE_BASE}/flash_recovery_area Flash Recovery Area Aize: 2048 M Bytes

[ ] Enable Archiving

Notice that enabling the flash recovery area did not require enabling archiving ... so I'm confused. From what I gathered so far:

  1. You must have the database in archivelog mode to have guaranteed restore points.
  2. You DO NOT need to have the database in archivelog mode to have non-guaranteed restore points.

So here on my notebook, I have:

SQL> select log_mode from v$database;


SQL> show parameters archive
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string
log_archive_format                   string      %t_%s_%r.dbf
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
remote_archive_enable                string      true
standby_archive_dest                 string      ?/dbs/arch
SQL> show parameters recovery
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /home/oracle/oracle/product
db_recovery_file_dest_size           big integer 2G
recovery_parallelism                 integer     0

SQL> create restore point test_restore_point;

Restore point created.

SQL> create restore point test_restore_point2 guarantee flashback database; create restore point test_restore_point2 guarantee flashback database *
ERROR at line 1:
ORA-38784: Cannot create restore point 'TEST_RESTORE_POINT2'. ORA-38785: Media recovery must be enabled for guaranteed restore point.

SQL> select * from v$restore_point;


---------- --------------------- --- ------------ --------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
    549101                     2 NO             0 12/APR/08
04:21:28.000000000 PM                                            

So the above sort of proves that you don't need to have the database in archivelog mode to have non-guaranteed restore points.

But how do you then flashback the database to a non-guaranteed restore point?

SQL> create user joe identified by joe;

User created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 272629760 bytes

Fixed Size                  2020088 bytes
Variable Size              83889416 bytes
Database Buffers          180355072 bytes
Redo Buffers                6365184 bytes
Database mounted.
SQL> flashback database to restore point test_restore_point; flashback database to restore point test_restore_point *
ERROR at line 1:
ORA-38726: Flashback database logging is not on.

So it seems like you can't flashback

SQL> alter database flashback on;
alter database flashback on
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38707: Media recovery is not enabled.

So I am confused .... What's the point of :

1) Allowing a non-guaranteed restore point, 
2) When the database is in noarchivelog mode
3) When the database does not have flashback enabled

.... when you can't flashback to it anyway ?

OK ... I guess it's a silly question. You have to have flashback on the database enabled to allow you to .. well .. flashback. But shouldn't Oracle just have prevented me from creating the non-guaranteed restore point if the database is in noarchivelog mode or if the database does not have flashback enabled ?

It seems like I am overlapping my ideas of what the flash recovery area is, restore points, and flashback database. Received on Sat Apr 12 2008 - 02:06:49 CDT

Original text of this message