How to add new Redo Log Group/Member in Oracle Database?
Adding a new log group in Oracle Database | Adding new member in particular log group in Oracle Database
 |
| Add a Redo Log File Group or Member |
Adding a new log group or a new log member is quite an easy task. But before you add one, you need to take care of few things
- The size of Redo Log Group (Members)
- The Path where you want to create the Log Members
Below mentioned query will help you find the size and number of members in each Log Group already existing in your Oracle Database:
SQL> select GROUP#, BYTES/1024/1024 SIZE_MB, MEMBERS, STATUS from V$LOG;
GROUP# SIZE_MB MEMBERS STATUS
---------- ---------- ---------- ----------------
1 50 1 INACTIVE
2 50 1 INACTIVE
3 50 1 CURRENT
Now find the path where already created REDO Log members are present,
SQL> select GROUP#, STATUS, MEMBER from V$LOGFILE;
GROUP# STATUS MEMBER
---------- ------- --------------------------------------------------
3 /u01/app/oracle/oradata/oradb12c/redo03.log
2 /u01/app/oracle/oradata/oradb12c/redo02.log
1 /u01/app/oracle/oradata/oradb12c/redo01.log
Now we'll add some new REDO Log Groups/Members in the below mentioned queries.
1) Add a new log group with only one member (ideally you should create log group with 2 members at least as Oracle recommends the same but the database can also work with a single member in each group. Please also read my port regarding Multiplexing a Redolog to know how to create multiple redo log members in a single group).
alter database add logfile ('/u01/app/oracle/oradata/oradb12c/redo04.log') size 50M;
This will automatically create a new GROUP with the next GROUP# sequence and add the member to the same.
SQL> select GROUP#, STATUS, MEMBER from V$LOGFILE;
GROUP# STATUS MEMBER
---------- ------- --------------------------------------------------
3 /u01/app/oracle/oradata/oradb12c/redo03.log
2 /u01/app/oracle/oradata/oradb12c/redo02.log
1 /u01/app/oracle/oradata/oradb12c/redo01.log
4 /u01/app/oracle/oradata/oradb12c/redo04.log
2) Add a new log group by mentioning GROUP number and multiple log members.
alter database add logfile group 5 ('/u01/app/oracle/oradata/oradb12c/redo05a.log','/u01/app/oracle/oradata/oradb12c/redo05b.log') size 50M;
SQL> select GROUP#, STATUS, MEMBER from V$LOGFILE;
GROUP# STATUS MEMBER
---------- ------- --------------------------------------------------
3 /u01/app/oracle/oradata/oradb12c/redo03.log
2 /u01/app/oracle/oradata/oradb12c/redo02.log
1 /u01/app/oracle/oradata/oradb12c/redo01.log
4 /u01/app/oracle/oradata/oradb12c/redo04.log
5 /u01/app/oracle/oradata/oradb12c/redo05a.log
5 /u01/app/oracle/oradata/oradb12c/redo05b.log
6 rows selected.
Note: Please make sure you have equal number of MEMBERS in each LOG group. This is just a demo of add logfile groups and members.
3) Add a Log Members in an Existing REDO LOG Group.
alter database add logfile member '/u01/app/oracle/oradata/oradb12c/redo03a.log' to group 3;
alter database add logfile member '/u01/app/oracle/oradata/oradb12c/redo02a.log' to group 2;
alter database add logfile member '/u01/app/oracle/oradata/oradb12c/redo01a.log' to group 1;
alter database add logfile member '/u01/app/oracle/oradata/oradb12c/redo04a.log' to group 4;
SQL> select GROUP#, STATUS, MEMBER from V$LOGFILE;
GROUP# STATUS MEMBER
---------- ------- --------------------------------------------------
3 /u01/app/oracle/oradata/oradb12c/redo03.log
2 /u01/app/oracle/oradata/oradb12c/redo02.log
1 /u01/app/oracle/oradata/oradb12c/redo01.log
4 /u01/app/oracle/oradata/oradb12c/redo04.log
5 /u01/app/oracle/oradata/oradb12c/redo05a.log
5 /u01/app/oracle/oradata/oradb12c/redo05b.log
3 INVALID /u01/app/oracle/oradata/oradb12c/redo03a.log
2 INVALID /u01/app/oracle/oradata/oradb12c/redo02a.log
1 INVALID /u01/app/oracle/oradata/oradb12c/redo01a.log
4 INVALID /u01/app/oracle/oradata/oradb12c/redo04a.log
The newly created members will be having the STATUS as INVALID. Perform 2 or more LOGFILE SWITCHES to add the new member to valid list.
SQL> alter system switch logfile;
SQL> alter system checkpoint;
All the members/groups will now be in valid state.
GROUP# STATUS MEMBER
---------- ------- --------------------------------------------------
3 /u01/app/oracle/oradata/oradb12c/redo03.log
2 /u01/app/oracle/oradata/oradb12c/redo02.log
1 /u01/app/oracle/oradata/oradb12c/redo01.log
4 /u01/app/oracle/oradata/oradb12c/redo04.log
5 /u01/app/oracle/oradata/oradb12c/redo05a.log
5 /u01/app/oracle/oradata/oradb12c/redo05b.log
3 /u01/app/oracle/oradata/oradb12c/redo03a.log
2 /u01/app/oracle/oradata/oradb12c/redo02a.log
1 /u01/app/oracle/oradata/oradb12c/redo01a.log
4 /u01/app/oracle/oradata/oradb12c/redo04a.log
10 rows selected.
SQL> select GROUP#, BYTES/1024/1024 SIZE_MB, MEMBERS, STATUS from V$LOG;
GROUP# SIZE_MB MEMBERS STATUS
---------- ---------- ---------- ----------------
1 50 2 INACTIVE
2 50 2 INACTIVE
3 50 2 INACTIVE
4 50 2 CURRENT
5 50 2 INACTIVE
No comments:
Post a Comment