Oracle 11g - Changing to ARCHIVELOG mode

There are several blogs and writeups on this topic. This is one of the most fundamental topic in Oracle database administration. This post describes my experience in AWS Oracle instance.
I created the Oracle instance using AWS EC2 instance. By default (in this instance) the DB instance was in NOARCHIVELOG mode.
This can be verified by using SQL plus or EM interface.

 

01	SQL> exit;

02	Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

03	With the Partitioning, OLAP, Data Mining and Real Application Testing options

04	oracle@domU-12-31-39-03-BD-92:[/home/oracle]

05	$ sqlplus / as sysdba

06

07	SQL*Plus: Release 11.2.0.1.0 Production on Tue May 8 19:40:28 2012

08

09	Copyright (c) 1982, 2009, Oracle. All rights reserved.

10

11

12	Connected to:

13	Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

14	With the Partitioning, OLAP, Data Mining and Real Application Testing options

15

16	SQL> select name, log_mode from v$database;

17

18	NAME LOG_MODE

19	--------- ------------

20	ODMDB NOARCHIVELOG

21

22

SQL>

23	SQL> archive log list;

24	Database log mode No Archive Mode

25	Automatic archival Disabled

26	Archive destination USE_DB_RECOVERY_FILE_DEST

27	Oldest online log sequence 44

28	Current log sequence 46

29

SQL>

Let us issue the command to enable the archive mode.

 

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

2

3	System altered.

4

5

SQL>

Now we shutdown the database.

 

1	SQL> shutdown immediate;

2	Database closed.

3	Database dismounted.

4	ORACLE instance shut down.

Resart the database.

 

01	SQL> startup;

02	ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceRACLE instance started.

03	Total System Global Area 711430144 bytes

04	Fixed Size 1338896 bytes

05	Variable Size 536871408 bytes

06	Database Buffers 167772160 bytes

07	Redo Buffers 5447680 bytes

08	Database mounted.

09	Database opened.

10

SQL>

OOOPS... That did NOT seem to work!!!

 

1	SQL> archive log list;

2	Database log mode No Archive Mode

3	Automatic archival Disabled

4	Archive destination USE_DB_RECOVERY_FILE_DEST

5	Oldest online log sequence 134

6	Current log sequence 136

7

SQL>

... So what went wrong? We set log_archive_start=TRUE parameter. This is an OBSOLETE parameter since 10g. Well what do we do then?
Lets try again...

 

01	SQL> shutdown immediate;

02	Database closed.

03	Database dismounted.

04	ORACLE instance shut down.

05	SQL> startup mount;

06	ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

07	ORACLE instance started.

08

09	Total System Global Area 711430144 bytes

10	Fixed Size 1338896 bytes

11	Variable Size 536871408 bytes

12	Database Buffers 167772160 bytes

13	Redo Buffers 5447680 bytes

14	Database mounted.

15	SQL> alter database archivelog;

16

17	Database altered.

18

19	SQL> alter database open;

20

21	Database altered.

22

23	SQL> archive log list;

24	Database log mode Archive Mode

25	Automatic archival Enabled

26	Archive destination USE_DB_RECOVERY_FILE_DEST

27	Oldest online log sequence 134

28	Next log sequence to archive 136

29	Current log sequence 136

30

SQL>

Well now the database is in ARCHIVELOG Mode :-)