Archiving redo log files

Changing a database to archivelog mode.

1. Shut down the database instance.

2. Start a new instance and mount, but do not open, the database. - STARTUP NOMOUNT

3. Switch the database's archiving mode. Then open the database for normal operations.
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;


Enabling Automatic Archiving
Under this option, no action is required to copy a group after it fills; Oracle automatically archives it.
You can enable automatic archiving before or after instance startup.
Always specify an archived redo log destination and file name format when enabling automatic archiving.

To enable automatic archiving at instance startup include the initialization parameter LOG_ARCHIVE_START in the database's initialization parameter file and set it to TRUE:
LOG_ARCHIVE_START=TRUE
(The new value takes effect the next time you start the database)

To disable use LOG_ARCHIVE_START=FALSE

To enable automatic archiving after instance startup use the SQL statement ALTER SYSTEM with the ARCHIVE LOG START clause. You can optionally include the archiving destination.
ALTER SYSTEM ARCHIVE LOG START;


Controlling the Number of Archiver Processes Oracle starts additional archiver processes (ARCn) as needed to ensure that the automatic processing of filled redo log files does not fall behind. However, if you want to avoid any runtime overhead of invoking additional ARCn processes, you can specify the number of processes to be started at instance startup using the LOG_ARCHIVE_MAX_PROCESSES initialization parameter. Up to 10 ARCn processes can be started. This parameter also limits the number of ARCn processes that can be started for the instance. No more than the specified number of processes can ever be started.
The LOG_ARCHIVE_MAX_PROCESSES is dynamic, and can be changed using the ALTER SYSTEM statement.
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3;

There is usually no need to change the LOG_ARCHIVE_MAX_PROCESSES initialization parameter from its default value of 1, because Oracle will adequately adjust ARCn processes according to system workload.


Performing Manual Archiving
You can manually archive groups of the online redo log whether or not automatic archiving is enabled:
If automatic archiving is not enabled, then you must manually archive groups of filled online redo log files in a timely fashion.
If automatic archiving is enabled, but you want to rearchive an inactive group of filled online redo log members to another location, you can use manual archiving. It is possible that the instance can reuse the redo log group before you have finished manually archiving, and thereby overwrite the files. If this happens, Oracle will put an error message in the alert file.
ALTER SYSTEM ARCHIVE LOG ALL;


Specifying the Archive Destination
When archiving redo logs, determine the destination to which you will archive.

LOG_ARCHIVE_DEST_n

where: n is an integer from 1 to 10

Method 1
The first method is to use the LOG_ARCHIVE_DEST_n parameter (where n is an integer from 1 to 10) to specify from one to ten different destinations for archival. Each numerically-suffixed parameter uniquely identifies an individual destination.
Eg. LOG_ARCHIVE_DEST_1 = 'LOCATION= /disk1/arc'
Remote archival through Oracle Net service name.
LOG_ARCHIVE_DEST_2 = 'SERVICE = standby1'

If you use the LOCATION keyword, specify a valid path name for your operating system. If you specify SERVICE, Oracle translates the net service name through the tnsnames.ora file to a connect descriptor. The descriptor contains the information necessary for connecting to the remote database. The service name must have an associated database SID, so that Oracle correctly updates the log history of the control file for the standby database.

Edit the LOG_ARCHIVE_FORMAT initialization parameter, using %s to include the log sequence number as part of the file name and %t to include the thread number. Use capital letters (%S and %T) to pad the file name to the left with zeroes. For example, enter:
LOG_ARCHIVE_FORMAT = arch%s.arc

Method 2: Using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST
The second method, which allows you to specify a maximum of two locations, is to use the LOG_ARCHIVE_DEST parameter to specify a primary archive destination and the LOG_ARCHIVE_DUPLEX_DEST to specify an optional secondary archive destination. Whenever Oracle archives a redo log, it archives it to every destination specified by either set of parameters.
LOG_ARCHIVE_DEST = '/disk1/arc'
LOG_ARCHIVE_DUPLEX_DEST ='/disk2/arc'


