Enable/Disable archivelog mode
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
- Stop database:
SQL> shutdown immediate;
- Start database in
mount
mode:SQL> startup mount;
- Enable archivelog
SQL> alter database archivelog;
- Open database
SQL> alter database open;
- 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
- Stop database:
SQL> shutdown immediate;
- Start database in
mount
mode:SQL> startup mount;
- Disable archivelog
SQL> alter database noarchivelog;
- Open database
SQL> alter database open;
- 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