Oracle Redo Log Files Multiplexing

First of all, let’s see output from v$log and v$logfile.



SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                                   IS_
    ---------------------------------------------------------------------------------------------------------- ---------
         3 STALE   ONLINE  G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG                                         NO
         2         ONLINE  G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG					     NO
         1         ONLINE  G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG                                         NO


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         11   52428800          1 YES INACTIVE                799976 01-MAY-12
         2          1         12   52428800          1 NO  CURRENT                 800427 01-MAY-12
         3          1         10   52428800          1 YES INACTIVE                779491 29-APR-12

As shown above, Group 2 was the CURRENT one and Group 3 becomes CURRENT one after switching log.


SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         11   52428800          1 YES INACTIVE                799976 01-MAY-12
         2          1         12   52428800          1 YES ACTIVE                  800427 01-MAY-12
         3          1         13   52428800          1 NO  CURRENT                 802153 01-MAY-12


To change Group Status from ACTIVE to INACTIVE, we need to issue checkpoint.


SQL> alter system checkpoint;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         11   52428800          1 YES INACTIVE                799976 01-MAY-12
         2          1         12   52428800          1 YES INACTIVE                800427 01-MAY-12
         3          1         13   52428800          1 NO  CURRENT                 802153 01-MAY-12



It is possible to add logfile even if Group is the CURRENT one.


SQL> alter database add logfile member 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03A.LOG' to group 3;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         11   52428800          1 YES INACTIVE                799976 01-MAY-12
         2          1         12   52428800          1 YES INACTIVE                800427 01-MAY-12
         3          1         13   52428800          2 NO  CURRENT                 802153 01-MAY-12


SQL> alter database add logfile member 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03B.LOG' to group 3;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         11   52428800          1 YES INACTIVE                799976 01-MAY-12
         2          1         12   52428800          1 YES INACTIVE                800427 01-MAY-12
         3          1         13   52428800          3 NO  CURRENT                 802153 01-MAY-12



SQL> select member from v$logfile where group# = 3;

MEMBER
------------------------------------------------------
G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG
G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03A.LOG
G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03B.LOG

It is not possible to drop CURRENT Group but we can drop any other GROUP.


SQL> alter database drop logfile  group 3;
alter database drop logfile  group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance orcl1 (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG'
ORA-00312: online log 3 thread 1: 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03A.LOG'
ORA-00312: online log 3 thread 1: 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03B.LOG'

SQL> alter database drop logfile  group 1;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         2          1         12   52428800          1 YES INACTIVE                800427 01-MAY-12
         3          1         13   52428800          3 NO  CURRENT                 802153 01-MAY-12



While adding Group again, you may consider all the groups to be of same size (better practice).


SQL> alter database add logfile group 1 size 52428800;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          0   52428800          1 YES UNUSED                       0
         2          1         12   52428800          1 YES INACTIVE                800427 01-MAY-12
         3          1         13   52428800          3 NO  CURRENT                 802153 01-MAY-12

SQL> select member from v$logfile where group# =1;

MEMBER
-----------------------------------------------------------------------------------------------------
G:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ONLINELOG\O1_MF_1_7T01969R_.LOG

While trying to add member for GROUP 1, it will generate error for same file name. we need to delete it physically as it's not an OMF FILE.


SQL> alter database add logfile member 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG' to group 1;
alter database add logfile member 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG' to group 1
*
ERROR at line 1:
ORA-00301: error in adding log file 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG' - file cannot be created
ORA-27038: created file already exists
OSD-04010:  option specified, file already exists


SQL> alter database add logfile member 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG' to group 1;

Database altered.

SQL> alter database add logfile member 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01A.LOG' to group 1;

Database altered.

SQL> alter database add logfile member 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01B.LOG' to group 1;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          0   52428800          4 YES UNUSED                       0
         2          1         17   52428800          1 YES INACTIVE                805988 01-MAY-12
         3          1         18   52428800          3 NO  CURRENT                 806007 01-MAY-12

Now, let's try to drop this OMF redo file, will encounter error eventually as it is still unused. switch log file to avoid this error.



SQL> alter database drop logfile member 'G:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ONLINELOG\O1_MF_1_7T01969R_.LOG';
alter database drop logfile member 'G:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ONLINELOG\O1_MF_1_7T01969R_.LOG'
*
ERROR at line 1:
ORA-00362: member is required to form a valid logfile in group 2
ORA-01517: log member: 'G:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ONLINELOG\O1_MF_1_7T01969R_.LOG'


SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         19   52428800          4 NO  CURRENT                 806398 01-MAY-12
         2          1         17   52428800          3 YES INACTIVE                805988 01-MAY-12
         3          1         18   52428800          3 YES ACTIVE                  806007 01-MAY-12

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         19   52428800          4 YES ACTIVE                  806398 01-MAY-12
         2          1         20   52428800          3 NO  CURRENT                 806404 01-MAY-12
         3          1         18   52428800          3 YES ACTIVE                  806007 01-MAY-12

SQL> alter database drop logfile member 'G:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ONLINELOG\O1_MF_1_7T01969R_.LOG';

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         19   52428800          3 YES ACTIVE                  806398 01-MAY-12
         2          1         20   52428800          1 NO  CURRENT                 806404 01-MAY-12
         3          1         18   52428800          3 YES ACTIVE                  806007 01-MAY-12



Let's Multiplex Members for Group 2. Here, all members are placed in same location but it's just a demo. Multiplexing must be done on different disk or disk controllers.



SQL> alter database add logfile member 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02A.LOG' to group 2;

Database altered.

SQL> alter database add logfile member 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02B.LOG' to group 2;

Database altered.


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         19   52428800          3 YES ACTIVE                  806398 01-MAY-12
         2          1         20   52428800          3 NO  CURRENT                 806404 01-MAY-12
         3          1         18   52428800          3 YES ACTIVE                  806007 01-MAY-12

SQL> select member from v$logfile order by 1;

MEMBER
---------------------------------------------------------
G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG
G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01A.LOG
G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01B.LOG
G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG
G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02A.LOG
G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02B.LOG
G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG
G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03A.LOG
G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03B.LOG

Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

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: