Oracle Archive Log Management

An archived redo log file is a copy of a filled member of an online redo log group. The file is not considered part of the database, but is an offline copy of an online redo log file created by the database and written to a user-specified location.

Archived redo log files are a crucial part of a backup and recovery strategy. You can use archived redo log files to:

• Recover a database backup
• Update a standby database
• Obtain information about the history of a database using the Oracle LogMiner utility.

The operation of generating an archived redo log file is known as archiving. This operation is either automatic or manual. It is only possible when the database is in ARCHIVELOG mode.

Database Maintain

• No Archive Log Mode
• Archive Log Mode

No Archive Log Mode:

• The Online Redo Logfile are overwritten each time.
• LGWR does not overwrite until ckpt completed for that group.

Archive Log Mode:

• The Online Redo Logfile are backup as Offline Archive Redo Logfile.
• Backup of all transaction help to recover database at any point in time.

SQL> archive log list;

How To Switching Archive Mode: DB allows to change the archive log mode at mount stage

alter database mount;

alter database archivelog;

alter database noarchivelog;

alter database open;

SQL> archive log list;

show parameter db_recovery_file_dest;

Archive Log Destination:

select * from v$archived_log;

Parameters	Description
log_archive_dest	archival destination text string
log_archive_trace	Establish archivelog operation tracing level
log_archive_format	archival destination format
log_archive_dest_1	archival destination #1 text string
log_archive_dest_state_1	archival destination #1 state text string
log_archive_start	start archival process on SGA initialization
log_archive_duplex_dest	duplex archival destination text string
log_archive_config	log archive config
log_archive_min_succeed_dest	minimum number of archive destinations that must succeed
log_archive_max_processes	maximum number of active ARCH processes
standby_archive_dest	standby database archivelog destination text string
archive_lag_target	Maximum number of seconds of redos the standby could lose

select dest_name,destination from v$archive_dest where status='VALID';

SQL> alter system set log_archive_dest_2='location=/u01/app/oracle/oradata/pearl/arch/';

Log File Destination:

select a.sequence#,b.member from v$log a, v$logfile b where a.group#=b.group# order by 1;

Logfile Forced to archive:

alter system archive log all;

select sequence#, archived from v$log;

Archive Parameters:

LOG_ARCHIVE_FORMAT = arch_%t_%s_%r.arc

               %t – thread number.

               %s – log sequence number.

               %r – reset logs ID (a timestamp value).

Archive Views:

v$database, V$archived_log, V$archive_dest, V$archive_processes, V$backup_redolog

How to enable Archiver and set the location:

shut immediate

startup mount

alter database archivelog;

alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/pearl/arch/' scope=both;

alter database open;