A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

Enable/Disable Archive Log Mode In Oracle Database?

3 min read
ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time.

NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.
ARCHIVELOG  MODE -> In this mode, after the online redo logs are filled , it will move to archive location.

Advantages

* You can perform hot backups (backups when the database is online).

* The archive logs and the last full backup (offline or online) or an older backup can completely recover the database without losing any data because all changes made in the database are stored in the log file.

Disadvantages

* It requires additional disk space to store archived log files. However, the agent offers the option to purge the logs after they have been backed up, giving you the opportunity to free disk space if you need it.
NO-ARCHIVELOG MODE -> In this mode, filled online redo logs wont be archives, instead they will be overwritten.

Advantages

It requires no additional disk space to store archived log files.

Disadvantages 

1. If you must recover a database, you can only restore the last full offline backup. As a result, any changes made to the database after the last full offline backup are lost.

2. Database downtime is significant because you cannot back up the database online. This limitation becomes a very serious consideration for large databases.

Note: Because NOARCHIVELOG mode does not guarantee Oracle database recovery if there is a disaster, the Agent for Oracle does not support this mode. If you need to maintain Oracle Server in NOARCHIVELOG mode.
1. Archivelog mode
SQL > select name,log_mode from v$database;

NAME LOG_MODE
--------- -----------
ASRBLG NOARCHIVELOG

SQL > archive log list
Database log mode No Archive Mode
Automatic archival Disbled
Archive destination /u01/app/oracle/FRA
Oldest online log sequence 
Next log sequence to archive 
Current log sequence 

Check and confirm if DB is running with spfile
 
SQL > alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/FRA' scope=spfile;
database altered.

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

SQL > startup mount
ORACLE instance started.
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
--------- -----------
ASRBLG ARCHIVELOG

SQL >archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/FRA
Oldest online log sequence 
Next log sequence to archive 
Current log sequence 


Disable Archivelog mode:
SQL >select name,log_mode from v$database;

NAME LOG_MODE
--------- -----------
ASRBLG ARCHIVELOG

SQL > archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/FRA
Oldest online log sequence 
Next log sequence to archive 
Current log sequence 


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

SQL > startup mount
ORACLE instance started.
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
--------- -----------
ASRBLG NOARCHIVELOG

SQL > archive log list
Database log mode No Archive Mode
Automatic archival Disbled
Archive destination /u01/app/oracle/FRA
Oldest online log sequence 
Next log sequence to archive 
Current log sequence 

Hope it worked !! šŸ™‚