What is Archivinglog Mode?
The process of copying inactive redo log files into archived files is called archiving. Archiving is only effective on databases which enable ARCHIVELOG mode. It makes databases keep transaction history much longer than finite redo logs. Which means that you can have larger recovery point objective (RPO) than usual whenever data lost, database corrupted, system failures or in any doubt.
Reasons to Use ARCHIVELOG
Here I list some advantages of enabling ARCHIVELOG mode for data protection:
- Setting longer recovery objective
- Enabling flashback technology
- Building standby database in data guard
- Transporting changes in GoldenGate
- RMAN database hot backup
As long as the destination of archived log has enough space to keep them, you are capable of setting a longer retention policy for your database.
Flashback technology need archived logs to be enabled for more extensions on data recovery, either in enabling flashback database or enabling flashback transaction query.
You have to enable ARCHIVELOG mode and then force logging on the primary database for data guard synchronization before building the standby database.
Same reasons as the above, GoldenGate requires ARCHIVELOG mode to be enabled on the EXTRACT database in order to transport changes to REPLICAT database.
You can't backup the whole database in NOARCHIVELOG by RMAN when the database is open as READ WRITE. However, it can be worked around.
In short, ARCHIVELOG mode is eventually used for database continuity in terms of data protection, not aims at availability.
The destination of archived logs can be file systems, disk groups of ASM or fast recovery area (FRA). If you're planning to use FRA, the default destination for archived logs. You should enable FRA before enabling ARCHIVELOG mode.
You can have a look at Managing Archived Redo Log Files for more operations about archived logs.
Reasons to Use NOARCHIVELOG
Now we have reasons to enable ARCHIVELOG. In contrast, there should have reasons for NOARCHIVELOG mode.
- Limited Space
- For Development or Testing
- Empty Databases
If you don't have enough space to put archived logs, you can turn it off by enabling NOARCHIVELOG in case of database hangs due to space full. Additionally, you don't even bother to enable FRA or allocate space for storing archived logs.
If the database you managed is only for development or testing purpose, you really don't need ARCHIVELOG mode. Mostly, you have no chance to use archived logs in such non-critical databases anyhow.
For example, an intermediary database, which is for servicing 12c database to retrieve data from 9i database over a db link. Such empty database might be important, but it does not contain any data somehow. That is to say, you don't even need to backup the database.
Enable ARCHIVELOG Mode in 11g and Later
The later releases are 12c, 18c and 19c. First of all, let's check current mode of this database.
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
Current log sequence 13
Shutdown the database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Startup to mount state.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1553305600 bytes
Fixed Size 2253544 bytes
Variable Size 956304664 bytes
Database Buffers 587202560 bytes
Redo Buffers 7544832 bytes
Database mounted.
For a RAC database, we restart it to mount on only one node by this:
[oracle@primary01 ~]$ srvctl stop database -d orclcdb
[oracle@primary01 ~]$ srvctl start database -d orclcdb -i ORCLCDB1 -o mount
The instance name is uppercase.
Next, we changed log mode to ARCHIVELOG.
SQL> alter database archivelog;
Database altered.
Open the database.
SQL> alter database open;
Database altered.
Check the log mode again.
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
We have enabled the archiving feature of the database.
Enable NOARCHIVELOG Mode
As for reverting ARCHIVELOG Mode, the steps of procedure are the same except that we change log mode to NOARCHIVELOG at mount state.
SQL> alter database noarchivelog;
Database altered.
Let's check archive log status.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
Current log sequence 13
Please note that, ARCHIVELOG or NOARCHIVELOG should be switched at database level not system level. For example, the following statement is wrong:
SQL> alter system archivelog;
Enable ARCHIVELOG Mode in 9i and Earlier
Create archive log destination directory and enter sqlplus
[oracle@test ~]$ mkdir -p /u01/app/oracle/product/9.2.0/archivelog/oracle9i/
[oracle@test ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.8.0 - Production on Thu Apr 14 18:59:03 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
Startup the database and see if there's any problem.
SQL> startup;
ORACLE instance started.
Total System Global Area 235999648 bytes
Fixed Size 450976 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
Check the current status of archive mode before enabling archivlog
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/9.2.0/dbs/arch
Oldest online log sequence 11
Current log sequence 13
Bounce the database to mount
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 235999648 bytes
Fixed Size 450976 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Set necessary parameters in SPFILE
SQL> alter system set log_archive_start=TRUE scope=spfile;
System altered.
SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/product/9.2.0/archivelog/oracle9i/' scope=spfile;
System altered.
SQL> alter system set log_archive_format='arch_%t_%s.arc' scope=spfile;
System altered.
Bounce the database to mount again
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 235999648 bytes
Fixed Size 450976 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Start and enable archive log
SQL> archive log start;
Statement processed.
SQL> alter database archivelog;
Database altered.
Open the database
SQL> alter database open;
Database altered.
Check the current status of archive mode after enabling archivelog
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/9.2.0/archivelog/oracle9i/
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
Force redo log file to switch
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
Check the log archive destination
[oracle@oracle9i ~]$ ll /u01/app/oracle/product/9.2.0/archivelog/oracle9i/
total 896
-rw-r----- 1 oracle oinstall 871424 Apr 14 19:13 arch_1_13.arc
-rw-r----- 1 oracle oinstall 32256 Apr 14 19:13 arch_1_14.arc
-rw-r----- 1 oracle oinstall 3072 Apr 14 19:13 arch_1_15.arc
-rw-r----- 1 oracle oinstall 1024 Apr 14 19:13 arch_1_16.arc
We're done.
Please note that, LOG_ARCHIVE_START is deprecated since 10g.
Conclusion
Dealing with archived logs sometimes is a complicated job to do, here's a deeper topic that might interest you: Online Archived Logs vs Backup Archived Logs
Does temporarily switching between ARCHIVELOG-to-NOARCHIVELOG-to-ARCHIVELOG render the backups taken when it was on either mode unusable?
Generally speaking, as long as the point-in-time you want to recover can be rolled forward by required archived logs, the backups are useful. Otherwise, they’re useless.
That’s why we usually take a full backup once the archivelog mode is back.