How to Enable ARCHIVELOG Mode in Oracle

For 9i database or below, please refer to: How to Enable ARCHIVELOG Mode in Oracle 9i

What is Archiving?

The process of copying inactive redo log files into archived files is called archiving. Archiving is only effective on databases which enable ARCHIVELOG mode. It makes databases keep transaction history much longer than finite redo logs. Which means that you can have larger recovery point objective (RPO) than usual whenever data lost, database corrupted, system failures or in any doubt.

Reasons to Use ARCHIVELOG

Here I list some advantages of enabling ARCHIVELOG mode for data protection:

• Setting longer recovery objective

As long as the destination of archived log has enough space to keep them, you are capable of setting a longer retention policy for your database.

• Enabling flashback technology

Flashback technology need archived logs to be enabled for more extensions on data recovery, either in enabling flashback database or enabling flashback transaction query.

• Building standby database in data guard

You have to enable ARCHIVELOG mode and then force logging on the primary database for data guard synchronization before building the standby database.

• Transporting changes in GoldenGate

Same reasons as the above, GoldenGate requires ARCHIVELOG mode to be enabled on the EXTRACT database in order to transport changes to REPLICAT database.

• RMAN database hot backup

You can't backup the whole database in NOARCHIVELOG by RMAN when the database is open as READ WRITE. However, it can be worked around.

In short, ARCHIVELOG mode is eventually used for database continuity in terms of data protection, not aims at availability.

The destination of archived logs can be file systems, disk groups of ASM or fast recovery area (FRA). If you're planning to use FRA, the default destination for archived logs. You should enable FRA before enabling ARCHIVELOG mode.

You can have a look at Managing Archived Redo Log Files for more operations about archived logs.

Reasons to Use NOARCHIVELOG

Now we have reasons to enable ARCHIVELOG. In contrast, there should have reasons for NOARCHIVELOG mode.

• Limited Space

If you don't have enough space to put archived logs, you can turn it off by enabling NOARCHIVELOG in case of database hangs due to space full. Additionally, you don't even bother to enable FRA or allocate space for storing archived logs.

• For Development or Testing

If the database you managed is only for development or testing purpose, you really don't need ARCHIVELOG mode. Mostly, you have no chance to use archived logs in such non-critical databases anyhow.

• Empty Databases

For example, an intermediary database, which is for servicing 12c database to retrieve data from 9i database over a db link. Such empty database might be important, but it does not contain any data somehow. That is to say, you don't even need to backup the database.

Enable ARCHIVELOG Mode

First of all, let's check current mode of this database.

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     11
Current log sequence           13

Shutdown the database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Startup to mount state.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1553305600 bytes
Fixed Size                  2253544 bytes
Variable Size             956304664 bytes
Database Buffers          587202560 bytes
Redo Buffers                7544832 bytes
Database mounted.

Change log mode to ARCHIVELOG.

SQL> alter database archivelog;

Database altered.

Open the database.

SQL> alter database open;

Database altered.

Check the log mode again.

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence           13

We have enabled the archiving feature of the database.

Enable NOARCHIVELOG Mode

As for reverting ARCHIVELOG Mode, the steps of procedure are the same except that we change log mode to NOARCHIVELOG at mount state.

SQL> alter database noarchivelog;

Database altered.

Please note that, ARCHIVELOG or NOARCHIVELOG should be switched at database level not system level. For example, the following statement is wrong: SQL> alter system archivelog;

Conclusion

Dealing with archived logs sometimes is a complicated job to do, here's a deeper topic that might interest you: Online Archived Logs vs Backup Archived Logs