How to add or drop online-redo logs?

Below are the steps on how to add a new online redo-group. It could be of the same size as other groups or different size.
1) List log groups, their archive status, activity status and size
SQL> SELECT GROUP#, ARCHIVED, STATUS, BYTES FROM V$LOG;

GROUP# ARC STATUS BYTES
———- — —————- ———–
1 NO ACTIVE 104857600
2 NO CURRENT 104857600
3 NO INACTIVE 104857600

2) Add a new log group with a different size and query v$log to show the activity status of the new log member is UNUSED
SQL> ALTER DATABASE
ADD LOGFILE GROUP 4 (‘E:\ORACLE\DB\TESTDB\ORADATA\REDO04A.LOG’,
‘E:\ORACLE\DB\TESTDB\ORADATA\REDO04B.LOG’)
SIZE 200M;

Database altered.

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

GROUP# ARC STATUS BYTES
———- — —————- ———-
1 NO CURRENT 104857600
2 NO INACTIVE 104857600
3 NO INACTIVE 104857600
4 YES UNUSED 209715200

Droping online redo logs, if the redo log is Status is CURRENT or ACTIVE, the on-line redo log cannot be dropped. When trying to drop CURRENT online redo log, Oracle will return “ORA-01623: log 1 is current log for instance testdb (thread 1) – cannot drop” error message. When trying to drop online redo log in ACTIVE status, Oracle will return “ORA-01624: log 1 needed for crash recovery of instance testdb (thread 1)”

To drop the online redo log, the files in the group has to be dropped manually.
SQL> select * from v$logfile where group# = 3;

GROUP# STATUS TYPE MEMBER IS_
———- ——- ——- —————————————- —
3 ONLINE E:\ORACLE\DB\TESTDB\ORADATA\REDO03A.LOG NO
3 ONLINE E:\ORACLE\DB\TESTDB\ORADATA\REDO03B.LOG NO

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

Database altered.

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