Database Flashback (FRA) Configuration and Tips for RAC with ASM

Database flashback is a new feature in 10g that is a continuation of flashback features introduced back in 9i release.

Reasons to Enable Database Flashback

If flashback is set up correctly, you can perform both complete and incomplete recovery faster and much easier especially when it comes to incomplete recovery (point-in-time recovery).

Also, some sites for the purpose of training or development, they need an earlier copy of the database. This now can be accomplished with 1 line of SQL code.

Design Goal

To get the following files in the FRA and keep flashback logs for 3 days:

  1. Flashback logs. New type of files.
  2. A copy of control file.
  3. Add log file members log groups.
  4. Archive logs.
  5. Enable block change tracking (BCT).
  6. Autobackups of control file.

Database Flashback Implementation

All steps are implemented on 10.2.0.4 64-bit 2-node RAC with ASM

1)  Enabling Flashback

SQL> alter system set db_recovery_file_dest_size = ‘135G’ scope=both sid=’*’;

System altered.

SQL> alter system set db_recovery_file_dest = ‘+ARCDG’ scope=both sid=’*’;

System altered.

SQL>
SQL> alter system set db_flashback_retention_target = 4320 scope=both sid=’*’;

System altered.

At this point there are no flashback logs being send to FRA. You must enable flashback on database level. Steps are very close to enabling archivelog mode in RAC database (but no exactly the same)

Stop all instances in the RAC environment

srvctl stop database -d <service name>

Start one instance only in mount state:

SQL> startup mount

ORACLE instance started.

Total System Global Area 2634022912 bytes

Fixed Size 2058496 bytes
Variable Size 1040189184 bytes
Database Buffers 1577058304 bytes
Redo Buffers 14716928 bytes
Database mounted.

SQL>
SQL> alter database flashback on;

Database altered.

SQL>
SQL> alter database open;

Database altered.

SQL> exit

— start second instance

srvctl start instance -d <service name> -i <instance name>

At this point, flashback logs should start getting generated from both RAC instances to FRA.

2) A Copy of Control File

Duplicating a control file in ASM is a bit complicated and unfortunately requires downtime of all instances.

Here are the steps:

A) Backup your pfile (optional step)

create pfile=’/u02/oracle/admin/sid/pfile/initsid.ora’ from spfile;

B) Add to control file parameter in spfile the diskgroup for FRA. In our case it’s ARCDG.

alter system set control_files = ‘your old existing control files’, ‘+ARCDG’ scope=spfile sid=’*’;

C) Backup controlfile (optional step)

alter database backup controlfile to trace;

D) Shutdown all instances

srvctl stop database -d <your database>

E) Startup a single instance in nomount

startup nomount

F) Connect to rman

rman nocatalog
connect target /

G) Create new copy from existing control file

restore controlfile from ‘ your old existing control file including full path’;

H) mount and open your instance. Check for newly created control files.

Select name from v$controlfile;

J) Bounce your databases for verification


srvctl stop database -d <your service name>
srvctl start database -d <your service name>

3) Add Log Members to Log File Groups

In order for FRA to recognize your new log files, you would have to add new log file groups and drop the old ones.

Column IS_RECOVERY_DEST_FILE under gv$logfile must be “YES” for log members under FRA disk group. This is the only way FRA will identify log files existence in v$flash_recovery_area_usage

Add a new log group, 1 member will be under FRA, and another member in a different disk group.


alter database add logfile thread <n> size 100m;

After finish adding new groups, drop the old ones. They are no longer needed.

Note: All new log files added will show status ‘Invalid’ (v$logfile) until they are used. Nothing to worrying about.

4) Archive Logs

Sending archive logs to FRA.

alter system set log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST’ scope=both sid=’*’;

5) Block Change Tracking

This is an uptime procedure. It will create about an 11 MB file initially.

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘+ARCDG’;

6) Control File Auto Backup

Control file auto backup to take place whenever a change is made to the control file. Execute only once from 1 instance.

rman nocatalog

rman> connect target /

CONFIGURE CONTROLFILE AUTOBACKUP ON;

You might also need to execute the following statement to redirect control file auto backups to FRA.

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

Verification


Select file_type, percent_space_used, number_of_files
from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED NUMBER_OF_FILES
CONTROLFILE 0.05 1
ONLINELOG 1.74 6
ARCHIVELOG 10.91 73
BACKUPPIECE 0.41 9
IMAGECOPY 0 0
FLASHBACKLOG 7.67 167

You can see size and number of files for every component we configured.

Hazem Ameen
Senior Oracle DBA

Advertisements

2 thoughts on “Database Flashback (FRA) Configuration and Tips for RAC with ASM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s