First we need to make sure that:
- Our RAC database is running in ARCHIVELOG mode. This can be checked using:
SQL> select log_mode from v$database
- The database is using some kind of shared storage, preferably ASM.
In the statements below a diskgroup of ASM is used. Remember to use the '+' sign in front of the diskgroup name, like '+FB01'.
Now we need to set the required parameters in the spfile to support the flashback function:
SQL> alter system set db_recovery_file_dest_size=20G scope=spfile sid='*';
SQL> alter system set db_recovery_file_dest='
SQL> alter system set db_flashback_retention_target=
The value for represents a value for which you want to be able to keep data. This value is ofcourse restricted by the available diskspace. The default is 1440 minutes.
Now we need to shutdown all instances of the RAC database. This means the complete cluster will be down during this action. If the database name is RACDB then the statement to shutdown the database looks like:
$ srvctl stop database -d RACDB
Start on one of the nodes an instance in mount mode:
SQL> startup mount;
Then turn flashback on using the command:
SQL> alter database flashback on
Verify the setting with the below SQL:
SQL> select log_mode, flashback_on from v$database
Now shutdown the instance again and restart the cluster database:
SQL> shutdown immediate
$ srvctl start database -d RACDB
Remember that a shutdown of the database als may have stopped the resources for the transparant application failover. They then need to be restarted also again.
This concludes the enabling of flashback on RAC.
Bug 7334226: RAC instance crash possible with FLASHBACK on
Please look up Note 7334226.8 on the Oracle Support site.
It states that all RAC versions up to 188.8.131.52 suffer from possible instance crashes if flashback is enabled. The issue is fixes in patch set update 184.108.40.206.2. and in the 220.127.116.11 base-release