Enable ARCHIVELOG mode for Oracle Database
What Is the Archived Redo Log?
Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log. The process of turning redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode. You can choose automatic or manual archiving.
An archived redo log file is a copy of one of the filled members of a redo log group. It includes the redo entries and the unique log sequence number of the identical member of the redo log group. For example, if you are multiplexing your redo log, and if group 1 contains identical member files a_log1 and b_log1, then the archiver process (ARCn) will archive one of these member files. Should a_log1 become corrupted, then ARCn can still archive the identical b_log1. The archived redo log contains a copy of every group created since you enabled archiving.
When the database is running in ARCHIVELOG mode, the log writer process (LGWR) cannot reuse and hence overwrite a redo log group until it has been archived. The background process ARCn automates archiving operations when automatic archiving is enabled. The database starts multiple archiver processes as needed to ensure that the archiving of filled redo logs does not fall behind.
You can use archived redo logs to
- Recover a database
- Update a standby database
- Get information about the history of a database using the LogMiner utility
Choosing Between NOARCHIVELOG and ARCHIVELOG Mode
The choice of whether to enable the archiving of filled groups of redo log files depends on the availability and reliability requirements of the application running on the database. If you cannot afford to lose any data in your database in the event of a disk failure, use ARCHIVELOG mode. The archiving of filled redo log files can require you to perform extra administrative operations.
Running a Database in NOARCHIVELOG Mode
When you run your database in NOARCHIVELOG mode, you disable the archiving of the redo log. The database control file indicates that filled groups are not required to be archived. Therefore, when a filled group becomes inactive after a log switch, the group is available for reuse by LGWR.
NOARCHIVELOG mode protects a database from instance failure but not from media failure. Only the most recent changes made to the database, which are stored in the online redo log groups, are available for instance recovery. If a media failure occurs while the database is in NOARCHIVELOG mode, you can only restore the database to the point of the most recent full database backup. You cannot recover transactions subsequent to that backup.
In NOARCHIVELOG mode you cannot perform online tablespace backups, nor can you use online tablespace backups taken earlier while the database was in ARCHIVELOG mode. To restore a database operating in NOARCHIVELOG mode, you can use only whole database backups taken while the database is closed. Therefore, if you decide to operate a database in NOARCHIVELOG mode, take whole database backups at regular, frequent intervals.
Running a Database in ARCHIVELOG Mode
When you run a database in ARCHIVELOG mode, you enable the archiving of the redo log. The database control file indicates that a group of filled redo log files cannot be reused by LGWR until the group is archived. A filled group becomes available for archiving immediately after a redo log switch occurs.
The archiving of filled groups has these advantages:
- A database backup, together with online and archived redo log files, guarantees that you can recover all committed transactions in the event of an operating system or disk failure.
- If you keep an archived log, you can use a backup taken while the database is open and in normal system use.
- You can keep a standby database current with its original database by continuously applying the original archived redo logs to the standby.
You can configure an instance to archive filled redo log files automatically, or you can archive manually. For convenience and efficiency, automatic archiving is usually best. Figure below illustrates how the archiver process (ARC0 in this illustration) writes filled redo log files to the database archived redo log.
If all databases in a distributed database operate in ARCHIVELOG mode, you can perform coordinated distributed database recovery. However, if any database in a distributed database is in NOARCHIVELOG mode, recovery of a global distributed database (to make all databases consistent) is limited by the last full backup of any database operating in NOARCHIVELOG mode.
Setting the Initial Database Archiving Mode
You set the initial archiving mode as part of database creation in the CREATE DATABASE statement. Usually, you can use the default of NOARCHIVELOG mode at database creation because there is no need to archive the redo information generated by that process. After creating the database, decide whether to change the initial archiving mode.
If you specify ARCHIVELOG mode, you must have initialization parameters set that specify the destinations for the archived redo log files (see "Setting Initialization Parameters for Archive Destinations").
Changing the Database Archiving Mode
To change the archiving mode of the database, use the ALTER DATABASE statement with the ARCHIVELOG or NOARCHIVELOG clause. To change the archiving mode, you must be connected to the database with administrator privileges (AS SYSDBA).
Check which mode is the database instance running in
$ sqlplus /nolog SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 5 16:25:34 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. SQL> connect /as sysdba Connected. SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 536 Current log sequence
The following steps switch the database archiving mode from NOARCHIVELOG to ARCHIVELOG
Shut down the database instance
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
An open database must first be closed and any associated instances shut down before you can switch the database archiving mode. You cannot change the mode from ARCHIVELOG to NOARCHIVELOG if any datafiles need media recovery.
- Back up the database (cold backup oradata and control files)
- Edit the initialization parameter file to include the initialization parameters that specify the destinations for the archived redo log files
Start a new instance and mount, but do not open, the database
SQL> startup mount; ORACLE instance started. Total System Global Area 2505338880 bytes Fixed Size 2230952 bytes Variable Size 805307736 bytes Database Buffers 1677721600 bytes Redo Buffers 20078592 bytes Database mounted.
To enable or disable archiving, the database must be mounted but not open.
Change the database archiving mode. Then open the database for normal operations
SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered.
Check mode again
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 536 Next log sequence to archive 538 Current log sequence 538
Database log mode is now Archive Mode.
- Shut down the database
- Back up the database
Changing the database archiving mode updates the control file. After changing the database archiving mode, you must back up all of your database files and control file. Any previous backup is no longer usable because it was taken in NOARCHIVELOG mode.
Performing Manual Archiving
As mentioned in "Running a Database in ARCHIVELOG Mode", for convenience and efficiency, automatic archiving is usually best. However, you can configure your database for manual archiving only. To operate your database in manual archiving mode, follow the procedure described in "Changing the Database Archiving Mode", but replace the ALTER DATABASE statement in step 5 with the following statement
alter database archivelog manual;
When you operate your database in manual ARCHIVELOG mode, you must archive inactive groups of filled redo log files or your database operation can be temporarily suspended. To archive a filled redo log group manually, connect with administrator privileges. Ensure that the database is either mounted or open. Use the ALTER SYSTEM statement with the ARCHIVE LOG clause to manually archive filled redo log files. The following statement archives all unarchived log files
alter system archive log all;
When you use manual archiving mode, you cannot specify any standby databases in the archiving destinations.
Even when automatic archiving is enabled, you can use manual archiving for such actions as rearchiving an inactive group of filled redo log members to another location. In this case, it is possible for the instance to reuse the redo log group before you have finished manually archiving, and thereby overwrite the files. If this happens, the database writes an error message to the alert log.
Adjusting the Number of Archiver Processes
The LOG_ARCHIVE_MAX_PROCESSES initialization parameter specifies the number of ARCn processes that the database initially starts. The default is four processes. There is usually no need specify this initialization parameter or to change its default value, because the database starts additional archiver processes (ARCn) as needed to ensure that the automatic processing of filled redo log files does not fall behind.
However, to avoid any run-time overhead of starting additional ARCn processes, you can set the LOG_ARCHIVE_MAX_PROCESSES initialization parameter to specify that up to 30 ARCn processes be started at instance startup. The LOG_ARCHIVE_MAX_PROCESSES parameter is dynamic, so you can change it using the ALTER SYSTEM statement.
The following statement configures the database to start six ARCn processes upon startup
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=6;
The statement also has an immediate effect on the currently running instance. It increases or decreases the current number of running ARCn processes to six.
Reference
