Enabling archiving on a database

Configuring archiving on a databases have various benefits. This is required when advanced replication technologies like Oracle Streams, Oracle GoldenGate or third party softwares like Informatica are configured. These softwares require online log files and archived redo log files for data minining.Let us see how we can configure archiving on a database that is running on no archiving mode.

First, the destination where the archive log files sit has to be configured. Set LOG_ARCHIVE_DEST_1 :

First, set environment and connect to SQL*Plus

$ . oraenv
db_name
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> archive log list

The above command confirms that archiving is not enabled

Set the archive log destination using ALTER SYSTEM command :

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/archive_dest' scope=spfile;

This does not enable archiving. It only sets the destination where archvied log files will be written to by ARCH process

SQL> archive log list;
SQL> col DEST_NAME FORMAT a33
SQL> col DESTINATION FORMAT a33
SQL> set line 188
SQL> select dest_id, dest_name, binding, status, target, destination from v$archive_dest;

Shut down the database using IMMEDIATE option

SQL> shutdown immediate;

Startup the database to MOUNT mode

SQL> startup mount;

Enable archiving using below command

SQL> alter database archivelog;
SQL> alter database open;
Verification

Executing commands is only one part of the story. We must verify that the command is taking effect. There are handful of ways to ensure that archiving is indeed enabled and the logs are getting written to the right destination. This is done using the below commands:

SQL> archive log list;
SQL> col DEST_NAME FORMAT a33
SQL> col DESTINATION FORMAT a33
SQL> set line 188

SQL> select dest_id, dest_name, binding, status, target, destination from v$archive_dest;
Comments