8.3.5 Adding or Dropping Online Redo Log Files
8.3.5 add or delete the current redo log file
Changing the size and number of the online redo log files is sometimes done to tune the database. you can add or drop online redo log file groups or members to the primary database without affecting the standby database. similarly, you can drop log file groups or members from the primary database without affecting your standby database. however, these changes do affect the performance of the standby database after switchover.
Changing the size and quantity of online redo log files sometimes optimizes the database. You can add or delete online redo log groups or log members in the primary database without affecting the standby database. Similarly, you can delete Log File groups or members in the master database without affecting your slave database. However, this change will affect the performance of the slave database after normal switchover.
Caution:
Note:
Whenever you add an online redo log file to the primary database, you shocould add corresponding online and standby redo log files to the standby database.
Whenever you add online redo log files to the master database, you should add corresponding online redo logs and backup redo log files to the slave database.
For example, if the primary database has 10 online redo log files and the standby database has 2, and then you switch over to the standby database so that it functions as the new primary database, the new primary database is forced to archive more frequently than the original primary database.
For example, if the master database has 10 online redo logs and the slave database has 2 logs, then when you switch to the slave database, the new master database will be forcibly archived more frequently than the original master database.
Consequently, when you add or drop an online redo log file at the primary site, it is important that you synchronize the changes in the standby database by following these steps:
Therefore, when you add or delete online redo logs in the master database, it is important to synchronize these changes to the slave database as follows,
1.
If Redo Apply is running, you must cancel Redo Apply before you can change the log files.
1. If the redo application is running, you must cancel the redo application after you change the log file.
2.
If the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO, change the value to MANUAL.
If the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO, change it to MANUAL.
3.
Add or drop an online redo log file:
3. add or delete online redo log files:
Bytes
To add an online redo log file, use a SQL statement such as this:
Add online redo log files and use the following SQL statement:
SQL> ALTER DATABASE ADD LOGFILE '/disk1/Oracle/oradata/payroll/prmy3.log' SIZE 100 M;
Bytes
To drop an online redo log file, use a SQL statement such as this:
Delete the online redo log file by using the following SQL statement:
SQL> ALTER DATABASE DROP LOGFILE '/disk1/oracle/oradata/payroll/prmy3.log ';
4.
Repeat the statement you used in Step 3 on each standby database.
Repeat Step 3 on each slave database.
5.
Restore the STANDBY_FILE_MANAGEMENT initialization parameter and the Redo Apply options to their original states.
Restore the STANDBY_FILE_MANAGEMENT initialization parameter to AUTO, and then redo the application to restore to the original state.
References:
Important configuration parameters of Oracle Data Guard
Configure Oracle 11g Data Guard based on the same host
Explore Oracle 11g elastic uard
Oracle Data Guard (RAC + DG) archive deletion policies and scripts
Role conversion for Oracle Data Guard
FAL gap in Oracle Data Guard logs
Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby