Re: flashback database in 10g Rel 2

From: jms <noone_at_noone.org>
Date: Sat, 12 Apr 2008 17:11:20 +1000
Message-Id: <48006098$0$25819$afc38c87@news.optusnet.com.au>

jms wrote:

> DA Morgan wrote:
>

>> 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

>
> 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;
>
> LOG_MODE
> ------------
> NOARCHIVELOG
> 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
> /10.2.0/db_1/flash_recovery_area
> 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;
>
> SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME
> NAME
> ---------- --------------------- --- ------------
> ---------------------------------------------------------------------------
> --------------------------------------------------------------------------------------------------------------------------------
> 549101 2 NO 0 12/APR/08
> 04:21:28.000000000 PM
> TEST_RESTORE_POINT
>
> =============================================
> 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.

Furthermore, at work, the directory specified by the db_recovery_file_dest parameter has 2 directories underneath it. One is called ARCHIVELOG, the other FLASHBACK, and there are different archivelogs(?) underneath each. Why ? ... and whenever i flashback the database to a guaranteed restore point, it always pickup the SCN / read from one the files under the ARCHIVELOG directory ( based on tailing the alert log ), never from the files in the FLASHBACK directory. Received on Sat Apr 12 2008 - 02:11:20 CDT