ARCHIVED LOG FILES

What is an archived redo log file?

Oracle enables you to save filled groups of online redo log files to one or more offline destinations, known collectively as the archived redo log, or more simply archive log. The process of turning online 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 identical filled members of an online redo log group. It includes the redo entries present in the identical member of a redo log group and also preserves the group's unique log sequence number. For example, if you are multiplexing your online redo log, and if Group 1 contains member files (depending upon the number of members you have attached for each group) redo_log_01_01.log and redo_log_01_02.log, then the archiver process (ARCn) will archive one of these identical members. 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 running in ARCHIVELOG mode, the log writer process (LGWR) is not allowed to reuse and hence overwrite an online redo log group until it has been archived. The background process ARCn automates archiving operations when automatic archiving is enabled. Oracle starts multiple archiver processes as needed to ensure that the archiving of filled online redo logs does not fall behind.

How they are useful to me???

ARCHIVELOG mode enabled databases are also called media recovery enabled databases. This means that you shall be able to restore and recover the database.

RESTORE-ing the database means restoring from the backup. The backup may be online, offline or a logical export of a database, schema or table.

RECOVERY means recovering the database to point of time of failure, log sequence or change sequence number. To perform these kind of operations we need the archived redo log files generated by Oracle from the time of the start of the last backup, which has been used to restore the database. The required number of the archived log files is dependent on kind of recovery you are going for.

So you can use archived redo logs to:

(1) Recover a database
(2) Update a standby database
(3) Gain information about the history of a database using the LogMiner utility.

(We shall discuss standby database and LogMiner at a later point of time, as that discussion is irrelevant here).

What if I run my database in NOARCHIVELOG MODE???

When you run your database in NOARCHIVELOG mode, you disable the archiving of the online redo log. The database's 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.

The choice of whether to enable the archiving of filled groups of online 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 online redo log files can require you to perform extra administrative operations.
NOARCHIVELOG mode protects a database only from instance failure, but not from media failure. Only the most recent changes made to the database, which are stored in the groups of the online redo log, are available for instance recovery. In other words, if a media failure occurs while in NOARCHIVELOG mode, you can only restore (not recover) the database to the point of the most recent full database backup. You cannot recover subsequent transactions.

Also, in NOARCHIVELOG mode you cannot perform online tablespace backups. Furthermore, you cannot use online tablespace backups previously taken while the database operated in ARCHIVELOG mode.

You can only use whole database backups taken while the database is closed to restore a database operating in NOARCHIVELOG mode. Therefore, if you decide to operate a database in NOARCHIVELOG mode, take whole database backups at regular, frequent intervals.

Can I use RMAN to backup my database, which is in NOARCHIVELOG mode?

Yes, you can use RMAN for offline or cold backups by keeping the database in mount mode. It is because the data files are not open at that stage.

What if I run the database in ARCHIVELOG mode???

When you run a database in ARCHIVELOG mode, you specify the archiving of the online redo log. The database control file indicates that a group of filled online redo log files cannot be used by LGWR until the group is archived. A filled group is immediately available for archiving 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. You can use a backup taken while the database is open and in normal system use if you keep an archived log.

You can keep a standby database current with its original database by continually applying the original's archived redo logs to the standby.

Can I anytime delete these archived log files from the computer at all???

If you do not delete these files at all it is a showstopper. The disks get filled with these files and database gets stuck. It is always advised to backup these archived log files on to a tape or any other destination of your choice, which does not effect the functioning of the database, and then delete them. You can determine required archived log files basing on the latest full backup of the database. There are so many factors to be considered and we shall discuss them in detail while discussing the backup strategies.

How can I set my database to ARCHIVELOG mode from NOARCHIVELOG mode???

(1) Shutdown the database with normal or immediate option but not abort option.
Shutdown immediate;
(2) Back up the database
(3) Edit the initialization parameter file to set archive log mode PARAMETERS
LOG_ARCHIVE_START=TRUE
LOG_ARCHIVE_DEST= 'c:oracleadminwhsarchive'
(4) Start a new instance and mount, but do not open, the database.
STARTUP MOUNT
To enable or disable archiving, the database must be mounted but not open.
(5) Switch the database's archiving mode. Then open the database for normal operations.
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
(6) Shut down the database.
SHUTDOWN IMMEDIATE
(7) 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.

When you set LOG_ARCHIVE_START=TRUE you are setting up automatic archiving of the database. If you do not set this it is manual and you are to manually archive the archive log files as and when they are filled. If not Oracle overwrites those online redo log files and they are no more available for performing any recovery.

How can I start the archiving after the instance is started???

To start the archiving after the instance is started issue the following statement
ALTER SYSTEM ARCHIVE LOG START;
You can optionally include the archiving destination.

If an instance is shut down and restarted after automatic archiving is enabled using the ALTER SYSTEM statement, the instance is reinitialized using the settings of the initialization parameter file.

Those settings may or may not enable automatic archiving. If your intent is to always archive redo log files automatically, then you should include LOG_ARCHIVE_START = TRUE in your initialization parameters.

How can I control the number of archive processes???

