database flash backup
A reader, August 06, 2007 - 8:21 pm UTC
Thanks,Tom!
Tt is very useful for me!
Regards!
Alan
database flashback
A reader, August 08, 2007 - 2:09 am UTC
Hi,Tom¿
Could you please give me a explain on this sentence you said:
on a rotating basis, not EVERY pre-checkpoint image needs to go here
Thanks very much.
Regards.
Alan
August 10, 2007 - 3:13 pm UTC
exactly what it says - say you want to flashback for at least 3 hours (your flashback retention)...
Great - we need a version of the block that is AT LEAST 3 hours old - it could be 5 hours, but it cannot be 2 hours old. That copy we have in the flash recovery area might be 4 hours old and in the intervening 4 hours - that block might have been checkpointed 500 times - we do not need to hold all (or even some really) intervening versions of that block...
content of flashback log
A reader, August 08, 2007 - 8:34 am UTC
Hi Tom,
Does the flashback log also consist out of change vectors like the redo log? How can I best imagine it's content?
August 14, 2007 - 10:04 am UTC
think of the flash recovery area as having
a) pre-checkpoint images of blocks (old versions of blocks, that are older than the time you want to flashback to)
b) archived redo logs - to roll forward the blocks in A to the correct point in time.
so the flashback database
a) finds a block version in the recovery area that pre-dates your flashback time
b) puts it in the datafile
c) uses the archives to roll it forward to the point in time you desire
database flashback
A reader, August 11, 2007 - 6:03 am UTC
Thanks! Tom.
you said:
exactly what it says - say you want to flashback for at least 3 hours (your flashback retention)...
Great - we need a version of the block that is AT LEAST 3 hours old - it could be 5 hours, but it cannot be 2 hours old. That copy we have in the flash recovery area might be 4 hours old and in the intervening 4 hours - that block might have been checkpointed 500 times - we do not need to hold all (or even some really) intervening versions of that block...
I think oracle can only keep one version of the block(the oldest one during the flashback retention time),when we do a flashback ,we can find the block and then aply archivelog,I think it can save much space of the flashback recover area.
But why you said some versions of the block ,not one ,why?
Regards!
Alan
August 14, 2007 - 3:32 pm UTC
why do you think that Oracle can only keep one version in there?
Heck, we keep multiple versions in the buffer cache - no reason why "one" is the only answer.
RE: content of flashback log
A reader, August 15, 2007 - 7:02 am UTC
hi tom,
thanks for your answer. please note, that i asked for the content of the flashback LOG - not the flash recovery area.
i don't think that the flashback log contains real block images. can i imagine the content of the flashback log stream to be equal to the redo log stream on the original blocks, that means including change vectors (row, field, action, old value, new value,...)?
August 20, 2007 - 1:32 pm UTC
then you need to rethink your thinking?
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14220/backrec.htm#sthref2385 <quote>
.... To Flashback a database, Oracle uses past block images to back out changes to the database. During normal database operation, Oracle occasionally logs these block images in Flashback logs.
</quote>
it cannot be change vectors - if it were EVERY CHANGE would need to be recorded there, all of them.
database flashback
A reader, August 15, 2007 - 7:20 am UTC
Thanks Tom!
But I still puzzled,maybe it was a foolish question,but I still want to know:
Yes,there are some versions of a block in the bache buffer,Why oracle just keep the oldest one into the flashback log in order to save space, when we do a database flash backup,we just find the oldest copy of the block then use archive log .
Sorry for my fool question,but please give a explain.
Regards!
Alan
August 20, 2007 - 1:32 pm UTC
because we need multiple versions so we can AGE OUT old stuff, yet, retain the ability to flashback.
Darshan, September 13, 2007 - 4:12 pm UTC
Hi Tom,
Hope you must be doing great!
I am little confused about the following lines
>>>>>
pre-checkpoint images of blocks (on a rotating basis, not EVERY pre-checkpoint image needs to go here)
copy we have in the flash recovery area might be 4 hours old and in the intervening 4 hours - that block might have been checkpointed 500 times - we do not need to hold all (or even some really) intervening versions of that block...
>>>>>>>
From documentation I understand old image of block goes to flashback logs. As per above lines it is not the case every time. Please clarify it bit more.
how it is decided that it should be written to flashback logs or not?
Best Regards,
Darshan
September 15, 2007 - 7:48 pm UTC
it is decided based on the amount of space in the flash recovery area and your retention target.
Flashback performance impact
Boris, February 11, 2008 - 7:31 pm UTC
Hi Tom,
Thank you for sharing your knowledge with us.
I cannot find much information on how flashback logs impact performance. What happens when Oracle cannot write flashback logs quickly? How flashback impact on performance can be minimized?
Thanks a lot,
Boris
February 11, 2008 - 10:51 pm UTC
it is sort of like archive logging.
Your mileage is your mileage and different from everyone else
update one block - a billion times, entirely different from someone updating one billion blocks - once.
You need to *test*
You need to ensure you have sufficient IO bandwidth
anyone that says "5.133253% impact" is not correct...
you can use the advisors to SIZE this area
you need to test to see what impact this has on you in real life.
Flashback performance impact
Boris, February 13, 2008 - 8:05 pm UTC
Hi Tom,
Thank you for your prompt reply.
I did test and found the results alarming, e.g. for two identical application runs:
Flashback disabled:
==================================
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
NPM 3186346935 npm 1 10.2.0.2.0 NO npm240-10.la
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 18 24-Jan-08 21:34:55 62 14.3
End Snap: 19 24-Jan-08 21:47:27 62 14.8
Elapsed: 12.52 (mins)
DB Time: 13.73 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 200M 200M Std Block Size: 4K
Shared Pool Size: 664M 664M Log Buffer: 6,200K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 627,227.58 4,088.99
Logical reads: 11,575.59 75.46
Block changes: 4,221.82 27.52
Physical reads: 169.86 1.11
Physical writes: 96.20 0.63
User calls: 835.51 5.45
Parses: 5.57 0.04
Hard parses: 0.01 0.00
Sorts: 153.89 1.00
Logons: 0.04 0.00
Executes: 1,171.35 7.64
Transactions: 153.39
% Blocks changed per Read: 36.47 Recursive Call %: 59.54
Rollback per transaction %: 0.00 Rows per Sort: 1.08
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.84 Redo NoWait %: 100.00
Buffer Hit %: 98.53 In-memory Sort %: 100.00
Library Hit %: 100.00 Soft Parse %: 99.76
Execute to Parse %: 99.52 Latch Hit %: 99.91
Parse CPU to Parse Elapsd %: 63.64 % Non-Parse CPU: 99.97
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 92.84 92.85
% SQL with executions>1: 98.71 99.33
% Memory for SQL w/exec>1: 96.45 98.64
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 687 83.4
db file parallel write 5,842 61 10 7.4 System I/O
db file scattered read 15,152 30 2 3.6 User I/O
db file sequential read 10,210 30 3 3.6 User I/O
log file sync 438 28 63 3.4 Commit
Flashback enabled:
==================================
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
NPM 3186346935 npm 1 10.2.0.2.0 NO npm240-10.la
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 18 25-Jan-08 01:43:48 63 13.8
End Snap: 19 25-Jan-08 01:57:03 63 13.8
Elapsed: 13.25 (mins)
DB Time: 30.99 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 200M 200M Std Block Size: 4K
Shared Pool Size: 664M 664M Log Buffer: 6,200K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 556,264.86 3,992.04
Logical reads: 9,725.16 69.79
Block changes: 3,594.56 25.80
Physical reads: 245.09 1.76
Physical writes: 99.94 0.72
User calls: 755.91 5.42
Parses: 2.55 0.02
Hard parses: 0.01 0.00
Sorts: 139.83 1.00
Logons: 0.04 0.00
Executes: 1,039.00 7.46
Transactions: 139.34
% Blocks changed per Read: 36.96 Recursive Call %: 58.90
Rollback per transaction %: 0.00 Rows per Sort: 1.08
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.61 Redo NoWait %: 100.00
Buffer Hit %: 97.48 In-memory Sort %: 100.00
Library Hit %: 100.00 Soft Parse %: 99.80
Execute to Parse %: 99.75 Latch Hit %: 99.91
Parse CPU to Parse Elapsd %: 68.18 % Non-Parse CPU: 99.98
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 92.87 92.87
% SQL with executions>1: 98.73 99.29
% Memory for SQL w/exec>1: 96.56 98.63
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 812 43.7
db file sequential read 62,250 557 9 30.0 User I/O
buffer busy waits 25,365 236 9 12.7 Concurrenc
db file scattered read 17,016 132 8 7.1 User I/O
db file parallel write 6,281 108 17 5.8 System I/O
Could you explain why database time increased from 13.73 to 30.99 minutes?
Since writing to flashback logs is similar to writing to the archived logs, e.g. asyncronous, the transactions should not wait for flashback writer process (RVWR) to complete writing to the flashback logs. In other words, whether or not RVWR can keep up with data changes, transaction throughput should remain the same. Is this a correct statement?
Thank you,
Boris
February 13, 2008 - 11:07 pm UTC
... In other words, whether or
not RVWR can keep up with data changes, transaction throughput should remain
the same. Is this a correct statement? ...
that in itself (i'm taking this snippet before even looking at anything statspack related) has to be known to be *false*
if rvwr incurrs write IO and you do this on disks that were IO bound BEFORE - eg: you did not give any extra resources for the obvious extra work that the instance as a whole would do - then they would be even MORE IO bound than before.
If you ADD WORKLOAD (which you did, you added extra work)
AND YOU DIDN'T HAVE FREE RESOURCES before for that workload
THEN - you have to expect an impact.
Architecturally speaking - if you have sufficient IO bandwidth, turning on archive log mode should not impact you
same here.
based on your IO (look at the average wait for IO before and after), I'd say you are flashbacking to the same - already stressed - disks you were using before.
eg: you just over IO'ed youself.
db file scattered read 15,152 30 2 3.6 User
db file scattered read 17,016 132 8 7.1 User
db file sequential read 10,210 30 3 3.6 User
db file sequential read 62,250 557 9 30.0 User
you tripled or quadrupled the IO times for reads.... I guess because you put everything on the existing, stressed set of disks.
flashback database question
bakunian, May 19, 2008 - 2:49 pm UTC
Tom,
In 10gR2 database I created guaranteed restore point and I tested drop user cascade.
Why flashback to 'before_drop' restore point did not restore user and all his objects? According to flashback database concepts it should not care what I did, since before images of blocks from before restore point should be replaced and recovered.
Thanks in advance
May 19, 2008 - 6:13 pm UTC
you showed us nothing here, we have no idea what you might have typed into anything.
flashback database question
bakunian, May 19, 2008 - 7:13 pm UTC
I refered to
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/rpfbdb001.htm#CEGGCDAE
<quote>
Creating a guaranteed restore point at a particular SCN enforces the requirement that you can perform a Flashback Database operation to return your database to its state at that SCN, even if flashback logging is not enabled for your database.
</quote>
SQL> grant resource to scott identified by tiger;
Grant succeeded.
SQL> create table scott.t as select * from all_tables;
Table created.
SQL> sho parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string C:\app\oracle\product/flash_recovery_area
db_recovery_file_dest_size big integer 2G
SQL> select flashback_on, log_mode from v$database;
FLASHBACK_ON LOG_MODE
------------------ ------------
NO ARCHIVELOG
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1247404 bytes
Variable Size 134219604 bytes
Database Buffers 125829120 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL> create restore point before_drop guarantee flashback database;
Restore point created.
SQL> select flashback_on, log_mode from v$database;
FLASHBACK_ON LOG_MODE
------------------ ------------
RESTORE POINT ONLY ARCHIVELOG
SQL> alter database open;
Database altered.
SQL> select count(*) from scott.t;
COUNT(*)
----------
1635
SQL> drop user scott cascade;
User dropped.
SQL> select name, time, storage_size from v$restore_point;
NAME
------------------------------
TIME
------------------------------------------------------------
STORAGE_SIZE
------------
BEFORE_DROP
19-MAY-08 03.47.39.000000000 PM
8192000
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1247404 bytes
Variable Size 138413908 bytes
Database Buffers 121634816 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL> flashback database to restore point before_drop;
Flashback complete.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL>
SQL>
SQL>
SQL> select username from dba_users;
USERNAME
------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
RMAN
OUTLN
MDSYS
ORDSYS
USERNAME
------------------------------
EXFSYS
DMSYS
WMSYS
CTXSYS
ANONYMOUS
XDB
ORDPLUGINS
SI_INFORMTN_SCHEMA
OLAPSYS
TSMSYS
BI
USERNAME
------------------------------
PM
MDDATA
IX
SH
DIP
OE
HR
27 rows selected.
Did I misunderstand the definition or order of steps is not correct?
Thank you
May 20, 2008 - 10:47 am UTC
...
SQL> recover database;
Media recovery complete.
SQL> alter database open;
....
why did you that? you went forward again.
http://docs.oracle.com/docs/cd/B19306_01/backup.102/b14192/flashptr005.htm#sthref647 <quote>
When the command completes, you may open the database read-only and inspect the effects of the operation, and if satisfied, open the database with the RESETLOGS option.
</quote>
don't recover, open with resetlogs.
(this is why we needed the sequence of commands...)
ops$tkyte%ORA10GR2> connect / as sysdba
Connected.
sys%ORA10GR2> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /home/ora10gr2/oradata/ora10gr
2/fbra
db_recovery_file_dest_size big integer 2G
recovery_parallelism integer 0
sys%ORA10GR2> select flashback_on, log_mode from v$database;
FLASHBACK_ON LOG_MODE
------------------ ------------
NO ARCHIVELOG
sys%ORA10GR2> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys%ORA10GR2> startup mount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1261836 bytes
Variable Size 150998772 bytes
Database Buffers 377487360 bytes
Redo Buffers 7122944 bytes
Database mounted.
sys%ORA10GR2> create restore point before_drop guarantee flashback database;
Restore point created.
sys%ORA10GR2> select flashback_on, log_mode from v$database;
FLASHBACK_ON LOG_MODE
------------------ ------------
RESTORE POINT ONLY ARCHIVELOG
sys%ORA10GR2> alter database open;
Database altered.
sys%ORA10GR2> pause
sys%ORA10GR2> select count(*) from dba_objects where owner = 'SCOTT';
COUNT(*)
----------
16
sys%ORA10GR2> drop user scott cascade;
User dropped.
sys%ORA10GR2> select count(*) from dba_objects where owner = 'SCOTT';
COUNT(*)
----------
0
sys%ORA10GR2> select name, time, storage_size from v$restore_point;
NAME
------------------------------
TIME
---------------------------------------------------------------------------
STORAGE_SIZE
------------
BEFORE_DROP
20-MAY-08 10.43.32.000000000 AM
8192000
sys%ORA10GR2> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys%ORA10GR2> startup mount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1261836 bytes
Variable Size 150998772 bytes
Database Buffers 377487360 bytes
Redo Buffers 7122944 bytes
Database mounted.
sys%ORA10GR2> flashback database to restore point before_drop;
Flashback complete.
<b>
sys%ORA10GR2> alter database open RESETLOGS;
Database altered.
sys%ORA10GR2> select count(*) from dba_objects where owner = 'SCOTT';
COUNT(*)
----------
16
</b>
Bakunian - I'm not entire sure...but
Brett, May 20, 2008 - 9:00 am UTC
I don't think that you should issue the "recover database" command. Also, you should have an open database with the resetlogs option. Flashback database is an incomplete recovery...I think that you are doing a full recovery.
May 20, 2008 - 11:40 am UTC
that was definitely it - and why I always want a test case showing step by step what someone did.
when they originally wrote:
...
In 10gR2 database I created guaranteed restore point and I tested drop user cascade.
Why flashback to 'before_drop' restore point did not restore user and all his objects?
...
we had no idea what they really did - as soon as we see it - it is obvious what went wrong and why.
flashback database question
bakunian, May 20, 2008 - 5:55 pm UTC
OK I admit that was kind of stupid that I did "recover database". But I have follow up question regarding db_recovery_file_dest_size. Considering size of the database below how Oracle calculates minimal size of this location in order to allow creation of guaranteed restore point without flashback logging enabled?
I was getting bellow errors even when db_recovery_file_dest_size=2G even considering that sum of all datafiles is less that 2G but it worked as soon as I set db_recovery_file_dest_size=3G
ORA-38784: Cannot create restore point 'BEFORE_DROP'.
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38708: not enough space for first flashback database log file
1 select sum(bytes)/1024 Kb,
2 tablespace_name
3 from sys.dba_data_files
4* group by tablespace_name
SQL> /
KB TABLESPACE_NAME
---------- ------------------------------
399360 SYSAUX
76800 UNDOTBS1
25600 USERS
512000 SYSTEM
102400 EXAMPLE
2048 NONCRIT
6 rows selected.
Thanks,
May 20, 2008 - 9:48 pm UTC
are you sure about that?
sys%ORA10GR2> startup mount
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1261836 bytes
Variable Size 150998772 bytes
Database Buffers 377487360 bytes
Redo Buffers 7122944 bytes
Database mounted.
sys%ORA10GR2> create restore point before_drop guarantee flashback database;
Restore point created.
sys%ORA10GR2> alter database open;
Database altered.
sys%ORA10GR2> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /home/ora10gr2/oradata/ora10gr
2/fbra
<b>db_recovery_file_dest_size big integer 1G</b>
recovery_parallelism integer 0
sys%ORA10GR2> @free 1
%
% MaxPoss Max
Tablespace Name KBytes Used Free Used Largest Kbytes Used
------------------- ------------ ------------ ------------ ------ ------------ ------------ ------
*a BIG_TABLE 17,891,328 17,889,472 1,856 100.0 960 33,554,416 53.3
*a SYSAUX 522,240 481,536 40,704 92.2 8,128 33,554,416 1.6
*a TEST 102,400 64 102,336 .1 102,336 33,554,416 .3
*a USERS 451,840 9,792 442,048 2.2 195,456 33,554,416 1.3
*m MSSM 8,192 64 8,128 .8 8,128 33,554,416 .0
*m SYSTEM 565,248 557,888 7,360 98.7 7,104 33,554,416 1.7
*m TEMP_NEW 7,495,680 7,495,680 0 100.0 0 33,554,416 22.3
*m UNDOTBS1 2,336,768 2,335,872 896 100.0 896 33,554,416 7.0
------------ ------------ ------------
sum 29,373,696 28,770,368 603,328
8 rows selected.
sys%ORA10GR2> @rset
sys%ORA10GR2> set termout off
so, my 29gb database was happy with 1gb of flash recovery area.
Not enough space
Brett, May 21, 2008 - 8:55 am UTC
Wouldn't the database also consider the amount of activity it has? A 2GB database that generates 6GB of redo a day would probably need a bigger flashback area than a 100GB database that generates 50MB. What is the value of the db_flashback_retention_target?
May 21, 2008 - 9:13 am UTC
not so much for the restore point when flashback logging disabled....
flashback database question
bakunian, May 21, 2008 - 2:19 pm UTC
Tom,
You have database 29Gb and db_recovery_file_dest_size=2G which is around 7% my database 1.1Gb with db_recovery_file_dest_size=70M is about the same.
So these then are very strange results do you have any idea or I am just doing something really stupid?
Do you know what controls size of flashback file just curious because it seems that first file created in my case is always 8Mb?
Thanks
SQL> startup mount;
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1247404 bytes
Variable Size 130025300 bytes
Database Buffers 130023424 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> select flashback_on, log_mode from v$database;
FLASHBACK_ON LOG_MODE
------------------ ------------
NO ARCHIVELOG
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string C:\app\oracle\product/flash_recovery_area
db_recovery_file_dest_size big integer 70M
recovery_parallelism integer 0
SQL>
SQL>
SQL> create restore point before_drop guarantee flashback database;
create restore point before_drop guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_DROP'.
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38708: not enough space for first flashback database log file
SQL> alter system set db_recovery_file_dest_size=500M;
System altered.
SQL> sho parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string C:\app\oracle\product/flash_recovery_area
db_recovery_file_dest_size big integer 500M
recovery_parallelism integer 0
SQL> create restore point before_drop guarantee flashback database;
create restore point before_drop guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_DROP'.
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38708: not enough space for first flashback database log file
SQL> select open_mode, flashback_on, log_mode from v$database;
OPEN_MODE FLASHBACK_ON LOG_MODE
---------- ------------------ ------------
MOUNTED NO ARCHIVELOG
SQL> alter system set db_recovery_file_dest_size=1G;
System altered.
SQL> sho parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string C:\app\oracle\product/flash_recovery_area
db_recovery_file_dest_size big integer 1G
recovery_parallelism integer 0
SQL> create restore point before_drop guarantee flashback database;
create restore point before_drop guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_DROP'.
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38708: not enough space for first flashback database log file
SQL> alter system set db_recovery_file_dest_size=2G;
System altered.
SQL> create restore point before_drop guarantee flashback database;
Restore point created.
SQL> alter database open;
Database altered.
SQL> @free %
% MaxPoss Max
Tablespace Name Kbytes Used Free Used Largest Kbytes Used
------------------- ------------ ------------ ------------ ------ ------------ ------------ -----
*a EXAMPLE 102,400 79,232 23,168 77.4 20,288 33,554,416 0.
*a NONCRIT 2,048 128 1,920 6.3 1,920 0 0.
*a SYSAUX 399,360 385,152 14,208 96.4 10,176 33,554,416 1.
*a USERS 25,600 17,728 7,872 69.3 7,360 102,400 25.
*m SYSTEM 512,000 511,040 960 99.8 960 33,554,416 2.
*m TEMP 20,480 20,480 0 100.0 0 33,554,416 0.
*m UNDOTBS1 76,800 11,008 65,792 14.3 48,064 33,554,416 0.
------------ ------------ ------------
sum 1,138,688 1,024,768 113,920
7 rows selected.
SQL>
May 21, 2008 - 3:18 pm UTC
no, i had 29gb and 1gb.
it isn't a percentage, 70mb would be absurdly small in real life.
I cannot reproduce your findings at all. What release
flashback database question
bakunian, May 21, 2008 - 6:05 pm UTC
I kind of guessed that db_recovery_file_dest_size is not a percentage of the size of the database then again my database is not realistic size for real world either. At first I thought it's Windows acting up but then tested on Solaris and the same thing. The interesting thing is that there nowhere in the docs it gives any idea how large or small db_recovery_file_dest_size can be. So what do you think this can be?
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams054.htm#REFRN10235
SQL> select * from v$version;
BANNER
-------------------------------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> @free
%
% MaxPoss Max
Tablespace Name Kbytes Used Free Used Largest Kbytes Used
------------------- ------------ ------------ ------------ ------ ------------ ------------ -----
*a EXAMPLE 102,400 69,888 32,512 68.3 32,064 33,554,416 0.
*a SYSAUX 337,920 329,600 8,320 97.5 5,056 33,554,416 1.
*a USERS 5,120 3,072 2,048 60.0 2,048 33,554,416 0.
*m SYSTEM 501,760 497,920 3,840 99.2 3,008 33,554,416 1.
*m TEMP 20,480 20,480 0 100.0 0 33,554,416 0.
*m UNDOTBS1 30,720 12,480 18,240 40.6 10,816 33,554,416 0.
------------ ------------ ------------
sum 998,400 933,440 64,960
6 rows selected.
SQL> alter system set db_recovery_file_dest_size=160M;
SQL> sho parameter recover
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest string +ORA_ASM
db_recovery_file_dest_size big integer 160M
recovery_parallelism integer 0
SQL> create restore point before_drop guarantee flashback database;
create restore point before_drop guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_DROP'.
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38708: not enough space for first flashback database log file
SQL> alter system set db_recovery_file_dest_size=167M;
System altered.
SQL> create restore point before_drop guarantee flashback database;
Restore point created.
May 21, 2008 - 9:52 pm UTC
now 167m works?
10.2.0.1 is entirely unpatched, that could be something different here - I'm using current 10.2 code.
db_recovery_file_dest question
A reader, May 24, 2010 - 3:22 pm UTC
Hi Tom,
I am so confused with the following parameters
db_recovery_file_dest_size
db_flashback_retention_target
This is our current setting. We have set the db_flashback_retention_target to 4320 and db_recovery_file_dest_size to 400 gb. But if I do a
du -h .
on the filesystem that hosts the flash recovery area I see that it is using close to 770 gb. I saw this query from one of the articles and this is the result
select space_used/(1024*1024),space_limit/(1024*1024) from v$recovery_file_dest;
SPACE_USED/(1024*1024) SPACE_LIMIT/(1024*1024)
---------------------- -----------------------
232193.244 409600
What is comprising of this 232 gb that Oracle thinks is used by flash recovery area ? Arent backups part of this calculation ? Apart from these, I have 2 months worth of flashback logs, why arent they getting purged ? This is so confusing. Can you shed some light ?
Thanks a lot !!!!
May 24, 2010 - 7:37 pm UTC
show us what is in there, how many files - do an
ls -lR
from there and post it if it is NOT TOO large.
flash recovery area followup to above question
A reader, May 25, 2010 - 10:38 am UTC
Hi Tom - When I added up to 770 GB above, I was including my backupsets as well. On reading from Oracle documentation, I found that image copies (and not backupsets) are part of the flash recovery area. So to estimate the value for my db_recovery_file_Dest_size, I should just estimate based on n days of flashback logs + archive logs ? Is my understanding correct reg backupsets ?
How would I arrive at how many days of flashback logs I maintain ? Does that have any relationship with db_flashback_retention_target parameter ?
May 25, 2010 - 12:50 pm UTC
http://docs.oracle.com/docs/cd/E11882_01/server.112/e10595/omf002.htm#ADMIN11489 quote:
Setting the DB_RECOVERY_FILE_DEST Parameter
Include the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters in your initialization parameter file to identify the default location for the Fast Recovery Area. The Fast Recovery Area contains:
*
Redo log files or multiplexed copies of redo log files
*
Control files or multiplexed copies of control files
*
RMAN backups (datafile copies, control file copies, backup pieces, control file autobackups)
*
Archived logs
*
Flashback logs
that is what it includes.
A reader, May 25, 2010 - 2:07 pm UTC
I just did a select on the view related to flash recovery area and this is what I got
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 1.8 1.12 55
BACKUPPIECE .02 .01 3
IMAGECOPY 0 0 0
FLASHBACKLOG 54.88 0 3821
6 rows selected.
If you see backuppiece, it shows that it is hardly using anything for my database. So I am still confused on what RMAN backups are included in the flash recovery area.
For your reference, we run backups as follows
backup TAG <tagname> incremental level = 0
format 'filename.dump' cumulative database
include current controlfile
plus archivelog;
Thanks.
May 25, 2010 - 6:34 pm UTC
where do your backups go - disk or tape, look at your channel allocations
A reader, May 26, 2010 - 9:02 am UTC
The backups go to disk
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 10 G;
May 26, 2010 - 9:17 am UTC
where on disk.
A reader, May 26, 2010 - 12:24 pm UTC
The format in the backup command specifies the destination as
/ora/flash/<dbname>/<filename>
where /ora/flash is the flash recovery area specified in DB_RECOVERY_FILE_DEST
May 26, 2010 - 12:37 pm UTC
so, you are just doing normal backups into that flash recovery directory, not using the flash recovery area as a flash recovery area.
To backup with rman to the flashrecovery area, you wouldn't have to tell it where to go, you would set backup optimization on.
http://docs.oracle.com/docs/cd/B19306_01/backup.102/b14192/bkscenar001.htm#sthref781 so, the backups you see there are your backups, but they are not in the flash recovery area as far as rman is concerned. They are just files in directories.
A reader, May 26, 2010 - 12:50 pm UTC
In my environment, backup optimization is already on.
What I am understanding from your previous note is that I have to omit the format option in order for the RMAN backups to participate in the flash recovery area usage ? Is that right ?
May 26, 2010 - 1:00 pm UTC
right, you would not tell us where to put it, we'd put it into the flash recovery area ourselves.
A reader, May 26, 2010 - 1:41 pm UTC
Does db_flashback_retention_target have any connection with db_recovery_file_dest_size. In other words, will the flashback logs be cleared if they are beyond the db_flashback_retention_target or are they tied to the space pressure in the flash recovery area ?
May 26, 2010 - 1:46 pm UTC
we'll use up to file dest size of storage in an attempt to be able to flashback to your retention target time - that is what ties them together.
If we exceed dest size, we'll have to get rid of something we can get rid of and miss your target.
If we are under dest size, we'll keep growing to dest size if necessary to support your target.
A reader, May 26, 2010 - 1:49 pm UTC
I am sorry... I was unclear of what you meant. These are my settings
db_recovery_file_dest_size=200G
db_flashback_retention_target=4320 (which is 72 hrs, I think)
So in this scenario, will the flashback logs start getting deleted when the flash recovery area is close to 200 G and delete any flashback logs older than 72 hrs or be default every 4th day the flashback logs will get deleted ?
May 27, 2010 - 7:06 am UTC
In short:
we will use up to 200gb in order to attempt to allow you to flashback 4,320 seconds, however, if 200gb is not large enough to store that much information - we will store as much as we can in that 200gb in order to flashback as far as possible - attempting to be able to do 4,320 seconds - if we cannot, we'll just use 200gb and you won't be able to flashback 4,320 seconds.
As we get pressured for space - we'll have to clear out space then and there.
Steven, August 17, 2010 - 8:14 am UTC
Hello,
I am on Oracle 10.2.0.4 on Windows and testing my Standby server to see if it can be opened for read/write.
I am using Oracle flashback so that I can revert the Standby back to its original state after opening it for testing.
I have created a flash recovery area (FRA) :
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=10G;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/oracle/flashback';
and created a guaranteed restore point:
CREATE RESTORE POINT Standby_flashback_testing GUARANTEE FLASHBACK DATABASE;
While testing I did a few flashback restore and didn't realized my flash recovery area has reach it's 10GB limit.
My FRA folder ('/u01/oracle/flashback') is still there but since it has reached the 10GB limit, I think the FRA is no longer usable as when I run
select NAME,SCN,TIME from v$restore_point;
it says no rows selected.
Previously I was able to see the restore point "Standby_flashback_testing" when the size was below 10GB.
My question is am I still able to retrieve/restore my flashback and is it true that when the FRA limit is reached, the restore point will be deleted?
Thanks,
Steven, August 19, 2010 - 2:13 am UTC
According to Oracle documentation:
<Quote>
There are two types of restore point:
*Guaranteed restore points: A guaranteed restore point enables you to flash the database back deterministically
to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter setting. The guaranteed ability to flash back depends on sufficient space being available in the flash recovery area.
Guaranteed restore points guarantee only that the database will maintain enough flashback logs to flashback the
database to the guaranteed restore point. It does not guarantee that the database will have enough undo to flashback any table to the same restore point.
Guaranteed restore points must be dropped explicitly by the user using the DROP RESTORE POINT statement. They do not age out. Guaranteed restore points can use considerable space in the flash recovery area. Therefore, Oracle recommends that you create guaranteed restore points only after careful consideration.
<Quote>
--> In the first paragraph it said "The guaranteed ability to flash back depends on sufficient space being available in the flash recovery area".
--> While in the last paragraph it mentioned "Guaranteed restore points must be dropped explicitly by the user using the DROP RESTORE POINT statement. They do not age out".
--> From the above, I take it that even though my FRA was full, it should not delete the restore point.
I have captured additional information (below results are after I noticed my restore point was no longer available):
SQL> show parameters db_recovery
NAME TYPE VALUE
------------------------------------ ----------- --------------------
db_recovery_file_dest string \u01\oracle\flashback
db_recovery_file_dest_size big integer 10G
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------ ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
---------- ---------------
\u01\oracle\flashback
1.0737E+10 0 0 0
I still have the physical flashback files in the FRA folder like O1_MF_66K26XNB_.FLB, O1_MF_66K31CJS_.FLB, etc
The timestamp of the last file in FRA folder is 12.27 which is about the same time I noticed the folder has reached the 10G limit.
In the alert log:
Tue Aug 17 12:16:56 2010
ORA-1669 signalled during: alter database activate standby database...
Tue Aug 17 12:25:10 2010
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 89.13% used, and has 1166671872 remaining bytes available.
Tue Aug 17 12:25:10 2010
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
The only thing I did before opening the standby database was to create a new control file as it was complaining that
the control file is inconsistent.
Thanks!
August 19, 2010 - 3:59 am UTC
... The only thing I did before opening the standby database was to create a new
control file as it was complaining that
the control file is inconsistent.
...
you say that like it is a small "so what" sort of thing? you wiped everything out with that. Restore points are maintained there. Recreating the control files is a "huge" event, not a trivial thing.
Flashback
A reader, March 16, 2011 - 5:59 am UTC
Hi Tom,
We are about to enable flashback recovery area for one of our production 10G databases. Flashback retention is going to be 2 days. At this stage, we keep 3 days worth of archived logs on file system which will go to flash recovery are, once enabled. We are going to test this in test database first. My question is:
(1) If flash recovery area is enabled, do we HAVE to keep database backup on disk under flash recovery area? I have gone through documentation which does not say anything about keeping database backup under flash recovery area is must to flashback database to previous time/SCN. I am quite sure that we just need enough space to keep 2 days worth of flashback logs and 2 days worth of archived logs to be able to meet flashback database requirements for 2 days but still wanted to confirm!
(2) We are going to allocate 100 GB to flash recovery are which I believe will be enough to store required flashback logs and archived logs to meet 2 days flashback requirements. Can you please inform how flashback log will be deleted? I mean if Oracle is able to keep flashback logs and archived logs to meet flash back retention period of 2 days, will the non required flashback logs be deleted by it self OR Oracle will continue keeping flashback logs to the size defined by DB_RECOVERY_FILE_DEST_SIZE and then will purge/overwrite old flashback logs?
(3) If we have dataguard setup, what would be the impact of performing FLASHBACK DATABASE operation on primary database to previous SNC on standby database? Do we need to perform FLASHBACK DATABASE to the same SCN as primary on standby as well?
Thanks for your time.
Cheers,
March 16, 2011 - 8:49 am UTC
(1) no you don't
(2) I believe you mean "archive logs" which we'll be keeping in the fast recovery area (yes, we renamed it from fast). We will not purge them until you've backed them up with rman - they are needed for recovery until then. As for the pre-checkpoint images of blocks we keep for flashback database (your reason for setting the flashback retention policy) will be maintained by us and purged as necessary. Remember the retention is a target - not a mandate - and if your 100gb estimate is too small - we might not be able to keep 2 days of stuff there.
(3)
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2797219000346454510
Flashback
A reader, March 16, 2011 - 5:57 pm UTC
Thanks for the quick reply Tom!
In point (2), I was talking about pre-checkpoint images of blocks (I reffered it as flashback log). So assuming that we have enough space under flash recovery area to meet our flashback retention period of 2 days (archived logs + pre-checkpoint images for 2 days), Oracle will purge these pre-checkpoint images of blocks after 2 days. Is it right?
Thank you again.
March 17, 2011 - 7:57 am UTC
No - not really, we'll get rid of them when we are pressed for space. There is no reason to be immediately proactive and limit you to two days - you gave us the flash recovery area, we'll use it.
Flashback - Data Recovery
bc, March 18, 2011 - 6:15 am UTC
Tom,
How do I convince someone that says "Flashback query is an administrative tool for data recovery and that does not guarantee restore points nor data recovery.
Used for application architecture and design is not a best practice.", that Flashback Query is the way to go when trying to track changes in a record instead of table level triggers.
March 18, 2011 - 8:17 am UTC
Flashback Query is sort of what they say.
However, there is
o UNDO based flashback query/flashback versions query - has a theoretical 5 day limit. You CAN guarantee the ability to flashback for some duration of time (since 10g) - so you are wrong about not being able to guarantee.
o recycle bin based flashback table - used for whoops operations, definitely something to know about if you need it
o UNDO based flashback table - same limits as UNDO based flashback query - it CAN be guaranteed
o Flash Recovery Area based flashback database - put the entire database back the way it was. Can use a restore point. CAN ABSOLUTELY BE GUARANTEED.
o Flashback Data Archive based flashback query/flashback versions query - long term data retention. Long term GUARANTEED data retention - for months or years.
Probably you start by telling them "no, I'm not talking about flashback query, I'm talking about the flashback data archive - totally different. This is not reliant on undo information - it is completely different"
and then send them to:
http://www.oracle.com/us/products/database/options/total-recall/index.html
Alexander, March 18, 2011 - 8:46 am UTC
Recently on this site I saw you give an example of using a restore point and using flashback query to go to it. I thought the restore points were only for flashback database; How does that work as far as undo block management? Since undo is aged out as needed, does Oracle hold on to the undo information for as long is the restore point exists (if it's guaranteed)?
March 18, 2011 - 10:45 am UTC
A restore point just remembers a "time", an "SCN" - and maybe enables flashback database if it wasn't already.
Flashback database queries up that SCN when you flashback to a restore point and really does an SCN based flashback.
Flashback query queries up that SCN....
It is a guaranteed restore point, but not necessarily a guaranteed flashback to point - you'd set that up via the retention guarantee
http://docs.oracle.com/docs/cd/E11882_01/server.112/e17120/undo002.htm#ADMIN11463
Flashback - Data Recovery
bc, March 18, 2011 - 9:50 am UTC
Thank you so much, your response is highly informative and appreciated.
Alexander, March 18, 2011 - 1:54 pm UTC
Isn't a restore point only relevant to flashback though? In which a guaranteed restore point would translate to a guaranteed flashback point? I don't ever recall seeing an example of a database restore using restore points.
March 18, 2011 - 2:00 pm UTC
Yes, it is relevant to flashback - flashback database and as demonstrated flashback query.
But it wouldn't be a guaranteed flashback QUERY point. It would be guaranteed for flashback DATABASE.
I don't ever
recall seeing an example of a database restore using restore points.
that is it's major use - for flashback database. Set a restore point, do something big/life changing like upgrade your application, upgrade the database, decide to undo that life changing event via flashback database.
http://docs.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_6011.htm#SQLRF20001 The guarantee is explicitly and exclusively for flashback database!
the documentation actually says:
Guaranteed restore points guarantee only that the database will maintain enough flashback logs to flashback the database to the guaranteed restore point. It does not guarantee that the database will have enough undo to flashback any table to the same restore point.
Alexander, March 18, 2011 - 2:28 pm UTC
"that is it's major use - for flashback database."
I know, I said database restore as in
restore database;
recover database;
I got it now originally I didn't really see the point of using a restore point for flashback query like your example. It is a lot cleaner than having to type out an until time though.
Flashback queries
A reader, July 11, 2011 - 10:40 pm UTC
Hi Tom,
You said in the above section: "For this, we find the "newest" pre-checkpoint image of that block in the flash recovery area that is older than 9am (eg: presume there could be an 8am, 8:30am, 9:01am, 9:30am and so on copy of that block in the flash recovery area - we would go for the 8:30am version)."
But I thought purpose to flashback is just not about a single block, we would have to do it for multiple blocks. Now lets say multiple tables got deleted at a time and we want to flashback database to that prior time.
Now the blocks of those tables got retrieved from particular flashback log(s) and archived log(s); but what about the rest of the blocks of the database? How they will have their prior images? Should Oracle be applying all the flashback logs for all of the blocks close to that time?
July 13, 2011 - 1:35 pm UTC
it would do that process FOR ALL BLOCKS that were checkpointed since 9am of course.
It does this block by block - so for every block checkpointed since 9am, we would "For this, we find the "newest" pre-checkpoint image of that block in the flash recovery area that is older than 9am (eg: presume there could be an 8am, 8:30am, 9:01am, 9:30am and so on copy of that block in the flash recovery area - we would go for the 8:30am version)."
that should have been clear since the sentence right before for "for this" one was:
The blocks in B) however are problematic, we need to put them back to a point in time prior to 9am this morning.
the BLOCKS (plural)
we need to put THEM (plural)
Flashback database queries
A reader, July 13, 2011 - 1:54 pm UTC
Dear Tom,
I am new to this part of the world. So I would like to ask you:
What do you mean by "processing all blocks checkpointed since 9am"? Will it be overwriting all the blocks (which are checkpointed close to 9am) over current blocks?
And also what do you mean be a nearest pre-checkpoint image?
July 15, 2011 - 8:48 am UTC
with flashback database, our goal is to put the database back the way it was bit for bit - as of a prior point in time.
In order to understand how we do that - you need to understand a bit about checkpointing. Every now and then - DBWR will flush dirty (modified) blocks from the cache onto disk. It is this set of blocks (all of the blocks flushed during the period of time you want to flashback over) that we need to "un-checkpoint" - to restore them to a point in time BEFORE the time you want to flashback to.
So, let's say it is 9am right now... The database is in some state - but we know that all of the blocks on disk are less than or equal to 9am as far as "time" goes. In the flash recovery area, we will also have versions of blocks that predate 9am - older images (these are the pre-checkpoint images I was referring to - images of the blocks from before some prior checkpoint of those blocks).
Now, time moves on - it is 10am. during the last hour - some blocks were written to disk - they are now older than 9am. If any of the blocks written to disk DID NOT HAVE AN OLD COPY in the flash recovery area - we would have placed an old copy in there before overwriting it. Many of the blocks we checkpointed from 9am-10am might already have had an old image in the flash recovery area - if they did - we probably didn't copy over a pre-checkpoint image for those blocks (we might have- we keep multiple copies as of different points in time over there).
Now, at 10am you decide to flashback the database to 9am. What we have to do is
a) identify all blocks in the current set of database files that were checkpointed between 9am and 10am.
b) goto the flash recovery area and find the NEWEST block whose time is less than or equal to 9am.
c) take those versions of the blocks from the flash recovery area and put them over the block images we identified in (a)
d) using the archive and perhaps online redo log files - roll forward any block images that need rolling forward to bring them up to date - to bring them up to 9am.
related to flash back recovery
A reader, June 14, 2012 - 2:47 am UTC
Hi Tom,
This is related to flash back recovery.
I am using oracle 11g (11.2) in windows 2008 server and having some problem to move db_recovery_file_dest to windows mapped drive. It is failing with following errors.
SQL> startup nomount pfile="E:\odbfs\u00\app\oracle\admin\ipca\scripts_ASM\init.ora";
ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
ORA-01263: Name given for file destination directory is invalid
OSD-04018: Unable to access the specified directory or device.
O/S-Error: (OS 3) The system cannot find the path specified.
But when i create database using local drive it works without any problem.
Now i have to move my local backup directory to F (mapped drive) - i know it will fail with same error.
Do i need to do anything in oracle side so that it can understand F network drive is available ( I had already given all permission to this domain user to that drive).
I think you had already came across this problem. your suggestion will be appreciated.
I followed metalink ID 145843.1 also but no luck.
Regards,
Jeetendra.
June 14, 2012 - 5:50 am UTC
do you understand how windows "network drives" work? It is not as straightforward and easy to use as a Unix network drive
Just because YOU can see a network drive does not mean that the service that is Oracle can see it. Windows is not a multi-user operating system like Unix. That F: drive - you can see it but that does not mean that anyone else can.
it would be not really advisable to use network drives for a *database* anyway (unless it is like an NFS filer or something).
I don't really use windows for anything other than running virtual machines that run linux personally.
You'll probably need to use the UNC name for the file system so the service that is Oracle can find the resource - but I'll refer you to support for this one, I really do not touch windows for running databases. Just for running my laptop ;)
related to flash back recovery
Jeetendra, June 14, 2012 - 2:49 am UTC
Hi Tom,
This is related to flash back recovery.
I am using oracle 11g (11.2) in windows 2008 server and having some problem to move
db_recovery_file_dest to windows mapped drive. It is failing with following errors.
SQL> startup nomount pfile="E:\odbfs\u00\app\oracle\admin\ipca\scripts_ASM\init.ora";
ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
ORA-01263: Name given for file destination directory is invalid
OSD-04018: Unable to access the specified directory or device.
O/S-Error: (OS 3) The system cannot find the path specified.
But when i create database using local drive it works without any problem.
Now i have to move my local backup directory to F (mapped drive) - i know it will fail with same
error.
Do i need to do anything in oracle side so that it can understand F network drive is available ( I
had already given all permission to this domain user to that drive).
I think you had already came across this problem. your suggestion will be appreciated.
I followed metalink ID 145843.1 also but no luck.
Regards,
Jeetendra.
A reader, September 24, 2013 - 8:14 pm UTC
Hi - We have a 11g database on Solaris OS. Have some questions on the flash recovery area. We have set the db_flashback_retention_target to 4320 which is 3 days. But when we check the flashback logs, they go back beyond 3 areas. Why arent they getting deleted based on the retention ?