How To Enable/Disable Archive Log Mode In Oracle Database

There are 2 types of logging mode in oracle database.

1. Archivelog mode :

In this mode, after the online redo logs are filled , it will move to archive location.

2. Noarchivelog mode :

In this mode, filled online redo logs wont be archives, instead they will be overwritten.

How to Enable archive log mode :

 

SQL > select name,log_mode from v$database;
 
NAME      LOG_MODE
--------- -----------
PROD      NOARCHIVELOG
 

SQL > archive log list
Database log mode              No Archive Mode
Automatic archival             Disbled
Archive destination            /u01/archive/PROD
Oldest online log sequence     506
Next log sequence to archive   513
Current log sequence           513
 

---- make sure db is running in spfile

SQL > alter system set log_archive_dest_1='LOCATION=/u01/archive/PROD' scope=spfile;
database altered.
 

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

SQL > startup mount
ORACLE instance started.
Total System Global Area 6415597568 bytes
Fixed Size                  2170304 bytes
Variable Size             905970240 bytes
Database Buffers         5502926848 bytes
Redo Buffers                4530176 bytes
Database mounted.
 

SQL >alter database archivelog;
 
database altered.
 

SQL >alter database open;
 
database altered.
 

SQL >select name,log_mode from v$database;
 
NAME      LOG_MODE
--------- -----------
PROD      ARCHIVELOG
 

SQL >archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/archive/PROD
Oldest online log sequence     506
Next log sequence to archive   513
Current log sequence           513
 

How to Disable archivelog mode :

SQL >select name,log_mode from v$database;

 

NAME      LOG_MODE

--------- -----------

PROD      ARCHIVELOG

 

SQL > archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/archive/PROD

Oldest online log sequence     506

Next log sequence to archive   513

Current log sequence           513

 

 

SQL > shutdown  immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL > startup mount

ORACLE instance started.

Total System Global Area 6415597568 bytes

Fixed Size                  2170304 bytes

Variable Size             905970240 bytes

Database Buffers         5502926848 bytes

Redo Buffers                4530176 bytes

Database mounted.

 

SQL >alter database noarchivelog;

database altered.

SQL >alter database open;

 

database altered.

SQL > select name,log_mode from v$database;

 

NAME      LOG_MODE

--------- -----------

PROD      NOARCHIVELOG

 

SQL > archive log list

Database log mode              No Archive Mode

Automatic archival             Disbled

Archive destination            /u01/archive/PROD

Oldest online log sequence     506

Next log sequence to archive   513

Current log sequence           513

 

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

Keep learning… Have a great day!!!