Tag Archives: Change in redo files in dataguard

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.