Setting up Flash Recovery Area (FRA) and flashback db

Oracle 10g - Setting up Flash Recovery Area (FRA) / flashback database


1. Ensure database is in archivelog mode


SELECT LOG_MODE FROM V$DATABASE



2. Create 'FRA' directory on appropriate mountpoint - mkdir fra



3. Set FRA size


ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE= SCOPE=SPFILE;


4. Set FRA location


ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=SPFILE;


5. Bounce the database


SHUTDOWN IMMEDIATE (wait for shutdown)


STARTUP


6. Check parameters have changed


SHOW PARAMETER RECOVERY


NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string /data/example/oracle/SID/oradata_06/fra

db_recovery_file_dest_size big integer 3G


7. Once parameters have been checked, shutdown the database and startup in mount state


SHUTDOWN IMMEDIATE (wait for shutdown)


STARTUP MOUNT


8.Turn flashback on


ALTER DATABASE FLASHBACK ON


9. Open the database


ALTER DATABASE OPEN


10. Check flashback is on


SELECT FLASHBACK_ON FROM V$DATABASE


FLASHBACK_ON

------------------

YES


11. Test flashback database – BE AWARE, A TEST WILL REQUIRE AN 'ALTER DATABASE OPEN RESETLOGS' COMMAND


- sqlplus '/ as sysdba'


- set time on


- Create a table (flash_thirupal for example)

create table flash_thirupal (

a number,

b varchar2(10)

c varchar2(40)

)

tablespace SYSTEM

- Insert some records into the test table


INSERT INTO flash_test VALUES (1, 'ABC','THIRUPAL');

INSERT INTO flash_test VALUES (2, 'DEF','BOREDDY');


- Commit the changes and note the time


- SELECT * FROM FLASH_TEST - should output the records just inserted


- shutdown immediate

- startup mount



- Flashback the database to a time before the table was created –


FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2008-07-24 10:00:00', 'YYYY - MM-DD HH24:MI:SS')

- ALTER DATABASE OPEN RESETLOGS

- SELECT * FROM FLASH _TEST - table should not exist


How far can I flash the database back?


Below is a useful bit of script for finding out the date and time the database can be flashed back to, the retention target (mins), the size of the current flashback area (mb) and the estimated amount of flashback logs needed to meet the retention target (mb).

Set line 160


select to_char(oldest_flashback_time, 'dd/mm/yyyy hh24:mi:ss') oldest_time, retention_target,
sum((flashback_size)/1024/1024) flashback_size_mb,
sum((estimated_flashback_size)/1024/1024) estimated_flashback_size_mb
from v$flashback_database_log
group by to_char(oldest_flashback_time, 'dd/mm/yyyy hh24:mi:ss'), retention_target



This will also indicate whether space needs to be added to the flashback area in order to perform 'flashback database' to the full extent of the retention target. For example, if your retention target is 24 hours and the 'oldest_time' is only 10 hours ago, you will need to add space to the fra or reduce the retention target.


Example of output…
OLDEST_TIME RETENTION_TARGET FLASHBACK_SIZE_MB ESTIMATED_FLASHBACK_SIZE_MB
------------------- ---------------- ----------------- ---------------------------
13/08/2008 21:02:01 360 2675.75 1095.2168


In the example output above it is showing that the database can be flashed back to 13/08/08 at 21:02pm, the retention target is 6 hours, the amount of flashback data currently in the FRA is 2675mb and the estimated amount of flashback data we need to flashback the database 6 hours is 1095mb. These figures mean it is possible to perform flashback database and recover the database to the defined target, and if necessary it can be taken back even further (current date/time is 14/08/08 9:30am so it can be flashed back 12.5 hours approx.)



Viewing FRA stats
set line 300

select to_char(begin_time, 'dd/mm/yyyy hh24:mi:ss') begin_time, to_char(end_time, 'dd/mm/yyyy hh24:mi:ss') end_time,
sum((flashback_data)/1024/1024) flash_data_mb, redo_data as redo_logs_generated, db_data, sum((estimated_flashback_size)/1024/1024) estimated_flashback_size_mb
from v$flashback_database_stat
group by to_char(begin_time, 'dd/mm/yyyy hh24:mi:ss'), to_char(end_time, 'dd/mm/yyyy hh24:mi:ss'), db_data, redo_data
order by begin_time desc

This script outputs certain stats about the FRA which are broken down on an hour-to-hour basis (approximately). The stats are:

- The time interval the stats relate to (beginning and end time)

- The amount of flashback data written (mb)

- The amount of redo logs generated (bytes)

- Amount of database data read and written during the interval (bytes)

- The estimated flashback size (as in the 'How far can I flashback' section)


The example output shows there is a significantly higher amount of flashback data being written to the FRA between 22:32 and 23:32 compared to 2:32 and 3:32 – this kind of information can be useful when configuring your FRA and when reviewing sizes and retention targets.


Information relating to RAC systems

To enable flashback database in a RAC database environment, the database must be started in exclusive before flashback database is enabled. To do so, perform the following steps via SQL*PLUS:

1. Disable clustering:

SQL> alter system set CLUSTER_DATABASE=FALSE scope=spfile sid='';

2. Shutdown immediate all instances

3. From instance where clustering is disabled, mount the database:

SQL> Startup mount;

4. If database is NOT in archivelog mode, issue:

SQL> alter database archivelog;

5. Turn on flashback database:

SQL> alter database flashback on;

6. Reenable clustering:

SQL> alter system set CLUSTER_DATABASE=TRUE scope=spfile sid='';

7. Shutdown database:

SQL> shutdown immediate;

8. Open all the RAC database instances