Enabling ARCHIVELOG Mode

Most of the Oracle production database require you to enable ARCHIVELOG mode for your database. When you enable this mode redo logs will be archived instead of overwritten. The archivelogs are stored in a seperate place usually can backed up regularly by your standard filesystem backup system. Archive logs are utilized by RMAN, Data Guard, Flashback and many others.

Enabling archive mode is simple, just connect to your database in mounted but closed mode (startup mount) and alter the database. But if you don’t tune alittle you’ll run into problems down the road, so lets specify some parameters too. Namely, consider LOG_ARCHIVE_DEST.

Lets start by checking the current archive mode.

[oracle@calora01]$sqlplus “/as sysdba”

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
————
NOARCHIVELOG

So we’re in NOARCHIVELOG mode and we need to change.
We can use a database alter statement.

SQL>create spfile from pfile;
SQL>shutdown immediate;
SQL>startup;
SQL>alter system set log_archive_dest_1=’location=/u02/oradata/ATT/arch’ scope=spfile;
SQL>alter system set log_archive_start=TRUE scope=spfile;
SQL>shutdown immediate;
Remember, if you run out of space in your archive log destination the database will shut down!

Now we can startup the database in mount mode and put it in archivelog mode.
[oracle@calora01]$sqlplus “/as sysdba”

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 184549376 bytes
Fixed Size 1300928 bytes
Variable Size 157820480 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
SQL>alter database mount;
Database mounted

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

You can see here that we put the database in ARCHIVELOG mode by using the SQL statement “alter database archivelog”, but Oracle won’t let us do this unless the instance is mounted but not open. To make the change we shutdown the instance, and then startup the instance again but this time with the “mount” option which will mount the instance but not open it. Then we can enable ARCHIVELOG mode and open the database fully with the “alter database open” statement.

There are several system views that can provide us with information reguarding archives, such as:
V$DATABASE
Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode and whether MANUAL (archiving mode) has been specified.
V$ARCHIVED_LOG
Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.
V$ARCHIVE_DEST
Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.
V$ARCHIVE_PROCESSES
Displays information about the state of the various archive processes for an instance.
V$BACKUP_REDOLOG
Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.
V$LOG
Displays all redo log groups for the database and indicates which need to be archived.
V$LOG_HISTORY
Contains log history information such as which logs have been archived and the SCN range for each archived log.
Using these tables we can verify that we are infact in ARCHIVELOG mode:
SQL> select log_mode from v$database;

LOG_MODE
————
ARCHIVELOG

2 thoughts on “Enabling ARCHIVELOG Mode

  1. Pingback: Format for parameter LOG_ARCHIVE_FORMAT? « Oracle Spin

  2. Pingback: Check archiver is running on archivelog mode? « Oracle Spin

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s