Relocating Flashback Recovery Area While A Guranteed Restore Point Is Present
- From: Fred Habash <fmhabash@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 11 Apr 2014 10:34:52 -0400
The official oracle docs (UG & MOS) asks that flashback is toggled (and GRP
recreated) if db_recovery_file_dest is changed.
However, my tests on 11.2.0.4.0 shows that this is not needed. I saw that
Oracle produced the flb files to the new location and alert log did not
complain.
Have you done this before? If yes, what was your experience?
References
----------------------------------------
1) How to change Flash Recovery Area to a new location ? (Doc ID 305651.1)
2) Space Usage Figures in V$flash_recovery_area_usage Misleading When FRA
Location is Changed (Doc ID 460145.1)
Case study
----------------------------------------
Setup FRA to dir dbr1
#########################
alter system set db_recovery_file_dest='/u01/ora_stage/fra/dbr1' scope=both
;
System altered.
Sql+( SYS@canon)> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
db_recovery_file_dest string /u01/ora_stage/fra/dbr1
db_recovery_file_dest_size big integer 1G
Enable FB & GRP
#########################
Sql+( SYS@canon)> alter database flashback on;
Database altered.
Sql+( SYS@canon)> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK
DATABASE;
Restore point created.
Verify flb files are generated
#########################
dbr1/canon/flashback:
total 205072
-rw-r----- 1 oracle oinstall 52436992 Apr 11 09:14 o1_mf_9nhxyr12_.flb
-rw-r----- 1 oracle oinstall 52436992 Apr 11 09:14 o1_mf_9nhxyv7b_.flb
-rw-r----- 1 oracle oinstall 52436992 Apr 11 09:14 o1_mf_9nhy6rnf_.flb
-rw-r----- 1 oracle oinstall 52436992 Apr 11 09:14 o1_mf_9nhy6978_.flb
myhost:oracle:11.2.0.4:no_sid> ls -ltrR dbr2
Repoint FRA to dir dbr2
#########################
alter system set db_recovery_file_dest='/u01/ora_stage/fra/dbr2' scope=both
;
System altered.
Sql+( SYS@canon)> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
db_recovery_file_dest string /u01/ora_stage/fra/dbr2
db_recovery_file_dest_size big integer 1G
Verify flb files are not created in new dir dbr2
#########################
myhost:oracle:11.2.0.4:no_sid> ls -ltrR dbr2
...
dbr2/canon/flashback:
total 256340
-rw-r----- 1 oracle oinstall 52436992 Apr 11 09:18 o1_mf_9nhybs0f_.flb
-rw-r----- 1 oracle oinstall 52436992 Apr 11 09:18 o1_mf_9nhyc89s_.flb
-rw-r----- 1 oracle oinstall 52436992 Apr 11 09:19 o1_mf_9nhygdd9_.flb
-rw-r----- 1 oracle oinstall 52436992 Apr 11 09:19 o1_mf_9nhyfzz2_.flb
-rw-r----- 1 oracle oinstall 52436992 Apr 11 09:19 o1_mf_9nhygrpx_.flb
Verify alert log is clean
#########################
Fri Apr 11 09:16:13 2014
ALTER SYSTEM SET db_recovery_file_dest='/u01/ora_stage/fra/dbr2' SCOPE=BOTH;
Fri Apr 11 09:16:50 2014
Thread 1 cannot allocate new log, sequence 152
Checkpoint not complete
Current log# 1 seq# 151 mem# 0: /u01/ora_stage/oradata/canon/redo01.log
Thread 1 advanced to log sequence 152 (LGWR switch)
Current log# 2 seq# 152 mem# 0: /u01/ora_stage/oradata/canon/redo02.log
Fri Apr 11 09:16:52 2014
Archived Log entry 11 added for thread 1 sequence 151 ID 0x5acbddcc dest 1:
Thread 1 cannot allocate new log, sequence 153
Checkpoint not complete
Current log# 2 seq# 152 mem# 0: /u01/ora_stage/oradata/canon/redo02.log
Fri Apr 11 09:17:01 2014
Thread 1 advanced to log sequence 153 (LGWR switch)
Current log# 3 seq# 153 mem# 0: /u01/ora_stage/oradata/canon/redo03.log
Fri Apr 11 09:17:01 2014
Archived Log entry 12 added for thread 1 sequence 152 ID 0x5acbddcc dest 1:
Thread 1 cannot allocate new log, sequence 154
Checkpoint not complete
Current log# 3 seq# 153 mem# 0: /u01/ora_stage/oradata/canon/redo03.log
Thread 1 advanced to log sequence 154 (LGWR switch)
Current log# 1 seq# 154 mem# 0: /u01/ora_stage/oradata/canon/redo01.log
--
Thank you ...
----------------------------------------
Fred Habash, Database Solutions Architect (Oracle OCP 8i,9i,10g,11g)
Other related posts:
