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.
alter system set db_recovery_file_dest='/u01/ora_stage/fra/dbr1' scope=both ;
System altered.
Sql+( SYS_at_canon)> show parameter db_recovery
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.
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
alter system set db_recovery_file_dest='/u01/ora_stage/fra/dbr2' scope=both ;
System altered.
Sql+( SYS_at_canon)> show parameter db_recovery
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
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
Fred Habash, Database Solutions Architect (Oracle OCP 8i,9i,10g,11g)
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
- How to change Flash Recovery Area to a new location ? (Doc ID 305651.1)
- 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