Resizing Redo Logs

It has been seen in cases where ORA-16401 and ORA-16055 reported in primary alert log when redo log switch is over frequently.

So suggestion is to Increase the Size of the Online Redologs to reduce Redolog Switch Frequency. And this may also required to improve primary database performance.

So the purpose of this article is How to Increase size of Redo Logs in a Dataguard Environment when Physical Standby in place.



Solution

Primary Database:

$sqlplus sys/admin as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 26 10:41:27 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         prim         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:

$sqlplus sys/admin as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 26 10:48:30 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         stdby         PHYSICAL STANDBY

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



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> 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.

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.



The files will be created in either the default or current directory of the database server, depending upon your operating system.

Here it would be created in fast_recovery_area since it is already configured.

Use can fully specify filenames of new log members to indicate where the operating system file should be created.
for example:
ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/app/oracle/oradata/prim/redo01.log') SIZE 100M;



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> 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 log switches, we can check the Status of Group 3

SQL> alter system switch logfile;

System altered.

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

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

SQL> alter system switch logfile;

System altered.

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

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

SQL> alter system checkpoint;

System altered.

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

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         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:


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 disconnect from 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


Happy Learning..!!
Cheers..!!

Standby Redo Logs

All DBA's and Interested persons who wish to implement Data Guard in Oracle 9i/10g/11g (9.x-11.x).


1. Standby Redo Logs - what are they and when to use them ??
-------------------------------------------------------------

Starting Oracle 9i you have to opportunity to add Standby Rodo Log Groups to your Online Redo Log Groups. These Standby Redo Logs then store the information received from the Primary Database. In case of a Failover situation, you will
have less data loss than without Standby Redo Logs.

In Oracle 91 only Physical standby support SRL but from 10g onwards both Physical and Logical standby supports SRL.

The great Advantage of Standby Redo Logs is that every Entry written into the Online RedoLogs of the Primary Database is transferred to the Standby Site and written into the Standby Redo Logs at the same time; therefore, you reduce the probability of Data Loss on the Standby Database.

Starting with 10g it is possible to start Real-Time Apply with Physical and Logical Standby Databases. With Real-Time Apply, the Redo is applied to the Standby Database from the Standby RedoLog instead of waiting until an Archive Log is created. So Standby Redo Logs are required for Real-Time Apply.


2. How Do I create Standby Redo Logs ??
------------------------------------------------------------

Standby Redo Logs are additional Redo Log Groups. If you were to query the V$LOGFILE view on the Standby Database, the output would typically look something like this:

   SQL> select * from v$logfile;

   GROUP# STATUS  TYPE    MEMBER
   ------ ------- ------- ------------------------------------
        1          ONLINE C:\ORACLE\ORADATA\STANDBY\REDO01.LOG
        2          ONLINE C:\ORACLE\ORADATA\STANDBY\REDO02.LOG
        3          ONLINE C:\ORACLE\ORADATA\STANDBY\REDO03.LOG

Before you add the Standby Redo Logs to your Standby Database, verify the number of maximum Logfile Groups and Logfile Members. If you don't remember these values, you can look at the CREATE CONTROLFILE script. To get such a
script, run the following command:

   SQL> alter database backup controlfile to trace;

This will create a *.TRC-file in your UDUMP Directory. When you edit or view this newly created file, you will find a script to recreate the Controlfile.

Notice the following relevant entries:

    MAXLOGFILES   8
    MAXLOGMEMBERS 3

In this example you can create a maximum of eight (8) Logfile groups.  Each group can contain a maximum of three (3) Members.

Now we can add Standby Redo Log files to the Standby Database (of course, the Standby Database must be in the MOUNT State):

   SQL> alter database add standby logfile group 4 ('C:\ORACLE\ORADATA\STANDBY\STBY04.LOG') SIZE 5M;

You can also add any further members to each group:

   SQL> alter database add standby logfile member 'C:\ORACLE\ORADATA\STANDBY\STBY14.LOG' to group 4;

