Skip to content

How to Enable/Disable Archive Log Mode in Oracle 11g

01/07/2015

It is advisable to backup your database before either enabling or disabling archive log mode as a precaution.

Enabling archive log mode

Verify the current archive log mode.

archive log mode 01

We can see that database log mode is “No Archive Mode”. Also worth noting is the destination used for archiving. In order to see where that location is mapped to we can check the recovery_file_dest parameter.

archive log mode 02

This shows us that the archive logs would be written to the fast recovery area (FRA). You may want to leave this as it is or, for example, if you prefer to store the archive logs elsewhere or disk space is tight on the drive hosting the FRA then you can amend where they are written to.

To change where the archive logs are written to we need to set the log_archive_dest_1 parameter, there can be multiple locations where archive logs are written to so you are able to set log_archive_dest_n parameters.

archive log mode 03

Before we can enable archive log mode we need to put the database into mount mode.

archive log mode 04

We can now set the archive log mode and open the database.

archive log mode 05

We can now see that archive log mode is enabled and that the logs will be written to a location other than the FRA.

Disabling archive log mode

Verify the current archive log mode.

archive log mode 06

We now need to shut the database down and bring it back in mount mode.

archive log mode 07

We now set the database to noarchivelog and re-open it. Verify the archive log mode with archive log list; and we see that it shows No Archive Mode.

archive log mode 08

Now we see that archive logging is disabled.

Force a log switch

You can force Oracle to perform a log switch, this results in the generation of an archive log file on the file system.

Connect to the database and check the log sequence.

archive log mode 09

One method to force the switch of log files is to use ALTER SYSTEM SWITCH LOGFILE.

We can then also see that the log sequence has updated.

archive log mode 10

 

The “Next log sequence to archive” number from the earlier screenshot is reflected in the file name of the newly generated log file.

archive log mode 11

ALTER SYSTEM SWITCH LOGFILE writes a checkpoint which indicates at what point the online redo log file has been written to the archive log file. It is a faster process than ALTER SYSTEM ARCHIVE LOG CURRNET as it does not wait for the archiver process to complete writing to the archive log file.

One risk of using ALTER SYSTEM SWITCH LOGFILE is that the command returns control to the user although the archiver process is still writing to the archive log file on the file system which could run out of space.

Advertisements

From → Oracle

Leave a Comment

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

%d bloggers like this: