Wednesday, May 28, 2014

Oracle 11g : Adding New Redo log group and Multiplexing existing ones on Primary and Standby database


1. Multiplex the existing log file groups  from currently one logfile member to two
2. Add one redo log group with two log file members on both primary and standby databases

Below is the existing number of log-files on primary database.

SQL >  SELECT, count(a.member) FROM v$logfile a, v$log b WHERE =
  2   group by
  3   order by 1;

---------- ---------------
         1               1
         2               1
         3               1

The above output shows that we have three logfile group with one member each. Same goes for the standby database mentioned below (group# 4,5,6) with one member each.

SQL> select * from logfile;

 GROUP# STATUS  TYPE    MEMBER                                  IS_
------- ------- ------- ------------------------------------------ ---
1 ONLINE   /u02/oracle/oradata/oradb/redo01.log             NO
2 ONLINE         /u02/oracle/oradata/oradb/redo02.log             NO
3 ONLINE         /u02/oracle/oradata/oradb/redo03.log             NO
4 STANDBY     /u02/oracle/oradata/oradb/sredo01.log            NO
5 STANDBY     /u02/oracle/oradata/oradb/sredo02.log            NO
6 STANDBY     /u02/oracle/oradata/orabb/sredo03.log            NO

Now lets get going with our two objectives at the start of this post

 1. below on both primary and standby

below needs to be done on both primary and standby

 SQL>alter system set standby_file_management=manual;
2. On Standby

cancel the MRP (recovery) process.

 SQL> alter database recover managed standby database cancel;

3. Now lets multiplex the existing groups first

On Primary:

 SQL>alter database add logfile member '/u03/oracle/oradata/oradb/redo01_b.log' to group 1;
 SQL>alter database add logfile member '/u03/oracle/oradata/oradb/redo02_b.log' to group 2;
 SQL>alter database add logfile member '/u03/oracle/oradata/oradb/redo03_b.log' to group 3;

On Standby:

 SQL>alter database add standby logfile member '/u03/oracle/oradata/oradb/sredo01_b.log' to group 4;
 SQL>alter database add standby logfile member '/u03/oracle/oradata/oradb/sredo02_b.log' to group 5;
 SQL>alter database add standby logfile member '/u03/oracle/oradata/oradb/sredo03_b.log' to group 6;

4. Add the additional redo log group with multiplexed members

Once multiplex of redo members done, add one more group as part of our second objective

On Primary:

SQL>ALTER DATABASE ADD LOGFILE GROUP 7 ('/u02/oracle/oradata/oradb/redo07.log', '/u03/oracle/oradata/oradb/redo07_b.log') SIZE 50M;

On Standby:

 ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/u02/oracle/oradata/oradb/sredo08.log', '/u03/oracle/oradata/oradb/sredo08_b.log') SIZE 50M;

 5: restart the MRP pricess on Standby




  2. No matter what database platform you’re running, dbaDIRECT is your answer for 24×7 monitoring and expert skill, at a lower cost than what’s possible with internal administration. We offer each of our core remote management services for all major database platforms, including Oracle, Sybase, MySQL, SQLServer, and IBM DB2. Our team of DBAs is here ’round the clock for your database needs, capable of servicing any size organization at any time of the day. Period.
    Remote dba services support 24x7 of below mentioned applications - more… Online Training- Corporate Training- IT Support U Can Reach Us On +917386622889 - +919000444287