Advertisements
Skip to content
October 3, 2012 / Shivananda Rao P

Resizing Redo Logs in a Dataguard Envrionment (Physical Standby in place)

Primary Database: srprim
Standby database: srps

Primary Database Server: dev
Standby Database Server: uat

Database version: 11.2.0.2

I have my physical standby database in sync with the primary database with the logs being applied in the form of real time apply. As you might be aware, for the “Real Time Apply” implementation, we need to make sure that we have created the Standby Redo Logs on the standby database with the size same as that of the Online Redo logs. The Standby Redo Logs also needs to be created on the primary database, but this is not mandatory. The Standby Redo Logs would not be used on the Primary database until there is a switchover operation performed and the primary database starts behaving as a standby database. Here is a snippet of the size of the redo logs (Online and Standby Redo logs) on the primary and standby database.

Primary Database:

[oracle@dev ~]$ sqlplus sys/oracle@srprim as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sat Sep 29 16:32:37 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, DataMining
and Real Application Testing options

SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS INSTANCE_NAME DATABASE_ROLE
------ ------------- --------------
OPEN   srprim        PRIMARY

SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#;

GROUP#     Size in MB
-------    ----------
1          50
2          50
3          50

SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;

GROUP#   Size in MB
-------  ----------
 6       50
 4       50
 5       50
 7       50

Standby Database:

[oracle@uat ~]$ sqlplus sys/oracle@srps as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sat Sep 29 16:47:11 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, DataMining
and Real Application Testing options

SQL> select status,instance_name,database_role from v$database,v$Instance;

STATUS  INSTANCE_NAME  DATABASE_ROLE
------- -------------- ----------------
MOUNTED srps           PHYSICAL STANDBY

SQL> select group#, sum(bytes/1024/1024)"Size in MB" from v$loggroup by group#;

GROUP# Size in MB
------ -------------
 1     50
 2     50
 3     50

SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;

GROUP# Size in MB
------ ----------
 4     50
 5     50
 6     50
 7     50

Check if the parameter standby_file_management is set to AUTO or MANUAL on standby database. If it is not set to MANUAL, then set it.

SQL> show parameter standby_file_management

NAME                    TYPE    VALUE
----------------------- ------- -------
standby_file_management string  AUTO
SQL>
SQL>
SQL> alter system set standby_file_management=manual;

System altered.

SQL> show parameter standby_file_management

NAME                     TYPE    VALUE
-----------------------  ------- -------
standby_file_management  string  MANUAL

On the primary database, check the status of the Online Redo Logs and resize them by dropping the INACTIVE redo logs and re-creating them with the new size.

Primary:

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

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

Here you can see that Online Redo Log groups 1 and 2 are INACTIVE. Hence we can drop them and re-create with the new size.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 size 100M;

Database altered.

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

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

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 size 100M;

Database altered.

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

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

Now that Online Redo Log Groups 1 and 2 are resized and the status of Group 3 is CURRENT, switch logfiles manually until Group 3 becomes INACTIVE

After a couple of log switches, we can check the Status of Group 3

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

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

Now that Group 3 is INACTIVE, we can drop it and re-create it with the new size.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 size 100M;

Database altered.

Now, we have resized all the Online Redo Logs on the Primary Database from 50M to 100M.

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

GROUP# size in MB
------ -------------
1      100
2      100
3      100

Moving on to the Standby Redo Logs on the Primary Database:


SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

GROUP# size in MB
------ -------------
6      50
4      50
5      50
7      50

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

GROUP# STATUS
------ ------------
4      UNASSIGNED
5      UNASSIGNED
6      UNASSIGNED
7      UNASSIGNED

The status of the Standby Redo Logs (SRL) on the Primary database would be UNASSIGNED as they would be used only when the primary database starts behaving as a Standby (Switchover)
We can easily drop the UNASSIGNED SRLs and re-create them with the new size.

SQL> alter database drop standby logfile group 4;

Database altered.

SQL> alter database add standby logfile group 4 size 100M;

Database altered.

Repeat this for the remaining SRLs on the primary database and we can see below that all the SRL on the Primary database have been resized.

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

GROUP# size in MB
------ -------------
6      100
4      100
5      100
7      100

Moving on to the standby database:

Standby:

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

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

Lets try to drop Online Redo Log Group 2 as Group 1 is CURRENT status and it cannot be dropped.

SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

Here above, we faced ORA-01156 error, which is self-explainatory. The recovery (MRP process) on the standby database is active and it needs to be cancelled before we drop any Redo log groups.

SQL> alter database recover managed standby database cancel;

Database altered.

Since the status of Group 2 is still clearing, lets clear it manually before dropping the group.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 size 100M;

Database altered.

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

GROUP# STATUS
------ -------------------
1      CURRENT
3      CLEARING
2      UNUSED

The same is the case with Group 3 whose status is CLEARING. We’ll clear it manually, drop it and create with the new size.

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 size 100M;

Database altered.

To drop online redo log group 1 that is in status CURRENT, generate an archive on the primary database by switching logfile, and then clear the CURRENT online redo log group on the standby database, drop it and re-create it with the required size as done for groups 2 and 3 earlier.

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

GROUP# size in MB
------ -------------
1      100
2      100
3      100

Resizing Standby Redo Logs on standby database:

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

GROUP# size in MB
------ --------------
4      50
5      50
6      50
7      50

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

GROUP# STATUS
------ -------------
4      ACTIVE
5      UNASSIGNED
6      UNASSIGNED
7      UNASSIGNED

Clear the SRL group whose status is ACTIVE as done earlier, drop the group and re-create them with the size same as that of the Online Redo Logs.

For the SRL groups whose status is UNASSIGNED, just drop the group and recreate them with the size same as that of the Online Redo Logs.

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

GROUP# size in MB
------ ------------
1      100
2      100
3      100

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

GROUP# size in MB
------ -------------
4      100
5      100
6      100
7      100

Once all the Standby Redo Logs and Online Redo Logs have been resize on both Primary and standby database, set the STANDBY_FILE_MANAGEMENT to AUTO on the standby database and start the recovery (MRP) on standby database.

SQL> alter system set standby_file_management=auto;

System altered.

SQL> alter database recover managed standby database disconnectfrom session using current logfile;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS STATUS         SEQUENCE#
------- -------------- -------------
ARCH    CONNECTED      0
ARCH    CLOSING        66
ARCH    CONNECTED      0
ARCH    CLOSING        63
RFS     IDLE           0
RFS     IDLE           0
MRP0    WAIT_FOR_LOG   71
RFS     IDLE           71
RFS     IDLE           0

9 rows selected.

Primary:

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
70

Standby:

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
70

Here we go !!

 

COPYRIGHT

© Shivananda Rao P, 2012, 2013, 2014. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

Advertisements

19 Comments

Leave a Comment
  1. TAI / Jan 15 2013 8:16 am

    Really nice article appreciate the effort that you have put it to make it crystal clear.

  2. Shivananda Rao P / Jan 15 2013 8:19 am

    Thank you Tauseef

  3. alexeymoseyev / Jan 18 2013 8:46 am

    nice. thank you!

  4. Ashish / Feb 2 2013 10:09 am

    Thanks Shiva…Really Helpful !

  5. Shivananda Rao P / Feb 2 2013 2:03 pm

    Thank you Ashish !!

  6. ramakantc / Mar 6 2013 3:08 pm

    Hi Shiva,
    Its a nice article and I really enjoy reading it.
    Just want to double confirm about standby log as I need to implement in one of my production environment and never did it before –
    As you mentioned “Clear the SRL group whose status is ACTIVE as done earlier, drop the group and re-create them with the size same as that of the Online Redo Logs.” –
    Once I cleared this ACTIVE log file will I be able to drop and re-create it?

  7. Shivananda Rao P / Mar 6 2013 5:28 pm

    Hi Ramakant,

    To clear the ACTIVE SRL, you can either clear it on the standby database by using:
    SQL>alter database clear logfile group ;
    drop it and recreate with a new size or you can do log switches on the primary database and check if the ACTIVE standby redo log group has turned out to be UNASSIGNED, later drop it and recreate with the new size. I prefer the second way (log switches on primary, dropping UNASSIGNED SRL on standby, recreating it with new size) of going for the ACTIVE SRL rather than clearing it, dropping and recreating.

    Regards,
    Shivananda

  8. Claudio / Mar 18 2013 11:49 am

    Hello
    I’m exactly in the same configuration with dataguard and we need to increase the redo logs size.
    Folloywing your very clear procedure, I’m successfully with all steps on primary and standby.
    Thank you very much !
    Claudio

  9. Ali Malik / May 20 2013 2:02 pm

    Shiv, I really liked your article, its really very helpful.
    I have a questions, as you said that switch the logfile on Primary by using “alter system switch logfile;” to make the SRL which is CURRENT & ACTIVE to unassigned, … but you have already cancel the redo apply earlier, how can the archive be moved?
    Please respond, i want to apply on production today.Thanks!

  10. Ashish / Jul 10 2013 6:36 am

    Dear Shiva,

    I have 1 DG at 11.2 g setup where we have two standby DB one on cloud with primary and one on local Data Center. Now whenever MRP trying to apply Log on local STBY DB ..Firewall goes down means n/w link goes down. Now I am planning to ADD 1 more physical STDBY DB on Local Data Center.

    Can you please send me the step for the same?
    Can i used RMAN backup from our Local STDBY DB backup or Need to Take backup fro PRIM DB
    ?

    Regards,
    Ashish

  11. Shivananda Rao P / Jul 10 2013 7:57 am

    Hello Ashish,

    Yes, you can create a standby database using the backup of the existing standby database. But, make sure you take the “standby controlfile backup” from the primary database. Please refer this http://surachartopun.com/2012/01/create-2nd-standby-database-from-1st.html

    HTH

    Regards,
    Shivananda

  12. Ashish / Jul 10 2013 8:25 am

    Thank you Shiva for Quick Response.. I will follow this linked and Once completed i ll update you.

    Lot of thanks.

    Regards,
    Ashish

  13. sdsdsds / Oct 9 2013 12:05 pm

    Thank you for your post. It was very useful.

  14. Syed / Oct 11 2013 12:49 pm

    Hello, I am facing a issue as per alert log –
    Archived Log entry 39444 added for thread 2 sequence 25244 rlc 755640989 ID 0x8668a60a dest 2:
    RFS[34]: No standby redo logfiles of size 204800 blocks available
    RFS[34]: Opened log for thread 2 sequence 25245 dbid -2066723558 branch 755640989
    Fri Oct 11 14:09:52 2013
    Media Recovery Log /pbx_u01/oradata/archives/PSYQTEL_arc_2_25244_755640989.dbf
    Media Recovery Waiting for thread 2 sequence 25245 (in transit)

    But when checked standby_log, All log file showing Active , please help how can we get clear log and start standby redo logs in use.

    1 7 UNASSIGNED 1 0 104857600 512 512 NO UNASSIGNED 0 0 0
    2 8 UNASSIGNED 1 0 104857600 512 512 NO UNASSIGNED 0 0 0
    3 9 UNASSIGNED 1 0 104857600 512 512 NO UNASSIGNED 0 0 0
    4 10 UNASSIGNED 1 0 104857600 512 512 NO UNASSIGNED 0 0 0
    5 11 2228243738 2 24021 104857600 512 512 YES ACTIVE 881353041 10/1/2013 4:13:55 PM 881353042 10/1/2013 4:13:55 PM 881353042 10/1/2013 4:13:55 PM
    6 12 2228243738 2 24022 104857600 512 1049088 YES ACTIVE 881355972 10/1/2013 4:18:07 PM 881357282 10/1/2013 4:19:47 PM 881357282 10/1/2013 4:19:47 PM
    7 13 2228243738 2 24061 104857600 512 2178560 YES ACTIVE 881581634 10/1/2013 9:46:01 PM 881585169 10/1/2013 9:54:03 PM 881585169 10/1/2013 9:54:03 PM
    8 14 2228243738 2 24105 104857600 512 2181120 YES ACTIVE 881730691 10/2/2013 2:00:59 AM 881732911 10/2/2013 2:05:03 AM 881732911 10/2/2013 2:05:03 AM
    Thanks – Syed

  15. Shivananda Rao P / Oct 13 2013 2:10 pm

    Hi Syed,

    Can you try recreating new standby redo log group, perform a few log switches on the primary so that the ACTIVE status of SRL turns into UNASSIGNED ?

    Regards,
    Shivananda

  16. Palani / Feb 5 2014 5:37 am

    Hi Shiva,

    It is really usefull information to resize redolog for Primary site and standby site. Thanks.

    Regards
    Palani

  17. Shivananda Rao P / Feb 5 2014 7:47 am

    Thanks Palani.

    Regards,
    Shivananda

  18. gauarv / Apr 5 2014 10:33 pm

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

    STATUS GROUP#
    —————- ———-
    CURRENT 1
    UNUSED 2
    UNUSED 5
    UNUSED 4
    UNUSED 3

    How i resize this in physical standby

  19. Chandan / May 8 2014 12:25 pm

    Superb document 🙂 thank you

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: