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 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s