AskDba.org Weblog

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

<span style="font-family: arial,helvetica,sans-serif; font-size: small;">ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 20G SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+DG1' SCOPE=BOTH;</span>

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

<span style="font-family: arial,helvetica,sans-serif; font-size: small;">[/home/oracle&gt;srvctl stop service -d </span><span><span style="font-family: arial,helvetica,sans-serif; font-size: small;">TESTDB</span></span><span style="font-family: arial,helvetica,sans-serif; font-size: small;">
[/home/oracle&gt;srvctl stop database -d TESTDB</span>

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.

<span style="font-family: arial,helvetica,sans-serif; font-size: small;">/home/oracle&gt;sqlplus "/ as sysdba"

Connected to an idle instance.

SQL&gt; 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&gt; alter system set cluster_database=false scope=spfile;

System altered.

SQL&gt; shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL&gt; exit
SQL&gt; 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&gt;alter database archivelog;
SQL&gt; alter database flashback on;

Database altered.
</span>

Set the Cluster_database parameter again to true.

<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL&gt;  alter system set cluster_database=true scope=spfile;
System altered.
SQL&gt;shutdown immediate</span>

We will again use srvctl to start the database and associated service

<span style="font-family: arial,helvetica,sans-serif; font-size: small;">[/home/oracle&gt;srvctl start database -d TESTDB
[/home/oracle&gt;srvctl start service -d </span><span><span style="font-family: arial,helvetica,sans-serif; font-size: small;">TESTDB</span></span><span style="font-family: arial,helvetica,sans-serif; font-size: small;">
</span>

We can confirm whether Archivelog mode and Flashback is enabled by querying V$DATABASE

<span style="font-family: arial,helvetica,sans-serif; font-size: small;">SQL&gt; SELECT LOG_MODE,FLASHBACK_ON FROM V$DATABASE;
LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   YES
</span>