11gR2 Flashback Guaranteed Restore Point without enabling Flashback logging

Flashback PicturePlenty has been documented and published regarding flashback database since the first implementation in Oracle 10g. However little is documented regarding using flashback restore points in Oracle when the flashback logging (flashback off) is not enabled.

At a client site flashback logging was turned off as a feature due to performance implications, in 11gR2 it is a dynamic online feature that you can now turn flashback on and off without a database bounce. A situation arose and questions were asked regarding the use of guaranteed restore points and flashback logging for  testing purposes.

For reference this paper gives a short summary and example of the Oracle documentation:

Download The Full PDF here: 11gR2_Flashback_Guaranteed_Restore_Point_without_enabling_Flashback_logging.pdf

Comments:

Hi,

Nice Desription . Would it require more undo space. How would it affect physical standby if flashed back to restore point.

Thanks
GP Singh

Posted by guest on April 17, 2013 at 09:23 PM EST #

Hi thanks for your comment. To answer the first question on undo space, the short answer is NO.

Remember this is flashback database and not a type of flashback relying on undo. The purpose of the article was to generate thoughts on the fact that firstly flashback logging is a performance / management overhead and secondly that to use flashback with a guaranteed restore point allowing flashback to a set point in time does not require flashback logging even to be on. Most DBA's don’t consider that fact!

To answer your second question on data guard, the answer really depends on the current scn of the standby.

There are a lot of examples out there and it should be remembered that since 10g oracle has been able to recover through reset logs which obviously occur in a flashback operation once completed.

An Example set of commands to try out:

1. On primary database, Stop dataguard broker.

SQL> alter system set dg_broker_start = false;

2. Flashback database to SCN

SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to SCN ; -- Could also be a restore point etc
SQL> alter database open resetlog;
SQL> alter system set dg_broker_start = true;

3. Determine the SCN before the RESETLOGS operation occurred.

SQL> SELECT TO_CHAR(RESETLOGS_CHANGE# - 2) FROM V$DATABASE;
-- should be same as earlier.

4. Obtain the current SCN on the standby database.

SQL> SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;

5. Determine if it is necessary to flash back the standby database.

If the value of CURRENT_SCN is larger than the value of resetlogs_change# - 2(), issue the following statement to flash back the standby database.

SQL> FLASHBACK STANDBY DATABASE TO SCN resetlogs_change# -2;

•If the value of CURRENT_SCN is less than the value of the resetlogs_change# - 2, skip to Step 6.

6. Restart Redo Apply.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Posted by Andy Baker on April 17, 2013 at 10:47 PM EST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

About Me Image
Andy Baker, Senior Principal Consultant for Oracle Consulting Services (@Bakers_byte), shares his news, views and ideas about the Oracle Database with a focus on innovation and emerging technologies.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today