What is ARCHIVELOG? Why enable it?
The redo log of a database consists of two or more redo log files. as the database requires at least two files. This causes size constraints for the amount of data saved by the Redo Logs. See “Oracle Managing Archived Redo Logs”. When the current redo log file fills, LGWR begins writing to the next available redo log file. When the last available redo log file fills, LGWR returns to the first redo log file and writes to it, starting the cycle again. Filled redo log files become available for reuse depending on archiving status.- If archiving is disabled (the database is in NOARCHIVELOG mode), a filled redo log file is available after the changes recorded in it have been written to the datafiles.
- If archiving is enabled (the database is in ARCHIVELOG mode), a filled redo log file is available to LGWR after the changes recorded in it have been written to the datafiles and the file has been archived.
Check ArchiveLog Status ?
Login to Server CMD with DBA user (usually is Oracle) $ export ORACLE_SID=<Database SID>
Connect to your database as sysdba
$ sqlplus / as sysdba
If it didn’t work, try:
$ sqlplus /nolog
SQL> connect / as sysdba
Check the ARCHIVELOG mode status. execute the following SQL command:
SQL> archive log list ;
The output should be one of the following:
ARCHIVELOG enabled:
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/oradata/<MYDB>/arch
Oldest online log sequence 5011
Next log sequence to archive 5013
Current log sequence 5013
ARCHIVELOG disabled
Database log mode No Archive Mode
Automatic archival Disabled
Conclusion
Enabling, archive log logs has its pros in case of your database has large transactions. On the other hand, if you enabled it you should watch the server storage, otherwise, you might face the error ORA-19809.- To enable Archive log or stop archiving mode read This article.