Adding or Dropping of Redo file in Dataguard 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.

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.

Advertisements

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.