Enable Archiving and Flashback in a RAC Database
Step 1. Create the local directories on each node needed for the nonshared (private) archive destination. In this example, ORACLE_BASE is set to /u01/app/oracle. Run these commands on each node:
[oracle@rmsclnxclu1 oracle]$ mkdir -p $ORACLE_BASE/test/archive
Step 2. Set the LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2 parameters. 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.
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
SCOPE=SPFILE SID='*'
System altered.
alter system set log_archive_dest_2='LOCATION=/u01/app/oracle/oradata/test/archive' SCOPE=SPFILE SID='*' ;
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, the parameter is actually deprecated. Automatic archiving will be enabled by default whenever an Oracle Database 10g 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
Database closed.
Database dismounted.
ORACLE instance shut down.
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
ORACLE instance started.
Total System Global Area 655434464 bytes
Fixed Size 455392 bytes
Variable Size 125829120 bytes
Database Buffers 528482304 bytes
Redo Buffers 667648 bytes
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:
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 1281
Next log sequence to archive 1282
Current log sequence 1282
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 +ASM_DISK
db_recovery_file_dest_size big integer 8G
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 1. Create the local directories on each node needed for the nonshared (private) archive destination. In this example, ORACLE_BASE is set to /u01/app/oracle. Run these commands on each node:
[oracle@rmsclnxclu1 oracle]$ mkdir -p $ORACLE_BASE/test/archive
Step 2. Set the LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2 parameters. 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.
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
SCOPE=SPFILE SID='*'
System altered.
alter system set log_archive_dest_2='LOCATION=/u01/app/oracle/oradata/test/archive' SCOPE=SPFILE SID='*' ;
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, the parameter is actually deprecated. Automatic archiving will be enabled by default whenever an Oracle Database 10g 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
Database closed.
Database dismounted.
ORACLE instance shut down.
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
ORACLE instance started.
Total System Global Area 655434464 bytes
Fixed Size 455392 bytes
Variable Size 125829120 bytes
Database Buffers 528482304 bytes
Redo Buffers 667648 bytes
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:
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 1281
Next log sequence to archive 1282
Current log sequence 1282
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 +ASM_DISK
db_recovery_file_dest_size big integer 8G
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.