Re: flashback database in 10g Rel 2
Date: Sat, 12 Apr 2008 17:06:49 +1000
Message-Id: <48005f89$0$25819$afc38c87@news.optusnet.com.au>
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:
- You must have the database in archivelog mode to have guaranteed restore points.
- 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/archSQL> 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 bytesDatabase 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