Understanding Archive Destination Status
Each archive destination has the status possibilities:
Valid/Invalid--indicates whether the disk location or service name information is specified and valid
Enabled/Disabled--indicates the availability state of the location and whether Oracle can use the destination
Active/Inactive--indicates whether there was a problem accessing the destination

Several combinations of these characteristics are possible. To obtain the current status and other information about each destination for an instance, query the V$ARCHIVE_DEST view.

The characteristics determining a locations status that appear in the view are shown below. Note that for a destination to be used, its characteristics must be valid, enabled, and active.

Destination Status
Characteristics Status - VALID Valid - TRUE Enabled - TRUE Active - TRUE
The user has properly initialized the destination, which is available for archiving.

Status - INACTIVE Valid - FALSE Enabled - n/a Active - n/a
The user has not provided or has deleted the destination information.

Status - ERROR Valid - TRUE Enabled - TRUE Active - FALSE
An error occurred creating or writing to the destination file; refer to error data.

Status - DEFERRED Valid - TRUE Enabled - FALSE Active - TRUE
The user manually and temporarily disabled the destination.

Status - DISABLED Valid - TRUE Enabled - FALSE Active - FALSE
The user manually and temporarily disabled the destination following an error; refer to error data.

Status - BAD PARAM Valid - n/a Enabled - n/a Active - n/a
A parameter error occurred; refer to error data. Usually this state is only seen when LOG_ARCHIVE_START is not set.


The LOG_ARCHIVE_DEST_STATE_n (where n is an integer from 1 to 10) initialization parameter allows you to control the availability state of the specified destination (n). The destination state can have two values: ENABLE and DEFER. ENABLE indicates that Oracle can use the destination, whereas DEFER indicates that the location is temporarily disabled.


Specifying the Mode of Log Transmission
There are two modes of transmitting archived logs to their destination: normal archiving transmission and standby transmission mode. Normal transmission involves transmitting files to a local disk. Standby transmission involves transmitting files through a network to either a local or remote standby database.

Normal Transmission Mode
In normal transmission mode, the archiving destination is another disk drive of the database server. In this configuration archiving does not contend with other files required by the instance and can complete more quickly. Specify the destination with either the LOG_ARCHIVE_DEST_n or LOG_ARCHIVE_DEST parameters. Ideally, you should permanently move archived redo log files and corresponding database backups from the local disk to inexpensive offline storage media such as tape. Because a primary value of archived logs is database recovery, you want to ensure that these logs are safe should disaster strike your primary database.

Standby Transmission Mode
In standby transmission mode, the archiving destination is either a local or remote standby database.

Caution: You can maintain a standby database on a local disk, but Oracle strongly encourages you to maximize disaster protection by maintaining your standby database at a remote site.
If you are operating your standby database in managed recovery mode, you can keep your standby database in sync with your source database by automatically applying transmitted archive logs.


Viewing Information About the Archived Redo Log
V$DATABASE
Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode.

V$ARCHIVED_LOG
Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.

V$ARCHIVE_DEST
Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.

V$ARCHIVE_PROCESSES
Displays information about the state of the various archive processes for an instance.

V$BACKUP_REDOLOG
Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.

V$LOG
Displays all online redo log groups for the database and indicates which need to be archived.

V$LOG_HISTORY
Contains log history information such as which logs have been archived and the SCN range for each archived log.


For example, the following query displays which online redo log group requires archiving:

SELECT GROUP#, ARCHIVED FROM SYS.V$LOG;

To see the current archiving mode, query the V$DATABASE view:

SELECT LOG_MODE FROM SYS.V$DATABASE;


The ARCHIVE LOG LIST Command The SQL*Plus command ARCHIVE LOG LIST can be used to show archiving information for the connected instance. For example:

SQL> ARCHIVE LOG LIST

Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\ORANT\oradata\IDDB2\archive
Oldest online log sequence 11160
Next log sequence to archive 11163
Current log sequence 11163