Relocating Flashback Recovery Area While A Guranteed Restore Point Is Present

From: Fred Habash <fmhabash_at_gmail.com>
Date: Fri, 11 Apr 2014 10:34:52 -0400
Message-ID: <CADpeV5zha-L1DD7UDDabw_TzQmwzgfaGXUiVcypZOvM=FUoy9A_at_mail.gmail.com>

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_at_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_at_canon)> alter database flashback on; Database altered.
Sql+( SYS_at_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_at_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)

--

http://www.freelists.org/webpage/oracle-l Received on Fri Apr 11 2014 - 16:34:52 CEST