Adding or Dropping of Redo file in Dataguard Standby Environment

Redo file adding or dropping in Data-guard Environment

Adding or Dropping the Redo log files in Primary database has not effect on Standby Data-guard.
But you make changes to improve the performance if you switch the Primary to Standby it caused similar performance issue with Standby.
Note: Adding and dropping does not effect standby (it keep working) but it is important to keep it both in Sync.

Process

Check the redo changes done in Primary and sync that changes in Standby by Stopping the recovery and make STANDBY_FILE_MANAGEMENT = MANUAL value. Then sync the primary changes with standby changes and again change parameter to AUTO and start the recovery on Standby database.

On Primary Server:

1. Check the redo changes on Primary

SELECT a.GROUP#, a.THREAD#, a.SEQUENCE#, a.ARCHIVED, a.STATUS,
b.MEMBER AS REDOLOG_FILE_NAME, (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;

On Standby Server:

1. Cancel the Redo log apply service running.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Change the STANDBY_FILE_MANAGEMENT to MANUAL from AUTO in Standby Server.

-- Check the current value
Show parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
------------------------- ------- ----------
standby_file_management string AUTO


-- Change the value to Manual
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT = MANUAL;

3. Add or Drop the Redo log file in Standby:

-- For Adding redolog file
ALTER DATABASE ADD LOGFILE '/disk1/oracle/oradata/payroll/prmy3.log' SIZE 100M;


-- For Dropping redolog file
ALTER DATABASE DROP LOGFILE '/disk1/oracle/oradata/payroll/prmy3.log';

4. Change the STANDBY_FILE_MANAGEMENT parameter value to AUTO.

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT = AUTO;

5. Start the applied log service on standby.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

6. If you have more than one standby repeat this steps.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.