This is controlled by Oracle, if it is left to Oracle, provided you are ready to take the runtime over heads. If you do not want them and you need to take control of these kind of situations set this parameter LOG_ARCHIVE_MAX_PROCESSES. The maximum number of processes you can start using this parameter is 10.

The LOG_ARCHIVE_MAX_PROCESSES is dynamic, and can be changed using the ALTER SYSTEM statement. The following statement increases (or decreases) the number of ARCn processes currently running:

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 2, because Oracle will adequately adjust ARCn processes according to system workload.

How can I disable the archiving for my database??

To disable the automatic archiving, set this parameter in the initialization parameter file:

LOG_ARCHIVE_START=FALSE

To automatic archiving after instance startup:

ALTER SYSTEM ARCHIVE LOG STOP;

To perform manual archiving:

ALTER SYSTEM ARCHIVE LOG ALL;

How can I set the archive destination? And Can I archive to multiple destinations???

(These methods are suggested by Oracle)

Method

Initialization Parameter

Host

Example

1

LOG_ARCHIVE_DEST_n where: n is an integer from 1 to 10

Local or remote

LOG_ARCHIVE_DEST_1 = 'LOCATION=/disk1/arc' LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1'

2

LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST

Local only

LOG_ARCHIVE_DEST = '/disk1/arc' LOG_ARCHIVE_DUPLEX_DEST = '/disk2/arc'

01 Method

Perform the following steps to set the destination for archived redo logs using the LOG_ARCHIVE_DEST_n initialization parameter:

(1) Use SQL*Plus to shut down the database with normal or immediate option but not abort.
SHUTDOWN

(2) Edit the LOG_ARCHIVE_DEST_n parameter to specify from one to ten archiving locations. The LOCATION keyword specifies an operating system specific path name.
For example, enter:
LOG_ARCHIVE_DEST_1 = 'LOCATION = /disk1/archive'
LOG_ARCHIVE_DEST_2 = 'LOCATION = /disk2/archive'
LOG_ARCHIVE_DEST_3 = 'LOCATION = /disk3/archive'

If you are archiving to a standby database, use the SERVICE keyword to specify a valid net service name from the tnsnames.ora file. For example, enter:

LOG_ARCHIVE_DEST_4 = 'SERVICE = standby1'

(3) 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

These settings will generate archived logs as follows for log sequence
numbers 100, 101, and 102:

/disk1/archive/arch100.arc,
/disk1/archive/arch101.arc,
/disk1/archive/arch102.arc

/disk2/archive/arch100.arc,
/disk2/archive/arch101.arc,
/disk2/archive/arch102.arc

/disk3/archive/arch100.arc,
/disk3/archive/arch101.arc,
/disk3/archive/arch102.arc

02 Method

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.

Perform the following steps to use method 2:

(1) Use SQL*Plus to shut down the database.
SHUTDOWN

(2) Specify destinations for the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameter (you can also specify LOG_ARCHIVE_DUPLEX_DEST dynamically using the ALTER SYSTEM statement).

For example, enter:

LOG_ARCHIVE_DEST = '/disk1/archive'
LOG_ARCHIVE_DUPLEX_DEST = '/disk2/archive'

(3) Edit the LOG_ARCHIVE_FORMAT 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_%t_%s.arc

For example, the above settings generates archived logs as follows for log sequence numbers 100 and 101 in thread 1:

/disk1/archive/arch_1_100.arc, /disk1/archive/arch_1_101.arc
/disk2/archive/arch_1_100.arc, /disk2/archive/arch_1_101.arc

Which metadata views are to be accessed for information???
There are several dynamic performance views that contain useful information about archived redo log files and redo logs.

Dynamic Performance View

Description

V$ARCHIVE_DEST

this enables you to know the status of archive destination

V$ARCHIVE_DEST_STATUS

this enables you to know if a destination is valid or not

V$ARCHIVE_GAP

if you find any entries there in this database your stand by database is not in synch with production and you are to find apply all those archive log files to fill that gap.

V$ARCHIVE_PROCESSES

this lists the archive processes configured and valid with ACTIVE state besides displaying information about the state of the various archive processes for an instance.

V$PROXY_ARCHIVEDLOG

this view contains descriptions of archived log backups which are taken with a new feature called Proxy Copy. Each row represents a backup of one archived log

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

This lists the redo log files that require archiving.

V$DATABASE

Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode.

V$ARCHIVE_DEST

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

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.

What is ARCHIVE LOG LIST and when I can issue that???

You can issue that command to know the log mode and the archive log file sequence numbers etc. To issue this command you are to log in as SYS at SQL*Plus ( for Oracle 9i or higher) or at SVRMGR for all other lower versions except 7 where SQLDBA was available for login.

ARCHIVE LOG LIST

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

This display tells you all the necessary information regarding the archived redo log settings for the current instance:

The database is currently operating in ARCHIVELOG mode.
Automatic archiving is enabled.
The archived redo log's destination is D:ORACLEWHSoradata archive
The oldest filled online redo log group has a sequence number of 11160.
The next filled online redo log group to archive has a sequence number of 11163.
The current online redo log file has a sequence number of 11163