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

It’s a prime responsibility of Oracle DBA to keep database up 24×7 but there may be some chances when your database gets crash. If your database gets crash and you want to recover it using archive log files then your database must be in archive mode. If your database is in archive made then you can recover your database at a large extent.

We have taken Oracle 11g express as an example but the process is same for Oracle 10g and Oracle 11g standard/enterprise.

Note: It is strongly recommended to backup of the database after a normal shutdown prior to changing the archive mode of a database.

Follow the below mentioned process to enable archive log mode in Oracle 10g or 11g:

How to Enable Archive Log mode?

First verify the database archive log mode. Connect your oracle database from command prompt using following command.

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Nov 10 13:30:30 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> archive log list

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     90
Current log sequence           91

Here you can see the archive log mode is in No Archive Mode. Note that Archive destination is USE_DB_RECOVERY_FILE_DEST. You can check the path by looking at the parameter DB_RECOVERY_FILE_DEST.

SQL> show parameter recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      C:\oraclexe\app\oracle\fast_re
                                                 covery_area
db_recovery_file_dest_size           big integer 10G
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0

By default archive logs are written to the flash recovery area. If you do not want to write archive logs to the flash recovery area, use following command to change the archive logs location.

SQL> alter system set db_recovery_file_dest='D:\archivelog' scope=both;
System altered.

SQL> archive log list;

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     90
Current log sequence           91

SQL> show parameter recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
db_recovery_file_dest                string      D:\test\archivelog
db_recovery_file_dest_size           big integer 10G
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0

Now shutdown the database and bring it in mount mode.

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1071769376 bytes
Fixed Size                  1339352 bytes
Variable Size             626295792 bytes
Database Buffers          435767616 bytes
Redo Buffers                4981616 bytes
Database mounted.

Now issue Alter database command to set your database in archive log mode.

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            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     90
Next log sequence to archive   91
Current log sequence           91

Here you can see that the database is in archive log mode and automatic archival is enabled as well.
If you want to check whether it is working or not, you can switch to the log file to see that an archive is written to the archive log location.

SQL> alter system switch logfile;
System altered.

SQL> show parameter recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
db_recovery_file_dest                string      D:\test\archivelog
db_recovery_file_dest_size           big integer 10G
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0

Enable Archive Log Mode

How to Disable Archive Log Mode?

Note: It is strongly recommended to backup of the database after a normal shutdown prior to changing the archive mode of a database.

Follow the below mentioned process to disable archive log mode in Oracle 10g or 11g:

First verify the database archive log mode. Connect your oracle database from command prompt using following command.

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Nov 10 13:52:42 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> archive log list

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     91
Next log sequence to archive   92
Current log sequence           92

Here you can see the Database is in Archive log mode. Now we have to shut down the database and bring it up in mount stage.

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1071769376 bytes
Fixed Size                  1339352 bytes
Variable Size             626295792 bytes
Database Buffers          435767616 bytes
Redo Buffers                4981616 bytes
Database mounted.

Now issue the following command to disable archive log mode and open the database.

SQL> alter database noarchivelog;
Database altered.

SQL> alter database open;
Database altered.

SQL> archive log list;

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     91
Current log sequence           92

Here you can see, ARCHIVELOG mode has been disabled. Now i hope you understood how to Enable/Disable Archive Log Mode in Oracle 10g/11g

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top
Scroll to Top