How to ReCreate the standby Redologs in DataGuard

Q. Why someone want to change the size of standby redo log?

Incase you wanted to change the size of the primary redo logs as a result you need to change the standby redolog size or you have created the standby redologs with different sizes than the primary redo logs (by mistake).

Q. Why you need standby redologs?

All the changes in the priamry redo is propagated to the standby site and written to standby redologs so you have minimal dataloss in case of failover. From 10g onwards you can have real time apply which means you can apply the changes to standby database using the standby redologs (need not to wait for the archived log to be created and then apply).

 

Q. What you must have for using the standby redologs?

You must create the standby redologs with size same as the primary redologs. RFS process doesn't use the standby redologs if you dont have them sized same as the primary redo.

No Standby redolog -- RFS make use of Archived logs (only fully written not open or currently writing) ---Apply this to the Standby done by MRP process--Incase of failure a certain data loss as it applies only the fully archived logs to the standby

Standby redolog -- RFS will write to standby redolog--on log switch these will be archived while MRP apply these changes to the Standby Database (REAL TIME APPLY). So all info is available with Standby redo logs minimizing the dataloss.

Q. Will switchover, data shipping happen properly when RFS not using the standby redologs?

Yes, most of the things works without standby redologs, the only benefit would be that you can minimize dataloss using the standby redolog. See my following blog, this setup is with the different sizes for primary and standby logs yet switchover , data transfer happen to be as good as it should be. No issues what so ever with the DG setup, configuration and functionality other than the one I mentioned (which is a huge factor as we use DG setup for highly available system and I don't think we can afford to have dataloss so use of standby redo logs is  mandatory)

http://abhinavsarin.blogspot.com/2012/09/oracle-11g-dataguard-unfolded.html

Q. Whats not working when RFS is not using the standby redologs ?

FSFO - Fast start Fail Over
As there is a gap in applying the redologs from the archive logs to the standby database you can't enable the FSFO. The configuration will throw some error regarding the GAP, before using the standby redologs lets see the standby database verbose -


DGMGRL>  show database verbose dg1_b
Database - dg1_b
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   4 minutes 30 seconds
  Apply Lag:       4 minutes 30 seconds  Real Time Query: OFF
  Instance(s):
    dg1
  Properties:
    DGConnectIdentifier             = 'dg1_b'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'dg1_b, dg1_a'
    LogFileNameConvert              = 'dg1_b, dg1_a'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'dg1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2.amazon.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dg1_b_DGMGRL.amazon.com)(INSTANCE_NAME=dg1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    TopWaitEvents                   = '(monitor)'
Database Status:
SUCCESS


Test Case

I have a primary and standby with primary redo log sizes as 100mb and standby redolog sizes as 50mb.

SQL> select GROUP#, BYTES/1024/1024 , STATUS,MEMBERS from  v$log;
    GROUP# BYTES/1024/1024 STATUS              MEMBERS
---------- --------------- ---------------- ----------
         1             100 CURRENT                   1
         3             100 CLEARING                  1
         2             100 CLEARING                  1

SQL> select GROUP#,BYTES/1024/1024/1024 , STATUS, FIRST_TIME,NEXT_TIME from v$standby_log;
    GROUP# BYTES/1024/1024/1024 STATUS     FIRST_TIME           NEXT_TIME
---------- -------------------- ---------- -------------------- --------------------
         4           .048828125 UNASSIGNED
         5           .048828125 UNASSIGNED
         6           .048828125 UNASSIGNED
         7           .048828125 UNASSIGNED


As you can see there is no standby redolog which is active all unassigned this implies that RFS is not attaching the standby redo logs, can see the different in sizes as well.
Now let's change the size of the standby redologs and see if the RFS (Remote File Server) use the same -

Step By Step Re Creating the Standby Redologs

1. Stop the log transport from the primary.

SQL> alter system set log_archive_dest_state_2 = defer scope = memory;
System altered.

2. Stop the recover from the standby databas.

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

3. Verify that you are not getting the data in standby by doing the logswitch in primry.

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

4. Drop the standby redologs in the standby and primary (for the switchover purpose).

SQL>  ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;
Database altered.
SQL>  ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;
Database altered.
SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;
Database altered.
SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;
Database altered.
same in primary.

5. Recreate the stnadby redologs.

SQL>  alter database add standby logfile THREAD 1 group 4 ('/opt/app/oracle/11.2.0.2/A13db/oradata/dg1/dg1_standby_redo01.log') size 100m;
Database altered.
SQL> alter database add standby logfile THREAD 1 group 5 ('/opt/app/oracle/11.2.0.2/A13db/oradata/dg1/dg1_standby_redo02.log') size 100m;
Database altered.
SQL>  alter database add standby logfile THREAD 1 group 6 ('/opt/app/oracle/11.2.0.2/A13db/oradata/dg1/dg1_standby_redo03.log') size 100m;
Database altered.
SQL> alter database add standby logfile THREAD 1 group 7
  2  ('/opt/app/oracle/11.2.0.2/A13db/oradata/dg1/dg1_standby_redo04.log') size 100m;

6. Enable the log transport from the primary database.

SQL> alter system set log_archive_dest_state_2 = enable scope = both;
System altered.
7. Start the managed recovery from the standby database.

SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.

8. Check the status of the standby redologs

SQL>  select GROUP#,BYTES/1024/1024/1024 , STATUS, FIRST_TIME,NEXT_TIME from v$standby_log;
    GROUP# BYTES/1024/1024/1024 STATUS     FIRST_TIME           NEXT_TIME
---------- -------------------- ---------- -------------------- --------------------
         4            .09765625 ACTIVE     03-OCT-2012 02:45:11 03-OCT-2012 02:45:49
         5            .09765625 UNASSIGNED
         6            .09765625 UNASSIGNED
         7            .09765625 UNASSIGNED
SQL> /
    GROUP# BYTES/1024/1024/1024 STATUS     FIRST_TIME           NEXT_TIME
---------- -------------------- ---------- -------------------- --------------------
         4            .09765625 ACTIVE     03-OCT-2012 02:45:11 03-OCT-2012 02:49:09
         5            .09765625 UNASSIGNED
         6            .09765625 UNASSIGNED
         7            .09765625 UNASSIGNED

Its active , so RFS attached the standby redologs now. Let's verify the verbose once again to see if the gap has been resolved-

DGMGRL>  show database verbose dg1_b
Database - dg1_b
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    dg1
  Properties:
    DGConnectIdentifier             = 'dg1_b'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '10'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'dg1_b, dg1_a'
    LogFileNameConvert              = 'dg1_b, dg1_a'
    FastStartFailoverTarget         = 'dg1_a'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'dg1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2.amazon.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dg1_b_DGMGRL.amazon.com)(INSTANCE_NAME=dg1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    TopWaitEvents                   = '(monitor)'
Database Status:
SUCCESS

So all good, no GAP as its using the standby redologs to apply the changes to the standby database.

Will have another post for FSFO sometime soon.