Enable/Disable archivelog mode

Published   Read time 2 min(s) 27 sec(s)   (3086 views).  Database  

Most high availability features of Oracle require you to enable ARCHIVELOG mode enable for your database. When you enable this mode redo logs will be archived instead of overwritten. The archivelogs are usually stored in a seperate place and can backed up regularly by your standard filesystem backup system (Legato NetWorker, Veritas NetBackup, etc.). Archive logs are utilized by rman.

Caution: If your going to enable archivelog mode on a real database I would recommend shutting down the database and doing a cold backup just in case.

Enabling archive mode is simple, just connect to your database in mounted but closed mode (startup mount) and alter the database. This post works with Oracle 10g (10.1.3.x)

Is database in archivelog mode or not?

SQL> archive log list;
   Database log mode               No Archive Mode
   Automatic archival              Disabled
   Archive destination             /u01/product/10.1.3/dbs/arch
   Oldest online log sequence      36
   Current log sequence            38

From the above, we see that No Archive Mode is set and also that Automatic archival is disabled.

Enabling ARCHIVELOG mode

For archivelog to be active, init.ora should have following parameters:

log_archive_start = true
log_archive_dest = "/u02/archivelog/<ORACLE_SID>"
log_archive_format = arch_%t_%s.arc

Perform the following steps to enable ARCHIVELOG mode

  1. Stop database: 
    SQL> shutdown immediate;
  2. Start database in mount mode: 
    SQL> startup mount;
  3. Enable archivelog 
    SQL> alter database archivelog;
  4. Open database 
    SQL> alter database open;
  5. Activate automatic archiving: 
    SQL> alter system archive log start;

Example output:

oracle@t5-8-db1 ~$ sqlplus / as sysdba
SQL*Plus: Release 10.1.3.2.0 - Production on Fri Feb 20 16:00:58 2009
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
Connected to an idle instance.
 
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/product/10.1.3/dbs/arch
Oldest online log sequence     36
Current log sequence           38
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
 
Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             511708752 bytes
Database Buffers          331350016 bytes
Redo Buffers                5132288 bytes
Database mounted.
SQL> alter database archivelog;
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/archivelog/<oracle-side>
Oldest online log sequence     37
Next log sequence to archive   38
Current log sequence           38
SQL>

We can now see that archive log mode is enabled. Notice that Automatic archive is enabled as well.

Disable ARCHIVELOG mode

Perform the following steps to disable ARCHIVELOG mode

  1. Stop database: 
    SQL> shutdown immediate;
  2. Start database in mount mode: 
    SQL> startup mount;
  3. Disable archivelog 
    SQL> alter database noarchivelog;
  4. Open database 
    SQL> alter database open;
  5. Dectivate automatic archiving: 
    SQL> alter system archive log stop;

For archivelog to remain disabled, comment out the following parameters in the init.ora file:

# log_archive_start = true
# log_archive_dest = "/u02/archivelog/<ORACLE_SID>"
# log_archive_format = arch_%t_%s.arc