Configure Databse to Run in ARCHIVELOG Mode

Part I)

First of  all you must configure the following parameters related to database archiving:

You have two main options for define archive log destinations:

1) Flash Recovery Area (recommended)

In this part, first of all you must be set the located size of the FRA:

SQl> Alter system set DB_RECOVERY_FILE_DEST_SIZE=2G scope=both;

Note:

DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST.

then specifies the location of the FRA, in this part you have three options for define the location

• Locally attached storage

SQL> alter system set DB_RECOVERY_FILE_DEST='/opt01/app/oracle/flash_recovery_area' scope=both;

• Oracle's Cluster File System (OCFS)


• Automatic Storage management (ASM)

SQL> alter system set DB_RECOVERY_FILE_DEST='+DATA' scope=both;

(DATA = ASM Disk volume)

Note: In a RAC database, all instances must have the same values for these parameters(DB_RECOVERY_FILE_DEST, DB_RECOVERY_FILE_DEST_SIZE).

2) ARCHIVELOG Destination Directories

• Directories

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/opt02/app/oracle/second_recovery_area mandatory' scope=both;

Note: If you want to use directories and FRA at the same time you must set  LOG_ARCHIVE_DEST_10 parameter as following command:

SQL> alter system set LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;

• Network Directories

SQL> alter system set LOG_ARCHIVE_DEST_2='LOCATION=/opt03/mnt_drive_z optional' scope=both;

• Database Service Name (Standby Database/Data guard)

SQL> alter system set LOG_ARCHIVE_DEST_3='SERVICE=standby_db mandatory' scope=both;

Note: You can define up to 10 different archive log destinations and each location as a mandatory or optional location.

The other important parameters are:

LOG_ARCHIVE_MIN_SUCCEED_DEST

SQL> alter system set LOG_ARCHIVE_MIN_SUCCEED_DEST=2 scope=both;

Note: This parameter defines the minimum number of destinations that the log file must be copied to before it can be overwritten. This value should be greater than or equal to the number of MANDATORY destinations in LOG_ARCHIVE_DEST_n.

LOG_ARCHIVE_FORMAT

SQL> alter system set LOG_ARCHIVE_FORMAT ='LOG%s_%t_%r.ARC' scope=both;

Part II)

SQL> connect / as sysdba

Part III)

SQL> shutdown immediate

Part IV)

SQL> startup mount

Part V)

SQL>alter database archivelog;

Part VI)

SQL> alter database open;

Part VII)

To display the status of archiving:

SQL> select log_mode from v$database;

Or

SQL> archive log list;