ENABLE ARCHIVELOG AND FLASHBACK IN RAC DATABASE

Step by step process of putting a RAC database in archive log mode and then enabling the flashback Database option.

Enabling archive log in RAC Database:

A database must be in archivelog mode before enabling flashback.

In this example database name is test and instances name are test1 and test2.

step 1:

creating recovery_file_dest in asm disk

SQL> alter system set db_recovery_file_dest_size=200m sid='*';

System altered.

SQL> alter system set db_recovery_file_dest='+DATA' sid='*';

System altered.

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Current log sequence 14
SQL>

step 2:

set the LOG_ARCHIVE_DEST_1 parameter. since these parameters will be identical for all nodes, we will use sid='*'. However, you may need to modify this for your situation if the directories are different on each node.

SQL> alter system set log_archive_dest_1='LOCATION=USE-DB_RECOVERY_FILE_DEST';

System altered.

step 3:

set LOG_ARCHIVE_START to TRUE for all instances to enable automatic archiving.

SQL> alter system set log_archive_start=true scope=spfile sid='*';

System altered.
Note that we illustrate the command for backward compatibility purposes, but in oracle database 10g onwards, the parameter is actually deprecated. Automatic archiving will be enabled by default whenever an oracle database is placed in archivelog mode.

step 4:

Set CLUSTER_DATABASE to FALSE for the local instance, which you will then mount to put the database into archivelog mode. By having CLUSTER_DATABASE=FALSE, the subsequent shutdown and startup mount will actually do a Mount Exclusive by default, which is necessary to put the database in archivelog mode, and also to enable the flashback database feature:

SQL> alter system set cluster_database=false scope=spfile sid='test1';

System altered.

step 5;
Shut down all instances. Ensure that all instances are shut down cleanly:

SQL> shutdown immediate

step 6:
Mount the database from instance test1 (where CLUSTER_DATABASE was set to FALSE) and then put the database into archivelog mode.

SQL> startup mount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Database mounted.

SQL> alter database archivelog;

Database altered.

NOTE:
If you did not shut down all instances cleanly in step 5,
putting the database in archivelog mode will fail
with an ORA-265 Error.

SQL> alter database archivelog;
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

step 7:
Confirm that the database is in archivelog mode, with the appropriate parameters, by issuing the ARCHIVE LOG LIST command:

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE-DB_RECOVERY_FILE_DEST
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15

step 8
Confirm the location of the RECOVERY_FILE_DEST via a SHOW PARAMETER.


SQL> show parameter recovery_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DATA
db_recovery_file_dest_size big integer 200M

Step 9:
Once the database is in archivelog mode, you can enable flashback while the database is still mounted in Exclusive mode (CLUSTER_DATABASE=FALSE).

SQL> alter database flashback on;

Database altered.

Step 10:
Confirm that Flashback is enabled and verify the retention target:

SQL> select flashback_on,current_scn from v$database;

FLASHBACK_ON CURRENT_SCN
------------------ -----------
YES 0

SQL> show parameter flash

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440

step 11:
Reset the CLUSTER_DATABASE parameter back to TRUE for all instances:

SQL> alter system set cluster_database=true scope=spfile sid=' * ';

System altered.

step 12:
shutdown the instance and then restart all cluster database instances.
All instances will now be archiving their redo threads.

SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

start the database, using srvctl command or normal startup

[root@rac1 bin]# ./srvctl status database -d test
Instance test1 is not running on node rac1
Instance test2 is not running on node rac2

[root@rac1 bin]# ./srvctl start database -d test

[root@rac1 bin]# ./srvctl status database -d test
Instance test1 is running on node rac1
Instance test2 is running on node rac2
[root@rac1 bin]#

on test1 instance:

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE-DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
SQL>

on test2 instance:

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE-DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL>

wow, both are in archive log mode

hope, this document will help you .

regards,
rajeshkumar g