Resizing Redo Log on Oracle 11g (No Archivelog Mode)


[root@mars ~]# su - oracle
[oracle@mars ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 27 15:12:23 2014

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


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

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT

SQL> alter system switch logfile;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE

SQL> alter database drop logfile group 1;

Database altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         2 CURRENT
         3 INACTIVE

SQL> ALTER DATABASE ADD LOGFILE GROUP 1 (
  2  '/u01/app/oracle/oradata/itcsyariah2/redo01a.log',
  3  '/data04/redolog/itcsyariah2/redo01b.log',
  4  '/backup/redolog/itcsyariah2/redo01c.log') SIZE 250M REUSE;

Database altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 UNUSED
         2 CURRENT
         3 INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

SQL> alter database drop logfile group 2;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE GROUP 2 (
  2  '/u01/app/oracle/oradata/itcsyariah2/redo02a.log',
  3  '/data04/redolog/itcsyariah2/redo02b.log',
  4  '/backup/redolog/itcsyariah2/redo02c.log') SIZE 250M REUSE;

Database altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 UNUSED
         3 INACTIVE

SQL> alter database drop logfile group 3;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE GROUP 3 (
  2  '/u01/app/oracle/oradata/itcsyariah2/redo03a.log',
  3  '/data04/redolog/itcsyariah2/redo03b.log',
  4  '/backup/redolog/itcsyariah2/redo03c.log') SIZE 250M REUSE;

Database altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 UNUSED
         3 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT

SQL> alter system switch logfile;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

SQL>

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: