Database flashback is a new feature in 10g that is a continuation of flashback features introduced back in 9i release.
In 9i flashback uses undo tablespace while the new database flashback feature uses a disk area referred to Flashback Recovery Area (FRA).
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) Add the new name control file to control_files parameter in spfile.
Alter system set control_files = ‘your old existing control file’, ‘your new control file’ scope=spfile sid=’*';
K) 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
