Configure Archive Log Mode

Oracle databases are created in NOARCHIVELOG mode. This means that you can only take consistent cold backups.

In order to perform online backups your database must be running in ARCHIVELOG mode. Archive log files can also be transmitted and applied to a standby database inDR (Disaster Recovery) site.

To find out the mode database is running. Login as sys user and issue the following statement.

SQL> archive log list
Database log mode : Archive Mode
Automatic archival : Enabled
Archive destination : /cgildb/oradata/archive
Oldest online log sequence : 3413
Next log sequence to archive : 3415
Current log sequence : 3415

You can also issue the following statement.

SQL> Select log_mode from v$database;
LOG_MODE
---------------
Archivelog

Convert NoArchive to ArchiveLog mode

Step 1: Set the following Initialization Parameter

1. Log_archive_dest_1 = 'LOCATION=/export/prodarch' MANDATORY REOPEN
2. Log_archive_dest_2 = 'SERVICE=stbyprod' OPTIONAL REOPEN=60
3. Log_archive_format = 'prod_%t_%r_%s.arc'
4. Log_archive_max_processes= 2
5. Log_archive_min_succeed_dest = 1
6. Log_archive_start= True (deprecated from oracle 10g).

Step 2:

SQL> Shutdown Immediate
Database Closed
Database Dismounted
Instance Shutdown
SQL> Startup Mount
Oracle Instance started
.....
Database Mounted
SQL> Alter Database Archivelog;
Database Altered
SQL> Alter Database Open;
Database Altered.

Thats it..Your database is in Archivelog mode now..

About Related Initialization Parameter

1. Log_archive_dest_n : n = 1 to 10. The destinationcan be on a local machine or a remote machine (Standby). The syntax is,
log_archive_dest_n = 'null' Service = service_name (from tnsnames) Location = directory [Mandatory Optional] [Reopen [= integer]]
where,
Mandatory - archiving must succeed at this destination.
Reopen - (time in secs) retries to archive when it did not succeed the first time. (default 300)

2. Log_archive_format: Format of the archived log filenames. Use any text in combination with any of the substitution variable to uniquely name the file. The substitution variables are,
%s - Log Sequence Number
%t - Thread Number
%r - Resetlogs ID (Ensure uniqueness when you reset the log sequence number)
%d - Database ID.

3. Log_archive_dest_state_n: (Enable Defer alternate reset)
Enable - available (default)
Defer - temporarily unavailable
Alternate - Initially deferred. Available when there is a failure in the primary destination.

4. Log_archive_max_processes : Maximum number of active archive processes. default 2. Range 1 - 10.

5. Log_archive_min_succeed_dest: Minimum number of destinations that arcn must write before overwriting the filled in online redo log file. Default - 1.

6. Log_archive_duplex_dest: When you use this parameter you can archive to a maximum of two destination. The first destination is given by log_archive_dest parameter and the second destination by this parameter.

7. Log_archive_start: Set this parameter to true to enable the archiver starts when you restart the instance. This parameter is not dynamic, so you must restart the instance to take effect.

SQL> alter system set log_archive_start=true scope=spfile;
SQL> Shutdown Immediate;
To enable automatic archiving with out shutting down the instance,
SQL> Alter system archive log start;
--Change the log_archive_start to true during next startup.

In 10g this parameter has been deprecated. By enabling the the archive log mode, the archiver will start automatically.