How to enable Archivelog mode in Oracle database 11g

There are two types of logging modes in Oracle database

1. ARCHIVELOG :- In this type of logging whatever oracle writes in a redo log file related to transactions in database, saved to another location after a log file has been filled . This location is called Archive location. if database is in Archive log mode then in case of any disaster, we can recover our database upto the last commit and user don't have to reenter their data. Until a redo log file is not written to the Archive location it cannot be reused by oracle to write redo related data.

2. NOARCHIVELOG :- In this type of logging whatever oracle writes in a redo log file related to transactions in database must be overwritten when all the log files have been filled. In this type of logging we can recover our database upto the last consistent backup we have with us, after that users have to reenter their data

To change the Oracle database in ARCHIVELOG mode

1. Check current archive log mode

[oracle@NVMBD1BZY150D00 ~]$ sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 3 10:45:21 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

sql> archive log list

Database log mode No Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2296
Current log sequence 2299
OR

SQL> select log_mode from v$database;

LOG_MODE

------------
NOARCHIVELOG

2 Change archive log location and archive log format with new location and format.

If needed set the archive log destination where you want to save your archive logs whether to a single location or to multiple location. If this is not set then Oracle save archive log files in DB_RECOVERY_FILE_DEST location if set. If you have not set your DB_RECOVERY_FILE_DEST location then you have to set your archive location before changing your database to ARCHIVELOG mode.

SQL> alter system set log_archive_dest_1='LOCATION=/u01/archives/' scope=both;

System altered.

SQL> alter system set log_archive_format = 'archive_%t_%s_%r.arc' scope=spfile;                
System altered.

3. Check location has reflected or not.

SQL> archive log list;

Database log mode No Archive Mode
Automatic archival Enabled
Archive destination /u01/archives/
Oldest online log sequence 2296
Current log sequence 2299

4. Shut down the database

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

5. Startup in mount mode to change archive log mode

SQL> startup mount

ORACLE instance started.

 
Total System Global Area 535662592 bytes

Fixed Size 1375792 bytes

Variable Size 385876432 bytes
Database Buffers 142606336 bytes
Redo Buffers 5804032 bytes
Database mounted.

6. Enable archive log

SQL> alter database archivelog;

Database altered.

7. Open database

SQL> alter database open;

Database altered.

8. Apply log switch to check archives generating at new location.

SQL> alter system switch logfile;

System altered.

9.Check /verify archivelog mode.

SQL> SELECT LOG_MODE FROM V$DATABASE;

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

OR

SQL> ARCHIVE LOG LIST

Database log mode Archive Mode

Automatic archival Enabled
Archive destination /u01/archives/
Oldest online log sequence 2296
Next log sequence to archive 2299
Current log sequence 2299
 
To change the Oracle database in NOARCHIVELOG mode
 
1. Shutdown your running database.

SQL> shut immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
 
2. Start your database in MOUNT mode.

 SQL> startup mount

ORACLE instance started.

 
Total System Global Area 1025298432 bytes

Fixed Size 1341000 bytes
Variable Size 322963896 bytes
Database Buffers 696254464 bytes
Redo Buffers 4739072 bytes
Database mounted.

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 /u01/archives
Oldest online log sequence 1
Current log sequence 1

SQL> select name,log_mode from v$database;

NAME LOG_MODE

--------- ------------
ORCL NOARCHIVELOG

Database changed to NOARCHIVELOG mode

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!