Archivelog Mode

Enabling database archive log mode has following benefits :

- Online database backup can be taken using RMAN or user managed backup method (hotbackup/copy) - Archive log files can be mined using LOGMINOR utility to track past changes which has caused damaged to database objects like mistakenly executed delete , update , insert operations or package/procedure/function alteration. LOGMINOR provides equivalent undo statement to roll back the DML operations & session info to identify the performer of such destructive activity 

- Archive log along with redo log enables database cloning & helps Recovery operations 

- Archive log are mandatory in replication environments like logical/physical standby 

Steps to enable Database Archivelog Mode:

Oracle database archivelog can be enabled in simple 3 steps as below …

Step1: Set archivelog re-requisite init parameters Online

log_archive_dest allows to specify the os file system destination for archive log file storage

 alter system set log_archive_dest_1='LOCATION=/archive/ATS';

 show parameter log_archive_dest_1

log_archive_format allows to specify the format of archive log file , where % t is thread %s is sequence & %r (10g onwards ) is reset-log identifier which allows archivelog to be uniquely identified after change in database incarnation (OPEN RESETLOGS)

alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

show parameter log_archive_format

archive log list

Step 2:  Putting database in Archive log Mode

shut immediate;

startup mount;

alter database archivelog;
alter database open;

Step 3: Verify archive log status 

alter session set nls_date_format='DD-MON-YYYY hh24:mi:ss';

alter system switch logfile;

select * from v$log; 
archive log list

col NAME for a50

select * from v$log;

select name,thread#,sequence# from v$archived_log where sequence#=4112;

Steps to disable Database Archive log Mode:

Step1: Put database in no archive log mode

shut immediate;

startup mount

alter database noarchivelog;

alter database open;

Step2: 

Verify no archive log mode

Note: Before changing the log mode of the database it is mandatory to have clean shutdown. If database was abruptly shut down it will require an instance recovery in next startup, attempt to change log mode post a shut abort will give below error.

ORA-00265: instance recovery required, cannot set ARCHIVE LOG mode

 

Disabling archive log mode will change the incarnation of the database.

In oracle9i and later releases, change in incarnation will cause all backup’s taken before log mode changes go invalid (cannot be used to restore the database if db crash happens post log mode changes). And it is always recommended to take a full backup done post change in log mode.

In oracle 10g (onwards), %r has been appended in archive log format to identify the incarnation change of the database which still allows old backup to be reused during restoration/recovery even after log mode changes

Enabling archivelog invites careful maintenance of archive log files, in case database is unable to create archive log most probably when file system is full database hangs.

If RMAN backup is used to take the backup of archive log then delete input clause should be used to delete archive logs post backup

If user managed backup is used then archive zipping job should be deployed with manual or auto deletion

In primary/standby setup, before manual deletion of an archive log make sure it is applied on standby database as well. RMAN delete input takes care of this automatically by throwing a warning message during backup that archive cannot be deleted as it required for recovery on standby database