The Systems People Inc.

The Systems People Inc.

Customer Focused Service since 1993

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.

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus / as sysdba

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

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name, log_mode from v$database;

NAME      LOG_MODE
--------- ------------
ODMDB     NOARCHIVELOG

SQL>
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     44
Current log sequence           46
SQL>

Let us issue the command to enable the archive mode.

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

System altered.

SQL>

Now we shutdown the database.

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

Resart the database.

SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceRACLE instance started.
Total System Global Area  711430144 bytes
Fixed Size                  1338896 bytes
Variable Size             536871408 bytes
Database Buffers          167772160 bytes
Redo Buffers                5447680 bytes
Database mounted.
Database opened.
SQL>

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

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     134
Current log sequence           136
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...

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  711430144 bytes
Fixed Size                  1338896 bytes
Variable Size             536871408 bytes
Database Buffers          167772160 bytes
Redo Buffers                5447680 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     134
Next log sequence to archive   136
Current log sequence           136
SQL>

Well now the database is in ARCHIVELOG Mode :-)

 

 Blog Stats

  • Total posts(18)
  • Total comments(0)

Forgot your password?