Please keep in mind that if the RFS process is able to write into a Standby Redo Log, the filesize of the Standby Redo Log MUST be equal to the current Online Redo Log of the Primary Database!!

3. Limitations to Standby Redo Logs
----------------------------------------------

In a Oracle 9i/10g Data Guard Environment, the RFS Process on the Standby Database receives the Data from the Primary and writes it to Disk (either Standby Redo Logs or Archived Redo Logs).

If you consider using Standby Redo Logs, you must make certain that they are the same size as the Online Redo Logs. If you have different sizes of Online Redo Logs, you have to create corresponding Standby Redo Logs. The RFS process
won't attach Standby Redo Logs if they are different from the Online Redo Log.
It is recommended to have at least one more of Standby Redo Log Group as you have of Online Redo Log Groups per Thread and Size..

Standby Redo Logs are filled with the same information that is written to the Online Redo Logs of the Primary Database.  Therefore, only the LGWR can provide this information to the Standby RFS process, so Standby Redo Logs will only
benefit if you set LGWR as the Transmitter on the Primary Database in the LOG_ARCHIVE_DEST_n Initialization Parameter. Starting with 10.2.0, even the ARCH is able to write into Standby RedoLogs.

The RFS-Process always tries to allocate the next available Standby RedoLog, so it is possible that you encounter a Switch between only two Standby RedoLogs,although you created lots more of them. There's no rota defined here like in
Online RedoLogs. This is reported in Bug 2722195.

NOTE : Starting with 10.2.0, even the ARCH is able to write into Standby RedoLogs.

4. Differents in the Log Apply Services when using Standby Redo Logs
--------------------------------------------------------------------

In case you do not have Standby Redo Logs, an Archived Redo Log is created by the RFS process and when it has completed, this Archived Redo Log is appliedto the Standby Database by the MRP (Managed Recovery Process) or the Logical
Apply in Oracle 10g when using Logical Standby. An open (not fully written) ArchiveLog file cannot be applied on the Standby Database and will not be used in a Failover situation. This causes a certain data loss.

If you have Standby Redo Logs, the RFS process will write into the Standby Redo Log as mentioned above and when a log switch occurs, the Archiver Process of the Standby Database will archive this Standby Redo Log to an Archived Redo Log,
while the MRP process applies the information to the Standby Database.  In a Failover situation, you will also have access to the information already written in the Standby Redo Logs, so the information will not be lost.

Starting with Oracle 10g you have also the Option to use Real-Time Apply with Physical and Logical Standby Apply. When using Real-Time Apply we directly apply Redo Data from Standby RedoLogs. Real-Time Apply is also not able to apply Redo
from partial filled ArchiveLogs if there are no Standby RedoLogs. So Standby RedoLogs are mandatory for Real-Time Apply.

NOTE : In 12c DEFAULT MRP will go to REAL TIME APPLY mode.

Default Standby recovery in REAL time apply.
      SQL>alter database recover managed standby database disconnect;
To Start MRP in non real time apply mode use,(in 12c)
       SQL>alter database recover managed standby database using archived logfile disconnect;

5. Standby RedoLogs and the Data Guard Broker
---------------------------------------------

If there is an active Data Guard Broker Configuration on this Data Guard Environment the following Error can be raised in the Data Guard Broker Configuration if the setup Transport Method (LogXptMode) is 'SYNC' or 'ASYNC'
and there are no Standby RedoLogs configured:

 Warning: ORA-16809: multiple warnings detected for the database

In the corresponding DRC.LOG-Files and the Database Status you can find:

 ORA-16789: standby redo logs not configured

-> So please add corresponding Standby RedoLogs to the Standby Database. As per default the Data Guard Broker tries to start Log Apply Services in Real-Time Apply Mode which is not possible without Standby RedoLogs.Note that starting with Oracle 11g ARCH Log Transport Method is deprecated.

Happy Learning..!!

Cheers..!!