Enabling and disabling Archivelog mode

Enabling and disabling Archivelog mode

Solution:

First check in what mode you are.

Select NAME, CREATED, LOG_MODE, CHECKPOINT_CHANGE#, ARCHIVE_CHANGE# from V$DATABASE;

From NOARCHIVELOG -> AUTOMATIC ARCHIVELOG MODE

To turn on automatic archivelog mode for Oracle9i set the parameters.

alter system set log_archive_start=TRUE scope=spfile;
alter system set log_archive_max_processes=3 scope=spfile;
alter system set log_archive_min_succeed_dest=1 scope=spfile;
alter system set log_archive_trace=0 scope=spfile;
alter system set log_archive_format='arch%t_EDB1_%s.ARC' scope=spfile;
alter system set log_archive_dest_1='location=/oracle10/ARCHIVES/EDB1' scope=spfile;

You have to restart the database

sqlplus "/ as sysdba"

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

alter system switch logfile;

Check if the archive log file is created at the log_archive_dest_1

Depending on the version of Oracle some small differences might exist. For example at Oracle10g you don't have to set:

log_archive_start=TRUE and the log_archive_format must contain %s, %t and %r.

For RAC especially, because of the two instances lets say OTE1 and OTE2 the steps are a little bit different.

First you have to set the parameter cluster_database = FALSE

Then to startup mount only one instance (PTE1)

Then shutdown immediate this instance (PTE1)

Then change parameter cluster_database = TRUE

From AUTOMATIC ARCHIVELOG MODE -> NOARCHIVELOG

Remove all parameters from spfile that have to do with archiving, and restart the database. For example

ALTER SYSTEM RESET log_archive_format SCOPE=SPFILE SID='*';

You have to restart the database

sqlplus "/ as sysdba"

shutdown immediate;

startup mount;

alter database noarchivelog;

alter database open;