Step-by-Step Guide

Purpose

This document explains how to convert an Oracle database:

From NOARCHIVELOG to ARCHIVELOG
From ARCHIVELOG to NOARCHIVELOG

The steps are written in simple language so beginners and production DBAs can easily understand the process.

What is ARCHIVELOG Mode?

When the database runs in ARCHIVELOG mode, Oracle saves a copy of every completed redo log before it is reused.

These archive logs are required for:

Database backup and recovery
Point-in-Time Recovery (PITR)
RMAN Online Backup
Oracle Data Guard
Flashback Database

Without archive logs, recovery options are very limited.

Step 1: Check the Current Database Mode

Connect to the database.


sqlplus / as sysdba

Run:


archive log list;


SELECT log_mode FROM v$database;

Scenario 1: Convert NOARCHIVELOG to ARCHIVELOG

Step 1: Configure Archive Log Location

Choose a location where archive logs will be stored.

Example:


ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u01/app/oracle/archive'
SCOPE=SPFILE;

You can verify:


SHOW PARAMETER log_archive_dest;

Step 2: Shutdown the Database


SHUTDOWN IMMEDIATE;

For RAC
srvctl stop database -d <DBNAME>

Step 3: Start Database in Mount Mode


STARTUP MOUNT;

For RAC
srvctl start database -d <DBNAME> -o mount

Step 4: Enable ARCHIVELOG Mode


sqlplus / as sysdba
select name, open_mode from v$database;
ALTER DATABASE ARCH0IVELOG;

Step 5: Open the Database


ALTER DATABASE OPEN;

For RAC
srvctl stop database -d <DBNAME>
srvctl start database -d <DBNAME>

Step 6: Verify ARCHIVELOG Mode


ARCHIVE LOG LIST;


Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archive


SELECT log_mode FROM v$database;


ARCHIVELOG

Step 7: Test Archive Log Generation

Force a log switch.


ALTER SYSTEM SWITCH LOGFILE;

Check generated archive logs.


ARCHIVE LOG LIST;

Or at the OS level:


ls -ltr /u01/app/oracle/archive

You should see a new archive log file.

Scenario 2: Convert ARCHIVELOG to NOARCHIVELOG

Step 1: Shutdown Database


SHUTDOWN IMMEDIATE;

For RAC
srvctl status database -d <DBNAME>
srvctl stop database -d <DBNAME>

Step 2: Start Database in Mount Mode


STARTUP MOUNT;

For RAC
srvctl start database -d <DBNAME> -o mount

Step 3: Disable ARCHIVELOG


ALTER DATABASE NOARCHIVELOG;

Step 4: Open the Database


ALTER DATABASE OPEN;

For RAC
srvctl stop database -d <DBNAME>
srvctl start database -d <DBNAME>

Step 5: Verify Database Mode


ARCHIVE LOG LIST;


SELECT log_mode FROM v$database;

Estimate Archive Log Space Requirement

Use the following query to estimate daily archive log generation:


SELECT A.*,
ROUND(A.Count# * B.AVG# / 1024 / 1024 / 1024) AS Daily_Avg_GB
FROM
(
    SELECT
        TO_CHAR(first_time,'YYYY-MM-DD') DAY,
        COUNT(*) Count#,
        MIN(recid) Min#,
        MAX(recid) Max#
    FROM v$log_history
    GROUP BY TO_CHAR(first_time,'YYYY-MM-DD')
) A,
(
    SELECT
        AVG(bytes) AVG#
    FROM v$log
) B
ORDER BY DAY;

This helps estimate how much storage is needed for archive logs each day.

shashi

Thursday, July 30, 2026

Oracle Database: Convert Between ARCHIVELOG and NOARCHIVELOG Mode