Friday, March 20, 2009

How to enable archivelog mode in Oracle 11g database

First lets check the current log mode:
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Mar 20 14:33:05 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 165
Current log sequence 167

SQL> select name, log_mode from v$database;

NAME LOG_MODE
--------- ------------
TEST NOARCHIVELOG

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /home/oracle/product/11g/db/db
s/spfiletest.ora
Now lets try to set the archive log destination:

SQL> alter system set log_archive_dest_1='/home/oracle/archivelog' scope=spfile;
alter system set log_archive_dest_1='/home/oracle/archivelog' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

I faced the above error, thought need to enable archiving first:

SQL> alter system set log_archive_start=TRUE
2 scope=spfile;

System altered.

But I received the error again.

SQL> alter system set log_archive_dest_1='/home/oracle/archivelog' scope=spfile;
alter system set log_archive_dest_1='/home/oracle/archivelog' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

Checked the error description:
SQL> !oerr ora 16179
16179, 00000, "incremental changes to \"%s\" not allowed with SPFILE"
// *Cause: Incremental changes to a log_archive_dest_n parameter cannot
// be made when using an SPFILE.
// *Action: Specify either LOCATION or SERVICE plus all other attributes
// to be set in one ALTER SYSTEM/SESSION SET command.

So I did some research and found that when using spfile and trying to set a parameter like log_archive_dest_1, we need to give
complete format of the parameter like:

SQL> alter system set log_archive_dest_1='location=/home/oracle/archivelog' scope=spfile;

System altered.
(Optionally we can also give other parameters in this statement like optional, reopen etc.)

Now let shutdown the database and do a startup mount:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 310380672 bytes
Database Buffers 104857600 bytes
Redo Buffers 6131712 bytes
Database mounted.

Enabling archivelog mode

SQL> alter database archivelog;

Database altered.

Open the database:

SQL> alter database open;

Database altered.

Test if archivelog is set properly:

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archivelog
Oldest online log sequence 165
Next log sequence to archive 167
Current log sequence 167
SQL> select name, log_mode from v$database;

NAME LOG_MODE
--------- ------------
TEST ARCHIVELOG

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> !ls -lrt /home/oracle/archivelog
total 44496
-rw-r----- 1 oracle oracle 45509632 2009-03-20 14:43 1_167_677948664.dbf
-rw-r----- 1 oracle oracle 1024 2009-03-20 14:43 1_168_677948664.dbf

So now we have enabled archivelog mode and have some archivelog file also!!!

8 Comments:

Leo George said...

Arup:

Thanks,
Can you check your parameter standby_archive_dest and see the value?

In my databasae (11.1.0.6.0), after setting the db_recovery_file_dest, the standby_archive_dest seems to have been set to %ORACLE_HOME%\RDBMS.

I tried doing some changes and made it to null / none for standby, but still the archive logs are getting written there. Please can you have a check and let me know.

Thanks,
Leo

Anonymous said...

can you check the parameter of standby_archive_dest and let me know.
my archivelogs are getting written to this location also. how to disable it. ?

Apun Hiran said...

Hello Leo,
The value of standby_archive_dest in my case is "?/dbs/arch". But no archives are getting generated there.
Can you post the output of:
show parameter archive;

Do you have a standby DB configured?
Regards
Apun

Apun Hiran said...

Hello Leo,
While searching I found out the following text in the oracle documentation:
Although the flash recovery area uses the LOG_ARCHIVE_DEST_10 destination by default, you can explicitly set up flash recovery areas to use one or more other LOG_ARCHIVE_DEST_n destinations or the STANDBY_ARCHIVE_DEST destination.

Check the link:
http://www.sc.ehu.es/siwebso/KZCC/Oracle_10g_Documentacion/server.101/b10823/log_transport.htm

There is a possibility that standby_archive_dest parameter is being used along with flash recovery. You can unset this parameter by configuring some other log_archive_dest parameters like log_archive_dest_10.

Regards
Apun

Allen said...

Bless you for this posting. Your page helped a lot. When I tried to change the archivelog mode through the Oracle Enterprise Manager (11g),the change would not seem to hold after the required reboot. But your script worked perfectly.

Anonymous said...

The parameter log_archive_start is deprecated in Oracle 11g as well.

Bhavesh said...

Hi,

Would like to clarify the use of log_archive_dest parameter?
1) When we set this parameter log_archive_dest_n (log_archive_dest_0 till log_archive_dest_9), what Oracle actually does? Is it generating multiple copies of the same archieve log files or it is using each destination one after another; once 1st destination is exhausted?

Any help would be hightly appreciated!!!!

Bhavesh

Apun Hiran said...

log_archive_dest_0 - log_archive_dest_9: These parameters help you set multiple locations for your archivelogs. Hence multiple copies are generated.

Regards
Apun