Resize Redolog files in Data guard environment

Primary

1. Check the current details of redolog and standby redolog files

SQL> select group#,bytes/1024/1024 size_,status from v$log;

    GROUP#      SIZE_   STATUS
---------- ------------  ----------
         5       512    INACTIVE
         6       512    INACTIVE
         7       512    CURRENT

SQL> select group#,bytes/1024/1024 from v$standby_log;

    GROUP# BYTES/1024/1024
---------- ---------------
         1            512
         2            512
         3            512

SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- --------------------------------------------------
         1 /data01/oradata/smsprod/stby_redo04a.log
         2 /data01/oradata/smsprod/stby_redo05a.log
         3 /data01/oradata/smsprod/stby_redo06a.log
         5 /data01/oradata/smsprod/redo05a.log
         6 /data01/oradata/smsprod/redo06a.log
         7 /data01/oradata/smsprod/redo07a.log

2. Drop existing redolog files which is not in 'CURRENT' state

SQL> alter database drop logfile group 1;              
Database altered.

SQL> alter database drop logfile group 2;
Database altered.  

3. Create redologs

SQL> alter database add logfile group 1 ('/data01/oradata/smsprod/onlinelog/redo01.log','/data02/oradata/smsprod/onlinelog/redo01.log') size 1024M;
Database altered.

SQL> alter database add logfile group 2 ('/data01/oradata/smsprod/onlinelog/redo02.log','/data02/oradata/smsprod/onlinelog/redo02.log') size 1024M;
Database altered.

4. Switch logfiles manually until Group 3 becomes INACTIVE state

SQL> alter system switch logfile;
System altered.

SQL> alter system checkpoint global;
System altered.

5. Makesure group 3 is INACTIVE

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

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

6. Drop group 3 and re-create

SQL> alter database drop logfile group 3;
Database altered.

SQL> alter database add logfile group 3 ('/data01/oradata/smsprod/onlinelog/redo03.log','/data02/oradata/smsprod/onlinelog/redo03.log') size 1024M;
Database altered.

7. Create standby logfile. Standby logfile should be same size of redolog files and count should be redolog +1 (Here need to create 4 standby logfiles as we have 3 redolog files

SQL> alter database add standby logfile group 5 ('/data01/oradata/smsprod/onlinelog/stby_redo05.log','/data02/oradata/smsprod/onlinelog/stby_redo05.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 6 ('/data01/oradata/smsprod/onlinelog/stby_redo06.log','/data02/oradata/smsprod/onlinelog/stby_redo06.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 7 ('/data01/oradata/smsprod/onlinelog/stby_redo07.log','/data02/oradata/smsprod/onlinelog/stby_redo07.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 8 ('/data01/oradata/smsprod/onlinelog/stby_redo08.log','/data02/oradata/smsprod/onlinelog/stby_redo08.log') size 1024M;
Database altered.

Standby

1. Cancel Redo Apply

SQL> alter database recover managed standby database cancel;
Database altered.

2. Change standby management to manual

SQL> show parameter standby_file_management

NAME                           TYPE        VALUE
--------------------------- ----------- ------------
standby_file_management                  string            AUTO

SQL> alter system set standby_file_management=manual;
Database altered.

3. Check existing files

SQL> show parameter standby_file_management

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

    GROUP# STATUS
---------- ----------------
         7 ACTIVE
         1 UNUSED

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

    GROUP# STATUS
---------- ----------
         5 UNASSIGNED
         6 UNASSIGNED

4. Clear logfiles which is 'ACTIVE' state

SQL> alter database clear logfile group 7;
Database altered.

5. Drop All redolog and standby log files

SQL> alter database drop logfile group 1;
Database altered.

SQL> alter database drop logfile group 7;
Database altered.

SQL> alter database drop logfile group 5;
Database altered.

SQL> alter database drop logfile group 6;
Database altered.

6. Re-create same as primary

SQL> alter database add logfile group 1 ('/data01/oradata/smsprod/onlinelog/redo01.log','/data02/oradata/smsprod/onlinelog/redo01.log') size 1024M;
Database altered.

SQL> alter database add logfile group 2 ('/data01/oradata/smsprod/onlinelog/redo02.log','/data02/oradata/smsprod/onlinelog/redo02.log') size 1024M;
Database altered.

SQL> alter database add logfile group 3 ('/data01/oradata/smsprod/onlinelog/redo03.log','/data02/oradata/smsprod/onlinelog/redo03.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 5 ('/data01/oradata/smsprod/onlinelog/stby_redo05.log','/data02/oradata/smsprod/onlinelog/stby_redo05.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 6 ('/data01/oradata/smsprod/onlinelog/stby_redo06.log','/data02/oradata/smsprod/onlinelog/stby_redo06.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 7 ('/data01/oradata/smsprod/onlinelog/stby_redo07.log','/data02/oradata/smsprod/onlinelog/stby_redo07.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 8 ('/data01/oradata/smsprod/onlinelog/stby_redo08.log','/data02/oradata/smsprod/onlinelog/stby_redo08.log') size 1024M;
Database altered.

7. Change standby file management parameter

SQL> alter system set standby_file_management=auto;
System altered.

8. Start redo apply

SQL> alter database recover managed standby database disconnect from session;
Database altered.