How to enable archivelog mode in database

Hi,

For an experienced DBA’s and support engineers this might be a very basic and irrelevant post but for freshers it is very important to know archive logs because archive logs forms the base of recovery  of database ,replication of database and many more features of oracle database are dependent on archive logs.

Also for experienced people it is always a good practice to refresh your basic concepts because all the bigger problems and errors have their roots in basic concepts.So if u have strong base u can get at least some hints of the error and u can then captiousness on that and solve the errors.

Anyways, archive logs are nothing but the offline copies of redo log files.As we know any DML,DDL operation performed on the database will get registered in the online redo log files so that the operation can be recovered back in case of instance failure.But since the online redo log groups are usually 3-10 in number they gets overwritten in a cyclic manner .that is whenever a redo log is filled the LGWR switches to another redo log group when second fills it switches to third and so on until the time when last redo log group is filled ,After that the LGWR switches back to first redo log group hence the information in first redo log group gets overwritten.Hence if there is any failure at this moment then we cannot recover the database since some amount of redo information from group 1 has been overwritten and hence lost.

So if we can make copy of the redo logs once it gets  filled, there will be no loss of information even when redo logs are overwritten  .This offline copy of redo logs is nothing but archive logs .In other words archive logs are the archive of the redo logs which are generated  each time an redo log  gets filled.

To verify that the database is in no archive log mode log into the sqlplus as sys and issue following command and u must get the following output




SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /export/home/oracle/temp/oracle/arch
Oldest online log sequence7
Current log sequence 9

To enable the database in archive log mode following parameters must be set in init.ora file of instance

log_archive_dest_1=’location=’

log_archive_dest_state_1=enable

log_archive_start=true /* this parameter is deprecated in Oracle 10gÂ

log_archive_format=arc_%t_%s_%r.dbf /* to specify the format names of archive logs

After making changes in init.ora file start the database in mount phase from sqlplus

SQL> startup mount;

then issue following command

SQL> alter database archivelog;

Database altered

then open the database

SQL> alter database open;

Database altered

To ensure whether database has put in archive log mode give the following command

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /export/home/oracle/temp/oracle/arch
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9 give switch logfile and ensure that log sequenec count has been incremented and archives are also generated at the destination specified by log_archive_dest_1=

SQL> alter system switch logfile;

SQL> archive log list

 Database log mode Archive Mode
Automatic archival Enabled
Archive destination /export/home/oracle/temp/oracle/arch
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10

Using these tables we can verify that we are infact in ARCHIVELOG mode:

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> select DEST_NAME,STATUS,DESTINATION from V$ARCHIVE_DEST;