Enabling Flashback /Archive Log mode on a Single Instance Database is quite straight forward. In case of RAC, you need to follow additional steps.
The requirements for enabling Flashback Database are:
- Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation.
- You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.
- For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.
First of all configure flash recovery area by setting db_recovery_file_dest_size and db_recovery_file_dest
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 20G SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+DG1' SCOPE=BOTH;
We are using ASM Diskgroup here which is sharable and available to both the nodes.Next step is to enable archivelog mode and then to turn on flashback. To perform this, database needs to be in mount mode.
We can use srvctl to disable any associated Database service and then stop the Database
[/home/oracle>srvctl stop service -d TESTDB [/home/oracle>srvctl stop database -d TESTDB
Now set Cluster_database=false for enabling the Archivelog mode. This is a additional step which is required in RAC Database. For Single Instance, we do not require it.
/home/oracle>sqlplus "/ as sysdba"
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1271564 bytes
Variable Size 314575092 bytes
Database Buffers 750780416 bytes
Redo Buffers 7114752 bytes
SQL> alter system set cluster_database=false scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> exit
SQL> startup mount
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1271564 bytes
Variable Size 314575092 bytes
Database Buffers 750780416 bytes
Redo Buffers 7114752 bytes
Database mounted.
SQL>alter database archivelog;
SQL> alter database flashback on;
Database altered.
Set the Cluster_database parameter again to true.
SQL> alter system set cluster_database=true scope=spfile;
System altered.
SQL>shutdown immediate
We will again use srvctl to start the database and associated service
[/home/oracle>srvctl start database -d TESTDB [/home/oracle>srvctl start service -d TESTDB
We can confirm whether Archivelog mode and Flashback is enabled by querying V$DATABASE
SQL> SELECT LOG_MODE,FLASHBACK_ON FROM V$DATABASE;
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG YES
